Обсуждение: need advice about out parameter settings while calling stored procedure in Java code
Hi ,
I am migrating from Oracle to Postgresql, and have encountered below issue while calling stored procedure via the latest PG JDBC (postgresql-9.4-1201.jdbc4.jar).
Here is the procedure snippet:
----------start------------
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;
----------end-------------
In my Java code, I have to set the value of the out parameters like input as bellowing:
stmt = con.prepareCall("{call xxx. p_myprocedure(?,?,?,?,?,?)}");
stmt.setString(1, "x1");
stmt.setString(2, "x21");
stmt.setString(3, "x3");
stmt.setString(4, "x4");
stmt.setString (5,"");//need to set the output value
stmt.setString (6,""); //need to set the output value
before invoking the ‘stmt.execute();’;
Otherwise, it will throw error like:
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.
But setting output is unnecessary if I use Oracle DB or access Postgresql DB via EDB jdbc driver.
Is there any possible way to avoid such out parameter settings ?
Thanks & regards,
Alex
Re: need advice about out parameter settings while calling stored procedure in Java code
Alex Wang schrieb am 30.07.2015 um 15:18: > I am migrating from Oracle to Postgresql, and have encountered below > issue while calling stored procedure via the latest PG JDBC > (postgresql-9.4-1201.jdbc4.jar).> > > ----------start------------ > > 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; > ----------end------------- > The above is invalid for PostgreSQL. Are you sure you are using PostgreSQL and not EnterpriseDB? (which I think offers such a compatibility layer) Thomas
Hi all,
I have fixed my issue with some modifications:
Modify the org.postgresql.core.v3.SimpleParameterList java file, the two revised methods are as below:
public void registerOutParameter( int index, int sqlType ) throws SQLException
{
if (index < 1 || index > paramValues.length)
throw new PSQLException(GT.tr("The column index is out of range: {0}, number of columns: {1}.", new Object[]{index, paramValues.length}), PSQLState.INVALID_PARAMETER_VALUE);
flags[index-1] |= OUT;
paramValues[index-1] = sqlType; //added by Alex: there is no such line in the original source code
}
public void convertFunctionOutParameters()
{
/* for (int i=0; i<paramTypes.length; ++i)
{
if (direction(i) == OUT)
{
paramTypes[i] = Oid.VOID;
paramValues[i] = "null";
}
}*/
} // revised by Alex
Thanks & regards,
Alex
From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Alex Wang
Sent: 2015年7月30日 21:19
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] need advice about out parameter settings while calling stored procedure in Java code
Hi ,
I am migrating from Oracle to Postgresql, and have encountered below issue while calling stored procedure via the latest PG JDBC (postgresql-9.4-1201.jdbc4.jar).
Here is the procedure snippet:
----------start------------
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;
----------end-------------
In my Java code, I have to set the value of the out parameters like input as bellowing:
stmt = con.prepareCall("{call xxx. p_myprocedure(?,?,?,?,?,?)}");
stmt.setString(1, "x1");
stmt.setString(2, "x21");
stmt.setString(3, "x3");
stmt.setString(4, "x4");
stmt.setString (5,"");//need to set the output value
stmt.setString (6,""); //need to set the output value
before invoking the ‘stmt.execute();’;
Otherwise, it will throw error like:
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.
But setting output is unnecessary if I use Oracle DB or access Postgresql DB via EDB jdbc driver.
Is there any possible way to avoid such out parameter settings ?
Thanks & regards,
Alex
This e-mail and any attachments thereto are intended for the sole use of the recipient(s) named above and may contain confidential and/or privileged material. Any use of the information contained herein (including, but not limited to, total or partial reproduction, communication, or dissemination in any form) by persons other than the intended recipient(s) is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer.
Hi all,
I have fixed my issue with some modifications:
Modify the org.postgresql.core.v3.SimpleParameterList java file, the two revised methods are as below:
public void registerOutParameter( int index, int sqlType ) throws SQLException
{
if (index < 1 || index > paramValues.length)
throw new PSQLException(GT.tr("The column index is out of range: {0}, number of columns: {1}.", new Object[]{index, paramValues.length}), PSQLState.INVALID_PARAMETER_VALUE);
flags[index-1] |= OUT;
paramValues[index-1] = sqlType; //added by Alex: there is no such line in the original source code
}
public void convertFunctionOutParameters()
{
/* for (int i=0; i<paramTypes.length; ++i)
{
if (direction(i) == OUT)
{
paramTypes[i] = Oid.VOID;
paramValues[i] = "null";
}
}*/
} // revised by Alex
Thanks & regards,
Alex
From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Alex Wang
Sent: 2015年7月30日 21:19
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] need advice about out parameter settings while calling stored procedure in Java code
Hi ,
I am migrating from Oracle to Postgresql, and have encountered below issue while calling stored procedure via the latest PG JDBC (postgresql-9.4-1201.jdbc4.jar).
Here is the procedure snippet:
----------start------------
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;
----------end-------------
In my Java code, I have to set the value of the out parameters like input as bellowing:
stmt = con.prepareCall("{call xxx. p_myprocedure(?,?,?,?,?,?)}");
stmt.setString(1, "x1");
stmt.setString(2, "x21");
stmt.setString(3, "x3");
stmt.setString(4, "x4");
stmt.setString (5,"");//need to set the output value
stmt.setString (6,""); //need to set the output value
before invoking the ‘stmt.execute();’;
Otherwise, it will throw error like:
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.
But setting output is unnecessary if I use Oracle DB or access Postgresql DB via EDB jdbc driver.
Is there any possible way to avoid such out parameter settings ?
Thanks & regards,
Alex
This e-mail and any attachments thereto are intended for the sole use of the recipient(s) named above and may contain confidential and/or privileged material. Any use of the information contained herein (including, but not limited to, total or partial reproduction, communication, or dissemination in any form) by persons other than the intended recipient(s) is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer.
Re: 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
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
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.
this list is for the community PostgreSQL server. Postgres Plus Advanced Server is a commercial product of EnterpriseDB, and you'll get much better support from them. Among other things, community Postgres doesn't have Packages, as thats an oracle specific feature.
-- john r pierce, recycling bits in santa cruz
Alex Wang wrote: > 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/ > > ~ > ~ > ~ > > Thanks & regards, > Alex Hello Alex, I'm not sure and as John indicated that this report and fix is required in this forum. The code example provided below works fine when used directly with a 9.4.1 server installation and 9.4-1200.jdbc4. This was just recently demostrated in this forum for meta data on CallableStatements. The only difference is the FUNCTION rather then your PROCEDURE definition/ call. danap. Complete Workable Code Sample Method. public void test(Connection connection) { // Method Instances String sqlStatementString; Statement sqlStatement; CallableStatement cstmt; try { sqlStatement = connection.createStatement(); sqlStatementString = "CREATE OR REPLACE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$" + " SELECT (x + y) AS sum, (x * y) AS prod; $$ LANGUAGE SQL;"; System.out.println(sqlStatement); sqlStatement.execute(sqlStatementString); // Execute Function. cstmt = connection.prepareCall("{call sum_n_product(?,?,?,?)}"); cstmt.registerOutParameter(3, Types.INTEGER); cstmt.registerOutParameter(4, Types.INTEGER); cstmt.setInt(1, 2); cstmt.setInt(2, 5); cstmt.execute(); int x1 = cstmt.getInt(3); int x2 = cstmt.getInt(4); System.out.println("x1: " + x1); System.out.println("x2: " + x2); // Drop Function. sqlStatementString = "DROP FUNCTION sum_n_product(int, int, OUT int, OUT int);"; System.out.println(sqlStatementString); sqlStatement.execute(sqlStatementString); cstmt.close(); sqlStatement.close(); } catch (SQLException sqle) { System.out.println("SQL Exeception" + sqle); } }