Re: Re: need advice about out parameter settings while calling stored procedure in Java code

Hi Vladimir/Dave,

Thanks for your kind reply. I am migrating a huge platform from Oracle (11g) to Postgresql (Postgres Plus Advanced Server 9.3.1.3). Here are the details about the issue I encountered for your reference which I hope they are clear enough for your analysis.

I have a stored procedure inside a package (myPackage)  whose SQL snippet  is:

PROCEDURE p_myprocedure(a character varying, b character varying, c character varying, d character varying, OUT o1 text, OUT o2 text) IS

  BEGIN

Blalabla;

END;

There are total 6 parameters : a, b, c, d are IN types;o1, o2 are OUT ones.

The main portion of my Java code is as below:

String pgurl = "jdbc:postgresql://dbhost:dbPort/dbInstance";

                                Connection con = null;

                                CallableStatement stmt = null;

                                try {

                                                // the postgresql driver string

                                                Class.forName("org.postgresql.Driver");

                                                // Class.forName("com.edb.Driver");

                                                // get the postgresql database connection

                                                con = DriverManager.getConnection(pgurl, "uerName", "Password");

                                                // con =DriverManager.getConnection("jdbc:edb://dbhost:dbPort/dbInstance","uerName","Password");

                                                stmt = con.prepareCall("{call myPackage. p_myprocedure (?,?,?,?,?,?)}");

                                                stmt.setString(1, "In1");

                                                stmt.setString(2, "In2");

                                                stmt.setString(3, "In3");

                                                stmt.setString(4, "In4");

                                                // stmt.setString (5,"");//code to set the OUT parameter

                                                // stmt.setString (6,""); //code to set the OUT parameter

                                                stmt.registerOutParameter(5, Types.VARCHAR); // O_QUERY_SQL

                                                stmt.registerOutParameter(6, Types.VARCHAR); // O_COLUMNS

                                                stmt.execute();

                                                System.out.println("string 5=" + stmt.getString(5));

                                                System.out.println("string 6=" + stmt.getString(6));

                                } catch (Exception e) {

                                                Blalba;

                                } finally {

                                                Blabla;

                                }

If the backed DB is Oracle or choosing EDB (edb-jdbc17.jar) as the JDBC driver, I don`t need to set the value of OUT type parameters while calling the stored procedure. In other words, these two lines in my java file can be removed:

                                                stmt.setString (5,"");//code to set the OUT parameter,  

                                                stmt.setString (6,""); //code to set the OUT parameter

After switching the JDBC driver to postgresql-9.4-1201.jdbc4.jar  file, bellowing error was thrown out if I remove stmt.setString (5,""); stmt.setString (6,""); lines in the Java file:

ERROR: function p_myprocedure (character varying, character varying, character varying, character varying) does not exist

  Hint: No function matches the given name and argument types. You might need to add explicit type casts.

As it is a huge application, there are tons of java files without setting OUT parameters (We used ORACLE DB previously,  there are no snippets like stmt.setString (5,""); stmt.setString (6,"");), so I come here for a stable solution.

Thanks & regards,

Alex

-----Original Message-----
From: Vladimir Sitnikov [mailto:sitnikov.vladimir@gmail.com]
Sent: 2015年8月1日 2:15
To: Alex Wang
Cc: pgsql-jdbc@postgresql.org; Dave Cramer
Subject: Re: [JDBC] Re: need advice about out parameter settings while calling stored procedure in Java code

Alex,

Can you provide a test that reproduces the problem?

I think that would simplify the analysis.

Frankly speaking I've not quite got what is the issue.

Vladimir