Rumburak
Posts: 7
Joined: Wed Mar 21, 2007 12:46 pm

SQLExecDirect {SQL no data}

Hello,

when executing an SQL-Script like this:

Code: Select all

update table1
set xxx='new_value'
from table1 where xxx='old_value
AI reports the Error "SQLExecDirect {SQL no data}" and stops executing.
This is only the case when there are no rows selected to update.

The same script is no problem in the SQL-Commandline
"(0 row(s) affected)
The database update succeeded"

MS-SQLServer 2005

This is no error, just a simple result and should not raise an error in AI!

Regards,
Rumburak
gigi
Posts: 2103
Joined: Tue Apr 11, 2006 9:55 am
Contact: Website

Hi Rumburak,

We acknowledge this behavior and after researching the cause we found the explanation. Please see the following link:
http://msdn2.microsoft.com/en-us/library/ms713611.aspx
If SQLExecDirect executes a searched update or delete statement that does not affect any rows at the data source, the call to SQLExecDirect returns SQL_NO_DATA.
Further more, the problem is whether to consider this an error or not.
We discovered a Microsoft knowledge base article ( http://support.microsoft.com/kb/195927 ) that addresses this issue.
In prior versions of the SQL Server ODBC driver, SQLExecute, SQLExecDirect, or SQLParamData returns SQL_SUCCESS when an application executes a searched UPDATE or DELETE statement that affects no rows.
For this case, the version 3.6 driver still returns SQL_SUCCESS to applications written with the ODBC 2.x API, but it returns SQL_NO_DATA to applications written with the ODBC 3.x API.
If either the ODBC 2.x application that receives SQL_SUCCESS or the ODBC 3.x application that receives SQL_NO_DATA then calls SQLRowCount, SQLRowCount returns a count of zero. ODBC 3.x more clearly defines the way results are returned than ODBC 2.x.
The Advanced Installer SQL configuration tool uses the ODBC 3.x API for better ODBC standard compliance, hence the problem. We decided, however, to improve this behavior such that SQL_NO_DATA returned by SQLExecDirect function should be considered a successful statement.

This shall be address in the next version of AI. Please let us know if need an urgent fix for this issue.

Regards,
Gigi
______________
Gheorghe Rada
Advanced Installer Team
http://www.advancedinstaller.com

Return to “Common Problems”