DisableBind database parameter
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.
Controls
-
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 11g
-
SNC SQL Native Client for Microsoft SQL Server
Syntax
|
1 |
DisableBind=<span>value</span> |
|
Parameter |
Description |
|---|---|
|
value |
Specifies whether you want to disable
|
Default
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:
|
1 |
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:
|
1 |
SELECT * FROM books WHERE author=:<span>bind_param</span> |
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:
-
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):
|
1 |
INSERT INTO Order_T(Order_ID, Order_Date, Customer_ID) |
|
1 |
VALUES(:<span>bind_param1</span>, :<span>bind_param2</span>, :<span>bind_param3</span>) |
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 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):
|
1 |
INSERT INTO Order_T(Order_ID, Customer_ID) |
|
1 |
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:
-
Database profile
Select the Disable Bind check box on the Transaction or System
page in the Database Profile Setup dialog box. -
Application
Type the following in code:
1SQLCA.DbParameter="DisableBind=1"