7

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 ?

user3891270
  • 371
  • 1
  • 3
  • 7
  • could you post a SQL/SELECT/code extract you're trying to execute here? – access_granted Apr 14 '20 at 17:02
  • Hi, i have added the sample proc. let me know if you need more information – user3891270 Apr 15 '20 at 04:33
  • I don't know Sybase, but I'm guessing you'll need to use `execute()` instead of `executeQuery` and handle update counts and result sets based on the boolean return value, and use `getMoreResults()` to move to next result (again update count or result set), or maybe you need to add `set nocount on` at the start of your procedure. – Mark Rotteveel Apr 15 '20 at 14:54
  • Also, you `case` statement seems to be missing an `end`, and it may need an explicit alias. – Mark Rotteveel Apr 15 '20 at 14:56
  • Does it matter if call Sybase procedure using CallableStatement instead of PrepareStatement ? – user3891270 Apr 16 '20 at 06:40
  • Ok i found the problem the issue is when we call statement.setFetchSize method only then this issue occurs. Now we can not change the application code as it is third party application. do you know if there is any way to disable this property ? – user3891270 Apr 21 '20 at 06:49

2 Answers2

2

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')}");
Community
  • 1
  • 1
Ori Marko
  • 56,308
  • 23
  • 131
  • 233
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

Jin Thakur
  • 2,711
  • 18
  • 15