Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2005
    Posts
    4

    Unanswered: Informix 10 with stored procedure with dynamic sql

    Hi, i am a new bee in Informix SQL.

    I have an error when i try to run my stored procedure : it give me a syntax error but i don't see why!

    CREATE PROCEDURE recupmaxdate (db_srv char(14), stationnumber char(2))
    RETURNING char(15);
    DEFINE resultdate char(15);
    DEFINE form_sql CHAR(250);
    LET form_sql= "select max(datearrival) into resultdate FROM " ||db_srv|| ";
    prepare xsql from form_sql;
    declare xcur cursor for xsql;
    OPEN xcur;
    FETCH xcur into resultdate;
    CLOSE xcur;
    FREE xcur;
    FREE xsql;
    RETURN resultdate;
    END PROCEDURE;


    Does anyone has any idea?
    Help Please !!!

  2. #2
    Join Date
    Sep 2010
    Location
    Germany, Brunswick
    Posts
    55
    The following is wrong:
    LET form_sql= "select max(datearrival) into resultdate FROM " ||db_srv|| ";
    It should be:
    LET form_sql= "select max(datearrival) into resultdate FROM " ||db_srv;
    or
    LET form_sql= "select max(datearrival) into resultdate FROM " ||db_srv|| "";

    If you run the query in dbaccess you get error:
    282: Found a quote for which there is no matching quote.
    and if you press "modify", the cursor jumps to the position of the error.
    WilhelmW

  3. #3
    Join Date
    Oct 2005
    Posts
    4
    Hi informixWilli.
    Thanks for your help but the problem is not resolved.
    i have replaced the line :

    LET form_sql= "select max(datearrival) into resultdate FROM " ||db_srv|| ";

    by this one :

    LET form_sql= "select max(datearrival) FROM " ||db_srv;

    So :

    CREATE PROCEDURE recupmaxdate (db_srv char(14), stationnumber char(2))
    RETURNING char(15);
    DEFINE resultdate char(15);
    DEFINE form_sql CHAR(250);
    LET form_sql= "select max(datearrival) FROM " ||db_srv;
    prepare xsql from form_sql;
    declare xcur cursor for xsql;
    OPEN xcur;
    FETCH xcur into resultdate;
    CLOSE xcur;
    FREE xcur;
    FREE xsql;
    RETURN resultdate;
    END PROCEDURE;

    But i always have the following error : 201 A syntax error has occurred et the cursor jumps to the line :

    prepare xsql from form_sql;

  4. #4
    Join Date
    Oct 2005
    Posts
    4
    I think that "PREPARE xxx FROM xxx" is not authorized!
    Is it possible?
    I have IDS10.0.

  5. #5
    Join Date
    Oct 2005
    Posts
    4

    POST CLOSED without success!

    For information :

    "Dynamic SQL in SP is introduced in IDS 11.5; and looks like you are trying using 10.0. That explains why your syntax is not recognized. "

    It's a response from a expert of IBM.

  6. #6
    Join Date
    Sep 2010
    Location
    Germany, Brunswick
    Posts
    55
    Thats right!

    You should think about migration to 11.50/11.70 because there are a lot of new features and 10.00 is out of support!
    WilhelmW

Posting Permissions

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