Connecting to Your Database

Appeon PowerBuilder® 2021

FOR WINDOWS

DOCUMENT ID: DC37776-01-1900-01

LAST REVISED: July 29, 2021

Copyright © Appeon. All rights reserved.

This publication pertains to Appeon software and to any subsequent release until otherwise indicated in new editions or technical notes. Information in this document is subject to change without notice. The software described herein is furnished under a license agreement, and it may be used or copied only in accordance with the terms of that agreement.

Upgrades are provided only at regularly scheduled software release dates. No part of this publication may be reproduced, transmitted, or translated in any form or by any means, electronic, mechanical, manual, optical, or otherwise, without the prior written permission of Appeon Inc.

Appeon and other Appeon products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of Appeon Inc.

SAP and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP and SAP affiliate company.

Java and all Java-based marks are trademarks or registered trademarks of Oracle and/or its affiliates in the U.S. and other countries.

Unicode and the Unicode Logo are registered trademarks of Unicode, Inc.

All other company and product names mentioned may be trademarks of the respective companies with which they are associated.

Use, duplication, or disclosure by the government is subject to the restrictions set forth in subparagraph (c)(1)(ii) of DFARS 52.227-7013 for the DOD and as set forth in FAR 52.227-19(a)-(d) for civilian agencies.

Appeon Inc., 1/F, Shell Industrial Building, 12 Lee Chung Street, Chai Wan District, Hong Kong

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
What to do next
Working with Standard Database Interfaces
Using the ODBC Interface
About the ODBC interface
Preparing ODBC data sources
Defining ODBC data sources
Defining the ODBC interface
SAP SQL Anywhere
PostgreSQL
Using the JDBC Interface
About the JDBC interface
Preparing to use the JDBC interface
Defining the JDBC interface
Using the OLE DB Interface
About the OLE DB interface
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
Preparing to use the ADO.NET interface
Defining the ADO.NET interface
Sharing ADO.NET Database Connections
Using the OData Interface (Obsolete)
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
Using Open Client directory services
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
Using Informix
Supported versions for Informix
Supported Informix datatypes
Features supported by the I10 interface
Basic software components for Informix
Preparing to use the Informix database
Defining the Informix database interface
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
Notes on using the MSOLEDBSQL and SNC interfaces
Using Oracle
Supported versions for Oracle
Supported Oracle datatypes
Basic software components for Oracle
Preparing to use the Oracle database
Defining the Oracle database interface
Using Oracle stored procedures as a data source
Using Oracle user-defined types
Support for HA event notification
ORA driver support for Oracle 11g features
Using DirectConnect
Using the DirectConnect interface
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
Working with Database Connections
Managing Database Connections
About database connections
Connecting to a database
Maintaining database profiles
Sharing database profiles
Importing and exporting database profiles
About the PowerBuilder extended attribute system tables
Setting Additional Connection Parameters
Basic steps for setting connection parameters
About the Database Profile Setup dialog box
Setting database parameters
Setting database preferences
Troubleshooting Your Connection
Troubleshooting Your Connection
Overview of troubleshooting tools
Using the Database Trace tool
Using the SQL statement trace utility
Using the ODBC Driver Manager Trace tool
Using the JDBC Driver Manager Trace tool
Using Embedded SQL
Using Embedded SQL with ODBC
ODBC SQL Support
ODBC Name qualification
ODBC SQL functions
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 PBODB.ini Initialization File
About the PBODB.ini file