SELECT UUID() in HSQLDB

Question

Does anyone know how to retrieve UUID via HSQLDB.

For example when i try

SELECT UUID();

via MYSQL it works fine. But the same statement doesn't work with HSQLDB. The following methods achieve the corresponding purpose

VALUES (UUID())

CALL(UUID())

SELECT UUID() FROM (VALUES(0)) t;

Is there a way which is same for mysql and hsqldb? HSQL doc says that UUID has been activated. http://hsqldb.org/doc/guide/builtinfunctions-chapt.html

Thanks.

is there any hack where statement like SELECT UUID() FROM (VALUES(0)) would also be possible to work with mysql? — user2083529, Mar 26 '18 at 15:04
HSQLDB requires the `FROM [table]` part... i found this question https://stackoverflow.com/questions/909702/how-to-do-select-current-timestamp-in-hsqldb but is't with current_timestamp() but i think the same method can be used for UUID() — Raymond Nijland, Mar 26 '18 at 15:04
`SELECT UUID() FROM (SELECT 0) t` might work in both MySQL (know for sure it works in MySQL) and HSQLDB — Raymond Nijland, Mar 26 '18 at 15:07
tried it now, it does't work when i specify table name. SELECT UUID() FROM (SELECT 0) t works only with mysql — user2083529, Mar 26 '18 at 15:08
"Is there a way which is same for mysql and hsqldb? " see mine answer. — Raymond Nijland, Mar 26 '18 at 15:46

score 2 · Accepted Answer · answered Mar 26 '18 at 18:55

2

Turn on the MySQL compatibility mode in HSQLDB and it will allow your SELECT statement:

answered Mar 26 '18 at 18:55

fredt

score 1 · Answer 2 · answered Mar 26 '18 at 15:46

Is there a way which is same for mysql and hsqldb?

Only way i can think off.
Create a table DUAL in HSQLDB

CREATE TABLE DUAL (
  id INT
);

So you can use

SELECT UUID() FROM DUAL LIMIT 1;

Then the query should work the same in both MySQL and HSQLDB.
DUAL in MySQL is a non existing dummy table.

2 Answers2