MySQL processing in Perl

A Perl application database uses the DBM package configured by itself to create and operate a database, therefore, the DBM package and other similar packages can be considered as a database extension. The other is to use the DBI package or similar packages to establish connections with other relational databases. Obviously, we usually need to use the latter.

Database Interface (DBI) is written by Tim Bunce (Tim.Bunce@ig.co.uk), DBI is specifically written for Perl. You can

The http://www.hermetica.com/technologia/DBI/ finds the appropriate information.

Initially, DBI was only an interface package developed by Tim Bunce. Later, he developed the DBD: Oracle package for processing interfaces with Oracle. Then, a series of DBD: modules packages are used to process interfaces with other types of databases. Similarly, you can:

Http://www.hermetica.com/ia/dbi/get detailed information. The available packages are as follows:

DBD-Oracle-0.29.tar.gz: Oracle Database

DBD-Informix-0.20pl0.tar.gz: Informix Database

DBD-QBase-0.03.tar.gz: quickbase

DBD-mSQL-0.60pl9.tar.gz: msql-based database

DBI-0.67.tar.gz: DBI Interface

In addition, ODBC drivers can be used to drive database systems such as access in windows. (Of course, Perl for Win32 also has the Win32: ODBC package used to process ODBC compatible drivers)

After retrieving the DBI driver package, do not rush to install it. First, test whether perl5 is installed. This is very important, and then:

If perl5 has been installed: Perl makefile. pl

If not installed: Perl makefile. pl perl_src =/path/to/perl/source/Dir

Then:

Make

Make install

You can run the command $ perl_dl_debug = 255 Perl-e 'use dBi; 'in the system ;'

Then we can get the following results:

Dynaloader. PM loaded (/usr/local/lib/perl5/i486-linux/5.003/usr/local/lib/perl5

/Usr/local/lib/perl5/site_perl/i486-linux/usr/local/lib/perl5/site_perl.

/Usr/local/lib/usr/lib)

Dynaloader: Bootstrap for DBI (Auto/DBI. So)

This indicates that your DBI package has been installed successfully.

The running Modes of DBI and DBD packages are as follows:

(Figure: working modes of DBI and DBD)

Let's take a look at the typical process of connecting data:

1. Load the DBI driver

2. Use DBD to connect to the corresponding database

3. Open a cursor containing SQL commands

4. retrieve a dataset

5. Close the cursor

6. Close the database connection

7. Exit

Before using DBI, we must first declare:

#! /Usr/bin/perl-W

Use dBi;

We have two ways to establish a connection between Perl and databases:

#! /Usr/bin/perl-W

Use dBi;

# Establish a connection to the database. The first parameter indicates the database type.

$ DBH = DBI-> connect ('Connection _ string', 'username', 'Password', 'msql ');

If (! Defined $ DBH ){

Die "cannot do/$ DBH-> CONNECT: $ DBI: errstr/N ";

}

In this way, a database handle is returned. This is a common usage, and another method returns the "Driver handle ":

#! /Usr/bin/perl-W

Use dBi;

$ DRH = DBI-> install_driver ('msql ');

If (! Defined $ DRH ){

Die "cannot load driver: $! /N ";

}

This method is used to check whether a driver exists in the system.

In use, there are three types of handles to be involved in programming: driver handle, database handle, and statement handle ), the relationship between them can be used to represent:

(Figure: working relationship between driver handle, data handle, and statement handle)

The following describes the process of using DBI to process databases:

1. The connection (database) is closed.

#! /Usr/bin/perl-W

#

# (C) 1996 alligator Descartes

#

# Inout. Pl: connects and disconnects from a specified database

Use dBi;

If ($ # argv <0 ){

Die "Usage: inout. pl/N ";

}

# Create new database handle. If we can't connect, die ()

$ DBH = DBI-> connect ('', $ argv [0],'', $ argv [1]);

If (! Defined $ DBH ){

Die "cannot connect to msql server: $ DBI: errstr/N ";

}

# Disconnect from the database

$ DBH-> disconnect;

Exit;

2. DBI and DBD: MySQL
DBI is a common interface for many databases, which means you can write a script that can work in many different databases. Therefore, you need a database Driver (DBD) defined for each database type. For MySQL, this driver is called DBD: MySQL. You can refer to dbis web page for more information. For more information about Object-Oriented Programming in perl5, see the Perl OOP page.

Iii. DBI Interface
General DBI Method

Connect
Establish a connection with a database server

Prepare
Obtain the SQL statement to be executed

Do
Prepare and execute an SQL statement

Disconnect
Disconnect from a database server

Quote
Inserted referenced string (Block)

Execute
Execute the Stored Procedure

Fetchrow_array
Extract the next row to an array.

Fetchrow_arrayref
Extracts the next row into the array and returns the array reference.

Fetchrow_hashref
Extract the next row to the hash table and return its reference

Fetchall_arrayref
Retrieve all the data to an array and return its reference.

Finish
Release System Resources

Rows
Number of rows to be returned

Data_sources
Returns an array of available databases on the local machine.

Chopbalanks
Remove Spaces

Num_of_params
Number of placeholders in Stored Procedures

Nullable
Which row can be null.

Special MySQL Methods

Insertid
Value that is automatically incremented

Is_blob
Rows of blob

Is_key
Is a key row.

Is_num
Is a number row

Is_pri_key
The row with the primary key.

Is_not_null
The row cannot be null.

Length
Theoretically the maximum number of Columns

Max_length
The maximum number of physical Columns

Name
Column name

Num_of_fields
Number of returned Fields

Table
Name of the table in the returned set

Type
Row Type

_ Createdb
Create a database

_ Dropdb
Delete A Database

Connect:
Use the connect method to establish a connection to the data source. $ Data_source should start with DBI: driver_name:, for example:
$ 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 user name or password is not defined, DBI uses the environment variables dbi_user and dbi_pass as their values. If you do not define a host, the default value is "localhost". If you do not define a port number, use the default MySQL port number (3306) as the port number.

Prepare:
Prepare SQL statements by using the database engine and return a statement handle ($ something) to participate in the execute method. For example:
$ Something = $ DBH-> prepare ($ statement) or die "can't prepare $ statement: $ DBH-> errstr/N ";
Do
The do method prepares and executes an SQL statement and returns the number of rows acting on it. This method is usually used for non-select statements and generally does not need to be executed multiple times (for example, insert, delete ). For example:
$ Rc = $ DBH-> do ($ statement) or die "can't execute $ statement: $ DBH-> errstr/N ";
Disconnect
Disconnect will disconnect from the database, which is usually used at the end of the program. For example:
$ Rc = $ DBH-> disconnect;
Quote
The quote method is used to "escape" any specific header in the string, and the reference mark is added.
$ SQL = $ DBH-> quote ($ string)
Execute
This method executes a stored statement. For a non-select statement, it returns the number of rows. For a SELECT statement, this method only starts to query data in the database. You need the fetch _ * method to retrieve data.
$ Rv = $…-> execute or die "can't execute the query: $…-> errstr;
Fetchrow_array
This method retrieves the data of the next row and stores it in an array. For example:
While (@ ROW = $ th-> fetchrow_array) {print QW ($ row [0]/t $ row [1]/t $ row [2]/n );}
Fetchrow_arrayref
This method retrieves the data of the next row and returns it to a reference to the array. For example:
While ($ row_ref = $ th-> fetchrow_arrayref) {print QW ($ row_ref-> [0]/t $ row_ref-> [1]/t $ row_ref-> [2]/n );}
Fetchrow_hashref
This method retrieves a row of data and returns a reference to a hash table containing Field Names/values. This method is not as efficient as using an array reference method. For example:
While ($ hash_ref = $ something-> fetchrow_hashref) {print QW ($ hash_ref-> {firstname}/T $ hash_ref-> {lastname}/T/$ hash_ref-> title}/n );}
Fetchall_arrayref
This method is used to retrieve all data (rows) from the execution result of an SQL statement ). It returns an array reference. You can print/display the data in a loop.
My $ table = $……> fetchall_arrayref or die "$……> 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 can be retrieved. You can release the statement handle and release system resources by using this method. For example:
$ Rc = $……-> finish;
Rows
Returns the number of rows that are used in (updated, delete, etc.) operations. This is usually used after the do () or non-select execute () statement. For example:
$ Rv = $ something-> rows;
Nullable
Returns an array reference. True indicates that this column can be null.
$ Null_possible = $ something-> {nullable };
Num_of_fields
The number of columns returned by the Select or listfileds statement. If the number is 0, it indicates the execution of a non-select statement, such as insert, delete or update.
$ Nr_of_fields = $ something-> {num_of_fields };
Data_sources
This method returns an array of available databases in the MySQL service in localhost.
@ DBS = DBI-> data_sources ("MySQL ");
Chopbalanks
This method determines whether spaces are removed from the returned rows.
$ Something-> {'chopbles') = 1;

Insertid
If you use the automatic value-added feature of MySQL, the latest automatic value-added feature will be stored. For example:
$ New_id = $ something-> {insertid };
Is_blob
Returns an array reference. True indicates that the indicated column is blob.
$ Keys = $ something-> {is_blob };
Is_key
Returns an array reference. True indicates that the indicated column is the key.
$ Keys = $ something-> {is_key };
Is_num
Returns an array reference. True indicates that the indicated column contains numbers.
$ Nums = $ something-> {is_num };
Is_pri_key
Returns an array reference. True indicates that the indicated column is a primary key.
$ Pri_keys = $ something-> {is_pri_key };
Is_not_null
Returns an array reference. "false" indicates that this column can contain null. You 'd better use the nullable attribute in the DBI standard.
$ Not_nulls = $ something-> {is_not_null };
Max_length, Length
Returns an array reference that indicates the maximum column size. The maximum length refers to the maximum value in the result table, and the length gives the theoretical maximum value.
$ Max_lengts = $ something-> {max_length}; $ lengts = $ something-> {length };
Name
Returns an array of column names.
$ Names = $ something-> {name };
Table
Returns an array reference of the table name.
$ Tables = $ something-> {table };