|
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 ? |
|
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:
Got 10 records without error. Changed ODBC driver to ASA16, preview returns error. 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...)
(06 Aug '21, 04:01)
Volker Barth
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
(06 Aug '21, 04:12)
Alex99
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 ?
(06 Aug '21, 04:59)
Alex99
Replies hidden
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...
(06 Aug '21, 05:53)
Volker Barth
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.
(06 Aug '21, 08:26)
Alex99
Within comments, you can put you code in a "pre"-tag pair.
(06 Aug '21, 09:14)
Volker Barth
Does the behavour change when you modify the connection's temporary divide_by_zero_error option setting ("On" by default)?
(06 Aug '21, 09:17)
Volker Barth
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)?
(06 Aug '21, 10:18)
Breck Carter
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.
(06 Aug '21, 10:40)
Volker Barth
|
|
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
1
( 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.
(06 Aug '21, 13:27)
Alex99
Replies hidden
Comment Text Removed
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?
(06 Aug '21, 15:23)
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.
(06 Aug '21, 15:58)
Breck Carter
We have latest version, 2019 R3. I tested some versions from 2017 ... no difference.
(07 Aug '21, 05:30)
Alex99
1
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.
(07 Aug '21, 05:40)
Alex99
|
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...
Please show us the PowerScript code that makes the bad request and checks the result. This is necessary for reproducing the behavior.
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.
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
Seriously?
...that's how you respond to a perfectly valid, perfectly polite request to "please show us the code"?
Well, "no special Powerbuilder code" fits "no special [DSN] entries", which together leads to "no special help provided".
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 )