| Author |
Calling a SQL Server Stored Procedure from within Perl
|
|
|
| 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 2007-11-21, 10:07 pm |
| On Nov 21, 10:41 am, ab <absmi...@hotmail.com> wrote:
> 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 2007-11-21, 10:07 pm |
| smallpond wrote:
> 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
| |
| Keith Keller 2007-11-21, 10:07 pm |
| On 2007-11-21, smallpond <smallpond@juno.com> wrote:
>
> 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.
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
--
kkeller-usenet@wombat.san-francisco.ca.us
(try just my userid to email me)
AOLSFAQ=http://www.therockgarden.ca/aolsfaq.txt
see X- headers for PGP signature information
| |
| smallpond 2007-11-21, 10:07 pm |
| On Nov 21, 12:56 pm, "J=FCrgen Exner" <jurge...@hotmail.com> wrote:
> smallpond wrote:
>
> 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 2007-11-21, 10:07 pm |
| On Nov 21, 1:57 pm, Keith Keller <kkeller-use...@wombat.san-
francisco.ca.us> wrote:
> On 2007-11-21, smallpond <smallp...@juno.com> wrote:
>
>
>
>
> 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
>
> --
> kkeller-use...@wombat.san-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
| |
| Keith Keller 2007-11-22, 4:23 am |
| On 2007-11-21, smallpond <smallpond@juno.com> wrote:
>
> 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.
Well, the scenario I had in mind is more like
perl -we '$v="";warn $v;'
Even less informative than your example. :)
--keith
--
kkeller-usenet@wombat.san-francisco.ca.us
(try just my userid to email me)
AOLSFAQ=http://www.therockgarden.ca/aolsfaq.txt
see X- headers for PGP signature information
| |
| Sherman Pendley 2007-11-22, 8:04 am |
| smallpond <smallpond@juno.com> writes:
> I think $h->err is only for the DBI modules.
True, but I thought the OP is using DBI?
> SQL has
> it's own non-standard routine to get the error which
> I am too lazy to look up.
True again, but isn't it DBD::*'s job to wrap that non-standard routine
with a standard DBI interface?
sherm--
--
WV News, Blogging, and Discussion: http://wv-www.com
Cocoa programming in Perl: http://camelbones.sourceforge.net
| |
|
| 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 2007-11-22, 7:06 pm |
| ab wrote:
> PS. What does LOLROTFL, YMMD mean?
Laughing out lout, rolling on the floor laughing, you made my day.
jue
| |
| J. Gleixner 2007-11-27, 7:10 pm |
| ab wrote:
> 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.
|
|
|
|