Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2005
    Posts
    7

    Unanswered: Stored Procedure error - informix DB

    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

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780
    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.

  3. #3
    Join Date
    Jul 2005
    Posts
    7
    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

  4. #4
    Join Date
    Feb 2005
    Posts
    43
    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.

  5. #5
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •