| PostgreSQL already includes the procedural language PL/Perl that can run Perl scripts. Accessing PostgreSQL from an external Perl script, however, requires the use of the Perl database-independent (DBI) module. The Perl DBI defines a set of functions, variables , and conventions to Perl scripts, regardless of what back-end database is actually used. Perl DBI provides a consistent interface to scripts, resulting in much more portable and flexible code. The DBI is just a general-purpose interface, however; a database driver is still needed to connect to a specific database. The Perl system does include an older, non-DBI PostgreSQL access module named Pg . However, this is an older module, and most development work recently has gone into the newer DBI-compliant modules. The overall architecture of the Perl DBI system is illustrated in Figure 13.2. Figure 13.2. The overall architecture of the Perl DBI system.  The PostgreSQL driver is named DBI::Pg , and it must be present and installed for execution to be successful. This class and driver set is modeled closely after the libpq library functions. Therefore, the functional interfaces are analogous to how things would be done in C. DBI Class (Connecting) The DBI class is the base class provided by the interface system. The following are the methods it provides: -
connect($data_source, $username, $password, \%attr); Options: data_source : The database driver name (such as dbi:pg ). username : Connect as this user . password : The user's password. %attr : Various options specific to that driver. Description: Establishes a database connection. If successful, returns a valid database handle object. -
available_drivers Description: Returns a list of valid database drivers. -
data_sources($driver) Description: Returns a list of valid databases for the specified driver. -
trace($level[, $file]) Description: Specifies the trace/debugging level and a logging file, if specified. DBI Handle Methods (Executing Queries) Once the DBI class returns a valid handle object, it will provide these methods: -
prepare($statement[, \%attr]) Description:This function sends the query statement, along with options, to the database engine for preparation. (This method does not perform the prepare with PostgreSQL; it simply caches the query until an execute is called.) -
do($statement[, \%attr][, @bind_values]) Description: Prepares and executes the supplied query statement.Additionally, optional attributes and where to bind the results can be specified. -
commit Description: Issues a COMMIT to the database back end. -
rollback Description: Issues a ROLLBACK to the database back end. -
disconnect Description: Disconnects from the database. -
ping Description: Determines whether the database server is still running. -
quote($string) Description: Escapes any special characters . Useful for formatting a query string before delivering to the back end. DBI Statement Handle Methods (Results) After a ResultSet has been returned, that object provides the following methods: -
execute([@bind_values]) Description: Executes the previously prepared statement. Optionally, binds the values for each element before executing the statement. -
fetchrow_arrayref Description: Fetches the next row of data holding values; returns a reference to the array. -
fetchrow_array Description: Fetches the next row of data holding values; returns an array. -
fetchrow_hashref Description: Fetches the next row of data holding values; returns a reference to an array. -
fetchall_arrayref Description: Fetches all the rows of data holding values; returns a reference to an array. -
finish Description: Indicates to the back end that no more rows will be fetched ; allows the server to reclaim resources. -
rows Description: Returns the number of rows affected by the last query. -
bind_col$column_number, $var_to_bind, \%attr); Description: Binds a specific PostgreSQL column to a Perl variable. Statement Handle Attributes The returned statement handles provide the following attributes: -
NUM_OF_FIELDS Description: Returns the number fields in the current row. -
NUM_OF_PARAMS Description: Returns the number of placeholders in the prepared statement. -
NAME Description: Returns a reference to an array that contains the field's names for each column. -
pg_size Description: Returns a reference to an array of integer values for each column. The integer shows the size of the column in bytes. Variable-length columns are indicated by “1. -
pg_type Description: Returns a reference to an array of strings for each column. The string shows the name of the data type. -
pg_oid_status Description: PostgreSQL-specific attribute that returns the OID of the last INSERT command. -
pg_cmd_status -
Description: PostgreSQL-specific attribute that returns the type of the last command. Possible types are INSERT , DELETE , UPDATE , and SELECT .  |
