THE PERL DATABASE INTERFACE (DBI).

1. Connection strings
2. Disconnection
3. Error Handling

3.1 err (DBI handle method)
3.2 errstr (DBI handle method)
3.3 PrintError (DBI handle attribute)
3.4 RaiseError (DBI handle attribute)

4. Retrieving Data (Statement Handle Methods)

4.1 fetchrow_array
4.2 fetchrow_arrayref
4.3 fetchrow_hashref
4.4 fetchall_arrayref

5. Binding Data (Statement Handle Methods)

5.1 bind_param
5.2 bind_param_inout

6. Database Handle Methods

6.1 selectrow_array
6.2 selectall_arrayref
6.3 selectcol_arrayref
6.4 do
6.5 prepare

7. Key
8. Useful Book References
9. Useful Online References

1. Connection Strings

In order to read data from a database, the source of the data needs to be connected to. Connecting to the data source ranges from something as simple as opening a file for a flat data file, to constructing a relatively complicated connection string for connecting to an RDBMS. Generally, in order to construct a connection string, there are 3 principal items of information required:

The Data Source Name, or DSN.
The username required to connect to the databse.
The password associated with the username used to connect to the database.

The DBI-connect() method is used to make the connection, and the connection string is contained within this. The Connection String Syntax is then as follows:

$dbh = DBI->connect($data_source_name, $username, $password);
$dbh = DBI->connect($data_source_name, $username, $password, \%attr);
$dbh = DBI->connect($data_source_name, $username, $password, {RaiseError => 1, AutoCommit => 0 });

If the connection is successful, a database handle object is returned. If the connection is unsuccessful, undef is returned and both the $DBI::err handle method and $DBI::errstr handle method are set. In order to check that the connection is successful, it is advisable to check for the return status of the DBI-connect. Taken the examples shown above, they would then be modified as follows:

$dbh = DBI->connect($data_source_name, $username, $password) || die $DBI::errstr;
$dbh = DBI->connect($data_source_name, $username, $password, \%attr) || die $DBI::errstr;
$dbh = DBI->connect($data_source_name, $username, $password, {RaiseError => 1, AutoCommit => 0 }) || die $DBI::errstr;

Connection String Examples.

$dbh = DBI->connect("dbi:Oracle:database_name", "username", "password" );
$dbh = DBI->connect("dbi:mSQL:database_name", "username", "password" );

2. Disconnection

3. Error Handling

3.1 err (DBI handle method)

err returns the database specific error code related to the last driver method or handle called. Normally, the error code returned will be an integer, however this is not always the case, especially where new custom error codes may have been defined.

3.2 errstr (DBI handle method)

errstr returns the database specific error message related to the last driver method or handle called. The error message contained in errstr, will correspond to the error code returned in err. As per its name, where errstr is defined, it returns a character string.

3.3 PrintError (DBI handle attribute)

3.4 RaiseError (DBI handle attribute)

4. Retrieving Data (Statement Handle Methods)

4.1 fetchrow_array

4.2 fetchrow_arrayref

4.3 fetchrow_hashref

4.4 fetchall_arrayref

5. Binding Data (Statement Handle Methods)

5.1 bind_param

5.2 bind_param_inout

6. Database Handle Methods

6.1 selectrow_array

6.2 selectall_arrayref

6.3 selectcol_arrayref

6.4 do

6.5 prepare

Key

Normal Text
Normal Italic Text - a user defined value or variable
Bold Text - any text in bold can be typed in at the command line
Bold Italic Text - - a user defined value or variable which should be typed in

Useful Book References

Programming the Perl DBI - Alligator Descartes and Tim Bunce - O'Reilly - 2000 - ISBN: 1-56592-699-4
Perl Black Book - Steven Holzner - CoriolisOpenPress - 1999 - ISBN: 1-57610-465-6

Useful Online References

THE PERL DATABASE INTERFACE (DBI).

www.cyberthinc.com