Thread: Bug in ODBC driver doing UPDATES and DELETES

Bug in ODBC driver doing UPDATES and DELETES

From
Tim Woodall
Date:
When doing an UPDATE or DELETE via ODBC, if no records are affected then
SQLExecute should return SQL_NO_DATA_FOUND and not SQL_SUCCESS.

I have included two patches below. The first is against RH7.2 latest update
which is tested. The other is against the latest CVS source and isn't
tested.

Regards,

Tim.


diff -ur postgresql-7.1.3/src/interfaces/odbc/statement.c postgresql-7.1.3-patched/src/interfaces/odbc/statement.c
--- postgresql-7.1.3/src/interfaces/odbc/statement.c    Mon Apr 23 02:00:49 2001
+++ postgresql-7.1.3-patched/src/interfaces/odbc/statement.c    Thu May 15 16:30:46 2003
@@ -864,6 +864,13 @@
     Int2        oldstatus,
                 numcols;
     QueryInfo    qi;
+    char        was_rows_affected = 1;
+    /* was_rows_affected is set to 0 iff an UPDATE or DELETE affects
+     * no rows. In this instance the driver should return
+     * SQL_NO_DATA_FOUND and not SQL_SUCCESS.
+     * I'm not sure about the use of char rather than int but this is
+     * consistent with the other was_* variables above.
+     */


     conn = SC_get_conn(self);
@@ -998,6 +1005,13 @@

         was_ok = QR_command_successful(self->result);
         was_nonfatal = QR_command_nonfatal(self->result);
+        if(self->result->command &&
+            (strncmp(self->result->command, "UPDATE", 6) == 0 ||
+            strncmp(self->result->command, "DELETE", 6) == 0) &&
+            strtoul(self->result->command + 7, NULL, 0) == 0)
+        {
+            was_rows_affected = 0;
+        }

         if (was_ok)
             self->errornumber = STMT_OK;
@@ -1055,7 +1069,10 @@
     }

     if (self->errornumber == STMT_OK)
-        return SQL_SUCCESS;
+        if(was_rows_affected)
+            return SQL_SUCCESS;
+        else
+            return SQL_NO_DATA_FOUND;

     else
     {






Index: statement.c
===================================================================
RCS file: /usr/local/cvsroot/psqlodbc/psqlodbc/statement.c,v
retrieving revision 1.77
diff -u -r1.77 statement.c
--- statement.c    12 May 2003 15:44:08 -0000    1.77
+++ statement.c    20 May 2003 10:45:35 -0000
@@ -1024,7 +1024,14 @@
     static char *func = "SC_execute";
     ConnectionClass *conn;
     APDFields    *apdopts;
-    char        was_ok, was_nonfatal;
+    char        was_ok, was_nonfatal, was_rows_affected = 1;
+    /* was_rows_affected is set to 0 iff an UPDATE or DELETE affects
+     * no rows. In this instance the driver should return
+     * SQL_NO_DATA_FOUND and not SQL_SUCCESS.
+     * I'm not sure about the use of char rather than int but this is
+     * consistent with the other was_* variables above.
+     */
+
     QResultClass    *res = NULL;
     Int2        oldstatus,
                 numcols;
@@ -1144,6 +1151,13 @@
     {
         was_ok = QR_command_successful(res);
         was_nonfatal = QR_command_nonfatal(res);
+        if(self->result->command &&
+            (strncmp(self->result->command, "UPDATE", 6) == 0 ||
+            strncmp(self->result->command, "DELETE", 6) == 0) &&
+            strtoul(self->result->command + 7, NULL, 0) == 0)
+        {
+            was_rows_affected = 0;
+        }

         if (was_ok)
             SC_set_errornumber(self, STMT_OK);
@@ -1246,7 +1260,10 @@
         }
     }
     if (SC_get_errornumber(self) == STMT_OK)
-        return SQL_SUCCESS;
+        if(was_rows_affected)
+            return SQL_SUCCESS;
+        else
+            return SQL_NO_DATA_FOUND;
     else if (SC_get_errornumber(self) == STMT_INFO_ONLY)
         return SQL_SUCCESS_WITH_INFO;
     else




--
God said, "div D = rho, div B = 0, curl E = - @B/@t, curl H = J + @D/@t,"
and there was light.

     http://tjw.hn.org/      http://www.locofungus.btinternet.co.uk/



Re: Bug in ODBC driver doing UPDATES and DELETES

From
Tim Woodall
Date:
On Tue, 20 May 2003, Tim Woodall wrote:

>
> When doing an UPDATE or DELETE via ODBC, if no records are affected then
> SQLExecute should return SQL_NO_DATA_FOUND and not SQL_SUCCESS.
>
> I have included two patches below. The first is against RH7.2 latest update
> which is tested. The other is against the latest CVS source and isn't
> tested.
>
>
The following patch to RH9 applies to the version in CVS (with an offset).
This patch has been tested on RH9.

Regards,

Tim.


[tim@twlinux psqlodbc]$ patch -p1 <psqlodbc-7.2.5-update.patch
patching file statement.c
Hunk #1 succeeded at 1022 (offset 60 lines).
Hunk #3 succeeded at 1258 (offset 65 lines).
[tim@twlinux psqlodbc]$ cat psqlodbc-7.2.5-update.patch
diff -ur psqlodbc-7.2.5.orig/statement.c psqlodbc-7.2.5/statement.c
--- psqlodbc-7.2.5.orig/statement.c     2002-11-29 15:43:50.000000000 +0000
+++ psqlodbc-7.2.5/statement.c  2003-06-16 11:50:32.000000000 +0100
@@ -962,7 +962,14 @@
        static char *func = "SC_execute";
        ConnectionClass *conn;
        APDFields       *apdopts;
-       char            was_ok, was_nonfatal;
+       char            was_ok, was_nonfatal, was_rows_affected = 1;
+       /* was_rows_affected is set to 0 iff an UPDATE or DELETE affects
+        * no rows. In this instance the driver should return
+        * SQL_NO_DATA_FOUND and not SQL_SUCCESS.
+        * I'm not sure about the use of char rather than int but this is
+        * consistent with the other was_* variables above.
+        */
+
        QResultClass    *res = NULL;
        Int2            oldstatus,
                                numcols;
@@ -1082,6 +1089,13 @@
        {
                was_ok = QR_command_successful(res);
                was_nonfatal = QR_command_nonfatal(res);
+               if(res->command &&
+                       (strncmp(res->command, "UPDATE", 6) == 0 ||
+                       strncmp(res->command, "DELETE", 6) == 0) &&
+                       strtoul(res->command + 7, NULL, 0) == 0)
+               {
+                       was_rows_affected = 0;
+               }

                if (was_ok)
                        SC_set_errornumber(self, STMT_OK);
@@ -1179,7 +1193,10 @@
                }
        }
        if (SC_get_errornumber(self) == STMT_OK)
-               return SQL_SUCCESS;
+               if(was_rows_affected)
+                       return SQL_SUCCESS;
+               else
+                       return SQL_NO_DATA_FOUND;
        else if (SC_get_errornumber(self) == STMT_INFO_ONLY)
                return SQL_SUCCESS_WITH_INFO;
        else

--
God said, "div D = rho, div B = 0, curl E = - @B/@t, curl H = J + @D/@t,"
and there was light.

     http://tjw.hn.org/      http://www.locofungus.btinternet.co.uk/