Re: Command Line execution of sql statement
In article <3EDFA9D9.6010301@sqlcanada.com>, Michael Krzepkowski
<NOmichaelkSPAM@sqlcanada.com> writes
>John M wrote:
>
>>Hi all,
>> I am not sure how to do this, but what I want to do is have a cron
>>job execute a sql statement and dump the results in to a comma
>>delimited file. I know how to do everything but actually execute the
>>query. I have led to believe that this can be done with isql or
>>dbaccess. Any help would be appreciated.
>>
>dbaccess databasename scriptname
>
>You will have to re-direct stdout and stderr to avoid e-mails from cron.
And set DBDELIMITER as by default it's a pipe sign '|', not a comma.
Care is also needed if there are any commas in the actual data which is
being uloaded....
--
Five Cats
On Thu, 5 Jun 2003 22:02:22 +0100, Five Cats <cats_five@hotmail.com>
wrote:
Depending on the Informix version, he can use the REPLACE function in
the SQL extract.
>In article <3EDFA9D9.6010301@sqlcanada.com>, Michael Krzepkowski
><NOmichaelkSPAM@sqlcanada.com> writes
>>John M wrote:
>>
>>>Hi all,
>>> I am not sure how to do this, but what I want to do is have a cron
>>>job execute a sql statement and dump the results in to a comma
>>>delimited file. I know how to do everything but actually execute the
>>>query. I have led to believe that this can be done with isql or
>>>dbaccess. Any help would be appreciated.
>>>
>>dbaccess databasename scriptname
>>
>>You will have to re-direct stdout and stderr to avoid e-mails from cron.
>
>And set DBDELIMITER as by default it's a pipe sign '|', not a comma.
>Care is also needed if there are any commas in the actual data which is
>being uloaded....
Five Cats wrote:
> Michael Krzepkowski <NOmichaelkSPAM@sqlcanada.com> writes
>> John M wrote:
>>> I am not sure how to do this, but what I want to do is have a cron
>>> job execute a sql statement and dump the results in to a comma
>>> delimited file. I know how to do everything but actually execute the
>>> query. I have led to believe that this can be done with isql or
>>> dbaccess. Any help would be appreciated.
>>>
>> dbaccess databasename scriptname
>>
>> You will have to re-direct stdout and stderr to avoid e-mails from cron.
>
> And set DBDELIMITER as by default it's a pipe sign '|', not a comma.
Or UNLOAD TO "file" DELIMITER "," SELECT ...
> Care is also needed if there are any commas in the actual data which is
> being uloaded....
No; those will be escaped with a backslash, and any backslashes will
also be escaped with a backslash. The UNLOAD code takes care of that
for you. Actually, if (as hinted by John Carlson), you did a REPLACE
operation on the data (replacing commas with backslash comma and
backslash with double backslash), then the UNLOAD code would escape
the backslash with another one, and escape the comma with a backslash,
and so on. You'd end up with a lot of unnecessary and unwanted
backslashes in the output data.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/