On Thu, Feb 27, 2003 at 05:37:40AM -0800, Jonathan Leffler wrote:
>
> Date: Thu, 27 Feb 2003 05:37:00 -0800
> From: Jonathan Leffler <[EMAIL PROTECTED]>
> Organization: Chaotic Sputterings
> User-Agent: Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en-US; rv:1.0.2) Gecko/20021120
> Netscape/7.01
> X-Accept-Language: en-us, en
> To: Rudy Lippan <[EMAIL PROTECTED]>
> Subject: Re: ParamValues & Bind
>
> Rudy Lippan wrote:
> >On Wed, 26 Feb 2003, Tim Bunce wrote:
> >>Rudy Lippan wrote:
> >>>I know there is a {NUM_OF_PARAMS} attribute and a {ParamValues}
> >>>attribute,
> >>>but is there a way to get the names of :foo style params before they are
> >>>bound?
> >>
> >>Umm, not officially currently but there's a good chance that keys
> >>%{$sth->{ParamValues}} would work on many drivers that support it.
> >>
> >>I'll add a note to that effect in the docs so that'll become the
> >>standard way.
>
> Please don't. Some drivers are completely unable to support named
> placeholders. Specifically, DBD::Informix cannot. The notations :xxx
> and :23 can both appear outside quoted strings in contexts that do not
> have anything to do with placeholders. DBD::Informix uses its own
> pre-parser code for the same reason -- only question mark placeholders
> can work reliably. And I am 99% sure that only question marks are
> sanctioned by ODBC...
That's actually not the issue at all.
As I understand it Rudy just wanted to be able to find out what
placeholders exist in a prepared statement:
$sth = $dbh->prepare("select * from table where id = ?");
print "Has placeholder $_\n" for keys %{$sth->{ParamValues}}
would print "Has placeholder 1".
The previous specification did not make clear at what point
$sth->{ParamValues} would contain anything useful. It now says:
=item C<ParamValues> (hash ref, read-only)
Returns a reference to a hash containing the values currently bound
to placeholders. The keys of the hash are the 'names' of the
placeholders, typically integers starting at 1. Returns undef if
not supported by the driver.
See L</ShowErrorStatement> for an example of how this is used.
-> If the driver supports C<ParamValues> but no values have been bound
-> yet then the driver should return a hash with placeholders names
-> in the keys but all the values undef, but some drivers may return
-> a ref to an empty hash.
It is possible that the values in the hash returned by C<ParamValues>
are not exactly the same as those passed to bind_param() or execute().
The driver may have modified the values in some way based on the
TYPE the value was bound with. For example a floating point value
bound as an SQL_INTEGER type may be returned as an integer.
It is also possible that the keys in the hash returned by C<ParamValues>
are not exactly the same as those implied by the prepared statement.
For example, DBD::Oracle translates 'C<?>' placeholders into 'C<:pN>'
where N is a sequence number starting at 1.
> SELECT * FROM [EMAIL PROTECTED]:owner.table WHERE somecolumn BETWEEN
> DATETIME(1970:01:01 00:00:00) YEAR TO SECOND AND
> DATETIME(1999:12:31 23:59:29) YEAR TO SECOND
>
> There are no placeholders in that - but there's a :owner and eight :NN
> values in it.
If DBD::Informix parses no placeholders then DBD::Informix
simply won't put any information into $sth->{ParamValues}.
I see no problem here.
> >>A slight downside is that it won't be possible to tell the difference
> >>between a placeholder that's not been bound and one that has but
> >>was bound to undef. But I don't think that'll be a big issue.
> >>Anyone disagree?
>
> If the system will automatically bind undef for any placeholders that
> have not had an explicit value bound to them, then it should be OK.
I don't think the DBI mandates a behaviour other than execute(...)
must have no values or all values. But I don't think execute() with
no values and bind_param() calls not made for all placeholders is
explicitly defined as an error. I know that DBD::Oracle will fail
due to an error at the Oracle OCI API level.
> Otherwise, it could be problematic - people do have to explicitly bind
> NULL/undef on occasion.
Sure. The issue is only whether people *need* to be able to use
$sth->{ParamValues} to tell if a values has been bound or not.
I don't think so. If that's needed then the app can track it itself.
Tim.
