Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Perl > Perl Misc > Calling a SQL Server Stored Procedure from within Perl

Reply
Thread Tools

Calling a SQL Server Stored Procedure from within Perl

 
 
ab
Guest
Posts: n/a
 
      11-21-2007
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




 
Reply With Quote
 
 
 
 
smallpond
Guest
Posts: n/a
 
      11-21-2007
On Nov 21, 10:41 am, ab <(E-Mail Removed)> 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
 
Reply With Quote
 
 
 
 
Jürgen Exner
Guest
Posts: n/a
 
      11-21-2007
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


 
Reply With Quote
 
smallpond
Guest
Posts: n/a
 
      11-21-2007
On Nov 21, 12:56 pm, "Jürgen Exner" <(E-Mail Removed)> 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
 
Reply With Quote
 
smallpond
Guest
Posts: n/a
 
      11-21-2007
On Nov 21, 1:57 pm, Keith Keller <(E-Mail Removed)-
francisco.ca.us> wrote:
> On 2007-11-21, smallpond <(E-Mail Removed)> 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
>
> --
> (E-Mail 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
 
Reply With Quote
 
ab
Guest
Posts: n/a
 
      11-22-2007
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?
 
Reply With Quote
 
Jürgen Exner
Guest
Posts: n/a
 
      11-22-2007
ab wrote:
> PS. What does LOLROTFL, YMMD mean?


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

jue


 
Reply With Quote
 
J. Gleixner
Guest
Posts: n/a
 
      11-27-2007
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.
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calling Stored procedure within the Orcale Server or outside client aadilnw@gmail.com Java 1 04-05-2007 01:10 AM
Get an error code after calling a SQL Server Stored Procedure. ab ASP .Net 1 08-02-2006 10:37 AM
'Procedure or function <stored procedure name> has too many arguments specified',,,ARGH! Mike P ASP .Net 0 06-19-2006 01:19 PM
updating multiple rows in MS SQL by way of calling stored procedure takes forever Lex Java 3 12-30-2005 02:15 PM
Hitting Sql Server Stored Procedure Breakpoint from ASP.NET app =?Utf-8?B?Um9iIEM=?= ASP .Net 0 04-16-2005 12:27 AM



Advertisments