DBI::ADO problem... execute() doesn't return valid rows from MSSQL

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View


I am having a problem getting DBI::ADO to work correctly.  Let me start
by admitting I am not a perl expert, but have the camel book here with
me and have googled extensively.  Here's my problem:

I am running ActiveState perl 5.8.6 on WinXP and connecting to MSSQL
2K.  I need to run a simple prepared statement against the database.
Even though the data  are in the tables, DBI does not consistently
return the rows.  Here's a test program I am using:

use strict;
use DBI;

my $dsn = "Provider=sqloledb;Server=dbHost;Database=dbName";
my $dbh =
  DBI->connect( "DBI:ADO:$dsn", "dbUID", "dbPWD",
    { RaiseError => 1, AutoCommit => 0 } ) or die "could not connect";


my $sth = $dbh->prepare(
      SELECT n.nodeName, w.whoFixes
      FROM SMARTS_nodes n INNER JOIN SMARTS_whoFixes w ON
      w.whoFixesId = n.whoFixesId
      WHERE n.nodeName=?
) or die $dbh->errstr;

my @nodes = qw(
  aualcr1 aumpns8 auinndc0 aumpcr2 aumim1a01 aumpns18 aumpnf02 aulocr1

my $i = 1;
my $f = 0;
my $n = 0;

for my $node (@nodes) {

    $sth->bind_param( 1, $node );
    $sth->execute() or die $sth->errstr;
    my @row = $sth->fetchrow_array();

    if ( scalar @row == 2 ) {
        printf( "%03d\t%-12s %-12s %s\n", $i++,$node,$row[0],$row[1] );
    else {
        printf( "%03d\t%-12s\n",$i++,$node );

printf( "\nSearched: %03d\tFound: %03d\tNot Found: %03d\n", $i-1, $f,
$n );

The output looks like this:
001    aualcr1      aualcr1      AP_CS_NETWORK
002    aumpns8
003    auinndc0
004    aumpcr2      aumpcr2      AP_CS_NETWORK
005    aumim1a01    aumim1a01    AP_CS_TECH_AU
006    aumpns18
007    aumpnf02
008    aulocr1      aulocr1      AP_CS_NETWORK
009    aumpaw01     aumpaw01     AP_CS_TECH_AU

Searched: 009    Found: 005    Not Found: 004

In my MSSQL database, rows 1,4,5,8,9 are indeed there, but so is row 3.
 I must be missing something or doing something I shouldn't, but cannot
figure out what I'm doing wrong.

Thanks for any help/pointers/URLs...

Re: DBI::ADO problem... execute() doesn't return valid rows from MSSQL

Doesn't exactly help you directly but http://www.wampserver.com/ might be an
option. Apache, Perl PHP and MYSQL all talking to each other on your XP
machine. Phewee !

Site Timeline