• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

output parameter from stored procedure: Operand type clash: VARCHAR is incompatible with VOID Type

 
Kameyama Sato
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am Using Eclipselink (2.0) to call a sybase stored procedure. And I am getting the above error message in log.

The stored procedure takes 4 parameters of the following type: int, int, varchar(50), varchar(255) output. (So the last one is a output parameter)
I am certain the stored procedure itself is correct because I can directly call it in database and it works.

Now here's my java code:


Here's the code to use it:



It seems that the the program cannot convert varchar to String? Am i missing something here? any thoughts? Thanks
 
James Sutherland
Ranch Hand
Posts: 553
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please include the full code for the stored procedure (SQL), and the SQL generated by the Query.

Also include the exception stack trace.
 
Kameyama Sato
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
James Sutherland wrote:Please include the full code for the stored procedure (SQL), and the SQL generated by the Query.

Also include the exception stack trace.


Here the stored procedure:


Here's the exception message:
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="mySP" )
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)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:593)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:526)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1729)
at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:234)
...
...
Caused by: com.sybase.jdbc3.jdbc.SybSQLException: Operand type clash: VARCHAR is incompatible with VOID TYPE

at com.sybase.jdbc3.tds.Tds.a(Unknown Source)
at com.sybase.jdbc3.tds.Tds.nextResult(Unknown Source)
at com.sybase.jdbc3.jdbc.ResultGetter.nextResult(Unknown Source)
at com.sybase.jdbc3.jdbc.SybStatement.nextResult(Unknown Source)
at com.sybase.jdbc3.jdbc.SybStatement.nextResult(Unknown Source)
at com.sybase.jdbc3.jdbc.SybStatement.updateLoop(Unknown Source)
at com.sybase.jdbc3.jdbc.SybStatement.executeUpdate(Unknown Source)
at com.sybase.jdbc3.jdbc.SybCallableStatement.executeUpdate(Unknown Source)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:789)
... 106 more
 
Kameyama Sato
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I also tried to invoke the stored procedure using the following code:


And I got the following exception:
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)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:526)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1729)
at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:234)
....
Caused by: java.sql.SQLException: JZ0SB: Parameter index out of range: 1.
at com.sybase.jdbc3.jdbc.ErrorMessage.raiseError(Unknown Source)
at com.sybase.jdbc3.jdbc.ParamManager.for(Unknown Source)
at com.sybase.jdbc3.jdbc.ParamManager.doSetParam(Unknown Source)
at com.sybase.jdbc3.jdbc.ParamManager.setParam(Unknown Source)
at com.sybase.jdbc3.jdbc.SybPreparedStatement.a(Unknown Source)
at com.sybase.jdbc3.jdbc.SybPreparedStatement.a(Unknown Source)
at com.sybase.jdbc3.jdbc.SybPreparedStatement.setInt(Unknown Source)
at org.eclipse.persistence.internal.databaseaccess.DatabasePlatform.setParameterValueInDatabaseCall(DatabasePlatform.java:2078)
at org.eclipse.persistence.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:677)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:576)
... 100 more
 
James Sutherland
Ranch Hand
Posts: 553
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The issue is that Sybase for some reason requires that OUTPUT parameters be registered as INOUT parameters.

So use,



You will also need to pass in a parameter value to the call.

See,
http://wiki.eclipse.org/EclipseLink/FAQ/JPA/Sybase
 
Kameyama Sato
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
James Sutherland wrote:The issue is that Sybase for some reason requires that OUTPUT parameters be registered as INOUT parameters.

So use,



You will also need to pass in a parameter value to the call.

See,
http://wiki.eclipse.org/EclipseLink/FAQ/JPA/Sybase


Works like a charm! Thanks James!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic