"Cursor has not been declared" (Yes it has!)

[Bruce Hay]

Your assignment statements are currently:
var = value;
but must be:
set var = value;

You should delete the DROP VARIABLE statements at the end, as well as the
DROP TABLE statements for temp1 and temp2.

The EXECUTE IMMEDIATE strings cannot include host variable references (e.g.
:LastDate); just construct the statement using concatenation to include the
value.

Version 8.0 does a better job of reporting syntax errors for batches and
stored procedures. Until then, comment out parts of the code until you find
the statement in error.

"Michael J. Austin" <aust...@ohsu.edu> wrote in message
news:tosvN6s...@forums.sybase.com...
> Bruce:
>
> Thanks for your response. It was very helpful. The DECLARE CURSOR help
text
> makes no mention of the begin...end requirement.
>
> I substituted your code for mine and received a "Syntax error near
DECLARE"
> error. The place in your code that ISQL "highlights" on displaying this
> error is marked by "^^^". I have included my entire code block so that you
> can see everything I am trying to do... I also cut out the prior portions
of
> this thread...
>
> TIA,
>
> Mike
>
> SET OPTION OUTPUT_FORMAT = ASCII;
>
> if not exists (select * from SYS.SYSTABLE where table_name='temp0') then
> CREATE GLOBAL TEMPORARY TABLE temp0 (TType Int, TName VarChar(50)) ON
> COMMIT PRESERVE ROWS;
> end if;
>
> begin
> ^^^ DECLARE LOCAL TEMPORARY TABLE temp1 (proc_no Integer);
> DECLARE LOCAL TEMPORARY TABLE temp2 (patient_id Integer);
> declare v_name VARCHAR(50);
> declare v_type SMALLINT;
> declare v_strSQL VarChar(500);
> DECLARE src_files CURSOR FOR
> SELECT t.TType, t.TName
> FROM temp0 t, sys.syscatalog s // use SYS.SYSTABLE instead?
> WHERE t.TName = s.tname
> ORDER BY t.TType, t.TName;
>
> load table temp0
> FROM 'c:\\maustin\\projects\\pentaxcori\\sourcefiles.dat';
>
> OPEN src_files;
> LOOP
> FETCH NEXT src_files INTO v_type, v_name;
> IF SQLSTATE = err_notfound THEN LEAVE src_files_loop;
> END IF;
> CASE v_type
> WHEN 0 THEN
> v_strSQL =
> 'INSERT INTO temp1 SELECT proc_no FROM report WHERE reportdate >= ' ||
> '':LastDate'' AND rpt_status = ''5'';';
> EXECUTE IMMEDIATE v_strSQL;'
> WHEN 1 THEN
> v_strSQL =
> 'INSERT INTO temp2 SELECT patient_id FROM patient WHERE timestamp >=
> '':LastDate'';';
> EXECUTE IMMEDIATE v_strSQL;'
>
> v_strSQL =
> 'SELECT CAST((substr(a.ssno, 4,1) + substr(a.ssno, 3,1) +
substr(a.ssno,
> 6,1) + ' +
> 'substr(a.ssno, 1,1) + substr(a.ssno, 8,1) + substr(a.ssno, 9,1) + ' +
> 'substr(a.ssno, 2,1) + substr(a.ssno, 5,1) + substr(a.ssno, 7,1)) AS
> Integer) + ' +
> '456763196 as ssno, a.country, a.city, a.dob, a.ethnic_grp, a.gender, '
+
> 'a.language, a.marital_status, a.postal_code, a.state ' +
> 'FROM patient a, temp2 t ' +
> 'WHERE a.patient_id = t.patient_id ># patient.cori;'
> EXECUTE IMMEDIATE v_strSQL;'
> WHEN 2 THEN
> v_strSQL =
> 'SELECT a.* FROM ' + v_name + 'a, temp1 t WHERE a.proc_no = t.proc_no
>#
> ' +
> v_name + '.cori;';
> EXECUTE IMMEDIATE v_strSQL;
> WHEN 3 THEN
> v_strSQL =
> 'SELECT a.* FROM ' + v_name + ' a, temp2 t WHERE a.patient_id =
> t.patient_id ># ' + v_name + '.cori;';
> EXECUTE IMMEDIATE v_strSQL;
> WHEN 4 THEN
> v_strSQL =
> 'SELECT * FROM ' + v_name + ' WHERE timestamp >= '':LastDate'' ># ' +
> v_name + '.cori;';
> EXECUTE IMMEDIATE v_strSQL;
> END CASE
> END LOOP;
> CLOSE src_files;
>
> END
>
> DROP VARIABLE v_name;
> DROP VARIABLE v_type;
> DROP VARIABLE v_strSQL;
>
> DROP TABLE temp0;
>
> DROP TABLE temp1;
>
> DROP TABLE temp2;
>
>
>
>

[Michael J. Austin]

All

I am trying to run the following code within an ISQL command window.
Eventually it will be part of a "command file" that will be passed to ISQL
as part of a batch process. The code generates a "cursor has not been
declared" error. Yet, plainly, the cursor *has* been declared. Here is the
code:

DECLARE LOCAL TEMPORARY TABLE temp0 (TType Int, TName VarChar(50)) ON COMMIT
PRESERVE ROWS;

DECLARE LOCAL TEMPORARY TABLE temp1 (proc_no Integer);
DECLARE LOCAL TEMPORARY TABLE temp2 (patient_id Integer);

CREATE VARIABLE v_name VARCHAR(50);
CREATE VARIABLE v_type SMALLINT;
CREATE VARIABLE v_strSQL VarChar(500);

SET OPTION OUTPUT_FORMAT = ASCII;

INPUT INTO temp0

FROM 'c:\\maustin\\projects\\pentaxcori\\sourcefiles.dat';

DECLARE src_files CURSOR FOR

SELECT t.TType, t.TName
FROM temp0 t, sys.syscatalog s

WHERE t.TName = s.tname
ORDER BY t.TType, t.TName;

OPEN src_files;

Neither I nor any of my co-workers can find anything syntactically wrong
with the above statements. I am logged in as 'dba', so there should not be a
permissions issue. If I type in the Declare Cursor code and submit it by
itself, it does not generate an error.

Any help appreciated.

Mike

[Stephen Rice]

Surround your code with BEGIN END (or use a stored procedure as was also
suggested) which creates a batch.

Without these each statement is executed separately by ISQL and therefore
there is no "context" from one to the next.

/steve
--
Stephen Rice
Technical Services Manager
iAnywhere Solutions
email: sr...@iAnywhere.com
Certified SQL Anywhere Associate

-- Please Post --

"Michael J. Austin" <aust...@ohsu.edu> wrote in message

news:lV6n1$rbBH...@forums.sybase.com...

[Cosmin Ioan]

I'm not a syntax guru so I can't advise you on exactly what's going on but I
remember a similar case with yours, trying to use a cursor and found a
perfect example in the stored procedures that come with the sample database
or with the system (or was it the help file!?). I used Copy/Paste and it
worked wonders :-) I bet you can find the answer within 5 minutes if you
do a search on 'stored procedures'.

Good luck,

Cosmin

P.S. If still in doubt, you might want to wait for further help however
'hacking' into Sybase provided examples is the best way to learn about SQL
Anywhere.

"Michael J. Austin" <aust...@ohsu.edu> wrote in message
news:lV6n1$rbBH...@forums.sybase.com...

[Michael J. Austin]

Bruce:

Thanks a lot for your assistance. I implemented all your suggestions. I
finally determined that there is something in my CASE statement that was
causing the problem..

My last position with the code was getting errors with the following
statement:

IF SQLSTATE = err_notfound THEN LEAVE src_files_loop;

(BTW, the statement is from the online help. Apparently, it applies to
embedded SQL. I couldn't find any code examples that applied to stored
procs.)

I spent a couple of hours reading the useless online help and trying to
substitute the SQLState constants and the SQLCode numbers in the above
statement. (For example, IF (SQLSTATE <> '00000') THEN LEAVE
src_files_loop;) No matter what I did, the DB engine would hang.

I give up. I don't have time for this. I just went with the brute force
method, which seems to be working.

Thanks again for your help.

Mike

[Michael J. Austin]

Bruce:

Thanks for your response. It was very helpful. The DECLARE CURSOR help text
makes no mention of the begin...end requirement.

I substituted your code for mine and received a "Syntax error near DECLARE"
error. The place in your code that ISQL "highlights" on displaying this
error is marked by "^^^". I have included my entire code block so that you
can see everything I am trying to do... I also cut out the prior portions of
this thread...

TIA,

Mike

SET OPTION OUTPUT_FORMAT = ASCII;

if not exists (select * from SYS.SYSTABLE where table_name='temp0') then
CREATE GLOBAL TEMPORARY TABLE temp0 (TType Int, TName VarChar(50)) ON
COMMIT PRESERVE ROWS;
end if;

begin
^^^ DECLARE LOCAL TEMPORARY TABLE temp1 (proc_no Integer);

DECLARE LOCAL TEMPORARY TABLE temp2 (patient_id Integer);

declare v_name VARCHAR(50);
declare v_type SMALLINT;
declare v_strSQL VarChar(500);

DECLARE src_files CURSOR FOR
SELECT t.TType, t.TName

FROM temp0 t, sys.syscatalog s // use SYS.SYSTABLE instead?

WHERE t.TName = s.tname
ORDER BY t.TType, t.TName;

load table temp0

FROM 'c:\\maustin\\projects\\pentaxcori\\sourcefiles.dat';

OPEN src_files;

LOOP
FETCH NEXT src_files INTO v_type, v_name;

IF SQLSTATE = err_notfound THEN LEAVE src_files_loop;

[Bruce Hay]

You must enclose the entire block of code within a BEGIN .. END block for
the cursor to remain; otherwise, the declaration goes out of scope
immediately. You will not be able to include an INPUT command within the
block, since that is an ISQL command and is not recognized by the server. If
you are going to use LOAD TABLE to load into a temporary table, you must use
CREATE GLOBAL TEMPORARY TABLE to create it, and must explicitly DROP it when
you are finished with it; otherwise, its definition remains in the catalog.
If the script will be re-used, then you can just create the table once and
test for its existence. If the other tables are used for intermediate
results, they can be declared within the block. The CREATE VARIABLE
statements should be DECLARE's inside the block.

if not exists (select * from SYS.SYSTABLE where table_name='temp0') then

CREATE GLOBAL TEMPORARY TABLE temp0 (TType Int, TName VarChar(50))
ON COMMIT PRESERVE ROWS;
end if;

begin

DECLARE LOCAL TEMPORARY TABLE temp1 (proc_no Integer);
DECLARE LOCAL TEMPORARY TABLE temp2 (patient_id Integer);

declare v_name VARCHAR(50);
declare v_type SMALLINT;
declare v_strSQL VarChar(500);

DECLARE src_files CURSOR FOR
SELECT t.TType, t.TName

FROM temp0 t, sys.syscatalog s // use SYS.SYSTABLE instead?

WHERE t.TName = s.tname
ORDER BY t.TType, t.TName;

load table temp0

FROM 'c:\\maustin\\projects\\pentaxcori\\sourcefiles.dat';

OPEN src_files;
// ...
close src_files;
end

"Michael J. Austin" <aust...@ohsu.edu> wrote in message
news:lV6n1$rbBH...@forums.sybase.com...

[Bruce Hay]

FWIW, here's another sample piece of code just to show the syntax for IF and
LEAVE:
begin
declare i int;
set i = 0;
lp: loop
if i >= 5 then leave lp end if;
if sqlstate <> '00000' then leave lp end if;
message 'current value of i is: ' || i to client;
set i = i + 1;
end loop
end

Note that "END IF" is required to complete the IF statement.

"Michael J. Austin" <aust...@ohsu.edu> wrote in message

news:LwNVp6v...@forums.sybase.com...