Unexpected lack-of-error when autoconverting illegal string to uniqueidentifier via C API?

I'm having a problem where a single development machine has stopped receiving SQL errors when it attempts to use an illegally formatted string as a uniqueidentifier (as part of a few different unit tests). The query is this:

SELECT * FROM MyTable WHERE Id = 'this is not a legally formatted identifier'

(where the 'Id' column is of type UNIQUEIDENTIFIER)

When this query is executed against a database from InteractiveSQL, or via the C API on any of our other development PCs, this generates a -157 error: "Cannot convert 'this is not a legally formatted identifier' to a uniqueidentifier", as we expect. On this one development machine, though, no error is generated when the query is issued via the C API, and the query appears to be processed as if it had been able to successfully convert it into a uniqueidentifier -- it simply returns no result rows. (executing the query via InteractiveSQL on this problematic machine generates the error as expected)

The only thing I'm aware of which makes this development machine different from the others is that I upgraded it to use SQLAnywhere 12.0.1.3853, whereas the other machines are running 12.0.1.3798. This behaviour remains the same, though, when I downgrade back to 12.0.1.3798.

Is there perhaps a local setting or configuration file that could be causing this unexpected lack-of-error only when executing the query through the C API, and only on this one computer? I'm just trying to work out why I'd be seeing different behaviour on this single Mac computer than on any of the other Macs and PCs.

data-conversion uniqueidentifier error

asked 13 Mar '13, 20:32

Trevor Powell
81●2●2●8
accept rate: 0%

edited 15 Mar '13, 22:15

Mark Culp
24.9k●10●139●297

I don't have 12.0.1.3853 installed at the moment, but I can confirm that 12.0.1.3298 and 16.0.0.1324 behave as expected: "If the data type of any argument is UNIQUEIDENTIFIER, convert to UNIQUEIDENTIFIER and compare." according to http://dcx.sybase.com/index.html#1201/en/dbreference/datatypes-s-5424762.html ...and therefore raises an error...

Cannot convert 'this is not a legally formatted id...' to a uniqueidentifier SQLCODE=-157, ODBC 3 State="07006"

However, it probably has nothing to do with the API or any configuration file, because the comparison and the resulting exception is the exclusive responsibility of the server. Comparison rules are baked-in, so to speak, not subject to the whims and vagaries of an unstable environment or a steenking client (no offense intended, some of my best friends are clients :)

So... sounds like a bug to me, given your testimony.

(14 Mar '13, 09:06) Breck Carter

Replies hidden

So... sounds like a bug to me, given your testimony.

However, when the database engine is downgraded to 12.0.1.3798, then the behaviour should be OK again, right? - And according to the OP, it is not. (All under the assumption the database engine is running locally...)

FWIW, with 12.0.1.3817, the following simple test via DBISQL raises -157 as expected, too:

SELECT cast('this is not a legally formatted identifier' as UNIQUEIDENTIFIER)

(14 Mar '13, 09:43) Volker Barth

@Volker: That doesn't exactly test tpowell's case, which was an implicit conversion that apparently doesn't get attempted. Instead, try this...

BEGIN
DECLARE @id UNIQUEIDENTIFIER;
SET @id = NEWID();
SELECT @@VERSION;
SELECT *
  FROM dummy
 WHERE @id = 'this is not a legally formatted identifier';
END;

(14 Mar '13, 09:47) Breck Carter

Thanks for the correction. With 12.0.1.3818, it raises -157, too:) - But then again, tested with DBISQL, not with the C API...

(14 Mar '13, 10:03) Volker Barth

@VolkerBarth Yes, the database engine is running locally, in all tests. The bad behaviour is limited to the one machine which had been upgraded to 12.0.1.3853, and persisted after being downgraded again, and only via the C API; every other interface I've tried throws the errors that one would expect. I haven't yet tested a clean install of SQLAnywhere, but that'll be my next step. The simple test you mention executes (via the C API) without throwing an error on all our development machines -- both upgraded and non-upgraded ones -- but returns no rows. It does throw an error under InteractiveSQL on all machines, under both versions.

(14 Mar '13, 19:30) Trevor Powell

Clean install of 12.0.1.3798 results in the error being thrown as expected. A clean install of 12.0.1.3853 does not throw the error, and also does not return any values (again, only via the C API). An in-place downgrade from 12.0.1.3853 to 12.0.1.3798 causes the C API to continue to not throw an error. Neither version 3798 nor 3853 nor 3853-downgraded-to-3798 give an error or a result from the 'SELECT cast' statement suggested by @volker, when issued via the C API. To me, this is starting to feel like it might be a bug in the C API, introduced between 3798 and 3853.. but I can't explain why it would persist after the in-place downgrade from 3853 to 3798.

(14 Mar '13, 20:25) Trevor Powell

I can't seem to reproduce this via the C API on my Windows machine with 12.0.1.3853 (I unfortunately do not have a Macintosh handy to test this on).

With capi.sql:

BEGIN
DECLARE @id UNIQUEIDENTIFIER;
SET @id = NEWID();
SELECT @@VERSION;
SELECT *
  FROM dummy
 WHERE @id = 'this is not a legally formatted identifier';
END;

(and assuming you have Visual Studio set up:)

cd "%SQLANY12%\sdk\dbcapi\examples"
build
dbinit capi.db
dbeng12 capi.db
dbcapi_isql.exe -c "uid=dba;pwd=sql;eng=capi"

Connected successfully!

dbcapi_isql.exe> read capi.sql
Estimated number of rows: 1
@@VERSION
---------
'12.0.1.3853'
---------
1 rows returned

Estimated number of rows: -1
dummy_col
---------
---------
0 rows returned
Failed: [-157] 'Cannot convert 'this is not a legally formatted id...' to a uniqueidentifier'

Warning: [105] 'Procedure has completed'
Total elapsed time = 5929ms

dbcapi_isql.exe>

(14 Mar '13, 21:36) Jeff Albion

showing 1 of 7 show all flat view

Be the first one to answer this question!

toggle preview

community wiki:

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

learn more about Markdown

Question tags:

error ×93
data-conversion ×17
uniqueidentifier ×7

question asked: 13 Mar '13, 20:32

question was seen: 1,887 times

last updated: 15 Mar '13, 22:15

SAP SQL Anywhere

Unexpected lack-of-error when autoconverting illegal string to uniqueidentifier via C API?

Follow this question

Related questions