RE: Perl Database

[Carlos Guillen]

The Win32::ODBC module may also be of use, although I personally prefer DBI.

-----Original Message-----
From: Simon Oliver [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 11, 2001 7:29 AM
To: Jarrod Ramsey
Cc: '[EMAIL PROTECTED]'
Subject: Re: Perl Database


Jarrod Ramsey wrote:
> 
> Hi,
>         I've never messed with using a Database with Perl, so I don't even
> know where to start.  I'm trying to put information in a Microsoft
Database
> through Perl.  Some is text, some is numerical.  I would appreciate any
> help.

Use the perl DBI (Database interface).  It is a platform / DBMS independant
database programming interface.

You can get it via ppm and you will beed a database driver (DBD) too,
probably
ODBC will suite your needs best:

`ppm install DBI DBD-ODBC`

Assuming you have setup a sytem DSN (via the ODBC administrator control
panel)
with the name 'testdsn' pointing to a Microsoft Access file at say:
 C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb

my $dsn = 'testdsn';
my $uid = ''; my $pwd = '';

my $dbh = DBI->connect("dbi:ODBC:$dsn", $uid, $pwd) or 
    die "Error: Connecting to '$dsn'";


If you don't want the hassle of setting up a DSN then use a DSN-less
connection instead:

my $driver = 'Microsoft Access Driver (*.mdb)';
my $db = 'C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb';
my $dsn = "driver=$driver;dbq=$db";
my $uid = ''; my $pwd = '';

my $dbh = DBI->connect("dbi:ODBC:$dsn", $uid, $pwd) or 
    die "Error: Connecting to '$dsn'";


Now you have an active connection you must prepare your SQL:

my $sql = 'SELECT * FROM products';
my $sth = $dbh->prepare($sql);


Execute the prepared statement:

$sth->execute;


Fetch the rows and print 'em:

while (my $row = $sth->fetch){
        print join(',', @$row), "\n";
}


Finally close off the handles:

$sth->finish;
$dbh->disconnect;


You will want to do some error checking too.  Read the DBI docs but the
simplest form of error checking is to use the RaiseError attribute and then
use eval{} blocks to catch the errors.  See the example below my sig.

--
  Simon Oliver

use warnings;
use strict;
use DBI;

my $dsn = 'testdsn';
my $uid = ''; my $pwd = '';
my $attr = {PrintError=>0,RaiseError=>1};

my ($dbh, $sql, $sth);

$dbh = DBI->connect("dbi:ODBC:$dsn", $uid, $pwd, $attr);
$sql = 'SELECT * FROM products';

eval {
  $sth = $dbh->prepare($sql);
  $sth->execute();
};

if ($@) {
  print "There was an error executing the statement: $@";
} else {
  print "Execute completed, here are the results:\n";
  while (my $row = $sth->fetch){
    print join(',', @$row), "\n";
  }
  $sth->finish;
}

$dbh->disconnect;
_______________________________________________
Perl-Win32-Admin mailing list
[EMAIL PROTECTED]
http://listserv.ActiveState.com/mailman/listinfo/perl-win32-admin

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.306 / Virus Database: 166 - Release Date: 12/4/2001
 

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.306 / Virus Database: 166 - Release Date: 12/4/2001
 
_______________________________________________
Perl-Win32-Admin mailing list
[EMAIL PROTECTED]
http://listserv.ActiveState.com/mailman/listinfo/perl-win32-admin