UUIDValue into database

[Former Member]

Hello,

I have a UUIDValue column, and I have to execute a query where in the condition there's specified a UUIDValue value. The problem is that I don't know how to specify this value correctly. I tried to use the UUIDValue value like a string, writing it between quotes:

SELECT * FROM myTable WHERE id='ca9ae8ad-2040-4890-bca5-c93b54770516'

with poor results. Then I tried without quotes, without dashes, but nothing!

Have you got any idea?

Thanks in advance,

G.

[VolkerBarth]

How is the value of the UNIQUEIDENTIFIER column displayed, i.e. what is returned when you do the SELECT without a WHERE clause?

That being said, if the column is declared as UNIQUEIDENTIFIER, the string comparison with or without hyphens should work. (It won't work when id is some kind of (VAR)CHAR datatype.)

[Former Member]

The column is UNIQUEIDENTIFIER and the query doesn't work with or without hyphens

[VolkerBarth]

What about my question? What does the query return?

[MCMartin]

I would expect that:

SELECT * FROM myTable WHERE id='ca9ae8ad20404890bca5c93b54770516'

should work

Can you provide a version number for SQLA

[VolkerBarth]

BTW: Is this somewhat related to this question - which seems not to be fully answered, as well?

[Former Member]

Yes, the problem is similar.

[Former Member]

it'd work if id is VARCHAR, but id is UNIQUEIDENTIFIER

[Former Member]

The query

    SELECT id FROM myTable

return UNIQUEIDENTIFIER values

[Former Member]

What version and build of the software are you running?

[Former Member]

Sybase Central 6.1.0.6420 and I use plug-in MobiLink12 12.0.1.3152

[philippefbertrand]

Please describe more fully your usage scenario (steps to reproduce). Are you trying to write MobiLink synchronization scripts? Are you using dbisql launched from Sybase Central? Can you post the schema of the table?

What do you mean by "poor results"? (Wrong rows returned, error, bad plan, etc)

Have you tried to use a binary constant?

SELECT * FROM myTable WHERE id=0xca9ae8ad20404890bca5c93b54770516

[Former Member]

I want execute a query to update a value into database. There are some condition in this query, and one of this is id (UNIQUEIDENTIFIER). Within the application are already running many queries, so I don't think there are communication problem. The problem is the type of value that I put into the query condition. If I use the query:

 SELECT * FROM myTable WHERE id=ca9ae8ad-2040-4890-bca5-c93b54770516

It returned the error:

 SQLCODE=-143, ODBC 3 State="42S22"

otherwise, if I use the query (with id like a VARCHAR):

 SELECT * FROM myTable WHERE id='ca9ae8ad-2040-4890-bca5-c93b54770516'

the result is a empty row.

[VolkerBarth]

id=ca9ae8ad-2040-4890-bca5-c93b54770516

The -143 error clearly states that ca9ae8ad is treated as an identifier, and there's no such column (or global variable) here.

As Martin has stated, you have to enclose the UUID value in quotes as it is a string. So what does

SELECT * FROM myTable WHERE id='ca9ae8ad20404890bca5c93b54770516'

yield (i.e. omitting the hyphens)?

Or filtering on a queried id value like this

SELECT * FROM myTable WHERE id = (SELECT MAX(id) FROM myTable)

[Former Member]

I already executed both queries, but the result is the same: empty row.

[philippefbertrand]

Where are you executing this update? You stated you were using the MobiLink plugin. Is it a consolidated database (what kind of db then?) or is it a remote database (UltraLite, SQL Anywhere?) you are modifying?

[Former Member]

I have a *.udb file and I try to execute this query in 2 different ways: 1- From Android application. 2- From MobiLink connection in Sybase Central.

I can execute SELECT, INSERT and UPDATE query in all tables of database. The ONLY problem is when I put a UUIDValue value into a condition of a query. Please Could you kindly supply me the query I should use for both points (1. and 2.)?

[Former Member]

If I didn't make myself clear, please let me know? I'm a newbie in sqlAnywhere and this is quite urgert.

Thank you in advance,

G.

[VolkerBarth]

Have you tried to reproduce the steps from Jeff's answer?

If so (and this does work), then this doesn't seem to be a generel SQL problem but possibly just related to the particular environment/API you use to query the database.

If so, it might be helpful if you could tell more about the environment, or if you could show parts of the application code that does run the failing query - e.g. the code using the UUIDValue() function, as you have stated.

[Former Member]

I tried to execute again the query as PhilippeBertrand advised me, and now it work. The right query is

SELECT * FROM myTable WHERE id='0xca9ae8ad20404890bca5c93b54770516'

I don't know why yesterday didn't work.

Thank you very much,

G.

[VolkerBarth]

Cf. my comment to this answer: You seem to specify the UUID as a binary literal, not as a string constant (which still should work as well).

[jeff_albion]

I just tried the following with 12.0.1.3423, on Windows x64, and it seemed to be successful:

> ulinit ultest.udb
> dbisql -ul -c "UID=dba;PWD=sql;UDB=ultest.udb"

SQL Code:

CREATE TABLE myTable(
  id uniqueidentifier primary key default newid()
);
INSERT INTO myTable;
SELECT * FROM myTable;
-- (Look at the results shown)
SELECT * FROM myTable WHERE id = '2E9D76B0-F327-4633-A4D8-10DAB9794B8F';
--    (1 Rows)

[VolkerBarth]

You could try the following query to find out whether string values are converted correctly to UNIQUEIDENTIFIER values:

select 1 from dummy
where Cast('ca9ae8ad-2040-4890-bca5-c93b54770516' as UNIQUEIDENTIFIER) =
   'ca9ae8ad20404890bca5c93b54770516'

That should yield 1 (and does with v12.0.1.3324). Trying with omitted/added hyphens should work as well:

select 1 from dummy
where Cast('ca9ae8ad20404890bca5c93b54770516' as UNIQUEIDENTIFIER) =
   'ca9ae8ad-2040-4890-bca5-c93b54770516'