Writing into Files from Stored Procedures

Hello Everybody, I'm trying to write the result set of a sql query into a file, from a stored procedure. My purpose is to make possible this : when an external program access to my database, a trigger is called. This trigger then backups the result set of the query into a file. I have no idea of how to write a file from a trigger or a stored procedure, so any help is welcome ;) PS : my database is Informix.

Hi, The first try would be using UNLOAD command, but i cant remember if its allowed in SPL... If not heck the "system" SPL (stored procedure language) command, it allows to run something on the S.O. shell maybe u can use it for that. Just ideas, should be other ways... On 5 mayo, 08:01, marc.aurele.seve...@gmail.com wrote: > Hello Everybody, > > I'm trying to write the result set of a sql query into a file, from a > stored procedure. > > My purpose is to make possible this : when an external program access > to my database, a trigger is called. This trigger then backups the > result set of the query into a file. > > I have no idea of how to write a file from a trigger or a stored > procedure, so any help is welcome ;) > > PS : my database is Informix.

On May 5, 6:32 am, "Enrique Ferreyra (Pachu)" <eferre...@gmail.com> wrote: > The first try would be using UNLOAD command, but i cant remember if > its allowed in SPL... UNLOAD is not part of SPL (more precisely, it is not an SQL statement understood by IDS; it is handled client-side by the programs that support the statement). > If not heck the "system" SPL (stored procedure language) command, it > allows to run something on the S.O. shell maybe u can use it for that. > > Just ideas, should be other ways... > > On 5 mayo, 08:01, marc.aurele.seve...@gmail.com wrote: > > I'm trying to write the result set of a sql query into a file, from a > > stored procedure. > > > My purpose is to make possible this : when an external program access > > to my database, a trigger is called. This trigger then backups the > > result set of the query into a file. > > > I have no idea of how to write a file from a trigger or a stored > > procedure, so any help is welcome ;) > > > PS : my database is Informix. There isn't any particularly easy way to do that. As Enrique suggested, if you aren't dealing with a temporary table, you can use the SYSTEM statement to run, say, dbaccess and unload the data that way. It isn't very elegant; it won't be very fast. And are you sure you want to trigger this every time someone selects from the table? What do you envisage as the action that triggers your triggered stored procedure? -=JL=-

> > There isn't any particularly easy way to do that. > > As Enrique suggested, if you aren't dealing with a temporary table, > you can use the SYSTEM statement to run, say, dbaccess and unload the > data that way.  It isn't very elegant; it won't be very fast.  And are > you sure you want to trigger this every time someone selects from the > table?  What do you envisage as the action that triggers your > triggered stored procedure? > I'd like to synchronise two very specific databases on two different servers. Network communication is not reliable, and network may sometimes be off. This synchronisation is a temporary solution (one or two weeks), so we cannot acquire an expensive software to do this. My idea : I put a trigger on INSERT, UPDATE, or DELETE statements in the first database. When someone inserts / updates / deletes data in the first database, the trigger exports the values into a file. When the network is available, the file is copied to the second server by FTP, and the second database is updated with the data. I guess there's probably a less dirty way to do this, but don't forget this is just a temporary solution.