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

Discussion in 'Perl Misc' started by doug, Jun 6, 2005.

  1. doug

    doug Guest

    Hello,

    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";

    $dbh->trace(0);

    my $sth = $dbh->prepare(
    q{
    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
    aumpaw01
    );

    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] );
    $f++;
    }
    else {
    printf( "%03d\t%-12s\n",$i++,$node );
    $n++;
    }
    #$sth->finish();
    }

    $dbh->disconnect();
    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...
     
    doug, Jun 6, 2005
    #1
    1. Advertising

  2. 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 !
     
    Julia De Silva, Jun 7, 2005
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Kristian
    Replies:
    0
    Views:
    556
    Kristian
    Nov 13, 2003
  2. Replies:
    16
    Views:
    4,299
    gregarican
    Jan 19, 2006
  3. Replies:
    2
    Views:
    697
    Steve Holden
    Jan 18, 2006
  4. Replies:
    0
    Views:
    168
  5. Replies:
    1
    Views:
    197
Loading...

Share This Page