PERL - DBI Query

Queries must be prepared and then executed. Two lines of code are required for this, first the prepare() function and then the execute() function.

PERL - DBI Prepare()

Inside the prepare() function lies the actual SQL query. Essentially the prepare function acts precisely like the console of an SQL platform. If you've been following along, all we need to do is define a variable with a(n) SQL statement. Then create a query handle and run our $connect statement along with the prepare function as outlined below.
The only main difference is that we have to use PERL's escaping characters and we probably have to use them more often.

dbipreparequery.pl:

#!/usr/bin/perl

# PERL MODULES WE WILL BE USING
use DBI;
use DBD::mysql;

# HTTP HEADER
print "Content-type: text/html \n\n";

# CONFIG VARIABLES
$platform = "mysql";
$database = "store";
$host = "localhost";
$port = "3306";
$tablename = "inventory";
$user = "username";
$pw = "password";

# DATA SOURCE NAME
$dsn = "dbi:mysql:$database:localhost:3306";

# PERL DBI CONNECT
$connect = DBI->connect($dsn, $user, $pw);

# PREPARE THE QUERY
$query = "INSERT INTO inventory (id, product, quantity) VALUES (DEFAULT, tomatoes, 4)";
$query_handle = $connect->prepare($query);

PERL - DBI Execute

Once the query has been prepared, we must execute the command with the execute function. This is accomplished in one final line appended to the code above.

dbiexecutequery.pl:

#!/usr/bin/perl

# PERL MODULES WE WILL BE USING
use DBI;
use DBD::mysql;

# HTTP HEADER
print "Content-type: text/html \n\n";

# CONFIG VARIABLES
$platform = "mysql";
$database = "store";
$host = "localhost";
$port = "3306";
$tablename = "inventory";
$user = "username";
$pw = "password";

# DATA SOURCE NAME
$dsn = "dbi:$platform:$database:$host:$port";

# PERL DBI CONNECT
$connect = DBI->connect($dsn, $user, $pw);

# PREPARE THE QUERY
$query = "INSERT INTO inventory (id, product, quantity) VALUES (DEFAULT, 'tomatoes', '4')";
$query_handle = $connect->prepare($query);

# EXECUTE THE QUERY
$query_handle->execute();

PERL - DBI Select Queries

Select queries fetch results and then return those results in the form of an array. Accessing the results of the array requires first that we bind the columns to variable names. Then we just need to set up a loop to loop through each row and print back the results to our browser.

dbiselectquery.pl:

#!/usr/bin/perl

# PERL MODULES WE WILL BE USING
use DBI;
use DBD::mysql;

# HTTP HEADER
print "Content-type: text/html \n\n";

# CONFIG VARIABLES
$platform = "mysql";
$database = "store";
$host = "localhost";
$port = "3306";
$tablename = "inventory";
$user = "username";
$pw = "password";

# DATA SOURCE NAME
$dsn = "dbi:mysql:$database:localhost:3306";

# PERL DBI CONNECT
$connect = DBI->connect($dsn, $user, $pw);

# PREPARE THE QUERY
$query = "SELECT * FROM inventory ORDER BY id";
$query_handle = $connect->prepare($query);

# EXECUTE THE QUERY
$query_handle->execute();

# BIND TABLE COLUMNS TO VARIABLES
$query_handle->bind_columns(undef, \$id, \$product, \$quantity);

# LOOP THROUGH RESULTS
while($query_handle->fetch()) {
   print "$id, $product, $quantity <br />";
} 

Two new functions were introduced in that last example, the bind_columns and the fetch() functions. Both are fairly self explanatory. Variable names are assigned to our column values via the bind_column function and the fetch() function goes out and fetches the rows matching the query

Posted in: PERL