Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2011
    Posts
    2

    Question Unanswered: Problem with exporting data to flat file

    Hi everybody.

    I am new with Informix and have little experience so I will explan the best I can:

    I need to export some information with certain format to a flat file by the use of Triggers and Stored Procedures, sadly I get just a few rows and never the entire table.

    There is a job that fills some tables and I fill my table through Triggers into those tables, so maybe the problem is that I insert rows to my table just before I want to export it to a flat file. I have tried creating views, creating temporary tables, moving the execution of the stored proc to another table (while I update a row of this one), cloning the table I need to export, changing the isolation level, changing the table's lock mode and I have not gotten all the rows of that table.

    What I do is this:
    CREATE TRIGGER trigger_name INSERT ON last_table
    REFERENCING new AS new
    FOR EACH ROW (
    INSERT INTO my_table ...
    .
    .
    .
    INSERT INTO my_table ...
    )
    AFTER (
    EXECUTE PROCEDURE export_table()
    );


    CREATE PROCEDURE export_table()
    DEFINE vsql char(200);
    LET vsql = 'echo " unload to flatfile.unl '|| "delimiter '|' "||
    '" > sqlfile.sql';
    SYSTEM vsql;
    LET vsql = 'echo "'||
    "select * from my_table"||
    '" >> sqlfile.sql';
    SYSTEM vsql;
    LET vsql = "dbaccess dbname sqlfile.sql";
    SYSTEM vsql;
    END PROCEDURE;


    I am using IDS Version 10.00.FC5. Any help would be great because I ran out of ideas.

    Thank you in advance. Regards.

  2. #2
    Join Date
    Jan 2011
    Posts
    2

    New note...

    If I try to insert a record into my last table directly from the console i get this message:

    "668: System command cannot be executed, or command failed with command exit status
    as ISAM error number.

    255: Not in transaction."

    Any ideas?

    Regards.

  3. #3
    Join Date
    Jan 2011
    Posts
    1
    Maybe instead of selecting the values from the table and outputting them to a file, you could use trace to "print" them to the file?

Tags for this Thread

Posting Permissions

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