Connection Reference
Database parameters and supported database interfaces

Description

For those DBMSs that support bind variables, PowerBuilder can bind input parameters to a compiled SQL statement by default. The DisableBind parameter allows you to specify whether you want to disable this binding.

When you set DisableBind to 1 to disable the binding, PowerBuilder replaces the input variable with the value entered by the application user or specified in code.

Applies to

ADO.NET

ASE, SYC SAP Adaptive Server Enterprise

INF Informix

IN9 Informix

ODBC (if driver and back-end DBMS support this feature)

OLE DB

O90 Oracle9i

O10 Oracle 10g

ORA Oracle (for 11g and later)

SNC SQL Native Client for Microsoft SQL Server

MSOLEDBSQL Microsoft OLE DB Driver for SQL Server

Syntax

DisableBind=value

Default value

DisableBind=1 for ADO.NET, ASE, SYC, SNC, and OLE DB, DisableBind=0 for other interfaces

Usage

Bind variables

In a SQL statement, a bind variable is a placeholder for a column value. By default, PowerBuilder 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, ASE, SYC, SNC, and OLE DB), PowerBuilder 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

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 PowerBuilder. (For more about using cached statements, see the description of the SQLCache parameter.)

Performance improvements

For SQL Anywhere and Oracle databases, bind variables improve performance by allowing PowerBuilder 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:

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 section called “Working in the DataWindow painter” in Users 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.

Examples

To specify that PowerBuilder should disable the binding of input parameters to a compiled SQL statement:

See also

SQLCache