FreeTDS Development Group

["Chris McDaniel" <Chris.McDaniel AT telus.com>]

Thank you - a great leap forwards - I'm not quite there but this is a step
in the right direction.  There is actual data coming back from the query
now, I'm just not getting it in the variables.  I'm thinking it might have
to do with the position of the placeholders...  I have posted to the
dbi-users list on this issue, hopefully I will get a response this time.
I've updated the trace at http://nicotine.golden.net/~chrism/freetds.log -
I'm just wondering if my new issue might have to do with the position of the
placeholders.  Here's my new code snippet:
$sth = $dbh->prepare('
       declare @DDI UDTDDI, [..] @AvailableInd UDTFlag
       select @DDI = ?, [...] @AvailableInd = ?
       execute p_ccs_MakeBooking @ClientAccount="xxxxxx", [...] @DDI=@DDI
OUTPUT, 
[...] @AvailableInd=@AvailableInd OUTPUT');

# bind_param_inout(placeholder position, \bind_var, max_len, type) 
# the values are assigned to the referenced variables earlier in the
script...
$sth->bind_param_inout(1, \$DDI, 32, DBI::SQL_VARCHAR);
[...]
$sth->bind_param_inout(8, \$AvailInd, 1, DBI::SQL_BIT);
$sth->execute(); 

So I was wondering if it were possible to somehow push the values into the
@variables after the query...  Maybe I'm just being naive thinking that the
placeholders are being updated because the exec hasn't run yet when they are
bound, but that's all I can come up with right now.

Thanks again!

Chris

-----Original Message-----
From: James K. Lowden [mailto:jklowden AT schemamania.org]
Sent: January 7, 2003 12:03 AM
To: freetds AT lists.ibiblio.org
Subject: Re: [freetds] DBD::ODBC (0.45), MS SQL, unixODBC, FreeTDS
(CVS), bind_param_ino ut


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
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds