I am trying to get data from a Microsoft SQL database.
All the players are on the same box.
The Microsoft SQL server is sitting on a Microsoft 2003 server and so is Perl and the application that needs the data. I am also the Administrator on that box. I have set up an ODBC connection that works and have gotten some code from the web to get me started. I have gotten to the following part where I have note the Perl code stops. I have added the "use diagnostics;" but receive no messages. I am not completely understanding what I am doing but have worked with ASP and SQL record sets before. I have done some Perl and am stuck here. I expect to see at least one email address come back.
Here is the code on the pl page. I run these pages through the web, IIS. When I run this pl page this is what it returns:
"Test 2
DSN connected to database.
Test 3
Test 4
SELECT EMAIL FROM LMS_USER
"
Code below. It seems to fail where i have noted in the code.
#!/usr/bin/perl
use diagnostics;
use URI::Escape;
use LWP::UserAgent;
use XML::Simple;
use Data:
umper;
use DBI;
print "Content-type:text/html\n\n";
#print "test 1";
#SQL_test_1.pl
# Function: dbh_connect
# Description: Creates a connection to a database
# Arguments: none
# Returns: $db_conn - database connection handle
#
$db_user = "xxx";
$db_pass = "xxxx";
$dsn_name = 'dbi:ODBC:iLincEmailAddress';
sub dbh_connect
{
my ($dbh);
$dbh = DBI->connect($dsn_name, $db_user, $db_pass, {
PrintError => 0,
AutoCommit => 1
});
if (! defined($dbh) )
{
print "Error connecting to DSN '$dsn_name'\n";
print "Error was:\n";
print "$DBI::errstr\n"; # $DBI::errstr is the error
# received from the SQL server
return 0;
}
return $dbh;
}
dbh_connect;
print"Test 2<BR>";
#Opens DB ODBC connection
if (! dbh_connect() )
{
print "Unable to connect to database.\n";
exit; # Unable to reconnect, exit the script gracefully
}
else
{
print "DSN connected to database.\n <BR>";
}
print"Test 3 <BR>";
my ($sql, $sth, $rc);
$sql = shift;
if (! ($sql) )
{
print ("Must pass SQL statement to db_sql!\n");
return 0;
}
print "Test 4 <BR>";
my ($sql, $sth, $href);
$sql = "SELECT EMAIL FROM LMS_USER";
print "$sql <BR>";
# failing here <-------------------------> failing here
$sth = $dbh->prepare($sql); # Prepare the SQL statement passed to db_sql
$sth = db_sql($sql); # Pass the SQL statement to server
print "Test 5 <BR>";
if ( ! defined( $sth ) || ! ($sth) )
{
print "Unable to prepare SQL statement:\n";
print "$sql\n";
return 0;
}
$rc = $sth->execute;
print "Test 6";
#
# Check that we received a statement handle
#
if (! ($sth) )
{
return 0;
}
while( $href = $sth->fetchrow_hashref )
{
print "EMAIL: " . $$href{"EMAIL"} . "\n";
}
return 1;
print"Test 7 <BR>";
print"Test 8<BR>";
return $sth;
print"Test 9<BR>";
All the players are on the same box.
The Microsoft SQL server is sitting on a Microsoft 2003 server and so is Perl and the application that needs the data. I am also the Administrator on that box. I have set up an ODBC connection that works and have gotten some code from the web to get me started. I have gotten to the following part where I have note the Perl code stops. I have added the "use diagnostics;" but receive no messages. I am not completely understanding what I am doing but have worked with ASP and SQL record sets before. I have done some Perl and am stuck here. I expect to see at least one email address come back.
Here is the code on the pl page. I run these pages through the web, IIS. When I run this pl page this is what it returns:
"Test 2
DSN connected to database.
Test 3
Test 4
SELECT EMAIL FROM LMS_USER
"
Code below. It seems to fail where i have noted in the code.
#!/usr/bin/perl
use diagnostics;
use URI::Escape;
use LWP::UserAgent;
use XML::Simple;
use Data:
umper;use DBI;
print "Content-type:text/html\n\n";
#print "test 1";
#SQL_test_1.pl
# Function: dbh_connect
# Description: Creates a connection to a database
# Arguments: none
# Returns: $db_conn - database connection handle
#
$db_user = "xxx";
$db_pass = "xxxx";
$dsn_name = 'dbi:ODBC:iLincEmailAddress';
sub dbh_connect
{
my ($dbh);
$dbh = DBI->connect($dsn_name, $db_user, $db_pass, {
PrintError => 0,
AutoCommit => 1
});
if (! defined($dbh) )
{
print "Error connecting to DSN '$dsn_name'\n";
print "Error was:\n";
print "$DBI::errstr\n"; # $DBI::errstr is the error
# received from the SQL server
return 0;
}
return $dbh;
}
dbh_connect;
print"Test 2<BR>";
#Opens DB ODBC connection
if (! dbh_connect() )
{
print "Unable to connect to database.\n";
exit; # Unable to reconnect, exit the script gracefully
}
else
{
print "DSN connected to database.\n <BR>";
}
print"Test 3 <BR>";
my ($sql, $sth, $rc);
$sql = shift;
if (! ($sql) )
{
print ("Must pass SQL statement to db_sql!\n");
return 0;
}
print "Test 4 <BR>";
my ($sql, $sth, $href);
$sql = "SELECT EMAIL FROM LMS_USER";
print "$sql <BR>";
# failing here <-------------------------> failing here
$sth = $dbh->prepare($sql); # Prepare the SQL statement passed to db_sql
$sth = db_sql($sql); # Pass the SQL statement to server
print "Test 5 <BR>";
if ( ! defined( $sth ) || ! ($sth) )
{
print "Unable to prepare SQL statement:\n";
print "$sql\n";
return 0;
}
$rc = $sth->execute;
print "Test 6";
#
# Check that we received a statement handle
#
if (! ($sth) )
{
return 0;
}
while( $href = $sth->fetchrow_hashref )
{
print "EMAIL: " . $$href{"EMAIL"} . "\n";
}
return 1;
print"Test 7 <BR>";
print"Test 8<BR>";
return $sth;
print"Test 9<BR>";
![[noevil]](/data/assets/smilies/noevil.gif "[noevil] [noevil]")
:' \%opts);