DisableBind – PB Docs 2019

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

I10 Informix

IN9 Informix

JDB JDBC

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

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:

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:

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):

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):

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