Skip to Content.
Sympa Menu

freetds - Re: [freetds] DBD::ODBC (0.45), MS SQL, unixODBC, FreeTDS (CVS) , bind_param_ino ut

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "James K. Lowden" <jklowden AT schemamania.org>
  • To: freetds AT lists.ibiblio.org
  • Subject: Re: [freetds] DBD::ODBC (0.45), MS SQL, unixODBC, FreeTDS (CVS) , bind_param_ino ut
  • Date: Tue, 7 Jan 2003 00:03:05 -0500

On Mon, 6 Jan 2003 17:51:07 -0700, "Chris McDaniel"
<Chris.McDaniel AT telus.com> wrote:
> I thought about that, but I get an error back saying I can't use the
> OUTPUT notation if I'm passing in a constant, but passing a null breaks
> DBI, so I'm a little confused... I am not a T-SQL expert by any
> stretch, but I was guessing that since the variables are declared with
> the output statement in the stored procedure, maybe I didn't have to do
> it too.

I guess after 10 years of experience with T-SQL, I'll claim to be an
expert. :-)

This for sure: if you don't call a proc with "output" after the variable
name, the server won't write to it.

You may not do:
exec procname 'data' output
or
exec procname @paramname = 'data' output

because there's nowhere for the server to write its output. That would
yield the "passing a constant" error.

To provide a place to write the output, declare a variable:

declare @out exec procname @paramname=@out output

Here's an example session:

[begin session]
1> create proc a @io int output as select @io=1
2> go
1> exec a 1 -- OK, passes constant as input
2> exec a @io=9 -- OK, passes constant as named input
3> go
(return status = 0)
(return status = 0)
1> exec a @io=9 output
2> go -- not OK: server cannot write return value to constant '9'
Msg 179, Level 15, State 1, Server CPRO200, Line 1
Cannot use the OUTPUT option when passing a constant to a stored
procedure.
1> declare @o int select @o=9 exec a @io=@o output select @o
2> go -- OK, server writes return value of @io to variable '@o'
(return status = 0)

1
declare @o int select @o=9 exec a @o output select @o
go -- OK, passes @o variable as positional output parameter
(return status = 0)

1
[end session]

That's what has to arrive at the server. How you conjure that from
DBD::ODBC, I don't know because I don't use it. :( I'd be tempted to try
something like this:

$sth = $dbh->prepare('
declare @DDI int, [...] @AvailableInd int
select @DDI = ? [...] @AvailableInd = ?
execute p_ccs_MakeBooking [...] @AvailableInd=@AvailableInd OUTPUT'
$sth->bind_param_inout(8, \$AvailInd, 1, DBI::SQL_BIT);

or perhaps:

$sth = $dbh->prepare('
declare @DDI int, [...] @AvailableInd int
select @DDI = 32 [...] @AvailableInd = 1
execute p_ccs_MakeBooking [...] @AvailableInd=? OUTPUT'
$sth->bind_param_inout(8, \$AvailInd, '@AvailableInd', DBI::SQL_BIT);

which I'd hope to yield:

execute p_ccs_MakeBooking [...] @AvailableInd=@AvailableInd OUTPUT

which would at least give the server something it recognizes.

Hope that's of some help.

--jkl




Archive powered by MHonArc 2.6.24.

Top of Page