Block (ODBC, OLE DB, Oracle, and SNC) database parameter
Description
For those interfaces that support it, Block specifies the
cursor blocking factor when connecting to a database. The blocking
factor determines the number of rows that a DataWindow object can fetch
from the database at one time.
Using the Block parameter can improve performance when accessing
a database in PowerBuilder.
Controls
-
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 |
Block=<span>blocking</span>_<span>factor</span> |
Parameter |
Description |
---|---|
blocking_factor |
The number of rows you want the DataWindow object to To turn off block fetching, set Block to 1. |
Default
The default value for the Block parameter depends on the DBMS
you are accessing, as summarized in the following table:
DBMS |
Block default value |
||||
---|---|---|---|---|---|
ODBC |
For most DataWindow objects, the Block default
If you specified that the DataWindow object should retrieve only
|
||||
OLE DB |
PowerBuilder sets the blocking factor to |
||||
Oracle |
PowerBuilder sets the blocking factor dynamically |

You should not have to set a non-default value for Block.
In most cases, the default blocking factor used by PowerBuilder should
meet your needs.
Usage
Requirements for ODBC data sources
To use the Block database parameter with an ODBC data source,
your ODBC driver must:
-
Be ODBC Version 2.0
compliant or higher, and -
Support the SQLExtendedFetch API
call
The SQL Anywhere® ODBC
driver that comes with PowerBuilder meets both of these requirements.
For information about whether your ODBC driver
meets these requirements, see the documentation that comes with
your driver.
Determining the Block value for ODBC data sources
PowerBuilder searches the following in this order to determine
the Block value for ODBC data sources:
-
The section for your database profile in the registry or the
value of the Transaction object property (in an application) -
The section for your ODBC driver in the PBODB126
initialization file
If PowerBuilder does not find a Block value in these locations,
it uses the default Block value for the DBMS you are accessing.
Turning off block fetching
To turn off block fetching for an ODBC data source or Oracle
database, set the Block parameter to 1.
OLE DB and Microsoft SQL Server
When you use the OLE DB database interface with a Microsoft SQL Server database and retrieve
data into a DataWindow or use an embedded SQL cursor
in a SELECT statement, server–side cursors
are used to support multiple command execution. If this has a negative
impact on performance, try increasing the size of the Block database parameter
to 500 or more, or adding the following line to the [Microsoft SQL Server] section in the PBODB126.INI file
to turn off server-side cursors:
1 |
ServerCursor='NO' |
Oracle and MaxFetchBuffer
For Oracle, the Block parameter can be used in conjunction
with the MaxFetchBuffer database parameter to improve performance
when the size of a row is very large. The MaxFetchBuffer parameter
has a default value of 5000000 bytes, which is sufficient for most applications.
The size of the actual fetch buffer is the product of the value
of the blocking factor and the size of the row.
If the fetch buffer required by the blocking factor and the
row size is greater than the value of MaxFetchBuffer, the value
of the blocking factor is adjusted so that the buffer is not exceeded.
For example, if block=500 and the row size is 10KB, the
fetch buffer is 5000KB, which equals the default maximum buffer size.
Examples
To set the blocking factor for DataWindow objects to 50
rows:
-
Database profile
Type
50
in the Retrieve
Blocking Factor box on the Transaction page in the Database Profile
Setup dialog box: -
Application
Type the following in code:
1SQLCA.DbParameter="Block=50"