Connection Reference
Database Preferences

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.

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

MSOLEDBSQL Microsoft OLE DB Driver for 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

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:

For instructions, see the section called “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:

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.

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:

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.

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:

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.

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:

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:

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.

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.

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

MSOLEDBSQL Microsoft OLE DB Driver for 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.

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 the section called “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 PBODB.ini file. For example:

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

The value in the PBODB.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:

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

Example 2

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

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.

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:

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:

See also

Use Extended Attributes

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 the section called “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 the section called “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

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 the section called “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:

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.

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:

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:

See also

Read Only