FreeTDS Development Group

["James K. Lowden" <jklowden AT schemamania.org>]

On Tue, 7 Jan 2003 00:18:58 -0700, "Chris McDaniel"
<Chris.McDaniel AT telus.com> wrote:
> 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...  

Right.  The log shows 8 0xAC (parameter response) tokens.  Yay!  

AFAICT from the documentation, DBI is pretty simple-minded about
placeholders.  You put a '?' wherever you want to substitute a value, and
use bind_param_inout() to provide two things:

1.  the input substitution string.
2.  the host variable to receive the output.

DBI hands the query to DBD, which relates the perl host variable to a
buffer for the ODBC driver to fill.  DBD call the driver's "bind a
parameter" function, calls its "do the query function", calls its "get the
param data" function.  If all goes well, you get your data.  

It's up to you to make sure the placeholders are correctly situated to
effect the string substitution.  The mapping of output parameters to host
variables is completely dependent on the position of those placeholders
and the query text, because the ODBC functions don't relate a parameter
name to your buffer; it's all done positionally.  That is, when you say:

> $sth->bind_param_inout(1, \$DDI, 32, DBI::SQL_VARCHAR);

you're not telling the *server* anything.  You're telling libodbc:

1.  replace the first '?' with '32', and
2.  write the results of the the first output parameter to $DDI

If the first '?' isn't located where the first output parameter should be,
well, that's tough cookies.  I can't say what I mean without an example. 
Suppose you had a proc:

create proc A 
@a int output, @b int output 
as 
select @a=8, @b=9

you'd expect:
        declare @x int, @y int 
        exec A @a=@x output, @b=@y output 
        select @x, @y

to return the integers 8 and 9.  ODBC-wise, you'd write:

prepare: 
        declare @x int, @y int 
        exec A @a=? output, @b=? output 
bind:
        1, $x, @x, DBI::SQL_INT
        2, $y, @y, DBI::SQL_INT

and, lo, $x and $y would get 8 and 9 respectively (let us hope).  But,
suppose you did this:

prepare: 
        declare @x int, @y int 
        exec A @a=?,  @b=? output 
bind:
        1, $x, @x, DBI::SQL_INT
        2, $y, @y, DBI::SQL_INT

forgetting the first "output" string.  The server sees:

        exec A @a=@x,  @b=@y output 

The strings are substituted correctly; you told libodbc to replace the
second '?' with '@y'.  But you also told it to write the first output
parameter to $x, which will happily do, and $x will get the value of @y:
9.  Oops.  When you go looking for the output for the second parameter,
there is nonesuch; you get an error instead.  Every ODBC driver I've ever
used would say something helpful like "General memory error". 
Translation: "You forgot something".  Perhaps we can do better.  

To read the parameter data, DBD::ODBC will call SQLParamData().  I'm too
tired now to be trusted, but you might want to add a line there to the
log.  You could capture success/failure, stmt->prepared_query_param_num,
the data and its length.  That way, it would be clear what's being handed
back to DBD::ODBC.  

We should extend src/odbc/unittests/t0004.c to illustrate the use of
placeholders with named output parameters.  Last time Frediano and I
discussed it, we decided IIRC that it was impossible (because parameters
aren't passed by name).  Now, it looks possible.  To me, anyway.  

> I have posted to the
> dbi-users list on this issue, hopefully I will get a response this time.

You might want to double-check some ODBC documentation, too.  Keep in mind
I haven't done this and I have no knowledge of Perl internals.  I'm just
looking at the interfaces and inferring how everything bolts together. 
I'm happy to be corrected.  

If someone's done this with FreeTDS's ODBC and perl, I don't know about
it.  How's it feel out there on the bleeding edge?  

Regards, 

--jkl




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