Calling a SQL Server Stored Procedure from within Perl

Discussion in 'Perl Misc' started by ab, Nov 21, 2007.

  1. ab

    ab Guest

    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
     
    ab, Nov 21, 2007
    #1
    1. Advertising

  2. ab

    smallpond Guest

    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
     
    smallpond, Nov 21, 2007
    #2
    1. Advertising

  3. 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
     
    Jürgen Exner, Nov 21, 2007
    #3
  4. ab

    smallpond Guest

    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
     
    smallpond, Nov 21, 2007
    #4
  5. ab

    smallpond Guest

    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
    >
    > --
    > -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
     
    smallpond, Nov 21, 2007
    #5
  6. ab

    ab Guest

    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?
     
    ab, Nov 22, 2007
    #6
  7. ab wrote:
    > PS. What does LOLROTFL, YMMD mean?


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

    jue
     
    Jürgen Exner, Nov 22, 2007
    #7
  8. ab

    J. Gleixner Guest

    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.
     
    J. Gleixner, Nov 27, 2007
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?Um9iIEM=?=
    Replies:
    0
    Views:
    584
    =?Utf-8?B?Um9iIEM=?=
    Apr 16, 2005
  2. Lex
    Replies:
    3
    Views:
    7,404
    jason
    Dec 30, 2005
  3. Mike P
    Replies:
    0
    Views:
    3,372
    Mike P
    Jun 19, 2006
  4. ab
    Replies:
    1
    Views:
    410
    =?Utf-8?B?QXVndXN0aW4gUHJhc2FubmE=?=
    Aug 2, 2006
  5. Replies:
    1
    Views:
    281
    =?ISO-8859-1?Q?Arne_Vajh=F8j?=
    Apr 5, 2007
Loading...

Share This Page