More on Client and Web Interfaces to RDBMS - Design and Use of Relational Databases in Chemistry - page 141

have methods to access data on an RDBMS server. Some Web applica-
tions, such as phpPgAdmin are specifically designed to interact with
an RDBMS. When a new client program is needed, most computer
languages offer a library that allows interaction with a server RDBMS
using SQL. Chapter 5 introduced some simple examples in several lan-
guages. In those examples, a constant SQL string was used. In an actual
client program, the SQL necessary to access the RDBMS will often need
to be different, depending on input from the user. One obvious way to
accomplish this is to use standard string operations to build a string
from various SQL fragments, substituting the user-supplied values
where necessary. This is entirely appropriate in many situations, but
there are more efficient ways to use SQL and the various SQL modules
and packages.
12.3.1 Placeholders in SQL Statements
Consider the following example using Perl to insert data into a table.
my $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$host", $username,
$password);
while (($id, $ic50, $ed50) = &get_data()) {
my $sql = "Insert Into test_assay (id, ic50, ed50)
Values ($id, $ic50, $ed50)";
my $sth = $dbh->prepare($sql);
my $rv = $sth->execute;
}
The get _ data function is not detailed here, but would return three val-
ues, perhaps from user input, a file of data, or an instrument. The detail
to notice in this example is that the call to $dbh->prepare is made once
for every set of data values. The DBI prepare function is relatively inef-
ficient. There is a more efficient way to insert multiple rows. The following
code uses placeholders in the SQL statement.
my $sql = "Insert Into test_assay (id, ic50, ed50) Values (?,?,?)";
my $sth = $dbh->prepare($sql);
while (($id, $ic50, $ed50) = &get_data()) {
last if ($id < 1);
my $rv = $sth->execute($id, $ic50, $ed50);
}
In this example, the prepare function is executed only once. The SQL
statement passed to the prepare function contains placeholders to rep-
resent values that will be made available once the statement is actually
executed. The placeholder is simply a question mark. The arguments to
the execute function provide three new values during each execution of
the loop. This runs faster than the previous example.