Database handle

Statement handle

Return code (often a status)

Return value (often a row count)

Use the connect method to make a database connection to the data source. The $data_source value should begin with DBI:driver_name:. Example uses of connect with the DBD::mysql driver:

$dbh = DBI-connect("DBI:mysql:$database", $user, $password);
$dbh = DBI-connect("DBI:mysql:$database:$hostname",
                    $user, $password);
$dbh = DBI-connect("DBI:mysql:$database:$hostname:$port",
                    $user, $password);

If the user name and/or password are undefined, DBI uses the values of the DBI_USER and DBI_PASS environment variables, respectively. If you don't specify a hostname, it defaults to 'localhost'. If you don't specify a port number, it defaults to the default MySQL port (3306).

As of Msql-Mysql-modules Version 1.2009, the $data_source value allows certain modifiers:

Multiple modifiers may be given; each must be preceded by a semicolon.

For example, if you want to avoid hardcoding the user name and password into a DBI script, you can take them from the user's ~/.my.cnf option file instead by writing your connect call like this:

$dbh = DBI-connect("DBI:mysql:$database"
                . ";mysql_read_default_file=$ENV{HOME}/.my.cnf",
                $user, $password);

This call will read options defined for the [client] group in the option file. If you wanted to do the same thing but use options specified for the [perl] group as well, you could use this:

$dbh = DBI-connect("DBI:mysql:$database"
                . ";mysql_read_default_file=$ENV{HOME}/.my.cnf"
                . ";mysql_read_default_group=perl",
                $user, $password);

The disconnect method disconnects the database handle from the database. This is typically called right before you exit from the program. Example:

$rc = $dbh-disconnect;

Prepares an SQL statement for execution by the database engine and returns a statement handle ($sth), which you can use to invoke the execute method.

Typically you handle SELECT statements (and SELECT-like statements such as SHOW, DESCRIBE, and EXPLAIN) by means of prepare and execute. Example:

$sth = $dbh-prepare($statement)
    or die "Can't prepare $statement: $dbh-errstr\n";

If you want to read big results to your client you can tell Perl to use mysql_use_result() with:

my $sth = $dbh-prepare($statement { "mysql_use_result" = 1});

The execute method executes a prepared statement. For non-SELECT statements, execute returns the number of rows affected. If no rows are affected, execute returns "0E0", which Perl treats as zero but regards as true. If an error occurs, execute returns undef. For SELECT statements, execute only starts the SQL query in the database; you need to use one of the fetch_* methods described here to retrieve the data. Example:

$rv = $sth-execute
          or die "can't execute the query: " . $sth-errstr;

The do method prepares and executes an SQL statement and returns the number of rows affected. If no rows are affected, do returns "0E0", which Perl treats as zero but regards as true. This method is generally used for non-SELECT statements that cannot be prepared in advance (due to driver limitations) or that do not need to be executed more than once (inserts, deletes, etc.). Example:

$rv = $dbh-do($statement)
        or die "Can't execute $statement: $dbh- errstr\n";

Generally the 'do' statement is much faster (and is preferable) than prepare/execute for statements that don't contain parameters.

The quote method is used to "escape" any special characters contained in the string and to add the required outer quotation marks. Example:

$sql = $dbh-quote($string)

This method fetches the next row of data and returns it as an array of field values. Example:

while(@row = $sth-fetchrow_array) {
        print qw($row[0]\t$row[1]\t$row[2]\n);
}

This method fetches the next row of data and returns it as a reference to an array of field values. Example:

while($row_ref = $sth-fetchrow_arrayref) {
        print qw($row_ref-[0]\t$row_ref-[1]\t$row_ref-[2]\n);
}

This method fetches a row of data and returns a reference to a hash table containing field name/value pairs. This method is not nearly as efficient as using array references as demonstrated above. Example:

while($hash_ref = $sth-fetchrow_hashref) {
        print qw($hash_ref-{firstname}\t$hash_ref-{lastname}\t\
                $hash_ref-{title}\n);
}

This method is used to get all the data (rows) to be returned from the SQL statement. It returns a reference to an array of references to arrays for each row. You access or print the data by using a nested loop. Example:

my $table = $sth-fetchall_arrayref
                or die "$sth-errstr\n";
my($i, $j);
for $i ( 0 .. $#{$table} ) {
        for $j ( 0 .. $#{$table-[$i]} ) {
                print "$table-[$i][$j]\t";
        }
        print "\n";
}

Indicates that no more data will be fetched from this statement handle. You call this method to free up the statement handle and any system resources associated with it. Example:

$rc = $sth-finish;

Returns the number of rows changed (updated, deleted, etc.) by the last command. This is usually used after a non-SELECTexecute statement. Example:

$rv = $sth-rows;

Returns a reference to an array of values that indicate whether columns may contain NULL values. The possible values for each array element are 0 or the empty string if the column cannot be NULL, 1 if it can, and 2 if the column's NULL status is unknown. Example:

$null_possible = $sth-{NULLABLE};

This attribute indicates the number of fields returned by a SELECT or SHOW FIELDS statement. You may use this for checking whether a statement returned a result: A zero value indicates a non-SELECT statement like INSERT, DELETE, or UPDATE. Example:

$nr_of_fields = $sth-{NUM_OF_FIELDS};

This method returns an array containing names of databases available to the MySQL server on the host 'localhost'. Example:

@dbs = DBI-data_sources("mysql");

This attribute determines whether the fetchrow_* methods will chop leading and trailing blanks from the returned values. Example:

$sth-{'ChopBlanks'} =1;

The trace method enables or disables tracing. When invoked as a DBI class method, it affects tracing for all handles. When invoked as a database or statement handle method, it affects tracing for the given handle (and any future children of the handle). Setting $trace_level to 2 provides detailed trace information. Setting $trace_level to 0 disables tracing. Trace output goes to the standard error output by default. If $trace_filename is specified, the file is opened in append mode and output for all traced handles is written to that file. Example:

DBI-trace(2);                # trace everything
DBI-trace(2,"/tmp/dbi.out"); # trace everything to
                              # /tmp/dbi.out
$dth-trace(2);               # trace this database handle
$sth-trace(2);               # trace this statement handle

You can also enable DBI tracing by setting the DBI_TRACE environment variable. Setting it to a numeric value is equivalent to calling DBI-(value). Setting it to a pathname is equivalent to calling DBI-(2,value).

If you use the AUTO_INCREMENT feature of MySQL, the new auto-incremented values will be stored here. Example:

$new_id = $sth-{mysql_insertid};

With old versions of the DBI interface, you could use $sth-{'insertid'}.

Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that the respective column is a BLOB. Example:

$keys = $sth-{is_blob};

Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that the respective column is a key. Example:

$keys = $sth-{is_key};

Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that the respective column contains numeric values. Example:

$nums = $sth-{is_num};

Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that the respective column is a primary key. Example:

$pri_keys = $sth-{is_pri_key};

Returns a reference to an array of boolean values; for each element of the array, a value of FALSE indicates that this column may contain NULL values. Example:

$not_nulls = $sth-{is_not_null};

is_not_null is deprecated; it is preferable to use the NULLABLE attribute (described above), because that is a DBI standard.

Each of these methods returns a reference to an array of column sizes. The length array indicates the maximum possible sizes that each column may be (as declared in the table description). The max_length array indicates the maximum sizes actually present in the result table. Example:

$lengths = $sth-{length};
$max_lengths = $sth-{max_length};

Returns a reference to an array of column names. Example:

$names = $sth-{NAME};

Returns a reference to an array of table names. Example:

$tables = $sth-{table};

Returns a reference to an array of column types. Example:

$types = $sth-{type};