Wroof has asked for the wisdom of the Perl Monks concerning the following question:

Good morning all,

I have been using the following code to access a database and pull out a bit of information for project that I have been working on. I had this part working so have not run it for the last few days but now it does not seam to work. It just skips the fetchrow_array part. 

 use DBI;

# DBD::ODBC

my $dsn = 'DBI:ODBC:Driver={SQL Server}';
my $host = '192.168.1.100,61667';
my $database = 'ABACUSDBS';
my $user = '**';
my $auth = '@@@@@@@@@@';

# Connect via DBD::ODBC using a system DSN.
my $dbh = DBI->connect("$dsn;Server=$host;Database=$database",
            $user,
            $auth,
            {RaiseError => 1, AutoCommit => 1}
            ) || die " Database connection not made: $DBI::errstr";
            
my $dbh2 = DBI->connect("$dsn;Server=$host;Database=$database",
            $user,
            $auth,
            {RaiseError => 1, AutoCommit => 1}
            ) || die " Database connection not made: $DBI::errstr";
#Prepare a SQL statement



my $sql = "SELECT TOP 1000 * FROM [ABACUSDBS].[dbo].[Bally] WHERE comp
+leted=0";

$sth = $dbh->prepare( $sql );
my $sth2 = $dbh2->prepare("UPDATE bally SET completed=1 where CarNo=?"
+);
my $temp = 'TRUE';

#execute the statement
#$dbh->begin_work;
$sth->execute();
#Bind the results to the local variables
$sth->bind_columns( undef, \$CarNo, \$Disc,\$Completed,\$total, \$date
+, \$PastDiscont, \$FullAmount );

#Retrieve values from the result set

while( $sth->fetchrow_array ) {
    print 'test';
    print "$CarNo, $Disc, $Completed, $Total, $date ,$PastDiscount, $F
+ullAmount\n";
    $sth2->execute($CarNo)
}


#Close the connection
$sth->finish();
$dbh->disconnect();
$dbh2->disconnect();

[download]

for the life of me I cannot work out what is happening. If I run the select statement on the database itself it returns 4 lines so I know there is data in there. If anyone can see the problem please help

below is the two calls. first one without the trace to show that it does nothing and gives no errors. The second part is the result of the trace but from all I can see it seams to get itself stuck in a loop but I cannot tell why.

C:\Users\Administrator>perl C:\Users\Administrator\Desktop\soap\Pullfr
+omdatabase
2.pl

C:\Users\Administrator>perl C:\Users\Administrator\Desktop\soap\Pullfr
+omdatabase
2.pl
    DBI 1.631-ithread default trace level set to 0x0/1 (pid 2864 pi 4e
+88b4) at P
ullfromdatabase2.pl line 2
    Note: perl is running without the recommended perl -w option
    -> DBI->connect(DBI:ODBC:Driver={SQL Server};Server=192.168.1.100,
+61667;Data
base=ABACUSDBS, sa, ****, HASH(0x4ec3dc))
    -> DBI->install_driver(ODBC) for MSWin32 perl=5.012003 pid=2864 ru
+id=0 euid=
0
       install_driver: DBD::ODBC version 1.29 loaded from C:/Strawberr
+y/perl/ven
dor/lib/DBD/ODBC.pm
    <- install_driver= DBI::dr=HASH(0x2795144)
    !! The warn '0' was CLEARED by call to connect method
    <- connect('Driver={SQL Server};Server=192.168.1.100,61667;Databas
+e=ABACUSDB
S', 'sa', ...)= ( DBI::db=HASH(0x27956c4) ) [1 items] at DBI.pm line 6
+71
       info: '' '[Microsoft][ODBC SQL Server Driver][SQL Server]Change
+d database
 context to 'ABACUSDBS'. (SQL-01000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language settin
+g to us_en
glish. (SQL-01000)' (err#0)
    <- STORE('RaiseError', 1)= ( 1 ) [1 items] at DBI.pm line 723
       info: '' '[Microsoft][ODBC SQL Server Driver][SQL Server]Change
+d database
 context to 'ABACUSDBS'. (SQL-01000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language settin
+g to us_en
glish. (SQL-01000)' (err#0)
    <- STORE('PrintError', 1)= ( 1 ) [1 items] at DBI.pm line 723
       info: '' '[Microsoft][ODBC SQL Server Driver][SQL Server]Change
+d database
 context to 'ABACUSDBS'. (SQL-01000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language settin
+g to us_en
glish. (SQL-01000)' (err#0)
    <- STORE('AutoCommit', 1)= ( 1 ) [1 items] at DBI.pm line 723
       info: '' '[Microsoft][ODBC SQL Server Driver][SQL Server]Change
+d database
 context to 'ABACUSDBS'. (SQL-01000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language settin
+g to us_en
glish. (SQL-01000)' (err#0)
    <- STORE('Username', 'sa')= ( 1 ) [1 items] at DBI.pm line 726
       info: '' '[Microsoft][ODBC SQL Server Driver][SQL Server]Change
+d database
 context to 'ABACUSDBS'. (SQL-01000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language settin
+g to us_en
glish. (SQL-01000)' (err#0)
    <- connected('DBI:ODBC:Driver={SQL Server};Server=192.168.1.100,61
+667;Databa
se=ABACUSDBS', 'sa', ...)= ( undef ) [1 items] at DBI.pm line 733
    <- connect= DBI::db=HASH(0x27956c4)
       info: '' '[Microsoft][ODBC SQL Server Driver][SQL Server]Change
+d database
 context to 'ABACUSDBS'. (SQL-01000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language settin
+g to us_en
glish. (SQL-01000)' (err#0)
    <- STORE('dbi_connect_closure', CODE(0x27950b4))= ( 1 ) [1 items] 
+at DBI.pm
line 742
    -> DBI->connect(DBI:ODBC:Driver={SQL Server};Server=192.168.1.100,
+61667;Data
base=ABACUSDBS, sa, ****, HASH(0x4ec4ec))
    !! The info '''' was CLEARED by call to connect method
    <- connect('Driver={SQL Server};Server=192.168.1.100,61667;Databas
+e=ABACUSDB
S', 'sa', ...)= ( DBI::db=HASH(0x2795e34) ) [1 items] at DBI.pm line 6
+71
       info: '' '[Microsoft][ODBC SQL Server Driver][SQL Server]Change
+d database
 context to 'ABACUSDBS'. (SQL-01000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language settin
+g to us_en
glish. (SQL-01000)' (err#0)
    <- STORE('RaiseError', 1)= ( 1 ) [1 items] at DBI.pm line 723
       info: '' '[Microsoft][ODBC SQL Server Driver][SQL Server]Change
+d database
 context to 'ABACUSDBS'. (SQL-01000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language settin
+g to us_en
glish. (SQL-01000)' (err#0)
    <- STORE('PrintError', 1)= ( 1 ) [1 items] at DBI.pm line 723
       info: '' '[Microsoft][ODBC SQL Server Driver][SQL Server]Change
+d database
 context to 'ABACUSDBS'. (SQL-01000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language settin
+g to us_en
glish. (SQL-01000)' (err#0)
    <- STORE('AutoCommit', 1)= ( 1 ) [1 items] at DBI.pm line 723
       info: '' '[Microsoft][ODBC SQL Server Driver][SQL Server]Change
+d database
 context to 'ABACUSDBS'. (SQL-01000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language settin
+g to us_en
glish. (SQL-01000)' (err#0)
    <- STORE('Username', 'sa')= ( 1 ) [1 items] at DBI.pm line 726
       info: '' '[Microsoft][ODBC SQL Server Driver][SQL Server]Change
+d database
 context to 'ABACUSDBS'. (SQL-01000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language settin
+g to us_en
glish. (SQL-01000)' (err#0)
    <- connected('DBI:ODBC:Driver={SQL Server};Server=192.168.1.100,61
+667;Databa
se=ABACUSDBS', 'sa', ...)= ( undef ) [1 items] at DBI.pm line 733
    <- connect= DBI::db=HASH(0x2795e34)
       info: '' '[Microsoft][ODBC SQL Server Driver][SQL Server]Change
+d database
 context to 'ABACUSDBS'. (SQL-01000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language settin
+g to us_en
glish. (SQL-01000)' (err#0)
    <- STORE('dbi_connect_closure', CODE(0x2795984))= ( 1 ) [1 items] 
+at DBI.pm
line 742
    !! The info '''' was CLEARED by call to prepare method
    <- prepare('SELECT TOP 1000 * FROM [ABACUSDBS].[dbo].[Bally] WHERE
+ completed
=0')= ( DBI::st=HASH(0x279601c) ) [1 items] at Pullfromdatabase2.pl li
+ne 29
    !! The info '''' was CLEARED by call to prepare method
    <- prepare('UPDATE bally SET completed=1 where CarNo=?')= ( DBI::s
+t=HASH(0x2
680a4c) ) [1 items] at Pullfromdatabase2.pl line 30
    <- execute= ( -1 ) [1 items] at Pullfromdatabase2.pl line 35
    <- bind_columns(undef, SCALAR(0x6f632c), ...)= ( 1 ) [1 items] at 
+Pullfromda
tabase2.pl line 37
    <- fetchrow_array= ( '0' ) [1 items] row1 at Pullfromdatabase2.pl 
+line 41
    <- finish= ( 1 ) [1 items] at Pullfromdatabase2.pl line 49
    <- disconnect= ( 1 ) [1 items] at Pullfromdatabase2.pl line 50
    <- disconnect= ( 1 ) [1 items] at Pullfromdatabase2.pl line 51
    <- DESTROY(DBI::st=HASH(0x2795784))= ( undef ) [1 items]
    <- DESTROY(DBI::db=HASH(0x2795dc4))= ( undef ) [1 items]
    !! The info '''' was CLEARED by call to disconnect_all method
    <- disconnect_all= ( '' ) [1 items] at DBI.pm line 750
!   <- DESTROY(DBI::db=HASH(0x2795634))= ( undef ) [1 items] during gl
+obal destr
uction
!   <- DESTROY(DBI::st=HASH(0x279601c))= ( undef ) [1 items] during gl
+obal destr
uction
!   <- DESTROY(DBI::dr=HASH(0x2795144))= ( undef ) [1 items] during gl
+obal destr
uction

[download]

Replies are listed 'Best First'.
Re: DBI fetchrow_array stopped working
by graff (Chancellor) on May 05, 2014 at 02:58 UTC
    When you say "it just skips the fetchrow_array part", do you mean that it seems to run "without error" (and also without printing anything in the "while" loop or updating the other database)?

    If that's what you mean, then you might need to check the return value from your $sth->execute call (and the "bind_columns" call). If the "prepare" call had failed, I think you would have gotten a full-blown failure with an error message - something to the effect of "can't call method 'execute' on an undefined object." If the prepare succeeds but the execute fails for some reason, there might not be any automatic notification about that.

    When you run the query manually, do you happen to notice whether the current table columns match what your script assumes them to be?

[reply]
[d/l]
Re: DBI fetchrow_array stopped working
by moritz (Cardinal) on May 05, 2014 at 08:52 UTC
[reply]
[d/l]
Re: DBI fetchrow_array stopped working
by mje (Curate) on May 06, 2014 at 13:30 UTC

    Add use strict and use warnings to your script as I can see mistakes like \$total then $Total used later. Also, why are you passing undef as the first argument to bind? Also, you can just use fetch when columns are bound and not fetchrow_array.

    There is something perculiar about your trace. fetchrow_array should return undef if there are no rows but yours is returning '0'. I think you are using scalar context on fetchrow_array and your first or last column is 0. Switch to just calling fetch and the problem should go away.

[reply]
Re: DBI fetchrow_array stopped working
by Anonymous Monk on May 05, 2014 at 07:11 UTC
    :) Prove it, show the trace
[reply]

      Added the trace info at the bottom of the OP

[reply]

Back to Seekers of Perl Wisdom