Analysis of DBI Database module

Perl DBI Programming Reading notes, in the original text see:

http://www.ibm.com/developerworks/cn/linux/l-cn-perldbi/

In order to communicate with the database, the Perl community developed a unified Database communication interface module: DBI. DBI, as a standard interface for communicating with databases in the Perl language, defines a series of methods, variables, and constants that successfully provide a database persistence layer unrelated to a specific database platform.

Back to top of page

An overview of the architecture of the DBI module

The structure of the entire DBI module can be divided into two main parts: the DBI module itself and the driver module for communicating with the specific database platform. The DBI module is used to define the programming interfaces that are provided to Perl developers and to invoke methods for different database driver modules. The implementation of the specific database communication driver module is related to the specific database platform, and is responsible for the actual operation of the specific data communication.

Three types of handles in the DBI module

In the definition of the DBI module, users can create three different types of handles.

• Driver module handle (Driver Handle): The driver module handle represents a driver that is loaded in memory, and it is created with the corresponding driver module loaded in the DBI module. Between it and the actual drive module is a one by one correspondence relationship. The driver module handle provides two main methods for Data_sources () and connect ().
• Database Handles: The database handle is the first step for programmers to communicate using the DBI module and the background database, which contains a separate connection to a particular database. Table one gives the connection string for the common database:
• Execute statement handle (Statement Handles): The EXECUTE statement handle is defined in the DBI specification as an interface to interact with and manipulate the database. These handles wrap a line of SQL statements and send them to the background database for execution. An example of executing a SQL statement using the EXECUTE statement handle is shown in program one.

Table 1. Connection strings for common databases

Database	Connection String (example)
Dbd::mysql	"Dbi:mysql:database= $dbname; host= $hostname;p ort= $port";
Dbd::oracle	"Dbi:oracle: $dbname"; "Dbi:oracle:host= $hostname; sid= $sid";
DBD::D B2	"DBI:DB2: $dbname";
Dbd::sybase	"Dbi:sybase:host= $hostname;d atabase= $dbname;p ort= $port";

Listing 1. Using the DBI module to connect to a database and execute SQL statements

$DBH = Dbi->connect ($connection _string, $userid, $passwd);  $sth = $dbh->prepare ("SELECT * from TBL");  $sth->execute ();  while (@row = $sth->fetchrow_array ()) {   print ' each record: @row \ n ';  }  $sth->finish ();  $DBH->disconnect ();

Establish and release connections

Using the Connect () method provided by the DBI module to create a database handle is that the programmer must provide a data source to specify the database to connect to. The specification of the DBI module requires the name of the data source and must start with the DBI: then connect the name of the database communication driver module and end With ': ', such as ' DBI:DB2: '. Corresponds to the Connect () method, a disconnect () is defined in the DBI module.

By calling the Dbi->available_drivers () method, the programmer can get a list of all the database communication driver modules that have been installed on the current machine. Then using the driver module name as a parameter, call the Dbi->data_sources () method, you can get a list of all the data sources corresponding to this driver module, program two gives an example.

Listing 2. Get all data sources that support driver modules

My @drivers = Dbi->available_drivers ();  Die "No dirvers defined! \ n "unless @drivers;  foreach my $driver (@drivers) {   print "driver: $driver \ n";   My @data_sources = dbi->data_sources ($driver) {   foreach my $data _source (@data_sources) {     print "\ t data sourc E: $data _source \ n ";   }  print "\ n";  }

Error handling

Two methods of error handling are provided in the DBI module. The first method relies on the programmer to manually detect the return value of the called method, and the second method detects the error through the DBI module, which is similar to the exception handling mechanism of the program. For manual error handling, you can activate by setting the "Printerror" and "RaiseError" two variables to zero. By default, the "Printerror" parameter is activated.

Method One, set in the parameters of the Connect () function that calls DBI:

%attr = (printerror = 0, raiseerror=>0);  My $dbh = Dbi->connect ("Dbi:Oracle:testdb", "username", "password", \%attr);

Method Two, set directly in the database handle:

$DBH->{printerror} = 0; $DBH->{raiseerror} = 0;

For automatic error detection, DBI provides two different levels of error handling. The "Printerror" parameter for the handle is set to 1, and the DBI module calls the Warn () function for error handling. It prints the error message to the screen, but does not abort the process. While the "raiseerror" parameter for the handle is set to 1, the DBI module calls the Die () function and aborts the process. These two different levels of error handling can be activated in any valid handle to the DBI module.

In addition to error handling, the DBI module provides a way to diagnose error messages. These methods can be used with any valid handle, and their return values include the error number and the error message.

$RV = $h->err (); $str = $h->errstr (); $str = $h->state ();

The $h->err () method returns an error number generated by the underlying database, and the $h->errstr () method returns a description of the error message generated by the underlying database; the $h->state () method returns a 5-bit SQLSTATE error string. In addition to the three methods above, which return error messages for troubleshooting, at the DBI module level, $DBI:: Err, $DBI:: Errstr, $DBI:: State returns the same value as the function above. See Cheng for an example using the DBI module's built-in error handling method.

Listing 3. Using the built-in error handling method of the DBI module

while (1) {  my $dbh;  # Disable automatic error handle  until {   $DBH = Dbi->connect ($connection _string, $userid, $username);   $DBH->{printerror} = 0;   $DBH->{raiseerror} = 0;   Warn "Unable to connect: $DBI:: Errstr. Sleep for 5 minutes. \ n ";   Sleep ();  }  # Enable automatic error handle  eval {  $dbh->{raiseerror} = 1;  My $sth = $dbh->prepare ("Select Foo, bar from TBL");  while (1) {   $sth->execute ();   while (my @row = $sth->fetchrow_array ()) {   print "row: @row \ n";  }  Sleep;}  }  Warn "Monitoring aborted by error: [Email protected]\n" If [email protected];  Sleep 5;  }

Back to top of page

Manipulating databases to perform simple queries

The most common operation between an application and a database is to query and extract data from the database. In the standard SQL statement specification, this process is using the keyword SELECT.

A standard simple query executed through DBI consists of the following four stages:

2. Preparation phase (Perpare SQL statement):

   By calling the prepare () method, the Prepare phase resolves the SQL statement, validates the SQL statement, and returns an execution statement handle that represents the SQL statement that will be executed within the database.

4. Execution phase (Execute SELECT statement):

   By invoking the Execute () method, the execution stage executes the SQL statement, queries the database, and populates the data structure of Perl with the data being queried. However, during this phase, your Perl application does not really have access to the data being queried.

6. Data extraction phase (fetching date):

   The third stage is called the data extraction phase, during which the actual data is extracted from the database by invoking a set of methods of the fetch () method family. Data extraction phase from the database to get the data from the query, in each data unit, injected into the PERL data structure. DBI provides several ways to extract data from a list, a reference to an array, or a reference to a hash table to the application. and the order of the fields in each record is determined by the order specified in the SQL statement. An example of three methods is shown in program four.

8. Completion phase (finishing date Fetch):

   The final stage is called the completion phase, which releases resources primarily and cleans up historical information stored in the relevant data structures by explicitly invoking the finish () method. When an execution statement handle (statement handler) is executed successfully, its state is marked as active. You can access it by accessing the Active property of the execution statement handle. After the user executes the fetch () method, after extracting the last column of data from the database, the database driver automatically shuts down the work in the database that is in progress with this execution statement handle, and resets the active property to inactive state. All this work is triggered automatically after reading the last column of data, and in most cases the user does not need to be extra concerned with the work done in the background during the process. Some additional cases require the application to actively invoke the finish () method to release resources. A typical example is when a database occupies a significant amount of disk space to store temporary files, storing query results, and the application does not need to save all the query results. For example, the execution of a bar such as "Select EMP_DEP, COUNT (*) from the EMP GROUP by EMP_DEP the ORDER by Count (*) DESC" SQL statement, in the case where the application only requires partial statistical results, the call should be displayed fini The sh () method, releasing the requested and occupied machine resources.

Listing 4. Three different methods of data extraction

$sth->execute ();  # Fetch data by a array while  (@row = $sth->fetchrow_array ()) {   print "Column1: $row [0] \ t Column2: $row [1]. \ n ";  }  # Fetch data by a reference pointing to array while  ($array _ref = $sth->fetchrow_arrayref ()) {   print "Column1 : $array _ref->[0] \ t Column2: $array _ref->[1]. \ n ";  }  # Fetch data by a reference pointing to hash table while  ($hash _ref = $sth->fetchrow_hashref ()) {   print "Col Umn1: $hash _ref->{column1} \ t Column2: $hash _ref->{column2}. \ n ";  }

Executing non-query statements

In the database's common DML statement, in addition to the SELECT statement, there are three kinds of insert,delete,update, we collectively refer to these three kinds of statements are non-query statements. Unlike query statement SELECT, they change only a subset of the records in the database, rather than returning a recordset to the application. Therefore, relative to the prepare-execute-fetch-deallocate sequence in the query statement, non-query statements do not require the data extraction phase, but also can be prepare and execute phase with a Do () method to complete. An example of calling the Do () method is as follows:

  $affected _row_number = $dbh->do ("DELETE from tbl WHERE foo = ' bar ');

The DBI module provides a Do () method to simplify the work, replacing the prepare () and execute () methods that were previously called. In fact, the Do () method in the DBI module simply contains the prepare () and execute () methods. This method does not have any performance difference when generating SQL execution statements using the add-write method, and using the prepare () method and the Execute () method respectively, but if you use the SQL Execution statement generation method with parameter bindings, the performance will be significantly different. Because the Do () method is used, for example, for each inserted record, the database must resolve each INSERT statement and generate an execution plan for the insert operation. However, using the prepare () method, the placeholder can be used in the prepare () method, which allows all the INSERT statements to share an execution plan, achieving the goal of increased efficiency, as shown in program six.

Parameter binding

In the preparation phase, there is an important concept of data binding, with three terms associated with it: placeholders (placeholder), parameters (parameter), and bindings (binding), which are used to dynamically generate SQL statements based on the context of the program. There are two ways to put variables into SQL statements: The first is to generate SQL statements by adding a method (interpolated SQL statement creation), which connects variables directly to other parts of the SQL statement, generating SQL statements that can be executed 。 Program five shows a SQL execution statement generated using the Perl string technique.

Listing 5. Example of dynamically generating SQL execution statements using Perl string technology

foreach $table _name (QW (table1, table2, Table3)) {  $sth = $dbh->prepare ("SELECT count (*) from $table _name"); 
   
     $sth->execute ();  }
   

The second method uses a placeholder in the SQL statement and binds the variable with it through the Bind_param () method, generating the SQL statement. Whenever the Bind_param () method must be called before the Execute () method, the bound parameter cannot be filled into the SQL statement, and the call to the SQL statement is doomed to fail. A typical example of using a binding method is a SQL statement (BIND value SQL statement creation), see program Six.

Listing 6. Examples of dynamically generating SQL execution statements using placeholders

  $sth = $dbh->prepare ("Select Foo, bar from table WHERE foo =?") and bar =? ");   $sth->bind_param (1, "FOO");   $sth->bind_param (2, "BAR");   $sth->execute ();

The first method uses Perl's string-processing function to generate a complete SQL statement and send it to the background database, and the binding method is different, which transmits the SQL statement with the placeholder and the bound value separately to the data, processes the data binding in the data background, and executes the bound SQL statement. Two different approaches will result in significant performance differences, especially if there are a large number of similar SQL statements that need to be processed. Mainstream large databases have a part called "Shared SQL Cache" that stores auxiliary data structures such as query statement execution plans to help the database execute SQL statements. When a new request is made to process an SQL statement, if the SQL statement already exists in the Shared SQL Cache, the database does not need to re-process the statement, and the information stored in the Cache can be reused, resulting in a significant performance gain.

Call a stored procedure

The stored procedure runs on the background database, which effectively reduces the amount of traffic between the client and the database. In this mode of operation, the client no longer needs to send every SQL statement that needs to be executed to the background database, and the stored procedure can define all the SQL statements to be executed within a stored procedure, uniformly execute and return the result of the operation to the client.

In the DBI module there is a method similar to the Bind_param () method, called the Bind_param_inout () method, which can return a value directly from the execution statement handle. The main application of this method is to call the stored procedure to receive the parameters and return the results. It is important to note that some databases, such as MYSQL, do not support this approach. The Bind_param_inout () method passes a reference to the database passing in a parameter that can accept the return value, a simple example is as follows:

  $sth->bind_param_inout (1, \ $bar, 50);

In the above example, the third parameter of the Bind_param_inout () method is the maximum length of the return value.

A complete example of calling a stored procedure in a DB2 database is shown in program seven.

Listing 7. Example of calling a stored procedure in a DB2 database

CREATE PROCEDURE proc (in Foo char (6), out bar integer)  specific proc_example  dynamic result sets 0  modifies s QL data not  deterministic  language SQL  begin atomic   Insert to TBL (' foo ', current date);   Select COUNT (*) into bar from tbl where foo = ' foo ';  End  # Start Perl script to call SQL procedure  $sth = $DBH->prepare ("Call proc (?,?)");  $sth->bind_param (1, $foo);  $sth->bind_param_inout (2, \ $bar, +);  $sth->execute ();  Print "Stored procedure returned $bar. \ n ";

Performing Database transactions

A database transaction is a technique that puts together a set of closely related SQL statements that are either executed successfully or fail, which we call the "all-or-nothing" pattern. A transaction begins with its first executable SQL statement, to the end of commit (commit) or rollback (rollback). If a transaction is committed, all its modifications to the database are saved and visible to other concurrent processes, and if a transaction is rolled back, all modifications it made to the database are discarded.

Not all database software supports transactions, but for all databases that support transactions, the DBI module provides a unified interface for manipulating transactions. Although the implementation of the database varies, the DBI module provides two different ways of handling automatic transaction submission (Auutomatic transcation committing) and manual transaction processing (powerful manual transaction). If the user creates a database handle, the parameter "autocommit" of the handle is set to 1, then the operation of each SQL statement executed through the database handle is immediately committed without any explicit statements being committed or rolled back; If the parameter "Autocommit" is set to 0, then each thing must end with a display call to the commit () method or the rollback () method. If the background database does not support transaction processing, the DBI module throws an error when the user attempts to modify the "autocommit" parameter to 0. The commit () method is defined in the DBI module to explicitly commit changes made to the database within a transactional scope. The method is invoked through a database handle, as follows:

$DBH->commit ();

If the "Autocommit" property in the database handle that calls the commit () method is set to 1, then the commit () method is called with a "commit ineffective with autocommit" warning message. If the background database does not support transactions, the "Autocommit" property is turned on by default, and an identical warning message is obtained after each call to the commit () method. Similar to this is the rollback () method, where each call to the rollback () method rolls back all modifications made to the database within the scope of the transaction. If "Autocommit" is set to one or the background database does not support transaction processing, the same warning message will be returned. An example of calling the rollback () method is as follows:

$DBH->rollback ();

Unfortunately, when the "Autocommit" property is set to 0, the behavior that is triggered when the disconnect () method terminates and the database connection is explicitly called is unpredictable. In some versions of the database, the commit () method is called automatically before the disconnect () method is called by the DBI module, commits all modifications to the database, and some database versions call the rollback () method to roll back all modifications made to the database. A comprehensive example is shown in program eight.

Listing 8. A detailed example of the DBI module handling database transactions

$DBH->connect ();  $DBH->{autocommit} = 0;   $DBH->{raiseerror} = 1;  eval {  load_some_data_to_database ();  Insert_some_data_to_database ();  Delete_some_data_from_database ();  $DBH->commit ()   }  if ([email protected]) {   $dbh->rollback ();  }  $DBH->disconnect ();

Back to top of page

The handle property in the advanced topic DBI in the DBI module

In addition to a set of methods that bind to database handles and execute statement handles, the DBI module provides a set of properties related to these handles for users to tune their execution environment. The essence of these handle properties is a hash table consisting of key/value pairs that can be accessed and modified in the same way as the hash table reference. A typical example is shown in program nine.

Listing 9. modifying and displaying the properties of a handle

$DBH->connect ($connect _string, $userid, $passwd);   $DBH->{autocommit} = 1;   Print "Autocommit: $dbh->{autocommit} \ n";

When the user accesses or sets these handle properties, the DBI module automatically checks the property names entered by the user. If the user attempts to reference a Location property, the DBI module will produce an error. Similarly, if a user attempts to modify a read-only property, the DBI module throws an error the same way. The DBI module throws an error with the Die () method, returning an error value, regardless of whether the user has set the "RaiseError" property when creating the database handle, after detecting the above error.

Naming rules for handle properties

The naming of the handle attribute seems to be messy, in fact, byelaw, the compatibility problem of the DBI module plays an important role. The casing naming convention for a handle property directly reflects who defines the attribute and assigns it a rule with three rules:

• All caps naming (Upper_case): All-uppercase attributes are usually defined by external standards, such as ISO SQL or ODBC.
• Mixed naming (Mixed_case): Names of such properties usually start with uppercase letters, but they are also mixed with lowercase letters. Such attributes are usually defined by the DBI module standard itself.
• All-lowercase naming (lower_case): Such properties are defined by their respective database drivers, and are referred to as "driver-dependent" properties.

Public Handle Properties

Table II lists some of the most common database handle and the handle properties that are supported by the EXECUTE statement handle (statement handle).

Table 2. Common public Handle Properties

Property name	Description
Printerror	If this value is set to 1, the Warn () method is used when the DBI module returns an error result;
RaiseError	If this value is set to 1, the Die () method is used when the DBI module has an incorrect result;

Properties and database metadata for database handles

Table III lists the handle properties that are supported by the most common database handles.

Table 3. Common Database Handle Properties

Property name	Description
Autocommit	If set to 1, each result executed through the DBI module is immediately committed, whereas if set to 0, All execution results are presented in the commit () method or rolled back with the rollback () method;
Name	Read-only property that stores the name of the database;

Database metadata is referred to as "data describing data" to describe the data itself. The information provided by the database metadata is particularly important when the user has dynamically generated SQL execution statements or created dynamic view requirements. The method of storing database metadata without a database vendor and the stored database metadata are different, and most mainstream data provides a system catalog that holds this information through tables and views. Although the above differences make it a task to provide a unified interface for accessing database metadata, the DBI module still provides two callable methods for accessing database metadata in the definition of DBI modules.

The first method $DBH->tables () returns an array of all the tables and views that can be found by the database handle that called it, as shown in program 10.

Listing 10. Using database handles to return metadata for a database

  My @tables = $dbh->tables ();    foreach my $table (@tables) {      print "table Name:". $table. "\ n";    }

The second method $DBH->table_info () returns an execution statement handle (statement handle) that contains more details, which can be extracted by means of the same method as accessing the normal execution statement handle.

Execute statement Handle property and table original data

As with the database handle, the EXECUTE statement handle (statement handle) also has its own handle property. The EXECUTE statement handle inherits a partial property from the database handle that created it, and most of its properties are used to represent the execution state and result of the statement, which belongs to the read-only property. Table Four lists the common execution statement properties:

Table 4. Common execution Statement handle properties

Property name	Description
Num_of_fields	The number of fields returned by the SELECT statement;
NAME	The name of the field returned by the SELECT statement;
NULLABLE	Whether a field can be empty;
TYPE	The type of a field;
Num_of_params	The number of placeholders used by a SQL execution statement;

You can get the table's original data by reading the handle properties of some execution statements, as shown in the following example:

Listing 11. Returning metadata for a table using the EXECUTE statement handle

My $sth = $dbh->prepare ("SELECT * from TBL");  $sth->execute ();  My $field _number = $sth->{num_of_fields};  Print "Number of fields: $field _number \ n";  Print "Column Name                                  Type   Nullable? \ n ";  print "----------------------------------------------------\ n";  For (my $index =0; $index < $field _number; $index + +) {   my $name = $sth->{name}->[$index];   My $type = $sth->{type}->[$index];   My $nullable = ("No", "Yes", "Unknown") [$sth->{nullable}->[$index]];   printf "%-30s%4d $s \ n", $name, $type, $nullable;  }  $sth->finish ();

DBD modules that conform to the DBI module interface

The DBI module defines a set of interfaces that interact with the user, while the DBD implements a specific interface between the databases. The relationship between the two can be likened to the relationship between the interface and the class that inherits the interface in object-oriented programming. There are two different implementations of database drivers, the first of which is based on the pure Perl language and does not rely on any C language compilers. This method is easiest to implement, but is not supported by most databases, and the typical DBD module examples are dbd::file and dbd::csv. Another method is more common, using the C program's help and the database for communication, known as the C/xs driver.

For database-driven purely Perl language development, the core of the entire DBD module is in a dbd::D river.pm module. Depending on the database you are actually connecting to, the name of Driver can be replaced by dbd::oracle or DBD::D B2. In the DBD::D River Package, which usually contains a sub-package, different sub-packages have different effects, and detailed information about them is shown in table five.

Table 5. Internal design of a typical DBD module

Package Name	function	function table (partial)
DBD::D River	Provides methods for DBD levels	Driver ()
DBD::D River::d R	Provides methods for driving the module handle level	Connect () Data_sources ()
DBD::D River::d b	Methods to provide database handle levels	Do () Prepare () Table_info () Ping () Rollback () Commit () FETCH () STORE ()
DBD::D river::st	Provides methods for executing the level of a statement handle	Execute () Bind_param () Fetchrow_array () Fetchrow_arrayref () Fetchrow_hashref ()

The DBD module based on the C/xs driver is very complex and is not described here.

Back to top of page

MYSQL Database-based example

We conducted a simple test of the MYSQL database. The results of the tests are shown in table six.

Table 6. MYSQL database test results for the DBI module

Test Cases	Purpose	problem
Test_dbh_and_sth ()	Test database handle and execute statement handle; querying, inserting and deleting database records;	Field names are all lowercase;
Test_fetch_metadata_of_table ()	Test the original data to obtain the data of the target;	Partial information error;
Test_call_sql_procedure ()	Test calls to SQL stored procedures;	Bind_param_inout () function does not work properly, to get the return value of the stored procedure needs special processing;

Back to top of page

Examples based on the DB2 database

Also we have an SQL script to create a test environment in DB2 data. (see annex III) test results are shown in table six.

A sample connection to the DB2 database using the DBI module is shown in program 12.

Table 7. Test results of the DB2 database on the DBI module

Test Cases	Purpose	problem
TEST_DRH ()	test driver handle;	N/A
Test_dbh_and_sth ()	Test database handle and execute statement handle; querying, inserting and deleting database records;	Field names are all uppercase;
Test_mixed_error_checing ()	Error handling mechanism of the test DBI module;	N/A
Test_bind_parameter_to_statement ()	Test Execution statement late binding function;	N/A
Test_fetch_metadata_of_table ()	Test the original data to obtain the data of the target;	Partial information error;
Test_call_sql_procedure ()	Test calls to SQL stored procedures;	N/A
Test_run_transaction ()	The test executes the transaction through the DBI module;	N/A

Listing 12. Connecting the DB2 database using the DBI module

Sub Setup_connections {   my $connections _reference = shift;   My $passwd = "xxxxxxxx";   My $db 2_connection_string = "Dbi:DB2:test";   My $db 2_userid = "Db2inst1";   My%db2_connection = ();   $db 2_connection{dbname} = "DB2";   $db 2_connection{connstr} = $db 2_connection_string;   $db 2_connection{userid} = $db 2_userid;   $db 2_connection{passwd} = $passwd;   Push (@{$connections _reference}, \%db2_connection);  }

Back to top of page

Summarize

In this paper, the DBI module for communication with database in Perl language is introduced in detail. With delicate strokes and vivid examples, the main components of the DBI module, structure and methods for user programming are described. At the same time, this article also involves some high-level topics in the DBI module, such as calls to stored procedures, processing of concurrent transactions, and processing of database and execution statement handle properties and metadata. In addition, this article also gives the introduction of the interface of the DBD module, hoping that the reader can be helpful in researching the DBD module of the third-party database. At the end of this paper, by defining a set of common DBI module test interface, three kinds of mainstream database db2,sybase and MYSQL are tested, and by comparing the test results, we get the similarities and differences of the DBI module on these three kinds of databases and the blind spot of support.