Database Connection

Simple introduction to SQL

You will need a working install of a database server to try this. MySQL and PostgreSQL should be an easy install for any Linux system.

If you are new to SQL check out this tutorial on Introduction to SQL.
Once you have your database server up and running and you have a database there are basically only four commands you will need to master before you can set started.

  1. CREATE TABLE, used to create new tables
  2. INSERT, used to add new rows of data to tables
  3. UPDATE, used to change data in rows of a table
  4. SELECT, used to read data that is in the rows of a table

The SQL commands of INSERT, UPDATE and SELECT you will use the most.

The DataBase Iinterface (DBI) library

Connecting to a database server in Perl is very easy thanks to the DBI library. It supports many connection types an provides a common API for all connections. VeSql (Very Easy SQL) is a simple GUI program that is an excelent example of how to use DBI to connect to a MySQL or a PostgreSQL server.

For a simple introduction to Perl DBI check out this tutorial on MySQL with Perl DBI.

The steps for database connection are

  1. Connect to the server, providing hostname, database name and login information
  2. Post a query or execute a database command (repeat as often as needed)
  3. Do something with the data if you did a select
  4. Disconnect from the server
use DBI;
my $Dbi = DBI->connect("dbi:mysql:ourdatabase:localhost", "username", "password");
my $Ref = $Dbi->prepare("SELECT username, phonenumber FROM ourtable LIMIT 1");
$Ref->execute;
my $Row = $Ref->fetchrow_hashref();
print "user = ".$Row->{'username'}." and phone number is ".$Row->{'phonenumber'}."\n";
$Ref->finish;
$Dbi->disconnect();

The above example uses mysql for the DBI driver to talk to a MySQL database. If your database is PostgreSQL you need to use the pg driver.

Download a working program VeSql as an example of this works. In VeSql the PerlQt code populates a QTable by reading the result returned from making a query to the server. It uses a foreach loop and the key name of the field to populate the table.

this->{Socket} = DBI->connect("dbi:".$DbiDbType.":".$DbName.":".$Host,$UserName,$Password);
$SqlRef = this->{Socket}->prepare($SqlCommand);
$SqlRef->execute;
ClearTable();
 
$ThisTableRow = 1;
while($row = $SqlRef->fetchrow_hashref())
{
    $ThisTableColumn = 1;
    if(ResultsTable->numRows() < $ThisTableRow)
    {
        ResultsTable->setNumRows($ThisTableRow);    
    }
    foreach $key (keys %$row)
    {
        if($ThisTableRow == 1)
        {                    
            if(ResultsTable->numCols() < $ThisTableColumn)
            {
                ResultsTable->setNumCols($ThisTableColumn);
            }
            ResultsTable->horizontalHeader()->setLabel($ThisTableColumn - 1, $key, 50);
        }
        ResultsTable->setText($ThisTableRow - 1, $ThisTableColumn - 1, $row->{$key});
        $ThisTableColumn++;
    }
    $ThisTableRow++;
}
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License