Re: Postgres odbc driver bug - Mailing list pgsql-interfaces

OK, I have found another person who reported this problem.  I have
attached their patch.  Can you compare yours to theirs and let me know
what you think?  Thanks.

> We have found a reproducible problem with the Postgres ODBC driver.  It has
> to do with the parameter replacement feature in the driver.  Below is a
> piece of test code that shows the problem.  We were able to fix the problem
> by making a change to execute.c in the ODBC driver code (attached).
>
> odbc driver version : Found in both 6.50 and 7.01.0004.
> postgresql database version : Found with both 7.0.2 and 7.0.3 versions of
> Postgres.
> Server Operating System: Windows2000
> Client Operation System: Windows2000
>
> Explanation of the odbc driver bug:
>
>
>    /*****************************************
>     * ERROR HAPPENS HERE:
>     * If we set the buffer that we binded to column "text1" now, it will
> FAIL.
>     * The SQLExecute in doQuery() will succeed (finding 0 rows), and the
>     * SQLFetchScroll call will return SQL_NO_DATA_FOUND.
>     *
>     * This is because of a bug in the odbc driver. The call to
> SQLNumResultCols
>     * will execute the query in the driver, but the buffer that is binded is
>
>     * empty, so no rows will be found.  This of course assumes that the
> table
>     * doesn't contain any empty records for the "text1" column, which is
> correct
>     * for our test.  Then when we call SQLExcute, the driver will realize it
> has
>     * already done the query work and will not do it again, even though our
>     * buffer for parameter substitution has changed (next line of code below
>
>     * this comment).
>     *****************************************/
>
>  Zip file is attached.  The code is currently set up to fail, but I describe
> in my comments that you can uncomment the code that sets the buffer before
> SQLNumResultCols to make things will work.
>
> Thanks for your attention,
> Keith Millard
>
>
>   _____
>
>  <http://www.swifttouch.com/SwiftCardJump.asp?SwiftLink=95NN9NB1Q1FWA> Alex
> Shore..
> Senior Software Engineer..
> Pumatech..
> tel 603-888-0666 x1017..
> ..
> ashore@pumatech.com..
> www.pumatech.com..
>
>

[ Attachment, skipping... ]

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
Index: bind.c
===================================================================
RCS file: /usr/local/src/cvsroot/psqlodbc/bind.c,v
retrieving revision 1.1.1.3
retrieving revision 1.1.1.3.2.2
diff -c -r1.1.1.3 -r1.1.1.3.2.2
*** bind.c    2001/03/24 08:14:27    1.1.1.3
--- bind.c    2001/03/24 17:08:26    1.1.1.3.2.2
***************
*** 144,153 ****
      }

      /* Data at exec macro only valid for C char/binary data */
!     if ((fSqlType == SQL_LONGVARBINARY || fSqlType == SQL_LONGVARCHAR) && pcbValue && *pcbValue <=
SQL_LEN_DATA_AT_EXEC_OFFSET)
          stmt->parameters[ipar].data_at_exec = TRUE;
      else
          stmt->parameters[ipar].data_at_exec = FALSE;

      mylog("SQLBindParamater: ipar=%d, paramType=%d, fCType=%d, fSqlType=%d, cbColDef=%d, ibScale=%d, rgbValue=%d,
*pcbValue= %d, data_at_exec = %d\n", ipar, fParamType, fCType, fSqlType, cbColDef, ibScale, rgbValue, pcbValue ?
*pcbValue: -777, stmt->parameters[ipar].data_at_exec); 

--- 144,158 ----
      }

      /* Data at exec macro only valid for C char/binary data */
!     if (pcbValue && (*pcbValue == SQL_DATA_AT_EXEC || *pcbValue <= SQL_LEN_DATA_AT_EXEC_OFFSET))
          stmt->parameters[ipar].data_at_exec = TRUE;
      else
          stmt->parameters[ipar].data_at_exec = FALSE;
+
+     /* Clear premature result */
+     if (stmt->status == STMT_PREMATURE)
+         SC_recycle_statement(stmt);

      mylog("SQLBindParamater: ipar=%d, paramType=%d, fCType=%d, fSqlType=%d, cbColDef=%d, ibScale=%d, rgbValue=%d,
*pcbValue= %d, data_at_exec = %d\n", ipar, fParamType, fCType, fSqlType, cbColDef, ibScale, rgbValue, pcbValue ?
*pcbValue: -777, stmt->parameters[ipar].data_at_exec); 

Index: convert.c
===================================================================
RCS file: /usr/local/src/cvsroot/psqlodbc/convert.c,v
retrieving revision 1.1.1.3
retrieving revision 1.1.1.3.2.1
diff -c -r1.1.1.3 -r1.1.1.3.2.1
*** convert.c    2001/03/24 08:14:31    1.1.1.3
--- convert.c    2001/03/24 15:35:10    1.1.1.3.2.1
***************
*** 838,844 ****
          param_number++;

          if (param_number >= stmt->parameters_allocated)
!             break;

          /* Assign correct buffers based on data at exec param or not */
          if (stmt->parameters[param_number].data_at_exec)
--- 838,857 ----
          param_number++;

          if (param_number >= stmt->parameters_allocated)
!         {
!             if (stmt->pre_executing)
!             {
!                 strcpy(&new_statement[npos], "NULL");
!                 npos += 4;
!                 stmt->inaccurate_result = TRUE;
!                 continue;
!             }
!             else
!             {
!                 new_statement[npos++] = '?';
!                 continue;
!             }
!         }

          /* Assign correct buffers based on data at exec param or not */
          if (stmt->parameters[param_number].data_at_exec)
***************
*** 866,873 ****
           */
          if (!buffer)
          {
!             new_statement[npos++] = '?';
!             continue;
          }

          param_ctype = stmt->parameters[param_number].CType;
--- 879,896 ----
           */
          if (!buffer)
          {
!             if (stmt->pre_executing)
!             {
!                 strcpy(&new_statement[npos], "NULL");
!                 npos += 4;
!                 stmt->inaccurate_result = TRUE;
!                 continue;
!             }
!             else
!             {
!                 new_statement[npos++] = '?';
!                 continue;
!             }
          }

          param_ctype = stmt->parameters[param_number].CType;
Index: execute.c
===================================================================
RCS file: /usr/local/src/cvsroot/psqlodbc/execute.c,v
retrieving revision 1.1.1.3
retrieving revision 1.1.1.3.2.2
diff -c -r1.1.1.3 -r1.1.1.3.2.2
*** execute.c    2001/03/24 08:14:35    1.1.1.3
--- execute.c    2001/03/24 16:40:47    1.1.1.3.2.2
***************
*** 34,39 ****
--- 34,40 ----
  #include "qresult.h"
  #include "convert.h"
  #include "bind.h"
+ #include "pgtypes.h"
  #include "lobj.h"

  extern GLOBAL_VALUES globals;
***************
*** 223,239 ****
       */
      if (stmt->prepare && stmt->status == STMT_PREMATURE)
      {
!         stmt->status = STMT_FINISHED;
!         if (stmt->errormsg == NULL)
!         {
!             mylog("%s: premature statement but return SQL_SUCCESS\n", func);
!             return SQL_SUCCESS;
!         }
          else
          {
!             SC_log_error(func, "", stmt);
!             mylog("%s: premature statement so return SQL_ERROR\n", func);
!             return SQL_ERROR;
          }
      }

--- 224,245 ----
       */
      if (stmt->prepare && stmt->status == STMT_PREMATURE)
      {
!         if (stmt->inaccurate_result)
!             SC_recycle_statement(stmt);
          else
          {
!             stmt->status = STMT_FINISHED;
!             if (stmt->errormsg == NULL)
!             {
!                 mylog("%s: premature statement but return SQL_SUCCESS\n", func);
!                 return SQL_SUCCESS;
!             }
!             else
!             {
!                 SC_log_error(func, "", stmt);
!                 mylog("%s: premature statement so return SQL_ERROR\n", func);
!                 return SQL_ERROR;
!             }
          }
      }

***************
*** 284,314 ****
      }


!     /*
!      * The bound parameters could have possibly changed since the last
!      * execute of this statement?  Therefore check for params and re-copy.
!      */
!     stmt->data_at_exec = -1;
!     for (i = 0; i < stmt->parameters_allocated; i++)
      {
!         /* Check for data at execution parameters */
!         if (stmt->parameters[i].data_at_exec == TRUE)
          {
!             if (stmt->data_at_exec < 0)
!                 stmt->data_at_exec = 1;
!             else
!                 stmt->data_at_exec++;
          }
!     }
!     /* If there are some data at execution parameters, return need data */

!     /*
!      * SQLParamData and SQLPutData will be used to send params and execute
!      * the statement.
!      */
!     if (stmt->data_at_exec > 0)
!         return SQL_NEED_DATA;


      mylog("%s: copying statement params: trans_status=%d, len=%d, stmt='%s'\n", func, conn->transact_status,
strlen(stmt->statement),stmt->statement); 

--- 290,326 ----
      }


!     /* Check if statement has any data-at-execute parameters when it is not in SC_pre_execute. */
!     if (!stmt->pre_executing)
      {
!
!         /*
!          * The bound parameters could have possibly changed since the last
!          * execute of this statement?  Therefore check for params and re-copy.
!          */
!         stmt->data_at_exec = -1;
!         for (i = 0; i < stmt->parameters_allocated; i++)
          {
!             /* Check for data at execution parameters */
!             if (stmt->parameters[i].data_at_exec == TRUE)
!             {
!                 if (stmt->data_at_exec < 0)
!                     stmt->data_at_exec = 1;
!                 else
!                     stmt->data_at_exec++;
!             }
          }
!         /* If there are some data at execution parameters, return need data */

!         /*
!          * SQLParamData and SQLPutData will be used to send params and execute
!          * the statement.
!          */
!         if (stmt->data_at_exec > 0)
!             return SQL_NEED_DATA;

+     }
+

      mylog("%s: copying statement params: trans_status=%d, len=%d, stmt='%s'\n", func, conn->transact_status,
strlen(stmt->statement),stmt->statement); 

***************
*** 778,785 ****

          }
          else
!         {                        /* for handling text fields and small
!                                  * binaries */

              if (cbValue == SQL_NTS)
              {
--- 790,796 ----

          }
          else
!         {                        /* for handling fields */

              if (cbValue == SQL_NTS)
              {
***************
*** 794,809 ****
              }
              else
              {
!                 current_param->EXEC_buffer = malloc(cbValue + 1);
!                 if (!current_param->EXEC_buffer)
                  {
!                     stmt->errornumber = STMT_NO_MEMORY_ERROR;
!                     stmt->errormsg = "Out of memory in SQLPutData (2)";
!                     SC_log_error(func, "", stmt);
!                     return SQL_ERROR;
                  }
-                 memcpy(current_param->EXEC_buffer, rgbValue, cbValue);
-                 current_param->EXEC_buffer[cbValue] = '\0';
              }
          }
      }
--- 805,839 ----
              }
              else
              {
!                 Int2 ctype = current_param->CType;
!                 if (ctype == SQL_C_DEFAULT)
!                     ctype = sqltype_to_default_ctype(current_param->SQLType);
!                 if (ctype == SQL_C_CHAR || ctype == SQL_C_BINARY)
                  {
!                     current_param->EXEC_buffer = malloc(cbValue + 1);
!                     if (!current_param->EXEC_buffer)
!                     {
!                         stmt->errornumber = STMT_NO_MEMORY_ERROR;
!                         stmt->errormsg = "Out of memory in SQLPutData (2)";
!                         SC_log_error(func, "", stmt);
!                         return SQL_ERROR;
!                     }
!                     memcpy(current_param->EXEC_buffer, rgbValue, cbValue);
!                     current_param->EXEC_buffer[cbValue] = '\0';
!                 }
!                 else
!                 {
!                     Int4 used = ctype_length(ctype);
!                     current_param->EXEC_buffer = malloc(used);
!                     if (!current_param->EXEC_buffer)
!                     {
!                         stmt->errornumber = STMT_NO_MEMORY_ERROR;
!                         stmt->errormsg = "Out of memory in SQLPutData (2)";
!                         SC_log_error(func, "", stmt);
!                         return SQL_ERROR;
!                     }
!                     memcpy(current_param->EXEC_buffer, rgbValue, used);
                  }
              }
          }
      }
Index: pgtypes.c
===================================================================
RCS file: /usr/local/src/cvsroot/psqlodbc/pgtypes.c,v
retrieving revision 1.1.1.3
retrieving revision 1.1.1.3.2.2
diff -c -r1.1.1.3 -r1.1.1.3.2.2
*** pgtypes.c    2001/03/24 08:14:46    1.1.1.3
--- pgtypes.c    2001/03/24 16:40:47    1.1.1.3.2.2
***************
*** 956,958 ****
--- 956,1011 ----
              return SQL_C_CHAR;
      }
  }
+
+ Int4
+ ctype_length(Int2 ctype)
+ {
+     switch (ctype)
+     {
+         case SQL_C_SSHORT:
+         case SQL_C_SHORT:
+             return sizeof(SWORD);
+
+         case SQL_C_USHORT:
+             return sizeof(UWORD);
+
+         case SQL_C_SLONG:
+         case SQL_C_LONG:
+             return sizeof(SDWORD);
+
+         case SQL_C_ULONG:
+             return sizeof(UDWORD);
+
+         case SQL_C_FLOAT:
+             return sizeof(SFLOAT);
+
+         case SQL_C_DOUBLE:
+             return sizeof(SDOUBLE);
+
+         case SQL_C_BIT:
+             return sizeof(UCHAR);
+
+         case SQL_C_STINYINT:
+         case SQL_C_TINYINT:
+             return sizeof(SCHAR);
+
+         case SQL_C_UTINYINT:
+             return sizeof(UCHAR);
+
+         case SQL_C_DATE:
+             return sizeof(DATE_STRUCT);
+
+         case SQL_C_TIME:
+             return sizeof(TIME_STRUCT);
+
+         case SQL_C_TIMESTAMP:
+             return sizeof(TIMESTAMP_STRUCT);
+
+         case SQL_C_BINARY:
+         case SQL_C_CHAR:
+             return 0;
+
+         default:                /* should never happen */
+             return 0;
+     }
+ }
Index: pgtypes.h
===================================================================
RCS file: /usr/local/src/cvsroot/psqlodbc/pgtypes.h,v
retrieving revision 1.1.1.3
retrieving revision 1.1.1.3.2.1
diff -c -r1.1.1.3 -r1.1.1.3.2.1
*** pgtypes.h    2001/03/24 08:14:46    1.1.1.3
--- pgtypes.h    2001/03/24 15:35:12    1.1.1.3.2.1
***************
*** 93,97 ****
--- 93,98 ----
  char       *pgtype_create_params(StatementClass *stmt, Int4 type);

  Int2        sqltype_to_default_ctype(Int2 sqltype);
+ Int4        ctype_length(Int2 ctype);

  #endif
Index: statement.c
===================================================================
RCS file: /usr/local/src/cvsroot/psqlodbc/statement.c,v
retrieving revision 1.1.1.3
retrieving revision 1.1.1.3.2.1
diff -c -r1.1.1.3 -r1.1.1.3.2.1
*** statement.c    2001/03/24 08:14:53    1.1.1.3
--- statement.c    2001/03/24 15:35:12    1.1.1.3.2.1
***************
*** 292,297 ****
--- 292,300 ----

          /* Clear Statement Options -- defaults will be set in AllocStmt */
          memset(&rv->options, 0, sizeof(StatementOptions));
+
+         rv->pre_executing = FALSE;
+         rv->inaccurate_result = FALSE;
      }
      return rv;
  }
***************
*** 519,524 ****
--- 522,528 ----
          QR_Destructor(self->result);
          self->result = NULL;
      }
+     self->inaccurate_result = FALSE;

      /****************************************************************/
      /* Reset only parameters that have anything to do with results */
***************
*** 551,568 ****
  void
  SC_pre_execute(StatementClass *self)
  {
-
      mylog("SC_pre_execute: status = %d\n", self->status);

      if (self->status == STMT_READY)
      {
          mylog("              preprocess: status = READY\n");

!         SQLExecute(self);

!         if (self->status == STMT_FINISHED)
          {
!             mylog("              preprocess: after status = FINISHED, so set PREMATURE\n");
              self->status = STMT_PREMATURE;
          }
      }
--- 555,587 ----
  void
  SC_pre_execute(StatementClass *self)
  {
      mylog("SC_pre_execute: status = %d\n", self->status);

      if (self->status == STMT_READY)
      {
          mylog("              preprocess: status = READY\n");
+
+         if (self->statement_type == STMT_TYPE_SELECT)
+         {
+             char old_pre_executing = self->pre_executing;
+             self->pre_executing = TRUE;
+             self->inaccurate_result = FALSE;

!             SQLExecute(self);

!             self->pre_executing = old_pre_executing;
!
!             if (self->status == STMT_FINISHED)
!             {
!                 mylog("              preprocess: after status = FINISHED, so set PREMATURE\n");
!                 self->status = STMT_PREMATURE;
!             }
!         }
!         else
          {
!             self->result = QR_Constructor();
!             QR_set_status(self->result, PGRES_TUPLES_OK);
!             self->inaccurate_result = TRUE;
              self->status = STMT_PREMATURE;
          }
      }
Index: statement.h
===================================================================
RCS file: /usr/local/src/cvsroot/psqlodbc/statement.h,v
retrieving revision 1.1.1.3
retrieving revision 1.1.1.3.2.1
diff -c -r1.1.1.3 -r1.1.1.3.2.1
*** statement.h    2001/03/24 08:14:54    1.1.1.3
--- statement.h    2001/03/24 15:35:13    1.1.1.3.2.1
***************
*** 213,218 ****
--- 213,221 ----
                                                           * parameter
                                                           * substitution */

+     char        pre_executing;    /* This statement is prematurely executing */
+     char        inaccurate_result;        /* Current status is PREMATURE
+                                          * but result is inaccurate */
  };

  #define SC_get_conn(a)      (a->hdbc)