Block (ODBC, OLE DB, Oracle, and SNC) – PB Docs 126

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

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.

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
value is the following, up to a maximum of 32K per column:

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
:

OLE DB

PowerBuilder sets the blocking factor to
1

Oracle

PowerBuilder sets the blocking factor dynamically
if one row size is too large. By default the blocking factor is
300 rows.

note.png 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 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:

  1. The section for your database profile in the registry or the
    value of the Transaction object property (in an application)

  2. 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:

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:

See Also

MaxFetchBuffer


Document get from Powerbuilder help
Thank you for watching.
Was this article helpful?
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x