java CallableStatement failed, while calling function of postgreSQL enterprise edition

Question

Case History:

I have upgraded postgreSQL normal to Enterprise edition

Old version: PostgreSQL 9.5.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-55), 64-bit

New Version: EnterpriseDB 9.5.0.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-55), 64-bit

Taken complete dump from old and imported in new version.

Problem statement:

While We are accessing postgre function from new version (Enterprise) (by making connection from java 1.8). Getting below error although there is no problem with establish connection with postgreSQL enterprise.

org.postgresql.util.PSQLException: A CallableStatement was executed with an invalid number of parameters
        at org.postgresql.jdbc.PgCallableStatement.executeWithFlags(PgCallableStatement.java:106)

This error comes for all 144 functions. Is there any change in java callableStatement and prepared statement while using enterprise PostgreSQL ? Although same procedures are working while establish connection with old version of PostgreSQL.

Please suggest

BR// Mohit M

Sandeep Singh Sandeep Singh · Accepted Answer · 2016-12-12T08:16:54

For me instead of CallableStatement, PreparedStatement worked. I was using following code using CallableStatement before

CallableStatement objCallableStatement = null;
objCallableStatement = connection.prepareCall("{call alad_balad(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");

objCallableStatement.setLong(1, 431);
objCallableStatement.setString(2, "12434");
objCallableStatement.setString(3, "1234");
objCallableStatement.setString(4, "en");
objCallableStatement.setString(5, null); 
objCallableStatement.setString(6, null);
objCallableStatement.setShort(7, Short.parseShort("0"));
objCallableStatement.setString(8, null);
objCallableStatement.setLong(9, 1);
objCallableStatement.setString(10, "1234");
objCallableStatement.registerOutParameter(11, java.sql.Types.VARCHAR);
objCallableStatement.registerOutParameter(12, java.sql.Types.VARCHAR);
objCallableStatement.registerOutParameter(13, java.sql.Types.VARCHAR);
objCallableStatement.registerOutParameter(14, java.sql.Types.BIGINT);
objCallableStatement.registerOutParameter(15, java.sql.Types.OTHER);
objCallableStatement.registerOutParameter(16, java.sql.Types.OTHER);
objCallableStatement.execute();

Now for enterprise version I am using the following code

PreparedStatement objPreparedStatement = null;
try{
objConnection = objConnectionDataSource.getConnection();
objPreparedStatement = objConnection.prepareCall("{call alad_balad(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");

objPreparedStatement.setLong(1, 431);
objPreparedStatement.setString(2, "12434");
objPreparedStatement.setString(3, "1234");
objPreparedStatement.setString(4, "en");
objPreparedStatement.setString(5, null); 
objPreparedStatement.setString(6, null);
objPreparedStatement.setShort(7, Short.parseShort("0"));
objPreparedStatement.setString(8, null);
objPreparedStatement.setLong(9, 1);
objPreparedStatement.setString(10, "1234");

objPreparedStatement.execute();
   ResultSet res = objPreparedStatement.getResultSet();
   String in = null;
   while ( res.next() ){
     in = res.getString(1);
     System.out.println(in);
   }

java CallableStatement failed, while calling function of postgreSQL enterprise edition

2 Answers