The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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

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.1k2436
accept rate: 43%

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:

×128
×46
×34
×33
×5

question asked: 18 Feb '13, 07:59

question was seen: 3,424 times

last updated: 12 Mar '13, 07:47