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 InfoMaker. 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

ASE SAP Adaptive Server Enterprise

AutoCommit

Connect DB at Startup

Connect to Default Profile

Keep Connection Open

Lock

Read Only

Shared Database Profiles

SQL Terminator Character

Use Extended Attributes

DIR SAP Sybase DirectConnect

AutoCommit

Connect DB at Startup

Connect to Default Profile

Keep Connection Open

Lock

Read Only

Shared Database Profiles

SQL Terminator Character

Use Extended Attributes

Informix

AutoCommit

Connect DB at Startup

Connect to Default Profile

Keep Connection Open

Lock

Read Only

Shared Database Profiles

SQL Terminator Character

Use Extended Attributes

JDB JDBC

AutoCommit

Connect DB at Startup

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 DB at Startup

Connect to Default Profile

Keep Connection Open

Lock

Read Only

Shared Database Profiles

SQL Terminator Character

Use Extended Attributes

OLE DB

AutoCommit

Connect DB at Startup

Connect to Default Profile

Keep Connection Open

Lock

Read Only

Shared Database Profiles

SQL Terminator Character

Use Extended Attributes

O90 Oracle9i

O10 Oracle 10g

ORA Oracle 11g/12c

Connect DB at Startup

Connect to Default Profile

Keep Connection Open

Read Only

Shared Database Profiles

SQL Terminator Character

Use Extended Attributes

SNC

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 DB at Startup

Connect to Default Profile

Keep Connection Open

Lock

Read Only

Shared Database Profiles

SQL Terminator Character

Use Extended Attributes


AutoCommit

database preference

Description

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

When AutoCommit is set to False (the default), InfoMaker issues SQL statements inside the scope of a transaction. When AutoCommit is set to True, InfoMaker 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.

Controls

ADO.NET

ASE, SYC SAP Adaptive Server Enterprise

DIR SAP Sybase 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 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

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, the DataWindow server 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.

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.

Connect DB at Startup

database preference

Description

When you are working in the InfoMaker development environment, Connect DB at Startup controls whether InfoMaker connects to the database when you open a painter requiring a connection (the default) or automatically when you start InfoMaker.

InfoMaker only

The Connect DB at Startup preference is available in the Database Preferences property sheet only in InfoMaker. It has no effect in InfoMaker applications at runtime or in PowerBuilder.

Controls

All database interfaces

In the development environment

In the Database painter, select or clear the Connect DB at Startup check box in the Database Preferences property sheet as follows:

  • Select the check box

    The next time you start InfoMaker, it automatically connects to the database at startup and stays connected throughout the session until you exit.

  • Clear the check box

    (Default) The next time you start InfoMaker, it connects to the database only when you open one of the following painters requiring a connection: Database, Report, Form, Query, or Data Pipeline. It does not connect to the database automatically at startup.

Default

The Connect DB at Startup check box in the Database Preferences property sheet is cleared by default.

Usage

Clearing the Connect DB at Startup check box (the default) in InfoMaker to connect to the database only when a painter requires it can save you money if you are accessing a database that charges for connect time.

Connect to Default Profile

database preference

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.

Controls

All database interfaces

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

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

database preference

Description

This preference takes effect in the PowerBuilder development environment. The setting of Keep Connection Open has no effect in InfoMaker because the Connect DB at Startup preference controls when InfoMaker connects to the database. (For information, see Connect DB at Startup.)

Applies to

All database interfaces

Default

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

Lock

database preference

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.

InfoMaker 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.

Controls

ASE, SYC SAP Adaptive Server Enterprise

DIR SAP Sybase 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

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

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:

  • 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.

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.

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.

Read Only

database preference

Description

Read Only specifies whether InfoMaker 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 InfoMaker updates the extended attribute system tables and other tables in your database when you make changes.

If you select the Read Only check box, InfoMaker 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 Form or Report painters when the Read Only check box is selected.

Controls

All database interfaces

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

    InfoMaker 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 Form or Report painters when the Read Only check box is selected.

  • Clear the check box

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

Default

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 Form or Report painters.

  • Therefore, you can use only SELECT and Retrieve statements in the Form or Report painters.

See also

Use Extended Attributes

Shared Database Profiles

database preference

Description

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

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

Controls

All database interfaces

In the development environment

In the Database painter, supply the path name of the InfoMaker 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

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\IM.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\IM.INI

SQL Terminator Character

database preference

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.

Controls

All database interfaces

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

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

database preference

Description

Controls access to the extended attribute system tables by specifying whether you want InfoMaker 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 InfoMaker.

Controls

All database interfaces

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 Form and Report painters use the appropriate default values for extended attributes (such as headers, labels, and text color). If the extended attribute system tables already exist, InfoMaker does not use them when you create a new report or form.

Default

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, InfoMaker 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