Eclipse Community Forums: EclipseLink » @NamedStoredProcedureQuery and StoredProcedureCall problem

Help

Home

Home » Eclipse Projects » EclipseLink » @NamedStoredProcedureQuery and StoredProcedureCall problem

Show: Today's Messages :: Show Polls :: Message Navigator

@NamedStoredProcedureQuery and StoredProcedureCall problem [message #671755]

Wed, 18 May 2011 21:34

NeoZ

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)

Report message to a moderator