Calling a SQL Server Stored Procedure from within Perl

[ab]

Hi,

I'm trying to call a simple Stored Procedure from within my Perl
script. I tested the SP named "usp_test" in the Query Analyzer and it
ran OK. My perl script looks like this

use Win32::ODBC;
if (!($MyDB = new Win32::ODBC("DSN=MyDSN;UID=MyLoginID;PWD=MyPwd;")))
{
print "Error: Unable to connect to the database\n";
exit;
}

The connection is OK, I've tested it by listing all the rows in a test
table. Now I have to call the SP. I tried something like this but that
didn't work:
my $sth = $MyDB -> prepare("EXEC usp_test");
$sth -> execute;

Any information is welcome.

Thanks,
Ab

 

[smallpond]

Hi,

I'm trying to call a simple Stored Procedure from within my Perl
script. I tested the SP named "usp_test" in the Query Analyzer and it
ran OK. My perl script looks like this

use Win32::ODBC;
if (!($MyDB = new Win32::ODBC("DSN=MyDSN;UID=MyLoginID;PWD=MyPwd;")))
{
print "Error: Unable to connect to the database\n";
exit;

}

The connection is OK, I've tested it by listing all the rows in a test
table. Now I have to call the SP. I tried something like this but that
didn't work:
my $sth = $MyDB -> prepare("EXEC usp_test");
$sth -> execute;

Any information is welcome.

Thanks,
Ab

I have yet to see any computer program print the error message:
"didn't work" yet people persist in claiming that is the
result of running their program. Software vendors go to great
lengths to return useful error indicators, so why not use them?

$rv = $h->err;

"Returns the native database engine error code from the last
driver method called. The code is typically an integer but
you should not assume that."

--S

 

[Jürgen Exner]

I have yet to see any computer program print the error message:
"didn't work" yet people persist in claiming that is the
result of running their program.

LOLROTFL, YMMD!!!

May I quote those words occasionally?

jue

 

[smallpond]

LOLROTFL, YMMD!!!

May I quote those words occasionally?

jue

Maybe write an automated responder (in perl of course)
to post.

As always happens when flaming someone, I was wrong.
I think $h->err is only for the DBI modules. SQL has
it's own non-standard routine to get the error which
I am too lazy to look up.
--S

 

[smallpond]

I've had my Perl programs occasionally say "Something's wrong" when I do
a warn with an undefined variable. Does that count? ;-)

(Yes, I go back and fix the warn call if I need it.)

--keith

--
(e-mail address removed)-francisco.ca.us
(try just my userid to email me)
AOLSFAQ=http://www.therockgarden.ca/aolsfaq.txt
see X- headers for PGP signature information

perl -we 'warn $v;'
Name "main::v" used only once: possible typo at -e line 1.
Use of uninitialized value in warn at -e line 1.
Warning: something's wrong at -e line 1.

Heh. I like the message. How to warn someone of an error
when the warning has an error.
--S

 

[ab]

Thanks for those discussions about warnings and errors. It didn't help
me at all. I was merely asking for a Perl script that could execute a
stored procedure. In the meanwhile I have found a solution that works.

use Win32::ODBC;
if (!($MyDB = new Win32::ODBC("DSN=MyDSN;UID=MyLoginID;PWD=MyPwd;")))
{
print "Error: Unable to connect to the database\n";
exit;
}
$MyDB -> Run("exec usp_test'");



PS. What does LOLROTFL, YMMD mean?

 

[Jürgen Exner]

PS. What does LOLROTFL, YMMD mean?

Laughing out lout, rolling on the floor laughing, you made my day.

jue

 

[J. Gleixner]

Thanks for those discussions about warnings and errors. It didn't help
me at all. I was merely asking for a Perl script that could execute a
stored procedure. In the meanwhile I have found a solution that works.

use Win32::ODBC;
if (!($MyDB = new Win32::ODBC("DSN=MyDSN;UID=MyLoginID;PWD=MyPwd;")))
{
print "Error: Unable to connect to the database\n";
exit;

How useful is that? Why not possibly add the reason why it failed?

Taking 1 minute to scan the documentation, looks like Error is a method
that might prove useful. Also, using die is more appropriate.

perldoc -f die

}
$MyDB -> Run("exec usp_test'");

use Win32::ODBC;
my $MyDB = Win32::ODBC->new( ... );
die "blah..." unless $MyDB;

$MyDB->Run('exec usp_test');

much cleaner. IMHO.