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

T The tec h in ique of usi ng placeholders i in prepared SQL statements is com-
mon in java as well. The following code snippets show examples in java.
PreparedStatement st = con.prepareStatement("Insert Into test_assay
(id,ic50,ed50) Values (?,?,?)");
while ( true ) {
data_values = get_data();
if ( data_values[0] < 1 ) break;
st.setInt (1, (int) data_values[0]);
st.setDouble(2, data_values[1]);
st.setDouble(3, data_values[2]);
st.executeUpdate();
}
In this code snippet, the connection would already have been established
and stored in the variable con . The con.prepareStatement method
recognizes the use of the placeholder. The st.setInt and st.setDouble
methods are more specific than in the perl example, requiring the integer
or double data type.
The final language example shows how php supports SQL placeholders.
$sql = "Insert Into test_assay (id,ic50,ed50) Values ($1,$2,$3)";
$stmt = pg_prepare($dbconn, "test_assay_insert", $sql);
while ( $data_values = get_data() ) {
if ( $data_value[0] < 1 ) break;
$rv = pg_execute($dbconn, "test_assay_insert", $data_values);
}
The placeholders here are $1 , $2 , and $3 instead of question mark.
Another difference using php is the use of a statement name, here test _
assay _ insert . This is used to uniquely identify the statement being
prepared. The same name is used again when that statement is to be exe-
cuted. Despite these differences, the principle is the same. The SQL state-
ment is prepared once using placeholders and executed as many times as
necessary to insert all data.
Placeholders are commonly used to insert or update tables using SQL.
It is not possible to use a placeholder at any place in an SQL statement.
For example, it is not possible to use a placeholder to represent a column
or table name. It is only possible to use a placeholder in an SQL statement
where a value to be inserted or updated would be used.
12.3.2 Bind Values in SQL Statements
When a client program selects data from an RDBMS table using SQL, there
are several methods that can be used. The following Perl code illustrates
some of these methods.