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();
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