A JDBC application can prepare a query that contains dynamic parameters. Each parameter in a 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 and that parameter to the driver. Therefore, the application must supply the following information:
• The data type of the variable that the application wishes to map to the dynamic parameter.
• The SQL data type of the dynamic parameter (the data type that the database system assigned to the parameter marker).
The JDBC Client relies on the binding of parameters to know how to send information to the database system in its native format. The host variable data type and SQL data type are assumed to be the same. For example, setInt() indicates to the driver that the data type of the host variable and the SQL type of the dynamic parameter are both Integer. If the host variable and SQL data type are not the same, the application should use setObject() to specify the application variable data type and the SQL data type. If an application furnishes incorrect parameter binding information to the driver, the results will be unpredictable.
To ensure interoperability, the JDBC Client uses only the parameter binding information provided by the application. Although the OpenAccess SDK SQL engine can publish dynamic parameter information back to a JDBC driver, some DBMSs cannot. For example, both the SQL Server and Oracle query processors can determine that a parameter is an integer. However, the Oracle query processor cannot publish this information back to the JDBC Client.
Note: The SQL data type is determined at prepare time by the OpenAccess SDK SQL engine or third-party SQL engine and does not change for the life of the statement. The SQL data type is not dependent on the data being used by the application. For example, it is not valid to bind the SQL type to SQL_NUMERIC with a precision of 15 and a scale of 5, and then bind it on a later execution to a SQL type of SQL_NUMERIC with a precision of 13 and a scale of 3.
Note: You can implement ParameterMetaData when using the OpenAccess SDK SQL engine or a third-party SQL engine that has implemented support for parameter descriptors. The JDBC Client returns this information when the application requests it, but depending on the database, performance penalties can be incurred. You can tune this feature through the data source service attribute DataSourceDescribeParam. Refer to the OpenAccess SDK Administrator’s Guide for information on service attributes.
