Export to ASCII file
I am trying to export a table to an ASCII file in a stored procedure on a
UNIX box.
I was using :
Unload to 'file'
select * from my_table
where field = "123";
Which causes a syntax error.
Is 'unload' a command that i can use in stored procedures, or is it only
useable in the 'dbacess' program?
Stuart wrote:
>
> I am trying to export a table to an ASCII file in a stored procedure on a
> UNIX box.
>
> I was using :
>
> Unload to 'file'
> select * from my_table
> where field = "123";>
> Which causes a syntax error.
>
> Is 'unload' a command that i can use in stored procedures, or is it only
> useable in the 'dbacess' program?
Yes, you are right. UNLOAD is a feature of dbaccess and you can't
use it in SPL or 4GL programs. If your SP is not used very often
then you can work around by running a script on the UNIX box from
the SP, which would do something like
dbaccess $DBNAME << sseccabd
UNLOAD TO $FILE SELECT * FROM my_table WHERE field = $VARsseccabd
But make sure that your script sets absolutely all the variables
it needs, including the path, INFORMIXDIR, INFORMIXSERVER and the
whole shebang.
It's not a great solution for an SP that gets used a lot because
of the overhead of invoking the script and dbaccess.
--
So Archimedes Plutonium is tied to a stake in the backyard, and
sleeping in his kennel. K*bo tiptoes up (carrying a dustbin lid)
and measures the length of the chain Arch is tied to, then marks
a radius on the ground...
Andrew Pearson wrote:
> Stuart wrote:
> >
> > I am trying to export a table to an ASCII file in a stored procedure on a
> > UNIX box.
> >
> > I was using :
> >
> > Unload to 'file'
> > select * from my_table
> > where field = "123";> >
> > Which causes a syntax error.
> >
> > Is 'unload' a command that i can use in stored procedures, or is it only
> > useable in the 'dbacess' program?
>
> Yes, you are right. UNLOAD is a feature of dbaccess and you can't
> use it in SPL
Correct.
> or 4GL programs.
Incorrect - both LOAD and UNLOAD are supported by I4GL (and also ISQL).
> If your SP is not used very often
> then you can work around by running a script on the UNIX box from
> the SP, which would do something like
>
> dbaccess $DBNAME << sseccabd
> UNLOAD TO $FILE SELECT * FROM my_table WHERE field = $VAR> sseccabd
>
> But make sure that your script sets absolutely all the variables
> it needs, including the path, INFORMIXDIR, INFORMIXSERVER and the
> whole shebang.
>
> It's not a great solution for an SP that gets used a lot because
> of the overhead of invoking the script and dbaccess.
The biggest problem is if the server is on MachineA and the application is on
MachineB, the unload file will be created on MachineA and not and MachineB.
--
Jonathan Leffler (jleffler@earthlink.net, jleffler@informix.com)
Guardian of DBD::Informix 1.00.PC1 -- see http://www.cpan.org/
#include <disclaimer.h>
Is there an equivalent to UNLOAD that can be used in a SP to produce a flat file?
Stuart wrote: > Is there an equivalent to UNLOAD that can be used in a SP to produce a flat > file? No. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"