Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Posts
    1

    Unanswered: Dynamic sql in informix stored proecedure

    i want to create a procedure in informix in which table name is in a variable (strTable) and i want to retrieve the records from that table. Later i will link this procedure to crystal report to create a report of the records.

    eg.
    Define strTable Varchar(30);
    Let strTable = "Employee";
    Select * from strTable;

    I tried dynamic sql statements "prepare" and "execute" in the procedure but it didn't work.
    eg.
    CREATE PROCEDURE tempProc()
    RETURNING
    VarChar(30);
    DEFINE tmpstr VARCHAR(255);
    Define rgn varchar(255);
    Let tmpstr='Employee';

    foreach curTemp For
    prepare s1 from "Select Region_name from ?"
    execute s1 using tmpstr
    WITH RESUME;
    END FOREACH
    END PROCEDURE;

    pls help me if u know any solution to this problem.
    Regards,
    Puneet
    (puneetjaggi@indiatimes.com)

  2. #2
    Join Date
    Jan 2003
    Posts
    30

    SPL does not support Dynamic SQL

    Hi,

    Informix Stored procedure language does not support Dynamic SQL.

    You have to write a C-program and use a SQLDA or Descriptor area.

    You can also use shell scripting and use environment variable in all inline sql statements.

    Eg

    MYTABLE="sysusers"
    dbaccess -e somedb <<EOF

    unload to /tmp/$MYTABLE.unl
    select * from $MYTABLE;

    EOF

Posting Permissions

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