Question Re: ODBC driver in Technology Q&A

ODBC driver

former_SQLA_member1694921 — Thu, 05 Aug 2021 04:09:46 GMT

When using ODBC driver from ASA version 17, SQL requests with errors in data doesn't set some error indication ( sqlcode and sqlstate are both 0 ), so client gets just some part of data without warning about error. Drivers from previous versions ( tested ASA 12 and 16 ) sets error code.

ISQL also shows error, but it doesn't use ODBC, so I suspect something in ODBC driver.

Client is Powerbuilder, same behavior for all latest versions. Also tested on different engines ( 12,16,17 ), no differences here.

Errors are usually dividing by zero or subselect which returns more than one row ...

Example with divide by zero error:

SELECT Row_Num as A,
       Mod(A,10) as B,
       A / B as C
  FROM sa_Rowgenerator( 1, 20 );

Or:

SELECT 10 / 0 as X FROM Dummy;

Is there any settings in connection parameters or other which applys to this behavior ?

Re: ODBC driver

VolkerBarth — Thu, 05 Aug 2021 06:10:28 GMT

Are the entries in the according ODBC DSNs different (besides the Driver, apparently), say for "SuppressWarnings"?

What 17 build do you use for the client?

Here's a list of the v17 bugfixes (currently up to 17.0.10.6285), there are several related to the ODBC driver but I'm not aware as they would relate to such a non-subtle issue...

Re: ODBC driver

Breck_Carter — Thu, 05 Aug 2021 08:00:38 GMT

Please show us the PowerScript code that makes the bad request and checks the result. This is necessary for reproducing the behavior.

Re: ODBC driver

former_SQLA_member1694921 — Thu, 05 Aug 2021 08:18:48 GMT

No special entries, I've tested several, but none helped. I've tryed 17.0.10.6285 ( our curren version ), but also several older, from 17.0.0 to 17.0.4, works the same Version 16 is OK.

Re: ODBC driver

former_SQLA_member1694921 — Thu, 05 Aug 2021 08:26:33 GMT

There is no special Powerbuilder code, just Retrieve() on Datawindow, generated from select statement above. This is just simple example, same behavior is in other datawindows as part of our application. No error events fired.

If I just change ODBC driver to ver 12 or 16, got error:

SQLCode: -853 Select Error: [Sybase][ODBC Driver][SQL Anywhere]Cursor not in a valid state

Re: ODBC driver

Breck_Carter — Thu, 05 Aug 2021 11:50:39 GMT

no special Powerbuilder code

Seriously?

...that's how you respond to a perfectly valid, perfectly polite request to "please show us the code"?

Re: ODBC driver

VolkerBarth — Thu, 05 Aug 2021 12:30:37 GMT

Well, "no special Powerbuilder code" fits "no special [DSN] entries", which together leads to "no special help provided".

Re: ODBC driver

former_SQLA_member1694921 — Fri, 06 Aug 2021 01:28:33 GMT

Why do you think so negative?

By no special [DSN] entries I meant just so, in DSN there's just necessary parameters:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\\SOFTWARE\\WOW6432Node\\ODBC\\ODBC.INI\\TestDSN]
"Driver"="C:\\\\PROGRA~1\\\\SQLANY~3\\\\Bin32\\\\dbodbc17.dll"
"DatabaseName"="TestDB"
"ServerName"="ASA"
"Integrated"="NO"
"CommLinks"="TCPIP{}"

As for Powerbuilder code, I don't know your knowledge about this, as this is SqlAnywhere forum. As it has IDE with support for executing SQL from it, the behaviour about ODBC drivers is repetable in IDE:

create new datawindow, from SQL, change from graphic mode to SQL
paste the above writen SQL ( SELECT Row_Num as A, ... )
finish creating datawindow
preview results

Got 10 records without error.

Changed ODBC driver to ASA16, preview returns error.

Re: ODBC driver

VolkerBarth — Fri, 06 Aug 2021 04:01:28 GMT

Thanks for sharing the ODBC entry. Do you adapt the DSN when switching drivers, or do you use different ones for each driver?

Does ODBC Tracing show any differences when using different drivers? Are the connection parameters of the according connections different?

Whait is the database server version, also 17.0.10.6285?

(Yes, I'm aware, no clue on my part, just hints how to diagnose this issue...)

Re: ODBC driver

former_SQLA_member1694921 — Fri, 06 Aug 2021 04:12:31 GMT

I have a DSN just for this testing ( TestDSN ), to switch I delete / create new DSN Tracing ... will try, yust need more time Database is the same version as driver ( 17.0.10.6285 ), tested also to older databases ( 12, 16 ) ... same behavior

Re: ODBC driver

former_SQLA_member1694921 — Fri, 06 Aug 2021 04:59:02 GMT

Trace analysis ( how to write new comment with formatting ? )

From SQLPrepareW ... SQLDescribeColW ... SQLColAttributesW up to SQLExecute is the same for both version ( except handles )

Differences begin with FETCH:

Version 12:

TestDB          38b0-2cb0   ENTER SQLExtendedFetch 
        HSTMT               0x042DB420
        UWORD                        1 <SQL_FETCH_NEXT>
        SQLLEN                     1
        SQLULEN *           0x0073CDE4
        UWORD *             0x038DD810

TestDB          38b0-2cb0   EXIT  SQLExtendedFetch  with return code -1 (SQL_ERROR)
        HSTMT               0x042DB420
        UWORD                        1 <SQL_FETCH_NEXT>
        SQLLEN                     1
        SQLULEN *           0x0073CDE4
        UWORD *             0x038DD810

        DIAG [24000] [Sybase][ODBC Driver][SQL Anywhere]Cursor not in a valid state (-853)

        DIAG [22012] [Sybase][ODBC Driver][SQL Anywhere]Division by zero (-628)

TestDB          38b0-2cb0   ENTER SQLFreeStmt 
    HSTMT               0x042DB420
    UWORD                        0 <SQL_CLOSE>

Version 17:

TestDB          10c0-2910   ENTER SQLExtendedFetch 
        HSTMT               0x04D9A898
        UWORD                        1 <SQL_FETCH_NEXT>
        SQLLEN                     1
        SQLULEN *           0x012FCBE4
        UWORD *             0x0443D810

TestDB          10c0-2910   EXIT  SQLExtendedFetch  with return code 1 (SQL_SUCCESS_WITH_INFO)
        HSTMT               0x04D9A898
        UWORD                        1 <SQL_FETCH_NEXT>
        SQLLEN                     1
        SQLULEN *           0x012FCBE4 (10)
        UWORD *             0x0443D810 (0)

        DIAG [22012] [SAP][ODBC Driver][SQL Anywhere]Division by zero (-628)

TestDB          10c0-2910   ENTER SQLFreeStmt 
        HSTMT               0x04D9A898
        UWORD               0 <SQL_CLOSE>

While Ver12 returns SQL_ERROR, ver 17 returns SQL_SUCCESS_WITH_INFO

Is there any settings to control this ?

Re: ODBC driver

VolkerBarth — Fri, 06 Aug 2021 05:53:10 GMT

Hm, puzzled. I'm no PowerBuilder user at all, so I can't tell about that – other to ask to check whether there are different setup settings within PB (PB.INI?) for different SQL Anywhere versions?

Does "call sa_conn_properties()" reveal differences when run within BP for both drivers?

V17 introduced "client-side plan caching" and changed the behaviour w.r.t. to "auto_commit" option for v17 servers - but I would not expect those to have impact here...

Re: ODBC driver

former_SQLA_member1694921 — Fri, 06 Aug 2021 08:26:59 GMT

PB uses only ODBC connection for SQL Anywhere, no native drivers. It has some settings ( pbodb.ini ), but no setting for error status sa_conn_properties() shows only difference in statistics, like bytes read and such, but no others.

I can ask on Appeon forum, but as this issue is between versions of driver of one particular database ... looks like something to live with.

Re: ODBC driver

VolkerBarth — Fri, 06 Aug 2021 09:14:07 GMT

Trace analysis ( how to write new comment with formatting ? )

Within comments, you can put you code in a "pre"-tag pair.

Re: ODBC driver

VolkerBarth — Fri, 06 Aug 2021 09:17:26 GMT

Does the behavour change when you modify the connection's temporary divide_by_zero_error option setting ("On" by default)?

Re: ODBC driver

Breck_Carter — Fri, 06 Aug 2021 09:57:46 GMT

Confirmed: Testing shows the PowerBuilder 11.5.2506 DataWindow facility sometimes does not diagnose divide-by-zero exceptions properly when a faulty SELECT is run via ODBC on the 64-bit SQL Anywhere Network Server Version 17.0.9.4882.

This can affect both the Preview window in the PowerBuilder DataWindow Painter, and the DataWindow Retrieve() function at run time.

Here are some faulty SELECTs that work / don't work as expected in the Preview window...

-- Displays error as expected

SELECT REPEAT ( 'x', 255 ) AS x, 
       1 / dummy_col AS y 
  FROM DUMMY

-- Displays garbled data, no error

SELECT Row_Num as A,
       Mod(A,10) as B,
       A / B as C
  FROM sa_Rowgenerator( 10, 20 )

-- Displays error as expected

SELECT REPEAT ( 'x', 255 ) AS x, 
       1 / Mod ( Row_Num, 10 ) AS y
  FROM sa_Rowgenerator( 10, 20 )

-- Displays error as expected

SELECT REPEAT ( 'x', 255 ) AS x, 
       Row_Num as A,
       Mod(A,10) as B,
       A / B as C
  FROM sa_Rowgenerator( 10, 20 )

-- Displays garbled data, no error

SELECT @@VERSION, 
       Row_Num as A,
       Mod(A,10) as B,
       A / B as C
  FROM sa_Rowgenerator( 10, 20 )

Re: ODBC driver

Breck_Carter — Fri, 06 Aug 2021 10:18:39 GMT

TestDB          10c0-2910   EXIT  SQLExtendedFetch  with return code 1 (SQL_SUCCESS_WITH_INFO)
        HSTMT               0x04D9A898
        UWORD                        1 <sql_fetch_next>
        SQLLEN                     1
        SQLULEN *           0x012FCBE4 (10)
        UWORD *             0x0443D810 (0)

        DIAG [22012] [SAP][ODBC Driver][SQL Anywhere]Division by zero (-628)

This may be a behavior change for SQL Anywhere; i.e., sometimes returning SQL_SUCCESS_WITH_INFO instead of SQL_ERROR.

Evidence for this supposition comes in the following (unrelated) bug fix, where it says "...returning SQL_ERROR deviates from the ODBC standard which requires that SQL_SUCCESS_WITH_INFO be returned...".

In other words, maybe your problem was caused when a similar "deviation" was "fixed" 🙂

    ================(Build #4784  - Engineering Case #813650)================

    If an error occurs when inserting a batch of rows with the SQL Anywhere ODBC 
    driver (a wide insert), then the driver drops into single row insert mode. 
    If this results in all rows being inserted correctly, then the ODBC driver 
    should return SQL_SUCCESS, not SQL_ERROR. This problem has been fixed. The 
    ODBC driver will return SQL_SUCCESS if all rows are inserted without error 
    and SQL_ERROR if one or more rows fail insertion.

    Note that returning SQL_ERROR deviates from the ODBC standard which requires 
    that SQL_SUCCESS_WITH_INFO be returned if some rows are successfully inserted.

The next question is, how do we get PowerBuilder to "see" the DIAG [22012] [SAP][ODBC Driver][SQL Anywhere]Division by zero (-628)?

Re: ODBC driver

VolkerBarth — Fri, 06 Aug 2021 10:40:34 GMT

Very wild guess: Setting "SuppressWarnings" in the ODBC DSN or connection parameter might make a difference - or not - or not a desired difference...

A test with "PrefetchOnOpen" might also be worthwhile.

Re: ODBC driver

Breck_Carter — Fri, 06 Aug 2021 10:52:55 GMT

Try using the SQL Anywhere 16 ODBC driver instead of SQL Anywhere 17.

( there is no planet on which SQL_SUCCESS_WITH_INFO makes sense for divide by zero 🙂

SQLCA.DBMS = 'ODB'
SQLCA.DBParm &
   = "ConnectString='Driver=SQL Anywhere 16;UID=dba;PWD=sql;ENG=inventory17_xps;DBN=inventory17;'," &
   + "ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT'"
CONNECT USING SQLCA;
IF SQLCA.SQLCODE <> 0 THEN
    MessageBox ( 'Error', &
        'CONNECT 1 failed in open:' &
        + '~r~nSQLCode = ' &
        + String ( SQLCA.SQLCode ) &
        + '~r~nSQLDBCode = ' &
        + String ( SQLCA.SQLDBCode ) &
        + '~r~n' &
        + SQLCA.SQLErrText )
    RETURN
END IF

Re: ODBC driver

former_SQLA_member1694921 — Fri, 06 Aug 2021 13:27:14 GMT

Using older drivers is not an option, most of our clients have license for ver 17 ...
divide_by_zero_error , SuppressWarnings, PrefetchOnOpen ... no difference

( this is not limited to division by zero, but any errors related to processing rows, like casting errors, subselect which returns more than one row and similar errors )

Like:

SELECT Row_Num as A,
       if A=10 then cast('A' as integer) endif as B
  FROM sa_Rowgenerator( 1, 20 );

This are just examples to demonstrate the problem. In reality we write SQL which avoids such errors, related to data, by testing '<> 0' when dividing or using SELECT FIRST on subselects and testing data before casting. But with many statements sometimes something slips or is not so obvious, and in this cases I want to see and respond to error, not just get some rows with no indication that something went wrong.