Perl SSH to server and execute SQL query

Question

I am doing ssh to the server where sql is been installed using Net::OpenSSH perl module.

Connection to the ssh server is happening here.

Here is how I am ssh'ing to the server:

my $ssh = Net::OpenSSH->new("$user:$passwd\@$host");
$ssh->error and die "Couldn't establish SSH connection: ". $ssh->error;

But while connecting to the sql server and executing a query I am getting following message.

**********************************************
* unixODBC - isql                            *
**********************************************
* Syntax                                     *
*                                            *
*      isql DSN [UID [PWD]] [options]        *
*                                            *
* Options                                    *
*                                            *
* -b         batch.(no prompting etc)        *
* -dx        delimit columns with x          *
* -x0xXX     delimit columns with XX, where  *
*            x is in hex, ie 0x09 is tab     *
* -w         wrap results in an HTML table   *
* -c         column names on first row.      *
*            (only used when -d)             *
* -mn        limit column display width to n *
* -v         verbose.                        *
* -lx        set locale to x                 *
* -q         wrap char fields in dquotes     *
* -3         Use ODBC 3 calls                *
* -n         Use new line processing         *
* -e         Use SQLExecDirect not Prepare   *
* -k         Use SQLDriverConnect            *
* --version  version                         *
*                                            *
* Commands                                   *
*                                            *
* help - list tables                         *
* help table - list columns in table         *
* help help - list all help options          * 
*                                            *
* Examples                                   *
*                                            *
*      isql WebDB MyID MyPWD -w < My.sql     *
*                                            *
*      Each line in My.sql must contain      *
*      exactly 1 SQL command except for the  *
*      last line which must be blank (unless *
*      -n option specified).                 *
*                                            *
* Please visit;                              *
*                                            *
*      http://www.unixodbc.org               *
*      nick@lurcher.org                      *
*      pharvey@codebydesign.com              *
********************************************** 

My code looks like:

$result = $ssh->capture("isql -SWebDB -UMyID -PMyPWD -w2222 < query.sql");
$ssh->error and warn "operation didn't complete successfully: ". $ssh->error;

where query.sql I have placed in the remote server where I am doing ssh.

I am thinking whether syntax is missing inside $ssh->capture(....); or do we have any other function in Net::OpenSSH to run SQL queries. Because through $ssh->capture(....); if I pass any scripts or unix commands(ex:ls) its totally working fine. So I am suspecting is there any functions in Net::OpenSSHto run sql queries. Need input from you guys.

Answer 1

That looks like the kind of screen that a program would display if you tried to run it with invalid command-line arguments. It's a help screen saying "don't run me like that - here are my valid options".

Here's your command:

isql -SWebDB -UMyID -PMyPWD -w2222

And here's the sample command from the help screen:

isql WebDB MyID MyPWD -w

I'm not sure where you got your command line from but there are a few differences.

• isql doesn't have a -S option. Instead, you just give the database name as an argument.
• isql doesn't have a -U option. Instead, you just give the username as an argument.
• isql doesn't have a -P option. Instead, you just give the password as an argument.
• isql has a -w option, but it doesn't look like it takes an argument. It's just an on/off flag.

You need to change your command line to look more like the examples given in the help text.

Update: But the best option is probably to use isql's networking capabilities. Run it on your local server and connect to the database server remotely.