B4J Question Problem using UUID parameter in PostgreSQL SQL statement

Diceman

Active Member
Licensed User
I have a PostgreSQL SQL statement like:

private SqlStr as String

B4X:
EmployeeId = "ef8ba4b3-7898-4240-aa8f-a0586ed04fb7"
SqlStr = "select * from employee where employee_id=?"
Params = Array As Object(EmployeeId)
rs         = SQL.ExecQuery2(SqlStr, Params)

This will throw an exception:
org.postgresql.util.PSQLException: ERROR: operator does not exist: uuid = character varying
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

The problem is EmployeeId is a string variable in the Params array like "ef8ba4b3-7898-4240-aa8f-a0586ed04fb7". But PostgreSQL stores the UUID as an 8 byte integer and I think it expects an integer in the Where clause so the Params needs to be an integer. To make matters worse, PostgreSQL displays employee_id as a string like "ef8ba4b3-7898-4240-aa8f-a0586ed04fb7" when the "Select Employee_id from employee" is executed. I don't think there a way for the Select statement to return the UUID as a Long instead of a string? In other words, a PostgreSQL Select statement returns the UUID column as a string, but won't accept the same UUID string as a parameter because it can't handle the variable type as a string.

Here is how the employee_id column is defined:

B4X:
CREATE TABLE public.employee (
  employee_id UUID DEFAULT uuid_generate_v4() NOT NULL,
...

The only "solution" I've come up with is to remove the UUID from all parameter arrays and hard code it into the SQL statement like:

B4X:
SqlStr = $"select * from employee where employee_id='${EmployeeId}'"$
SqlStr = $"update table employee set company_id='${CompanyId}' where  employee_id='${EmployeeId}'"$
SqlStr = $"insert into employee (employee_id, salary) values('${EmployeeId}',?)"$

I prefer not to do this because the variables could be subject to an sql injection attack on the database. Using parameters are much safer.
I also don't want to change the 8 byte UUID columns like Employee_Id into a 37 character string column because it uses a lot more storage and will be slower.

Is there a solution to using UUID's in PostgreSQL with B4x?
TIA
 

keirS

Well-Known Member
Licensed User
Longtime User
This works for the H2 DBMS and should work for PostgreSQL.

B4X:
Dim UUID As Object
UUID =  GetUUIDFromString("ef8ba4b3-7898-4240-aa8f-a0586ed04fb7")
SQL1.ExecNonQuery2("INSERT INTO  employee values(?)",Array As Object(UUID))



Sub GetUUIDFromString(UUID As String) As Object
  Dim JOUUID As JavaObject
  JOUUID.InitializeStatic("java.util.UUID")
  JOUUID = JOUUID.RunMethodJO("fromString",Array As Object (UUID))
  Return JOUUID
End Sub
 
Upvote 0

Diceman

Active Member
Licensed User
Tip: you should never need to write Array As Object. It is enough to write Array.

Try this:
B4X:
"select * from employee where employee_id= uuid(?)"

Erel,
That worked, thanks! :D
But I don't understand where the UUID() function comes from. I can't find any reference to it on the PostgreSQL web pages.

TIA

BTW, isn't "Array(val1, val2)" the same as "Array as Object(val1, val2)"?
 
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
Upvote 0

Diceman

Active Member
Licensed User

Don,
Yes, I am aware of the UUID data type for PostgreSQL. But I cannot find any reference to the UUID(<value>) SQL function.

B4X:
SQL Example:
Select MyUUIDCol, UUID(MyUUIDCol),  UUID('ef8ba4b3-7898-4240-aa8f-a0586ed04fb7') from MyTable  --Works
or
B4X Example:
Select * from MyTable where MyUUIDCol=?    'Fails
Select * from MyTable where MyUUIDCol=UUID(?)   'Works

I thought if there was such a function, it would be listed under https://www.postgresql.org/docs/9.6/static/uuid-ossp.html which is the PostgreSQL extension needed to use UUID's. It looks to me like the function UUID() is casting the value to a UUID data type but that is not the normal way to explicitly cast it in PostgreSQL.

With a little more digging it appears that someone (uuid-ossp?) has created a UID() assignment cast by using https://www.postgresql.org/docs/9.6/static/sql-createcast.html
CREATE CAST (TEXT AS UUID) WITH FUNCTION UUID(TEXT) AS ASSIGNMENT;
But this UUID() function does not appear in the list of uuid-ossp predefined functions. It is just undocumented and I would never have discovered it if it were not for Erel. I was just curious how he knew it existed.

TIA
 
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
But I cannot find any reference to the UUID(<value>) SQL function.
maybe this is related (just trying to help)
https://www.postgresql.org/docs/9.6/static/install-procedure.html
--with-uuid=LIBRARY

Build the uuid-ossp module (which provides functions to generate UUIDs), using the specified UUID library. LIBRARY must be one of:

  • bsd to use the UUID functions found in FreeBSD, NetBSD, and some other BSD-derived systems

  • e2fs to use the UUID library created by the e2fsprogs project; this library is present in most Linux systems and in OS X, and can be obtained for other platforms as well

  • ossp to use the OSSP UUID library
The link does not work but i found
https://github.com/sean-/ossp-uuid
 
Upvote 0

Diceman

Active Member
Licensed User
Don,
I should have said originally that I do have ossp-uuid installed and working correctly in PostgreSQL. This took only 10 seconds to install uuid-ossp.
After I installed uuid-ossp I can use UUID within the PostgreSQL environment just fine without any problems.

My original problem was with using UUID as an SQL parameter in B4J.
I could not execute
SqlStr = "select * from employee where employee_id=?"
if employee_id was a UUID because I would get an exception:
org.postgresql.util.PSQLException: ERROR: operator does not exist: uuid = character varying
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
This problem only occurred with UUID parameters. It will work if I hard code the UUID string into the SQL statement and not use a UUID parameter.

Erel's solution of using
SqlStr = "select * from employee where employee_id=UUID(?)"​
works just great.

Everything is working just fine now with UUID SQL parameters in B4J with Erel's UUID() solution. :p
I am just curious as to how Erel knew there was a UUID() assignment cast in PostgreSQL because I did not find it documented anywhere.
 
Upvote 0

udg

Expert
Licensed User
Longtime User
I am just curious as to how Erel knew.
That's easy:
Erel knows everything
Erel is everywhere
Erel is at any time

These are the basics.
:)
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
if employee_id was a UUID because I would get an exception:
org.postgresql.util.PSQLException: ERROR: operator does not exist: uuid = character varying
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
My original problem was with using UUID as an SQL parameter in B4J.
Under the hood, B4J's ExecQuery2 uses Java's PreparedStatement's setObject method (see https://github.com/AnywhereSoftware...rc/anywheresoftware/b4j/objects/SQL.java#L339). When you pass a Java string to setObject, than that string is mapped to an SQL data type of CHAR, VARCHAR or LONGVARCHAR (see https://www.cis.upenn.edu/~bcpierce/courses/629/jdkdocs/guide/jdbc/getstart/mapping.doc.html). Looks like in this case, the setObject method maps the passed on Java string that contains the UUID to a VARCHAR. So using a prepared statement, the Java string is mapped to a VARCHAR and then this VARCHAR is compared to the PostgreSQL UUID data type. At this point, you get the error message, indicating that PostgreSQL has no means of comparing VARCHAR to UUID
ERROR: operator does not exist: uuid = character varying
At the same time, the error message does give a hint on how to solve the issue
You might need to add explicit type casts.

It looks like PostgreSQL at one time had similar issues with String to Integer conversion (and may still have). See https://www.postgresql.org/message-id/[email protected].
 
Upvote 0
Top