YouTip

In this chapter, we will introduce how to connect to a database using Perl. In Perl 5, we can use the DBI module to connect to databases. DBI stands for Database Independent Interface. It serves as a standard interface for Perl to communicate with databases, defining a set of methods, variables, and constants to provide a database persistence layer that is independent of the specific database platform. * * * ## DBI Structure DBI is independent of the specific database platform and can be used with databases such as Oracle, MySQL, or Informix. !(#) The diagram shows that DBI receives SQL data from all APIs (Application Programming Interfaces), dispatches it to the corresponding driver for execution, and then retrieves and returns the data. ### Variable Name Conventions The following are common variable naming conventions: $dsn The handle for the driver object $dbh The handle for a database object $sth The handle for a statement or query object $h A generic handle ($dbh, $sth, or $drh), depending on the context $rc The boolean value (true or false) returned by an operation $rv The integer value returned by an operation @ary An array (list) of values returned by a query for a single row $rows The number of rows returned by an operation $fh A file handle undef The NULL value, indicating undefined %attr A reference to a hash of attributes passed to a method * * * ## Database Connection Next, we will use a MySQL database as an example to demonstrate how Perl interacts with a database. Here, we create a database named TUTORIAL in MySQL, with a table named Websites. The table structure and data are shown below: !(#) Download the table data: [https://static.jyshare.com/download/websites_perl.sql](https://static.jyshare.com/download/websites_perl.sql) Next, we use the following code to connect to the database: ## Example #!/usr/bin/perl -w use strict; use DBI; my$host = "localhost"; my$driver = "mysql"; my$database = "TUTORIAL"; my$dsn = "DBI:$driver:database=$database:$host"; my$userid = "root"; my$password = "123456"; my$dbh = DBI->connect($dsn, $userid, $password)or die$DBI::errstr; my$sth = $dbh->prepare("SELECT * FROM Websites"); $sth->execute(); while(my@row = $sth->fetchrow_array()){print join('t', @row)."n"; }$sth->finish(); $dbh->disconnect(); * * * ## Insert Operation Execution Steps: * Use the prepare() API to preprocess the SQL statement. * Use the execute() API to execute the SQL statement. * Use the finish() API to release the statement handle. * Finally, if everything goes well, commit the above operations. 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; Applications can also bind output and input parameters. The following example executes an insert query by using variables to replace the ? placeholders: 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 Execution Steps: * Use the prepare() API to preprocess the SQL statement. * Use the execute() API to execute the SQL statement. * Use the finish() API to release the statement handle. * Finally, if everything goes well, commit the above operations. 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; Applications can also bind output and input parameters. The following example executes an update query by using variables to replace the ? placeholders: $name = '';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 values to be set. The following example changes the alexa value to 1000 for all records where the country is CN: $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 Execution Steps: * Use the prepare() API to preprocess the SQL statement. * Use the execute() API to execute the SQL statement. * Use the finish() API to release the statement handle. * Finally, if everything goes well, commit the above operations. The following data will delete all records from Websites where the alexa value is greater than 1000: $alexa = 1000;my $sth = $dbh->prepare("DELETE FROM Websites WHERE alexa = ?"); $sth->execute( $alexa ) or die $DBI::errstr;print "Number of deleted records :" + $sth->rows; $sth->finish(); $dbh->commit or die $DBI::errstr; * * * ## Using the do Statement The **do** statement can execute UPDATE, INSERT, or DELETE operations. It is more concise. It returns true on success and false on failure. Example: $dbh->do('DELETE FROM Websites WHERE alexa>1000'); * * * ## COMMIT Operation Commit finalizes the transaction, completing the database operation: $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; * * * Transactions Like other languages, Perl DBI supports transaction processing for database operations. There are two ways to implement it: ### 1. Start a transaction when connecting to the database $dbh = DBI->connect($dsn, $userid, $password, {AutoCommit => 0}) or die $DBI::errstr; The above code sets AutoCommit to false when connecting. This means that when you perform update operations on the database, it will not automatically write those updates directly to the database. Instead, the program must use $dbh->commit to actually write the data to the database, or $dbh->rollback to roll back the recent operations. ### 2. Start a transaction using the $dbh->begin_work() statement This method does not require setting AutoCommit = 0 when connecting to the database. You can perform multiple transaction operations on a single database connection without needing to connect to the database for each transaction start. $rc = $dbh->begin_work or die $dbh->errstr;####################### Execute some SQL operations here ##################### $dbh->commit; # Operation on success----------------------------- $dbh->rollback; # Roll back on failure * * * ## Disconnecting from the Database If we need to disconnect from the database, we can use the disconnect API: $rc = $dbh->disconnect or warn $dbh->errstr;