|
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. |
|
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) |
|
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' |
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.)
The column is UNIQUEIDENTIFIER and the query doesn't work with or without hyphens
What about my question? What does the query return?
I would expect that:
SELECT * FROM myTable WHERE id='ca9ae8ad20404890bca5c93b54770516'
should work
Can you provide a version number for SQLA
BTW: Is this somewhat related to this question - which seems not to be fully answered, as well?
Yes, the problem is similar.
it'd work if id is VARCHAR, but id is UNIQUEIDENTIFIER
The query
return UNIQUEIDENTIFIER values
What version and build of the software are you running?
Sybase Central 6.1.0.6420 and I use plug-in MobiLink12 12.0.1.3152
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?
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:
It returned the error:
otherwise, if I use the query (with id like a VARCHAR):
the result is a empty row.
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
yield (i.e. omitting the hyphens)?
Or filtering on a queried id value like this
I already executed both queries, but the result is the same: empty row.
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?
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.)?
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.
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.
I tried to execute again the query as PhilippeBertrand advised me, and now it work. The right query is
I don't know why yesterday didn't work.
Thank you very much,
G.
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).