Perl DBI and MySQL

Simple prepare/execute

The most simple way of obtaining all the rows in a table and displaying them is like this:

my $dbh = dbi->connect_db();               # connect
my $sql = qq{select * from table};      # the query to execute
my $sth = $dbh->prepare($sql);          # prepare the query
$sth->execute();                        # execute the query
my @row;

my $col1='';

my $col2='';
while (@row = $sth->fetchrow_array) {  # retrieve one row at a time

$col1=$row[0];

$col2=$row[1];
    print "$col1---$col2---\n";
}

Obtaining metadata from the result-set

ODBC can describe the result-set for you listing the number of columns and the names of the columns. You can obtain this information with the statement attributes NUM_OF_FIELDS, NAME, NAME_uc, NAME_lc, NAME_hash, NAME_lc_hash and NAME_uc_HASH.

Assuming you have the table 'test' with columns 'a' (integer), 'b' (char(10)) and 'c' (float):

my $sth = $dbh->prepare(select * from test);
$sth->{NUM_OF_FIELDS} returns 3.  
$sth->{NAME}->[0] returns 'a' although it could return 'A'
$sth->{NAME_uc}->[0] returns 'A'.  
$sth->{NAME_uc}->[0] returns 'a'.

Using parameters

The main reasons for using parameters/placeholder are:

1. You can prepare the SQL once then execute many times with different parameters thus saving the prepare parsing.
2. With bound parameters you don't need to bother about quoting issues.

Simple example:

my @row;                                # row data
my $dbh = connect_db();              # connect
my $var = "";
my $sql = qq{select * from table where column = ?};
                                        # the query to execute with parameter
my $sth = $dbh->prepare($sql);         # prepare the query
$sth->execute(($var));                 # execute the query with parameter
while (@row = $sth->fetchrow_array) { # retrieve one row
    print join(", ", @row), "\n";
}
$var = "another value to search for";
$sth->execute(($var));                 # execute the query (no need to re-prepare)
while (@row = $sth->fetchrow_array) { # retrieve one row
    print join(", ", @row), "\n";
}

You can use multiple parameter markers e.g.
$sql = q/select * from table where col1 = ? and col2 = ?/;
$sth->execute(($param1, $param2));

Note You cannot use parameter markers in place of column names e.g. select ? from table or select * from table where ? = 'A'.

DBI also provides a method to bind the returned column data to perl variables using the bind_col and bind_columns methods:
Going back to the first example we had:

my $dbh = DBI->connect();             # connect
my $sql = qq/select * from table/;     # the query to execute
my $sth = $dbh->prepare($sql);         # prepare the query
$sth->execute();                       # execute the query
my @row;
while (@row = $sth->fetchrow_array) { # retrieve one row
    print join(", ", @row), "\n";
}

which can be rewritten to use bound columns as follows:

my $dbh = DBI->connect();             # connect
my $sql = qq/select * from table/;    # the query to execute
my $sth = $dbh->prepare($sql);        # prepare the query
$sth->execute();                      # execute the query
my ($col1, $col2, $col3);
# Note columns start at 1 (not 0).
$sth->bind_col(1, \$col1);
$sth->bind_col(2, \$col2);
$sth->bind_col(3, \$col3);
while ($sth->fetch) { # retrieve one row
    print "$col1, $col2, $col3\n";
}

Binding columns and using the fetch method is usually faster than using methods like fetchrow_array. As with bind_param you can specify the type the column is bound as.

$sth->bind_col(1, \$col1, {TYPE => SQL_INTEGER});
$sth->bind_col(2, \$col2, {TYPE => SQL_CHAR});$sth->bind_col(3, \$col3, {TYPE => SQL_FLOAT});

Source: http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_2.html

sub connect_db 

{
 my $dbh = DBI->connect('DBI:mysql::localhost:3306','user','password',{
                                    RaiseError => 1,
                                    PrintError => 1,
                                    })|| warn "Database connection not made: $DBI::errstr";
    return($dbh);
}

Here we prepare the SQL, execute it, then use the fetchrow_array method to return an array of column values for each row. Repeatedly calling the fetchrow_array method until fetchrow_array returns undef, returns all the rows in the result-set.