Connecting to Your Database

Appeon PowerBuilder® 2017 R3

FOR WINDOWS

DOCUMENT ID: DC37776-01-1700-01

LAST REVISED: July 25, 2018

Copyright © 2018 by Appeon Limited. 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 Limited.

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

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