Spring + postgres + stored procedure

[spring_dummy]

Hi, I have stored procedures on postgres to call with spring
this is merely function such as

listerActionsModele (provide a list of Actions as a function !)

declare curseur refcursor;
begin
open curseur for select l.idListeAction,
l.nomlisteAction,
a.identLovAction,
a.fichierPicto,
a.actionIHM,
a.codeAction
from listeAction l natural join lovAction a
order by l.idListeAction;
return curseur;


my test code ......

import javax.sql.DataSource;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.Map;
import java.util.Iterator;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.object.StoredProcedure;
import org.springframework.jdbc.support.nativejdbc.Common sDbcpNativeJdbcExtractor;
import org.springframework.jdbc.core.SqlParameter;
//import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.datasource.*;
import org.springframework.jdbc.core.SqlReturnResultSet;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;

import org.springframework.jdbc.datasource.*;


import org.springframework.dao.DataAccessException;
import org.springframework.dao.InvalidDataAccessApiUsageE xception;
import org.springframework.jdbc.BadSqlGrammarException;
import org.springfram :( ework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.ParameterMapper;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.SqlReturnResultSet;
import org.springframework.jdbc.datasource.ConnectionHold er;
import org.springframework.jdbc.support.SQLExceptionTrans lator;
import org.springframework.jdbc.support.SQLStateSQLExcept ionTranslator;
import org.springframework.transaction.support.Transactio nSynchronizationManager;



public class TestSP {

public static void main(String[] args) {

System.out.println("DB TestSP!");
TestSP t = new TestSP();
t.test();
System.out.println("Done!");

}

void test() {
DriverManagerDataSource ds = new DriverManagerDataSource();
ds.setDriverClassName("org.postgresql.Driver");
ds.setUrl("jdbc:postgresql://10.10.10.130:5432/testdb");
ds.setUsername("cosma");
ds.setPassword("");

MyStoredProcedure sproc = new MyStoredProcedure(ds);
Map res = sproc.execute();
printMap(res);


System.out.println("fin execute" );


}

private class MyStoredProcedure extends StoredProcedure {
public static final String SQL = "\"listerActionsModele\"";
private int count = 0;

public MyStoredProcedure(DataSource ds) {
setDataSource(ds);
setFunction(true);
setSql(SQL);
declareParameter(new SqlOutParameter("rs", Types.OTHER , new RowCallbackHandlerImpl()));


//declareParameter(new SqlReturnResultSet("rs", new RowCallbackHandlerImpl()));
/*

declareParameter(new SqlReturnResultSet("rs", new RowMapper() {
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
Action o = new Action();
o.setIdentlisteaction(rs.getInt(1));
o.setIdentlovaction(rs.getInt(1));
// add more mappings here ????
return o;
}
}));
*/

compile();
}

public int getCount() {
return count;
}

private class RowCallbackHandlerImpl implements RowCallbackHandler {
public void processRow(ResultSet rs) throws SQLException {
while (rs.next()) {
System.out.println(rs.getInt(1));
}
count++;
}
}

public Map execute() {
Map out = execute(new HashMap());
System.out.println(out);
return out;
}
}

private static void printMap(Map r) {
Iterator i = r.entrySet().iterator();
while (i.hasNext()) {
System.out.println((String) i.next().toString());
}
}
}

end;

and the error ....

DB TestSP!
1 sept. 2004 14:32:58 org.springframework.jdbc.datasource.DriverManagerD ataSource setDriverClassName
INFO: Loaded JDBC driver: org.postgresql.Driver
1 sept. 2004 14:32:58 org.springframework.beans.factory.xml.XmlBeanDefin itionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
1 sept. 2004 14:32:58 org.springframework.beans.factory.support.Abstract BeanFactory getBean
INFO: Creating shared instance of singleton bean 'DB2'
1 sept. 2004 14:32:59 org.springframework.beans.factory.support.Abstract BeanFactory getBean
INFO: Creating shared instance of singleton bean 'HSQL'
1 sept. 2004 14:32:59 org.springframework.beans.factory.support.Abstract BeanFactory getBean
INFO: Creating shared instance of singleton bean 'MS-SQL'
1 sept. 2004 14:32:59 org.springframework.beans.factory.support.Abstract BeanFactory getBean
INFO: Creating shared instance of singleton bean 'MySQL'
1 sept. 2004 14:32:59 org.springframework.beans.factory.support.Abstract BeanFactory getBean
INFO: Creating shared instance of singleton bean 'Oracle'
1 sept. 2004 14:32:59 org.springframework.beans.factory.support.Abstract BeanFactory getBean
INFO: Creating shared instance of singleton bean 'Informix'
1 sept. 2004 14:32:59 org.springframework.beans.factory.support.Abstract BeanFactory getBean
INFO: Creating shared instance of singleton bean 'PostgreSQL'
1 sept. 2004 14:32:59 org.springframework.beans.factory.support.Abstract BeanFactory getBean
INFO: Creating shared instance of singleton bean 'Sybase'
1 sept. 2004 14:32:59 org.springframework.jdbc.support.SQLErrorCodesFact ory <init>
INFO: SQLErrorCodes loaded: [HSQL Database Engine, Oracle, Sybase SQL Server, Microsoft SQL Server, Informix Dynamic Server, PostgreSQL, MySQL, DB2]
1 sept. 2004 14:32:59 org.springframework.jdbc.support.SQLErrorCodesFact ory getErrorCodes
INFO: Looking up default SQLErrorCodes for DataSource
1 sept. 2004 14:32:59 org.springframework.jdbc.datasource.DriverManagerD ataSource getConnectionFromDriverManager
INFO: Creating new JDBC connection to [jdbc:postgresql://10.10.10.130:5432/testdb]
1 sept. 2004 14:32:59 org.springframework.jdbc.support.SQLErrorCodesFact ory getErrorCodes
INFO: Database Product Name is PostgreSQL
1 sept. 2004 14:32:59 org.springframework.jdbc.support.SQLErrorCodesFact ory getErrorCodes
INFO: Driver Version is PostgreSQL 7.4.3 JDBC3 with SSL (build 214)
1 sept. 2004 14:32:59 org.springframework.jdbc.object.SqlCall compileInternal
INFO: Compiled stored procedure. Call string is [{? = call "listerActionsModele"()}]
1 sept. 2004 14:32:59 org.springframework.jdbc.object.RdbmsOperation compile
INFO: RdbmsOperation with SQL ["listerActionsModele"] compiled
1 sept. 2004 14:32:59 org.springframework.jdbc.datasource.DriverManagerD ataSource getConnectionFromDriverManager
INFO: Creating new JDBC connection to [jdbc:postgresql://10.10.10.130:5432/testdb]
1 sept. 2004 14:32:59 org.springframework.jdbc.core.JdbcTemplate extractReturnedResultSets
ATTENTION: ResultSet returned from stored procedure but a corresponding SqlReturnResultSet parameter was not declared
1 sept. 2004 14:32:59 org.springframework.jdbc.support.SQLErrorCodeSQLEx ceptionTranslator translate
ATTENTION: Unable to translate SQLException with errorCode '0', will now try the fallback translator
1 sept. 2004 14:32:59 org.springframework.jdbc.support.SQLStateSQLExcept ionTranslator translate
INFO: Translating SQLException with SQLState '34000' and errorCode '0' and message [ERROR: cursor "<unnamed portal 1>" does not exist
]; SQL was [{? = call "listerActionsModeleBis"()}] for task [executing CallableStatementCallback [CallableStatementCreatorFactory.CallableStatementC reatorImpl: sql=[{? = call "listerActionsModeleBis"()}]: params=[{}]]]
org.springframework.jdbc.UncategorizedSQLException : (executing CallableStatementCallback [CallableStatementCreatorFactory.CallableStatementC reatorImpl: sql=[{? = call "listerActionsModeleBis"()}]: params=[{}]]): encountered SQLException [ERROR: cursor "<unnamed portal 1>" does not exist
]; nested exception is org.postgresql.util.PSQLException: ERROR: cursor "<unnamed portal 1>" does not exist

org.postgresql.util.PSQLException: ERROR: cursor "<unnamed portal 1>" does not exist

at org.postgresql.util.PSQLException.parseServerError (PSQLException.java:139)
at org.postgresql.core.QueryExecutor.executeV3(QueryE xecutor.java:152)
at org.postgresql.core.QueryExecutor.execute(QueryExe cutor.java:100)
at org.postgresql.core.QueryExecutor.execute(QueryExe cutor.java:67)
at org.postgresql.jdbc3.Jdbc3RefCursorResultSet.next( Jdbc3RefCursorResultSet.java:42)
at org.springframework.jdbc.core.JdbcTemplate$RowCall backHandlerResultSetExtractor.extractData(JdbcTemp late.java:780)
at org.springframework.jdbc.core.JdbcTemplate.process ResultSet(JdbcTemplate.java:617)
at org.springframework.jdbc.core.JdbcTemplate.extract OutputParameters(JdbcTemplate.java:581)
at org.springframework.jdbc.core.JdbcTemplate$7.doInC allableStatement(JdbcTemplate.java:530)
at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:500)
at org.springframework.jdbc.core.JdbcTemplate.call(Jd bcTemplate.java:520)
at org.springframework.jdbc.object.StoredProcedure.ex ecute(StoredProcedure.java:101)
at TestSP$MyStoredProcedure.execute(TestSP.java:131)
at TestSP.test(TestSP.java:77)
at TestSP.main(TestSP.java:64)


??? I do not find standard code in the doc and cannot find solution myself
with RowMapper or anything else
please help or propose code, thanks in advance.
Your dummy user

[irbouho]

I spent about 1h trying to resolve this problem :cry:
1.configuration
- postgresql 7.2 on windows XP
- jdbc driver: pg74.215.jdbc3.jar (http://jdbc.postgresql.org/download/pg74.215.jdbc3.jar)
- jdk 1.4.2_03
- Spring: 1.1 from CVS
2.table users


select id, login from users;
id | login
----+--------
0 | system
1 | admin
2 | guest
3 | taha
&#40;4 rows&#41;

3.stored procedure


CREATE FUNCTION demo&#40;&#41; RETURNS refcursor AS '
DECLARE
ref refcursor;
BEGIN
OPEN ref FOR SELECT id, login FROM users;
RETURN ref;
END;
' LANGUAGE plpgsql;

4.java class


public class Testos &#123;
public static void main&#40;String&#91;&#93; args&#41; throws Exception &#123;
new Testos&#40;&#41;.execute&#40;&#41;;
&#125;

public void execute&#40;&#41; throws Exception &#123;
//some DI will be welcome here
BasicDataSource ds = new BasicDataSource&#40;&#41;;
ds.setDriverClassName&#40;"org.postgresql.Driver"&#41;;
ds.setUrl&#40;"jdbc&#58;postgresql&#58;//localhost&#58;5432/xdocs"&#41;;
ds.setUsername&#40;"xdocs"&#41;;
ds.setPassword&#40;"xdocs"&#41;;
//do not forget to turn autocommit false,
//otherwise, the refcursor will be closed before the data is read
ds.setDefaultAutoCommit &#40;false&#41;;

DemoStoredProcedure proc = new DemoStoredProcedure&#40;ds&#41;;
Map params = new HashMap&#40;&#41;;
proc.execute&#40;params&#41;;

ds.close&#40;&#41;;
&#125;

private class DemoStoredProcedure extends StoredProcedure &#123;
public static final String SQL = "demo";
public DemoStoredProcedure&#40;DataSource ds&#41; &#123;
setDataSource&#40;ds&#41;;
setSql&#40;SQL&#41;;
declareParameter&#40;new SqlReturnResultSet&#40;"refcur", new DemoRowMapper&#40;&#41;&#41;&#41;;
compile&#40;&#41;;
&#125;
&#125;

private class DemoRowMapper implements RowCallbackHandler &#123;
public void processRow&#40;ResultSet rs&#41; throws SQLException &#123;
//get our ref cursor
ResultSet cur = &#40;ResultSet&#41; rs.getObject&#40;1&#41;;
while &#40;cur.next&#40;&#41;&#41; &#123;
System.out.println&#40;cur.getInt&#40;1&#41; + " - " + cur.getString&#40;2&#41;&#41;;
&#125;
cur.close&#40;&#41;;
&#125;
&#125;
&#125;

5.comments
- you must turn false autocommit, otherwise, the refcursor will be closed before the data is read by the java class.
- since the stored procedure returns a refcursor, you should use :
Resultset cur = (ResultSet) rs.getObject(1);
- demo is a function, but we can not use setFunction(true)

[spring_dummy]

It's work ! Many thanks for your time

just one thing, how can I adapt this code to return a List of Items coming from the rs ?

Does spring provide tools like jakarta dbutils ?

[trisberg]

Change the DemoRowMapper to a true RowMapper :) This should give you a List of User objects.


private class DemoRowMapper implements RowMapper &#123;
public Object mapRow&#40;ResultSet rs, int rowNum&#41; throws SQLException &#123;
User u = new User&#40;&#41;;
u.setId = rs.getInt&#40;1&#41;;
u.setLogin = rs.getString&#40;2&#41;;
return u;
&#125;
&#125;


Thanks for the example, Omar. I never got this to work on Postgres before, but I think I was using an older JDBC driver, at that point, that did not support ref cursors.

[spring_dummy]

at the end how do you get the List from the execute method ?

give full sample code as a reference

thanks
your dummy user :lol:
:oops:

[trisberg]

The execute method returns a Map with one entry for each out parameter keyed by the name specified when you declare the parameters. So this code would retrive the List returned for the "refcur" parameter declared in DemoStoredProcedure:

DemoStoredProcedure proc = new DemoStoredProcedure&#40;ds&#41;;
Map params = new HashMap&#40;&#41;;
Map results = proc.execute&#40;params&#41;;
List refcurRows = results.get&#40;"refcur"&#41;;

[springnaive]

I did not find the method setDefaultAutoCommit() in pg74.216.jdbc3.jar, but I found it in postgresql 8.* jdbc 3. So I am wondering how you could get around to call this method without upgrading the postgres to version 8.

The setDefaultAutoCommit() method was found in org.postgresql.ds.PGConnectionPoolDataSource class in postgresql.8.0-315.jdbc3.jar.

Do you have other way if I prefer not to upgrade postgres to version 8 ?

Thanks,
SpringNaive
:confused: