Manually: INSERT INTO ... UUID() ... (Native SQL)

Support
#1

To populate the database with test data, I created a number of insert statements which I run manually.
It sets id = uuid(), version = 1, created_ts = now(), created_by = 'admin' (update, delete = null) and some of the data feilds. Then Commit;

I can see the inserted datasets in tables and dropdowns, but…

If I want to edit the manually inserted datasets, I get “Access denied”

It I want to edit an existing entity and change a reference to one of the manually inserted datasets, it gives me a “Object "Table1" was modified in another transaction”.

If I create a new entity and want to set a reference to one of the manually created datasets, it gives me this error: “IllegalStateException: During synchronization a new object was found through a relationship that was not marked cascade PERSIST: com.company.basicdemo.entity.Table1-817444f2-8174-44f2-326d-b3a1326db3a1 [detached].”

Everything works fine, for entities created in the web-interface.
And of course, I’m logged in as admin.
I’m using HSQLDB.

Looking at the database the rows look exactly like the ones created using the web-interface except of the UUID, which has no dashes, if inserted manually. I guess this is the problem.

What is the correct way to create a valid UUID when using native SQL for INSERT?

#2

Now I switched the project to MySQL just to try the same thing… and this is what I found:

HSQLDB:
DDL by CUBA: ID varchar(36)
UUID pattern by CUBA: 8-4-4-4-12
UUID pattern by HSQLDB: 32

MySQL (5.6.21, didn’t update it just for this test):
DDL by CUBA: ID varchar(32)
UUID pattern by CUBA: 32
UUID pattern by MySQL: 8-4-4-4-12

Clearly, insert statement on MySQL using UUID() causes truncation…

So I tried replace(uuid(), '-', '') and guess what… everything works.

Probably, some badass substring-concat-combination might do the trick for HSQLDB to get the hyphens at the right place and then it should work as well… but this must be a joke. However, will try tomorrow.

#3

Hi,

Every database created by CUBA init scripts contains the newid() function that returns a suitable UUID.
In fact, for MySQL it is defined simply as

create function newid() returns varchar(32) not deterministic no sql
return replace(uuid(), '-', '')^

So if you use SQL like insert into SOME_TABLE(ID, VERSION, ...) values (newid(), 0, ...) it will work for all supported databases.

1 Like
#4

Many thanks! It works.
Is this somewhere documented? At least I couldnt’t find that information.

#5

What are the benefits of replacing dashes on UUID in MySQL, I couldn’t catch the clue …
In my case I’m only facing troubles while trying to use native queries…
I tried following hints on DbTypeConverter - Платформа CUBA. Руководство по разработке приложений, and couple of other instructions but didn’t help…

Now I’m facing the reverse case of this issue…
I am using MySql in my project.
When I try to get UUID id through native query, and try to get reference through datamanager by UUID I’m having trouble converting from String uuid value to UUID…
What I get: aa66864883dd2da5aa029ab20eb60d29
Expected: aa668648-83dd-2da5-aa02-9ab20eb60d29

I opened a new issue fot it, because couldn’t find any relative solution or issue.

Any help appreciated… Many thanks!