For those DBMSs that support bind variables, DataWindow Designer binds input parameters to a compiled SQL statement by default. The DisableBind parameter allows you to specify whether you want to disable this default binding.
When you set DisableBind to 1 to disable the binding, DataWindow Designer replaces the input variable with the value entered by the application user or specified in code.
ADO.NET
Informix 9
ODBC (if driver and back-end DBMS support this feature)
OLE DB
O84 Oracle 8.x and Oracle8i
O90 Oracle9i
O10 Oracle 10g
DisableBind = value
DisableBind = 1 for ADO.NET and OLE DB, DisableBind=0 for other interfaces
Bind variables In a SQL statement, a bind variable is a placeholder for a column value. By default, DataWindow Designer associates (binds) data from a variable defined in your application to the bind variable each time the SQL statement executes.
Using bind variables in SQL statements For example, the following SQL statement retrieves those rows in the Books table about books written by Hemingway:
SELECT * FROM books WHERE author = "Hemingway"
Suppose that you want to execute this statement to get information about books written by other authors. Instead of compiling and executing a new statement for each author, you can define a bind variable that represents the author’s name. The user then supplies the author’s actual name when the application executes. By using bind variables, you ensure that the statement is compiled only once and executed repeatedly with new values supplied by the user.
If your database supports bind variables and DisableBind is set to 0 to enable binding (the default for all database interfaces except ADO.NET and OLE DB), DataWindow Designer generates the statement with parameter markers (:bind_param) and passes the actual parameter value at execution time. For example:
SELECT * FROM books WHERE author = :bind_param
Using the DataDirect ODBC driver
The DataDirect wire protocol driver for Sybase Adaptive Server
Enterprise does not support the SQL describe
parameter function that is necessary to support the DisableBind
feature. If you use this driver, setting DisableBind=0 has
no effect.
Bind variables and cached statements Using bind variables in conjunction with cached statements can improve the performance of most applications, depending on the application. In general, applications that perform a large amount of transaction processing benefit the most from using bind variables and cached statements.
In order to use cached statements, make sure that DisableBind is set to 0. This enables the binding of input variables to SQL statements in DataWindow Designer. (For more about using cached statements, see the description of the SQLCache parameter.)
Performance improvements For Adaptive Server Anywhere and Oracle databases, bind variables improve performance by allowing DataWindow Designer to insert and modify strings that exceed 255 characters.
Bind variables and default column values When DisableBind is set to 0 to enable the use of bind variables, the DataWindow painter does both of the following to get maximum performance improvement from using bind variables when you add rows to a DataWindow object:
Generates a SQL INSERT statement that includes all columns (except identity and SQL Server timestamp)
Reuses this SQL INSERT statement for each row you add to the DataWindow object
For example, if a table named Order_T contains three columns named Order_ID, Order_Date, and Customer_ID, the DataWindow painter generates the following SQL INSERT statement when DisableBind is set to 0 (default binding enabled):
INSERT INTO Order_T(Order_ID, Order_Date, Customer_ID)
VALUES(:bind_param1, :bind_param2, :bind_param3)
If one of these columns is null, the DataWindow painter sets a null value indicator for this column parameter and executes the statement. This behavior is important to understand if you want your back-end DBMS to set a default value for any columns in your DataWindow object.
To illustrate, suppose that your application users do not enter a value for the Order_Date column because they expect the back-end DBMS to set this column to a default value of TODAY. Then, they retrieve the row and find that a null value has been set for Order_Date instead of its default value. This happens because the SQL INSERT statement generated by the DataWindow painter specified a null value indicator, so the DBMS set the column value to null instead of to its default value as expected.
Setting a default column value when binding is enabled If you are using bind variables (DisableBind set to 0) and want the back-end DBMS to set a column to its default value when your application user does not explicitly enter a value in a new row, you should set an initial value for the DataWindow object column that mirrors the DBMS default value for this column.
In the DataWindow painter, you can set or modify a column’s initial value in the Column Specifications dialog box.
For more about the Column Specifications dialog box, see the User’s Guide.
Setting a default column value when binding is disabled If you are not using bind variables (DisableBind set to 1) and want the back-end DBMS to set a column to its default value when your application user does not explicitly enter a value in a new row, you do not need to set an initial value for the DataWindow column.
This is because with bind variables disabled, the DataWindow painter generates a SQL INSERT statement for each row added to the DataWindow object. If a column does not contain an explicit value, it is not included in the SQL INSERT statement.
Using the Order_T table example, if your application user enters 123 as the value for the Order_ID column and A-123 as the value for the Customer_ID column, the DataWindow painter generates the following SQL INSERT statement when DisableBind is set to 1 (binding disabled):
INSERT INTO Order_T(Order_ID, Customer_ID)
VALUES(123, 'A-123')
Your back-end DBMS would then set the Order_Date column to its default value as expected, since a value for Order_Date is not explicitly set in the SQL INSERT statement generated by the DataWindow painter.
To specify that DataWindow Designer should disable the binding of input parameters to a compiled SQL statement:
Database profile Select the Disable Bind check box on the Transaction or System tab in the Database Profile Setup dialog box.
Application Type the following in code:
SQLCA.DbParameter = "DisableBind=1"
