Database preferences and supported database interfaces

The following table lists each supported database interface and the connection-related database preferences you can use with that interface in PowerBuilder. The preferences listed in the table pertain to the database connection, and not to the behavior of the Database painter itself.

Database interface

Database preferences

ADO.NET

AutoCommit

Connect to Default Profile

Keep Connection Open

Read Only

Shared Database Profiles

SQL Terminator Character

Use Extended Attributes

ASE SAP Adaptive Server Enterprise

AutoCommit

Connect to Default Profile

Keep Connection Open

Lock

Read Only

Shared Database Profiles

SQL Terminator Character

Use Extended Attributes

DIR SAP DirectConnect

AutoCommit

Connect to Default Profile

Keep Connection Open

Lock

Read Only

Shared Database Profiles

SQL Terminator Character

Use Extended Attributes

IN9 and I10 Informix

AutoCommit

Connect to Default Profile

Keep Connection Open

Lock

Read Only

Shared Database Profiles

SQL Terminator Character

Use Extended Attributes

JDBC

AutoCommit

Connect to Default Profile

Keep Connection Open

Lock

Read Only

Shared Database Profiles

SQL Terminator Character

Use Extended Attributes

ODBC

Using AutoCommit and Lock with ODBC

The AutoCommit and Lock database preferences are supported by the ODBC interface only if both the ODBC driver you are using and the back-end DBMS support the feature.

AutoCommit

Connect to Default Profile

Keep Connection Open

Lock

Read Only

Shared Database Profiles

SQL Terminator Character

Use Extended Attributes

OLE DB

AutoCommit

Connect to Default Profile

Keep Connection Open

Read Only

Shared Database Profiles

SQL Terminator Character

Use Extended Attributes

O90 Oracle9i

O10 Oracle 10g

ORA Oracle 11g/12c

Connect to Default Profile

Keep Connection Open

Read Only

Shared Database Profiles

SQL Terminator Character

Use Extended Attributes

SNC SQL Native Client

AutoCommit

Connect to Default Profile

Keep Connection Open

Lock

Read Only

Shared Database Profiles

SQL Terminator Character

Use Extended Attributes

SYC SAP Adaptive Server Enterprise

AutoCommit

Connect to Default Profile

Keep Connection Open

Lock

Read Only

Shared Database Profiles

SQL Terminator Character

Use Extended Attributes


AutoCommit

Description

For those DBMSs and database interfaces that support it, AutoCommit controls whether PowerBuilder issues SQL statements outside or inside the scope of a transaction.

When AutoCommit is set to False (the default), PowerBuilder issues SQL statements inside the scope of a transaction. When AutoCommit is set to True, PowerBuilder issues SQL statements outside the scope of a transaction.

When to specify AutoCommit

In the development environment, you must set AutoCommit before connecting to the database. AutoCommit takes effect only when the database connection occurs. Changes to AutoCommit after the connection occurs have no effect on the current connection.

In code, you can reset the value of AutoCommit at any time. This lets you override the initial setting if necessary.

Applies to

ADO.NET

ASE and SYC SAP Adaptive Server Enterprise

DIR SAP DirectConnect

I10 Informix

IN9 Informix

JDB JDBC

ODBC (if driver and back-end DBMS support this feature)

OLE DB

SNC SQL Native Client for Microsoft SQL Server

In an application

For those DBMSs and database interfaces that support it, you can set AutoCommit in a script as a property of the Transaction object. The following syntax assumes you are using the default Transaction object SQLCA (but you can also define your own Transaction object):

SQLCA.AutoCommit=value

Parameter

Description

value

Specifies whether PowerBuilder issues SQL statements outside or inside the scope of a transaction. Values are:

  • True

    PowerBuilder issues SQL statements outside the scope of a transaction. The statements are not part of a logical unit of work (LUW). If the SQL statement is successful, the DBMS updates the database immediately as if a COMMIT statement had been issued.

  • False

    (Default) PowerBuilder issues SQL statements inside the scope of a transaction. PowerBuilder issues a BEGIN TRANSACTION statement at the start of the connection and issues another BEGIN TRANSACTION statement after each COMMIT or ROLLBACK statement is issued.


In the development environment

Select or clear the AutoCommit Mode check box on the Connection tab in the Database Profile Setup dialog box, as follows:

  • Select the check box.

    Sets AutoCommit to true for this connection.

  • Clear the check box.

    (Default) Sets AutoCommit to false for this connection.

For instructions, see Setting Additional Connection Parameters in Connecting to Your Database.

Default value

AutoCommit=False

Usage

Transactions

A transaction is one or more SQL statements that form a logical unit of work (LUW). Within a transaction, all SQL statements must succeed or fail as one logical entity. Changes are made to the database only if all statements in the transaction succeed and a COMMIT is issued. If one or more statements fail, you must issue a ROLLBACK to undo the changes. This ensures the integrity and security of data in your database.

Executing SQL DDL statements

Some DBMSs require you to execute certain SQL statements outside the scope of a transaction. For example, when connected to a SQL Server 7 or earlier database, you must execute SQL Data Definition Language (DDL) statements such as CREATE TABLE and DROP TABLE outside a transaction. There are two reasons for this:

  • It ensures that the structure of your database cannot change during a transaction.

  • It improves database performance, because DDL statements are costly operations to recover.

Therefore, to execute DDL statements or stored procedures containing DDL statements in a SQL Server database, you must set AutoCommit to true to issue the DDL statements outside the scope of a transaction. You should, however, set AutoCommit back to false immediately after executing the DDL statements.

When you change the value of AutoCommit from false to true, PowerBuilder issues a COMMIT statement by default.

Caution

When you set AutoCommit to true, you cannot roll back database changes. Therefore, use care when changing the setting of AutoCommit.

Using EXECUTE IMMEDIATE

When AutoCommit is set to True, you can use the EXECUTE IMMEDIATE dynamic SQL statement to issue BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION, and other SQL statements to control your own transaction processing. If you use the EXECUTE IMMEDIATE dynamic SQL statement to issue BEGIN TRANSACTION, you must use the EXECUTE IMMEDIATE dynamic SQL statement to issue a corresponding COMMIT TRANSACTION or ROLLBACK TRANSACTION.

For information about using the EXECUTE IMMEDIATE statement, see the section called “Dynamic SQL Format 1” in PowerScript Reference.

DirectConnect interface

As part of the Connect process, the DIR interface automatically issues TransactionMode=short to override the access service default configuration. It then issues begin transaction at connect time and after every Commit and Rollback whenever AutoCommit=False. Most developers should start their connections with AutoCommit=True, switch to False only when the application demands transaction processing, and then switch back to AutoCommit=True after the transaction is committed or rolled back.

Examples

To set AutoCommit to true and issue SQL statements outside the scope of a transaction:

  • Development environment

    Select the AutoCommit Mode check box on the Connection tab in the Database Profile Setup dialog box.

  • Application

    Type the following in a script:

    SQLCA.AutoCommit=True

Using the examples in code

If you specify AutoCommit Mode in your database profile, the correct syntax displays on the Preview tab in the Database Profile Setup dialog box. You can copy the syntax from the Preview tab into your code.

Connect to Default Profile

Description

Connect to Default Profile controls whether the Database painter establishes a connection to a database using a default profile when the painter is invoked. If not selected, the Database painter opens without establishing a connection to a database.

Applies to

All database interfaces

In an application

You cannot set the Connect to Default Profile database preference in code.

In the development environment

In the Database painter, select or clear the Connect to Default Profile check box in the Database Preferences dialog box as follows:

  • Select the check box

    (Default) The next time you invoke the Database painter, it automatically connects to the default database profile.

  • Clear the check box

    The next time you invoke the Database painter, it does not automatically connect to the default database profile.

Default value

The Connect to Default Profile check box in the Database Preferences dialog box is selected by default.

Usage

Connect to Default Profile allows you to open the Database painter without establishing a connection to a database. Consequently, you can perform all database-related tasks, including defining a database profile and connecting to a database, in the Database painter. However, you might want to continue to define profiles and/or connect to a database using the Database Profile since opening the Database painter uses more system resources.

Keep Connection Open

Description

By default, PowerBuilder opens a database connection the first time you open a painter requiring a connection, and stays connected throughout the session until you exit.

When you connect to a database in the PowerBuilder development environment without using a database profile, you can set the Keep Connection Open database preference to specify when PowerBuilder closes the database connection.

Keep Connection Open applies only when connecting to a database in the PowerBuilder development environment without using a database profile. The setting of Keep Connection Open has no effect when you use a database profile to connect in PowerBuilder.

Applies to

All database interfaces (only in the development environment)

In an application

You cannot set the Keep Connection Open database preference in code.

In the development environment

In the Database painter, select or clear the Keep Connection Open check box in the Database Preferences dialog box as follows:

  • Select the check box

    (Default) Stays connected to the database throughout your PowerBuilder session and closes the connection when you exit.

  • Clear the check box

    Opens the database connection when a painter requires it and closes the connection when you close a painter or finish compiling a script

Default value

The Keep Connection Open check box in the Database Preferences dialog box is selected by default.

Usage

Requirements for using Keep Connection Open

To use the Keep Connection Open database preference, both of the following must be true:

  • Working in the development environment

    You must be working in the development environment.

  • Using default connection information

    PowerBuilder must use the most recently used connection information in the Windows registry to connect to the database. Keep Connection Open has no effect when you select a database profile to connect to the database.

What happens

If you meet both of these requirements, clearing the Keep Connection Open check box opens a database connection only when you are working in a painter that requires a connection, and closes the connection at other times. This can save you money if you are accessing a database that charges for connect time.

Lock

Description

For those DBMSs and database interfaces that support the use of lock values and isolation levels, the Lock preference sets the isolation level to use when connecting to the database. 

In multiuser databases, transactions initiated by different users can overlap. If these transactions access common data in the database, they can overwrite each other or collide.

To prevent concurrent transactions from interfering with each other and compromising the integrity of your database, certain DBMSs allow you to set the isolation level when you connect to the database. Isolation levels are defined by your DBMS, and specify the degree to which operations in one transaction are visible to operations in a concurrent transaction. Isolation levels determine how your DBMS isolates or locks data from other processes while it is being accessed.

PowerBuilder uses the Lock preference to allow you to set various database lock options. Each lock value corresponds to an isolation level defined by your DBMS.

When to specify the Lock value

You must set the Lock value before you connect to the database. The Lock value takes effect only when the database connection occurs. Changes to the Lock value after the connection occurs have no effect on the current connection.

Applies to

ASE and SYC SAP Adaptive Server Enterprise

DIR SAP DirectConnect

I10 Informix

IN9 Informix

JDB JDBC

ODBC (if driver and back-end DBMS support this feature)

OLE DB

SNC SQL Native Client for Microsoft SQL Server

In an application

For those DBMSs and database interfaces that support it, you can set the Lock value in code as a property of the Transaction object. The following syntax assumes you are using the default Transaction object, SQLCA, but you can also use a user-defined Transaction object:

SQLCA.Lock ='value'

where value is the lock value you want to set.

Lock values

The following table lists the lock values and corresponding isolation levels for each database interface that supports locking. You set the lock value in code, and the isolation level in a database profile.

For more about the isolation levels that your DBMS supports, see your DBMS documentation.

Database interface

Lock values

Isolation levels

IN9 and I10 Informix

(for OnLine databases only)

Dirty Read

Committed Read

Cursor Stability

Repeatable Read

Dirty Read

Committed Read

Cursor Stability

Repeatable Read

JDB JDBC

RU

RC

RR

TS

TN

Read Uncommitted

Read Committed

Repeatable Read

Serializable Transactions

Transaction None

ODBC

RU

RC

RR

TS

TV

Read Uncommitted

Read Committed

Repeatable Read

Serializable Transactions

Transaction Versioning

OLE DB

RU

RC

RR

TS

TC

Read Uncommitted

Read Committed

Repeatable Read

Serializable Transactions (default)

Chaos

SNC SQL Native Client

RU

RC

RR

SS

TS

TC

Read Uncommitted

Read Committed (default)

Repeatable Read

Snapshot

Serializable Transactions

Chaos

SAP Adaptive Server Enterprise

0

1

3

Read Uncommitted

Read Committed (default)

Serializable Transactions

SAP DirectConnect

0

1

2

3

Read Uncommitted

Read Committed (default)

Repeatable Read

Serializable Transactions


In the development environment

Select the isolation level you want from the Isolation Level drop-down list on the Connection tab in the Database Profile Setup dialog box.

For instructions, see Setting Additional Connection Parameters in Connecting to Your Database.

Default value

The default lock value depends on how your database is configured. For information, see your DBMS documentation.

Usage

ODBC

The TV (Transaction Versioning) setting does not apply to SQL Anywhere databases.

OLE DB

The default value for Lock in the discontinued MSS native interface and the SNC interface for Microsoft SQL Server 2005 is Read Committed, but for OLE DB the default is Serializable Transactions. If you want to connect to SQL Server 2000 using OLE DB, you can override the default value by specifying a value for Lock in the PBODB170.INI file. For example:

[Microsoft SQL Server]
...
LOCK='RC'
...

The value in the PBODB170.INI file is used if you do not change the default in the database profile or set the Lock parameter of the Transaction object in code.

SAP Adaptive Server Enterprise

SAP Adaptive Server Enterprise supports the following lock values, which correspond to SQL Server isolation levels:

  • 0 -- Read Uncommitted (dirty reads)

    Isolation level 0 prevents other transactions from changing data that an uncommitted transaction has already modified (through SQL statements such as UPDATE).

    Other transactions cannot modify the data until the transaction commits, but they can still read the uncommitted data (perform dirty reads). Isolation level 0 prohibits retrieval locks on tables or pages.

    Isolation level 0 is valid only for SAP System 10 or higher databases.

  • 1 -- Read Committed

    (Default) Isolation level 1 prevents dirty reads by issuing shared locks on tables or pages.

    A dirty read occurs when one transaction modifies a table row and a second transaction reads that row before the first transaction commits the change. If the first transaction rolls back the change, the information read by the second transaction becomes invalid.

  • 3 -- Serializable Transactions (HOLDLOCK behavior)

    Isolation level 3 prevents dirty reads, nonrepeatable reads, and phantoms for the duration of a transaction.

    A nonrepeatable read occurs when one transaction reads a row and then a second transaction modifies that row. If the second transaction commits the change, subsequent reads by the first transaction produce different results than the original read.

    A phantom occurs when one transaction reads a set of rows that satisfy a search condition, and then a second transaction modifies that data through a SQL INSERT, UPDATE, or DELETE statement. Subsequent reads by the first transaction using the same search conditions produce a different set of rows than the original read.

Dynamically controlling the isolation level

PowerBuilder makes a second connection to implement either of the following while connected to an SAP Adaptive Server Enterprise database:

  • The Retrieve.AsNeeded property to specify that a DataWindow should retrieve only as many rows as needed from the database

  • A SELECTBLOB embedded SQL statement to select a single blob column in a specified table row

The lock value you set before making the first Adaptive Server Enterprise connection is automatically inherited by the second connection, and cannot be changed for the second connection.

However, you can dynamically control the isolation level for the first (original) Adaptive Server Enterprise connection in an application by coding the following PowerScript embedded SQL statement, where n is 0, 1, or 3 for the isolation level you want to set for the first connection:

EXECUTE IMMEDIATE "set transaction isolation level n"

For example, the following PowerScript embedded SQL code specifies isolation level 0 (dirty read behavior) for the second connection, and isolation level 1 (read committed behavior) for the first connection:

// Isolation level inherited by second connection
SQLCA.Lock="0"
CONNECT USING SQLCA;
// Override lock value 0 for first connection only
EXECUTE IMMEDIATE "set transaction isolation level 1";

Use in three-tier applications

If an ASE connection on an application server, is used by a component with a specified isolation level and cached by the server, it is released back into the connection pool with the isolation level set by the component. If that connection is then used by another component that has no specified isolation level, the isolation level may not be the default level expected by the component (1). This could result in the occurrence of deadlocks. To avoid this, always set the SQLCA.Lock property explicitly in application server components.

Examples

Example 1

To set the Lock value to RC (Read Committed) for a SQL Anywhere database:

  • Development environment

    Select Read Committed from the Isolation Level drop-down list in the Database Profile Setup dialog box.

  • Application

    Type the following in a script:

    SQLCA.Lock="RC"

Example 2

To set the Lock value to 3 (Serializable Transactions) for an SAP Adaptive Server Enterprise database:

  • Development environment

    Select Serializable Transactions from the Isolation Level drop-down list in the Database Profile Setup dialog box.

  • Application

    Type the following in a script:

    SQLCA.Lock="3"

Using the examples in code

If you specify Isolation Level in your database profile, the syntax displays on the Preview tab in the Database Profile Setup dialog box. You can copy the syntax from the Preview tab into your code.

Read Only

Description

Read Only specifies whether PowerBuilder should update the extended attribute system tables and any other tables in your database. The extended attribute system tables (also known as the extended catalog) consist of five tables that contain default extended attribute information for your database.

The Read Only setting determines whether you can modify (update) the tables in your database. By default, the Read Only check box is cleared in the Database Preferences dialog box. This means that PowerBuilder updates the extended attribute system tables and other tables in your database when you make changes.

If you select the Read Only check box, PowerBuilder does not update the extended attribute system tables or any other tables in your database. You cannot modify (update) information in the extended attribute system tables or any other database tables from the DataWindow painter when the Read Only check box is selected.

Applies to

All database interfaces

In an application

You cannot set the Read Only database preference in code.

In the development environment

In the Database painter, select or clear the Read Only check box in the Database Preferences dialog box as follows:

  • Select the check box

    PowerBuilder does not update the extended attribute system tables or any other tables in your database. You cannot modify (update) information in the extended attribute system tables or any other database tables from the DataWindow painter when the Read Only check box is selected.

  • Clear the check box

    (Default) PowerBuilder updates the extended attribute system tables and any other tables in your database when you modify them.

Default value

The Read Only check box in the Database Preferences dialog box is cleared by default.

Usage

If you select the Read Only check box in the Database Preferences dialog box, you cannot modify information in any tables from the DataWindow painter.

Therefore, you can use only:

  • SELECT and Retrieve statements in the DataWindow painter

  • SELECT statements in embedded SQL

See also

Use Extended Attributes

Shared Database Profiles

Description

Specifies the path name of the PowerBuilder initialization file containing the database profiles you want to share.

For instructions on sharing database profiles in the PowerBuilder development environment, see Managing Database Connections in Connecting to Your Database.

Applies to

All database interfaces

In an application

You cannot set the Shared Database Profiles database preference in code.

In the development environment

In the Database painter, supply the path name of the PowerBuilder initialization file containing shared profiles in the Shared Database Profiles box in the Database Preferences dialog box. You can type the path name or click the Browse button to display it.

For instructions, see Setting Additional Connection Parameters in Connecting to Your Database.

Default value

The Shared Database Profiles box in the Database Preferences dialog box is blank (unspecified) by default.

Examples

To share database profiles contained in the file I:\SHARE\PB.INI on the Windows platform, type or browse to the following in the Shared Database Profiles box in the Database Preferences dialog box:

I:\SHARE\PB.INI

SQL Terminator Character

Description

Specifies the SQL statement terminator character used by the Database painter's Interactive SQL (ISQL) view.

The default terminator character for the ISQL view is a semicolon (;). If a semicolon conflicts with the terminator character used by your DBMS syntax, you can change the painter's terminator character by specifying a different character in the SQL Terminator Character box in the Database Preferences dialog box. A good choice for a terminator character is the backquote (`) character.

Changing the terminator character is recommended when you are using the ISQL view to create or execute stored procedures, triggers, and SQL scripts.

Applies to

All database interfaces

In an application

You cannot set the SQL Terminator Character database preference in code.

In the development environment

In the Database Preferences dialog box in the Database painter, type the terminator character you want to use in the SQL Terminator Character box. For instructions, see Setting Additional Connection Parameters in Connecting to Your Database.

Default value

The default SQL Terminator Character value in the Database Preferences dialog box is a semicolon (;).

Usage

The following are typical situations that might require you to change the default SQL Terminator Character value:

  • Creating stored procedures and triggers

    If you are creating stored procedures and triggers in the ISQL view, change the painter's terminator character to one that you do not expect to use in the stored procedure or trigger syntax for your DBMS, such as the backquote (`) character.

    After you finish using the stored procedure, you can change the terminator character back to a semicolon (;). If you prefer, you can continue to use the new terminator character as long as it does not conflict with any stored procedure or trigger syntax you plan to use.

  • Executing SQL scripts

    If you plan to execute any SQL scripts in the ISQL view, make sure the terminator character used in the script agrees with the terminator character currently set in the view.

Examples

To change the SQL statement terminator character in the ISQL view to a backquote (`), type a backquote in the SQL Terminator Character box in the Database Preferences dialog box.

Use Extended Attributes

Description

Controls access to the extended attribute system tables by specifying whether you want PowerBuilder to create these tables. The extended attribute system tables (also known as the extended catalog) consist of five tables that contain default extended attribute information for your database.

By default, the Use Extended Attributes check box is selected in the Database Preferences dialog box. This setting creates the extended attribute system tables the first time you connect to a database using PowerBuilder.

Applies to

All database interfaces

In an application

You cannot set the Use Extended Attributes database preference in code.

In the development environment

In the Database painter, select or clear the Use Extended Attributes check box in the Database Preferences dialog box as follows:

  • Select the check box

    (Default) Creates the extended attribute system tables when connecting to the database for the first time.

  • Clear the check box

    Does not create the extended attribute system tables if they do not exist. Instead, the DataWindow painter use the appropriate default values for extended attributes (such as headers, labels, and text color). If the extended attribute system tables already exist, PowerBuilder does not use them when you create a new DataWindow object.

Default value

The Use Extended Attributes check box in the Database Preferences dialog box is selected by default.

Usage

If you clear the Use Extended Attributes check box in the Database Preferences dialog box, PowerBuilder does not do any of the following:

  • Create the extended attribute system tables

  • Insert, update, or delete rows in the extended attribute system tables

  • Select information (such as header names) from the extended attribute system tables

  • Execute statements that reference the extended attribute system tables

See also

Read Only