Capabilities and limitations of dynamic commands

Following is a description of dynamic command capabilities that DirectConnect supports, as well as the limitations that apply.

• You can only prepare use procedure, transfer, and most non-select SQL statements, such as insert, update, and delete statements. For a current list of SQL statements, see the IBM DB2 SQL Reference manual.

• The DB2 access service does not support the from clause in an update statement.

• The DB2 access service supports a maximum of 50 simultaneously-prepared dynamic statements per client connection.

• releases all resources associated with the prepared statement. The client application must also free its connection at this point so that CT-Library synchronizes with the actual state of both DB2 and the DB2 access service.

• The DB2 access service can support both short and long transactions in either allocate on request or allocate on connect mode.

• The first time an application issues a prepare (or declares a cursor), the DB2 access service forces TransactionMode for that connection to long. The Allocate property remains unchanged.

• The DB2 access service supports the execute immediate capability of a dynamic command, but the statement must not return data. (This is an Open Server restriction, not a DB2 access service limitation.) The DB2 access service allows a use procedure statement (as long as the stored procedure does not return data) but not a transfer statement.

• Parameter markers for dynamic commands are supported as follows:

  • In sybase mode, parameter markers within the text can use Sybase conventions, such as @paramname. When necessary, the DB2 access service transforms parameters to the native syntax.

  • In passthrough mode, parameter marker syntax is database-specific, so you must use question marks (?) as parameter markers.

• The DB2 access service also supports long character and binary parameters. Client applications must describe such properties as datatype CS_LONGCHAR or CS_LONGBINARY.

  Consider the following restrictions:

  • DB2 limits the SQL statement for any request (such as a prepare) to a total of 32,765 bytes. The maximum size of both data and null indicators in a row is 32,767 bytes.

  • Open Server Tabular Data Stream (TDS) allows a maximum TextSize property of 32,000 bytes.

For general information about dynamic SQL and dynamic commands, see the Sybase Open Client Embedded SQL Programmer's Guide and the IBM DB2 SQL Reference manual.

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF