DYNAMIC_PREPARE is a Boolean-valued connection property for enabling dynamic SQL prepared statements:
If DYNAMIC_PREPARE is set to "true," every invocation of Connection.prepareStatement during a session attempts to return a precompiled statement in a PreparedStatement object.
In this case, when a PreparedStatement is executed, the statement it contains is already precompiled in the database, with place holders for dynamically assigned values, and the statement needs only to be executed.
If DYNAMIC_PREPARE is set to "false" for a connection, the PreparedStatement object returned by Connection.prepareStatement does not contain a precompiled statement.
In this case, each time a PreparedStatement is executed, the dynamic SQL statement it contains must be sent to the database to be both compiled and executed.
The default value for DYNAMIC_PREPARE is "true."
In the following example, DYNAMIC_PREPARE is set to "false" to disable precompilation of dynamic SQL statements. In the example, props is a Properties object for specifying connection properties.
...
props.put("DYNAMIC_PREPARE", "false")
Connection conn = DriverManager.getConnection(url, props);
When DYNAMIC_PREPARE is set to "true," note that:
Not all dynamic statements can be precompiled under the prepare command. The SQL-92 standard places some restrictions on the statements that can be used with the prepare command, and individual database vendors may have their own constraints.
If the database generates an error because it is unable to precompile and save a statement sent to it through Connection.prepareStatement, jConnect traps the error and returns a PreparedStatement object containing an uncompiled dynamic SQL statement. Each time the PreparedStatement object is executed, the statement is re-sent to the database to be compiled and executed.
A precompiled statement resides in memory in the database and persists either to the end of a session or until its PreparedStatement object is explicitly closed. Garbage collection on a PreparedStatement object does not remove the prepared statement from the database.
As a general rule, you should explicitly close every PreparedStatement object after its last use to prevent prepared statements from accumulating in server memory during a session and slowing performance.
