Re: need help on $sth->bind_param

Palla, James schrieb:
> PERL.ORG DBI-USERS,
> Where can I find the valid syntax for the  DBI command "$sth->bind_param".
> 
> The perldoc DBI command shows me this:
> 
>        $rc = $sth->bind_param($p_num, $bind_value);
>        $rc = $sth->bind_param($p_num, $bind_value, $bind_type);
>        $rc = $sth->bind_param($p_num, $bind_value, \%attr);
> 
> How can I see the valid $bind_type values, the third parameter in the command   $sth->bind_param($p_num, $bind_value, $bind_type) ???
> 
> I need to know  this because of an SQL0440N error I got when running a perl program under development.  It is the first time I am using a WHERE CLAUSE LIKE predicate.  A google search suggests I need to provide a bind_parameter to resolve the SQL0440N error such as this:
> 
> $sth->bind_param( 1, 'DB', $attrib_char );
> $sth->bind_param( 2, 'TEXAS', $attrib_char );
> 
> The column in the DBI prepare that we believe is the problem is a TIMESTAMP column.
> 
> Our perl release is perl v5.8.6.
> 
> We are running DB2 V8.2 fixpack 18 on UNIX.
> 
> An help you can give us will be much appreciated.
> 
> James
> 858-246-0300
> 
> James Palla
> Database Administrator
> ACT Data Center
> University of California, San Diego
> Office:   858-246-0300
> Home:   858-538-5685
> Cell:      858-380-7912
> Fax:       858-534-8610
> Email: [email protected]
> 
>    my $sthINQ = $dbh->prepare("SELECT DBA_ID, ".
>       "CHANGE_TIME, ".
>       "FP_TICKET, ".
>       "DB_NAME, ".
>       "SCHEMA_NAME, ".
>       "OBJ_NAME, ".
>       "OBJ_TYPE, ".
>       "ENV_CODE, ".
>       "SCRIPT_DIRECTORY, ".
>       "SUBSTR(CHANGE_DESC,1,50) AS CHANGE_DESC, ".
>       "REQUESTED_BY  ".
>       "FROM DBA.DBA_CHANGE_LOG ".
>       "WHERE DBA_ID = ? ".
>       "AND CHANGE_TIME LIKE ? ".
>       "ORDER BY 1,2 ")
>       or die "prepare #4 failed: " . $dbh->errstr();
> 
>    $sthINQ->bind_param(1,$dba_id);
>    $sthINQ->bind_param(2,$change_time_LIKE);
> 
>    $sthINQ->execute()
>       or die "execute #4 failed: " . $sthINQ->errstr();
> 

Have a look at the DBI documentation:

<citation src="http://search.cpan.org/~timb/DBI-1.609/DBI.pm#bind_param">

Data Types for Placeholders

The \%attr parameter can be used to hint at the data type the placeholder should 
have. This is rarely needed. Typically, the driver is only interested in knowing 
if the placeholder should be bound as a number or a string.

   $sth->bind_param(1, $value, { TYPE => SQL_INTEGER });

As a short-cut for the common case, the data type can be passed directly, in 
place of the \%attr hash reference. This example is equivalent to the one above:

   $sth->bind_param(1, $value, SQL_INTEGER);

The TYPE value indicates the standard (non-driver-specific) type for this 
parameter. To specify the driver-specific type, the driver may support a 
driver-specific attribute, such as { ora_type => 97 }.

The SQL_INTEGER and other related constants can be imported using

   use DBI qw(:sql_types);

</citation>

For the possible values of $bind_type look in the code of DBI.pm for 'sql_types'.

Greetings

Robert