dbdrv - a Perl module for low-level utility function to access an SQL database.
use dbdrv;
dbdrv::load("Oracle"); # load oracle driver
my @all_tables= dbdrv::all_tables();This module contains low-level functions for accessing an SQL database which are not part of the standard DBD database drivers. Examples of such functions are getting a list of all tables, getting a list of all views or getting the primary key(s) of a certain table.
Since the implementation of these functions is different for each database, the module dbdrv provides a unified interface, just like DBI does. And just like DBI knows driver modules like DBD::Oracle, this module has driver modules, too, for example dbdrv:oci.
The module should always be initialized with a call of dbdrv::load. Before the call of this function, the other functions to access the database are not defined. This is how dbdrv::load is called:
dbdrv::load($driver_name);Known driver names are:
This loads the driver for the oracle database (dbdrv_oci.pm).
The function calls dbdrv::dberror() if the loading of the driver failed. See also "error handling" further below.
dbdrv uses two error handling functions, for fatal and non-fatal errors. These functions are dbdrv::dberror() and dbdrv::dbwarn(), you can use them for your application, too. This may be useful since it is easy to ovveride these functions and provide a method of your own to print out the error messages.
if ($not_fatal_error)
{ dbdrv::dbwarn($module_name,$function,__LINE__,
"a non fatal error occured");
}This function takes 4 arguments. $module_name is the name of your perl-module, $function the name of your function where the error occured. The 3rd parameter is the line-number and the fourth is the error-string. dbdrv::dbwarn() returns after it has printed the error-message.
if ($fatal_error)
{ dbdrv::dberror($module_name,$function,__LINE__,
"a fatal error occured, aborting the program");
}This function takes the same parameters as dbdrv::dbwarn(), the only difference is that it terminates your program by calling die.
Both functions mentioned above print messages to STDERR. You can, however override these two functions. This is useful when your application is for example a graphical application and you want error-messages to appear in a graphical error-box.
Overriding the error or warn-function is done by calling one of the two following functions:
sub my_warn_func
{ my($err_message)= @_;
warn "There was an error: $err_message";
}
$dbdrv::set_warn_func(\&my_warn_func)This function has only one parameter which should be a reference to a function or undef. In the first case, the function you supplied is used to print the error-message. It should expect one single string as argument and show, in what way ever, this string to the user. If the parameter of set_warn_func is undef or missing, the original error printing function (a simple call to warn) is re-instated.
sub my_error_func
{ my($err_message)= @_;
die "There was an error: $err_message";
}
$dbdrv::set_error_func(\&my_error_func)This function is very similar to set_warn_func. The only difference is that the cases when your error-print function is called are usually fatal errors that can not be recovered. So it is safe when your function terminates the program with die. In some cases however, your function may not terminate the program and simply return. It is, however, your responsibility to check when this makes sense.
dbdrv has an internal function in order to print SQL commands that are executed. This function does a simple print with a final carriage return. This function may be overridden like this:
sub my_trace_func
{ my($trace_message)= @_;
print $trace_message,"\n";
}
dbdrv::set_sql_trace_func(\&my_error_func)dbdrv::set_sql_trace_func has only one parameter wich should be a reference to a function or undef. In the first case, the function you supplied is used to print the SQL statement. It should expect one single string as argument and show, in what way ever, this string to the user. If the parameter of set_sql_trace_func is undef or missing, the original tracing function (a simple call to print) is re-instated.
Tracing is switched on or of by writing 0 of one to the variable $sql_trace. This for example, switches SQL tracing on:
$dbdrv::sql_trace=1;Note that if your application executes SQL statements, too, it has to call the sql trace function in order to show all SQL statments to the user:
my $cmd= "select max( $pk ) from MY_TABLE";
dbdrv::sql_trace($cmd);
# ^^ this shows the SQL statement to the user
my @array = $dbh->selectrow_array($cmd);
# ^^ this executes the SQL statementIf your application uses prepare or execute from the DBI module, you should use prepare or execute from dbdrv instead.
my $format;
my $sth= dbdrv::prepare(\$format,$dbh,
"delete from $self->{_table} " .
"where KEY = ? ");The first parameter is a reference to a scalar variable. This is used by dbdrv::execute later. $dbh is the database handle and the 3rd parameter is the SQL statement with placeholders ("?") as described in the DBI manpage. The function returns a statement handle, just like it is described at prepare in the DBI module.
if (!dbdrv::execute($format,$dbh,$sth,@params))
{ error ... }The first parameter is the format scalar variable that was initialized by dbdrv::prepare. $dbh is the database handle and $sth is the statement handle that was returned with dbdrv::prepare. All following parameters (@params) are used to fill the placeholders that were given to dbdrv::prepare in the SQL statement string.
dbitable::commit($dbh)This performs a commit on the database
dbitable::rollback($dbh)This performs a rollback on the database
dbitable::set_autocommit($dbh,$val)This sets the autocommit-feature of the given database-handle. Autocommit is switched on or off according to to value of $val.
dbitable::get_capabilities(l)It returns the hash of all driver capabilities (scriptaliases, options).
my $dbh= dbitable::check_alias($name)This only returns the exists result for the given alias name for the alias list hash.
my $dbh= dbitable::get_alias($name,@values)Returns the sequel with the parsed alias, filled with the values.
my $hash= dbitable::get_help($dbh,$topic)Returns the help query for the topic as a single string that contains all lines.
my $hash= dbitable::get_help_topic($dbh)Returns a list of all topics found in helptable fetched from the database.
my $dbh= dbitable::connect_database($dbname,
$username,$password,$autocommit)This method creates a connection to the database. The database corresponds to the first parameter of the connect function of the DBI module. See also the DBI manpage. The function returns the DBI-handle or undef in case of an error. The DBI-handle is also stored in the internal global handle variable. This variable is used as default in all the other functions in this module when their DBI-handle parameter is an empty string ("") or undef. The $autocommit parameter determines wether the database is opened with automatic commiting or not. See also set_autocommit(), commit() and rollback().
dbitable::disconnect_database($dbi_handle)This function is used to disconnect from the database. If the parameter $dbi_handle is an empty string, the default DBI-handle is used (see also description of connect_database).
$dbh= dbitable::check_dbi_handle($dbh);This function does the checking of the dbi-handle in all functions of this module. When $dbh is empty ("") or undef, it returns the internal standard dbi-handle (dbdrv::std_dbh), otherwise it returns the parameter $dbh. When the given parameter is not a valid DBI handle, it returns undef.
if (dbdrv::check_existence($dbh,$object_name))
{ print "table exists and can be accessed\n"; }
else
{ print "table does not exist or is not accessible\n"; }This function performs a simple check wether a table exists. $dbh is the database handle, $table_name the name of the table. $user_name is the username and optional. The username is storedin the variable dbdrv::std_username when connect_database() is called. The function returns 1 when the table is accessible and undef when not.
my @pk_list= dbdrv::primary_keys($dbh,$table_name)This function returns a list of primary keys of a given table. Note that the list may be empty, and that the list may contain more than one column name if a combination of columns forms the primary key.
my %fk_hash= dbdrv::foreign_keys($dbh,$table_name)This function returns all columns in a table that are foreign keys in a hash. Note that foreign keys may have a different column name in the foreign table they refer to. The foreign key hash has the following format:
my %fk_hash= ( $column_name1 =>
[$foreign_table1,$foreign_column_name1],
$column_name2 =>
[$foreign_table2,$foreign_column_name2],
...
$column_nameN =>
[$foreign_tableN,$foreign_column_nameN],
)my %rk_hash= dbdrv::resident_keys($dbh,$table_name)This function returns all tables where one of the primary key columns in the given table $table_name is used as foreign key. It returns a hash of the following format
my %rk_hash= ( $primary_key1 =>
[$resident_table11,$resident_column11],
[$resident_table12,$resident_column12],
...
[$resident_table1N,$resident_column1N],
$primary_key2 =>
[$resident_table21,$resident_column21],
[$resident_table22,$resident_column22],
...
[$resident_table2N,$resident_column2N],
...
)my @objects= accessible_objects($dbh,$table_name,$user_name)This function returns all accessible public objects (tables and views) for a given user ($user_name).
my ($name,$owner)=real_name($dbh,$user_name,$object_name)This resolves synonyms and returns the real name of the table and it's owner.
my $new_name=canonify_name($dbh,$user_name,$object_name,$object_owner)This converts a given object and it's owner to a name in the form "owner.name" or a public synonym that maps to the given object.
my @list= dbdrv::object_is_table($dbh,$table_name,$user_name)Checks wether a given object is a table (returns "1" in that case). Note that $table_owner is not needed if it's a public synonym or of the table name contains the owner in the form "owner.object_name".
my @list= dbdrv::object_dependencies($dbh,$table_name,$user_name)This function returns a list of all dependend objects. These are views and tables that depend on the given table $table_name. It returns an array of the following format
my @list= ( [$owner1, $name1, $type1],
[$owner2, $name2, $type2],
...
)The field $type is either "VIEW", "TABLE" or "PROCEDURE".
my %rk_hash= dbdrv::object_references($dbh,$table_name,$user_name)This function returns a list of all referenced objects. These are tables and views the current object ($table_name) depends from. It returns a hash of the following format
my @list= ( [$owner1, $name1, $type1],
[$owner2, $name2, $type2],
...
)The field $type is either "VIEW", "TABLE" or "PROCEDURE".
my @list= dbdrv::object_addicts($dbh,$table_name,$user_name)This function returns a list of all triggers and constraints for a given table. For each trigger or constraint, the owner is also returned.
It returns an array of the following format
my @list= ( [$name1, $owner1, $type1],
[$name2, $owner1, $type2],
...
)The field $type is either "C", for constraints or "T" for triggers.
my $text= dbdrv::read_viewtext($dbh,$view_name, $user_name)This function returns the text of a view definition
my $text= dbdrv::read_checktext($dbh,$constraint_name, $constraint_owner)This function returns the text of a check constraint definition
my %rk_hash= dbdrv::read_triggertext($dbh,$trigger_name, $trigger_owner)This function returns name, type, event, referer, clause, status body and description of a trigger definition. It returns a hash of the following format:
my @list= ( [$name1, $type1, $event1, $referer1,
$clause1, $status1, $body1, $description1],
[$name2, $type2, $event2, $referer2,
$clause2, $status2, $body2, $description2],
...
)Note that currently this function only returns results when you're logged in as database administrator (Oracle 8.0.5)
--- to be continued ---
--- to be continued ---
Goetz Pfeiffer, Goetz.Pfeiffer@helmholtz-berlin.de
perl-documentation, DBI manpage