Skip to Content
0

Warning to developers: SQL Anywhere and numeric datawindow bind variables

May 18, 2017 at 04:40 PM

36

avatar image
Former Member

I have been working with SAP SQL Anywhere support group to pin down why a simple SQL statement was not using the primary key index and is doing a full table scan. The primary key of the table is numeric(20,0). The datawindow sql statement is of the form select * from table where key=:an_key - where :an_key is declared as a numeric argument. If I create the same datawindow but the value passed in is a string then the index is used. After much going back and forth we have determined that the issue is that PowerBuilder passes in a numeric argument as a double. A double is considered by SQL Anywhere to be an approximate datatype. If I have a primary key that is numeric(20,0) then it will not use the primary index and will do a table scan whereas if the primary key is an integer then it will use the primary key! This seems to affect all versions of Sybase SQL Anywhere. I have reproduced this in PB6.5 and PB12.5

SQL
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers