Getting data from MS SQL on Win Server using Perl

[cxw]

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

 

[1DMF]

ok , if you think that the data structure is an ARRAY of HASHES, you only need to know two things to change or get something.

1. the index of the array (basically the record) and remember arrays start at ZERO.

2. the key of the hash to change/get (the column name).

so if you are doing a getSQL and you know you should only have one records (you could do some error checking by asking how many are in the array) like so...

my @rs;
if(@rs = &getSQL("LMS_USER","USER_NAME,EMAIL","USER_NAME='005861539'") <> 1){error stuff here}
else{
 successs stuff here}

anyway, you know you only have one record so we know the index of the array is ZERO. We also know that we want EMAIL so we know the key to the hash we want, so we do it like this.

my $email = $rs[0]{'EMAIL'};

Again if you wanted to alter something in the record you'd do this...

$rs[0]{'EMAIL'} = "me\@mydomain.com";

remember changing something in the record set does not update the actual SQL DB, as the records exists only in memory.

and you see how i've put the email addy, in perl you must escape the @ if you hard code it in the string.

I see you used $_-> when you were not in a loop, $_ is the default variable when you do not supply one. let me explain.

you could code it the longer way

foreach my $rec (@rs) {
    print "$rec->{'EMAIL'}\n";
}

but to be quick an lazy we just put

for(@rs){
   print "$_->{'EMAIL'}\n";
}

because we didn't give it a variable to put each element of the array into it defaults to the inbuilt one $_ , and that's why I used that sysntax.

Sorry if it confused you, so you see if you are talking direct to the record set it is $varname[index]->{'hashhey'};

if you are using an index you don't actually need to put the -> , so in short you get $rs[0]{'EMAIL'};

hope this makes sense

Glad you got it working and hope you find it as usefull as I have over the years.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[1DMF]

Also don't know if you are interested but i've converted the module to OO (Object Orientated) design.

You can download the module here

http://www.homeloanpartnership.com/sql.zip

the way you use it is as follows..

#!/usr/bin/perl

# Set Error Trapping
use CGI::Carp qw(fatalsToBrowser warningsToBrowser); 
use warnings;
use strict;

# Set path to user modules
use FindBin qw($Bin);
use lib "$Bin";

# User Sql Module
use Sql;

# create SQL object
my $sql = new Sql;

# set DSN
$sql->set ("DSN","DSN=myDSN;UID=myUID;PWD=myPWD;");

# set Table
$sql->set ("TABLE","myTable");

# set Columns
$sql->set ("COLS","myCol1,myCol2");

# set Where
$sql->set ("WHERE","myCol1='myVal'");

# set Order
$sql->set ("ORDER","myCol2 DESC");

# get the records into an ARRAY of HASHES
my @rs = $sql->get;

# print HTML header
print "Content-Type: text/html\n\n";

# Loop and Print records
for(@rs){
    print "$_->{'myCol1'} , $_->{'myCol2'} <br />";
}

exit();

There are a few pointers for its use, but this object option enables you to re-use the object and just set the bits you need.

The WHERE clause is reset after each method, this ensures if you had set it to "1=1" , and then issued a $sql->del , and forgot to change the WHERE , you don't wipe your table ;-)

each method is as follows.

$sql->get , $sql->del , $sql->ins , $sql->upd , $sql->sum , $sql->cnt

the possible object variables are...

COLS , TABLE , DSN , VALUES , WHERE , FDSN , ORDER

You can change DSN to be a FILE DSN by setting FDSN to true like so...

$sql->set ("FDSN",1);

NOTE: I use double quotes for the 'set' method as text requires single quotes for SQL so if you were doing an insert you'd do this...

$sql-set("TABLE","myTable");
$sql->set("COLS","myCol1,myCol2");
$sql->set("VALUES","'myVal','myVal2'");
my $rec = $sql->ins;

And for updates...

$sql->set("TABLE","myTable");
$sql->set("COLS","myCol1='myVal1',myCol2='myVal2'");
$sql->set("WHERE","myRecID=25");
my $rec = $sql->upd;

Not sure it is any better than the other module, I just wanted to learn some OO design and so used this module as a learning tool.

Maybe you can try it out and let me know which one you prefer.

Have fun.

Regards, 1DMF.


"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[cxw]

This works fine:
my @rs = &getSQL("LMS_USER","USER_NAME,EMAIL,USER_ID","USER_NAME='test1'");

However I need to find by the email address like so:
my @rs = &getSQL("LMS_USER","USER_NAME,EMAIL,USER_ID","EMAIL='test1@something.com'");

How do I need to format the select statment so it will accept the email address?
Thank you
Chip

 

[cxw]

OK I forgot to say that the email address comes in a CVS file and so i get it in a var.
It would look somthing like this:
# open file
open (TXTFILE, "c:/SOARimports/NU_AD2032_ILINC_EXTRACT_stf.csv") or die("Unable to open file");;
@lines = <TXTFILE>;

# read file into an array
foreach $data(@lines) {
#print "$data <BR>";
#loop through file putting users in array here
# split the line of text by comma
my @values = split(',', $data);

# put the line into an array
my($NUuserID,$Lname,$Fname,$Email) = @values;
chomp($NUUserName);
chomp($NUuserID);
chomp($Email);

#Then here I will need to find the user by email address:
my @rs = &getSQL("LMS_USER","USER_NAME,EMAIL,USER_ID","EMAIL='$Email'");

Only I get an script will not run error.
I think it is the @ in the email address.

 

[cxw]

OK I figured it out.
About time I figured something out myself for a change.
What I need to do is insert a \ before the @ like below:
stephi.ahn\@some.com
So I will format the string before searching on it.
Thanks
Chip

 

[1DMF]

not if the email is in a variable, you do not need to string match and escape.

so if a form is submitted with an email field and you assign it to a variable in the satndard CGI way.

my $email = $cgi->param('Email');

you don't need to escape $email.

i'm suprised your example fails I have not had this problem, but I have not read a text file and used it in that manner.

I have used

if(my @ID = &getSQL("myTable","ID","Email='" . $cgi->param('EMAIL') . "' AND Pword = '" . $cgi->param('PWORD') . "'")){do what ever;}
else{die "invalid details";}

with out any problems, maybe the CGI module does some escaping for you.

what exactly was the error message?




"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[cxw]

It is not clear to me on how to return the error or sucess on the query?
If (my @rs = &getSQL("LMS_USER","USER_NAME,EMAIL,USER_ID,IS_GUEST,STATUS","EMAIL='$NewEmail' and STATUS='active'") <> 1){
print "fail:";
}else{
print "OK";
}

Thanks,
Chip

 

[1DMF]

the way you have it is fine as long as you are expecting only 1 record.

you could do it like this

my @rs;
if (!@rs = &getSQL("LMS_USER","USER_NAME,EMAIL,USER_ID,IS_GUEST,STATUS","EMAIL='$NewEmail' and STATUS='active'")){
print "fail:";
}else{
print "OK";
}

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!