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

asked 09 Sep '11, 04:21

devdevoteam's gravatar image

devdevoteam
155510
accept rate: 0%

edited 09 Sep '11, 06:49

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819


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

answered 09 Sep '11, 11:40

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

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

(09 Sep '11, 12:18) devdevoteam

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.

permanent link

answered 09 Sep '11, 06:48

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

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

permanent link

answered 09 Sep '11, 11:03

devdevoteam's gravatar image

devdevoteam
155510
accept rate: 0%

try the uuid without the dashes in your example:

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

answered 12 Sep '11, 13:27

Martin's gravatar image

Martin
9.0k130169257
accept rate: 14%

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

(21 Sep '11, 05:24) devdevoteam
Replies hidden

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.

(21 Sep '11, 06:12) Volker Barth

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

(21 Sep '11, 09:48) devdevoteam
Replies hidden

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

(21 Sep '11, 10:06) Volker Barth
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:

×162
×78
×7

question asked: 09 Sep '11, 04:21

question was seen: 4,624 times

last updated: 21 Sep '11, 10:13