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 ?

Zel

could you post a SQL/SELECT/code extract you're trying to execute here? – Hearsay

Hi, i have added the sample proc. let me know if you need more information – Zel

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. – Allen

Also, you case statement seems to be missing an end, and it may need an explicit alias. – Allen

Does it matter if call Sybase procedure using CallableStatement instead of PrepareStatement ? – Zel

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 ? – Zel

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')}");

Abernethy

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

Teresita

Hi, The SP runs fine with command line and even with java code but the issue is when i use below method to restrict the size then it gives error. statement.fetchSize(100); – Zel

Please change sp and send size as parameter. SELECT TOP(5) * FROM Employees ORDER BY Surname; – Teresita

couldn't get you , can you please elaborate ? – Zel

instead of using statement.fetchSize(100); you can use top 10 syntax to get 100 rows andpass 100 as parameter to SP. – Teresita

thats workaround and not the solution. i just wanted to know whether its sybase driver bug – Zel