freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

[freetds] Return SQL_NO_DATA when an UPDATE statement didn't change anything

From: Hristo Hristov <hhristov AT vmware.com>
To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
Subject: [freetds] Return SQL_NO_DATA when an UPDATE statement didn't change anything
Date: Wed, 3 Aug 2011 02:17:51 -0700

Hi,

I wrote a "hack" to satisfy one test of the application I'm working on.
I'm not familiar with the code, so I'm not sure that it will work for all
cases.
I did live debugging and saw that the result_type variable in my test case is
TDS_DONEPROC_RESULT.

http://msdn.microsoft.com/en-us/library/ms713584%28v=vs.85%29.aspx
If SQLExecute executes a searched update, insert, or delete statement that
does not affect any rows at the data source, the call to SQLExecute returns
SQL_NO_DATA.
http://msdn.microsoft.com/en-us/library/ms709313%28v=vs.85%29.aspx
When an ODBC 3.x application calls SQLExecDirect, SQLExecute, or SQLParamData
in an ODBC 2.x driver to execute a searched update or delete statement that
does not affect any rows at the data source, the driver should return
SQL_SUCCESS, not SQL_NO_DATA. When an ODBC 2.x or ODBC 3.x application
working with an ODBC 3.x driver calls SQLExecDirect, SQLExecute, or
SQLParamData with the same result, the ODBC 3.x driver should return
SQL_NO_DATA.

--- src/vmware/src/odbc/odbc.c 2011-08-03 12:01:34.000000000 +0300
+++ src/vmware/src/odbc/odbc.c.new 2011-08-03 12:00:45.000000000 +0300
@@ -3439,6 +3439,12 @@
ODBC_RETURN(stmt, SQL_NO_DATA);
break;

+ case TDS_DONEPROC_RESULT:
+ if (stmt->errs.lastrc == SQL_SUCCESS &&
stmt->dbc->env->attr.odbc_version == SQL_OV_ODBC3
+ && stmt->row_count == 0 && !stmt->cursor)
+ ODBC_RETURN(stmt, SQL_NO_DATA);
+ break;
+
case TDS_CMD_FAIL:
/* TODO test what happened, report correct error to client */
tdsdump_log(TDS_DBG_INFO1, "SQLExecute: bad results\n");

Here is the trace from my test:
---------------------------------------------------
[ODBC][5080][1312211092.844871][SQLPrepare.c][192]
Entry:
Statement = 0x7fffe02f5c90
SQL = [UPDATE VPX_IP_POOL_ASSOCIATION WITH (ROWLOCK)
SET POOL_ID = ? WHERE NETWORK_ID = ?][length = 82 (SQL_NTS)]
[ODBC][5080][1312211092.844907][SQLPrepare.c][367]
Exit:[SQL_SUCCESS]
[ODBC][5080][1312211092.844943][SQLBindParameter.c][213]
Entry:
Statement = 0x7fffe02f5c90
Param Number = 1
Param Type = 1
C Type = -25 SQL_C_SBIGINT
SQL Type = 2 SQL_NUMERIC
Col Def = 20
Scale = 0
Rgb Value = 0x7fffe1cb9b48
Value Max = 8
StrLen Or Ind = 0x7fffe1cb9b50
[ODBC][5080][1312211092.844980][SQLBindParameter.c][393]
Exit:[SQL_SUCCESS]
[ODBC][5080][1312211092.845016][SQLBindParameter.c][213]
Entry:
Statement = 0x7fffe02f5c90
Param Number = 2
Param Type = 1
C Type = -25 SQL_C_SBIGINT
SQL Type = 2 SQL_NUMERIC
Col Def = 20
Scale = 0
Rgb Value = 0x7fffe0754cf8
Value Max = 8
StrLen Or Ind = 0x7fffe0754d00
[ODBC][5080][1312211092.845052][SQLBindParameter.c][393]
Exit:[SQL_SUCCESS]
[ODBC][5080][1312211092.845089][SQLExecute.c][183]
Entry:
Statement = 0x7fffe02f5c90
[ODBC][5080][1312211092.846736][SQLExecute.c][344]
Exit:[SQL_SUCCESS]
---------------------------------------------------

Trace after my change:
---------------------------------------------------
[ODBC][1941][1312296379.830539][SQLPrepare.c][192]
Entry:
Statement = 0x7ffffb4f05b0
SQL = [UPDATE VPX_IP_POOL_ASSOCIATION WITH (ROWLOCK)
SET POOL_ID = ? WHERE NETWORK_ID = ?][length = 82 (SQL_NTS)]
[ODBC][1941][1312296379.830576][SQLPrepare.c][367]
Exit:[SQL_SUCCESS]
[ODBC][1941][1312296379.830613][SQLBindParameter.c][213]
Entry:
Statement = 0x7ffffb4f05b0
Param Number = 1
Param Type = 1
C Type = -25 SQL_C_SBIGINT
SQL Type = 2 SQL_NUMERIC
Col Def = 20
Scale = 0
Rgb Value = 0x7ffffbc84b48
Value Max = 8
StrLen Or Ind = 0x7ffffbc84b50
[ODBC][1941][1312296379.830649][SQLBindParameter.c][393]
Exit:[SQL_SUCCESS]
[ODBC][1941][1312296379.830685][SQLBindParameter.c][213]
Entry:
Statement = 0x7ffffb4f05b0
Param Number = 2
Param Type = 1
C Type = -25 SQL_C_SBIGINT
SQL Type = 2 SQL_NUMERIC
Col Def = 20
Scale = 0
Rgb Value = 0x7ffffc090ad8
Value Max = 8
StrLen Or Ind = 0x7ffffc090ae0
[ODBC][1941][1312296379.830722][SQLBindParameter.c][393]
Exit:[SQL_SUCCESS]
[ODBC][1941][1312296379.830759][SQLExecute.c][183]
Entry:
Statement = 0x7ffffb4f05b0
[ODBC][1941][1312296379.832447][SQLExecute.c][344]
Exit:[SQL_NO_DATA]
---------------------------------------------------

BR,
Hristo

[freetds] Return SQL_NO_DATA when an UPDATE statement didn't change anything, Hristo Hristov, 08/03/2011