ODBC driver

[VolkerBarth]

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 ?

[VolkerBarth]

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...

[Breck_Carter]

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

[former_SQLA_member1694921]

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.

[former_SQLA_member1694921]

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

[Breck_Carter]

no special Powerbuilder code

Seriously?

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

[VolkerBarth]

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

[Breck_Carter]

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 )

[Breck_Carter]

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

[former_SQLA_member1694921]

• 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.

[Breck_Carter]

What version and build of PowerBuilder are you using?

Do you know if any different version(s)/build(s) of PB behave differently with SQL Anywhere 17?

[Breck_Carter]

> our clients have license for ver 17

You should contact SAP tech support. Most folks on this forum are non-SAP volunteers with no access to the internal workings of SQL Anywhere.

[former_SQLA_member1694921]

We have latest version, 2019 R3. I tested some versions from 2017 ... no difference.

[former_SQLA_member1694921]

Yes, I know. I'll try at SAP and Appeon. My resume is that both are involved in this, SAP has changed return status from fetch call on error, Appeon ( Powerbuilder ) has not correctly tested this status. SAP ISQL and some other SQL clients I tested reported error on this example. We'll see.

Thanks for help.