Connection Reference
Database Parameters

Description

Specifies the version of the ADO.NET data provider that is in use on the client workstation.

Applies to

ADO.NET

Syntax

ADORelease='value'

Default value

1.1.411.0 for Adaptive Server, 9.2.0.401 for Oracle.

Usage

The ADORelease database parameter specifies the version of the ADO.NET database provider used for native connections to a database server using ADO.NET.

For Adaptive Server, specify Sybase.Data.AseClient as the Namespace in the Database Profile Setup dialog box for ADO.NET to display available versions of the SAP ASE ADO.NET Data Provider in the Release drop-down list.

The database provider is supplied in the .NET assemblies Sybase.PowerBuilder.Db.dll (for Adaptive Server 12.5.x and Oracle9i) and Sybase.PowerBuilder.DbExt.dll (for Adaptive Server 15.x and Oracle 10g). You must deploy the appropriate version of this DLL with your application.

For Oracle, specify Oracle.DataAccessClient as the Namespace in the Database Profile Setup dialog box for ADO.NET to display available versions of the Oracle Data provider for .NET (ODP.NET) in the Release drop-down list.

When you select a driver version, the ADO.NET interface attempts to load that driver. If the driver is redirected to a higher version of the driver, the higher driver is loaded, but only the features in the selected driver may be supported. For example with Oracle ODP.NET, if ADORelease is set to 9.2.0.401 but the policy file on your computer redirects the driver to version 10.1.0.301, the ODP.NET 10.1.0.301 driver is loaded. New features in ODP.NET 10.1.0.301 are not supported.

Examples

To specify that your PowerBuilder application accesses an Adaptive Server 15 database using the ASE ADO.NET Data Provider:

Description

Allows you to set your own client driver name for the Oracle database connection.

Applies to

ORA Oracle (for 11g and later)

Syntax

AppDriverName =value

Default value

None.

Usage

The maximum length of the name is 8 characters. You can display the client driver name with the V$SESSION_CONNECT_INFO or GV$SESSION_CONNECT_INFO dynamic performance view queries.

Examples

To specify a client driver name:

Description

If the DBMS supports it, specifies the application name you want to use when connecting to the database in PowerBuilder.

Applies to

ASE, SYC SAP Adaptive Server Enterprise

DIR SAP DirectConnect

SNC SQL Native Client for Microsoft SQL Server

MSOLEDBSQL Microsoft OLE DB Driver for SQL Server

Syntax

AppName='application_name'

Default value

For Adaptive Server and DirectConnect, PowerBuilder sets the CS_APPNAME connection property to PowerBuilder, as follows:

AppName='PowerBuilder'

For SQL Server, there is no default value for AppName.

Usage

Adaptive Server databases

It is useful to specify a different AppName value for each of your Adaptive Server applications. If you are an administrator, you can query the MASTER.DBO.SYSPROCESSES table to determine which applications are running on the database server. The value specified for AppName displays in the program_name column of the MASTER.DBO.SYSPROCESSES table, making it easy to identify the applications.

Examples

Example 1

To set the application name to Test:

Example 2

(Does not apply to DirectConnect) You can set the AppName and Host parameters in a single statement to specify both the application name and the host name. To set the application name to Sales and the host name to Fran:

See also

Host

(applies only to ASE and SYC SAP Adaptive Server Enterprise)

Description

Allows you to perform asynchronous operations on your database in PowerBuilder. If you have coded a RetrieveRow event for a DataWindow object or report, you can cancel the current database retrieval operation or start another (non-database) operation that does not use the same database connection before the current operation completes. You can also switch to another Windows process while the retrieval takes place.

By default, PowerBuilder operates synchronously.

Applies to

ASE, SYC SAP Adaptive Server Enterprise

DIR SAP DirectConnect

I10 Informix

IN9 Informix

JDB JDBC

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

O90 Oracle9i

O10 Oracle 10g

ORA Oracle (for 11g and later)

Syntax

Async=value

Default value

Async=0

Usage

Enabling asynchronous operation in PowerBuilder is useful when you are executing a complex SQL statement that takes several minutes to return results. If the Async parameter is set to 1, you can do either of the following while the SQL statement is executing:

When to set Async

If you are communicating with the database in code, you can reset the Async value at any time before or after the Transaction object has connected to the database.

How data is retrieved

When you retrieve data in a DataWindow object or report, the following steps occur in order:

What happens before the first row is retrieved

While the server is compiling and executing the SQL statement and before PowerBuilder retrieves the first row of data, you must have done both of the following to enable asynchronous operation (allowing you to cancel the current operation before it retrieves the first row of data):

What happens after the first row is retrieved

After the first row of data is retrieved and between subsequent row fetches, you must have done only the following to enable asynchronous operation:

Examples

Example 1

To enable asynchronous operation:

Example 2

You can set the Async and DBGetTime parameters in a single statement. DBGetTime specifies the number of seconds you want PowerBuilder to wait for a response from the DBMS when you retrieve rows in a DataWindow object or report. To enable asynchronous operation and set the DBGetTime parameter to 20 seconds:

See also

DBGetTime

Description

Specifies that PowerBuilder bind input parameters in dynamic SQL statements when executing a stored procedure.

Applies to

ADO.NET

O90 Oracle9i

O10 Oracle 10g

SNC SQL Native Client for Microsoft SQL Server

MSOLEDBSQL Microsoft OLE DB Driver for SQL Server

Syntax

BindSPInput=value

Default value

BindSPInput=0

Usage

For SNC, when BindSPInput is set to 0, you can use the same syntax to declare a stored procedure in a script as you can when using the PowerBuilder OLE DB interface. When BindSPInput is set to 1, the SNC interface supports SQL Server large value datatypes as procedure IN/OUT parameters or function return values.

The syntax for declaring a procedure with SNC is:

DECLARE logical_procedure_name PROCEDURE FOR 
   [@rc=]procedure_name
   {@param1=value1 [OUTPUT], @param2=value2 [OUTPUT], ...}
   {USING transaction_object};

[@rc=] indicates that you want to get the procedure's return value.

Use the keyword OUTPUT or OUT to indicate an output parameter if you want to get the output parameter's value.

If BindSPInput=0, value1, value2,... can be either PowerBuilder script variables or literal values. If BindSPInput=1, value1, value2,... must be PowerBuilder script variables. If you specify literal values, the interface returns a runtime error.

When you declare a dynamic SQL statement with a procedure, enter a question mark (?) for each IN/OUT parameter in the statement. Value substitution is positional. For examples, see Dynamic SQL Format 3 and 4 in the section called “Using dynamic SQL” in PowerScript Reference.

For Oracle, set BindSPInput to 1 to ensure that CLOB, NCLOB, and BLOB parameters work correctly as stored procedure parameters.

For ADO.NET:

Examples

Setting BindSPInput

To specify that PowerBuilder should bind parameters in dynamic SQL statements when executing a stored procedure:

Using the ADO.NET SQL Server interface

Consider the following two SQL statement fragments:

create procedure p_1 (@inparm1 TEXT) AS

The preceding statement does not work if BindSPInput is set to 1, because the SQL Server interface does not support Text.

create procedure p_2 (@inparm1 VARCHAR(MAX)) AS

The preceding statement can work with BindSPInput set to 1, because the SQL Server interface does support VARCHAR(MAX).

See also

DisableBind

Description

Specifies that binary data or an ANSI string is to be submitted to or retrieved from a text column with UPDATEBLOB or SELECTBLOB.

Applies to

ASE, SYC SAP Adaptive Server Enterprise

I10 Informix

IN9 Informix

OLE DB

O90 Oracle9i

O10 Oracle 10g

ORA Oracle (for 11g and later)

SNC SQL Native Client for Microsoft SQL Server

MSOLEDBSQL Microsoft OLE DB Driver for SQL Server

Syntax

BinTxtBlob=value

Default value

BinTxtBlob=0

Usage

By default, when you use the UPDATEBLOB and SELECTBLOB SQL statements with a database column with a text datatype (long or clob for Oracle or text for Adaptive Server or OLE DB access to SQL Server), the PowerBuilder blob that is updated or selected is expected to contain a Unicode string. If the blob contains any other kind of data, such as binary data or an ANSI string, set the BinTxtBlob database parameter to 1 before calling SELECTBLOB or UPDATEBLOB. This prevents PowerBuilder or the database server from attempting to perform any conversion to or from Unicode.

Oracle O90, O10, and ORA

The Oracle database interfaces use a Unicode database handle. With the default setting (BinTxtBlob=0) and UPDATEBLOB, they send the data directly to the Oracle server and inform the server that the binary data contains Unicode strings. Any conversion needed is performed by the server. For SELECTBLOB, they get a Unicode string from the server.

When BinTxtBlob is set to 1, the value of the NLS_LANG environment variable determines the binding character set. The ANSI string or binary data is transferred directly to or from the server as in PowerBuilder 9 and previous releases.

To set BinTxtBlob to 1 with the O90 interface, you must use an Oracle 9.2 or later client, or you will receive an error.

OLE DB, SNC, ASE, and SYC

If BinTxtBlob is set to 0, the OLE DB, SNC, ASE, and SYC interfaces perform any necessary conversion. If BinTxtBlob is set to 1, the data is passed to the server without conversion.

Examples

In code, before calling SELECTBLOB or UPDATEBLOB with a PowerBuilder blob that contains ANSI string data or binary data, set the BinTxtBlob parameter to 1:

SQLCA.DBParm="BinTxtBlob=1"

Restore the default setting of 0 if an operation needs to be performed on a blob that contains Unicode string data.

For example, suppose a Unicode string "ABC" stored in client memory as "65 00 66 00 67 00" is updated to the database using UPDATEBLOB. If BinTxtBlob is set to 0, the data is converted to ANSI and stored in the database text column as "65 66 67". If BinTxtBlob is set to 1, no conversion occurs and the data is stored in its original form as "65 00 66 00 67 00".

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.

Applies to

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

OLE DB

O90 Oracle9i

O10 Oracle 10g

ORA Oracle (for 11g and later)

SNC SQL Native Client for Microsoft SQL Server

MSOLEDBSQL Microsoft OLE DB Driver for SQL Server

Syntax

Block=blocking_factor

Default value

The default value for the Block parameter depends on the DBMS you are accessing, as summarized in the following table:

Block=1000

Block=100

Usage

Requirements for ODBC data sources

To use the Block database parameter with an ODBC data source, your ODBC driver must:

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:

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 PBODB.ini file to turn off server-side cursors:

ServerCursor='NO'

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:

See also

MaxFetchBuffer

Description

Specifies the internal blocking factor used by the SAP Sybase Client Library (CT-Lib) interface when declaring a cursor. The blocking factor determines the number of rows fetched from the database at one time when CT-Lib makes a physical request for data.

The Block DBParm parameter applies only to declared cursors and not to DataWindow objects.

Applies to

ASE and SYC SAP Adaptive Server Enterprise

DIR SAP DirectConnect

Syntax

Block=blocking_factor

Default value

Block=100

Examples

Example 1

To set the blocking factor to 1000 rows:

Example 2

The following embedded SQL statements show how to set the blocking factor in code and use it to declare a cursor. These statements set the blocking factor to 1000 rows and declare a cursor that uses this internal blocking factor.

SQLCA.DBParm="Block=1000"
DECLARE dept_cursor CURSOR FOR
      SELECT dept_id, dept_name FROM department
      USING SQLCA;
OPEN dept_cursor;

Description

Specifies whether the OLE DB data provider can cache sensitive authentication information, such as a password, in an internal cache.

Applies to

OLE DB

Syntax

CacheAuthentication='value'

Default value

CacheAuthentication='False'

Examples

To tell the OLE DB data provider to cache authentication information:

See also

DataLink

IntegratedSecurity

Description

Allows PowerBuilder to specify an EAServer connection cache by name. This database parameter applies only when a PowerBuilder custom class user object is deployed as an EAServer component.

Applies to

JDB JDBC

ODBC

O90 Oracle9i

Syntax

CacheName='value'

Default value

None

Usage

When you create a PowerBuilder custom class user object that uses an EAServer connection cache, you must specify a user name, password, server name, and connectivity library. However, if the "enable cache-by-name access" option has been enabled on EAServer, you just need to enter the connection cache name to specify the connection cache.

This parameter cannot be set dynamically. The value set when the connection is made remains in effect until it is disconnected.

Examples

On the EAServer page in the Database Profile Setup dialog box, select the Access Cache By Name check box and enter the EAServer cache name in the Cache Name box. The PowerScript syntax for the CacheName DBParm parameter displays on the Preview page:

SQLCA.DBParm="CacheName='mydbcache'"

Copy the syntax from the Preview page into your script.

See also

GetConnectionOption (obsolete)

ReleaseConnectionOption (obsolete)

UseContextObject (obsolete)

Description

Controls whether the ODBC interface uses call escape syntax for stored procedure calls (the default) or converts the calls to driver-specific native SQL syntax before sending the command to the ODBC driver.

Applies to

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

Syntax

CallEscape='value'

Default value

CallEscape='Yes'

Usage

When to use

Set CallEscape to No if the ODBC driver you are using expects to receive stored procedure calls in native (driver-specific) SQL syntax instead of in call escape syntax.

For information about the stored procedure call syntax your ODBC driver expects, see your vendor's driver documentation.

Level 2 or higher ODBC driver required

To use the CallEscape parameter, your ODBC driver must meet Level 2 or higher API conformance requirements. CallEscape has no effect when you are using an ODBC driver that meets Core or Level 1 API conformance requirements.

Example of stored procedure call escape syntax

The following example shows a call to a stored procedure named sp_test that uses call escape syntax:

{call sp_test(1,1)}

Examples

To convert stored procedure calls to native SQL syntax before sending the command to your ODBC driver:

Description

Specifies the character set you want the SAP Sybase Open Client software to use when connecting to an SAP Adaptive Server Enterprise database or a database accessed through DirectConnect.

Applies to

ASE, SYC SAP Adaptive Server Enterprise

DIR SAP DirectConnect

Syntax

CharSet='character_set'

Default value

None

Usage

When you specify a value for CharSet, PowerBuilder:

Overriding the Locale parameter

If you have previously set a value for the Locale parameter that includes settings for the language and character set you want to use, you can override the character set value by specifying a new value for the CharSet parameter and reconnecting to the database.

Unicode data access

PowerBuilder can access Unicode data in an Adaptive Server Enterprise (ASE) 12.5 or later Unicode database or in Unicode columns in ASE 12.5 or later. PowerBuilder converts between double-byte character set (DBCS) data and Unicode automatically, provided that the CharSet and Language parameters are set with DBCS values (or the Locale parameter is set with DBCS values). For example:

CharSet='big5'
Language='tchinese'

Examples

To set the character set to iso_1:

See also

Language

Locale

Description

Client_Locale identifies the locale that the client application uses.

Applies to

I10 Informix

Syntax

Client_locale='language_territory.codeset'

Default value

Based on the operating system's locale.

Usage

The I10 native interface uses the Informix GLS (Global Language Support) API for global language support. Client_Locale specifies the value of the Informix environment variable CLIENT_LOCALE. The I10 interface uses this setting to access string data in an Informix database and to process SQL statements. If you do not set the DBParm, the default client locale value is based on the OS locale.

For example, to access a database that has a Japanese SJIS locale, Client_Locale should be set to ja_jp.sjis on the client system.

For more information about the Informix CLIENT_LOCALE and DB_LOCALE environment variables, see the IBM Informix GLS User's Guide, currently available at https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.glsug.doc/glsug.htm.

Examples

To set the client locale to 'en_us.utf8':

See also

Db_Locale

StrByCharset

Description

Specifies whether Oracle should maintain connections in a pool. An Oracle connection pool is a group of reusable physical connections spanning several sessions and managed by the Oracle Call Interface (OCI). The CNNPool DBParm is maintained in the ORA driver for backward compatibility only. It is ignored if you use the Pooling DBParm.

Applies to

O90 Oracle9i

O10 Oracle 10g

ORA Oracle (for 11g and later)

Syntax

CnnPool='value'

Default value

No

Usage

If CnnPool='Yes', the Oracle client creates a connection pool and can connect to Oracle9i, and Oracle 10g Server. The pool contains physical connections to Oracle Server and is managed by the OCI. The pool persists until PowerBuilder terminates or OCI.dll is unloaded.

PowerBuilder can connect to 10 different Oracle servers, as distinguished by service name, concurrently. There can be no more than 20 connections per pool. Each pool is created by the Oracle9i or later client and might also contain connections to Oracle8 and Oracle8i servers. Once a connection pool has been created, PowerBuilder maintains the physical connections until it terminates or OCI.DLL is unloaded.

Examples

To use connection pooling:

See also

StatementCache

Description

Specifies the number of seconds the interface should wait for a command to execute.

Applies to

ADO.NET

Syntax

CommandTimeout=value

Default value

None

Usage

The default value for the CommandTimeout parameter is driver-specific.

When you use this parameter with an AdoTransaction object in DataWindow .NET, specify a value for this parameter in the second argument of the AdoTransaction constructor.

Examples

To set the CommandTimeout value to wait 60 seconds for a command to execute:

Description

Specifies whether PowerBuilder should commit (the default) or roll back all previously uncommitted database updates before disconnecting from a data source.

Applies to

All database interfaces

Syntax

CommitOnDisconnect='value'

Default value

CommitOnDisconnect='Yes'

Usage

Set CommitOnDisconnect to No if you want PowerBuilder to roll back uncommitted database updates (instead of automatically committing them when you disconnect from the database).

Examples

To tell PowerBuilder to roll back uncommitted database updates instead of committing them when disconnecting from the database:

Description

Allows the user to connect to the Oracle Server with SYSOPER or SYSDBA system privileges. Supports proxy authentication with additional user names (ORA driver only) when connecting to Oracle 10.2 or higher database servers.

Applies to

O90 Oracle9i

O10 Oracle 10g

ORA Oracle (for 11g and later)

Syntax

ConnectAs='value'

Default value

Normal

Usage

If ConnectAs='Normal', this parameter is not used. If ConnectAs ='SYSDBA' or ConnectAs ='SYSOPER', Oracle allows the user to connect with SYSDBA or SYSOPER privileges, respectively, provided that these privileges have been granted the user.

If you connect using ConnectAs ='SYSDBA', Oracle uses the SYS schema instead of the schema that might already be associated with the user ID. If you connect using ConnectAs ='SYSOPER', Oracle uses the PUBLIC schema.

The PowerBuilder ORA driver supports the proxy authentication feature that was introduced in Oracle 10.2. With proxy authentication, the end user typically authenticates to a middle tier (such as a firewall), that in turn logs into the database on the user's behalf as a proxy user. After logging into the database, the proxy user can switch to the end user's identity and perform operations using the authorization accorded to that user.

The ConnectAs DBParm parameter allows you to take advantage of this proxy connection feature. For example, if the user's Transaction object LogID is "Scott" and you set the ConnectAs DBParm parameter to "John", the OCI client logs in to database as the proxy user ("Scott"), then switches to the end user identity ("John").

If you are using connection or session pooling, the proxy user name is the connection or session pooling creator (which you can provide in the PoolCreator and PoolPwd DBParm parameters), and the Transaction object's LogID is ignored. No proxy session can be created if pooling is set to homogeneous session mode.

Examples

Example 1

To use the SYS schema instead of the schema associated with the User ID:

Example 2

To connect as a proxy user (Scott) for the end user named John:

Description

Sets driver-specific connection options when you are accessing an ODBC data source in PowerBuilder. These options specify the following:

Certain ConnectOption parameters apply to all ODBC drivers, whereas others apply only to particular ODBC drivers.

For information on each ConnectOption parameter and whether you can use it with your ODBC driver, see the table in the Syntax section.

Applies to

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

Syntax

ConnectOption=' SQL_DRIVER_CONNECT,value;   
   SQL_INTEGRATED_SECURITY,value;
   SQL_OPT_TRACE,value;   
   SQL_OPT_TRACEFILE,value;
   SQL_PRESERVE_CURSORS,value;
   SQL_USE_PROCEDURE_FOR_PREPARE,value '

The following table lists the applicable ODBC drivers, purpose, and values for each ConnectOption parameter.

Default value

ConnectOption='SQL_DRIVER_CONNECT, SQL_DRIVER_COMPLETE;

SQL_INTEGRATED_SECURITY,SQL_IS_OFF;

SQL_OPT_TRACE,SQL_OPT_TRACE_OFF;

SQL_PRESERVE_CURSORS,SQL_PC_OFF;

SQL_USE_PROCEDURE_FOR_PREPARE,SQL_UP_ON'

Usage

Microsoft Server ODBC driver

The ConnectOption parameter applies only if you are accessing a SQL Server database with the Microsoft ODBC SQL Server driver.

You must obtain the Microsoft SQL Server ODBC driver from Microsoft Corporation. This driver is not supplied with PowerBuilder.

Examples

To specify nondefault options for the ConnectOption parameter:

Description

Specifies the parameters required to connect to an ODBC data source. PowerBuilder uses these parameters to connect to the database.

Applies to

ODBC

Syntax

The ConnectString syntax displays on a single line. You must enclose the entire ConnectString in single quotes and separate parameters within the ConnectString with semicolons.

ConnectString='DSN=data_source_name; {UID=user_ID; PWD=password; driver_specific_parameters}'

Default value

None

Usage

PowerBuilder generates the ConnectString automatically when you define an ODBC data source and copies it to the Preview box in the Database Profile Setup dialog box. This happens before you connect to the data source in PowerBuilder.

Therefore, you do not have to enter the ConnectString yourself when defining an ODBC data source. However, you might need to edit the ConnectString value in the Database Profile Setup dialog box.

You can change the ConnectString parameter if necessary by editing it in the Database Profile Setup dialog box. For example, if you change the name of an existing ODBC data source, edit its database profile to update the connect string with the new DSN (data source name) value.

Examples

Example 1

This example shows a connect string for an ODBC data source that contains the data source name (DSN=Sales), user ID (UID=dba), and password (PWD=sql). Parameters within the connect string are separated by semicolons.

Description

Specifies an integer for database connection increments per session.

Applies to

ORA Oracle (for 11g and later), O10 Oracle 10g, and Oracle 9.2 databases

Syntax

CSIncr =value

Default value

1

Usage

This value is ignored when the SessionHomogeneous DBParm is set to false.

Examples

The following example sets the increment for the number of database connections in a session pool to 10:

See also

CSMax

CSMin

Description

Specifies the maximum number of database connections in a connection or session pool.

Applies to

ORA Oracle (for 11g and later), O10 Oracle 10g, and Oracle 9.2 databases

Syntax

CSMax =value

Default value

100

Usage

This value is ignored when the SessionHomogeneous DBParm is set to false.

Examples

The following example limits the number of database connections in a connection pool to 15:

See also

CSIncr

CSMin

Description

Specifies the minimum number of database connections in a connection or session pool.

Applies to

ORA Oracle (for 11g and later), O10 Oracle 10g, and Oracle 9.2 databases

Syntax

CSMin =value

Default value

1

Usage

This value is ignored when the SessionHomogeneous DBParm is set to false.

Examples

The following code keeps a minimum of 5 database connections open in a connection pool:

See also

CSIncr

CSMax

Description

Specifies the cursor library to use when connecting to an ODBC data source.

Applies to

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

Syntax

CursorLib='value'

Default value

CursorLib='Driver_Cursors'

Examples

To specify use of the ODBC Version 2.0 or higher cursor library when connecting to an ODBC data source:

Description

When used with the CursorScroll parameter, specifies locking options for cursors in ODBC data source.

The values you can set for CursorLock control two aspects of cursor locking:

Applies to

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

Syntax

CursorLock='lock_value'

Default value

If you do not specify a value for CursorLock, PowerBuilder defaults to the cursor lock setting specified by your ODBC driver.

Examples

To set scrolling and locking options for cursors in an ODBC data source:

See also

CursorScroll

Description

When used with the CursorLock parameter, specifies scrolling options for cursors in an ODBC data source.

The location of a cursor indicates the current position in the result set produced by a SQL statement. Scrolling allows a cursor to move through the data in a result set one row at a time.

Applies to

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

Syntax

CursorScroll='scroll_value'

Default value

If you do not specify a value for CursorScroll, PowerBuilder defaults to the cursor scroll settings specified for your ODBC data source driver.

Usage

For large result sets, it might be impractical to use a keyset-driven cursor that requires the driver to save keys for the entire result set. Instead, you can use a mixed cursor by specifying a 32-bit integer value that is the number of rows in your keyset (see Example 2). This number is typically smaller than the result set. The default keyset size is 0.

A mixed cursor uses KeySet scrolling within the specified keyset and Dynamic scrolling outside the keyset.

Examples

Example 1

To set scrolling and locking options for cursors in an ODBC data source:

Example 2

This example sets the number of rows in the keyset to 100. Assume that the entire result set has 1000 rows. When the cursor is opened, the driver saves keys for the first 100 rows of the result set. It then retrieves the next block of 100 keys until the entire result set is retrieved.

See also

CursorLock

Description

Supports the scrollable cursor feature introduced in Adaptive Server Enterprise 15.0, including directional scrolling (forwards and backwards) and sensitivity towards independent changes to table.

Applies to

ASE, SYC SAP Adaptive Server Enterprise (15.0 and later)

Syntax

CursorType='value'

Default value

CursorType='NonScrollable'

Usage

Adaptive Server Enterprise 15.0 allows both scrollable and nonscrollable cursors, which can be either semi-sensitive or insensitive. "Scrollable" means that you can scroll through the cursor result set by fetching any, or many, rows, rather than one row at a time; you can also scan the result set repeatedly. A scrollable cursor allows you to set the position of the cursor anywhere in the cursor result set for as long as the cursor is open.

To use a scrollable cursor, you must use a DECLARE CursorName CURSOR SQL statement to declare it with a suitable SELECT statement, such as the PowerBuilder Dynamic SQL Format 3 and Dynamic SQL Format 4 statements, and you must have the query engine provided in Adaptive Server 15.0 or later.

For sensitive scrolling to work correctly, the table must have a clustered index or a clustered unique constraint, such as a clustered primary key.

All scrollable cursors are read-only and can only be used when the value of the CursorUpdate database parameter is 0 (the default). If you need an updatable cursor, set the CursorUpdate parameter to 1. When CursorUpdate is set to 1, the value of CursorType is ignored. All update cursors are nonscrollable.

If a scrollable cursor is moved to a position before the first row or after the last row, SQLCA.SQLCode returns 100 and no data is returned. However, users can continue to fetch data by using a suitable FETCH statement after receiving this SQLCode value.

Both client and server must be Adaptive Server 15.0 or higher.

Examples

To specify support for semi-sensitive scrollable cursors (data modifications are presented when scrolling forwards):

See also

CursorUpdate

Description

For those interfaces that support it, CursorUpdate specifies whether cursors in your target database are declared read-only or updatable.

Applies to

DIR SAP DirectConnect

ASE and SYC SAP Adaptive Server Enterprise

Syntax

CursorUpdate=value

Default value

CursorUpdate=0

Usage

Set the CursorUpdate parameter to 1 to declare updatable cursors if you plan to use either of the following SQL statements in your application (table represents the table name and cursor represents the cursor name):

DELETE FROM table WHERE CURRENT OF cursor
UPDATE table SET set_clause WHERE CURRENT OF cursor

If you are communicating with the database in a PowerBuilder script, you can reset the CursorUpdate value anytime before or after the Transaction object has connected to the database.

When you declare cursors updatable in a database accessed through DirectConnect, the cursor declaration you code must include a FOR UPDATE OF column_list clause.

When you use updatable cursors with the DIR interface and a Gatewayless connection to the mainframe, you must set Block=1 before executing the cursor. You can reset the Block parameter to its default of 100 after you close the cursor within your code.

Examples

To specify that database cursors are declared updatable:

Description

Specifies the name of the database you want to connect to.

Applies to

ADO.NET

SNC SQL Native Client for Microsoft SQL Server

MSOLEDBSQL Microsoft OLE DB Driver for SQL Server

Syntax

DataBase='database_name'

Default value

None

Examples

To connect to the database "mydb":

Description

Specifies that you want to create a file or use an existing file containing your connection information to connect to your data source.

Applies to

ADO.NET

OLE DB

SNC SQL Native Client for Microsoft SQL Server

MSOLEDBSQL Microsoft OLE DB Driver for SQL Server

Syntax

DataLink='file_name'

Default value

None

Usage

The Data Link option allows you to access Microsoft's Data Link API. The Data Link API allows you to define a file or use an existing file that contains your connection information. A Data Link file is identified with the suffix .udl.

To launch the API, double-click on Manage Data Links under OLE DB Utilities in the Installed Database Interfaces list or select the File Name check box on the Connection page in the Database Profile Setup dialog box and click the button next to the File Name box.

For more information on using the Data Link API, see Microsoft's Universal Data Access website.

Using a Data Link file versus setting other database parameters

If you use a Data Link file to connect to your data source with the ADO.NET or OLE DB interface, all other settings you make in the Database Profile Setup dialog box are ignored.

If you use a Data Link file to connect to your data source with the SNC interface, the setting in the ProviderString database parameter still takes effect. The SNC interface gets a connection string from the data link file, and then copies the contents of the ProviderString parameter into the connection string so that it contains the connection parameters from both the data link file and the ProviderString parameter. You might want to take advantage of this feature if you do not want to save the user name and password in the UDL file. You can specify them in the ProviderString parameter instead.

Examples

To use the file oledb.udl to connect to an OLE DB data provider:

Description

Identifies the data source to which you want to connect. The data source can be a file, a database, or an ODBC data source depending on the OLE DB data provider you are using.

Applies to

ADO.NET

OLE DB

Syntax

DataSource='datasource_name'

Default value

None

Usage

For the SNC interface for SQL Server 2005, specifying a value for Server on the Connection page or specifying a value for SQLCA.ServerName in code is equivalent to setting this parameter.

The value of the Data Source parameter varies depending on the type of data source connection you are making. For example, if you are using Microsoft's OLE DB Provider for ODBC, you would enter the actual ODBC data source name for the Data Source value. If you are using Microsoft's OLE DB Provider for SQL Server, you would enter the actual Microsoft SQL Server server name for the Data Source value.

For more information, see the documentation provided by your OLE DB data provider.

Examples

Example 1

To use the Microsoft OLE DB Provider for ODBC to connect to the PB Demo DB:

Example 2

To use the Microsoft OLE DB Provider for Oracle to connect to an Oracle 8 database:

See also

DataLink

Provider

Description

When you update data in the DataWindow painter, PowerBuilder builds a SQL UPDATE statement in the background. The Date parameter determines how PowerBuilder specifies a date datatype when it builds the SQL UPDATE statement.

Applies to

JDB JDBC

ODBC

O90 Oracle9i

O10 Oracle 10g

ORA Oracle (for 11g and later)

Syntax

The syntax you use to specify the Date parameter differs slightly depending on the database.

The Database Profile Setup dialog box inserts special characters (quotes and backslashes) where needed, so you can specify just the date format.

In a PowerBuilder application script, you must use the following syntax:

JDBC and ODBC syntax

PowerBuilder parses the backslash followed by two single quotes (\'') as a single quote when it builds the SQL UPDATE statement.

Date=' \''date_format\'' '

Oracle syntax

PowerBuilder parses each set of four consecutive single quotes ('''') as a single quote when it builds the SQL UPDATE statement.

Date=' ''''date_format'''' '

Default value

The default value for Date depends on the DBMS you are accessing, as summarized in the following table:

Examples

About these examples

Assume you are updating a table named Employee by setting the Startdate column to 2006-04-23. This date is represented by the following date format:

yyyy-mm-dd

Example 1 (JDBC and ODBC syntax)

To specify that PowerBuilder should use this format for the date datatype when it builds the SQL UPDATE statement:

What happens

PowerBuilder builds the following SQL UPDATE statement to update the table:

UPDATE EMPLOYEE
SET STARTDATE='2006-04-23'

Example 2 (Oracle syntax)

To specify that PowerBuilder should use this format for the date datatype when it builds the SQL UPDATE statement:

What happens

PowerBuilder builds the following SQL UPDATE statement to update the table:

UPDATE EMPLOYEE
SET STARTDATE='2006-04-23'

See also

DateTime

Time

Description

When you update data in the DataWindow painter, PowerBuilder builds a SQL UPDATE statement in the background. The DateFormat parameter determines how PowerBuilder specifies a date datatype when it builds the SQL UPDATE statement.

Applies to

ADO.NET

OLE DB

SNC SQL Native Client for Microsoft SQL Server

MSOLEDBSQL Microsoft OLE DB Driver for SQL Server

Syntax

DateFormat='date_format'

Default value

If no value is specified for the DateFormat parameter, PowerBuilder does not use a date datatype.

Usage

When you call stored procedures, the database server might not accept the date format built by PowerBuilder. If this occurs, you can try to use another format. For example, for Microsoft SQL Server, try this format:

DateFormat='\''yyyy-mm-dd\'''

Examples

Assume you are updating a table named Employee by setting the Startdate column to 2006-04-23. This date is represented by the date format yyyy-mm-dd.

To specify that PowerBuilder should use this format for the date datatype when it builds the SQL UPDATE statement:

What happens

PowerBuilder builds the following SQL UPDATE statement to update the table:

UPDATE EMPLOYEE
SET STARTDATE='2006-04-23'

See also

DateTimeFormat

TimeFormat

Description

When you update data in the DataWindow painter, PowerBuilder builds a SQL UPDATE statement in the background. The DateTime parameter determines how PowerBuilder specifies a DateTime datatype when it builds the SQL UPDATE statement. (A DateTime datatype contains both a date value and a time value.)

Applies to

JDB JDBC

ODBC

O90 Oracle9i

O10 Oracle 10g

ORA Oracle (for 11g and later)

Syntax

The syntax you use to specify the DateTime differs slightly depending on the database.

The Database Profile Setup dialog box inserts special characters (quotes and backslashes) where needed, so you can specify just the DateTime format.

In code, you must use the following syntax:

JDBC and ODBC syntax

PowerBuilder parses the backslash followed by two single quotes (\'') as a single quote when it builds the SQL UPDATE statement.

DateTime=' \''DateTime_format\'' '

Oracle syntax

PowerBuilder parses each set of four consecutive single quotes ('''') as a single quote when it builds the SQL UPDATE statement.

DateTime=' ''''DateTime_format'''' '

Default value

The default value for DateTime depends on the DBMS you are accessing, as summarized in the following table:

Examples

About these examples

Assume you are updating a table named Files by setting the Timestamp column to 4/2/06 3:45 pm. This DateTime is represented by the following DateTime format:

m/d/yy h:mm am/pm

Example 1 (JDBC, ODBC, and OLE DB syntax)

To specify that PowerBuilder should use this format for the DateTime datatype when it builds the SQL UPDATE statement:

What happens

PowerBuilder builds the following SQL UPDATE statement to update the table:

UPDATE FILES
SET TIMESTAMP='4/2/06 3:45 pm'

Example 2 (Oracle syntax)

To specify that PowerBuilder should use this format for the DateTime datatype when it builds the SQL UPDATE statement:

What happens

PowerBuilder builds the following SQL UPDATE statement to update the table:

UPDATE FILES
SET TIMESTAMP='4/2/06 3:45 pm'

See also

Date

Time

Description

For those interfaces that support it, DateTimeAllowed controls whether columns having a DateTime datatype can appear as unique key columns in the WHERE clause of a SQL UPDATE or DELETE statement. PowerBuilder generates an UPDATE statement or a DELETE statement followed by an INSERT statement to update the database from a DataWindow object.

When you are working in the DataWindow painter, you specify which columns to include in the WHERE clause by selecting them from the Unique Key Columns list in the Specify Update Properties dialog box.

By default, DateTimeAllowed is set to 0 to prohibit DateTime columns from displaying in the Unique Key Columns list and consequently from appearing in the WHERE clause of an UPDATE or DELETE statement. When you set DateTimeAllowed to 1, any DateTime columns in your database table display in the Unique Key Columns list and can be selected to appear in the WHERE clause of an UPDATE or DELETE statement.

Applies to

ASE, SYC SAP Adaptive Server Enterprise

DIR SAP DirectConnect

I10 Informix

IN9 Informix

Syntax

DateTimeAllowed=value

Default value

DateTimeAllowed=0

Usage

When to set

To allow the use of DateTime columns as unique key columns in the WHERE clause of an UPDATE or DELETE statement when you are updating the database from a DataWindow object, set DateTimeAllowed to 1.

For instructions on using the Specify Update Properties dialog box to specify update characteristics for a DataWindow object, see the section called “Controlling Updates in DataWindow objects” in Users Guide.

What happens when you save the DataWindow object

When you set DateTimeAllowed to 1, select a DateTime column to appear in the WHERE clause, and then save the DataWindow object, this column continues to display in the Unique Key Columns list even if you set DateTimeAllowed to 0 on a subsequent connection.

Examples

To allow the use of DateTime columns in the WHERE clause of an UPDATE or DELETE statement:

Description

When you update data in the DataWindow painter, PowerBuilder builds a SQL UPDATE statement in the background. The DateTimeFormat parameter determines how PowerBuilder specifies a DateTime datatype when it builds the SQL UPDATE statement. (A DateTime datatype contains both a date value and a time value.)

Applies to

ADO.NET

OLE DB

SNC SQL Native Client for Microsoft SQL Server

MSOLEDBSQL Microsoft OLE DB Driver for SQL Server

Syntax

DateTimeFormat='datetime_format'

Default value

If no value is specified for the DateTimeFormat parameter, PowerBuilder does not use a datetime datatype.

Usage

When you call stored procedures, the database server might not accept the DateTime format built by PowerBuilder. If this occurs, you can try to use another format. For example, for Microsoft SQL Server, try this format:

DateTimeFormat='\''yyyy-mm-dd hh:mm:ss.fff\'''

PowerBuilder parses the backslash followed by two single quotes (\'') as a single quote.

Examples

Assume you are updating a table named Files by setting the Timestamp column to 4/2/06 3:45 pm. This DateTime is represented by the following DateTime format:

m/d/yy h:mm am/pm

To specify that PowerBuilder should use this format for the DateTime datatype when it builds the SQL UPDATE statement:

What happens

PowerBuilder builds the following SQL UPDATE statement to update the table:

UPDATE FILES
SET TIMESTAMP='4/2/06 3:45 pm'

See also

DateFormat

TimeFormat

Description

Specifies the section in a .NET configuration file to be used to specify custom configuration settings.

Applies to

ADO.NET

Syntax

DBConfigSection='value'

Default value

None.

Usage

You can use the standard select @@identity syntax to obtain the value of an identity column. You can also use an alternative syntax, such as select scope_identity(), by adding sections to a .NET configuration file for your application.

The configuration file resides in the same directory as the application and has the same name as the application with the extension .config. It can contain multiple custom configuration sections. Each has two attributes: dbParm and getIdentity. You can set either or both of these attributes. For Web applications, add the settings to the web.config file.

The dbParm value sets the value of the DBParm parameter of the transaction object. It has a maximum length of 1000 characters. If you set a value for a parameter in the configuration file, any value that you set in code or in the Database Profile Setup dialog box is overridden.

The getIdentity value specifies the syntax used to retrieve the value of an identity column. It has a maximum length of 100 characters. If you do not specify a value for getIdentity, the select @@identity syntax is used.

For more information about creating the configuration file, see the section called “Getting identity column values” in Connecting to Your Database.

Examples

To specify that your PowerBuilder application uses the custom configuration file called myconfig1:

This sample configuration file for PowerBuilder is called pb190.exe.config. It contains three custom configurations. The <myconfig> element sets both the dbParm and getIdentity attributes. <myconfig1> sets getIdentity only, and <myconfig2> sets dbParm only. The <runtime> section is in the configuration file that ships with PowerBuilder but would not be included in the configuration file that you ship with your application, which would have the same name as your application with the extension exe.config. For .NET Web Service targets, you add the custom configurations to the web.config file. The <configSections> section should be added before any other application settings.

<configuration>
   <configSections>
     <sectionGroup name="dbConfiguration">
        <section name="myconfig" 
         type="Sybase.PowerBuilderDataWindow.Db.DbConfiguration,
         Sybase.PowerBuilderDataWindow.Db"
        />

        <section name="myconfig1"
         type="Sybase.PowerBuilderDataWindow.Db.DbConfiguration,
         Sybase.PowerBuilderDataWindow.Db"
        />
        <section name="myconfig2"
         type="Sybase.PowerBuilderDataWindow.Db.DbConfiguration,
         Sybase.PowerBuilderDataWindow.Db"
        />
      </sectionGroup>
   </configSections>

<runtime>
      <assemblyBinding xmlns=
       "urn:schemas-microsoft-com:asm.v1">
         <dependentAssembly>
            <assemblyIdentity name=
            "Sybase.PowerBuilder.Db"/>
            <codeBase href="file:///C:/Program Files/
             Appeon/PowerBuilder 19.0/DotNET/bin/
             Sybase.PowerBuilder.Db.dll"/>
         </dependentAssembly>
         <dependentAssembly>
           <assemblyIdentity name=
            "Sybase.PowerBuilder.WebService.WSDL"/>
           <codeBase href="file:///C:/Program Files/
            Appeon/PowerBuilder 19.0/DotNET/bin/
            Sybase.PowerBuilder.WebService.WSDL.dll"/>
         </dependentAssembly>
         <dependentAssembly>
           <assemblyIdentity name=
            "Sybase.PowerBuilder.WebService.Runtime"/>
            <codeBase href="file:///C:/Program Files/
             Appeon/PowerBuilder 19.0/DotNET/bin/
             Sybase.PowerBuilder.WebService.
             Runtime.dll"/>
         </dependentAssembly>
         <probing privatePath="DotNET/bin" />
      </assemblyBinding>
   </runtime>
   <dbConfiguration>
      <myconfig dbParm="disablebind=0"
       getIdentity="select scope_identity()"
       />
      <myconfig1 getIdentity="select scope_identity()"
      />
      <myconfig2 =
       "Namespace='Oracle.DataAccess.Client',
       DataSource='ora10gen',DisableBind=1,
       NCharBind=1,ADORelease='10.1.0.301'"
       />
   </dbConfiguration>
</configuration>

Description

Specifies the number of seconds PowerBuilder waits for a response from the DBMS when you retrieve rows in a DataWindow object or query. When you set the Async parameter to 1 to enable asynchronous operation, you can also set the DBGetTime parameter for those DBMSs that support this parameter.

If DBGetTime is set to 0 (the default), PowerBuilder waits indefinitely for a DBMS response (the request never times out). If the DBGetTime value expires before the first row is retrieved, your request is automatically canceled.

Applies to

ASE, SYC SAP Adaptive Server Enterprise

DIR SAP DirectConnect

I10 Informix

IN9 Informix

JDB JDBC

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

O90 Oracle9i

O10 Oracle 10g

ORA Oracle (for 11g and later)

Syntax

DBGetTime=value

Default value

DBGetTime=0

Usage

Requirements for using DBGetTime

To use the DBGetTime parameter, you must do both of the following:

Examples

To enable asynchronous operation and set the DBGetTime parameter to 20 seconds:

See also

Async

Description

Db_Locale identifies the locale of the data in the database.

Applies to

I10 Informix

Syntax

Db_Locale='language_territory.codeset'

Default value

Client_Locale value.

Usage

The I10 native interface uses the Informix GLS (Global Language Support) API for global language support. Db_Locale specifies the value of the Informix environment variable DB_LOCALE. If you do not set the DBParm, the default Db_Locale value is the Client_Locale value.

Db_Locale specifies the language, territory, and code set that the database server needs to correctly interpret locale-sensitive datatypes such as NChar and NVarChar in a specific database. The code set specified in DB_LOCALE determines which characters are valid in any character column, as well as in the names of database objects such as databases, tables, columns, and views.

For more information about the Informix CLIENT_LOCALE and DB_LOCALE environment variables, see the IBM Informix GLS User's Guide, currently available at https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.glsug.doc/glsug.htm.

Examples

To set the database locale to 'en_us.utf8':

See also

Client_Locale

Locale

Description

Specifies the maximum length of a text field that is returned when you include the text field in a SQL SELECT statement.

You can set the DBTextLimit parameter if you want to include a long text string in a DataWindow object without treating the text as a binary large object (blob) datatype.

Applies to

ASE and SYC SAP Adaptive Server Enterprise

DIR SAP DirectConnect

SNC SQL Native Client for Microsoft SQL Server

MSOLEDBSQL Microsoft OLE DB Driver for SQL Server

Syntax

DBTextLimit='value'

Default value

The default value for DBTextLimit is the default specified for the DBTEXTLIMIT DB-Library or CS_TEXTLIMIT CT-Library connection property.

Usage

The text field length that DB-Library or CT-Library returns is the lesser of the DBTextLimit value and the setting for the global variable TEXTSIZE.

If the setting for TEXTSIZE is less than the value you specify for DBTextLimit, DB-Library or CT-Library returns the TEXTSIZE value.

Examples

To have DB-Library or CT-Library return a text field that is up to 32,000 bytes long when you include the text field in a SQL SELECT statement:

Description

Specifies the decimal separator setting used by the back-end DBMS that you are accessing in PowerBuilder. If your DBMS uses a decimal separator other than period (.), which is the default, set DecimalSeparator to the value for your DBMS to ensure that PowerBuilder correctly handles numeric strings returned from your database.

Applies to

ADO.NET

DIR SAP DirectConnect

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

OLE DB

O90 Oracle9i

O10 Oracle 10g

ORA Oracle (for 11g and later)

SNC SQL Native Client for Microsoft SQL Server

MSOLEDBSQL Microsoft OLE DB Driver for SQL Server

Syntax

DecimalSeparator='value'

Default value

DecimalSeparator='.'

Usage

When to set DecimalSeparator

The DecimalSeparator parameter currently supports period (.) and comma (,) as valid values. Therefore, if the decimal separator setting for your DBMS is a comma, you should set the DecimalSeparator parameter to ',' (comma) to make sure PowerBuilder correctly handles numeric strings returned from your database.

Example using Oracle

Assume you are accessing an Oracle database in PowerBuilder and the decimal separator setting is a comma (,). Oracle returns to PowerBuilder the numeric string '123,50' containing a comma instead of a period as the decimal separator. PowerBuilder then sends this string to its decimal conversion routines.

By default, the PowerBuilder decimal conversion routines expect a period as the decimal separator. If you set the DecimalSeparator parameter to ',' (comma), PowerBuilder correctly handles this string and returns it as '123,50'.

Examples

To specify that your DBMS uses a comma (,) as the decimal separator setting:

See also

NumericFormat

Description

The DefaultProcOwner parameter lets you set a default owner for a stored procedure. The parameter takes effect only when the stored procedure is not qualified. For ODBC, the PBNewSPInvocation parameter must also be set.

Applies to

ADO.NET

ODBC

Syntax

DefaultProcOwner='value'

Usage

The parameter can be set dynamically at runtime after connecting to a database. You can also set it in your PBODB.ini file if you want to create and retrieve data into a DataWindow with a stored procedure data source in the DataWindow painter. The runtime setting overrides the setting in PBODB.ini.

You can also cancel the setting at runtime. If you do so, PowerBuilder uses the current user as the owner of a non-qualified stored procedure when it obtains the parameters of the stored procedure.

Examples

To set the default owner to proms in PBODB.ini:

[SQL Anywhere] DefaultProcOwner='proms'

In code:

SQLCA.DBParm="DefaultProcOwner='proms'"; 

To cancel the setting:

 SQLCA.DBParm="DefaultProcOwner=''";

Note that the single quotes in the previous example contain an empty string, not a space.

See also

PBMaxTextSize

Description

Specifies whether you want PowerBuilder to enclose the names of tables, columns, indexes, and constraints in double quotes when it generates SQL statements. This affects the behavior of any PowerBuilder painter that generates SQL syntax.

Applies to

ADO.NET

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

O90 Oracle9i

O10 Oracle 10g

ORA Oracle (for 11g and later)

SNC SQL Native Client for Microsoft SQL Server

MSOLEDBSQL Microsoft OLE DB Driver for SQL Server

Syntax

DelimitIdentifier='value'

Default value

The default value for the DelimitIdentifier parameter depends on the DBMS you are accessing, as follows:

Usage

Informix

Informix database servers can create a log of database transactions in either ASCII or non-ASCII format. If the database is creating a non-ASCII log, the setting of the DelimitIdentifier is optional. If the database is creating an ASCII log, you must set DelimitIdentifier='Yes' to make the SQL syntax generated by PowerBuilder behave as expected.

SAP Adaptive Server Enterprise

When you set DelimitIdentifier to 'Yes', the "set quoted_identifier on" command is automatically sent to Adaptive Server to adjust your database connection on the server. Otherwise, the "set quoted_identifier off" command is sent to the server. This feature occurs with ASE, SYC, JDBC, ODBC, and OLE DB interfaces.

Microsoft SQL Server

When you set DelimitIdentifier to 'Yes', the "set quoted_identifier on" command is automatically sent to Microsoft SQL Server to adjust your database connection on the server when you use ODBC or OLE DB. Otherwise, the "set quoted_identifier off" command is sent to the server.

Sending "set quoted_identifier off" to the server can cause some SQL commands to fail if the SQL code contains quotation marks. To prevent PowerBuilder from sending this instruction to the server, set the DelimitIdentifierToDB parameter to "No" in the PBODB initialization file or the connection string.

JDBC and ODBC

The DelimitIdentifier parameter setting overrides the DelimitIdentifier setting specified for your JDBC driver in the registry and for your ODBC driver in the PBODB initialization file.

DirectConnect

If you want to use mixed-case identifier names, you must set DelimitIdentifier='Yes'. Also, you must set LowerCaseIdent='No' to preserve case sensitivity of identifiers stored in the DB2 system catalog.

Examples

To specify that PowerBuilder should not enclose table and column names in double quotes when it generates SQL statements:

See also

DelimitIdentifierToDB

LowerCaseIdent

Description

Specifies whether PowerBuilder should send a "set quoted_identifier off" instruction to the server when the DelimitIdentifier parameter is not set.

Applies to

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

OLE DB

Syntax

DelimitIdentifierToDB='value'

Default value

'No'

Usage

When you set DelimitIdentifier to 'Yes', the "set quoted_identifier on" command is automatically sent to the database server to adjust your database connection on the server when you use ODBC or OLE DB. Otherwise, the "set quoted_identifier off" command is sent to the server.

Sending "set quoted_identifier off" to the server can cause some SQL commands to fail if the SQL code contains quotation marks. To prevent PowerBuilder from sending this instruction to the server, set the DelimitIdentifierToDB parameter to 'No' in the PBODB initialization file or the connection string.

Examples

To specify that PowerBuilder should not send a set quoted_identifier instruction to the server, add the following to the appropriate section of your PBODB initialization file or your connection string:

"DelimitIdentifierToDB='No'"

See also

DelimitIdentifier

Description

For those DBMSs that support bind variables, PowerBuilder can bind input parameters to a compiled SQL statement by default. The DisableBind parameter allows you to specify whether you want to disable this binding.

When you set DisableBind to 1 to disable the binding, PowerBuilder replaces the input variable with the value entered by the application user or specified in code.

Applies to

ADO.NET

ASE, SYC SAP Adaptive Server Enterprise

I10 Informix

IN9 Informix

JDB JDBC

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

OLE DB

O90 Oracle9i

O10 Oracle 10g

ORA Oracle (for 11g and later)

SNC SQL Native Client for Microsoft SQL Server

MSOLEDBSQL Microsoft OLE DB Driver for SQL Server

Syntax

DisableBind=value

Default value

DisableBind=1 for ADO.NET, ASE, SYC, SNC, and OLE DB, DisableBind=0 for other interfaces

Usage

Bind variables

In a SQL statement, a bind variable is a placeholder for a column value. By default, PowerBuilder associates (binds) data from a variable defined in your application to the bind variable each time the SQL statement executes.

Using bind variables in SQL statements

For example, the following SQL statement retrieves those rows in the Books table about books written by Hemingway:

SELECT * FROM books WHERE author="Hemingway"

Suppose that you want to execute this statement to get information about books written by other authors. Instead of compiling and executing a new statement for each author, you can define a bind variable that represents the author's name. The user then supplies the author's actual name when the application executes. By using bind variables, you ensure that the statement is compiled only once and executed repeatedly with new values supplied by the user.

If your database supports bind variables and DisableBind is set to 0 to enable binding (the default for all database interfaces except ADO.NET, ASE, SYC, SNC, and OLE DB), PowerBuilder generates the statement with parameter markers (:bind_param) and passes the actual parameter value at execution time. For example:

SELECT * FROM books WHERE author=:bind_param

Bind variables and cached statements

Using bind variables in conjunction with cached statements can improve the performance of most applications, depending on the application. In general, applications that perform a large amount of transaction processing benefit the most from using bind variables and cached statements.

In order to use cached statements, make sure that DisableBind is set to 0. This enables the binding of input variables to SQL statements in PowerBuilder. (For more about using cached statements, see the description of the SQLCache parameter.)

Performance improvements

For SQL Anywhere and Oracle databases, bind variables improve performance by allowing PowerBuilder to insert and modify strings that exceed 255 characters.

Bind variables and default column values

When DisableBind is set to 0 to enable the use of bind variables, the DataWindow painter does both of the following to get maximum performance improvement from using bind variables when you add rows to a DataWindow object:

For example, if a table named Order_T contains three columns named Order_ID, Order_Date, and Customer_ID, the DataWindow painter generates the following SQL INSERT statement when DisableBind is set to 0 (default binding enabled):

INSERT INTO Order_T(Order_ID, Order_Date, Customer_ID)
      VALUES(:bind_param1, :bind_param2, :bind_param3)

If one of these columns is null, the DataWindow painter sets a null value indicator for this column parameter and executes the statement. This behavior is important to understand if you want your back-end DBMS to set a default value for any columns in your DataWindow object.

To illustrate, suppose that your application users do not enter a value for the Order_Date column because they expect the back-end DBMS to set this column to a default value of TODAY. Then, they retrieve the row and find that a null value has been set for Order_Date instead of its default value. This happens because the SQL INSERT statement generated by the DataWindow painter specified a null value indicator, so the DBMS set the column value to null instead of to its default value as expected.

Setting a default column value when binding is enabled

If you are using bind variables (DisableBind set to 0) and want the back-end DBMS to set a column to its default value when your application user does not explicitly enter a value in a new row, you should set an initial value for the DataWindow object column that mirrors the DBMS default value for this column.

In the DataWindow painter, you can set or modify a column's initial value in the Column Specifications dialog box.

For more about the Column Specifications dialog box, see the section called “Working in the DataWindow painter” in Users Guide.

Setting a default column value when binding is disabled

If you are not using bind variables (DisableBind set to 1) and want the back-end DBMS to set a column to its default value when your application user does not explicitly enter a value in a new row, you do not need to set an initial value for the DataWindow column.

This is because with bind variables disabled, the DataWindow painter generates a SQL INSERT statement for each row added to the DataWindow object. If a column does not contain an explicit value, it is not included in the SQL INSERT statement.

Using the Order_T table example, if your application user enters 123 as the value for the Order_ID column and A-123 as the value for the Customer_ID column, the DataWindow painter generates the following SQL INSERT statement when DisableBind is set to 1 (binding disabled):

INSERT INTO Order_T(Order_ID, Customer_ID) VALUES(123, 'A-123')

Your back-end DBMS would then set the Order_Date column to its default value as expected, since a value for Order_Date is not explicitly set in the SQL INSERT statement generated by the DataWindow painter.

Examples

To specify that PowerBuilder should disable the binding of input parameters to a compiled SQL statement:

See also

SQLCache

Description

The JDBC driver your application uses to connect to the database.

Applies to

JDB JDBC

Syntax

Driver='driver_name'

Default value

None

Usage

The driver name identifies the Java class name for the particular driver you are using to connect to the database.

Examples

Example 1

To set the driver name of an SAP Sybase jConnect driver:

Example 2

To set the driver name of an Oracle JDBC Driver:

See also

URL

Description

When you access an SAP Adaptive Server Enterprise database in PowerBuilder through Open Client, DS_Alias is one of several parameters that you can set to enable network-based directory services in your application. (For other directory services parameters, see the See Also section.)

Some directory service providers and drivers support the creation of alias entries. An alias entry provides a link to a primary directory entry in a hierarchy, thereby giving users multiple ways to access the primary entry while searching the directory structure for a particular network entity.

For those directory service providers and drivers that support aliases, DS_Alias specifies whether the provider is allowed to follow links for (expand) alias entries while searching the directory hierarchy. The default behavior is to allow expansion of alias entries for providers that support this feature.

You must specify a value for DS_Alias before connecting to the database in PowerBuilder.

Applies to

ASE, SYC SAP Adaptive Server Enterprise

Syntax

DS_Alias=value

Default value

DS_Alias=1

Usage

When to use

To prevent access to your data through directory alias entries, set DS_Alias to 0. This prohibits directory service providers that support aliases from expanding alias entries during a directory search.

Set Release parameter

For this parameter to take effect, you must also set the Release parameter to 11 or higher to specify that your application should use the appropriate version of SAP Sybase Open Client Client-Library (CT-Lib) behavior. See the description of the Release parameter for more information.

Requirements for use

To use DS_Alias or any other parameter supporting Open Client directory services, you must meet certain requirements for using directory services in your PowerBuilder application. For details, see the section called “Requirements for using Open Client directory services” in Connecting to Your Database.

Corresponding CT-Lib connection property

Specifying a value for DS_Alias sets the corresponding SAP Sybase CT-Lib connection property named CS_DS_EXPANDALIAS.

Examples

To prohibit directory service providers that support aliases from expanding alias entries during a directory search:

See also

DS_Copy

DS_DitBase

DS_Failover

DS_Password

DS_Principal

DS_Provider

DS_TimeLimit

Release

Description

When you access an SAP Adaptive Server Enterprise database in PowerBuilder through Open Client, DS_Copy is one of several parameters that you can set to enable network-based directory services in your application. (For other directory services parameters, see the See Also section.)

Some directory service providers and drivers support the use of caching. Caching allows a directory service provider to use cached information while searching a directory instead of making a request to the directory server agent for information.

For those directory service providers and drives that support caching, DS_Copy specifies whether the provider is allowed to use cached information during a directory search. The default behavior is to allow providers that support this feature to use cached information.

You must specify a value for DS_Copy before connecting to the database in PowerBuilder.

Applies to

ASE, SYC SAP Adaptive Server Enterprise

Syntax

DS_Copy=value

Default value

DS_Copy=1

Usage

When to use

Allowing providers to use cached information during directory searches makes the searches faster, but does not ensure that the provider is using the most up-to-date directory information.

To ensure that the application gets the most recent changes to directory entries when it requests directory information, set DS_Copy to 0 to prohibit providers that support caching from using cached information during a directory search.

Set Release parameter

For this parameter to take effect, you must also set the Release parameter to 11 or higher to specify that your application should use the appropriate version of SAP Sybase Open Client Client-Library (CT-Lib) behavior. See the description of the Release parameter for more information.

Requirements for use

To use DS_Copy or any other parameter supporting Open Client directory services, you must meet certain requirements for using directory services in your PowerBuilder application. For details, see the section called “Requirements for using Open Client directory services” in Connecting to Your Database.

Corresponding CT-Lib connection property

Specifying a value for DS_Copy sets the corresponding SAP Sybase CT-Lib connection property named CS_DS_COPY.

Examples

To prohibit directory service providers that support caching from using cached information during a directory search:

See also

DS_Alias

DS_DitBase

DS_Failover

DS_Password

DS_Principal

DS_Provider

DS_TimeLimit

Release

Description

When you access an SAP Adaptive Server Enterprise database in PowerBuilder through Open Client, DS_DitBase is one of several parameters that you can set to enable network-based directory services in your application. (For other directory services parameters, see the See Also section.)

When you use Open Client directory services, a default (active) directory information tree base (DIT base) is specified in the Open Client/Server Configuration utility. The DIT base is the directory node where directory searches start. This is analogous to the current working directory in MS-DOS file systems.

DS_DitBase lets you specify the name of the directory node where you want searches for directory entries to start. The DS_DitBase value you specify must be a fully qualified name that uses the syntax required by your directory service provider and driver (see the Examples section for illustrations).

The default value for DS_DitBase is the DIT base currently specified as active in the Open Client/Open Server Configuration utility.

You must specify a value for DS_DitBase before connecting to the database in PowerBuilder.

Applies to

ASE, SYC SAP Adaptive Server Enterprise

Syntax

DS_DitBase='dit_base'

Default value

The default value for DS_DitBase is the DIT base currently specified as active in the Open Client/Open Server Configuration utility.

Usage

When to use

Set DS_DitBase to specify a starting node for directory searches other than the DIT base node specified as active in the Open Client/Open Server Configuration utility. For instructions on using the Open Client/Open Server Configuration utility, see your SAP Sybase Open Client/Server configuration guide.

Set Release parameter

For this parameter to take effect, you must also set the Release parameter to 11 or higher to specify that your application should use the appropriate version of SAP Sybase Open Client Client-Library (CT-Lib) behavior. See the description of the Release parameter for more information.

Requirements for use

To use DS_DitBase or any other parameter supporting Open Client directory services, you must meet certain requirements for using directory services in your PowerBuilder application. For details, see the section called “Requirements for using Open Client directory services” in Connecting to Your Database.

Corresponding CT-Lib connection property

Specifying a value for DS_DitBase sets the corresponding SAP Sybase CT-Lib connection property named CS_DS_DITBASE.

Examples

About these examples

The examples that follow show how to specify a DS_DitBase value for different directory service providers.

See your directory service provider's documentation for complete information about the format your provider requires for specifying the DIT base.

Example 1 (Windows NT Registry)

This example shows the syntax for DS_DitBase if your directory service provider is the Windows NT Registry:

Node name: SALES:software\sybase\server\SYS11NT

DS_DitBase: SALES:software\sybase\server

To set DS_DitBase:

Example 2 (DCE/CDS)

This example shows the syntax for DS_DitBase if your directory service provider is Distributed Computing Environment Cell Directory Services (DCE/CDS):

Node name: /.../boston.sales/dataservers/sybase/SYS11

DS_DitBase: /.../boston.sales/dataservers

To set DS_DitBase:

Example 3 (Novell NDS)

This example shows the syntax for DS_DitBase if your directory service provider is Novell NetWare Directory Services (NDS):

Node name: CN=SYS11.OU=miami.OU=sales.O=sybase

DS_DitBase: OU=miami.OU=sales.O=sybase

To set DS_DitBase:

See also

DS_Alias

DS_Copy

DS_Failover

DS_Password

DS_Principal

DS_Provider

DS_TimeLimit

Release

Description

When you access an SAP Adaptive Server Enterprise database in PowerBuilder through Open Client, DS_Failover is one of several parameters that you can set to enable network-based directory services in your application. (For other directory services parameters, see the See Also section.)

SAP Sybase Open Client Client-Library (CT-Lib) requires a directory to map logical server names to network addresses. The source for this directory can be either the SAP Sybase Interfaces file or a network-based directory service provider (such as DCE Cell Directory Services or the Windows Registry).

If you want an application to use a directory source other than the Interfaces file, CT-Lib must be able to load the appropriate directory driver. If CT-Lib cannot load the required driver, you can set DS_Failover to specify whether CT-Lib should silently default (fail over) to using the Interfaces file as the directory source.

By default, DS_Failover specifies that CT-Lib should use the Interfaces file as the directory source if it cannot load the requested directory driver.

You must specify a value for DS_Failover before connecting to the database in PowerBuilder.

Applies to

ASE, SYC SAP Adaptive Server Enterprise

Syntax

DS_Failover=value

Default value

DS_Failover=1

Usage

When to use

To prevent CT-Lib from using the Interfaces file as the directory source if it cannot load the requested directory driver, set DS_Failover to 0.

If DS_Failover is set to 0 to prevent use of the Interfaces file and CT-Lib cannot load the requested directory driver, the connection's directory source is undefined. This causes certain operations requiring directory access to fail.

Set Release parameter

For this parameter to take effect, you must also set the Release parameter to 11 or higher to specify that your application should use the appropriate version of SAP Sybase Open Client Client-Library (CT-Lib) behavior. See the description of the Release parameter for more information.

Requirements for use

To use DS_Failover or any other parameter supporting Open Client directory services, you must meet certain requirements for using directory services in your PowerBuilder application. For details, see the section called “Requirements for using Open Client directory services” in Connecting to Your Database.

Corresponding CT-Lib connection property

Specifying a value for DS_Failover sets the corresponding SAP Sybase CT-Lib connection property named CS_DS_FAILOVER.

Examples

To prohibit CT-Lib from using the Interfaces file as the directory source if it cannot load the requested directory driver:

See also

DS_Alias

DS_Copy

DS_DitBase

DS_Password

DS_Principal

DS_Provider

DS_TimeLimit

Release

Description

When you access an SAP Adaptive Server Enterprise database in PowerBuilder through Open Client 12.5 or higher software, DS_Password is one of several parameters that you can set to enable network-based directory services in your application. (For other directory services parameters, see the See Also section.)

Some directory service providers and drivers require an authenticated principal (user ID) name and password to control an application's access to directory entries. For those providers and drivers, DS_Principal and DS_Password specify the principal name and password your application should use to identify you to the directory service provider.

You must specify a value for DS_Password before connecting to the database in PowerBuilder.

Applies to

ASE, SYC SAP Adaptive Server Enterprise

Syntax

DS_Password='password'

Default value

None

PowerBuilder does not set DS_Password or the corresponding SAP Sybase Open Client Client-Library (CT-Lib) connection parameter CS_DS_PASSWORD if you do not specify a value.

Usage

When to use

If your directory service provider requires an authenticated principal name for directory access, set DS_Password to the password that goes with your directory service principal name.

Set Release parameter

For this parameter to take effect, you must also set the Release parameter to 11 or higher to specify that your application should use the appropriate version of SAP Sybase Open Client Client-Library (CT-Lib) behavior. See the description of the Release parameter for more information.

Requirements for use

To use DS_Password or any other parameter supporting Open Client 12.5 directory services, you must meet certain requirements for using directory services in your PowerBuilder application. For details, see the section called “Requirements for using Open Client directory services” in Connecting to Your Database.

Corresponding CT-Lib connection property

Specifying a value for DS_Password sets the corresponding SAP Sybase CT-Lib connection property named CS_DS_PASSWORD.

Examples

To specify MYPASS as your application's password:

See also

DS_Alias

DS_Copy

DS_DitBase

DS_Failover

DS_Principal

DS_Provider

DS_TimeLimit

Release

Description

When you access an SAP Adaptive Server Enterprise database in PowerBuilder through Open Client, DS_Principal is one of several parameters that you can set to enable network-based directory services in your application. (For other directory services parameters, see the See Also section.)

Some directory service providers and drivers require an authenticated principal (user ID) name to control an application's access to directory entries. For those providers and drivers, DS_Principal and DS_Password specify the principal name and password your application should use to identify you to the directory service provider.

You must specify a value for DS_Principal before connecting to the database in PowerBuilder.

Applies to

ASE, SYC SAP Adaptive Server Enterprise

Syntax

DS_Principal='principal_name'

Default value

None

PowerBuilder does not set DS_Principal or the corresponding SAP Sybase Open Client Client-Library (CT-Lib) connection parameter CS_DS_PRINCIPAL if you do not specify a value.

Usage

When to use

If your directory service provider requires an authenticated principal name for directory access, set DS_Principal to the principal (user ID) name that goes with your directory service password.

Set Release parameter

For this parameter to take effect, you must also set the Release parameter to 11 or higher to specify that your application should use the appropriate version of SAP Sybase Open Client Client-Library (CT-Lib) behavior. See the description of the Release parameter for more information.

Requirements for use

To use DS_Principal or any other parameter supporting Open Client directory services, you must meet certain requirements for using directory services in your PowerBuilder application. For details, see the section called “Requirements for using Open Client directory services” in Connecting to Your Database.

Corresponding CT-Lib connection property

Specifying a value for DS_Principal sets the corresponding SAP Sybase CT-Lib connection property named CS_DS_PRINCIPAL.

Examples

To specify JSMITH as your application's principal name:

See also

DS_Alias

DS_Copy

DS_DitBase

DS_Failover

DS_Password

DS_Provider

DS_TimeLimit

Release

Description

When you access an SAP Adaptive Server Enterprise database in PowerBuilder through Open Client, DS_Provider is one of several parameters that you can set to enable network-based directory services in your application. (For other directory services parameters, see the See Also section.)

When you use Open Client directory services, you must specify your directory service provider names in the Open Client/Open Server Configuration utility so that the required drivers can be loaded for each provider. The default directory service provider is the one currently specified as active in the Configuration utility.

DS_Provider lets you specify a directory service provider name listed in the Open Client/Open Server Configuration utility other than the default (active) provider. The default value for DS_Provider is the provider name currently specified as active in the Configuration utility.

You must specify a value for DS_Provider before connecting to the database in PowerBuilder.

Applies to

ASE, SYC SAP Adaptive Server Enterprise

Syntax

DS_Provider='provider_name'

Default value

The default value for DS_Provider is the provider name currently specified as active in the Open Client/Open Server Configuration utility.

Usage

When to use

Set DS_Provider to use a directory service provider specified in the Open Client/Open Server Configuration utility other than the default (active) provider. For instructions on using the Open Client/Open Server Configuration utility, see your SAP Sybase Open Client/Server configuration guide.

Set Release parameter

For this parameter to take effect, you must also set the Release parameter to 11 or higher to specify that your application should use the appropriate version of SAP Sybase Open Client Client-Library (CT-Lib) behavior. See the description of the Release parameter for more information.

Requirements for use

To use DS_Provider or any other parameter supporting Open Client directory services, you must meet certain requirements for using directory services in your PowerBuilder application. For details, see the section called “Requirements for using Open Client directory services” in Connecting to Your Database.

Corresponding CT-Lib connection property

Specifying a value for DS_Provider sets the corresponding SAP Sybase CT-Lib connection property named CS_DS_PROVIDER.

Examples

To specify NTREGISTRY as the directory service provider name:

See also

DS_Alias

DS_Copy

DS_DitBase

DS_Failover

DS_Password

DS_Principal

DS_TimeLimit

Release

Description

When you access an SAP Adaptive Server Enterprise database in PowerBuilder through Open Client, DS_TimeLimit is one of several parameters that you can set to enable network-based directory services in your application. (For other directory services parameters, see the See Also section.)

Some directory service providers and drivers support the use of time limits for a directory search. For those providers and drivers, DS_TimeLimit specifies the maximum number of seconds that a directory search lasts.

By default, DS_TimeLimit specifies that there is no time limit for a directory search.

You must specify a value for DS_TimeLimit before connecting to the database in PowerBuilder.

Applies to

ASE, SYC SAP Adaptive Server Enterprise

Syntax

DS_TimeLimit='value'

Default value

DS_TimeLimit='no_limit'

Usage

Set Release parameter

For DS_TimeLimit to take effect, you must also set the Release parameter to 11 or higher to specify that your application should use the appropriate version of SAP Sybase Open Client Client-Library (CT-Lib) behavior. See the description of the Release parameter for more information.

Requirements for use

To use DS_TimeLimit or any other parameter supporting Open Client directory services, you must meet certain requirements for using directory services in your PowerBuilder application. For details, see the section called “Requirements for using Open Client directory services” in Connecting to Your Database.

Corresponding CT-Lib connection property

Specifying a value for DS_TimeLimit sets the corresponding SAP Sybase CT-Lib connection property named CS_DS_TIMELIMIT.

Examples

To specify that you want the directory search to last a maximum of 120 seconds (2 minutes):

See also

DS_Alias

DS_Copy

DS_DitBase

DS_Failover

DS_Password

DS_Principal

DS_Provider

Release

Description

Specifies that data should be encrypted before sending it over the network.

Applies to

SNC SQL Native Client for Microsoft SQL Server

MSOLEDBSQL Microsoft OLE DB Driver for SQL Server

Syntax

Encrypt=value

Default value

Encrypt=0

Usage

SQL Server 2005 always encrypts network packets associated with logging in to the server. If no certificate is provided on the server when it starts up, SQL Server generates a self-signed certificate that is used to encrypt login packets.

SQL Server Configuration Manager can be used to configure the SQL Native Client to request an encrypted connection using the Secure Sockets Layer (SSL), and to accept a self-signed certificate without validation. You can also request encryption by setting the Encrypt DBParm to 1, which sets the SQL Native Client connection string keyword Encrypt. To enable encryption to be used when a certificate has not been provided on the server, set both Encrypt and TrustServerCertificate. The value of TrustServerCertificate is ignored if Encrypt is not set.

Examples

To specify that PowerBuilder should encrypt data:

See also

TrustServerCertificate

Description

Specifies a password for encrypting and decrypting data.

Applies to

I10 Informix

Syntax

EncryptionPass='value'

Default value

None.

Usage

In IDS 10.0 and later, the SQL statement SET ENCRYPTION PASSWORD can improve the confidentiality of data and support data integrity by defining or resetting a password for encryption and decryption of data at the column level.

You can set the EncryptionPass and Hint static DBParms on the System tab page in the Database Profile Setup dialog box for I10 connections to specify a password and an optional hint to help you remember the password. The password does not display in the database Profile Setup dialog box and is encrypted in the database profile in the registry. The application uses built-in Informix functions to encrypt and decrypt character data.

Examples

To specify Archimedes as the password for data encryption and Eureka as the hint to help you remember the password:

See also

Hint

Description

Specifies whether you want PowerBuilder to encrypt your password automatically when connecting to an OLE DB data provider.

Applies to

OLE DB

Syntax

EncryptPassword='value'

Default value

EncryptPassword='False'

Examples

To tell PowerBuilder to encrypt your password when connecting to Microsoft SQL Server or an OLE DB data provider:

See also

DataLink

IntegratedSecurity

MaskPassword

PersistEncrypted

Description

Specifies the name of a mirror server, allowing you to maintain database availability if a failover event occurs.

Applies to

SNC SQL Native Client for Microsoft SQL Server

MSOLEDBSQL Microsoft OLE DB Driver for SQL Server

Syntax

FailoverPartner =value

Default value

None

Usage

When failover occurs, the existing PowerBuilder connection to SQL Server is lost. The SNC driver releases the existing connection and tries to reopen it. If reconnection succeeds, PowerBuilder triggers the DBNotification event.

The following conditions must be satisfied for PowerBuilder to trigger the failover event:

When failover occurs:

Examples

The following example sets the name of a mirror server for failover events:

Description

Specifies the amount of time (in milliseconds) you want PowerBuilder to wait between attempts to fail over to another database server if the current database server goes down.

Applies to

O90 Oracle9i

O10 Oracle 10g

ORA Oracle (for 11g and later)

Syntax

FoDelay='value'

Default value

FoDelay='10'

Usage

You can enter a failover delay value only if you have enabled failover.

This parameter cannot be set dynamically. The value set when the connection is made remains in effect until it is disconnected.

Examples

To tell PowerBuilder to wait 20 milliseconds between attempts to fail over:

See also

SvrFailover

Description

Specifies whether PowerBuilder displays a runtime dialog box indicating when a failover occurs.

Applies to

ASE, SYC SAP Adaptive Server Enterprise

O90 Oracle9i

O10 Oracle 10g

ORA Oracle (for 11g and later)

Syntax

FoDialog='value'

Default value

FoDialog='No'

Usage

You can display a runtime dialog box only if you have enabled failover. The dialog box does not display in COM+.

This parameter cannot be set dynamically. The value set when the connection is made remains in effect until it is disconnected.

Examples

To tell PowerBuilder to display a runtime dialog box when a failover occurs:

See also

SvrFailover

Description

Specifies the number of times you want PowerBuilder to try to fail over to an another database server if the current database server goes down.

Applies to

O90 Oracle9i

O10 Oracle 10g

ORA Oracle (for 11g and later)

Syntax

FoRetryCount='value'

Default value

FoRetryCount='10'

Usage

You can enter a failover retry value only if you have enabled failover.

This parameter cannot be set dynamically. The value set when the connection is made remains in effect until it is disconnected.

Examples

To tell PowerBuilder to try 20 times to fail over:

See also

SvrFailover

Description

Controls whether PowerBuilder converts a DataWindow object retrieval argument of decimal datatype to scientific (exponential) notation if the argument exceeds 12 digits but has fewer than 16 digits. If FormatArgsAsExp is set to Yes (the default), PowerBuilder performs this conversion.

Applies to

ASE and SYC, SAP Adaptive Server Enterprise

DIR SAP DirectConnect

JDB JDBC

ODBC interface

O90 Oracle9i

O10 Oracle 10g

ORA Oracle (for 11g and later)

Syntax

FormatArgsAsExp= 'value'

Default value

FormatArgsAsExp='No'

Usage

When to use

The setting of FormatArgsAsExp might affect the speed of data retrieval in your DataWindow objects, especially if you are accessing large databases.

If FormatArgsAsExp is set to Yes, PowerBuilder converts retrieval arguments of type decimal to scientific notation if the argument exceeds 12 digits but has fewer than 16 digits. Some DBMS optimizers might interpret the resulting scientific notation as a different datatype and scan all rows in the table to find it. This can slow data retrieval if, for example, you are accessing a DB2 database with many large tables.

Setting FormatArgsAsExp to No tells PowerBuilder to leave the retrieval argument as a decimal and not convert it to scientific notation. This speeds data retrieval for large databases.

Examples

To tell PowerBuilder to convert a retrieval argument exceeding 12 digits but with fewer than 16 digits to scientific notation:

Description

Specifies how DataWindows generate =null and <> null expressions in retrieval arguments.

Applies to

All database interfaces

Syntax

GenerateEqualsNull= 'value'

Default value

GenerateEqualsNull='No'

Usage

When to use

When a DataWindow retrieves data from tables that contain null columns, most DBMS interfaces expect expressions of the form, IS NULL and IS NOT NULL. Expressions that reference null data as values, such as Where column = NULL, can cause the DBMS to reject the retrieval. For this reason, such expressions in DataWindows are normally converted to the standard ANSI syntax during retrieval.

If your DataWindow retrieves null data from a DBMS that supports expressions of the =null or <> null form, and you want to suppress the conversion of those expressions to standard syntax, you can set the GenerateEqualsNull DBParm to true.

Examples

Consider these two statements:

SELECT "a1"."c1"  FROM "a1"  WHERE "a1"."c2" = :p1
SELECT "a1"."c1"  FROM "a1"  WHERE "a1"."c2" <> :p1

If GenerateEqualsNull is set to false, the statements are generated as:

SELECT "a1"."c1"  FROM "a1"  WHERE "a1"."c2" is null
SELECT "a1"."c1"  FROM "a1"  WHERE "a1"."c2" is not null

If GenerateEqualsNull is set to true, the statements are generated as:

SELECT "a1"."c1"  FROM "a1"  WHERE "a1"."c2" = null
SELECT "a1"."c1"  FROM "a1"  WHERE "a1"."c2" <> null

Description

Specifies how EAServer should behave if all connections in a cache are being used. This parameter applies only when a PowerBuilder custom class user object is deployed as an EAServer component.

Applies to

JDB JDBC

ODBC

O90 Oracle9i

O10 Oracle 10g

ORA Oracle (for 11g and later)

Syntax

GetConnectionOption='value'

Default value

GetConnectionOption='JAG_CM_FORCE'

Usage

This parameter cannot be set dynamically. The value set when the connection is made remains in effect until it is disconnected.

Examples

On the EAServer page in the Database Profile Setup dialog box, select JAG_CM_NOWAIT from the Get Connection Option drop-down list. The PowerScript syntax for the GetConnectionOption parameter displays on the Preview page:

SQLCA.DBParm="GetConnectionOption='JAG_CM_NOWAIT'"

Copy the syntax from the Preview page into your script.

See also

CacheName (obsolete)

ReleaseConnectionOption (obsolete)

UseContextObject (obsolete)

Description

Specifies whether a High Availability (HA) client connected to an RAC database will be notified if the database server shuts down.

Applies to

O10 Oracle 10g

Syntax

HANotification=value

Default value

HANotification='No'

Usage

Oracle Real Application Clusters (RAC) is a cluster database that uses a shared cache architecture. In Oracle 10g Release 2, a High Availability (HA) client connected to an RAC database can register a callback to indicate that it wants the server to notify it in case of a database failure event that affects a connection made by the client. The DBNotification event on the Transaction object is triggered when the client is notified that such an event has occurred.

The HANotification database parameter must be set to 'Yes' for the DBNotification event to be triggered when the database server shuts down. If HANotification is set to 'Yes', the Oracle client and server must both be Oracle 10g version 10.2.0.1.0 or higher or the connection will fail.

This database parameter is not supported by MTS/COM+ and will be ignored in PowerBuilder component connections on MTS/COM+.

Examples

To specify that the HA client should be notified when the database server shuts down:

Description

Determines whether the first error or the highest severity error is reported when more than one error is raised during the connection or attempted connection to the database

Applies to

SNC SQL Native Client for Microsoft SQL Server

MSOLEDBSQL Microsoft OLE DB Driver for SQL Server

Syntax

HigSeverityError=value

Default value

HighSeverityError=0

Usage

When you set this DBParm to 1, PowerBuilder chooses the error with the highest severity code to pass back to the caller.

Examples

To have PowerBuilder report the error with the highest severity code, use the following DBParm setting:

SQLCA.DBParm="HighServerityError=1"

Description

Specifies a hint to assist in remembering the password specified for encrypting and decrypting data.

Applies to

I10 Informix

Syntax

Hint='value'

Default value

None.

Usage

In IDS 10.0 and later, the SQL statement SET ENCRYPTION PASSWORD can improve the confidentiality of data and support data integrity by defining or resetting a password for encryption and decryption of data at the column level.

You can set the EncryptionPass and Hint static DBParms on the System tab page in the Database Profile Setup dialog box for I10 connections to specify a password and an optional hint to help you remember the password. The password does not display in the database Profile Setup dialog box and is encrypted in the database profile in the registry. The application uses built-in Informix functions to encrypt and decrypt character data.

Examples

To specify Archimedes as the password for data encryption and Eureka as the hint to help you remember the password:

See also

EncryptionPass

Description

If your DBMS supports it, specifies the workstation name when connecting to the database in PowerBuilder. The Host parameter lets you assign any 10-character label to identify the process you are about to create when you connect to the database. This label helps you distinguish your process from others running on the database server.

Applies to

ASE, SYC SAP Adaptive Server Enterprise

SNC SQL Native Client for Microsoft SQL Server

MSOLEDBSQL Microsoft OLE DB Driver for SQL Server

Syntax

Host='workstation_name'

Default value

None

Usage

For Adaptive Server, when you specify a value for Host, PowerBuilder sets the CS_HOSTNAME connection property to the workstation name you specify.

The value you specify for the Host parameter displays in the hostname column of the MASTER.DBO.SYSPROCESSES table in a SQL Server database. How you use the Host parameter depends on the design of your PowerBuilder application.

For example, many sites want to secure their production tables so that updates are possible only through a specific application. To do this, you can grant explicit authority to the PowerBuilder application but not to users. The application prompts the user for an authorization ID and password, verifies it, and then connects to the database through a single application login ID. Only this application login ID has authorization to update production tables.

In this scenario, you can use the Host parameter to store the name of the user running the application.

Examples

Example 1

To set the host name to Alan:

Example 2

You can use the Host and AppName parameters together to specify both the host name and the application name. To set the host name to Jane and the application name to Sales:

Example 3

The Host name in the preceding examples is hard coded. You can get the name dynamically using the Windows GetComputerNameW function. There is no PowerScript equivalent for this function. Here is the external function declaration:

FUNCTION boolean GetComputerNameW(ref string cname,ref long nbuf) LIBRARY "Kernel32.dll" 

The following code in the Open event of the application uses an external function call to get the host name and set its value in the Host parameter. You must allocate sufficient space for the returned host name:

string ls_compname
long ll_buf
ll_buf=25

ls_compname=space(ll_buf)
GetComputerNameA(ls_compname, ll_buf) 

// Profile mysyb 
SQLCA.DBMS="SYC Adaptive Server Enterprise" 
SQLCA.Database="mydata" 
SQLCA.LogPass="mylogpass" 
SQLCA.ServerName="mysybsvr" 
SQLCA.LogId="mylogid" 
SQLCA.AutoCommit=False
SQLCA.DBParm="Host='" + ls_compname + "'" 

Connect using SQLCA; 

See also

AppName

Description

Specifies the name of the host request library defined in a DB2/MVS database.

Applies to

DIR SAP DirectConnect (applies only to Access Service for DB2/MVS and Open ServerConnect)

Syntax

HostReqOwner='owner_id'

Default value

HostReqOwner='SAP'

Usage

The host request library is a special DB2 table that stores host-resident requests. A host-resident request is a SQL statement that a client application can execute as a procedure. If you do not use SAP as the owner name for this host request library, you should set the HostReqOwner parameter to an appropriate name for your site.

Examples

To set the name of your host request library to Stratus:

See also

TRS

UseProcSyntax

Description

Specifies the single quote character you want PowerBuilder to use to delimit the names of identifiers (tables, columns, indexes, and constraints) when it generates SQL statements. PowerBuilder uses the quote character you specify instead of the default quote character returned by your driver or data provider.

Applies to

ADO.NET

JDB JDBC

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

OLE DB

Syntax

IdentifierQuoteChar = 'quote_character'

Default value

None

PowerBuilder searches the following in this order to determine the IdentifierQuoteChar value:

If PowerBuilder does not find an IdentifierQuoteChar value in these locations, it makes a SQLGetInfo call to your driver to return the default SQL_IDENTIFIER_QUOTE_CHAR value.

Usage

By default, some drivers return quote characters that do not work with PowerBuilder's parsing routines, such as the backquote character (`). As a result, delimiting is turned off for these drivers in PowerBuilder.

However, if you paint SQL statements containing identifiers that require delimiters, syntax errors can occur if you are using a driver for which delimiting is turned off. To avoid such errors, set IdentifierQuoteChar to override the driver's default quote character.

Examples

To specify c as the quote character you want PowerBuilder to use to delimit identifiers in SQL statements:

See also

DelimitIdentifier

Description

Specifies the syntax the SNC database interface uses to obtain the identity value of a new row in a database table.

When a DataWindow update request inserts a new row into a Microsoft SQL Server table that contains an IDENTITY column, the DataWindow engine calls the SNC interface to obtain the identity value of the newly inserted row. The Identity database parameter allows you to define how this request is implemented.

Applies to

SNC SQL Native Client for Microsoft SQL Server

MSOLEDBSQL Microsoft OLE DB Driver for SQL Server

Syntax

Identity=value

Default value

Identity='@@IDENTITY'

Usage

By default, the SNC interface issues SELECT @@identity to obtain the IDENTITY column value of the newly inserted row. It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.

SELECT IDENT_CURRENT('tablename') returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.

SELECT SCOPE_IDENTITY() returns the last IDENTITY value produced on a table and by a statement in the same scope, regardless of the table that produced the value.

Because Identity is a dynamic database parameter, it can be set and reset at any time during an application.

Examples

To specify the syntax for obtaining the identity value of a newly inserted row:

Description

Specifies the level of impersonation that the data server is allowed to use when impersonating its OLE DB data provider and PowerBuilder. This parameter applies only to network connections other than Remote Procedure Call (RPC) connections.

Applies to

OLE DB

Syntax

ImpersonationLevel='value'

Default value

ImpersonationLevel='Not set'

Examples

To set a level of impersonation to anonymous:

See also

DataLink

Description

Specifies the Informix DBPATH setting. The DBPATH environment variable identifies a list of directories that contain Informix databases. INET_DBPATH typically specifies the location of Informix databases if this is other than in a directory on the database server.

Applies to

I10 Informix

IN9 Informix

Syntax

INET_DBPATH='server_db_path'

Default value

By default, PowerBuilder uses the value specified for DBPATH in the HKEY_LOCAL_MACHINE\SOFTWARE\Informix\Environment registry key.

Examples

Example 1