The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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>

asked 18 Feb '13, 07:59

Gabriel%20Francischini's gravatar image

Gabriel Fran...
31223
accept rate: 0%

edited 19 Feb '13, 11:02

Graham%20Hurst's gravatar image

Graham Hurst
2.7k11843

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

(18 Feb '13, 09:03) Breck Carter
Replies hidden

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?

(18 Feb '13, 09:44) Volker Barth

Try CAST...

SELECT p.u
from tb1 p 
where CAST ( p.u AS VARCHAR ( 20 ) ) like '%16409700%'
(18 Feb '13, 10:17) Breck Carter
Replies hidden

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:

expression [ NOT ] LIKE pattern [ ESCAPE escape-character ]

expression is interpreted as a CHAR or NCHAR string. [...] Similarly, pattern is interpreted as a CHAR or NCHAR string [...]

(18 Feb '13, 10:26) Volker Barth

Well, something is necessary :)

(18 Feb '13, 11:13) Breck Carter

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.

(22 Feb '13, 13:24) Chris Keating
Comment Text Removed
Comment Text Removed
Comment Text Removed

im in ur base killin ur d00dz!

alt text

(22 Feb '13, 15:50) Breck Carter
showing 3 of 7 show all flat view

This is fixed as engineering case CR 734471, SQL Anywhere version 12.0.1.3870 or later.

Notwithstanding the scary King Kong picture :-)

permanent link

answered 11 Mar '13, 16:16

Andy%20Quick's gravatar image

Andy Quick
2.2k2737
accept rate: 45%

Comment Text Removed

Boba

(11 Mar '13, 17:06) Breck Carter

Thanks for the solution.

(12 Mar '13, 07:28) Gabriel Fran...
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×145
×51
×39
×35
×5

question asked: 18 Feb '13, 07:59

question was seen: 3,633 times

last updated: 12 Mar '13, 07:47