Question Re: store uniqueidentifier - primary key in Technology Q&A

store uniqueidentifier - primary key

Former Member — Fri, 09 Sep 2011 04:21:49 GMT

My Android application is using ultralite DB. I should store a primary key (pk) into a java variable. pk datatype is uniqueidentifier; as I cannot use String neither long type Please what is the best way to store it?

Thank you very much in advance. S

Re: store uniqueidentifier - primary key

VolkerBarth — Fri, 09 Sep 2011 06:48:47 GMT

Just let me hint that there are some reasons not to use UUIDs as PKs (some of them are discussed in this forum, too) and that GLOBAL AUTOINCREMENT defaults (with datatype INT/BIGINT) might be an alternative...

From the docs:

UNIQUEIDENTIFIER values are stored as BINARY(16), but are described to client applications as BINARY(36). This description ensures that if the client fetches the value as a string, it has allocated enough space for the result.

And SQL type BINARY is automatically mapped to Java type byte[]. So that seems to be the preferred Java mapping.

Re: store uniqueidentifier - primary key

Former Member — Fri, 09 Sep 2011 11:03:11 GMT

Thank you for your kind reply. I did not create that db but I only have to connect to its and to execute query.

In this particular case, I have to get my pk <(uniqueidentifier datatype) and use it into another query.

My error is probably because I execute a query in such a way: SELECT * FROM mytable WHERE pk='5ad8bc00-2822-433f-88d1-2dd028e70b17';

would you kindly provide us an example?

Thanks in advance. S

Re: store uniqueidentifier - primary key

VolkerBarth — Fri, 09 Sep 2011 11:40:29 GMT

Sorry, I don't use UltraLite/UltraLiteJ so my chance to help is quite delimited...

What error (SQLCODE) are you getting?

Do you use an UUIDValue type or a string or byte[] to store the UUID?

You might try to use a prepared statement, here with a UUID string, such as (untested!):

// Create a new PreparedStatement object from an existing connection.
String sql_string = "SELECT * FROM myTable WHERE pk = ?";
PreparedStatement ps = conn.prepareStatement(sql_string);
ps.set(1, '5ad8bc00-2822-433f-88d1-2dd028e70b17');

// result returns true if the execute statement runs     successfully.
boolean result = ps.execute();

// Check if the PreparedStatement contains a ResultSet.
if (ps.hasResultSet()) {
     // Store the ResultSet in the rs variable.
     ResultSet rs = ps.getResultSet;
}
// Close the PreparedStatement to release resources.
ps.close();

Re: store uniqueidentifier - primary key

Former Member — Fri, 09 Sep 2011 12:18:59 GMT

Thank you very much, it does return a resultSet and I'll investigate further ASAP.

Re: store uniqueidentifier - primary key

MCMartin — Mon, 12 Sep 2011 13:27:19 GMT

try the uuid without the dashes in your example:

SELECT * FROM mytable WHERE pk='5ad8bc002822433f88d12dd028e70b17';

Re: store uniqueidentifier - primary key

Former Member — Wed, 21 Sep 2011 05:24:37 GMT

This is the error message I get: [UltraLite Database] Cannot convert 5ad8bc002822433f88d12dd028e70b17 to a uniqueidentifier SQLCODE=-157, ODBC 3 State="07006"

Re: store uniqueidentifier - primary key

VolkerBarth — Wed, 21 Sep 2011 06:12:45 GMT

What does the following return (cf. the other FAQ):

select cast('5ad8bc002822433f88d12dd028e70b17' as UNIQUEIDENTIFIER)

For SQL Anywher 12.0.1 (not Ultralite), this does return the expected value.

Re: store uniqueidentifier - primary key

Former Member — Wed, 21 Sep 2011 09:48:21 GMT

it seems to be solved placing before "0x" and with no "-". thank you very much. S

Re: store uniqueidentifier - primary key

VolkerBarth — Wed, 21 Sep 2011 10:06:53 GMT

That would mean you are using the UUID as a binary literal, not as a string constant. And then the 0x prefix is necessary. However, it should work to use it as a string constant, too.

Therefore we're glad that you have found a solution.

Nevertheless it would be interesting to find out why it hasn't worked for you the way so many here have suggested...