The DBI interface

Portable DBI methods.

connect

You use the connect method to make a database connection to the data source. The $data_source value should begin with DBI:driver_name:. Example connect methods 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 username and/or password are undefined, then the DBI will use the values of the DBI_USER, DBI_PASS environment variables respectively. If you don't specify a hostname, then it will default to "localhost". If you don't specify a port, then it defaults to the default mysql port (3306).

prepare

Prepare gets a SQL statement ready for execution by the database engine and returns a statement handle ($sth) which invokes the execute method. Example:

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

do

The "do" method prepares and executes a SQL statement and returns the number of rows effected. This method is generally used for "non-select" statements which can not be prepared in advance (driver limitation) or which do not need to executed more than once (inserts, deletes, etc.). Examples:

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

disconnect

Disconnect will disconnect the database handle from the database. This is typically called right before you exit from the program. Example:

$rc = $dbh->disconnect;

quote

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

$sql = $dbh->quote($string)

execute

This method executes the prepared statement. For non-select satements, it returns the number of rows affected. For select statements, execute only starts the SQL query in the database. You need to use one of the fetch_* methods below to retrieve the data. Example:

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

fetchrow_array

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); 
}

fetchrow_arrayref

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);
}

fetchrow_hashref

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);
}

fetchall_arrayref

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 arrays of references to each row. You access/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";
}

finish

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 it may be holding. Example:

$rc = $sth->finish;

rows

Returns the number of rows affected (updated, deleted, etc.) from the last command. This is usually used after a do() or non-select execute() statement.

$rv = $sth->rows;

NULLABLE

A reference to an array of boolean values; TRUE indicates that this column may contain NULLs.

$null_possible = $sth->{NULLABLE}; 

NUM_OF_FIELDS

Number of fields returned by a SELECT or LISTFIELDS 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.

$nr_of_fields = $sth->{NUM_OF_FIELDS};

data_sources

This method returns an array of databases available to the mysql daemon on localhost.

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

ChopBlanks

This determines whether a fetchrow will chop preceding and trailing blanks off the returned values.

$sth->{'ChopBlanks') =1; 

insertid

If you use the auto-increment feature of mysql, the new auto-incremented values will be stored here.

$new_id = $sth->{insertid};

is_blob

Reference to an array of boolean values; TRUE indicates that the respective column is a blob.

$keys = $sth->{is_blob};

is_key

Reference to an array of boolean values; TRUE indicates, that the respective column is a key.

$keys = $sth->{is_key};

is_num

Reference to an array of boolean values; TRUE indicates, that the respective column contains numeric values.

$nums = $sth->{is_num};

is_pri_key

Reference to an array of boolean values; TRUE indicates, that the respective column is a primary key.

$pri_keys = $sth->{is_pri_key};

is_not_null

A reference to an array of boolean values; FALSE indicates that this column may contain NULLs. You should better use the NULLABLE attribute above which is a DBI standard.

$not_nulls = $sth->{is_not_null};

max_length

length

A reference to an array of maximum column sizes. The max_length is the maximum physically present in the result table, length gives the theoretically possible maximum.

$max_lengts = $sth->{max_length};
$lengts = $sth->{length};

NAME

A reference to an array of column names.

$names = $sth->{NAME};

table

Returns a reference to an array of table names.

$tables = $sth->{table};

Go to the first, previous, next, last section, table of contents.