Home » Eclipse Projects » EclipseLink » @NamedStoredProcedureQuery and StoredProcedureCall problem
@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)
|
|
| | |
Goto Forum:
Current Time: Sat Apr 27 04:18:45 GMT 2024
Powered by FUDForum. Page generated in 0.02425 seconds
|