Perl database connection

DBI structure

DBI has nothing to do with the specific database platform, we can apply it in Oracle , MySQL or Informix, etc.

DBI in the chart gets all the API (Application Programming) Interface . The SQL data sent by the API is then distributed to the corresponding driverfor execution, and finally the data is obtained and returned.

Handle to dsn driver object
$dbh Handle to a database object
$sth Handle to a statement or query object
$h universal handle ($dbh, $sth, or $drh), context dependent
The Bush value returned by the $rc operation code (true or false)
The integer value returned by the $rv operation code
@Array (list) of a row of values returned by an ary query
The row value returned by the $rows operation code
$fh file handle
Undef NULL value indicates undefined
\%Attr references the hash value of the attribute and passes it to the method

Database connection

Next, let’s take MySQL database as an example to demonstrate how Perl operates on the database.

Here we create in the MySQL database RUNOOB database, the datasheet isthe Websites table structure and data are shown in the following figure:

Download the data sheet: https://static.runoob.com/download/websites_perl.sql

Next, we use the following code to connect to the database:

#/ Usr/bin/perl - wuse strict; Use DBI; My $host="localhost"#
Host address my $driver="mysql"# The interface type defaults to
Localhost $database="RUNOOB"# Database#
The handle of the driver object, my $dsn="DBI: $driver: database=$database: $host"; My $userid="root"#
Database username my $password="123456"# Database password#
Connect to database my $dbh=DBI ->connect ($dsn, $userid, $password) order $DBI:: errstr; My $sth=$dbh ->prepare ("SELECT
*From Websites "; # Preprocessing SQL Statement $sth ->execute(); # Executing SQL Operations#
The annotation section uses the binding value operation # $alexa=20# My $sth=
$dbh ->prepare ("SELECT name, URL # From Websites # WHERE alexa>?")#
Execute ($alexa) or die $DBI:: errstr#
Loop out all data while（ my@row =$sth ->fetchrow_ Array() {printjoin (' t', @ row). " n";} $th ->finish()$ Dbh ->disconnect();

Insert operation

Perform the steps:

• Use prepare() API preprocesses SQL statements.

• Use execute() API executes the SQL statement.

• Use finish() API releases the statement handle.

• Finally, if all goes well, the above actions will be submitted.

my $sth = $dbh->prepare("INSERT INTO Websites
                       (name, url, alexa, country )
                        values
                       ('Twitter', 'https://twitter.com/', 10, 'USA')");
$sth->execute() or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

The application can also bind output and input parameters, as shown in the following example by replacing the ? placeholder to execute an insert query:

my $name = "Twitter";
my $url = "https://twitter.com/";
my $alexa = 10;
my $country = "USA";
my $sth = $dbh->prepare("INSERT INTO Websites
                       (name, url, alexa, country )
                        values
                       (?,?,?,?)");
$sth->execute($name,$url,$alexa, $country)
          or die $DBI::errstr;
$sth->finish();
$dbh->commit or die $DBI::errstr;

Update operation

Perform the steps:

• Use prepare() API preprocesses SQL statements.

• Use execute() API executes the SQL statement.

• Use finish() API releases the statement handle.

• Finally, if all goes well, the above actions will be submitted.

my $sth = $dbh->prepare("UPDATE Websites
                        SET   alexa = alexa + 1
                        WHERE country = 'CN'");
$sth->execute() or die $DBI::errstr;
print "Number of updated records:" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

The application can also bind output and input parameters, as shown in the following example by replacing the ? placeholder to execute an update query:

$name = 'Novice Tutorial';

my $sth = $dbh->prepare("UPDATE Websites
                        SET   alexa = alexa + 1
                        WHERE name = ?");
$sth->execute('$name') or die $DBI::errstr;
print "Number of updated records :" + $sth->rows;
$sth->finish();

Of course, we can also bind the value to be set, as shown below country for CN of alexa are modified to 1000 :

$country = 'CN';
$alexa = 1000：;
my $sth = $dbh->prepare("UPDATE Websites
                        SET   alexa = ?
                        WHERE country = ?");
$sth->execute( $alexa, '$country') or die $DBI::errstr;
print "Number of updated records :" + $sth->rows;
$sth->finish();

Delete data

Perform the steps:

• Use prepare() API preprocesses SQL statements.

• Use execute() API executes the SQL statement.

• Use finish() API releases the statement handle.

• Finally, if all goes well, the above actions will be submitted.

The following data will Websites in alexa data greater than 1000 is deleted:

$alexa = 1000;
my $sth = $dbh->prepare("DELETE FROM Websites
                        WHERE alexa = ?");
$sth->execute( $alexa ) or die $DBI::errstr;
print "Number of records deleted :" + $sth->rows;
$sth->finish();
$dbh->commit or die $DBI::errstr;

Use the do statement

The do statement can perform operations such as UPDATE , INSERT , or DELETE. It is relatively short to use and returns’ true’ for successful execution and false for failed execution. The example is as follows:“

$dbh->do('DELETE FROM Websites WHERE alexa>1000');

COMMIT operation

commit to commit the transaction, complete the operation of the database:

$dbh->commit or die $dbh->errstr;

ROLLBACK operation

If an error occurs during SQL execution, you can roll back the data without making any changes:

$dbh->rollback or die $dbh->errstr;

Business