Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Nov 2009
    Posts
    10

    Unanswered: help me on procedure syntax error...

    please help me ..i got syntax error when creating the procedure given below....


    -------------------------------------------------------------------------------
    CREATE PROCEDURE saveTableDetails1(var_table_name char(20),var_row_id char(20),var_action_occured char(20))

    DEFINE var_pkid_name char(20);
    select first 1 colname from syscolumns into var_pkid_name where tabid=(select tabid from systables where tabname=var_table_name);
    # ^
    # 201: A syntax error has occurred.
    #

    DEFINE var_column_name char(20);
    DEFINE var_column_length INT;
    DEFINE var_i INT;
    DEFINE var_ar_pk_id char(20);
    DEFINE var_ar_alert_id char(20);
    DEFINE var_ar_l_table_name char(20);
    DEFINE var_ar_l_field char(20);
    DEFINE var_ar_r_table_name char(20);
    DEFINE var_ar_r_field char(20);
    DEFINE var_al_when char(20);
    DEFINE var_al_numberof_days char(20);

    LET i = 0;


    select MAX(colno) from syscolumns into var_column_length where tabid=(select tabid from systables where tabname=var_table_name);


    WHILE i < var_column_length
    select skip i first 1 colname from syscolumns into var_column_name where tabid=(select tabid from systables where tabname=var_table_name) ;
    SELECT ar_pk_id ,ar_alert_id,ar_l_table_name,ar_l_field ,ar_r_table_name ,ar_r_field INTO var_ar_pk_id,var_ar_alert_id,var_ar_l_table_name,v ar_ar_l_field,var_ar_r_field FROM bam_alert_rule where (ar_l_table_name=var_table_name and ar_l_field= var_column_name) or (ar_r_table_name=var_table_name and ar_r_field= var_column_name) ;
    IF( var_ar_pk_id is not null and var_ar_alert_id is not null ) then
    select al_when,al_numberof_days from bam_alerts into var_al_when ,var_al_numberof_days where al_pk_id=ar_alert_id;
    insert into bam_alert_table_details(bat_pk_id,bat_alert_id,bat _table_name,bat_field_name,bat_row_id,bat_alert_st atus,bat_name_pkid,bat_action_occured,bam_alert_wh en,bam_no_of_days)values(bam_changed_table_details _seq.NEXTVAL,var_ar_alert_id,var_table_name ,var_column_name,var_row_id ,'Pending',var_bat_pkid_name,var_action_occured,va r_al_when,var_al_numberof_days);
    END IF;
    LET i = i+1;
    END WHILE;
    END PROCEDURE;

    ----------------------------------------------------------------------------------------------



    please any one help me.....

  2. #2
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    select first 1 colname into var_pkid_name from syscolumns where tabid=(select tabid from systables where tabname=var_table_name);
    ________________________________________
    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
    10
    thanks for ur kind help.....now its working...

  4. #4
    Join Date
    Nov 2009
    Posts
    10
    please help me on this part of code...is there any problem for using a variable in skip...please help me..


    WHILE i < var_column_length
    select skip i first 1 colname from syscolumns into var_column_name where tabid=(select tabid from systables where tabname=var_table_name) ;
    SELECT ar_pk_id ,ar_alert_id,ar_l_table_name,ar_l_field ,ar_r_table_name ,ar_r_field INTO var_ar_pk_id,var_ar_alert_id,var_ar_l_table_name,v ar_ar_l_field,var_ar_r_field FROM bam_alert_rule where (ar_l_table_name=var_table_name and ar_l_field= var_column_name) or (ar_r_table_name=var_table_name and ar_r_field= var_column_name) ;
    IF( var_ar_pk_id is not null and var_ar_alert_id is not null ) then
    select al_when,al_numberof_days from bam_alerts into var_al_when ,var_al_numberof_days where al_pk_id=ar_alert_id;
    insert into bam_alert_table_details(bat_pk_id,bat_alert_id,bat _table_name,bat_field_name,bat_row_id,bat_alert_st atus,bat_name_pkid,bat_action_occured,bam_alert_wh en,bam_no_of_days)values(bam_changed_table_details _seq.NEXTVAL,var_ar_alert_id,var_table_name ,var_column_name,var_row_id ,'Pending',var_bat_pkid_name,var_action_occured,va r_al_when,var_al_numberof_days);
    END IF;
    LET i = i+1;
    END WHILE;

  5. #5
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    this syntax probably isn't supported...
    try using dynamic statments :
    EXECUTE IMMEDIATE
    Examples of the EXECUTE IMMEDIATE Statement
    ________________________________________
    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).
    ________________________________________

  6. #6
    Join Date
    Nov 2009
    Posts
    1
    I accept with information:WHILE i < var_column_length
    select skip i first 1 colname from syscolumns into var_column_name where tabid=(select tabid from systables where tabname=var_table_name) ;
    where (ar_l_table_name=var_table_name and ar_l_field= var_column_name) or (ar_r_table_name=var_table_name and ar_r_field= var_column_name) ;
    IF( var_ar_pk_id is not null and var_ar_alert_id is not null ) then
    select al_when,al_numberof_days from bam_alerts into var_al_when ,var_al_numberof_days where al_pk_id=ar_alert_id;
    insert into bam_alert_table_details(bat_pk_id,bat_alert_id,bat _table_name,bat_field_name,bat_row_id,bat_alert_st atus,bat_name_pkid,bat_action_occured,bam_alert_wh en,bam_no_of_days)

  7. #7
    Join Date
    Nov 2009
    Posts
    10
    thanks for all ur helps....

    please currect this query..

    LET var_query1="select ba_action_id into var_ba_action_id from bam_alert_actions where ba_alert_id=var_ar_alert_id and ba_action_id like "||var_action_occured||"";

    var_action_occured--->is a variable...is that the proper way that we can



    the whole procedure given below...


    CREATE PROCEDURE savetabledetails1(var_table_name char(20),var_row_id char(20),var_action_occured char(20))

    DEFINE var_pkid_name char(20);
    DEFINE var_column_name char(20);
    DEFINE var_column_length char(20);
    DEFINE i int;
    DEFINE var_ar_pk_id char(20);
    DEFINE var_ar_alert_id char(20);
    DEFINE var_al_when char(20);
    DEFINE var_al_numberof_days char(20);
    DEFINE var_ba_action_id char(20);
    DEFINE var_query lvarchar(2000);
    DEFINE var_query1 lvarchar(2000);
    LET i = 0;
    LET var_query="select colname from syscolumns where tabid=(select tabid from systables where tabname=?)";
    select MAX(colno) into var_column_length from syscolumns where tabid=(select tabid from systables where tabname=var_table_name);
    select first 1 colname into var_pkid_name from syscolumns where tabid=(select tabid from systables where tabname=var_table_name);

    PREPARE var_query_stmt FROM var_query;
    DECLARE var_query_cur cursor FOR var_query_stmt;
    OPEN var_query_cur USING var_table_name;


    WHILE (i<var_column_length)
    FETCH var_query_cur INTO var_column_name;

    FOREACH SELECT ar_pk_id ,ar_alert_id INTO var_ar_pk_id,var_ar_alert_id FROM bam_alert_rule where (ar_l_table_name=var_table_name and ar_l_field= var_column_name) or (ar_r_table_name=var_table_name and ar_r_field= var_column_name)


    IF( var_ar_pk_id is not null and var_ar_alert_id is not null ) then
    select ba_action_id into var_ba_action_id from bam_alert_actions where (ba_alert_id=var_ar_alert_id and ba_action_id like var_action_occured);
    --LET var_query1="select ba_action_id into var_ba_action_id from bam_alert_actions where ba_alert_id=var_ar_alert_id and ba_action_id like "||var_action_occured||"";
    --EXECUTE IMMEDIATE var_query1;
    --if(var_ba_action_id is not null) then
    select al_when,al_numberof_days into var_al_when ,var_al_numberof_days from bam_alerts where al_pk_id=var_ar_alert_id;
    insert into bam_alert_table_details(bat_pk_id,bat_alert_id,bat _table_name,bat_field_name,bat_row_id,bat_alert_st atus,bat_name_pkid,bat_action_occured,bam_alert_wh en,bam_no_of_days)values(bam_changed_table_details _seq.NEXTVAL,var_ar_alert_id,var_table_name,var_co lumn_name,var_row_id ,'Pending',var_pkid_name,var_action_occured,var_al _when,var_al_numberof_days);
    --end if;
    END IF;
    END FOREACH;
    LET i=i+1;
    END WHILE;
    CLOSE var_query_cur;
    FREE var_query_cur;
    FREE var_query_stmt;
    END PROCEDURE;

  8. #8
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Hi, you're using ESQL/C statements but you can only use SPL and SQL statements in a stored procedure. The invalid statements are marked in red.
    Code:
    CREATE PROCEDURE savetabledetails1(var_table_name char(20),
                                        var_row_id char(20),
                                        var_action_occured char(20))
    
    DEFINE var_pkid_name char(20);
    DEFINE var_column_name char(20);
    DEFINE var_column_length char(20);
    DEFINE i int;
    DEFINE var_ar_pk_id char(20);
    DEFINE var_ar_alert_id char(20);
    DEFINE var_al_when char(20);
    DEFINE var_al_numberof_days char(20);
    DEFINE var_ba_action_id char(20);
    DEFINE var_query lvarchar(2000);
    DEFINE var_query1 lvarchar(2000);
    
        LET i = 0;
        LET var_query = "select colname" ||
                    " from syscolumns where tabid =" ||
                    " (select tabid from systables where tabname = ?)";
    
        select MAX(colno) into var_column_length
        from syscolumns
        where tabid = (select tabid from systables where tabname = var_table_name);
    
        select first 1 colname into var_pkid_name
        from syscolumns
        where tabid = (select tabid from systables where tabname = var_table_name);
    
        PREPARE var_query_stmt
        FROM var_query;
        DECLARE var_query_cur cursor FOR var_query_stmt;
        OPEN var_query_cur USING var_table_name;
    
        WHILE (i<var_column_length)
            FETCH var_query_cur INTO var_column_name;
    
            FOREACH SELECT ar_pk_id,ar_alert_id INTO var_ar_pk_id,var_ar_alert_id
                FROM bam_alert_rule
                where (ar_l_table_name = var_table_name
                and ar_l_field = var_column_name)
                or (ar_r_table_name = var_table_name
                and ar_r_field = var_column_name)
    
    
                IF( var_ar_pk_id is not null and var_ar_alert_id is not null ) then
                    select ba_action_id into var_ba_action_id
                    from bam_alert_actions
                    where (ba_alert_id = var_ar_alert_id
                    and ba_action_id like var_action_occured);
                    
                    select al_when,al_numberof_days
                    into var_al_when ,var_al_numberof_days
                    from bam_alerts where al_pk_id = var_ar_alert_id;
    
                    insert into bam_alert_table_details(
                        bat_pk_id,bat_alert_id,bat _table_name,bat_field_name,
                        bat_row_id,bat_alert_st atus,bat_name_pkid,
                        bat_action_occured,bam_alert_wh en,
                        bam_no_of_days)
                        values(bam_changed_table_details _seq.NEXTVAL,
                        var_ar_alert_id,var_table_name,var_co lumn_name,
                        var_row_id ,'Pending',var_pkid_name,var_action_occured,
                        var_al _when,var_al_numberof_days);
                END IF;
            END FOREACH;
            LET i = i+1;
        END WHILE;
        CLOSE var_query_cur;
        FREE var_query_cur;
        FREE var_query_stmt;
    END PROCEDURE;
    Also dynamically constructed and prepared SQL statements are impossible in stored procedures.
    Use the FOREACH statement to get the behaviour of a cursor, so substitute the green (and red) marked text with:
    Code:
    FOREACH SELECT colno, colname INTO i, var_column_name
        FROM syscolumns c, systables t
        WHERE c.tabid = t.tabid
        AND tabname = var_table_name
        ORDER BY colno
    
        ...
    END FOREACH
    Regards,
    Hans
    Last edited by Tyveleyn; 12-13-09 at 18:43.

  9. #9
    Join Date
    Nov 2009
    Posts
    10
    but its working properly..only problem is at


    LET var_query1="select ba_action_id into var_ba_action_id from bam_alert_actions where ba_alert_id=var_ar_alert_id and ba_action_id like "||var_action_occured||"";
    ...

  10. #10
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    So you're using the newest server version then... I'm only acquainted with versions up to 10.00. Nevertheless it appears to me that my suggestion involves less processing and thus possible less execution time.
    But the parsing problem you've got is a universal one: since var_action_occured is a character variable you've got to quote it inside your SQL statement. Like:
    Code:
    "select ba_action_id into var_ba_action_id from bam_alert_actions where ba_alert_id = var_ar_alert_id and ba_action_id like '" || trim(var_action_occured) || "'";
    Regards,
    Hans

  11. #11
    Join Date
    Nov 2009
    Posts
    10
    yes ..i am using informix version 11.5...in that stil i am geting an error like...


    # ^
    # 667: Variable(var_ba_action_id) not declared.
    #

    and the complete procedure is ...

    CREATE PROCEDURE savetabledetails1(var_table_name char(20),var_row_id char(20),var_action_occured char(20))

    DEFINE var_pkid_name char(20);
    DEFINE var_column_name char(20);
    DEFINE var_column_length char(20);
    DEFINE i int;
    DEFINE var_ar_pk_id char(20);
    DEFINE var_ar_alert_id char(20);
    DEFINE var_al_when char(20);
    DEFINE var_al_numberof_days char(20);

    DEFINE var_ba_action_id char(20);

    DEFINE var_query lvarchar(2000);
    DEFINE var_query1 lvarchar(2000);
    LET i = 0;
    LET var_query="select colname from syscolumns where tabid=(select tabid from systables where tabname=?)";
    select MAX(colno) into var_column_length from syscolumns where tabid=(select tabid from systables where tabname=var_table_name);
    select first 1 colname into var_pkid_name from syscolumns where tabid=(select tabid from systables where tabname=var_table_name);

    PREPARE var_query_stmt FROM var_query;
    DECLARE var_query_cur cursor FOR var_query_stmt;
    OPEN var_query_cur USING var_table_name;


    WHILE (i<var_column_length)
    FETCH var_query_cur INTO var_column_name;

    FOREACH SELECT ar_pk_id ,ar_alert_id INTO var_ar_pk_id,var_ar_alert_id FROM bam_alert_rule where (ar_l_table_name=var_table_name and ar_l_field= var_column_name) or (ar_r_table_name=var_table_name and ar_r_field= var_column_name)


    IF( var_ar_pk_id is not null and var_ar_alert_id is not null ) then

    --select ba_action_id into var_ba_action_id from bam_alert_actions where (ba_alert_id=var_ar_alert_id and ba_action_id like var_action_occured);

    LET var_query1="select ba_action_id into var_ba_action_id from bam_alert_actions where ba_alert_id=var_ar_alert_id and ba_action_id like '" || trim(var_action_occured) || "' ";
    EXECUTE IMMEDIATE var_query1;
    if(var_ba_action_id is not null) then
    select al_when,al_numberof_days into var_al_when ,var_al_numberof_days from bam_alerts where al_pk_id=var_ar_alert_id;
    insert into bam_alert_table_details(bat_pk_id,bat_alert_id,bat _table_name,bat_field_name,bat_row_id,bat_alert_st atus,bat_name_pkid,bat_action_occured,bam_alert_wh en,bam_no_of_days)values(bam_changed_table_details _seq.NEXTVAL,var_ar_alert_id,var_table_name,var_co lumn_name,var_row_id ,'Pending',var_pkid_name,var_action_occured,var_al _when,var_al_numberof_days);
    end if;
    END IF;
    END FOREACH;
    LET i=i+1;
    END WHILE;
    CLOSE var_query_cur;
    FREE var_query_cur;
    FREE var_query_stmt;
    END PROCEDURE;

    why that error is coming....please help me out...its very urgent..
    Last edited by yassar; 12-14-09 at 07:35.

  12. #12
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    The construction where inside the SQL query the assignment with 'INTO' is coded is invalid!
    Code:
    LET var_query1 = "select ba_action_id into var_ba_action_id from bam_alert_actions where ba_alert_id = var_ar_alert_id and ba_action_id like '" || trim(var_action_occured) || "'";
    As far as I know the only valid statements inside a prepared SQL statement are strictly Informix-SQL except for the argument placeholder '?'.
    The assignment of an output value into a variable should be done by the EXECUTE statement.

    Regards

  13. #13
    Join Date
    Nov 2009
    Posts
    10
    thanks for all ur help....

    stil hav a problem....
    CREATE PROCEDURE savetabledetails1(var_table_name char(20),var_row_id char(20),var_action_occured char(20))

    DEFINE var_pkid_name char(20);
    DEFINE var_column_name char(20);
    DEFINE var_column_length char(20);
    DEFINE i int;
    DEFINE var_ar_pk_id char(20);
    DEFINE var_ar_alert_id char(20);
    DEFINE var_al_when char(20);
    DEFINE var_al_numberof_days char(20);
    DEFINE var_ba_pk_id char(20);
    DEFINE var_query lvarchar(2000);
    DEFINE var_query1 lvarchar(2000);
    LET i = 0;
    LET var_query="select colname from syscolumns where tabid=(select tabid from systables where tabname=?)";
    select MAX(colno) into var_column_length from syscolumns where tabid=(select tabid from systables where tabname=var_table_name);
    select first 1 colname into var_pkid_name from syscolumns where tabid=(select tabid from systables where tabname=var_table_name);

    PREPARE var_query_stmt FROM var_query;
    DECLARE var_query_cur cursor FOR var_query_stmt;
    OPEN var_query_cur USING var_table_name;


    WHILE (i<var_column_length)
    FETCH var_query_cur INTO var_column_name;

    FOREACH SELECT ar_pk_id ,ar_alert_id INTO var_ar_pk_id,var_ar_alert_id FROM bam_alert_rule where (ar_l_table_name=var_table_name and ar_l_field= var_column_name) or (ar_r_table_name=var_table_name and ar_r_field= var_column_name)
    --insert into bam_alert_table_details(bat_pk_id,bat_alert_id) values(bam_changed_table_details_seq.NEXTVAL,var_r ow_id);

    IF( var_ar_pk_id is not null and var_ar_alert_id is not null ) then

    --select ba_pk_id into var_ba_pk_id from bam_alert_actions where (ba_alert_id=var_ar_alert_id and ba_action_id like "||var_action_occured||");

    "which one i hav to use here..??
    statement above or below...??
    LET var_query1="select ba_pk_id from bam_alert_actions where ba_alert_id=? and ba_action_id like '||var_action_occured||'";


    PREPARE var_query_stmt1 FROM var_query1;
    DECLARE var_query_cur1 cursor FOR var_query_stmt1;
    OPEN var_query_cur1 USING var_ar_alert_id;
    FETCH var_query_cur1 INTO var_ba_pk_id;



    if(var_ba_pk_id is not null) then

    select al_when,al_numberof_days into var_al_when ,var_al_numberof_days from bam_alerts where al_pk_id=var_ar_alert_id;
    insert into bam_alert_table_details(bat_pk_id,bat_alert_id,bat _table_name,bat_field_name,bat_row_id,bat_alert_st atus,bat_name_pkid,bat_action_occured,bam_alert_wh en,bam_no_of_days)values(bam_changed_table_details _seq.NEXTVAL,var_ar_alert_id,var_table_name,var_co lumn_name,var_row_id ,'Pending',var_pkid_name,var_action_occured,var_al _when,var_al_numberof_days);
    end if;
    CLOSE var_query_cur1;
    FREE var_query_cur1;
    FREE var_query_stmt1;

    END IF;
    END FOREACH;
    LET i=i+1;
    END WHILE;
    CLOSE var_query_cur;
    FREE var_query_cur;
    FREE var_query_stmt;
    END PROCEDURE;

    if i use extra curser for this as second stmnt above in red..i am geting an error like...

    execute procedure savetabledetails1("bam_alerts","854","&#37;A%");
    #^
    # 696: Variable (var_ba_pk_id) has undefined value.
    #


    please help me on this.....its very urgent...
    Last edited by yassar; 12-23-09 at 04:47.

  14. #14
    Join Date
    Nov 2009
    Posts
    10
    somebody help me please on above issue....

  15. #15
    Join Date
    Jan 2010
    Posts
    2
    As far as I know the only valid statements inside a prepared SQL statement are strictly Informix-SQL except for the argument placeholder
    --------------------
    quang cao online | quang cao

Posting Permissions

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