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

[Former Member]

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.

[Breck_Carter]

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.

[VolkerBarth]

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)

[Breck_Carter]

@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;

[VolkerBarth]

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

[Former Member]

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

[Former Member]

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.

[jeff_albion]

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>