Use Perl DBI to connect to the MySQL database

Source: Internet
Author: User

 

 


One of the coolest modules in Perl is the Perl database interface (DBI ). By providing a series of internal functions that can be converted into original call functions, the DBI module provides a unified interface for many different databases. With Perl, you can easily use databases and create dynamic web pages.

Currently, MySQL is a database widely used for Web website development. It is a free and open-source SQL operation. This article describes how to implement the communication between Perl and MySQL. It describes the important methods provided by DBI and a simple Script Template during development. The premise of this operation is that your system has installed MySQL and Perl.

Download and install

Start, download and install the Perl DBI module and MySQL DBD. Run the following command in the Perl Command Line to complete the installation process:

Perl> Perl-mcpan-e "Install DBI"
Perl> Perl-mcpan-e "Install DBD: MySQL"

Note: You can manually download andInstall DBIAndMySQL DBD.

Now Perl DBI and MySQL DBD should be installed in your system.

Then, enter the following command on the mysql client command line to create a table for SQL query.

Mysql> Create Table users (ID int (4) primary key, username varchar (25), country varchar (2 ));
Query OK, 0 rows affected (0.11 Sec)

Mysql> insert into users values (1, 'john', 'in'), (2, 'Tom ', 'us'), (3, 'layla ', 'us ');
Query OK, 3 rows affected (0.11 Sec)
Records: 3 duplicates: 0 Warnings: 0

Once a table is created, use the DBI method to create a Script Template (see table ).

Table

#! /Bin/perl

# Load Module
Use dBi;

# Connect
My $ DBH = DBI-> connect ("DBI: mysql: Database = DB2; host = localhost", "Joe", "guessme ", {'raiseerror' => 1 });

# Execute insert Query
My $ rows = $ DBH-> do ("insert into users (ID, username, country) values (4, 'Jay ', 'cz ')");
Print "$ rows row (s) affected ";

# Execute SELECT query
My $ something = $ DBH-> prepare ("select username, country from users ");
$ Something-> execute ();

# Iterate through resultset
# Print values
While (my $ ref = $ something-> fetchrow_hashref ()){
Print "User: $ ref-> ";
Print "Country: $ ref-> ";
Print "----------";
}

# Clean up

$ DBH-> finish (); // Add the release result set. Otherwise, an error is returned during disconnect.
$ DBH-> disconnect ();

Four steps

When using Perl DBI to execute an SQL query, follow these four simple steps:

  1. Start by callingConnect ()Method to initialize the database handle.Connect ()Method to receive connection parameters and use them as strings, including database types ("MySQL"), Host name ("Localhost"), And database name ("DB2"). Database Name ("Joe") And password ("Guessme") As the second and third variablesConnect ()Method.
  2. Create an SQL query string and useDo ()OrPrepare ()AndExecute ()Method to execute the query statement.Do ()This method is applicable to one-time use.Insert, updateOrDeleteQuery,Prepare ()AndExecute ()The method isSelectQuery. The objects obtained by using these methods are different, depending on the type of the query, and whether the query result is successful or not. SuccessfulSelectThe query returns a result object.Insert/update/deleteThe query returns some related rows. If the query fails, an error is returned.
  3. ForSelectQuery, The result object is further processed to extract data. Use a loop,Fetchrow_hashref ()The method returns each record as a Perl signal.
  4. By callingDisconnect ()Method to end the session.

This script module saves you time when you continue writing MySQL database connection code in Perl next time. Happy programming!

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.