view release on metacpan or  search on metacpan

DBI.pm  view on Meta::CPAN

  $sth = $dbh->prepare("SELECT foo, bar FROM table WHERE baz=?");

  $sth->execute( $baz );

  while ( @row = $sth->fetchrow_array ) {
    print "@row\n";
  }

The typical method call sequence for a I<non>-C<SELECT> statement is:

  prepare,
    execute,
    execute,
    execute.

for example:

  $sth = $dbh->prepare("INSERT INTO table(foo,bar,baz) VALUES (?,?,?)");

  while(<CSV>) {
    chomp;
    my ($foo,$bar,$baz) = split /,/;
	$sth->execute( $foo, $bar, $baz );
  }

The C<do()> method can be used for non repeated I<non>-C<SELECT> statement
(or with drivers that don't support placeholders):

  $rows_affected = $dbh->do("UPDATE your_table SET foo = foo + 1");

To commit your changes to the database (when L</AutoCommit> is off):

  $dbh->commit;  # or call $dbh->rollback; to undo changes

Finally, when you have finished working with the data source, you should
L</disconnect> from it:

  $dbh->disconnect;


=head2 General Interface Rules & Caveats

The DBI does not have a concept of a "current session". Every session
has a handle object (i.e., a C<$dbh>) returned from the C<connect> method.
That handle object is used to invoke database related methods.

Most data is returned to the Perl script as strings. (Null values are
returned as C<undef>.)  This allows arbitrary precision numeric data to be
handled without loss of accuracy.  Beware that Perl may not preserve
the same accuracy when the string is used as a number.

Dates and times are returned as character strings in the current
default format of the corresponding database engine.  Time zone effects
are database/driver dependent.

Perl supports binary data in Perl strings, and the DBI will pass binary
data to and from the driver without change. It is up to the driver
implementors to decide how they wish to handle such binary data.

Most databases that understand multiple character sets have a
default global charset. Text stored in the database is, or should
be, stored in that charset; if not, then that's the fault of either
the database or the application that inserted the data. When text is
fetched it should be automatically converted to the charset of the
client, presumably based on the locale. If a driver needs to set a
flag to get that behaviour, then it should do so; it should not require
the application to do that.

Multiple SQL statements may not be combined in a single statement
handle (C<$sth>), although some databases and drivers do support this
(notably Sybase and SQL Server).

Non-sequential record reads are not supported in this version of the DBI.
In other words, records can only be fetched in the order that the
database returned them, and once fetched they are forgotten.

Positioned updates and deletes are not directly supported by the DBI.
See the description of the C<CursorName> attribute for an alternative.

Individual driver implementors are free to provide any private
functions and/or handle attributes that they feel are useful.
Private driver functions can be invoked using the DBI C<func()> method.
Private driver attributes are accessed just like standard attributes.

Many methods have an optional C<\%attr> parameter which can be used to
pass information to the driver implementing the method. Except where
specifically documented, the C<\%attr> parameter can only be used to pass
driver specific hints. In general, you can ignore C<\%attr> parameters
or pass it as C<undef>.


=head2 Naming Conventions and Name Space

The DBI package and all packages below it (C<DBI::*>) are reserved for
use by the DBI. Extensions and related modules use the C<DBIx::>
namespace (see L<http://www.perl.com/CPAN/modules/by-module/DBIx/>).
Package names beginning with C<DBD::> are reserved for use
by DBI database drivers.  All environment variables used by the DBI
or by individual DBDs begin with "C<DBI_>" or "C<DBD_>".

The letter case used for attribute names is significant and plays an
important part in the portability of DBI scripts.  The case of the
attribute name is used to signify who defined the meaning of that name
and its values.

  Case of name  Has a meaning defined by
  ------------  ------------------------
  UPPER_CASE    Standards, e.g.,  X/Open, ISO SQL92 etc (portable)
  MixedCase     DBI API (portable), underscores are not used.
  lower_case    Driver or database engine specific (non-portable)

It is of the utmost importance that Driver developers only use
lowercase attribute names when defining private attributes. Private
attribute names must be prefixed with the driver name or suitable
abbreviation (e.g., "C<ora_>" for Oracle, "C<ing_>" for Ingres, etc).


=head2 SQL - A Query Language

Most DBI drivers require applications to use a dialect of SQL
(Structured Query Language) to interact with the database engine.
The L</"Standards Reference Information"> section provides links
to useful information about SQL.