Re: Help sought with definition and implementation of ParamTypes attribute

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   },

> 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:

*** 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);
	    }
	}

> 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");
	}
    }


-- 
H.Merijn Brand          Amsterdam Perl Mongers  http://amsterdam.pm.org/
using & porting perl 5.6.2, 5.8.x, 5.10.x, 5.11.x on HP-UX 10.20, 11.00,
11.11, 11.23, and 11.31, SuSE 10.1, 10.2, and 10.3, AIX 5.2, and Cygwin.
http://mirrors.develooper.com/hpux/           http://www.test-smoke.org/
http://qa.perl.org      http://www.goldmark.org/jeff/stupid-disclaimers/