I have a PostgreSQL SQL statement like:
private SqlStr as String
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:
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:
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
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
