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

Re: Calling a SQL Server Stored Procedure from within Perl

On Nov 21, 10:41 am, ab 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

Re: Calling a SQL Server Stored Procedure from within Perl

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

Re: Calling a SQL Server Stored Procedure from within Perl

On 2007-11-21, 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.

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

Re: Calling a SQL Server Stored Procedure from within Perl

On Nov 21, 12:56 pm, "Jürgen Exner" wrote:
> 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

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

Re: Calling a SQL Server Stored Procedure from within Perl

On Nov 21, 1:57 pm, Keith Keller francisco.ca.us> wrote:
> On 2007-11-21, 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.
>
> 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

Re: Calling a SQL Server Stored Procedure from within Perl

On 2007-11-21, smallpond 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

Re: Calling a SQL Server Stored Procedure from within Perl

smallpond 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

Re: Calling a SQL Server Stored Procedure from within Perl

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?

Re: Calling a SQL Server Stored Procedure from within Perl

ab wrote:
[snip]
>
> PS. What does LOLROTFL, YMMD mean?

http://acronyms.thefreedictionary.com

--

Len

Re: Calling a SQL Server Stored Procedure from within Perl

ab wrote:
> PS. What does LOLROTFL, YMMD mean?

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

jue

Re: Calling a SQL Server Stored Procedure from within Perl

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.