Passing UUID() to MySQL

Ignition
1

Hello,

I would like to pass the UUID() to MySQL in a query to automatically create GUID's during insert queries. I've tried it like this

sql = 'INSERT INTO tblMapGraphics(uidClusterId, iBlockNum, iRowNum, iColNum) VALUES (? , ?, ?, ?)'
			args = 				  [uuid(), 	    BlockId,	 row, 		col]
			
			system.db.runPrepUpdate(sql, args,db)

UUID() is not a define variable, so that throws an error. If I enclose the function in quotes, then MySQL interprets it as a string and does not create the GUID.

Can anyone help me with the proper syntax?

2

if UUID() is a function in MySQL, then you need to include it as a plain string in your query I think.

So instead of a "?" for your first entry in VALUES, try directly putting in UUID()

3 Likes
3

Or, if you need the uuid later, I would create this in the script with str(java.util.System.UUID.randomUUID())

1 Like
4

Hi Richard. This suggestion works perfectly. Thanks for the advice. I thought my only option might be to write stored procedures for everything. This is much better for my application.

Thanks!

Eric

5

Note that many databases cannot return UUIDs that are auto-generated by your database (but see the exception here). But the whole point of UUIDs is that they can be generated at any point with extreme confidence of no collisions. That means if you need the UUID later in your code, you should generate it with java as Nick shows, and hang onto it.

(If your DB supports UUID columns, don't stringify it.)

2 Likes
6

Thanks Phil. I'm not too deep into this yet. I'll test it tonight and rework if this is a problem for me.

Eric