[Devel] unixodbc reconnect

[Alex Gradinar]

I've implemented ODBC reconnect when connection not open or communication link 
failure.

Modifications of source
1. my_con.h
1.1. add defines
#define CON_ID(db_con)          (((struct my_con*)((db_con)->tail))->id)
#define CON_ENV(db_con)         (((struct my_con*)((db_con)->tail))->env)
#define MAX_CONN_STR_LEN 2048

1.2. add declaration of function build_conn_str

char *build_conn_str(struct db_id* id, char *buf);

2. my_con.c
2.1 remove define MAX_CONN_STR_LEN  which we moved to my_con.h
#define MAX_CONN_STR_LEN 2048

2.2. modify build_conn_str function
the buf which is saved the connect string is the parameter
the beginning of the function is
============
char *build_conn_str(struct db_id* id, char *buf)
{
 int len, ld, lu, lp;
 char *p;

 if (!buf) return 0;


 ld = id->database?strlen(id->database):0;
============

2.3. modify new_connection function
2.3.1 change definition of  conn_str
========
 char conn_str[MAX_CONN_STR_LEN];
========

2.3.2 change call of the function build_conn_str to
========
 if (!build_conn_str(id, conn_str)) {

========

3. file dbase.c function submit_query
3.1. add some local variables
========
 SQLCHAR outstr[1024];
 SQLSMALLINT outstrlen;
 SQLCHAR sqlstate[15];
 SQLCHAR ErrorMsg[SQL_MAX_MESSAGE_LENGTH+1];
 SQLINTEGER native_error = 0;
 char conn_str[MAX_CONN_STR_LEN];
 SQLRETURN sts;

========

3.2. and check slqstate of SQLAllocStmt and try to reconnect if connection not 
open or communication link failure.
new code
========
 ret = SQLAllocStmt(CON_CONNECTION(_h), &CON_RESULT(_h));
 if (!SQL_SUCCEEDED(ret))
 {
  LOG(L_ERR, "ERROR:unixodbc:submit_query: Statement allocation error %d\n",
   (int)(long)CON_CONNECTION(_h));
  extract_error("SQLAllocStmt", CON_CONNECTION(_h), SQL_HANDLE_DBC);

      sts = SQLError (CON_ENV(_h), CON_CONNECTION(_h), SQL_NULL_HSTMT, sqlstate, 
&native_error,
    ErrorMsg, sizeof(ErrorMsg), NULL);
  if (SQL_SUCCEEDED (sts) &&
      strncmp(sqlstate,"08003",5)!=0 && /* Connect not open */
      strncmp(sqlstate,"08S01",5)!=0) /* Communication link failure */ {
      return ret;
  }

             LOG(L_ERR, "ERROR:unixodbc:submit_query: try to reconnect\n");

  // Disconnect
  SQLDisconnect (CON_CONNECTION(_h));

  // Reconnect
      if (!build_conn_str(CON_ID(_h), conn_str)) {
                 LOG(L_ERR, "ERROR:unixodbc:submit_query: failed to build 
connection string\n");
      return ret;
      }

      ret = SQLDriverConnect(CON_CONNECTION(_h), (void *)1, (SQLCHAR*)conn_str,
   SQL_NTS, outstr, sizeof(outstr), &outstrlen,
              SQL_DRIVER_COMPLETE);
      if (!SQL_SUCCEEDED(ret)) {
                 LOG(L_ERR, "ERROR:unixodbc:submit_query: failed to connect\n");
                 extract_error("SQLDriverConnect", CON_CONNECTION(_h), 
SQL_HANDLE_DBC);
      return ret;
  }

  ret = SQLAllocStmt(CON_CONNECTION(_h), &CON_RESULT(_h));
  if (!SQL_SUCCEEDED(ret))
  {
      LOG(L_ERR, "Statement allocation error %d\n",
       (int)(long)CON_CONNECTION(_h));
      extract_error("SQLAllocStmt", CON_CONNECTION(_h), SQL_HANDLE_DBC);
      return ret;
  }
 }
========

Best regards,
Alex Gradinar

----- Original Message ----- 
From: "Alex Gradinar" <Alex.Gradinar at cartel.md>
To: <devel at openser.org>
Sent: Tuesday, February 28, 2006 5:13 PM
Subject: unixodbc bugs


> 1. function convert_rows in file res.c
> while allocationg memery to columns of row in this code
>
> ==========
>      CON_ROW(_h) = (strn*)pkg_malloc((int)columns);
> ==========
> allocate only amount of bytes as number of columns,
> but we have to allocate number of columns * sizeof struct strn
> This is correct code
> ==========
>      CON_ROW(_h) = (strn*)pkg_malloc((int)columns*sizeof(strn));
> ==========
> 2. function convert_rows in file res.c
> while getting data from odbc in this code
> ==========
> ret = SQLGetData(CON_RESULT(_h), i, SQL_C_CHAR,
>        (CON_ROW(_h)[i-1]).s, 1024, &indicator);
>
> ==========
> write 1024 bytes, but if we chage size of variable 's' in struct strn
> we have to keep in mind that this number of bytes we have to change too.
> This is correct code
> ==========
> ret = SQLGetData(CON_RESULT(_h), i, SQL_C_CHAR,
>        (CON_ROW(_h)[i-1]).s, sizeof((CON_ROW(_h)[i-1]).s), &indicator);
>
> ==========
> 3. function submit_query in dbase.c
> in this code
> ==========
>        if(CON_RESULT(_h))
>        {
>                ret = SQLFreeStmt(&CON_RESULT(_h), SQL_CLOSE);
>                if (!SQL_SUCCEEDED(ret))
>                {
>                        LOG(L_ERR, "Statement allocation error %d\n",
>                                (int)(long)CON_CONNECTION(_h));
>                        extract_error("SQLAllocStmt", CON_CONNECTION(_h), 
> SQL_HANDLE_DBC);
>                        return ret;
>                }
>        }
>
> ==========
> 3.1. call the function SQLFreeStmt, but error about allocation
>
> 3.2. with my odbc driver (OpenLink) SQLFreeStmt returned Error
> I recomend to change this code to
> ==========
>        if(CON_RESULT(_h))
>        {
>            SQLFreeStmt(&CON_RESULT(_h), SQL_RESET_PARAMS);
>            SQLFreeStmt(&CON_RESULT(_h), SQL_UNBIND);
>            SQLFreeStmt(&CON_RESULT(_h), SQL_CLOSE);
>            SQLFreeStmt(&CON_RESULT(_h), SQL_DROP)
>        }
> ==========
>