Hi folks. I've looked through a slew of DBI/stored procedure nodes here and elsewhere on the web. I've gone through the DBI and DBD::ODBC documentation. I've gotten most of the way there...

I have a stored procedure which inserts data into a table, and returns the auto-generated key field. I can successfully call this to insert the data. If I try to get the return value, I'm hosed.

I've gone and individually used bind_param_inout on each of my fields that I'm inserting, but I can't for the life of me figure out how I'm supposed to bind an output value. The problem is, the field I'm returning is not one of the fields I'm inserting.. so it does not have an index to bind to.

The code is really not so important, and a mess right now. The error, however, might shed some light.

If I bind each of the parameters individually, I get:
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL 
+Server]Formal parameter '@PONumber' was defined as OUTPUT but the act
+ual parameter not declared OUTPUT. (SQL-42000)(DBD: st_execute/SQLExe
+cute err=-1) at store_test.pl line 46.

[download]
Bind Param is called in the following manner:
$sth->bind_param_inout(1,\$a, 255) or die $sth->errstr;

[download]
..where 1 is the index (1 not 0 based) of the parameter in the list of expected parameters.

I'm probably missing something silly.. but then again, it seems like stored procedures have been a recurring question here, and on other sites, with often incomplete answers.

Thanks in advance,
-=rev=-

In reply to Yet Another Stored Procedure Question by Reverend Phil

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.