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.

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.

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?