Phoenix.pm: PERL-SQL question

[Shay Harding]

----- Original Message -----
From: "Doug Miles" <doug.miles at bpxinternet.com>
To: <phoenix-pm-list at happyfunball.pm.org>
Sent: Wednesday, January 30, 2002 11:34 AM
Subject: Re: Phoenix.pm: PERL-SQL question


> N wrote:
>
> > I have file on a unix server and need the
> > info(actually it is the same file I am having troubles
> > with now) loaded to a MSSQL database on an NT machine
> > and was wondering if there was a way I could make a
> > database connection from the UNIX server to the NT
> > database through perl rather than writing an ftp
> > function to send the file over and then an NT perl db
> > loader picking it up and then inserting the values in
> > the database.
> > Rgds
> > N
>
> DBI/DBD::ODBC should do what you want.  Anyone out there have experience
> with this?

No experience but I think DBD::ODBC is probably an XS interface to some .dll
on Windows so it might only run on Windows? I doubt it is pure Perl so it
probably won't run under UNIX. There are ODBC drivers that you can install
on UNIX, but once again, I typically don't interface with Windows so I can't
say for sure. I am certain you can go the other way though: Windows ->
UNIX -> Windows where the Windows box selects off the UNIX database (I
presume either MySQL or MSQL?) then insert into the Windows box via
DBD::ODBC. Something typical might look like this:

#!perl

use DBI;
my $odbc = DBI->connect("DBI:odbc:database:server", "user", "pass");   #
This might not be syntactically correct
my $unix = DBI->connect("DBI:mysql:database:server", "user", "pass");  # I
know this is correct

my $sth = $unix->prepare("SELECT some data");
$sth->execute;

while (my $data = $sth->fetchrow_arrayref){
    $odbc->do("INSERT the data into Windows database");
}

$sth->finish;   # not usually necessary
$odbc->disconnect;
$unix->disconnect;


It really doesn't matter what database is running on the UNIX server as this
should work for Oracle, Sybase, Postgres, MySQL, mSQL, etc.

Shay