Thread: Problem: psql -c works but psql -f fails when executed from a Perl script using system().

I want to execute a sql statement from Perl using system() and psql.
Using -c it works like a charm. For some reason it fails to execute them
when I use the -f option (and <) . Excuting the command given to system()
from a Unix prompt (sh and/or bash) works however like a charm. According to
perldoc -f system the command does the following "the entire argument is
passed to the system's command shell for parsing (this is "/bin/sh -c" on
Unix platforms.". When I manually execute the command using sh -c'command"
it also works correctly.

What works is:
----------------
/usr/local/pgsql/bin/psql -e -d dwhtest -U username  -h server <
/tmp/filldatabasetest.sql
sh -c "/usr/local/pgsql/bin/psql -e -d dwhtest -U username  -h server <
/tmp/filldatabasetest.sql"
a prog.sh file which contains the line "usr/local/pgsql/bin/psql -e -d
dwhtest -U username  -h server < /tmp/filldatabasetest.sql"

What fails is, piece of the perlcode:
-------------
my $cmd="
/usr/local/pgsql/bin/psql -e -d dwhtest -U username  -h server <
/tmp/filldatabasetest.sql";
system("$cmd");

contents file /tmp/filldatabasetest.sql
-------------------------------------
 INSERT INTO fieldsize_test values ( 2,'some text ');

Any other statement like "delete from fieldsize_test;" also fails.

I hope anyone can explain me why it fails and/or what I'm doing wrong.
Personally I'm baffled.

Remark1: I did found out that there are issues with -f and that < is better
for large files with sql statements. Mine can be a few MB's in size.
Remark2: I can't -c because of shell buffer size limits.

Jan

Found the little critter. made a mistake. "Luke, use the close function on
time, always".
File with sql statements was created on the fly but was not closed before
the psql statement was executed by system(). Exit code of system was 0 which
indicated, all is well. Well it wasn't. Grmmph.

Jan


"Jan Bessels" <jbessels@planet.nl> wrote in message
news:ap8rvt$ib7$1@reader12.wxs.nl...
> I want to execute a sql statement from Perl using system() and psql.
> Using -c it works like a charm. For some reason it fails to execute them
> when I use the -f option (and <) . Excuting the command given to system()
> from a Unix prompt (sh and/or bash) works however like a charm. According
to
> perldoc -f system the command does the following "the entire argument is
> passed to the system's command shell for parsing (this is "/bin/sh -c" on
> Unix platforms.". When I manually execute the command using sh -c'command"
> it also works correctly.
>
> What works is:
> ----------------
> /usr/local/pgsql/bin/psql -e -d dwhtest -U username  -h server <
> /tmp/filldatabasetest.sql
> sh -c "/usr/local/pgsql/bin/psql -e -d dwhtest -U username  -h server <
> /tmp/filldatabasetest.sql"
> a prog.sh file which contains the line "usr/local/pgsql/bin/psql -e -d
> dwhtest -U username  -h server < /tmp/filldatabasetest.sql"
>
> What fails is, piece of the perlcode:
> -------------
> my $cmd="
> /usr/local/pgsql/bin/psql -e -d dwhtest -U username  -h server <
> /tmp/filldatabasetest.sql";
> system("$cmd");
>
> contents file /tmp/filldatabasetest.sql
> -------------------------------------
>  INSERT INTO fieldsize_test values ( 2,'some text ');
>
> Any other statement like "delete from fieldsize_test;" also fails.
>
> I hope anyone can explain me why it fails and/or what I'm doing wrong.
> Personally I'm baffled.
>
> Remark1: I did found out that there are issues with -f and that < is
better
> for large files with sql statements. Mine can be a few MB's in size.
> Remark2: I can't -c because of shell buffer size limits.
>
> Jan
>
>
>
>