Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

The Results Are In! (Returning data from stored procedures on MS SQL and Win32 using DBI::ODBC)

by Reverend Phil (Pilgrim)
on Jun 24, 2003 at 15:15 UTC ( [id://268549]=note: print w/replies, xml ) Need Help??


in reply to Re: Yet Another Stored Procedure Question
in thread Yet Another Stored Procedure Question

Thanks to everyone who responded. A half a fresh night's sleep, 200 cathartic pages more of Harry Potter, and of course the replies I received here got me hitting things from different angles, and I've gotten it to work. I'm posting what I did here because hopefully this will help the next person who searches for stored procedures and find lots of mostly helpful tidbits =)

This is in reference (as the title suggests) to calling stored procedures via DBI&DBD::ODBC on Win32 systems to an m$ SQL server. The test table contained two fields: InField is a varchar field, and OutField is an int, autonumbering.
CREATE PROCEDURE TestProc @InField varchar(50) AS INSERT INTO TestTable (InField) VALUES (@InField) SELECT @@identity GO
For those as unfamiliar with stored procs as I am, @@identity on MS SQL returns the last selected identity-type field (ie: autonumbering) in the previous action. Here is the perl code which drops off data and picks up the auto-number field.
..connect to db.. my $sth = $dbh->prepare('{call TestProc(?)}'); my $a = "TEST"; $sth->bind_param(1, $a) or die $sth->errstr; $sth->execute(); my $results = $sth->fetch(); $sth->finish(); $dbh->disconnect; print "results are $$results[0]\n";
Not the prettiest piece of code, but hopefully it's basic enough to be clear to somebody looking to accomplish the same thing.

Thanks again for the help and the fresh angles. The myriad docs and posts on various sites all had different ways to go about it, and whether or not they work seems quite dependant on so many things.

-=rev=-

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://268549]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others wandering the Monastery: (4)
As of 2024-03-29 07:08 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found