Help needed w/a Script to query Access DB

[yim11]

Hello!
I have Perl script that talks with the Northwinds example database in MS Access using an ODBC DSN of Northwind.

My problem is that the script only returns the first 4 values from the database table when run.
My question is how can I fix the script to display all the results?
Many Thanks in advance!
-Jim

The script follows:
---Begin script---

use CGI;
use DBI;
use Data:umper;
my $query = new CGI;
my $dbh = DBI->connect( "dbi:ODBC:northwind", "", "", {RaiseError => 1, PrintError => 1, AutoCommit => 1} ) or
die "Unable to connect: " . $DBI::errstr . "\n";

# OK, connected, now select from Customers table.

my $sel = $dbh->prepare( "select * from Customers where CustomerID like ?" );

$sel->execute( qq{A%} );
print $query->header,
$query->start_html(-title=>'RSPR'),

print "Driver : " . $dbh->{Driver}->{Name} . "\n";
print "\n";
print &quot;<P>&quot;;
print &quot;SQL Statement: &quot; . $sel->{Statement} . &quot;\n&quot;;
print &quot;<P>&quot;;
print &quot;Table contains: &quot; . $sel->{NUM_OF_FIELDS} . &quot; columns.\n&quot;;
print &quot;<P>&quot;;
print &quot;Column names are: &quot; . join( &quot;\n\t&quot;, @{$sel->{NAME}}, &quot;&quot; );
print &quot;<P>&quot;;
print &quot;Number of Params: &quot; . $sel->{NUM_OF_PARAMS} . &quot;\n&quot;;
print &quot;<P>&quot;;

{ my @row;
while (@row = $sel->fetchrow_array) {
print join( &quot;\t&quot;,@row, &quot;\n&quot;}
}


print &quot;\n&quot;;
# Finished

$sel->finish;
$dbh->disconnect;
exit;
---End Script---

 

[geoffp]

I haven't used the DBI module, but I have a written a few scripts that talk to Access databases using Win32::ODBC. I haven't had any problems going that route. You can do everything you have in your script with Win32::ODBC. Here is some sample script using Win32::ODBC:

--- BEGIN script
use Win32::ODBC;

$MYDSN = &quot;Northwind&quot;; #name of your DSN

my( $db ) = new ODBC($MYDSN);
if ( !$db ) {
print &quot;Error connecting to $MYDSN: &quot;. ODBC::Error().&quot;\n&quot;;
return;
}

$SQL = &quot;select * from Customers where CustomerID like ?&quot;;
if ( $db->Sql($SQL) ) {
# there was an error
}
else {
# process the rows
while ( ($db->FetchRow()) ) {
my( %Data ) = $db->DataHash();
# access each value
# see on-line documentation for info

}
}

$db->Close();

--- END script

 

[MikeLacey]

yim,

your DBI script looks ok to me, how many rows are returned if your do the same thing using an access query?
Mike
michael.j.lacey@ntlworld.com