Re: Help sought with definition and implementation of ParamTypes attribute

H.Merijn Brand wrote:
> On Fri, 10 Oct 2008 16:19:20 +0100, Martin Evans
> <[email protected]> wrote:
> 
>> Hi,
>>
>> The DBI specification for ParamTypes taken from the DBI pod says the 
>> following for ParamTypes:
>>
>> Returns a reference to a hash containing the type information currently 
>> bound to placeholders.  The keys of the hash are the ’names’ of the 
>> placeholders: either integers starting at 1, or, for drivers that 
>> support named placeholders, the actual parameter name string. The hash 
>> values are hashrefs of type information in the same form as that 
>> provided to the various bind_param() methods (See "bind_param" for the 
>> format and values), plus anything else that was passed as the third 
>> argument to bind_param().  Returns undef if not supported by the driver.
>>
>> I'm not sure why the values of the keys are hash references unless
> 
> because if only the numeric values were supported, it would have been a
> list, not a hash, but when placeholder names come in sight, a list
> would not do
> 
> $sth = $dbh->prepare ("select * from xx where xs between ? and ? or xc = ?");
> $sth->execute (4, 7, "0");
> 
> 	ParamValues   => { 1 => 4, 2 => 7, 3 => "0" },
> 	ParamTypes    => { 1 => 5, 2 => 5, 3 => 1   },
> 
> If param names are supported, that might look like
> 
> 	ParamValues   => { foo => 4, bar => 7, baz => "0" },
> 	ParamTypes    => { foo => 5, bar => 5, baz => 1   },

but here the keys 1, 2, 3 or foo, bar and baz do not have hash 
references as values as per "the hash values are hashrefs" they have 
scalars as values. What I understood "the hash values are hashrefs" to 
mean was:

ParamTypes => {1 => {something => x, somethingelse => y},
                2 => {something => x, somethingelse => y}}

and I was questioning what the "something" and "somethingelse" were 
since I am only aware of a "type".


>> multiple values are to be stored. If multiple values per key are stored 
>> what are they typically? I can only find one DBD which implements 
>> ParamTypes (DBD::Pg) and unless I am mistaken it sets the values of the 
> 
> I implemented it in DBD::Unify as of 0.75 in a bigger patch to
> implement as much as possible of the DBI defenition:

thanks for that pointer - I missed DBD::Unify

> *** Release 0.75 - Tue 23 Sep 2008 <[email protected]>
> 
>     - Three-level dbd_verbose and documentation
>     - $ENV{DBD_TRACE} sets $dbh->{dbd_verbose} on/before connect
>     - New tests for $h->trace (...) and $h->{dbd_verbose}
>     - Added type_info_all (), get_info (), and parse_trace_flag ()
>     - Note that identifiers are now quoted
>     - Override quote_identifier () (UNIFY has no CATALOGS)
>     - Accept 2-arg and 3-arg ->do ()
>     - Accept %attr to ->prepare ()
>     - Raised all verbose levels by 1. 1 and 2 are now DBI only
>     - Removed 05-reauth.t
>     - NULLABLE now always 2, as it doesn't work
>     - Implemented CursorName  sth attribute
>     - Implemented ParamValues sth attribute
>     - Implemented ParamTypes  sth attribute
>     - Implemented RowsInCache sth attribute (always 0)
>     - Tested with Unify 6.3AB on HP-UX 10.20 with perl 5.8.8
>     - Tested with Unify 8.2BC on HP-UX 11.00 with perl 5.8.8
>     - Tested with Unify 8.3I  on HP-UX 11.23 with perl 5.10.0
>     - Tested with Unify 8.3K  on AIX 5.2.0.0 with perl 5.8.8
>       Tests will fail on older perls, as the test cases use scalarIO
> 
>> keys to a scalar value - the type of the parameter.
> 
> in dbd_st_FETCH_aatrib ()
> 
>     if (kl == 10 && strEQ (key, "ParamTypes")) {
> 	HV *hv = newHV ();
> 	retsv  = newRV (sv_2mortal ((SV *)hv));
> 	while (--p >= 0) {
> 	    char key[8];
> 	    sprintf (key, "%d", p + 1);
> 	    hv_store (hv, key, strlen (key), newSViv (imp_sth->prm[p].ftp), 0);
> 	    }
> 	}

So you seem to have implemented it like DBD::Pg but that does not seem 
to agree with how ParamTypes is documented.



>> Reason I'm asking is it is on my to do list for DBD::ODBC.
> 
> The test case in t/20-uni-basic.t now looks like
> 
> ok ($sth = $dbh->prepare ("select * from xx where xs between ? and ? or xc = ?"), "sel prepare");
> ok ($sth->execute (4, 7, "0"), "execute");
> ok (1, "-- Check the internals");
> {   my %attr = (	# $sth attributes as documented in DBI-1.607
> 	NAME          => [qw( xs xl xc xf xr xa xh xT xd xe )],
> 	NAME_lc       => [qw( xs xl xc xf xr xa xh xt xd xe )],
> 	NAME_uc       => [qw( XS XL XC XF XR XA XH XT XD XE )],
> 	NAME_hash     => {qw( xs 0 xl 1 xc 2 xf 3 xr 4 xa 5 xh 6 xT 7 xd 8 xe 9 )},
> 	NAME_lc_hash  => {qw( xs 0 xl 1 xc 2 xf 3 xr 4 xa 5 xh 6 xt 7 xd 8 xe 9 )},
> 	NAME_uc_hash  => {qw( XS 0 XL 1 XC 2 XF 3 XR 4 XA 5 XH 6 XT 7 XD 8 XE 9 )},
> 	uni_types     => [ 5, 2, 1, 8, 7, -4, -6, -7, -3, -11],
> 	TYPE          => [ 5, 2, 1, 8, 7, 6, 7, 10, 9, 11],
> 	PRECISION     => [ 4, 9, 5, 64, 32, 9, 15, 0, 0, 0],
> 	SCALE         => [ 0, 0, 0, 0, 0, 2, 2, 0, 0, 0],
> #	NULLABLE      => [ 0, 1, 1, 1, 1, 1, 1, 1, 1, 1], # Does not work in Unify (yet)
> 	NULLABLE      => [ 2, 2, 2, 2, 2, 2, 2, 2, 2, 2],
> 	CursorName    => "c_sql_00_000001",
> 	NUM_OF_FIELDS => 10,
> 	NUM_OF_PARAMS =>  3,
> 	Database      => $dbh,
> 	ParamValues   => { 1 => 4, 2 => 7, 3 => "0" },
> 	ParamTypes    => { 1 => 5, 2 => 5, 3 => 1   },
> 	ParamArrays   => undef, # NYI
> 	RowsInCache   => 0,
> 	);
>     foreach my $attr (sort keys %attr) {
> 	#printf STDERR "\n%-20s %s\n", $attr, "@{$sth->{$attr}}";
> 	my $av = exists $sth->{$attr} ? $sth->{$attr} : undef;
> 	is_deeply ($av, $attr{$attr}, "attr $attr");
> 	}
>     }
> 
> 

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com