On Sun, 2002-05-12 at 13:11, Tim Bunce wrote: > [I've come back to look at this thread that I filed a while ago...] > > On Wed, Feb 13, 2002 at 01:04:15PM -0800, Michael Peppler wrote: > > Hi, > > > > The DBI docs state the following:
<snip>
> > However this syntax does NOT work with DBD::Sybase because of the way
> > OpenClient handles the prepare. The issue is the "? is null" part:
> > neither the Sybase client libs, nor the server can determine what
> > datatype the placeholder is supposed to be, and I see no way to add a
> > directive to tell the server that this item is of the same type as
> > "product_code".
>
> Why do you have to?
The problem is that a "prepared" statement with ?-style placeholders is
sent as-is to the server, which compiles it, and generates the
appropriate temporary stored procedure for it.
The (? is null) bit generates the following error message from Sybase:
ct_result(ct_dynamic(CS_PREPARE)) returned -205 at
/usr/lib/perl5/site_perl/5.6.0/i386-linux/DBD/Sybase.pm line 105.
DBD::Sybase::db prepare failed: Server message number=12828 severity=16
state=1 line=1 server=troll procedure=DBD2 text=The datatype of a
parameter marker used in the dynamic prepare statement could not be
resolved.
During the prepare() call Sybase will resolve all the input parameters,
and determine the datatypes for each of them. But in the case of the (?
is null) construct the '?' does not resolve to an existing column, so
Sybase errors out.
> If "neither the Sybase client libs, nor the server can determine
> what datatype the placeholder is supposed to be" can't you just
> bind it as a varchar or similar?
There is no way to bind the parameter as SQL_VARCHAR (or whatever),
because the error occurs on the prepare, not on the bind or execute.
> Are there any other valid sql statements that have this problem?
> (Like using placeholders as parameters for functions that take
> different kind of datatypes.)
I don't know - I just tried the following:
$dbh->prepare("select convert(varchar, ?, 109)");
which promptly failed with
The untyped variable ? is allowed only in in a WHERE clause or the SET
clause of an UPDATE statement or the VALUES list of an INSERT statement.
In this case the convert() call would be expecting a DATETIME
column/variable.
I tried this also with
select sin(?)
which clearly expects a float variable, and that fails with the same
error.
I don't know if this sort of prepare() would be valid with Oracle (or
any other db engine), but it is definitely a no-no with Sybase.
This means that
update foo
set bar = isnull(?, 'some string')
where baz = some value
is also be illegal (where isnull() returns the second parameter if the
first is null.
> > Sybase, on the other hand, understands "= null".
>
> What do "the Sybase client libs, nor the server" say about the
> datatype of this placeholder: "? = null"
Nothing - Sybase complains that it can't tell what datatype it is.
And checking this again it's the database server that complains. The
client libraries don't come into play here.
> > I'll document this in the DBD::Sybase docs, but I just wanted to flag
> > the fact that the method documented in the DBI docs is unfortunately
> > not portable to Sybase (and *probably* not to ODBC when using a
> > Sybase). I don't know if ODBC w/MS-SQL handles this correctly or not.
>
> What wording have you added to the docs? [I'm trying to work out
> what to say in the DBI docs.]
Looks like I haven't done this yet.
I'll try to formulate something in the next few days - I'll let you
know.
Michael
--
Michael Peppler Data Migrations, Inc.
[EMAIL PROTECTED] *or* [EMAIL PROTECTED]
http://www.mbay.net/~mpeppler
International Sybase User Group: http://www.isug.com
signature.asc
Description: This is a digitally signed message part
