store uniqueidentifier - primary key

[Former Member]

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

[VolkerBarth]

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();

[Former Member]

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

[VolkerBarth]

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.

[MCMartin]

try the uuid without the dashes in your example:

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

[Former Member]

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

[VolkerBarth]

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.

[Former Member]

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

[VolkerBarth]

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

[Former Member]

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