Table of Contents

Introduction to Database Connections

Understanding Data Connections

How to find the information you need

Accessing data in PowerBuilder

Accessing the Demo Database

Using database profiles

About creating database profiles

Creating a database profile

What to do next

Working with Standard Database Interfaces

Using the ODBC Interface

About the ODBC interface

What is ODBC?

Using ODBC in PowerBuilder

Components of an ODBC connection

Types of ODBC drivers

Ensuring the proper ODBC driver conformance levels

Obtaining ODBC drivers

Using ODBC drivers with PowerBuilder

Getting help with ODBC drivers

Preparing ODBC data sources

Defining ODBC data sources

How PowerBuilder accesses the data source

Defining multiple data sources for the same data

Displaying Help for ODBC drivers

Selecting an ODBC translator

Defining the ODBC interface

SAP SQL Anywhere

Supported versions for SQL Anywhere

Basic software components for SQL Anywhere

Preparing to use the SQL Anywhere data source

Defining the SQL Anywhere data source

Support for Transact-SQL special timestamp columns

What to do next

PostgreSQL

Limited support for stored procedure

Support for auto-increment column

Using the JDBC Interface

About the JDBC interface

What is JDBC?

Using the JDBC interface

Components of a JDBC connection

JDBC registry entries

Supported versions for JDBC

Supported JDBC datatypes

Preparing to use the JDBC interface

Defining the JDBC interface

Using the OLE DB Interface

About the OLE DB interface

What is OLE DB?

Components of an OLE DB connection

Obtaining OLE DB data providers

Supported versions for OLE DB

Preparing to use the OLE DB interface

Defining the OLE DB interface

Using the ADO.NET Interface

About ADO.NET

About the PowerBuilder ADO.NET database interface

Components of an ADO.NET connection

OLE DB data providers

Preparing to use the ADO.NET interface

Defining the ADO.NET interface

Getting identity column values

Sharing ADO.NET Database Connections

Importing an ADO.NET Connection from a Third-Party .NET Assembly

Exporting an ADO.NET Connection to a Third-Party .NET Assembly

Using the OData Interface (Deprecated)

Database Profile Setup - OData Dialog Box

Connection Tab

Certificate Tab

Proxy Server Tab

Preview Tab

Database Painter

Create a DataWindow Using an OData Service

Set the Connection Information for the OData Service

A known issue with OData

Working with Native Database Interfaces

Using Native Database Interfaces

About native database interfaces

Components of a database interface connection

Using a native database interface

Using Adaptive Server Enterprise

Supported versions for Adaptive Server

Supported Adaptive Server datatypes

Basic software components for Adaptive Server

Preparing to use the Adaptive Server database

Defining the Adaptive Server database interface

Using Open Client security services

What are Open Client security services?

Requirements for using Open Client security services

Security services DBParm parameters

Using Open Client directory services

What are Open Client directory services?

Requirements for using Open Client directory services

Specifying the server name with Open Client directory services

Directory services DBParm parameters

Using PRINT statements in Adaptive Server stored procedures

Creating a DataWindow object based on a cross-database join

Installing stored procedures in Adaptive Server databases

What are the PowerBuilder stored procedure scripts?

How to run the scripts

Using Informix

Supported versions for Informix

Supported Informix datatypes

Informix DateTime datatype

Informix Time datatype

Informix Interval datatype

Features supported by the I10 interface

Accessing Unicode data

Assigning an owner to the PowerBuilder catalog tables

Support for long object names

Renaming an index

SQL statement caching

Creating and dropping indexes without locking

Column-level encryption

Using multiple OUT parameters in user-defined routines

Basic software components for Informix

Preparing to use the Informix database

Defining the Informix database interface

Specifying the server name

Accessing serial values in a PowerBuilder script

Using Microsoft SQL Server

Supported versions for SQL Server

Supported SQL Server datatypes

Basic software components for Microsoft SQL Server

Preparing to use the SQL Server database

Defining the SQL Server database interface

Migrating from the MSS or OLE DB database interfaces

SQL Server 2008 features

New database parameters

Support for new datatypes in SQL Server 2008

T-SQL enhancements

Unsupported SQL Server 2008 features

Notes on using the SNC interface

Using Oracle

Supported versions for Oracle

Supported Oracle datatypes

Datatype conversion

Basic software components for Oracle

Preparing to use the Oracle database

Defining the Oracle database interface

Specifying the Oracle server connect descriptor

Using Oracle stored procedures as a data source

What is an Oracle stored procedure?

What you can do with Oracle stored procedures

Using Oracle stored procedures with result sets

Using a large-object output parameter

RPC calls to stored procedures with array parameters

Using Oracle user-defined types

Support for HA event notification

ORA driver support for Oracle 11g features

Using DirectConnect

Using the DirectConnect interface

Connecting through the DirectConnect middleware product

Connecting through the Open ServerConnect middleware product

Selecting the type of connection

Supported versions for the DirectConnect interface

Supported DirectConnect interface datatypes

Basic software components for the DirectConnect interface

Preparing to use the database with DirectConnect

Defining the DirectConnect interface

Creating the extended attribute system tables in DB2 databases

Creating the extended attribute system tables

Using the DB2SYSPB.SQL script

Working with Database Connections

Managing Database Connections

About database connections

When database connections occur

Using database profiles

Connecting to a database

Selecting a database profile

What happens when you connect

Specifying passwords in database profiles

Using the Preview tab to connect in a PowerBuilder application

Maintaining database profiles

Sharing database profiles

About shared database profiles

Setting up shared database profiles

Using shared database profiles to connect

Making local changes to shared database profiles

Maintaining shared database profiles

Importing and exporting database profiles

About the PowerBuilder extended attribute system tables

Logging in to your database for the first time

Displaying the PowerBuilder extended attribute system tables

Contents of the extended attribute system tables

Controlling system table access

Setting Additional Connection Parameters

Basic steps for setting connection parameters

About the Database Profile Setup dialog box

Setting database parameters

Setting database parameters in the development environment

Setting database parameters in a PowerBuilder application script 

Setting database preferences

Setting database preferences in the development environment

Setting AutoCommit and Lock in a PowerBuilder application script

Troubleshooting Your Connection

Troubleshooting Your Connection

Overview of troubleshooting tools

Using the Database Trace tool

About the Database Trace tool

Starting the Database Trace tool

Stopping the Database Trace tool

Using the Database Trace log

Sample Database Trace output

Using the SQL statement trace utility

Using the ODBC Driver Manager Trace tool

About ODBC Driver Manager Trace

Starting ODBC Driver Manager Trace

Stopping ODBC Driver Manager Trace

Viewing the ODBC Driver Manager Trace log

Sample ODBC Driver Manager Trace output

Using the JDBC Driver Manager Trace tool

About JDBC Driver Manager Trace

Starting JDBC Driver Manager Trace

Stopping JDBC Driver Manager Trace

Viewing the JDBC Driver Manager Trace log

Using Embedded SQL

Using Embedded SQL with ODBC

ODBC SQL Support

ODBC Name qualification

ODBC SQL functions

DBHandle

ODBC Using escape clauses

ODBC Transaction management statements

ODBC Using CONNECT, DISCONNECT, COMMIT, and ROLLBACK

ODBC Performance and locking

ODBC Non-cursor statements

ODBC DELETE, INSERT, and UPDATE

ODBC SELECT

ODBC Cursor statements

ODBC Retrieval using cursors

ODBC FETCH NEXT

ODBC FETCH FIRST, FETCH PRIOR, and FETCH LAST

ODBC Update

ODBC Database stored procedures

ODBC Retrieval

ODBC DECLARE and EXECUTE

ODBC DECLARE and EXECUTE with PBNewSPInvocation

ODBC FETCH

ODBC CLOSE

ODBC EXECUTE

ODBC Using database stored procedures in DataWindow objects

Using Embedded SQL with JDBC

JDBC DECLARE and EXECUTE

Using Embedded SQL with OLE DB

OLE DB SQL support

OLE DB Name qualification

OLE DB SQL functions

OLE DB Using ODBC escape Sequences

OLE DB Transaction management statements

OLE DB Using CONNECT, DISCONNECT, COMMIT, and ROLLBACK

OLE DB Performance and locking

OLE DB Non-cursor statements

OLE DB DELETE, INSERT, and UPDATE

OLE DB SELECT

OLE DB Cursor statements

OLE DB Retrieval using cursors

OLE DB FETCH NEXT

OLE DB Database stored procedures

OLE DB Retrieval

OLE DB DECLARE and EXECUTE

OLE DB FETCH

OLE DB CLOSE

OLE DB EXECUTE

OLE DB Using database stored procedures in DataWindow objects

Using Embedded SQL with ADO.NET

ADO.NET DECLARE and EXECUTE

Using Embedded SQL with SAP Adaptive Server Enterprise

SAP Adaptive Server Enterprise Name qualification

SAP Adaptive Server Enterprise SQL functions

SAP Adaptive Server Enterprise Transaction management statements

SAP Adaptive Server Enterprise Using CONNECT, COMMIT, DISCONNECT, and ROLLBACK

SAP Adaptive Server Enterprise Using AutoCommit

SAP Adaptive Server Enterprise Performance and locking

SAP Adaptive Server Enterprise Non-cursor statements

SAP Adaptive Server Enterprise DELETE, INSERT, and UPDATE

SAP Adaptive Server Enterprise SELECT

SAP Adaptive Server Enterprise Cursor statements

SAP Adaptive Server Enterprise Retrieval Using Cursors

SAP Adaptive Server Enterprise Closing the Cursor

SAP Adaptive Server Enterprise Database stored procedures

SAP Adaptive Server Enterprise Retrieval

SAP Adaptive Server Enterprise DECLARE and EXECUTE

SAP Adaptive Server Enterprise FETCH

SAP Adaptive Server Enterprise CLOSE

SAP Adaptive Server Enterprise Update

SAP Adaptive Server Enterprise Return values and output parameters

SAP Adaptive Server Enterprise Temporary tables

SAP Adaptive Server Enterprise System stored procedures

SAP Adaptive Server Enterprise Using database stored procedures in DataWindow objects

Using Embedded SQL with Informix

Informix name qualification

Informix transaction management statements

Informix using CONNECT, COMMIT, DISCONNECT, and ROLLBACK

Informix performance and locking

Informix non-cursor statements

Informix DELETE, INSERT, and UPDATE

Informix SELECT

Informix cursor statements

Informix retrieval using cursors

Informix nonupdatable cursors

Informix updatable cursors

Informix FETCH statements

Informix FETCH NEXT

Informix FETCH FIRST, FETCH PRIOR, and FETCH LAST

Informix CLOSE for cursors

Informix database stored procedures

Informix retrieval using database stored procedures

Informix DECLARE and EXECUTE

Informix FETCH

Informix CLOSE

Informix update using database stored procedures

Informix using database stored procedures in DataWindow objects

Informix database stored procedure summary

Using Embedded SQL with Microsoft SQL Server

Microsoft SQL Server Name qualification

Microsoft SQL Server Functions

Microsoft SQL Server Transaction management statements

Microsoft SQL Server Using CONNECT, COMMIT, DISCONNECT, and ROLLBACK

Microsoft SQL Server Using AutoCommit

Microsoft SQL Server Performance and locking

Microsoft SQL Server Non-cursor statements

Microsoft SQL Server DELETE, INSERT, and UPDATE

Microsoft SQL Server SELECT

Microsoft SQL Server Cursor statements

Microsoft SQL Server Fetching rows

Microsoft SQL Server FETCH NEXT

Microsoft SQL Server FETCH FIRST, FETCH PRIOR, and FETCH LAST

Microsoft SQL Server Closing the cursor

Microsoft SQL Server Database stored procedures

Microsoft SQL Server Retrieval

Microsoft SQL Server DECLARE and EXECUTE

Microsoft SQL Server FETCH

Microsoft SQL Server CLOSE

Microsoft SQL Server Update

Microsoft SQL Server Temporary tables

Microsoft SQL Server Using database stored procedures in DataWindow objects

Microsoft SQL Server Database stored procedures summary

Using Embedded SQL with Oracle

Oracle Name qualification

Oracle SQL functions

Oracle Transaction management statements

Oracle Using CONNECT, DISCONNECT, COMMIT, and ROLLBACK

Oracle Performance and locking

Oracle Non-cursor statements

Oracle DELETE, INSERT, and UPDATE

Oracle SELECT

Oracle Cursor statements

Oracle Retrieval

Oracle Update

Oracle Cursor support summary

Oracle Database stored procedures

Supported features when using Oracle stored procedures

Using DECLARE, EXECUTE, FETCH, and CLOSE with Oracle stored procedures

Oracle DECLARE and EXECUTE

Oracle FETCH

Oracle CLOSE

Appendix

APPENDIX Adding Functions to the PBODB170 Initialization File

About the PBODB170 initialization file