Question Re: UUIDValue into database in Technology Q&A

UUIDValue into database

Former Member — Tue, 20 Sep 2011 04:38:38 GMT

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,

Re: UUIDValue into database

VolkerBarth — Tue, 20 Sep 2011 05:26:35 GMT

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

Re: UUIDValue into database

Former Member — Tue, 20 Sep 2011 05:38:24 GMT

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

Re: UUIDValue into database

VolkerBarth — Tue, 20 Sep 2011 06:18:54 GMT

What about my question? What does the query return?

Re: UUIDValue into database

MCMartin — Tue, 20 Sep 2011 06:33:49 GMT

I would expect that:

SELECT * FROM myTable WHERE id='ca9ae8ad20404890bca5c93b54770516'

should work

Can you provide a version number for SQLA

Re: UUIDValue into database

VolkerBarth — Tue, 20 Sep 2011 07:01:34 GMT

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

Re: UUIDValue into database

Former Member — Tue, 20 Sep 2011 07:54:53 GMT

Yes, the problem is similar.

Re: UUIDValue into database

Former Member — Tue, 20 Sep 2011 07:56:16 GMT

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

Re: UUIDValue into database

Former Member — Tue, 20 Sep 2011 08:00:32 GMT

The query

    SELECT id FROM myTable

return UNIQUEIDENTIFIER values

Re: UUIDValue into database

Former Member — Tue, 20 Sep 2011 08:06:03 GMT

What version and build of the software are you running?

Re: UUIDValue into database

Former Member — Tue, 20 Sep 2011 08:10:40 GMT

Sybase Central 6.1.0.6420 and I use plug-in MobiLink12 12.0.1.3152

Re: UUIDValue into database

philippefbertrand — Tue, 20 Sep 2011 08:37:49 GMT

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

Re: UUIDValue into database

Former Member — Tue, 20 Sep 2011 09:11:54 GMT

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.

Re: UUIDValue into database

VolkerBarth — Tue, 20 Sep 2011 09:24:27 GMT

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)

Re: UUIDValue into database

VolkerBarth — Tue, 20 Sep 2011 09:38:46 GMT

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'

Re: UUIDValue into database

Former Member — Tue, 20 Sep 2011 09:47:03 GMT

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

Re: UUIDValue into database

jeff_albion — Tue, 20 Sep 2011 11:29:24 GMT

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)

Re: UUIDValue into database

philippefbertrand — Tue, 20 Sep 2011 11:34:43 GMT

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?

Re: UUIDValue into database

Former Member — Tue, 20 Sep 2011 11:58:42 GMT

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

Re: UUIDValue into database

Former Member — Wed, 21 Sep 2011 03:54:47 GMT

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,