Use Perl DBI to connect to the MySQL database

 

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: 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. 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. ForSelectQuery, The result object is further processed to extract data. Use a loop,Fetchrow_hashref ()The method returns each record as a Perl signal. 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!