CallableStatement PostgreSQL Error

[Vic Hood]

Hi All,
Im trying to write sample stored functions in postgresql and call them using the CallableStatement offered by JDBC.

Here's some my test code

Consumer bean =new Consumer();
	    CallableStatement pstmt = null;
	   	    try {
	    	con.setAutoCommit(false);
	      String 	query = "{ ? = call getData( ? ) }";
	      pstmt = con.prepareCall(query); 
	      pstmt.registerOutParameter(1, Types.OTHER);
	      pstmt.setInt(2,5);
	      pstmt.execute(); // execute update statement
	      bean=(Consumer)pstmt.getObject(1);
 System.out.println("bean"+bean.getConsumer_name());
.....

And my Stored function is of the form .
CREATE FUNCTION getData(int) RETURNS SETOF db_consumer AS $$ SELECT * FROM db_consumer WHERE consumer_id = $1; $$ LANGUAGE SQL;

However, I'm getting the following error when I try to run the code .

org.postgresql.util.PSQLException: A CallableStatement was executed with an invalid number of parameters
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:408)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:381)
	at com.enzen.cis.dao.Updatetest.main(Updatetest.java:44) 

Any idea on why this is happening would be appreciated.

[Vic Hood]

Anyone ? Im kinda stuck with this

[Rob Spoor]

I'm only guessing here, but I don't think it's possible to call a function like it was a stored procedure. In MS SQL Server it's certainly not allowed. Try calling it like this:

Consumer bean =new Consumer();   
PreparedStatement pstmt = null;   
try {
    con.setAutoCommit(false);   
    String query = "SELECT getData(?)";
    pstmt = con.prepareStatement(query);    
    pstmt.setInt(1, 5);   
    ResultSet rs = pstmt.executeQuery();
    rs.next(); // won't fail as there is exactly one result from this function
    bean=(Consumer)rs.getObject(1);   
    System.out.println("bean"+bean.getConsumer_name());

[Martin Vajsar]

Rob Spoor wrote:I'm only guessing here, but I don't think it's possible to call a function like it was a stored procedure. In MS SQL Server it's certainly not allowed.

It should work. It definitely works in Oracle. Actually, this is why escaping (the curly braces) must be used, it allows the JDBC driver to rewrite and process the query as required by its database.

However, your advice is certainly worth to try, if the direct call doesn't work with MS SQL, other databases might be affected too.