B4J Question jRDC2 PostgreSQL how to call procedures

Alexander Stolte

Well-Known Member
Licensed User
Hello,

how can i call procedures in jRDC2 on PostgreSQL?

I call it so:
B4X:
CALL myprocdeure(?,?)
but jRDC2 says:
B4X:
org.postgresql.util.PSQLException: ERROR: procedure sdbspget_checkuser(character varying, character varying, character varying, character varying) does not exist
  Hinweis: No procedure matches the given name and argument types. You might need to add explicit type casts.
but the procedure exists, i can call it in the editor.
I also set the default schema in the connection string:
B4X:
JdbcUrl=jdbc:postgresql://linktothedb:5432/mydb?currentSchema=my_schema
Thanks
 

Erel

Administrator
Staff member
Licensed User
Start with calling it directly from a B4J program with the SQL library. It will be easier to debug it.
 

Alexander Stolte

Well-Known Member
Licensed User
maybe i found the issue...

PostgreSQL is case lower case and my procedure names have big and little letters in the name and i'm using higher case letters as column name.
MySQL was easier
 

Alexander Stolte

Well-Known Member
Licensed User
This was one of the problems, but not the final problem.

I found out that the problem is, that postgresql have the build in feature "Overloading Functions", that means the same Function or Procedure Name can use with other parameter types. The Problem of my jRDC is, that i send an array of object to the jRDC2 like in the jRDC2 Tutorial, postgre is thinking i send varying types instead of the real types like integer,float,string, so postgre cause a error.

Now i search for a solution, where i can change the type like :"CALL procedure(? as integer)".
 

Alexander Stolte

Well-Known Member
Licensed User
Okay, fix the next bug, you can call a procedure like this: CALL procedure(?::INT);

The next problem are, stored procedures on postgresql can not return values... this means you can't put a SELECT * FROM table in this to return this.
Now I have to turn all procedures into functions. PostgreSQL is so funny :rolleyes: it makes so lot fun *irony out*
 
Top