Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » @NamedStoredProcedureQuery and StoredProcedureCall problem
@NamedStoredProcedureQuery and StoredProcedureCall problem [message #671755] Wed, 18 May 2011 21:34 Go to next message
NeoZ  is currently offline NeoZ Friend
Messages: 9
Registered: May 2011
Junior Member
Hi, I have a simple stored procedure like this:

CREATE PROCEDURE zzzSP  
  @x           int,  
  @y           int,  
  @z    varchar(10),  
  @message     varchar(255)  output   
    
AS  
  
if @x=1  
begin  
   select @message = 'ok'  
end  
else  
begin  
   select @message = 'not ok'  
end  

The stored procedure itself works if I call it directly in database tool.
and I tried to use @NamedStoredProcedureQuery and StoredProcedureCall to call it, and both didn't work.and I've been struggling with this for a couple of days. Any thought would be appreciated!

For @NamedStoredProcedureQuery, I have the following
@NamedStoredProcedureQueries (  
    @NamedStoredProcedureQuery(name="zzzSP", procedureName="zzzSP",  
                  parameters ={    
            @StoredProcedureParameter(queryParameter="x", type=Integer.class),      
            @StoredProcedureParameter(queryParameter="y", type=Integer.class),   
            @StoredProcedureParameter(queryParameter="z", type=String.class),   
            @StoredProcedureParameter(queryParameter="message ",direction=Direction.OUT, type=String.class)    
        })        
)  

Caling code:


Query q = getEntityManager().createNamedQuery("zzzSP");  
q.setParameter("x", inputInteger1);  
q.setParameter("y", inputInteger2);  
q.setParameter("z", "my_input_string");  
  
return (String)q.getSingleResult();



Exception seen in log:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.2.0.v20110202-r8913): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.sybase.jdbc3.jdbc.SybSQLException: Operand type clash: VARCHAR is incompatible with VOID TYPE

Error Code: 206
Call: EXECUTE zzzSP @x = ?, @y = ?, @z = ?, @message = ? OUTPUT
bind => [4 parameters bound]
Query: DataReadQuery(name="zzzSP" ) at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:324)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:798)
at org.eclipse.persistence.platform.database.SybasePlatform.executeStoredProcedure(SybasePlatform.java:251)

For the StoredProcedureCall
        PLSQLStoredProcedureCall spcall = new PLSQLStoredProcedureCall();  
        spcall.setProcedureName("zzzSP");  
              
        spcall.addNamedArgument("x", JDBCTypes.INTEGER_TYPE);  
        spcall.addNamedArgument("y",JDBCTypes.INTEGER_TYPE);  
        spcall.addNamedArgument("z",JDBCTypes.VARCHAR_TYPE, 10);  
          
        spcall.addNamedOutputArgument("message",JDBCTypes.VARCHAR_TYPE, 255);  
          
        DataReadQuery query = new DataReadQuery();  
              
        query.addArgument("x");  
        query.addArgument("y");  
        query.addArgument("z");  
        query.addArgument("message");  
          
        query.setCall(spcall);  
          
        List args = new ArrayList();  
        args.add(Integer.valueOf(111));  
        args.add(Integer.valueOf(222));  
        args.add("myStr");  
        args.add("");  
          
        query.addArgumentValues(args);  
      
    List results = (List)   session.executeQuery(query


Error seen in log:
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.2.0.v20110202-r8913): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: JZ0SB: Parameter index out of range: 1.
Error Code: 0
Call:
DECLARE
x_TARGET INTEGER := :1;
y_TARGET INTEGER := :2;
z_TARGET VARCHAR(10) := :3;
message_TARGET VARCHAR(255);
BEGIN
zzzSP(x=>x_TARGET, y=>y_TARGET, z=>z_TARGET, message=>message_TARGET);
:4 := message_TARGET;
END;
bind => [:1 => 111, :2 => 222, :3 => my_stringdses, message => :4]
Query: DataModifyQuery()
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:684)

Re: @NamedStoredProcedureQuery and StoredProcedureCall problem [message #672007 is a reply to message #671755] Thu, 19 May 2011 14:50 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

See,
http://www.coderanch.com/t/538133/ORM/java/output-parameter-stored-procedure-Operand#2443181

Also, for the PLSQL procedure, PLSQL is specific to Oracle, this will not work with Sybase.


James : Wiki : Book : Blog : Twitter
Re: @NamedStoredProcedureQuery and StoredProcedureCall problem [message #672045 is a reply to message #672007] Thu, 19 May 2011 17:14 Go to previous message
NeoZ  is currently offline NeoZ Friend
Messages: 9
Registered: May 2011
Junior Member
got the @NamedStoredProcedureQuery work. thanks.
Previous Topic:eclipselink does two inserts and one update when using cyclic references
Next Topic:Passing parameters to be used in Customizer
Goto Forum:
  


Current Time: Wed Dec 17 23:14:16 GMT 2014

Powered by FUDForum. Page generated in 0.03484 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software