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 DataWindow Designer.
ODBC (if driver and back-end DBMS support this feature)
OLE DB
O84 Oracle 8.x and Oracle8i
O90 Oracle9i
O10 Oracle 10g
Block = blocking_factor
Parameter |
Description |
|---|---|
blocking_factor |
The number of rows you want the DataWindow object to fetch from the database at one time. The blocking factor can be a number from 1 to 1000, inclusive. To turn off block fetching, set Block to 1. |
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 value is the following, up to a maximum of 32K per column: Block = 1000
If you specified that the DataWindow object should retrieve only as many rows as needed from the database (Retrieve.AsNeeded property), the Block default value is the following, up to a maximum of 32K per column: Block = 100
|
OLE DB |
DataWindow Designer sets the blocking factor to 1 |
Oracle |
DataWindow Designer sets the blocking factor to 300 rows. |
Using the default blocking factor
You should not have to set a non-default value for Block.
In most cases, the default blocking factor used by DataWindow Designer should
meet your needs.
Requirements for ODBC data sources To use the Block DbParameter 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 Adaptive Server® Anywhere ODBC driver that comes with DataWindow Designer 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 DataWindow Designer 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 PBODB105 initialization file
If DataWindow Designer 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.
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.
To set the blocking factor for DataWindow objects to 50 rows:
Database profile Type 50 in the Retrieve
Blocking Factor box on the Transaction tab in the Database Profile
Setup dialog box:
Application Type the following in code:
SQLCA.DbParameter = "Block=50"
