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.

asked 20 Sep '11, 04:38

Giuseppe_devoteam's gravatar image

Giuseppe_dev...
1113
accept rate: 0%

edited 20 Sep '11, 12:51

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104

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

(20 Sep '11, 05:26) Volker Barth

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

(20 Sep '11, 05:38) Giuseppe_dev...
Replies hidden

What about my question? What does the query return?

(20 Sep '11, 06:18) Volker Barth

I would expect that:

SELECT * FROM myTable WHERE id='ca9ae8ad20404890bca5c93b54770516'

should work

Can you provide a version number for SQLA

(20 Sep '11, 06:33) Martin
Replies hidden

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

(20 Sep '11, 07:01) Volker Barth
Replies hidden

Yes, the problem is similar.

(20 Sep '11, 07:54) Giuseppe_dev...

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

(20 Sep '11, 07:56) Giuseppe_dev...

The query

    SELECT id FROM myTable

return UNIQUEIDENTIFIER values

(20 Sep '11, 08:00) Giuseppe_dev...

What version and build of the software are you running?

(20 Sep '11, 08:06) Glenn Paulley

Sybase Central 6.1.0.6420 and I use plug-in MobiLink12 12.0.1.3152

(20 Sep '11, 08:10) Giuseppe_dev...

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
(20 Sep '11, 08:37) PhilippeBert...

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.

(20 Sep '11, 09:11) Giuseppe_dev...

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)
(20 Sep '11, 09:24) Volker Barth

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

(20 Sep '11, 09:47) Giuseppe_dev...

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?

(20 Sep '11, 11:34) PhilippeBert...

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

(20 Sep '11, 11:58) Giuseppe_dev...
Comment Text Removed

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.

(21 Sep '11, 03:54) Giuseppe_dev...
Replies hidden

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.

(21 Sep '11, 08:02) Volker Barth

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.

(21 Sep '11, 09:58) Giuseppe_dev...

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

(21 Sep '11, 10:09) Volker Barth
showing 5 of 20 show all flat view

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)
permanent link

answered 20 Sep '11, 11:29

Jeff%20Albion's gravatar image

Jeff Albion
10.7k171174
accept rate: 24%

edited 20 Sep '11, 11:31

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'
permanent link

answered 20 Sep '11, 09:38

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674
accept rate: 32%

edited 20 Sep '11, 09:39

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:

×145
×103
×7

question asked: 20 Sep '11, 04:38

question was seen: 1,402 times

last updated: 21 Sep '11, 10:09