5.7 Bound Parameters | SQL in a Nutshell (In a Nutshell (OReilly))

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.

Since there is a slight cost involved in setting up a parameterized statement, it's good practice to only parameterize statements that will be executed at least three times prior to the statement closing. If a statement is only executed once prior to freeing the statement, the costs of preparing the statement and binding the parameters could result in a performance net loss to the application.

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 {OdbcOleDbSql}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.
```
 {OdbcOleDbSql}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;     } 
```

Table 5-8. Frequently used parameter object types

DbType object type	Description
`AnsiString`	Variable-length, non-Unicode character string between 1 and 8,000 characters.
`AnsiStringFixedLength`	Fixed-length, non-Unicode character string.
`Binary`	Variable-length binary string between 1 and 8,000 bytes.
`Boolean`	Represents a true or false value.
`Byte`	Unsigned integer value ranging 0 to 255.
`Currency`	Currency value ranging from -2 ⁶³ to 2 ⁶³ -1.
`DateTime`	Date and time value.
`Decimal`	Numeric value ranging from 10 ^-28 to 7.9 10 ²⁸ with about 28 significant digits.
`Double`	Floating point type ranging from 5.0 10 ^-324 to 1.7 10 ³⁰⁸ with about 15 digits.
`Int{16,32,64}`	Signed integer; the number suffix indicates the number of bits of precision. For example, `Int32` is a 32-bit integer.
`Single`	Floating point type ranging from 5.0 10 ^-324 to 1.7 10 ³⁰⁸ , with about 15 digits.
`String`	Variable-length, Unicode character string.
`StringFixedLength`	Fixed-length, Unicode character string.
`UInt{16,32,64}`	Unsigned integer; the number suffix indicates the number of bits of precision. For example, `UInt32` is a 32-bit unsigned integer.

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;     } }

Table 5-9. Frequently used PreparedStatement set methods

Method name	Description
`setBlob(int` `i` `, Blob` `value` `)`	Sets the placeholder at position `i` to the `Blob` contained in `value` .
`setBoolean(int` `i` `, boolean` `value` `)`	Sets the placeholder at position `i` to the `boolean` contained in `value` .
`setByte(int` `i` `, byte` `value` `)`	Sets the placeholder at position `i` to the `byte` contained in `value` .
`setClob(int` `i` `, Clob` `value` `)`	Sets the placeholder at position `i` to the `Clob` contained in `value` .
`setDate(int` `i` `, Date` `value` `[, Calendar` `cal` `])`	Sets the placeholder at position `i` to the `Date` contained in `value` . If `cal` is provided, it'll be used to interpret the `Date` `value` .
`setDouble(int` `i` `, double` `value` `)`	Sets the placeholder at position `i` to the `double` contained in `value` .
`setFloat(int` `i` `, float` `value` `)`	Sets the placeholder at position `i` to the `float` contained in `value` .
`setInt(int` `i` `, int` `value` `)`	Sets the placeholder at position `i` to the `int` contained in `value` .
`setLong(int` `i` `, long` `value` `)`	Sets the placeholder at position `i` to the `long` contained in `value` .
`setNull(int` `i` `, int` `v` `)`	Sets the placeholder at position `i` to a NULL value when `v` is true.
`setString(int` `i` `, String` `value` `)`	Sets the placeholder at position `i` to the `String` contained in `value` .
`setTimestamp(int` `i` `, Timestamp` `value` `[,Calendar` `cal` `])`	Sets the placeholder at position `i` to the `Timestamp` contained in `value` . If `cal` is provided, it'll be used to interpret the `Timestamp` `value` .