How to use Perl to interact with MySQL Database

2017-09-02

Introduction

I have found the Practical Extraction and Report Language (PERL) to be the best way to access to the database. I do use the MySQL Workbench the GUI design tool that integrates development into an integrated environment. However, invoking a PERL script in the terminal is the fastest to do tasks such as filters, inserting records, or even processing data (that is more advanced and will be addressed later).

Before you try this make sure you have PERL with DBI and CGI modules running. You also need to have the MySQL database running as per previous procedure. You also need to know your MySQL user name and password.

Procedure

1. Create a text file to store your PERL script.

2. Invoke perl. After shebang specify the pahtname for the perl program. May be different on your system. Shebang is a character sequence that the terminal uses to execute the path following. Use strict to force you to make your declarations explicit. This is just used to help debugging. Use DBI to invoke the DBI module for interacting with MySQL.

3. Next section is variable declarations.

4. Use basic PERL commands to take in user input.

5. To use DBI to interact with MySQL first connect to MySQL. If the connect function works, it returns a database handle assigned to $dbh. If connect function fails it returns undef. From this point we are going to use several conventional DBI handles, $dbh - a handle to database object, $sth a handle to a query object.

6. To issue a SELECT query is completed in three steps; 1. temporarily store SELECT statement as a string in variable $query, 2) Invoke prepare() using the $dbh database handle. Prepare sends the statement to the driver for preprocessing prior to executing. The return value from prepare() is stored in the $sth statement handle. The $sth is then executed to complete the query. Note the MySQL statement does not require semicolons as required in the MySQL program. The semicolon terminator is a convention of MySQL and is not used in DBI scripts. At this stage the query output is in $sth ready for further processing.

7. Use fetchrow_array function to process $sth, and return the number of data entries in example table. Then populate the $info_id variable with plus one to create a unique record identification. The last row prints the record identification with the data for troubleshooting. Comment out if not required.

8. Now invoke an INSERT INTO MySQL command to populate a new data record. Using the same DBI handles so will not explain again.

9. Now terminate the script.

10. Now close the text editor you are using. Check the permissions on the script file. Make sure it is user executable. Now execute command in the terminal.

Conclusion

This procedure will create a PERL script to create a data entry in the example table in test database. Refer to procedure to create an Information Database for further information on creating the database table.

Open *.pl file using text editor.

Sql Code Text File.