B4J Question jRDC2 PostgreSQL how to call procedures

Discussion in 'B4J Questions' started by Alexander Stolte, Jul 7, 2019.

  1. Alexander Stolte

    Alexander Stolte Well-Known Member Licensed User

    Hello,

    how can i call procedures in jRDC2 on PostgreSQL?

    I call it so:
    Code:
    CALL myprocdeure(?,?)
    but jRDC2 says:
    Code:
    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:
    Code:
    JdbcUrl=jdbc:postgresql://linktothedb:5432/mydb?currentSchema=my_schema
    Thanks
     
  2. Erel

    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.
     
  3. Alexander Stolte

    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
     
    Erel likes this.
  4. Alexander Stolte

    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)".
     
  5. Alexander Stolte

    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*
     
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