We have a stored procedure being called using the iAnywhere JDBC getting the
following error: "Update operation attempted on non-updatable query". The
strange thing is this error occurs only the first time we call the
procedure. After that, we can call the same procedure and it works on the
same set of data. Prior to calling the stored procedure, there are other
select/update/insert statements executed on tables other than the one we get
the error on.

I have found that running our application using the jConnect 5.5 JDBC does
not have any problem, we do not get the error at all.

Our version of sybase is 11.0.1.2376.

Here is the schema of the table being modified:

CREATE TABLE "qc"."Sequences" (
"sequenceId" varchar(30) NOT NULL,
"currentValue" int NULL,
"minValue" int NULL DEFAULT 1,
"maxValue" int NULL DEFAULT 2147483647,
"ts" timestamp NULL DEFAULT TIMESTAMP,
PRIMARY KEY ( "sequenceId" ASC )
) IN "system";


Here is the body of the stored procedure. The offending line is "update
Sequences set currentValue = lCurrentValue where current of
sequencesGetNextNoCursor;".

create function SequencesGetNextNo(inSequenceId name,
inMinValue counter default null,
inMaxValue counter default null)
returns counter
begin

declare lCurrentValue counter;
declare lMinValue counter;
declare lMaxValue counter;

declare sequencesGetNextNoCursor cursor for
select currentValue, minValue, maxValue
from Sequences
where sequenceId = inSequenceId
for update;

set lCurrentValue = 1;

update Sequences
set minValue = minValue
where sequenceId = inSequenceId;

if (sqlcode != 0)
then
/*
* It does not exist, create it.
*/
if(inMinValue is null) then
set inMinValue = lCurrentValue;
else
set lCurrentValue = inMinValue;
end if;

if(inMaxValue is null) then
set inMaxValue = 0x7fffffff;
end if;

call SequencesCreate(inSequenceId,
lCurrentValue,
inMinValue,
inMaxValue);
else
open sequencesGetNextNoCursor;

if (sqlcode = 0)
then
fetch next sequencesGetNextNoCursor
into lCurrentValue,
lMinValue,
lMaxValue;

if (sqlcode = 0)
then
if (lCurrentValue >= lMaxValue)
then
set lCurrentValue = lMinValue;
else
set lCurrentValue = lCurrentValue + 1;
end if;

update Sequences
set currentValue = lCurrentValue
where current of sequencesGetNextNoCursor;

if (sqlcode <> 0)
then
message 'update of Sequences failed.' type warning to client;
end if
end if;

close sequencesGetNextNoCursor;
end if;
end if; //update

return lCurrentValue;
end;