B4J Question jRDC2 PostgreSQL how to call procedures

[Alexander Stolte]

Hello,

how can i call procedures in jRDC2 on PostgreSQL?

I call it so:

CALL myprocdeure(?,?)

but jRDC2 says:

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:

JdbcUrl=jdbc:postgresql://linktothedb:5432/mydb?currentSchema=my_schema

Thanks

 

[Erel]

Start with calling it directly from a B4J program with the SQL library. It will be easier to debug it.

 

[Alexander Stolte]

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]

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]

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 it makes so lot fun *irony out*