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:

from tb1 p 
where p.u like '%16409700%'

To reproduce the error use the following script:

insert into tb1

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

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"/>
        <table name="tb1" sync="changes">
                <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"/>
                <primarycolumn name="a" direction="asc"/>

asked 18 Feb '13, 07:59

Gabriel%20Francischini's gravatar image

Gabriel Fran...
accept rate: 0%

edited 19 Feb '13, 11:02

Graham%20Hurst's gravatar image

Graham Hurst

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

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

Notwithstanding the scary King Kong picture :-)

permanent link

answered 11 Mar '13, 16:16

Andy%20Quick's gravatar image

Andy Quick
accept rate: 45%

Comment Text Removed


(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



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:


question asked: 18 Feb '13, 07:59

question was seen: 6,083 times

last updated: 12 Mar '13, 07:47