view release on metacpan or  search on metacpan

DBI.pm  view on Meta::CPAN

Using placeholders and C<@bind_values> with the C<do> method can be
useful because it avoids the need to correctly quote any variables
in the C<$statement>. But if you'll be executing the statement many
times then it's more efficient to C<prepare> it once and call
C<execute> many times instead.

The C<q{...}> style quoting used in this example avoids clashing with
quotes that may be used in the SQL statement. Use the double-quote-like
C<qq{...}> operator if you want to interpolate variables into the string.
See L<perlop/"Quote and Quote-like Operators"> for more details.

Note drivers are free to avoid the overhead of creating an DBI
statement handle for do(), especially if there are no parameters. In
this case error handlers, if invoked during do(), will be passed the
database handle.

=head3 C<last_insert_id>

  $rv = $dbh->last_insert_id();
  $rv = $dbh->last_insert_id($catalog, $schema, $table, $field);
  $rv = $dbh->last_insert_id($catalog, $schema, $table, $field, \%attr);

Returns a value 'identifying' the row just inserted, if possible.
Typically this would be a value assigned by the database server
to a column with an I<auto_increment> or I<serial> type.
Returns undef if the driver does not support the method or can't
determine the value.

The $catalog, $schema, $table, and $field parameters may be required
for some drivers (see below).  If you don't know the parameter values
and your driver does not need them, then use C<undef> for each.

There are several caveats to be aware of with this method if you want
to use it for portable applications:

B<*> For some drivers the value may only be available immediately after
the insert statement has executed (e.g., mysql, Informix).

B<*> For some drivers the $catalog, $schema, $table, and $field parameters
are required, for others they are ignored (e.g., mysql).

B<*> Drivers may return an indeterminate value if no insert has
been performed yet.

B<*> For some drivers the value may only be available if placeholders
have I<not> been used (e.g., Sybase, MS SQL). In this case the value
returned would be from the last non-placeholder insert statement.

B<*> Some drivers may need driver-specific hints about how to get
the value. For example, being told the name of the database 'sequence'
object that holds the value. Any such hints are passed as driver-specific
attributes in the \%attr parameter.

B<*> If the underlying database offers nothing better, then some
drivers may attempt to implement this method by executing
"C<select max($field) from $table>". Drivers using any approach
like this should issue a warning if C<AutoCommit> is true because
it is generally unsafe - another process may have modified the table
between your insert and the select. For situations where you know
it is safe, such as when you have locked the table, you can silence
the warning by passing C<Warn> => 0 in \%attr.

B<*> If no insert has been performed yet, or the last insert failed,
then the value is implementation defined.

Given all the caveats above, it's clear that this method must be
used with care.

The C<last_insert_id> method was added in DBI 1.38.

=head3 C<selectrow_array>

  @row_ary = $dbh->selectrow_array($statement);
  @row_ary = $dbh->selectrow_array($statement, \%attr);
  @row_ary = $dbh->selectrow_array($statement, \%attr, @bind_values);

This utility method combines L</prepare>, L</execute> and
L</fetchrow_array> into a single call. If called in a list context, it
returns the first row of data from the statement.  The C<$statement>
parameter can be a previously prepared statement handle, in which case
the C<prepare> is skipped.

If any method fails, and L</RaiseError> is not set, C<selectrow_array>
will return an empty list.

If called in a scalar context for a statement handle that has more
than one column, it is undefined whether the driver will return
the value of the first column or the last. So don't do that.
Also, in a scalar context, an C<undef> is returned if there are no
more rows or if an error occurred. That C<undef> can't be distinguished
from an C<undef> returned because the first field value was NULL.
For these reasons you should exercise some caution if you use
C<selectrow_array> in a scalar context, or just don't do that.


=head3 C<selectrow_arrayref>

  $ary_ref = $dbh->selectrow_arrayref($statement);
  $ary_ref = $dbh->selectrow_arrayref($statement, \%attr);
  $ary_ref = $dbh->selectrow_arrayref($statement, \%attr, @bind_values);

This utility method combines L</prepare>, L</execute> and
L</fetchrow_arrayref> into a single call. It returns the first row of
data from the statement.  The C<$statement> parameter can be a previously
prepared statement handle, in which case the C<prepare> is skipped.

If any method fails, and L</RaiseError> is not set, C<selectrow_arrayref>
will return undef.


=head3 C<selectrow_hashref>

  $hash_ref = $dbh->selectrow_hashref($statement);
  $hash_ref = $dbh->selectrow_hashref($statement, \%attr);
  $hash_ref = $dbh->selectrow_hashref($statement, \%attr, @bind_values);

This utility method combines L</prepare>, L</execute> and
L</fetchrow_hashref> into a single call. It returns the first row of
data from the statement.  The C<$statement> parameter can be a previously
prepared statement handle, in which case the C<prepare> is skipped.