|
Hello, I'm experiencing a unexpected result when executing a query statement in ultralite. The problem is that the interactive sql is throwing the error "[UltraLite Database] Cannot convert 16409700 to a varchar SQLCODE=-157, ODBC 3 State="07006" when I execute the following command with a specific dataset in my database: SELECT p.u from tb1 p where p.u like '%16409700%' To reproduce the error use the following script: insert into tb1 values(1,'a','b','c',4,1,17,2,4,17,'','','','','d','e',12,'f','g',5,16409700,'','h') SELECT p.u from tb1 p where p.u like '%16409700%' //ERROR - [UltraLite Database] Cannot convert 16409700 to a varchar //SQLCODE=-157, ODBC 3 State="07006" // clear all truncate table tb1 insert into tb1 values(2,'a','b','c',4,1,17,2,4,17,'','','','','d','e',12,'f','g',5,1,'','h') SELECT p.u from tb1 p where p.u like '%1%' // OK - no error Database schema: <?xml version="1.0" encoding="utf-8" standalone="no"?> <ul:ulschema xmlns:ul="urn:ultralite"> <collation name="1252LATIN1" case_sensitive="no" utf8="yes"/> <tables> <table name="tb1" sync="changes"> <columns> <column name="a" type="integer" null="no"/> <column name="b" type="varchar(40)" null="yes"/> <column name="c" type="varchar(80)" null="yes"/> <column name="d" type="varchar(240)" null="yes"/> <column name="e" type="smallint" null="yes"/> <column name="f" type="smallint" null="yes"/> <column name="g" type="integer" null="yes"/> <column name="h" type="integer" null="yes"/> <column name="i" type="integer" null="yes"/> <column name="j" type="integer" null="yes"/> <column name="k" type="integer" null="yes"/> <column name="l" type="integer" null="yes"/> <column name="m" type="integer" null="yes"/> <column name="n" type="integer" null="yes"/> <column name="o" type="varchar(100)" null="yes"/> <column name="p" type="varchar(100)" null="yes"/> <column name="q" type="smallint" null="yes"/> <column name="r" type="varchar(10)" null="yes"/> <column name="s" type="varchar(10)" null="yes"/> <column name="t" type="smallint" null="yes"/> <column name="u" type="integer" null="yes"/> <column name="v" type="integer" null="yes"/> <column name="x" type="long varchar" null="yes"/> </columns> <primarykey> <primarycolumn name="a" direction="asc"/> </primarykey> <indexes/> </table> </tables> </ul:ulschema> |
|
This is fixed as engineering case CR 734471, SQL Anywhere version 12.0.1.3870 or later. Notwithstanding the scary King Kong picture :-) Comment Text Removed
(11 Mar '13, 17:06)
Breck Carter
Thanks for the solution.
(12 Mar '13, 07:28)
Gabriel Fran...
|
Are you sure it isn't the INSERT that is causing the problem?
Is tb1.u the third-last column in tb1, and if so, is it declared as VARCHAR with a maximum length less than 8? If so, then 16409700 won't fit after conversion to '16409700'.
Hmm, according to the table declaration, column "u" is of type integer, which should surely accept 16409700 as value.
Therefore I guess the problem may be due to a conversion error of the LIKE predicate's operands.
@Gabriel: What build number are you using?
Try CAST...
Well, for SQL Anywhere queries (not Ultralite), a cast should not be necessary, as the docs state tat both operands are converted to CHAR/NCHAR automatically for LIKE search conditions:
Well, something is necessary :)
There is an issue with UL in handling the implicit cast. As a workaround, cast the value as suggested by Breck will workaround this issue.
im in ur base killin ur d00dz!