Re: [eclipselink-users] eclipselink+postgresql+stored procedure

[James Sutherland <jamesssss@xxxxxxxxx>]

Assuming your SQL is correct, and returns the correct data for building a
Permission as it is mapped, then you can use,

em.createNativeQuery("select * from userpermissions(?)", Permission.class);

If you don't include the class, then the raw data is returned as an
Object[], which you could still use to build your object.  You may also
consider using a ResultSetMapping.

PostgreSQL technially does not support stored procedures, only stored
functions, which are somewhat different, so you cannot use
StoredProcedureCall.



zamek42 wrote:
> 
> Hi All,
> 
> I would like to use Postgresql stored procedures instead of a complicated
> sql subqueries. 
> I have a simple userpermissions(userId integer) stored procedure,
> which is resultset of a userpermission table (and jpa object named
> Permissions).
> 
> How can I call it from jpa (eclipselink)?
> 
> I tried:
> 1. Query qry = em.createNativeQuery("select * from userpermissions(?)");
>         qry.setParameter(1, usr.getId());
>         return qry.getResultList();
> it works well, but its results not a List of Permission, but a List of
> Object :(
> 
> 2. @NamedStoredProcedureQueries (
>     @NamedStoredProcedureQuery(name="userpermissions",
> procedureName="userpermissions",
>            
> parameters=@StoredProcedureParameter(name="userId",queryParameter="userId",type=Integer.class,direction=Direction.IN),
>             resultClass=Permissions.class, returnsResultSet=true)
> )
> ...
>        Query qry
> =em.createNamedQuery(EJBUtilConst.USER_PERMISSION_STORED_PROC);
>         qry.setParameter("userId", usr.getId());
>         return qry.getResultList();
> 
> it didn't works: "prepared statement "userpermissions" does not exist"
> 
> 3. PLSQLStoredProcedureCall plsql = new PLSQLStoredProcedureCall();
>         plsql.setProcedureName("userpermissions");
>         plsql.addNamedArgument("userId", "userId", Integer.TYPE);
>         ReadAllQuery q = new ReadAllQuery();
>         q.setCall(plsql);
>         q.addArgument("userId");
>         List args = new ArrayList();
>         args.add(usr.getId());
>         q.addArgumentValues(args);
>         Query query = JpaHelper.createQuery(q, em);
>         return query.getResultList();
> it didn't works:
> "Query argument userId not found in the list of parameters provided during
> query execution."
> 
> And I don't have any more idea:(
> 
> thx a lot
> Zamek
> 


-----
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland 
http://www.eclipse.org/eclipselink/
 EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
TopLink 
Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink , 
http://wiki.oracle.com/page/TopLink TopLink 
Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink , 
http://www.nabble.com/EclipseLink-f26430.html EclipseLink 
Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence 
-- 
View this message in context: http://www.nabble.com/eclipselink%2Bpostgresql%2Bstored-procedure-tp24719825p24726483.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.