B4J Question Problem using UUID parameter in PostgreSQL SQL statement

Discussion in 'B4J Questions' started by Diceman, Jun 7, 2018.

  1. Diceman

    Diceman Member Licensed User

    I have a PostgreSQL SQL statement like:

    private SqlStr as String

    Code:
    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:

    Code:
    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:

    Code:
    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
     
  2. Erel

    Erel Administrator Staff Member Licensed User

    Tip: you should never need to write Array As Object. It is enough to write Array.

    Try this:
    Code:
    "select * from employee where employee_id= uuid(?)"
     
    Diceman likes this.
  3. keirS

    keirS Well-Known Member Licensed User

    This works for the H2 DBMS and should work for PostgreSQL.

    Code:
    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 StringAs Object
      
    Dim JOUUID As JavaObject
      JOUUID.InitializeStatic(
    "java.util.UUID")
      JOUUID = JOUUID.RunMethodJO(
    "fromString",Array As Object (UUID))
      
    Return JOUUID
    End Sub
     
    Diceman likes this.
  4. Diceman

    Diceman Member Licensed User

    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)"?
     
  5. DonManfred

    DonManfred Expert Licensed User

  6. Diceman

    Diceman 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.

    Code:
    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
     
  7. DonManfred

    DonManfred Expert Licensed User

    maybe this is related (just trying to help)
    https://www.postgresql.org/docs/9.6/static/install-procedure.html
    The link does not work but i found
    https://github.com/sean-/ossp-uuid
     
  8. Diceman

    Diceman 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.
     
  9. udg

    udg Expert Licensed User

    That's easy:
    Erel knows everything
    Erel is everywhere
    Erel is at any time

    These are the basics.
    :)
     
  10. Diceman

    Diceman Member Licensed User

    I didn't know that.
    So Erel if you're listening, about the next Powerball numbers ...? :p
     
    udg and moster67 like this.
  11. Erel

    Erel Administrator Staff Member Licensed User

    Sent you a private message with the numbers.

    The truth is less heroic :)

    Google: postegres string to uuid

    First search result: https://stackoverflow.com/a/34652560/971547
     
  12. Erel

    Erel Administrator Staff Member Licensed User

    Yes.
     
  13. OliverA

    OliverA Well-Known Member Licensed User

    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
    At the same time, the error message does give a hint on how to solve the issue
    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/50EBFD42.6060401@cn.fujitsu.com.
     
  14. Diceman

    Diceman Member Licensed User

    Erel, thanks for the winning Powerball lottery number.
    I ordered both you and Don your own Lambo's. ;)
     
    DonManfred and Erel like this.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice