issue while executing Sybase procedure from JDBC

Question

While executing Sybase procedure from JDBC, I am getting below error:

Execute cursor is declared on a procedure which contains a non-SELECT or a SELECT with COMPUTE clause. for the declaration of this cursor to be legal it should have a single select statement without a compute clause

I am using JCONN4 sybase jar. Does sybase has such restrictions on procedure to not have select statement with compute clause?

Also I searched in Sybase documentation but couldn't get proper answer.

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.39996_1250/html/svrtsg/svrtsg348.htm

I cannot post the procedure here, but I can post the sample

create proc sample (@value_date datetime = null) as
begin 
if @value_date is null 
  select @value_date  = some_column from some_table

select a,b,c,d into #ad
from 
 table_a where a='something'

select a,b,c,d case when a=0 then 0 else b/a
from #ad

end

The above procedure is called using sybStatemt.executeQuery function

Looks like its Sybase bug. steps to reproduce the issue

1. Create a procedure having select with compute clause as i described above

2. write jdbc program and use belew method statement.setFetchSize(1000);

3. Execute the program and you will see the error

now the question is does Sybase really has these kind of restrictions or it is specific to their Driver only and we can say its driver issue ?

Answer 1

You must use CallableStatement when calling store procedure

If you execute a stored procedure in a CallableStatement object that represents parameter values as question marks, you get better performance than if you use both question marks and literal values for parameters. Also, if you mix literals and question marks, you cannot use output parameters with a stored procedure.

The following example creates sp_stmt as a CallableStatement object for executing the stored procedure MyProc:

CallableStatement sp_stmt = conn.prepareCall(   "{call MyProc(?,?)}");

The two parameters in MyProc are represented as question marks. You can register one or both of them as output parameters using the registerOutParameter methods in the CallableStatement interface.

In the following example, sp_stmt2 is a CallableStatement object for executing the stored procedure MyProc2.

 CallableStatement sp_stmt2 = conn.prepareCall(   {"call MyProc2(?,'javelin')}");

Answer 2

Run your sp from sybase command prompt. If it gives result it should work with sybase driver. I have used ado.net driver in c# it can run similar queries https://marketplace.visualstudio.com/items?itemName=CDATASOFTWARE.SybaseADONETProvider

Your Sp looks simple. But i think your sp had some runtime issue.

I think this line

if @value_date is null 
  select @value_date  = some_column from some_table

should be

if @value_date is null 
  select @value_date  = some_column from some_table where col1='kkk' so that only 

one value comes