Commented by: millka (Martin Mueller) (millka)

I get a similar error in my ODBC based database library (without ADO).

In my library, i sometimes delete detail records belonging to some master record by something like "DELETE FROM details WHERE master_id = xyz". For speed reasons, I dont check for detail records before, but instead delete them blindly. Of course, if no detail records exist for that master record, the delete statement doesnt change the database.

According to the latest MSDN library, ODBC SqlExecute should return SQL_NO_DATA in that case.

The Firebird driver was changed accordingly because of ODBC5

Besides Firebird, my library uses MS Access, Paradox, dBase and MS-SQL. All these drivers do NOT return SQL_NO_DATA in that case, but return SQL_SUCCESS_WITH_INFO instead.

According to my old printed ODBC 2.0 Manual, SqlExecute should return SQL_SUCCESS_WITH_INFO in that case - with SQLSTATE 01S03 (page 299).

After further investigation, i found this in MSDN library:

http://msdn.microsoft.com/library/en-us/odbc/htm/odbcsql_no_data.asp

The behaviour depends on whether the application and/or driver uses ODBC 2.x or 3.x.

I assume, the Bugfix ( ODBC5 ) needs to be improved, however right now i cant give the exact condition the driver should check to decide when to behave like ODBC 2x or 3.x ...

Commented by: faridz (faridz)

Driver should return SQL_NO_DATA_FOUND when no rows are affected by the delete or update statement. All major DBMS ODBC drivers behave this way including MS SQL Server, Sybase, DB2, Informix and Oracle. And that's the correct return code per ODBC spec. Here's the pertinent paragraph from MS Doc for SQLExecDirect:

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.

Commented by: bill lam (b.lam)

IMO, to make it bug-compatible, it can offer an option during configuring the dsn in odbc admin.

if it fears too many option will be added in the future, it may be implemented as a bit mask, eg.

option= 1 return success_with_info for no row affected
option= 2 return SQL_CHAR for SQL_WCHAR in sqldescribecol
option= 4 (other)

Commented by: millka (Martin Mueller) (millka)

Hi faridz,

you are only correct if the Application uses ODBC 3.x mode. However if the Application uses ODBC 2.x mode, returning SQL_NO_DATA is simply wrong.

Firebirds ODBC driver has to return SQL_NO_DATA in 3.x mode, but SQL_SUCCESS_WITH_INFO in 2.x mode, like every other ODBC 2.x or 3.x driver, including MS-SQL-Server.

An ODBC driver has to obey the full ODBC spec, not just single sentences on single pages.

Please read: http://msdn.microsoft.com/library/en-us/odbc/htm/odbcsql_no_data.asp

and: http://msdn.microsoft.com/library/en-us/odbc/htm/odbcbehavioral_changes.asp

and the whole section: http://msdn.microsoft.com/library/en-us/odbc/htm/odbcbackward_compatibility_and_standards_compliance.asp

and especially this: http://msdn.microsoft.com/library/en-us/odbc/htm/odbctypes_of_drivers.asp

That last topic clearly states, that an ODBC 3.x driver has to support 2.x mode by request (SQL_ATTR_APP_ODBC_VERSION environment attribute).

Unfortunately that means that the driver has to handle both modes.

I dont know for sure if ADO uses ODBC 2.x mode instead of 3.x mode, but the behaviour irfan karakas reported in the first post of this bug report can only be explained if ADO uses 2.x mode. I am sure Firebird does not want to ignore ADO ...

Tip to Vladimir Tsvigun: It might be easier (= less work) to reach full ODBC 2.x compliance first and worry about ODBC 3.0 and 3.5 compliance later ..

ODBC 3.x compliant applications (or layers like ADO) are expected to handle ODBC 2.x drivers and ODBC 3.x drivers and their behavioural differences.

Commented by: Vladimir Tsvigun (praktik)

Hi All!

Please, see:

1)
if ( connection->env->useAppOdbcVersion == SQL_OV_ODBC3 )
return SQL_NO_DATA;
else
{
postError( "01001", "Cursor operation conflict" );
return SQL_SUCCESS_WITH_INFO;
}

You accept such variant
or such

2)
if ( connection->env->useAppOdbcVersion == SQL_OV_ODBC3 )
return SQL_NO_DATA;
else
return SQL_SUCCESS;

--
Best regards,
Vladimir Tsvigun

Commented by: Vladimir Tsvigun (praktik)

Hi Bill,

> option= 2 return SQL_CHAR for SQL_WCHAR in sqldescribecol

Please, specify.
??? ???????? ???????, ??? ??? ???????????

Commented by: Vladimir Tsvigun (praktik)

Hi Bill,

> option= 2 return SQL_CHAR for SQL_WCHAR in sqldescribecol

Please, specify.
It is a real position, it is necessary for you?

Commented by: millka (Martin Mueller) (millka)

Hi Vladimir,

I looked around in the driver source, searching for "AppOdbcVersion" ..
Your first suggestion looks very good:

if (connection->env->useAppOdbcVersion == SQL_OV_ODBC3)
return SQL_NO_DATA;
else {
postError ("01001", "Cursor operation conflict");
return SQL_SUCCESS_WITH_INFO;
}

but please check the parameters of postError ():

In ODBC 2.0, an update/delete without db change results in SQLSTATE 01S03. According to the printed 2.0 manual, SQLSTATE 01S03 means:
- Error: "No rows updated or deleted."
- Description: "The prepared statement associated with the hstmt was a positioned update or delete statement and no rows were updated or deleted. (Function returns SQL_SUCCESS_WITH_INFO.)"

I dont know if that manual is available online, but maybe its in the MSDN Library archive CD/DVD.

According to todays MSDN Library: http://msdn.microsoft.com/library/en-us/odbc/htm/odbcsqlstate_mappings.asp ,
the 2.x state 01S03 gets mapped to 3.x state 01001, but i think that mapping should happen only in 3.x mode, because a 2.x application would expect 01S03 (and all the other old SQLSTATE values).

Maybe the driver should use all the old SQLSTATE values as parameter to postError (except in 3.x only functions), and inside postError, these old states would be mapped to the new states, but only if useAppOdbcVersion is SQL_OV_ODBC3 .. I guess that sounds like a lot of work ..

Because my own odbc-based library only checks the return code (SQL_SUCCESS_WITH_INFO) but not the SQLSTATE when deleting, my problem would be solved, but i dont know about other applications / layers in 2.x mode (e.g. ADO) ..

And theres even more ..
MSDN Lib: http://msdn.microsoft.com/library/en-us/odbc/htm/odbcbehavioral_changes.asp :
"ODBC 3.x applications should never call SQLAllocEnv. As a result, if the Driver Manager receives a call to SQLAllocEnv, it recognizes the application as an ODBC 2.x application."

.. which means setting the environment option is not the only way to switch between 2.x and 3.x mode. I think in the driver source i saw that useAppOdbcVersion gets initialized to v3, but i couldnt find any place where it gets set to v2 (e.g. when SqlAllocEnv gets called).

I would suggest to implement your suggested bugfix, then check if that solves not only my personal problem but the ADO problem too. Then wait if someone else has problems with the old versus new SQLSTATEs ..

And now i am only guessing: I think compliance to ODBC 2 is more import than version 3. If i remember correctly, when MS defined ODBC 3.0/3.5, other methods like ADO were already available. So i assume that only a minority of ODBC applications uses ODBC 3.0 - but maybe i am wrong ..

Commented by: Vladimir Tsvigun (praktik)

Hi All,
Well, CVS changed,
now set postError( "01S03", "No rows updated or deleted" );

Commented by: Vladimir Tsvigun (praktik)

Hi Martin,
> And theres even more ..
> MSDN Lib: http://msdn.microsoft.com/library/en-
> us/odbc/htm/odbcbehavioral_changes.asp :
> "ODBC 3.x applications should never call SQLAllocEnv. As a result, if the Driver
> Manager receives a call to SQLAllocEnv, it recognizes the application as an
> ODBC 2.x application."
>
> .. which means setting the environment option is not the only way to switch
> between 2.x and 3.x mode. I think in the driver source i saw that
> useAppOdbcVersion gets initialized to v3, but i couldnt find any place where it
> gets set to v2 (e.g. when SqlAllocEnv gets called).

 I assume that it should be done with Driver Manager,

after call SqlAllocEnv execute autocall SQLSetEnvAttr (SQL_OV_ODBC2)