view release on metacpan or  search on metacpan

DBI.pm  view on Meta::CPAN

As an alternative to specifying the data type in the C<bind_param> call,
you can let the driver pass the value as the default type (C<VARCHAR>).
You can then use an SQL function to convert the type within the statement.
For example:

  INSERT INTO price(code, price) VALUES (?, CONVERT(MONEY,?))

The C<CONVERT> function used here is just an example. The actual function
and syntax will vary between different databases and is non-portable.

See also L</"Placeholders and Bind Values"> for more information.


=item C<bind_param_inout>

  $rc = $sth->bind_param_inout($p_num, \$bind_value, $max_len)  or die $sth->errstr;
  $rv = $sth->bind_param_inout($p_num, \$bind_value, $max_len, \%attr)     or ...
  $rv = $sth->bind_param_inout($p_num, \$bind_value, $max_len, $bind_type) or ...

This method acts like L</bind_param>, but also enables values to be
updated by the statement. The statement is typically
a call to a stored procedure. The C<$bind_value> must be passed as a
reference to the actual value to be used.

Note that unlike L</bind_param>, the C<$bind_value> variable is not
copied when C<bind_param_inout> is called. Instead, the value in the
variable is read at the time L</execute> is called.

The additional C<$max_len> parameter specifies the minimum amount of
memory to allocate to C<$bind_value> for the new value. If the value
returned from the database is too
big to fit, then the execution should fail. If unsure what value to use,
pick a generous length, i.e., a length larger than the longest value that would ever be
returned.  The only cost of using a larger value than needed is wasted memory.

Undefined values or C<undef> are used to indicate null values.
See also L</"Placeholders and Bind Values"> for more information.


=item C<bind_param_array>

  $rc = $sth->bind_param_array($p_num, $array_ref_or_value)
  $rc = $sth->bind_param_array($p_num, $array_ref_or_value, \%attr)
  $rc = $sth->bind_param_array($p_num, $array_ref_or_value, $bind_type)

The C<bind_param_array> method is used to bind an array of values
to a placeholder embedded in the prepared statement which is to be executed
with L</execute_array>. For example:

  $dbh->{RaiseError} = 1;        # save having to check each method call
  $sth = $dbh->prepare("INSERT INTO staff (first_name, last_name, dept) VALUES(?, ?, ?)");
  $sth->bind_param_array(1, [ 'John', 'Mary', 'Tim' ]);
  $sth->bind_param_array(2, [ 'Booth', 'Todd', 'Robinson' ]);
  $sth->bind_param_array(3, "SALES"); # scalar will be reused for each row
  $sth->execute_array( { ArrayTupleStatus => \my @tuple_status } );

The C<%attr> ($bind_type) argument is the same as defined for L</bind_param>.
Refer to L</bind_param> for general details on using placeholders.

(Note that bind_param_array() can I<not> be used to expand a
placeholder into a list of values for a statement like "SELECT foo
WHERE bar IN (?)".  A placeholder can only ever represent one value
per execution.)

Scalar values, including C<undef>, may also be bound by
C<bind_param_array>. In which case the same value will be used for each
L</execute> call. Driver-specific implementations may behave
differently, e.g., when binding to a stored procedure call, some
databases may permit mixing scalars and arrays as arguments.

The default implementation provided by DBI (for drivers that have
not implemented array binding) is to iteratively call L</execute> for
each parameter tuple provided in the bound arrays.  Drivers may
provide more optimized implementations using whatever bulk operation
support the database API provides. The default driver behaviour should 
match the default DBI behaviour, but always consult your driver
documentation as there may be driver specific issues to consider.

Note that the default implementation currently only supports non-data
returning statements (INSERT, UPDATE, but not SELECT). Also,
C<bind_param_array> and L</bind_param> cannot be mixed in the same
statement execution, and C<bind_param_array> must be used with
L</execute_array>; using C<bind_param_array> will have no effect
for L</execute>.

The C<bind_param_array> method was added in DBI 1.22.

=item C<execute>

  $rv = $sth->execute                or die $sth->errstr;
  $rv = $sth->execute(@bind_values)  or die $sth->errstr;

Perform whatever processing is necessary to execute the prepared
statement.  An C<undef> is returned if an error occurs.  A successful
C<execute> always returns true regardless of the number of rows affected,
even if it's zero (see below). It is always important to check the
return status of C<execute> (and most other DBI methods) for errors
if you're not using L</RaiseError>.

For a I<non>-C<SELECT> statement, C<execute> returns the number of rows
affected, if known. If no rows were affected, then C<execute> returns
"C<0E0>", which Perl will treat as 0 but will regard as true. Note that it
is I<not> an error for no rows to be affected by a statement. If the
number of rows affected is not known, then C<execute> returns -1.

For C<SELECT> statements, execute simply "starts" the query within the
database engine. Use one of the fetch methods to retrieve the data after
calling C<execute>.  The C<execute> method does I<not> return the number of
rows that will be returned by the query (because most databases can't
tell in advance), it simply returns a true value.

If any arguments are given, then C<execute> will effectively call
L</bind_param> for each value before executing the statement.  Values
bound in this way are usually treated as C<SQL_VARCHAR> types unless
the driver can determine the correct type (which is rare), or unless
C<bind_param> (or C<bind_param_inout>) has already been used to
specify the type.

If execute() is called on a statement handle that's still active
($sth->{Active} is true) then it should effectively call finish()