scmuser created the topic: Use of use DBI;
Hi,
why do we use following syntax in perl program. can you please explain it?
use DBI;
rajeshkumar replied the topic: Re: Use of use DBI;
Perl’s coolest modules is the Perl database interface (DBI). This module offers a unified interface to different databases, providing a series of generic functions that are internally translated into native function calls. This makes it extremely easy to take advantage of a database and create dynamic Web pages using Perl.
Download and install
To begin, download and install the Perl DBI module and the MySQL DBD driver by running the following commands at your Perl prompt:
perl> perl -MCPAN -e "install DBI"
perl> perl -MCPAN -e "install DBD::mysql"
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.
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.
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.
#!/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
";
}
Regards,
Rajesh Kumar
Twitt me @ twitter.com/RajeshKumarIn
rajeshkumar replied the topic: Re: Use of use DBI;
One more example for connecting database using perl
You can connect to MySQL database from a Perl script using the Perl DBI module. DBI stands for “Database Interface” and is a database layer, with simple interface for SQL queries. Here is a Perl script connecting to MySQL database and printing FirstName and LastName columns from a table called Users:
#!/usr/local/bin/perl
use DBI;
print “Content-type:text/html\n\n”;
$dbh = DBI->connect(“dbi:mysql:database=; host=localhost; user=; password=”)
or die “Connecting from PHP to MySQL database failed: $DBI::errstr”;
$sSQL = “SELECT FirstName, LastName FROM Users”;
$st = $dbh->prepare($sSQL)
or die “Preparing MySQL query failed: $DBI::errstr
“;
$st->execute()
or die “The execution of the MySQL query failed: $DBI::errstr”;
while ($row = $st->fetchrow_hashref())
{
print ” $row->{FirstName} $row->{LastName}
“;
}
$dbh ->disconnect();
Regards,
Rajesh Kumar
Twitt me @ twitter.com/RajeshKumarIn
rajeshkumar replied the topic: Re: Use of use DBI;
One more example…
#!/usr/bin/perl
use DBI;
$database = "DBNAME";
$hostname = "DBSERVER";
$port = "3306";
$username = "DBUSERNAME";
$password = 'DBPASSWORD';
$dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
$dbh = DBI->connect($dsn, $username, $password) or die("Could not connect!");
$sql = "SELECT * FROM mytable";
$sth = $dbh->prepare($sql);
$sth->execute;
while(($column1, $column2) = $sth->fetchrow_array)
{
print "C1 = $column1, C2 = $column2n";
}
$dbh->disconnect;
where DBNAME, DBUSERNAME, and DBPASSWORD are your database name, database username and database password, and where DBSERVER is your database server.
Regards,
Rajesh Kumar
Twitt me @ twitter.com/RajeshKumarIn
- DevOps Certified Professionals (DCP)
- Site Reliability Engineering Certified Professionals (SRECP)
- Master in DevOps Engineering (MDE)
- DevSecOps Certified Professionals (DSOCP)
URL - https://www.devopsschool.com/certification/
My Linkedin - https://www.linkedin.com/in/rajeshkumarin
- Best AI tools for Software Engineers - November 4, 2024
- Installing Jupyter: Get up and running on your computer - November 2, 2024
- An Introduction of SymOps by SymOps.com - October 30, 2024
