Enhancements for the ADO.NET Interface

The PowerBuilder 12 ADO interface has been extended to support ADO.NET providers. Now, you can connect at runtime to any data source that adheres to the ADO.NET 2.0 Common Provider model.

ADO .NET Provider

Enhancements for the ADO.NET Interface

ADO.NET for Oracle

ODP.NET Driver Updates Drivers for these ODP.NET versions are updated:

  • For Oracle 10g, the Oracle.DataAccess.dll driver was upgraded from Version 10.1.0.301 to 2.102.2.20

  • For Oracle 11, the Oracle.DataAccess.dll Version 2.111.6.20 driver was added

Both drivers are ADO.NET 2.0 compatible.

The PowerBuilder ADO.NET interface no longer includes a driver for Oracle ODP.NET 9i. Users of that provider should migrate to Oracle ODP.NET 10g.

New Features of ODP.NET 2.0 for Oracle 10.2 and Earlier

  • Client Identifier: The client identifier is a predefined attribute for the Oracle application context namespace, USERENV. Like proxy authentication, the client identifier enables tracking user identities. However, unlike proxy authentication, the client identifier does not require separate sessions for the proxy user and end user. Also, the client identifier does not need to be a database user, and can be set to any string. Most important, the client identifier enables ODP.NET developers to use application context and Oracle Label Security, and to configure an Oracle Virtual Private Database (VPD) more easily. Configure the client identifier for Oracle ADO.NET data providers in the Driver Specific tab of the Database Profile Setup dialog.

  • Connection Pool Optimizations for RAC Databases: An Oracle Data Provider for ADO.NET optimizes connection pooling for Real Application Cluster (RAC) databases by balancing work requests across Oracle RAC instances, based on load balancing advisory and service requirements. In addition, the ODP.NET connection pool can be enabled to proactively free resources associated with connections that have been severed when an Oracle RAC service, instance, or node goes down. Specify ODP.NET connection pool optimizations as arguments to the ProviderString DBParm parameter. You can enter driver-specific parameters at the bottom of the Connection tab of the Database Profile Setup dialog.

  • Large Object Retrieval: You can retrieve entire columns of large object (LOB) data even if the select list does not contain a primary key, row id, or unique key. To use this enhancement, set the InitialLOBFetchSize property value to -1 for CLOB and BLOB objects.

  • LONG Retrieval: You can retrieve entire columns of LONG and LONGRAW data even if the select list does not contain a primary key, row id, or unique key. To use this enhancement, set the InitialLONGFetchSize property value to -1.

  • XMLType: The Oracle XMLType datatype is mapped to the PowerBuilder string type, with these limitations:

    • XMLType cannot be used in Where clauses within PowerBuilder Embedded SQL statements or in a DataWindow object.

    • XMLType columns cannot be selected directly by an Oracle cursor.

    • XMLType cannot be a parameter of a procedure or function, because PowerBuilder binds XMLType as a string type, but Oracle does not support that usage.

  • Client Access Through a Proxy: 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 Connection tab of the Database Profile Setup dialog provides a Connect As dropdown control. To create a proxy connection, enter a different value that is not one of the predefined control items (Default, SYSOPER, and SYSDBA).

  • Transparent Application Failover Notification: Transparent Application Failover (TAF) notification enables an application connection to automatically reconnect to another database instance if the connection is severed. When a failover occurs, applications may wish to be notified. A new DBParm, SvrFailover, supports TAF notification. By default, SvrFailover is set to 0. If SvrFailover is set to 1 (true or yes), the transaction object invokes the DBNotification event when a failover occurs.

New Features for ODP.NET 2.0 for Oracle 11g

  • ODP.NET Configuration: Developers can now configure ODP.NET using configuration files, including the .NET application configuration file, web.config, and machine.config. Settings in the machine.config file override the registry settings. The settings in the application configuration file or the web.config file overrides the values in the machine.config file.

  • Additional Connection Pool Optimizations for RAC and Data Guard: ODP.NET now cleans up the connection pool when the database down event is received from Real Application Clusters (RAC) or Oracle Data Guard. This is in addition to the events for which ODP.NET previously cleaned up the connection pool: node down, service member down, and service down.

  • Windows-Authenticated User Connection Pooling: You can now manage operating system-authenticated connections as part of ODP.NET connection pools, through Windows account management.

  • Connection Pool Performance Counters: ODP.NET publishes performance counters for connection pooling, which can be viewed using the Windows Performance Monitor. For PowerBuilder, the counters can be set in the Windows registry or in the application configuration file.

The following ADO.NET 1.1 features are not supported:

  • Oracle User-Defined Types: PowerBuilder does not support UDT types.

  • Bulk Copy Operations: ADO.NET 1.1 enables applications to efficiently load large amounts of data from a table in one database to another table in the same or a different database. PowerBuilder does not support bulk copies; instead it uses pipelines for table copy operations.

ADO.NET for Adaptive Server Enterprise

Drivers for these ADO.NET versions are updated:

  • The ASE 12.5x ADO.NET driver, Sybase.Data.AseClient.dll, is updated from Version 1.1.411.0 to 1.1.670.0. The ASE 12.5x ADO.NET driver is ADO.NET 1.1 compatible, and does not support ADO.NET 2.0.

  • The ASE 15 ADO.NET driver is updated from Sybase.Data.AseClient.dll Version 1.15.50.0 to Sybase.AdoNet2.AseClient.dll 1.15.325.0. The ASE 15 ADO.NET driver is ADO.NET 2.0 compatible.

New Features for ASE 15

The ASE 15 ADO.NET driver supports these new ASE identity types:

  • Bigint identity

  • Int identity

  • SmallInt identity

  • Tinyint identity

  • unsigned bigint identity

  • unsigned int identity

  • unsigned smallint identity

ADO.NET for Microsoft SQL Server

New Features for SQL Server 2005 and Earlier

  • Large value types:

    • varchar(max)

    • nvarchar(max)

    • varbinary(max)

  • xml, varchar(max) and nvarchar(max) are mapped to the PowerBuilder string type; varbinary(max) is mapped to the PowerBuilder blob type.

  • PowerBuilder supports SQL Server database mirroring, and a DBNotification event is fired when failover occurs. A new DBParm parameter, FailoverPartner, enables you to set the SQL Server failover partner server, as in the SQL Native Client (SNC) interface

  • Query notifications are not supported by the PowerBuilder ADO interface for SQL Server.

New Features for SQL Server 2008

The following SQL Server 2008 features are supported:

  • New datatypes:

    • date

    • time

    • datetime2

    • varbinary(max)(filestream)

The SQL Server date, time and datetime2 datatypes are mapped to PowerBuilder date, time and datetime types. varbinary(max) (filestream ) is mapped to the PowerBuilder blob type. The maximum scale of time or datetime2 is 6.

  • The new T-SQL commands support:

    • MERGE statement

    • Grouping sets

    • Row constructors

    • Table hints

    • The new T-SQL command works in the PowerBuilder ADO interface for SQL Server, as in the SNC interface.

These SQL Server 2008 featues are not supported:

  • datetimeoffset datatype

  • Table-valued parameters

ADO.NET for SQL Anywhere

Connect to a SQL Anywhere database using an iAnywhere.Data.SQLAnywhere provider. PowerBuilder applications can perform all database related operations, such as exploring SQL Anywhere database objects like tables and procedures, and retrieving and updating data in the Database Painter.

ADO.NET for Informix

The Informix DATETIME HOUR TO SECOND type is treated as type TIME in PowerBuilder. Also, the TIME type column is displayed in the Database Painter as DATETIME, because the two variants of Informix DATETIME type are indistinguishable in the resultset schema. The IBM.Data.Informix driver does not support the BindSPInput dbparm.

ADO.NET for DB2

PowerBuilder supports DB2 using the System.Data.Odbc provider for both runtime and design time operations.