Chemistry Reference
In-Depth Information
my $sql = "Select smiles,cas from nci.structure where gnova.
matches(smiles,'c1ccccc1C(=O)NC') limit 20";
my $sth = $dbh->prepare($sql);
my $rv = $sth->execute;
while (my @row = $sth->fetchrow_array()) {
print join "\t",@row;
print "\n";
}
In this code snippet, the SQL statement is prepared and executed and each
row is fetched into an array and then printed. It is also possible to fetch all
rows at one time, for example, using the following code snippet.
my $sth = $dbh->prepare($sql);
my $rv = $sth->execute;
my $data = $sth->fetchall_arrayref();
while ( my $row = shift(@$data) ) {
print join "\t",@$row;
print "\n";
}
After the SQL statement is executed, an array reference is returned using
fetchall _ arraryref
. The individual rows from this array are then
printed. Some care needs to be taken when using
fetchall _ arrayref
when a large number of rows are returned. In that case, not all rows may
be returned and the function will need to be called again until all rows
are returned. The documentation for the DBI perl module discusses this
more fully.
1
In this final example, the use of bind variables is illustrated.
my $sth = $dbh->prepare($sql);
my $rv = $sth->execute;
$sth->bind_columns(\$smiles, \$cas);
while ($sth->fetchrow_array()) {
print "$smiles\t$cas\n";
}
The
bind _ columns
function requires as many perl variables as there
are columns in the
select
statement. The names used here are indicative
of the columns selected, making the code more understandable. The use
of
bind _ columns
is also very efficient.
12.4 Web Applications
Writing a Web application to help such users search or update a database
is more than simply offering a text box for them to type in SQL state-
ments. The focus of section is not to show how a full Web application can
be developed that uses SQL and an RDBMS server. There are some useful
SQL functions that can benefit any Web application.
Search WWH ::
Custom Search