CODEWITHSUNDEEP
sqlh2uuid
Katan Gang
Katan Gang

Reputation: 21

How can i insert a specific uuid into h2 database using sql script

I am using a h2 database to run my tests and i need to insert some default values in the database prior to testing. I'm trying to write an sql script for this however, i am having an issue inserting a uuid as a value into the relevant table.

I tried inserting the uuid as follows; insert into user_profile_table (profile_id, user_id) values (1, 'c80d54d3-500d-4539-9479-8e8961477193'); however i get an JdbcSQLDataException error stating: Value too long for column.

I assumed this is because the user_id column type is BINARY(16) so i decided to try the following query insert into user_profile_table (profile_id, userkeycloak_id) values (1, UNHEX(REPLACE('c80d54d3-500d-4539-9479-8e8961477193', '-',''))); however, then i get an error stating: JdbcSQLSyntaxErrorException: Function "UNHEX" not found; SQL statement. I read online that the h2 DB does not support the unhex function. I tried to look for others means of converting the the uuid to a format compatible with the h2 database but havent had any luck finding a viable solution.

Hence, my question is: is there any query i can use to insert the uuid (in a BINARY(16)) format into the h2 db table? Any suggestions would be highly appreciated.

Upvotes: 1

Views: 2065

Answers (2)

Evgenij Ryazanov
Evgenij Ryazanov

Reputation: 8178

To insert a UUID value into BINARY(16) column you can use standard binary string literals in both MySQL and H2:

insert into user_profile_table (profile_id, user_id)
values (1, X'c80d54d3500d453994798e8961477193');

Upvotes: 3

LagraaBTS
LagraaBTS

Reputation: 1

try this solution by microsoft : https://learn.microsoft.com/en-us/sql/t-sql/data-types/uniqueidentifier-transact-sql?view=sql-server-ver16

Upvotes: 0

Related Questions