om mamma-tips: January 2011

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:

You can prepare the SQL once then execute many times with different parameters thus saving the prepare parsing.
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.

Below is a perl subroutine to escape latex special characters. This is useful for printing SMILES notation of the compounds.

sub latex_escape

{

my $paragraph = shift;

# Replace a \ with $\backslash$

# This is made more complicated because the dollars will be escaped

# by the subsequent replacement. Easiest to add \backslash

# now and then add the dollars

$paragraph =~ s/\\/\\backslash/g;

# Must be done after escape of \ since this command adds latex escapes

# Replace characters that can be escaped

$paragraph =~ s/([\$\#&%_{}])/\\$1/g;

# Replace ^ characters with \^{} so that $^F works okay

$paragraph =~ s/(\^)/\\$1\{\}/g;

# Replace tilde (~) with \texttt{\~{}}

$paragraph =~ s/~/\\texttt\{\\~\{\}\}/g;

# Now add the dollars around each \backslash

$paragraph =~ s/(\\backslash)/\$$1\$/g;

return $paragraph;

}

Credit for perl subroutine: http://www.mail-archive.com/templates@template-toolkit.org/msg07971.html

Here is list of Latex special characters (Source: http://happymutant.com/latex/latex2.html)

special characters

Since certain characters are used in LaTeX commands (e.g., the backslash and curly braces), if you want to actually print these characters in your document, there are special commands that tell LaTeX to print these characters (not to treat them as part of a command). Here are some of those characters, along with the commands to print them:

character	command
\	$\backslash$
$	\$
%	\%
^	\^
&	\&
_	\_
~	\~
#	\
{	$\{$
}	$\}$
Â£	\pounds

om mamma-tips

Wednesday, January 19, 2011

R (igraph)

Friday, January 14, 2011

Rsync remote PC

Perl DBI and MySQL

Simple prepare/execute

Obtaining metadata from the result-set

Using parameters

Thursday, January 13, 2011

Perl function for Latex Escape Characters

special characters