Dynamic SQL allows a client application to execute SQL statements containing variables with values that are determined at run time. It is primarily useful for precompiler support. A client application prepares a dynamic SQL statement by associating a SQL statement containing placeholders with an identifier and sending the statement to an Open ServerConnect application so that the statement becomes a prepared statement.
When a client application is ready to execute a prepared statement, it defines values to substitute for the SQL statement placeholders and sends a command to execute the statement. These values become the command input parameters. After the statement executes the desired number of times, the client application deallocates the statement.
Dynamic SQL permits a client application to act interactively, passing different information at different times to the Open ServerConnect application, as it gets that information from the user. The Open ServerConnect application can then fill in the missing pieces in the SQL query with the data the user provides.
In Open ServerConnect, this process must occur as a long-running transaction. When a client issues a dynamic SQL command, Open ServerConnect indicates a TDS_DYNAMIC event through TDINFPGM or TDGETREQ. The server application retrieves the type of command through a TDYNAMIC call and then satisfies the client request.
Table 2-11 defines the valid Open ServerConnect responses for various client requests.
Client action |
Open ServerConnect application response |
|---|---|
Client issues a prepare command (TD_PREPARE) |
1. Get operation type (TDS_GET) (TDYNAMIC) 2. Get statement ID length (TDS_GET) (TDYNAMIC) 3. Retrieve statement ID (TDS_GET) (TDYNAMIC) 4. Retrieve statement length (TDS_GET) (TDYNAMIC) 5. Retrieve statement (TDS_GET) (TDYNAMIC) 6. Send statement ID length (TDS_SET) (TDYNAMIC) 7. Send statement ID (TDS_SET) (TDYNAMIC) 8.Acknowledge request (TDS_SET) (TDYNAMIC) 9. Send DONE packet (TDS_ENDREPLY) (TDSNDDON) 10. Return a language, RPC, dynamic, or cursor request type (TDGETREQ) |
Client requests an input parameter description (CS_DESCRIBE_INPUT) |
1. Get operation type (TDS_GET) (TDYNAMIC) 2. Get statement ID length (TDS_GET) (TDYNAMIC) 3. Retrieve statement ID (TDS_GET) (TDYNAMIC) 4. Send statement ID length (TDS_SET) (TDYNAMIC) 5. Send statement ID (TDS_SET) (TDYNAMIC) 6. Acknowledge request (TDS_SET) (TDYNAMIC) 7. Describe input parameters (TDESCRIB) 8. Send DONE packet (TDS_ENDREPLY) (TDSNDDON) 9. Get next request (TDGETREQ) |
Client requests an output parameter description (CS_DESCRIBE_OUTPUT) |
1. Get operation type (TDS_GET) (TDYNAMIC) 2. Get statement ID length (TDS_GET) (TDYNAMIC) 3. Retrieve statement ID (TDS_GET) (TDYNAMIC 4. Describe output column(s) (TDESCRIB) 5. Send statement ID length (TDS_SET) (TDYNAMIC) 6. Send statement ID (TDS_SET) (TDYNAMIC) 7. Acknowledge request (TDS_SET) (TDYNAMIC) 8. Send DONE packet (TDS_ENDREPLY) (TDSNDDON) 9. Get next request (TDGETREQ) |
Client issues an execute request (TD_EXECUTE) |
1. Get operation type (TDS_SET) (TDYNAMIC) 2. Get statement ID length (TDS_GET) (TDYNAMIC) 3. Retrieve statement ID (TDS_GET) (TDYNAMIC) 4. Retrieve number of parameters (TDNUMPRM) 5. Retrieve input parameter values (TDRCVPRM) 6. Send statement ID length (TDS_SET) (TDYNAMIC) 7. Send statement ID (TDS_SET) (TDYNAMIC) 8. Acknowledge request (TDS_SET) (TDYNAMIC) [application logic: execute client request] 9. Send result rows (TDSNDROW) 10. Send DONE packet (TDS_ENDREPLY) (TDSNDDON) 11. Return a language, RPC, dynamic, or cursor request type (TEGETREQ) |
Client issues an execute immediate request (TD_EXECUTE_IMMEDIATE) |
1. Get operation type (TDS_GET) (TDYNAMIC) 2. Get statement ID (should be zero) (TDS_GET) (TDYNAMIC) 3. Retrieve statement length (TDS_GET) (TDYNAMIC) 4. Retrieve statement (TDS_GET) (TDYNAMIC) 5. Acknowledge request (TDS_SET) (TDYNAMIC) [application logic: execute client request] 6. Send result rows (TDSNDROW) 7. Send DONE packet (TDS_ENDREPLY) (TDSNDDON) 8.Return a language, RPC, dynamic, or cursor request type (TDGETREQ) |
Client issues a deallocation request (TD_DEALLOC) |
1. Get operation type (TDS_GET) (TDYNAMIC) 2. Get statement ID length (TDS_GET) (TDYNAMIC) 3. Retrieve statement ID (TDS_GET) (TDYNAMIC) 4. Send statement ID length (TDS_SET) (TDYNAMIC) 5. Send statement ID (TDS_SET) (TDYNAMIC) 6. Acknowledge request (TDS_SET) (TDYNAMIC) 7. Send DONE packet (TDS_ENDREPLY) (TDSNDDON) 8. Return a language, RPC, dynamic, or cursor request type (TDGETREQ) |
