running sql scripts from perl script

I am not sure if this probleem is primarily perl or MySQL, so I'll try
here first. With any luck, there is someone else here who uses perl to
run sql scripts against a MySQL DB.

First, the following is a command that I can run from the Windows
console, and it works as expected.

mysql -u my_uid --password=my_pword --database=my_db <
C:\DesignDocs\load_data.sql

Here are two versions of the relevant statement in my perl script that
do not work:

my @args =
("mysql","--user=my_uid","--password=my_pword","--database=my_db","<","C:\\DesignDocs\\load_data.sql ");
print system(@args);


my @args =
("mysql","--user=my_uid","--password=mypword","--database=my_db","<","C:\\DesignDocs\\load_data.sql ");
print system(@args);

Both of these give me, as output, the page describing proper usage of
mysql, suggesting that mysql is not getting the parameters as I
specified them.

I expect I can work around this by creating a simple batch file, but
that doesn't explain why my perl statements don't work when, as far as
I can tell from the documentation I have at hand, they should have
worked.

Can anyone out there solve this puzzle?

Thanks

Ted

On 22 May 2006 13:47:19 -0700, Ted wrote:
> I am not sure if this probleem is primarily perl or MySQL, so I'll try
> here first. With any luck, there is someone else here who uses perl to
> run sql scripts against a MySQL DB.
>
> First, the following is a command that I can run from the Windows
> console, and it works as expected.
>
> mysql -u my_uid --password=my_pword --database=my_db <
> C:\DesignDocs\load_data.sql
>
> Here are two versions of the relevant statement in my perl script that
> do not work:
>
> my @args =
> ("mysql","--user=my_uid","--password=my_pword","--database=my_db","<","C:\\DesignDocs\\load_data.sql ");
> print system(@args);
>
>
> my @args =
> ("mysql","--user=my_uid","--password=mypword","--database=my_db","<","C:\\DesignDocs\\load_data.sql ");
> print system(@args);
>
> Both of these give me, as output, the page describing proper usage of
> mysql, suggesting that mysql is not getting the parameters as I
> specified them.
>
> I expect I can work around this by creating a simple batch file, but
> that doesn't explain why my perl statements don't work when, as far as
> I can tell from the documentation I have at hand, they should have
> worked.
>
> Can anyone out there solve this puzzle?

"<" is not an argument. It is a shell-dependant redirect of input from a
file instead of a terminal keyboard.

--
Either way, it'll remind the clued that there's only one letter
difference between 'turkey' and 'turnkey'.
-- Mike Andrews

Peter H. Coffin wrote:
> On 22 May 2006 13:47:19 -0700, Ted wrote:
>> I am not sure if this probleem is primarily perl or MySQL, so I'll
>> try here first. With any luck, there is someone else here who uses
>> perl to run sql scripts against a MySQL DB.
>>
>> First, the following is a command that I can run from the Windows
>> console, and it works as expected.
>>
>> mysql -u my_uid --password=my_pword --database=my_db <
>> C:\DesignDocs\load_data.sql
>>
>> Here are two versions of the relevant statement in my perl script
>> that do not work:
>>
>> my @args =
>> ("mysql","--user=my_uid","--password=my_pword","--database=my_db","<","C:\\DesignDocs\\load_data.sql ");
>> print system(@args);
>>
>>
>> my @args =
>> ("mysql","--user=my_uid","--password=mypword","--database=my_db","<","C:\\DesignDocs\\load_data.sql ");
>> print system(@args);
>>
>> Both of these give me, as output, the page describing proper usage of
>> mysql, suggesting that mysql is not getting the parameters as I
>> specified them.
>>
>> I expect I can work around this by creating a simple batch file, but
>> that doesn't explain why my perl statements don't work when, as far
>> as I can tell from the documentation I have at hand, they should have
>> worked.
>>
>> Can anyone out there solve this puzzle?
>
> "<" is not an argument. It is a shell-dependant redirect of input
> from a file instead of a terminal keyboard.

Yes, and he is trying to get the contents of the load_data.sql file to be
redirected as input.

Ted wrote:
> mysql -u my_uid --password=my_pword --database=my_db <
> C:\DesignDocs\load_data.sql

This illustrates something you should understand about shell command
processing if you're going to use the system() call from Perl.

The shell don't pass all the args you see above to the child process.
Specifically, redirects with ">" or "<" or "|" are handled by the shell,
by remapping the file descriptors of the child process.

Once the file descriptors are set up for the child process, then the
remainder of the command line is passed to that child process as it
exec's the named program. This is basically the way most UNIX shells
work, with respect to I/O redirection.

If you want to learn more about this, read POSIX C API docs on system
calls like dup(), dup2(), execl(), fork(), etc.

As far as I can tell, the modern "cmd.exe" on Windows works the same,
though I believe the older DOS command shells actually pass the "<"
redirection symbol to the child processes as arguments. But the mysql
client assumes it's being run by the modern type of shell, and does not
recognize "<" as an argument.

The mysql client application provides a way to load a script without
redirecting I/O. You can execute the literal client statement "SOURCE
<file>" from the command line, using the mysql -e option.

For example:

my @args =
("mysql","--user=my_uid","--password=my_pword","--database=my_db","-e","source
C:\\DesignDocs\\load_data.sql");
print system(@args);

Regards,
Bill K.

Bill Karwin wrote:
> Ted wrote:
>
>> mysql -u my_uid --password=my_pword --database=my_db <
>> C:\DesignDocs\load_data.sql
>
>
> This illustrates something you should understand about shell command
> processing if you're going to use the system() call from Perl.
>
> The shell don't pass all the args you see above to the child process.
> Specifically, redirects with ">" or "<" or "|" are handled by the shell,
> by remapping the file descriptors of the child process.
>
> Once the file descriptors are set up for the child process, then the
> remainder of the command line is passed to that child process as it
> exec's the named program. This is basically the way most UNIX shells
> work, with respect to I/O redirection.
>
> If you want to learn more about this, read POSIX C API docs on system
> calls like dup(), dup2(), execl(), fork(), etc.
>
> As far as I can tell, the modern "cmd.exe" on Windows works the same,
> though I believe the older DOS command shells actually pass the "<"
> redirection symbol to the child processes as arguments. But the mysql
> client assumes it's being run by the modern type of shell, and does not
> recognize "<" as an argument.
>
> The mysql client application provides a way to load a script without
> redirecting I/O. You can execute the literal client statement "SOURCE
> <file>" from the command line, using the mysql -e option.
>
> For example:
>
> my @args =
> ("mysql","--user=my_uid","--password=my_pword","--database=my_db","-e","source
> C:\\DesignDocs\\load_data.sql");
> print system(@args);
>
> Regards,
> Bill K.

Bill,

No, the old DOS shell interpreted redirection symbols itself ever since they
were first implemented (DOS 2.0? 2.1? somewhere back then).


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle wrote:
> No, the old DOS shell interpreted redirection symbols itself ever since
> they were first implemented (DOS 2.0? 2.1? somewhere back then).

Interesting. I believe you that the I/O was handled by the DOS shell,
but I could swear I've seen return messages from applications that made
me think that the "<" arg was also passed to the app. I must have made
an incorrect assumption.

In any case, DOS is dead so it doesn't matter. ;-)

Regards,
Bill K.

Thanks Bill, Peter, Paul and Jerry.

Your input is appreciated.

Bill, your example proved valuable, and worked like a charm.

Thanks again.

Ted