Perl DBI Summary

Source Text Address: http://blog.csdn.net/like_zhz/article/details/5441946

Different relational models of DBI and DBD:

##########################################################################
The portable DBI Method:
Connect establishes a connection to a database server
Disconnect disconnecting the database server
Prepare preparing to execute an SQL statement
Execute executes the prepared statement
Do prepare and execute an SQL statement
Quote quoted in the string or BLOB value to be inserted
Fetchrow_array the next line as a field array
Fetchrow_arrayref the next line as a reference array for a field
Fetchrow_hashref the next line as a reference to a hash table
Fetchall_arrayref out all data as a field array
Finish completes a statement and lets the system release resources
Rows returns the number of rows affected
Data_sources returns an array of databases that can be obtained on localhost
Chopblanks controls whether the Fetchrow_* method strips space
Num_of_params the number of placeholders (placeholder-parameters) in the prepared statement
NULLABLE its column can be null
Trace performs debug tracing
##########################################################################
$DBH Database Handle
$STH statement Handle
$RC return code (often a state)
$RV return value (often a number of rows)
##########################################################################
①connect ($data _source, $username, $password)
Use the Connect method to make a database connect to a data source. $data _source value should start with Dbi:driver_name:. Examples of using connect with the Dbd::mysql driver:
$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);

②disconnect
The Disconnect method disconnects the database handle from the database. It is usually called before you exit from the program. Example:
$RC = $dbh->disconnect;
Prepare ($statement)
Prepares a SQL statement executed by the database engine and returns a statement handle ($STH) that you can use to invoke the Execute method. Generally you use prepare and execute to handle SELECT statements (and class SELECT statements, such as show, describe, and explain). Example:
$sth = $dbh->prepare ($statement)
Or die "Can ' t prepare $statement: $DBH->errstr/n";
③execute
The Execute method executes a prepared statement. For non-SELECT statements, execute returns the number of rows affected. If no rows are affected, execute returns "0E0", and Perl sees it as zero rather than true. For the SELECT statement, execute simply launches the SQL query in the database; you need to retrieve the data using one of the fetch_* methods described below. Example:
$RV = $sth->execute
Or die "can ' t execute the query: $sth->errstr;
④do ($statement)
The Do method prepares and executes an SQL statement and returns the number of rows affected. If no rows are affected, DO returns "0E0", and Perl treats it as zero rather than true. This method is typically used for non-SELECT statements that cannot be prepared beforehand (due to driver limitations) or do not need to execute multiple times (insert, delete, and so on). Example:
$RV = $dbh->do ($statement)
Or die "Can ' t execute $statement: $DBH->errstr/n";
⑤quote ($string)
The quote method is used to "escape" any special characters contained in a string and to increase the required external quotation marks. Example:
$sql = $dbh->quote ($string)
⑥fetchrow_array
This method removes a row of data and returns it as an array of field values. Example:
while (@row = $sth->fetchrow_array) {
Print QW ($row [0]/t$row[1]/t$row[2]/n);
}
⑦fetchrow_arrayref
This method removes a row of data and returns it as a reference to an array of field values. Example:
while ($row _ref = $sth->fetchrow_arrayref) {
Print QW ($row _ref->[0]/t$row_ref->[1]/t$row_ref->[2]/n);
}
⑧fetchrow_hashref
This method takes a row of data and returns a reference to a hash table that contains the field name/value pairs. This method is not as effective as using the array reference above. Example:
while ($hash _ref = $sth->fetchrow_hashref) {
Print QW ($hash _ref->{firstname}/t$hash_ref->{lastname}/t/
$hash _ref-> title}/n);
}
⑨fetchall_arrayref
This method is used to obtain all the data (rows) that are returned from the SQL statement. It returns a reference to an array that contains a reference to an array of each row. You use a nested loop to access or print data. Example:
My $table = $sth->fetchall_arrayref
Or die "$sth->errstr/n";
My ($i, $j);
For $i (0: $#{$table}) {
For $j (0: $#{$table->[$i]}) {
Print "$table->[$i] [$j]/t";
}
print "/n";
}
⑩finish
The name no more data will be removed from this statement handle. You call this method to release the statement handle and any system resources associated with it. Example:
$RC = $sth->finish;
? rows
Returns the number of rows changed (updated, deleted, and so on) by the last command. This is typically used after an EXECUTE statement that is not a select. Example:
$RV = $sth->rows;
? NULLABLE
Returns a reference to an array of Boolean values, and a true value for each member of the array that indicates that the column can contain null values. Example:
$null _possible = $sth->{nullable};
? Num_of_fields
This property indicates the number of fields returned by a Select or Show field statement. You can use it to check if a statement returns a result: a 0 value indicates a non-select statement like INSERT, delete, or update. Example:
$nr _of_fields = $sth->{num_of_fields};
? data_sources ($driver _name)
This method returns an array that contains the database names that the MySQL server on host ' localhost ' can get. Example:
@dbs = dbi->data_sources ("MySQL");
? Chopblanks
This property determines whether the Fetchrow_* method will strip the header and trailing blanks of the return value. Example:
$sth->{' chopblanks '} = 1;
Trace ($trace _level)
? Trace ($trace _level, $trace _filename)
The trace method turns tracing on or off. When called as a dbi class method, it affects the tracking of all handles. When called as a database or a statement handle method, it affects the tracking of the given handle (and the future descendants of the handle). Set $trace_level to 2 to provide detailed trace information, set $trace_level to 0 to turn off tracing. The trace output is output by default to the standard error output. If you specify $trace_filename, the file is opened in Add mode and the hand of all tracked handles is written to the file. Example:
Dbi->trace (2); # Trace Everything
  dbi->trace (2, "/tmp/dbi.out"); Trace everything to/tmp/dbi.out
  $ Dth->trace (2);               # Trace This database handle
   $sth->trace ( 2);               # Trace This statement Handle
############################################## ############################

##########################################################################

Connect to Informix Database fetch data:------

#!/usr/bin/perl

Use DBI;
Use Dbd::informix;

My @field;
My @sql_list;
My @cmd_list;
My $time _stamp;
My $time _stamp2;
My $w _file;
my $count = 0;

My $vendor =shift;
My $db _type=shift;
My $db _server=shift;
My $user _name=shift;
My $password =shift;
My $OMC _name=shift;
My $src _dir=shift;
My $desc _dir=shift;
My $cmd _list=shift;
My $sql _list=shift;

#Get the current time and open the write file
My ($sec, $min, $hour, $mday, $mon, $year) = (localtime) [0,1,2,3,4,5];
$year = $year + 1900;
$mon = $mon +1;
$mon =sprintf ("%02d", $mon);
$mday =sprintf ("%02d", $mday);
$hour =sprintf ("%02d", $hour);
$min =sprintf ("%02d", $min);
$sec =sprintf ("%02d", $sec);
$time _stamp= $year $mon $mday. $hour. $min. $sec;
$time _stamp2= $year. " -". $mon." -". $mday." ". $hour.": $min. ":". $sec;

#Connect to the database
Print "Start connecting $db_type database [databaseserver: $db _server].../n";
My $dbh = Dbi->connect ("DBI: $db _type: $db _server", "$user _name", "$password", {printerror = 0});
If ($DBH) {
Print "Connection $db_type database successful [databaseserver: $db _server].../n";
}else{
Print "Connection to $db_type database failed [databaseserver: $db _server].../n";
Exit (1);
}

For ($i =0; $i < @sql_list; $i + +) {
Combine file names and open write files
$vendor =uc ($vendor);
$OMC _name=uc ($OMC _name);
$cmd _list[$i]=lc ($cmd _list[$i]);
$file _name= "mss# $vendor # $OMC _name# $cmd _list[$i]# $time _stamp#100#";
Open (Wfile, "> $src _dir/$file _name") | | Die "Open file failed!/n";

#Execute SELECT statement and get data
$sql _list[$i]=~s//n//n/g;
Print "Execute SQL statement: /n[$sql _list[$i]]/n";
My $sth = $dbh->prepare (qq{$sql _list[$i]});
$sth->execute ();
Print "Execute SQL statement succeeded .../n";

Read record data
While (@field = $sth->fetchrow_array) {
Write_file (Wfile, ($OMC _name));
Write_file (Wfile, (@field));
Write_file (Wfile, ($time _stamp2));
Print Wfile "/n";
$count + +;
}
Close (Wfile);

If ($count eq 0) {
Print "No get to record .../n/n";
System ("Rm-f $src _dir/$file _name");
}else{
$file _name_src= $file _name. " 0.src ";
Print "Successfully obtained $count record .../n";
Print "Generate file [$file _name_src].../n/n";
System ("Mv-f $src _dir/$file _name $desc _dir/$file _name_src");
}
$count = 0;
$sth->finish ();
}

#Close the database connection
$DBH->disconnect ();
Print "Close $db_type database connection [databaseserver: $db _server].../n";

Sub Trim
{
My ($line) [email protected]_;
$line =~s/^[/t]+//; #Replace the leading space
$line =~s/[/t/r/n]+$//; #Remove trailing spaces
Return $line;
}

Sub Write_file
{
My $i = 0;
My ($W _file, @row_data) [email protected]_;
My [email protected]_data;
For ($i =0; $i < $col _num; $i + +)
{
$row _data[$i]=trim ($row _data[$i]);
Print $W _file $row _data[$i]. "|";
}
}

Perl DBI Summary