CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX 		Search Posts SEARCH
POSTS 		Who's Online WHO'S
ONLINE 		Log in LOG
IN

Home: Perl Programming Help: Beginner:
Easiest way to run Microsoft SQL proc.

 


MDTech.us_MAN
Novice

Feb 26, 2013, 8:16 PM

Post #1 of 14 (409 views)
Easiest way to run Microsoft SQL proc. Can't Post
Hello All,

Would you please tell me the best way to run a simple Microsoft SQL with a ton of input parameters and/or a ton of output parameters, too.

Also, please include a way to run simple SELECT statements.

Maxwell.
- Owner and Webmaster of http://www.MDTech.us


FishMonger
Veteran

Feb 27, 2013, 6:52 AM

Post #2 of 14 (390 views)
Re: [MDTech.us_MAN] Easiest way to run Microsoft SQL proc. [In reply to] Can't Post
You do it the same way as you were doing in your other question where you were connecting to a mysql database. The difference here is that you'll specify ODBC instead of mysql as the database driver.

http://search.cpan.org/~mjevans/DBD-ODBC-1.41/ODBC.pm


(This post was edited by FishMonger on Feb 27, 2013, 6:52 AM)


MDTech.us_MAN
Novice

Feb 27, 2013, 12:54 PM

Post #3 of 14 (383 views)
Re: [FishMonger] Easiest way to run Microsoft SQL proc. [In reply to] Can't Post
Another question, why does the following script return


Code
C:\Users\Maxwell>perl 111.pl -w -W 
Content-type: text/html 
 
<html><head><title>TST</title></head><body>1.41MaxwellDreytserNatalya</body></html>
Code
#!/usr/bin/perl 
use strict; 
use warnings; 
use DBI; 
use CGI; 
use DBD::ODBC; 
 
my $cgi = CGI->new; 
 
print "Content-type: text/html\n\n"; 
  
print "<html><head><title>TST</title></head><body>"; 
 
use CGI::Carp qw(warningsToBrowser fatalsToBrowser); 
  
print $DBD::ODBC::VERSION; 
  
my $db_instance = "192.168.1.6\\MDTSQL1"; 
my $db_name = "BCPADB"; 
my $db_user = "Maxwell175"; 
my $db_pass = "mD1234567890"; 
my $dbh = DBI->connect("dbi:ODBC:Driver={SQL Server};Server=$db_instance;UID=$db_user;PWD=$db_pass"); 
my @rows = (); 
my $sql = "SELECT First_Name FROM [BCPADB].[dbo].[Members]"; 
my $sth = $dbh->prepare($sql) or die("\n\nPREPARE ERROR:\n\n$DBI::errstr"); 
$sth->execute or die("\n\nQUERY ERROR:\n\n$DBI::errstr"); 
while(@rows = $sth->fetchrow_array()) { print join("\n ---", @rows); } 
$dbh->disconnect; 
 
  
print "</body></html>";


FishMonger
Veteran

Feb 27, 2013, 1:10 PM

Post #4 of 14 (378 views)
Re: [MDTech.us_MAN] Easiest way to run Microsoft SQL proc. [In reply to] Can't Post
I suspect that you have different versions of the script and the one you ran didn't have the same print statement.

Or, you edited the script and altered the print statement to include the join statement, but forgot to save the file before executing it.

Or, you only have one entry in the db and all 3 names are in that one record.


(This post was edited by FishMonger on Feb 27, 2013, 1:12 PM)


MDTech.us_MAN
Novice

Feb 27, 2013, 9:34 PM

Post #5 of 14 (369 views)
Re: [FishMonger] Easiest way to run Microsoft SQL proc. [In reply to] Can't Post
Oops, I pasted the wrong code. Blush

Code
#!/usr/bin/perl 
#use strict; 
#use warnings; 
use DBI; 
use CGI; 
use DBD::ODBC; 
 
my $cgi = CGI->new; 
 
print "Content-type: text/html\n\n"; 
  
print "<html><head><title>TST</title></head><body>"; 
 
use CGI::Carp qw(warningsToBrowser fatalsToBrowser); 
  
print $DBD::ODBC::VERSION; 
  
my $db_instance = "192.168.1.6\\MDTSQL1"; 
my $db_name = "DB"; 
my $db_user = "<USERNAME>; 
my $db_pass = "<PASSWORD>"; 
my $dbh = DBI->connect("dbi:ODBC:Driver={SQL Server};Server=$db_instance;UID=$db_user;PWD=$db_pass"); 
my @rows = (); 
my $sql = "USE [BCPADB]; DECLARE	@return_value int, @Exists bit,	@OutErrMsg varchar(100); EXEC	@return_value = [dbo].[Check_if_Rec_Exists]	   @Email = N'admin@mdtech.us', @Exists = @Exists OUTPUT, @OutErrMsg = @OutErrMsg OUTPUT; SELECT	@Exists as N'@Exists', @OutErrMsg as N'@OutErrMsg', @return_value as N'Return Value';"; 
my $sth = $dbh->prepare($sql) or die("\n\nPREPARE ERROR:\n\n$DBI::errstr"); 
$sth->execute or die("\n\nQUERY ERROR:\n\n$DBI::errstr"); 
while(@rows = $sth->fetchrow_array()) { print join("\n", @rows); print "\n"; } 
$dbh->disconnect; 
 
  
print "</body></html>";


FishMonger
Veteran

Feb 28, 2013, 6:52 AM

Post #6 of 14 (363 views)
Re: [MDTech.us_MAN] Easiest way to run Microsoft SQL proc. [In reply to] Can't Post
And your question/problem is ...?


MDTech.us_MAN
Novice

Feb 28, 2013, 9:36 AM

Post #7 of 14 (358 views)
Re: [FishMonger] Easiest way to run Microsoft SQL proc. [In reply to] Can't Post
The problem is I get the following error with the previously pasted code:

Code
[Thu Feb 28 12:34:27 2013] 111.pl: 
[Thu Feb 28 12:34:27 2013] 111.pl: 
[Thu Feb 28 12:34:27 2013] 111.pl: QUERY ERROR: 
[Thu Feb 28 12:34:27 2013] 111.pl: 
[Thu Feb 28 12:34:27 2013] 111.pl: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ','. (SQL-42000) 
[Thu Feb 28 12:34:27 2013] 111.pl: [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (SQL-42000) at 111.pl line 26.


FishMonger
Veteran

Feb 28, 2013, 9:49 AM

Post #8 of 14 (356 views)
Re: [MDTech.us_MAN] Easiest way to run Microsoft SQL proc. [In reply to] Can't Post
First, uncomment the strict and warnings pragams. You should ALWAYS load those pragmas. If needed, you could selectively disable some of their features, but that is rarely ever needed.

Next, fix the error on line 20. You left off the closing quote.

Then rerun the script and see if that fixes the problem.

EDIT:
You also need to fix your quoting on line 24, which is probably the cause of the sql error message.


(This post was edited by FishMonger on Feb 28, 2013, 9:51 AM)


MDTech.us_MAN
Novice

Feb 28, 2013, 8:12 PM

Post #9 of 14 (347 views)
Re: [FishMonger] Easiest way to run Microsoft SQL proc. [In reply to] Can't Post
Thanks for the hit. I enabled the warning program and found out that Perl thought the SQL variables where Perl variables and I placed the \ character before all of the @s.

Now, another question, how do I disable the warning program in one specific section of code, because I get the following warning when I am aware I'm going to have a error. (I'm talking about the code most recently posted)

Code
Content-type: text/html 
 
[Thu Feb 28 22:21:11 2013] 111.pl: Use of uninitialized value $rows[1] in join or string at 111.pl line 27. 
<html><head><title>TST</title></head><body>1 
 
0 
</body></html>

(This post was edited by MDTech.us_MAN on Feb 28, 2013, 8:13 PM)


FishMonger
Veteran

Mar 1, 2013, 6:11 AM

Post #10 of 14 (340 views)
Re: [MDTech.us_MAN] Easiest way to run Microsoft SQL proc. [In reply to] Can't Post
Post your updated version so I can see how/where you're using $row[1].


MDTech.us_MAN
Novice

Mar 1, 2013, 8:38 AM

Post #11 of 14 (336 views)
Re: [FishMonger] Easiest way to run Microsoft SQL proc. [In reply to] Can't Post
It's saying the value is uninitialized because the SQL proc returned NULL. (This it what I want)

Here is the code:
Code
#!/usr/bin/perl 
use strict; 
#use warnings; 
use DBI; 
use CGI; 
use DBD::ODBC; 
 
my $cgi = CGI->new; 
 
print "Content-type: text/html\n\n"; 
  
print "<html><head><title>TST</title></head><body>"; 
 
use CGI::Carp qw(warningsToBrowser fatalsToBrowser); 
  
#print $DBD::ODBC::VERSION; 
  
my $db_instance = "192.168.1.6\\MDTSQL1"; 
my $db_name = "BCPADB"; 
my $db_user = "Maxwell175"; 
my $db_pass = "mD1234567890"; 
my $dbh = DBI->connect("dbi:ODBC:Driver={SQL Server};Server=$db_instance;UID=$db_user;PWD=$db_pass"); 
my @rows = (); 
my $sql = "USE [BCPADB]; DECLARE \@return_value int, \@Exists bit, \@OutErrMsg varchar(100); EXEC \@return_value = [dbo].[Check_if_Rec_Exists] \@Email = N'admin\@mdtech.us', \@Exists = \@Exists OUTPUT, \@OutErrMsg = \@OutErrMsg OUTPUT; SELECT	\@Exists as N'\@Exists', \@OutErrMsg as N'\@OutErrMsg', \@return_value as N'Return Value';"; 
my $sth = $dbh->prepare($sql) or die("\n\nPREPARE ERROR:\n\n$DBI::errstr"); 
$sth->execute or die("\n\nQUERY ERROR:\n\n$DBI::errstr"); 
while(@rows = $sth->fetchrow_array()) { print join("\n", @rows); print "\n"; }      # <-- Here is where I use @rows. 
$dbh->disconnect; 
 
  
print "</body></html>";

(This post was edited by MDTech.us_MAN on Mar 1, 2013, 10:16 AM)


FishMonger
Veteran

Mar 1, 2013, 5:13 PM

Post #12 of 14 (320 views)
Re: [MDTech.us_MAN] Easiest way to run Microsoft SQL proc. [In reply to] Can't Post
I was going to recommend assigning '' an empty string to all undef fields, which is only 1 line of code, but it's just as easy to disable the uninitialized warning. However, you SHOULD NOT get into the habit of circumventing warnings just because you don't want to deal with it in a more appropriate manner.

Take note that I broke up the statements in the while loop as well as the sql statement. The sql could be broken out a little more. Putting multiple statements on a single line is bad style and should be avoided.

Code
my $sql = q/USE [BCPADB]; 
            DECLARE @return_value int, @Exists bit, @OutErrMsg varchar(100); 
            EXEC @return_value = [dbo].[Check_if_Rec_Exists] @Email = N'admin@mdtech.us', @Exists = @Exists OUTPUT, @OutErrMsg = @OutErrMsg OUTPUT; 
            SELECT @Exists as N'@Exists', @OutErrMsg as N'@OutErrMsg', @return_value as N'Return Value';/; 
 
while(my @row = $sth->fetchrow_array) { 
    no warnings 'uninitialized'; 
    print join("\n", @row), "\n"; 
}


MDTech.us_MAN
Novice

Mar 1, 2013, 8:41 PM

Post #13 of 14 (315 views)
Re: [FishMonger] Easiest way to run Microsoft SQL proc. [In reply to] Can't Post
So how do I automatically assign '' to all blank strings easily?

Also, how do you use Perl variables in the q/ ... /, since I need to use the email that is given as a parameter?

P.S. Thanks for the trick with the query.


(This post was edited by MDTech.us_MAN on Mar 1, 2013, 9:12 PM)


FishMonger
Veteran

Mar 2, 2013, 6:53 AM

Post #14 of 14 (307 views)
Re: [MDTech.us_MAN] Easiest way to run Microsoft SQL proc. [In reply to] Can't Post
Quote
So how do I automatically assign '' to all blank strings easily?
Add this just before the print statement;
Code
$_ //= '' for @row;
Quote
Also, how do you use Perl variables in the q/ ... /, since I need to use the email that is given as a parameter?
Use the qq operator instead of the q operator when you need variable interpolation. If you do that, you will also need to escape the @ symbols like you were doing.

This might need a slight tweak, but is how I'd format the statement for easier reading/maintenance.
Code
my $sql = qq/USE [BCPADB]; 
             DECLARE \@return_value int, 
                     \@Exists bit, 
                     \@OutErrMsg varchar(100); 
             EXEC \@return_value = [dbo].[Check_if_Rec_Exists], 
                  \@Email = N'$email', 
                  \@Exists = \@Exists OUTPUT, 
                  \@OutErrMsg = \@OutErrMsg OUTPUT;  
             SELECT \@Exists as N'\@Exists', 
                    \@OutErrMsg as N'\@OutErrMsg', 
                    \@return_value as N'Return Value';/;

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives