Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2009
    Posts
    14

    Unanswered: Issues with Cursor in informix procedure

    I have a simple procedure with a cursor and am running into syntax error at the declaration of cursor line.

    Sample code is below

    CREATE PROCEDURE test()

    BEGIN
    DEFINE ent_type_id INTEGER;

    CREATE TEMP Table entities(name varchar(64));

    DECLARE ent_types CURSOR FOR
    Select ent_type_id
    from ent_type_def
    for read only;

    Open ent_types;
    FETCH ent_types into :ent_type_id;

    WHILE SQLCODE == 0
    INSERT INTO entities
    select name
    from ent_type_def
    where ent_type_id = :ent_type_id;

    FETCH ent_types into :ent_type_id;
    END WHILE;

    Select name
    from entities;

    END;
    END PROCEDURE;

  2. #2
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    1) What IDS version?
    2) You are written an ESQL/C style over SPL ... never will work...

    If you work with IDS before 11.50 , you must use FOREACH can't use PREPARE+DECLARE+FETCH .

    If you use IDS 11.50 this is the right syntax, this command already allowed, check the manual: Usage.

    your code with corrections...
    Code:
    #Using DECLARE (works only in IDS 11.50)
    #tested in IDS 11.50 UC5
    
    create temp table ent_type_def (name char(10), ent_type_id int);
    Temporary table created.                                        
    
    CREATE PROCEDURE test()
    
    BEGIN
    DEFINE v_ent_type_id INTEGER;;
    
    CREATE TEMP Table entities(name varchar(64));;
    
    PREPARE s_ent_types FROM "Select ent_type_id from ent_type_def for read only" ;;
    DECLARE c_ent_types CURSOR FOR s_ent_types;;
    
    Open c_ent_types;;
    FETCH c_ent_types into v_ent_type_id;;
    
    WHILE SQLCODE == 0
    INSERT INTO entities
    select name
    from ent_type_def
    where ent_type_id = v_ent_type_id;;
    
    FETCH c_ent_types into v_ent_type_id;;
    END WHILE;;
    
    --For what is this SELECT???
    --Select name from entities;
    
    END;;
    END PROCEDURE;
    Routine created.
    
    ;
    
    Database closed.
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  3. #3
    Join Date
    Nov 2009
    Posts
    14
    Thanks for the quick reply. we are using IDS 10 and probably that is the reason i am having problem. And also the last select is the one returning back from the procedure.

    So i guess i will have to use FOREACH since we are using IDS 10, or i will have to follow your logic right?

    Thanks again.

  4. #4
    Join Date
    Nov 2009
    Posts
    14
    Can you please tell me how to use foreach in this example and also how do i grant & execute this procedure

  5. #5
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    manual v10 : FOREACH
    manual v11 : FOREACH
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

Posting Permissions

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