SQL in a Nutshell, 2nd Edition [Electronic resources] نسخه متنی

5.7 Bound Parameters

One of the easiest ways to
optimize a slow database application that
executes many similar SQL statements is to parameterize the most
commonly used SQL statements. Parameterization is a way of reusing a
SQL statement by writing it with placeholders for frequently changing
values. This provides two major benefits: the potential for reduced
server roundtrips and better processing efficiency on the database
server, since the server doesn't need to parse,
plan, and optimize the execution of frequently used SQL statements on
every execution.

Parametrized statements are useful in
using string or binary values within SQL statements that contain
unfriendly characters, such as quote marks or terminal NULL
characters that indicate the end of a string. Usage in this way
provides security benefits to a database application where user input
is used within statements. Not binding a parameter coming from an end
user could allow clever modification of the statement in a way that
may divulge secret information from the database.

5.7.1 ADO.NET Bound Parameters

The following C# code fragment executes
a SQL INSERT statement that adds new sales to
the sales table in the pubs database. The INSERT
statement is parameterized to provide better performance, since the
statement object needs to be parsed only once on the server.

// Create a Command object for the SQL statement
Statement statement = connection.CreateCommand( );
statement.CommandText =  
"INSERT INTO SALES(stor_id, 
ord_num, 
ord_date, 
qty, 
payterms, 
title_id) " +
"VALUES(@stor_id, @ord_num, @ord_date, @qty, @payterms, @title_id)";
//Prepare the statement on the server
statement.Prepare( );
// Declare parameters that will be bound
{Odbc|OleDb|Sql}Parameter stor_id, ord_num, ord_date, 
qty, payterms, title_id;
stor_id = statement.Parameters.Add( "@stor_id", DbType.String );
ord_num = statement.Parameters.Add( "@ord_num", DbType.String );
ord_date = statement.Parameters.Add( "@ord_date", DbType.DateTime);
qty = statement.Parameters.Add( "@qty", DbType.Int16 );
payterms = statement.Parameters.Add( "@payterms", DbType.String );
title_id = statement.Parameters.Add( "@title_id", DbType.String );
while( GetNextSale(stor_id, ord_num, ord_date, qty, payterms, title_id) )
{
// Execute the statement
int result = statement.ExecuteNonQuery( );
if( result != 1 )
{
// If result isn't 1, then the insert failed.
System.Console.WriteLine( "The INSERT failed." );
break;
}
}

5.7.1.1 Use the following steps to execute statements with bound parameters in ADONET:

As done in previous sections, we create an ADO.NET
Command object and assign a SQL statement to it.
The difference for using bound parameters within the statement is in
the VALUES clause of the
INSERT statement. Contained within the
VALUES clause are six named placeholders for the
parameters that will be later bound to the Command
object. In ADO.NET the placeholders begin with the @ symbol and are
followed by an identifier that is unique amongst all placeholders in
the statement.

Statement statement = connection.CreateCommand( );
statement.CommandText =  
"INSERT INTO SALES(stor_id, 
ord_num, 
ord_date, 
qty, 
payterms, 
title_id) " +
"VALUES(@stor_id, @ord_num, @ord_date, @qty, @payterms, @title_id)";

Invoke the Prepare method on the
Command object to prepare the SQL statement for
execution. This notifies the database layer that the
Command object will be executed with bound
parameters.

statement.Prepare( );

Declare the parameter objects using the parameter type that matches
the Command object:
OdbcParameter, OleDbParameter,
or SqlParameter. After declaring the parameters,
create parameter objects by invoking the Add
method of the Command object's
Parameter collection and assign the return value
to the Parameter object. The first argument to the
Add method is the name of the placeholder that the
parameter will map to during execution. In the example below, the
stor_id Parameter object will
map to the first item in the VALUES clause,
which has the placeholder named @stor_id. The
second argument in the Add method is the column
type that the placeholder maps to on the server. Look to Table 5-8 for a list of frequently used types.

{Odbc|OleDb|Sql}Parameter stor_id, ord_num, ord_date, 
qty, payterms, title_id;
stor_id = statement.Parameters.Add( "@stor_id", DbType.String );
ord_num = statement.Parameters.Add( "@ord_num", DbType.String );
ord_date = statement.Parameters.Add( "@ord_date", DbType.DateTime);
qty = statement.Parameters.Add( "@qty", DbType.Int16 );
payterms = statement.Parameters.Add( "@payterms", DbType.String );
title_id = statement.Parameters.Add( "@title_id", DbType.String );

In this example, the Parameter objects are
assigned a value by the user-defined GetNextSale
function call, which could be implemented like this:

static bool GetNextSale(SqlParameter stor_id, 
SqlParameter ord_num, 
SqlParameter ord_date, 
SqlParameter qty,
SqlParameter payterms, 
SqlParameter title_id)
{
// Omitted is the code that would
// Read a sale record from a file, or user input, etc.
// If there are no more sale records, return false.
if( !more_records ) return false;
// Assign values to the parameter objects
stor_id.Value  = 1234;
ord_num.Value  = "ABCD.123";
ord_date.Value = new DateTime(2003,2,24);
qty.Value      = 50;
payterms.Value = "Net 60";
title_id.Value = "SD2043";
return true;
}

Notice that the parameters are assigned a value by assigning directly
to the Value property on the
Parameter object. The stor_id
parameter object is assigned a value of 1234, whereas
ord_date is assigned a C# ADO.NET
DateTime object. The function returns false if
there are no more sales to insert into the table; otherwise the
function returns true.

Combined with a while loop, the program will
continue to insert new sales into the database until the
GetNextSale function runs out of new records to
process.

while(GetNextSale(stor_id, ord_num, ord_date, qty, payterms, title_id) )
{

Invoking the ExecuteNonQuery method on the
Command object executes the
INSERT statement, with the bound parameter
values replaced for their corresponding placeholders. The
ExecuteNonQuery method returns the number of rows
affected, which will be 1 in this case on a successful single-row
insert. This return value is used in error handling and the
application will exit if the statement should ever fail to execute.

    // Execute the statement
int result = statement.ExecuteNonQuery( );
if( result != 1 )
{
// If result isn't 1, then the insert failed.
System.Console.WriteLine( "The INSERT failed." );
break;
}

5.7.2 Binding Parameters with JDBC

The following Java code fragment
executes a SQL INSERT statement that adds new
sales to the sales table in the
pubs database. The
INSERT statement is parameterized to provide
better performance.

// Create a Command object for the SQL statement
PreparedStatement statement = connection.prepareStatement(
"INSERT INTO SALES(stor_id, 
ord_num, 
ord_date, 
qty, 
payterms, 
title_id) " +
"VALUES(?, ?, ?, ?, ?, ?)" );
while( getNextSale(statement) )
{
// Execute the statement
int result = statement.executeUpdate( );
if( result != 1 )
{
// If result isn't 1, then the insert failed.
System.out.println( "The INSERT failed." );
break;
}
}

5.7.2.1 Use the following steps to execute statements with bound parameters in JDBC:

Create a JDBC PreparedStatement object and pass
the parameterized SQL statement into its constructor. The difference
for using bound parameters within the statement is in the
VALUES clause of the INSERT
statement. Contained within the VALUES clause
are six placeholders (the question marks) for the parameters that
will later be bound to the PreparedStatement
object.

PreparedStatement statement = connection.prepareStatement(
"INSERT INTO SALES(stor_id, 
ord_num, 
ord_date, 
qty, 
payterms, 
title_id) " +
"VALUES(?, ?, ?, ?, ?, ?)" );

In this example, the parameters are assigned a value by the
user-defined getNextSale function call, which
could be implemented like this:

static boolean getNextSale( PreparedStatement statement ) 
throws SQLException
{
// Omitted is the code that would
// Read a sale record from a file, or user input, etc.
// If there are no more sale records, return false.
if( !more_records ) return false;
statement.setString(1, "1234");
statement.setString(2, "ABCD.123");
statement.setDate(3, new java.sql.Date(2003, 2, 24));
statement.setInt(4,50);
statement.setString(5, "Net 60");
statement.setString(6, "SD2043");
return true;
}

Each binding position is referenced by its ordinal position in the
SQL statement, with the first position starting at 1. The values are
assigned to placeholders using the set methods
found on the PreparedStatement object. Table 5-9 contains a list of frequently used
set methods.

The function returns false if there are no more sales to insert into
the table; otherwise the function returns true.

Combined with a while loop, the program will
continue to insert new sales into the database until the
getNextSale function runs out of new records to
process.

while( getNextSale(statement) )
{

Invoking the executeUpdate method on the
PreparedStatement object executes the
INSERT statement, with the bound parameter
values replaced for their corresponding placeholders. The
executeUpdate method returns the number of rows
affected, which will be 1 in this case on a successful single-row
insert. This return value is used in error handling and the
application will exit if the statement should ever fail to execute.

    // Execute the statement
int result = statement.executeUpdate( );
if( result != 1 )
{
// If result isn't 1, then the insert failed.
System.out.println( "The INSERT failed." );
break;
}
}