The use of parameters allows for the creation of flexible SQL queries that are evaluated at run time. A parameter is a variable in an SQL statement. For example, suppose a Parts table has columns named PartID, Description, and Price. To add a part without parameters would require constructing an SQL statement such as:
INSERT INTO Parts (PartID, Description, Price)
VALUES (2100, 'Drive shaft', 50.00)
Although this statement inserts a new order, it is not a good solution for an order entry application because the values to insert cannot be hard-coded in the application. Another alternative is to construct the SQL statement at run time, using the values to be inserted. However, this also is not a good solution, due to the complexity of constructing statements at run time. The best solution is to replace the elements of the VALUES clause with question marks (?), or parameter markers:
INSERT INTO Parts (PartID, Description, Price)
VALUES (?, ?, ?)
The parameter markers are then bound to application variables. To add a new row, the application has only to set the values of the variables and execute the statement. The driver then retrieves the current values of the variables and sends them to the data source. If the statement will be executed multiple times, the application can make the process even more efficient by preparing the statement.
The statement just shown might be hard-coded in an order entry application to insert a new row. However, parameter markers are not limited to vertical applications. For any application, they ease the difficulty of constructing SQL statements at run time by avoiding conversions to and from text. For example, the part ID just shown is most likely stored in the application as an integer. If the SQL statement is constructed without parameter markers, the application must convert the part ID to text and the data source must convert it back to an integer. By using a parameter marker, the application can send the part ID to the driver as an integer. The driver can usually send it to the data source as an integer, thereby saving two conversions. For long data values, this is critical. The text forms of such values often exceed the allowable length of an SQL statement.
Parameters are legal only in certain places in SQL statements. For example, they are not allowed in the select list (the list of columns to be returned by a SELECT statement), nor are they allowed in both sides of a binary operator, such as the equals sign (=). In the last case, it would be impossible to determine the parameter type. In general, parameters are legal only in Data Manipulation Language (DML) statements, and not in Data Definition Language (DDL) statements.
Each parameter in an SQL statement must be associated, or bound, to a variable in the application before the statement is executed. When the application binds a variable to a parameter, it describes that variable — address, data type, and so on — to the driver. It also describes the parameter itself — SQL data type, precision, and so on. The driver stores this information in the structure maintained for that statement, and uses the information to retrieve the value from the variable when the statement is executed.
Parameters can be bound or rebound at any time before a statement is executed. If a parameter is rebound after a statement is executed, the binding does not apply until the statement is re-executed. To bind a parameter to a different variable, an application simply rebinds the parameter with the new variable. The previous binding is automatically released.
A variable remains bound to a parameter until a different variable is bound to it. All parameters are unbound by calling f90SQLFreeStmt with the SQL_RESET_PARAMS option, or when the statement is released. For this reason, the application must be sure that variables are not freed until after they are unbound.
Because parameter bindings are just information stored in a structure maintained by the driver, they can be set in any order. They are also independent of the SQL statement that is executed. For example, suppose an application binds three parameters and then executes the following SQL statement:
INSERT INTO Parts (PartID, Description, Price)
VALUES (?, ?, ?)
If the application then immediately executes this SQL statement:
SELECT * FROM Orders
WHERE OrderID = ?, OpenDate = ?, Status = ?
on the same statement handle, the parameter bindings for the INSERT statement are used because those are the bindings stored in the statement structure. In most cases, this is a poor programming practice and should be avoided.
The application binds parameters by calling f90SQLBindParameter. f90SQLBindParameter binds one parameter at a time. With it, the application specifies:
The following, is a simplified explanation of how to use the subroutine f90SQLBindParameter. Details about this subroutine can be found in the reference section of this manual. A typical call to subroutine f90SQLBindParameter looks like this:
| call f90SQLBindParameter | (StatementHandle, ParameterNumber, InputOutputType, ValueType, ParameterType, ColumnSize, & DecimalDigits, {ParameterValue,| ParameterValuePtr, BufferLength,} StrLen_or_IndPtr, Ret) |
Where:
StatementHandle is a statement handle.
ParameterNumber is an integer (kind= SQLUSMALLINT_KIND) indicating the parameter number, ordered sequentially in increasing order, starting at 1.
InputOutputType is an integer (kind= SQLSMALLINT_KIND) indicating if the parameter is for input, output or both. f90SQL defines the constants SQL_PARAM_INPUT, SQL_PARAM_OUTPUT and SQL_PARAM_INPUT_OUTPUT that can be used for this parameter.
ValueType is the Fortran data type identifier of the parameter. You should used one of the type identifiers defined in f90SQL (see Table 4.3 in Chapter 4) for this argument.
ParameterType is the SQL data type of the parameter. You should use one of the SQL type identifiers defined in f90SQL (see table 4.2 in Chapter 4) for this argument.
ColumnSize: The column size of numeric data types is defined as the maximum number of digits used by the data type of the column or parameter, or the precision of the data. For character types, this is the length in characters of the data. For binary data types, column size is defined as the length in bytes of the data. For the time, timestamp, and all interval data types, this is the number of characters in the character representation of this data. This argument is an integer of kind SQLUINTEGER_KIND.
DecimalDigits: The decimal digits of decimal and numeric data types are defined as the maximum number of digits to the right of the decimal point, or the scale of the data. For approximate floating point number columns or parameters, the scale is undefined, since the number of digits to the right of the decimal point is not fixed. For datetime or interval data that contains a seconds component, the decimal digits are defined as the number of digits to the right of the decimal point in the seconds component of the data. This argument is an integer of kind SQLSMALLINT_KIND.
ParameterValue is the variable to be bounded to the parameter or a pointer to this variable.
ParameterValuePtr and BufferLength: instead of ParameterValue, the user may want to pass the address of the buffer that stores the parameter value (ParameterValuePtr) and the length of this buffer (BufferLength).
Note: A call to f90SQLBindParameter must pass either ParameterValue or the argument pair ParameterValuePtr, BufferLength.
StrLen_or_IndPtr is a pointer (i.e. the address) to a variable that stores the length/indicator value for the buffer.
Example 5.4 shows a simple use of SQL parameters. The program connects to the BookSales database and allows the user to request a list of publishers located in a given state. Note how instead of building a SQL statement for each state, we built a parameterized query. The state entered by the user is a parameter. This example also shows how the subroutine f90SQLPrepare can be used to speed up a query.
|