I am trying to write a stored procedure to insert into a table some records.

However I can not get the procedure to compile - I get a syntax error
"Error: A syntax error has occurred. (State:37000, Native Code: FFFFFF37)"
but I cannot find it

code is :
create procedure test()

DECLARE lv_tk CHAR(15),
ln_tk_count INT

declare get_timekeeper CURSOR FOR
SELECT tkinit
FROM timekeep
WHERE tktmdate IS NULL

foreach get_timekeeper INTO lv_tk
SELECT COUNT(*)
INTO lv_tk_count
FROM bo_calendar boc
WHERE NOT EXISTS ( SELECT 1
FROM timecard tc1
WHERE tc1.ttk = lv_tk
AND tc1.tworkdt = boc.date)
AND boc.date <= today
AND boc.day_name NOT IN ('Sunday','Saturday')
AND boc.bank_holiday IS NULL
AND boc.date > '01-05-'||YEAR(TODAY)

INSERT INTO BO_missing_timesheets
VALUES (tk.tkinit,ln_tk_count)

let lv_tk_count = 0
let lv_tk = NULL
END foreach
END PROCEDURE

Can anybody see the error?
Oh one thing I have noticed is that I get more syntax error if I put execution marks in.

Thanks
James

Hi James,

Please run the SPL with trace on:
Example:
DEFINE xxxxx ;
set debug file to "/tmp/myfile";
trace on;
......
......
trace off;
end procedure

Gustavo.

I'm doing this through DBACCESS, and it doesn't semm to like the set commands.

I have narrowed it down a bit, the code now looks like

create procedure test()

DEFINE lv_tk CHAR(15);
DEFINE ln_tk_count INT;

declare get_tk CURSOR FOR
SELECT tkinit FROM timekeep WHERE tktmdate IS NULL;

foreach get_tk INTO lv_tk
SELECT COUNT(*)
INTO lv_tk_count
FROM bo_calendar boc
WHERE NOT EXISTS ( SELECT 1
FROM timecard tc1
whERE tc1.ttk = lv_tk
AND tc1.tworkdt = boc.date)
AND boc.date <= today
AND boc.day_name NOT IN ('Sunday','Saturday')
AND boc.bank_holiday IS NULL
AND boc.date > '01-05-'||YEAR(TODAY);

INSERT INTO BO_missing_timesheets
VALUES (tk.tkinit,ln_tk_count);
commit ;

let lv_tk_count = 0;
let lv_tk = NULL;
END foreach;
END PROCEDURE

The error according to dbaccess is in the "declare get_tk CURSOR FOR" line

To find documentation that will help with your stored procedure (user-defined routine), check out this link:
http://www-306.ibm.com/software/data.../pubs/library/

That said, FOREACH declares and opens a cursor, fetches rows, then closes the cursor. Remove the DECLARE statement and try this:


foreach select tkinit into lv_tk from timekeep
where tktmdate IS NULL

SELECT COUNT(*)
INTO lv_tk_count
FROM bo_calendar boc ...

end foreach;

If you feel you need a named cursor, you may do that too, but with the FOREACH. See the documentation for your version of Informix.

Hi,
Please, ithink that you test change "declare get_tk CURSOR FOR", for "declare get_tk WITH HOLD CURSOR FOR", next recreate the SPL.

Gustavo.