Output param come back just like you would expect
my $sth = $db->prepare(qq|SavePlanMember [EMAIL PROTECTED] = 1111, [EMAIL PROTECTED]
= 1|)
or die "Can't Prepare " . $db->errstr();
$sth->execute() or die "Can't Execute " . $sth->errstr();
my @ROW = ()
while (@ROW = $sth->fetchrow_array()) {
..ROW is SP output param
}
$sth->finish().
Or if what is coming back is a single row
my $sth = $db->prepare(qq|SavePlanMember [EMAIL PROTECTED] = 1111, [EMAIL PROTECTED]
= 1|)
or die "Can't Prepare " . $db->errstr();
$sth->execute() or die "Can't Execute " . $sth->errstr();
my @ROW = $sth->fetchrow_array();
$sth->finish()
I never found any docs that really helped as it wouldn't account for the
fact that I was using freeTDS or it wouldn't take into account that it was
MS-SQL etc...
In the end, its not all that different then using MySQL with no SPs, quoting
is a real pain in the butt but that is because I've always been able to use
placeholders in the past.
The only real difference is with the syntax used in the SP, for example in
my case both of these are valid
my $sth = $db->prepare(qq|SavePlanMember [EMAIL PROTECTED] = 1111, [EMAIL PROTECTED]
= 1|)
my $sth = $db->prepare(qq|SavePlanMember 1111, 1|)
If you do it the first way (name the fields), then you can pass things into
the SP in any order, if you do the second way (just values) then you have to
send the values in order (which is a pain).
I've found all the higher methods (selectall_arrayref, hashref, DO etc) all
work pretty much exactly the same way.
Hope this helps.
-Chris
----- Original Message -----
From: "v79k" <[EMAIL PROTECTED]>
To: "Chris Faust" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, April 21, 2004 11:07 AM
Subject: Re: Syntax for calling stored procedure from Perl Script
> Thanks. It worked.
> And what about output parameters? Suppose the stored
> procedure returns a count. How do we do that? Sorry,
> but this just came up.
>
>
> Is there a website that I can refer that has code
> snippets and the syntax to call stored procedures from
> Perl with FreeTDS? Mailing list is fine, but if I have
> to ask several questions I'd rather look it up on the
> website rather than waste someone's time everytime I
> have a problem. Moreover, this is a syntax issue.
>
> Thanks,
> vk
>
>
> --- Chris Faust <[EMAIL PROTECTED]> wrote:
> > Here is a simple example of a SP and the code for it
> > that I use.
> >
> > create procedure SavePlanMember
> > @iPlanId int,
> > @iUserId int
> > as
> >
> > INSERT INTO PlanMembers
> > (iPlanId, iUserId)
> > VALUES (@iPlanId, @iUserId)
> >
> >
> > my $sth = $db->prepare(qq|SavePlanMember [EMAIL PROTECTED]
> > = 1111, [EMAIL PROTECTED] = 1|)
> > or die "Can't Prepare " . $db->errstr();
> > $sth->execute() or die "Can't Execute "
> > $sth->errstr();
> >
> > I'm using FreeTDS and DBD::Sybase so I can't use
> > placeholders with MS-SQL..
> >
> > Hope this helps.
> >
> > -Chris
> > ----- Original Message -----
> > From: "v79k" <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Sent: Tuesday, April 20, 2004 12:45 PM
> > Subject: Syntax for calling stored procedure from
> > Perl Script
> >
> >
> > > Hi,
> > > My Perl script connects to the database(SQL Server
> > > 2000) and queries it. Also updates a column.
> > > It works fine but now I have to do it with stored
> > > procedures. I created the stored procedures
> > > but am unsure of the syntax to call it from Perl
> > > script. The procedure takes in one parameter,
> > > a perl variable say $x.
> > >
> > > I even tried without paramaters but in either case
> > get
> > > the following error.
> > >
> > > my $query = $dbh->prepare("{call procedureName
> > ($x)
> > > }");
> > > $query->execute(); # Execute SQL Query!
> > >
> > > Error: Incorrect syntax near '{'...
> > >
> > >
> > > I must pass parameters to the stored procedure.
> > the
> > > procedure exists in the database.
> > >
> > > Any help/comments are appreciated.
> > >
> > > Thanks,
> > > vk
> > >
> > >
> > >
> > >
> > > __________________________________
> > > Do you Yahoo!?
> > > Yahoo! Photos: High-quality 4x6 digital prints for
> > 25c
> > > http://photos.yahoo.com/ph/print_splash
> > >
> >
>
>
>
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Photos: High-quality 4x6 digital prints for 25c
> http://photos.yahoo.com/ph/print_splash
>
