Using the ODBC interface
On most platforms you can access a wide variety of ODBC data
sources in PowerBuilder. This section describes what you need to know
to use ODBC connections to access your data in PowerBuilder.
Supported ODBC drivers and data sources For a complete list of the ODBC drivers supplied
with PowerBuilder and the data sources they access, see “Supported
Database Interfaces” in online Help.
What is ODBC?
The ODBC API
Open Database Connectivity (ODBC)
is a standard application programming interface (API) developed
by Microsoft. It allows a single application to access a variety
of data sources for which ODBC-compliant drivers exist. The application
uses Structured Query Language (SQL) as the standard data access language.
The ODBC API defines the following:
- A library of ODBC function calls that connect to the data
source, execute SQL statements, and retrieve results - A standard way to connect and log on to a DBMS
- SQL syntax based on the X/Open and SQL
Access Group (SAG) CAE specification (1992) - A standard representation for data types
- A standard set of error codes
Accessing ODBC data sources
Applications that provide an ODBC interface, like PowerBuilder,
can access data sources for which an ODBC driver exists. An ODBC
data source driver is a dynamic link library (DLL) or
shared library that implements ODBC function calls. The application
invokes the ODBC driver to access a particular data source.
Platform differences
The capabilities for accessing ODBC data sources differ on
each supported PowerBuilder platform. The following sections describe
platform-specific considerations when you are using ODBC with PowerBuilder
on Windows and UNIX. Keep these considerations in mind as you read
the rest of this chapter.
For up-to-date information As connectivity to additional ODBC data sources is supported
in future releases of PowerBuilder, updated information will be available
from the Sybase Customer Service and Support website
. You should
also check the Release Notes
for the latest
information about supported ODBC drivers for your edition of PowerBuilder.
Using ODBC on Windows
What you can do
All ODBC connectivity features are supported in PowerBuilder on
Windows. You can:
- Connect to an Adaptive Server Anywhere standalone database (including the EAS Demo DB)
using the Adaptive Server Anywhere ODBC driver and the ODBC interface - Create and delete local Adaptive Server Anywhere databases
For instructions, see the PowerBuilder
User’s Guide
. - Use Sybase-supplied INTERSOLV ODBC drivers to access
your data
For a list of the ODBC drivers supplied on
Windows, see “Supported Database Interfaces” in online Help. - In all editions except
PowerBuilder
Desktop, use Level 1 or higher ODBC-compliant drivers obtained from
vendors other than Sybase to access your data
See “Obtaining ODBC drivers”. - Use Microsoft’s ODBC Data Source Administrator
to define ODBC data sources
See “About defining ODBC data
sources”.
Using ODBC on UNIX
What you can do
In PowerBuilder on all UNIX platforms, you can
:
- Use the Sybase-supplied INTERSOLV ODBC drivers to access data
On UNIX, the only ODBC connections supported in PowerBuilder
are those using the INTERSOLV ODBC drivers included with the product. You cannot
use
ODBC drivers obtained from other vendors to access data. - Connect to an Adaptive Server Anywhere standalone database (including the EAS Demo DB)
using the Adaptive Server Anywhere ODBC driver and the ODBC interface - Create and delete local Adaptive Server Anywhere databases.
What you cannot do
In PowerBuilder on all UNIX platforms, you cannot
:
- Use ODBC-compliant drivers
obtained from vendors other than Sybase to access your data
Components of an ODBC connection
How an ODBC connection is made
When you access an ODBC data source in PowerBuilder, your connection
goes through several layers before reaching the data source. It
is important to understand that each layer represents a separate
component of the connection, and that each component may come from
a different vendor.
Because ODBC is a standard API, PowerBuilder uses the same interface
to access every ODBC data source. As long as a driver is ODBC compliant, PowerBuilder can
access it through the ODBC interface to the ODBC Driver Manager.
The development environment and the ODBC interface work together
as the application component.
The following diagrams show the general components of an ODBC connection.
Component descriptions
The following table gives the provider and a brief description
of each ODBC component shown in the diagram:
| Component | Provider | What it does |
|---|---|---|
| Application | Sybase | Calls ODBC functions to submit SQL statements, catalog requests, and retrieve results from a data sourcePowerBuilder uses the same ODBC interface to access all ODBC data sources |
| ODBC Driver Manager | Microsoft | Installs, loads, and unloads drivers for an application |
| Driver | Driver vendor | Processes ODBC function calls, submits SQL requests to a particular data source, and returns results to an applicationIf necessary, translates an application’s request so it conforms to the SQL syntax supported by the backend databaseSee “Types of ODBC drivers” |
| Data source | DBMS or database vendor | Stores and manages data for an application. Consists of the data to be accessed and its associated DBMS, operating system, and (if present) network software that accesses the DBMS |
Types of ODBC drivers
When PowerBuilder is connected to an ODBC data source, you may
see messages from the ODBC driver that include the words single-tier
or multiple-tier
.
These terms refer to the two types of drivers defined by the ODBC standard:
- Single-tier driver
- Multiple-tier driver
Single-tier driver
A single-tier ODBC driver processes
both ODBC functions and SQL statements. In other words, a single-tier
driver includes the data access software required to manage the
data source file and catalog tables.
An example of a single-tier ODBC driver is one that accesses
Xbase files, such as the INTERSOLV dBASE ODBC driver.
Multiple-tier driver
A multiple-tier ODBC driver processes
ODBC functions, but sends SQL statements to the database engine
for processing. Unlike the single-tier driver, a multiple-tier driver
does not include the data access software required to manage the
data directly.
An example of a multiple-tier ODBC driver is the Sybase Adaptive Server Anywhere driver.
Ensuring the proper ODBC driver conformance levels
You can access data in PowerBuilder Enterprise or PowerBuilder Professional with
ODBC drivers obtained from vendors other
than
Sybase, such as directly from DBMS vendors.
An ODBC driver obtained from another vendor must meet certain conformance
requirements to ensure that it works properly with PowerBuilder. This
section describes how to make sure your driver meets these requirements.
What are ODBC conformance levels?
PowerBuilder can access many data sources for which ODBC-compliant drivers
exist. However, ODBC drivers manufactured by different vendors may vary
widely in the functions they provide.
To ensure a standard level of compliance with the ODBC interface,
and to provide a means by which application vendors can determine
if a specific driver provides the functions they need, ODBC defines
conformance levels for drivers in two areas:
- API Deals with supported ODBC function calls
- SQL grammar Deals with supported SQL statements and SQL data types
API conformance levels
ODBC defines three API conformance levels, in order of increasing functionality:
- Core A set of core API functions that corresponds to the functions
in the X/Open and SAG Call Level Interface (CLI) specification - Level 1 Includes all Core API functions as well as several extended functions
- Level 2 Includes all Core and Level 1 API functions as well as additional extended
functions
To ensure the proper ODBC driver API conformance
level:
-
It is recommended that the ODBC drivers
you use with PowerBuilder meet Level 1 or higher
API
conformance requirements. However, PowerBuilder may also work with
drivers that meet Core level API conformance requirements.
SQL conformance levels
ODBC defines three SQL grammar conformance levels, in order
of increasing functionality:
- Minimum A set of SQL statements and data types that meets a basic
level of ODBC conformance - Core Includes all Minimum SQL grammar as well as additional statements
and data types that roughly correspond to the X/Open and
SAG CAE specification (1992) - Extended Includes all Minimum and Core SQL grammar as well as an extended
set of statements and data types that support common DBMS extensions
to SQL
To ensure the proper ODBC driver SQL conformance
level:
-
It is recommended that the ODBC drivers
you use with PowerBuilder meet Core or higher
SQL
conformance requirements. However, PowerBuilder may also work with
drivers that meet Minimum level SQL conformance requirements.
Obtaining ODBC drivers
Two sources
There are two ways that you can obtain ODBC drivers for use
with PowerBuilder:
- From Sybase (recommended) Install one or more of the ODBC drivers shipped with PowerBuilder.
You can do this when you first install PowerBuilder, or later. Using
an ODBC driver supplied by Sybase ensures that the driver was tested
and found to work with PowerBuilder. - From another vendor PowerBuilder Enterprise and PowerBuilder Professional let you
access data with any
Level 1 or higher ODBC-compliant
drivers obtained from a vendor other than Sybase. In most cases,
these drivers will work with PowerBuilder. However, Sybase may not
have tested the drivers to verify this.
Accessing ALLBASE/SQL, SQLBase, and XDB through
ODBC The vendors of ALLBASE/SQL, SQLBase, and XDB recommend
using ODBC to access their DBMSs. To comply with this recommendation, PowerBuilder no
longer provides native database interfaces for accessing ALLBASE/SQL,
SQLBase, and XDB. Instead, you should use ODBC-compliant drivers
obtained from other vendors to access these DBMSs.
Platform differences
The ability to use ODBC drivers supplied by Sybase or from
another vendor to access data in the development environment depends
on the platform you are using, as follows:
| Platform | Product includes ODBC driver(s) | Product allows use of other vendors’ ODBC drivers |
|---|---|---|
| Windows | Yes (INTERSOLV and Adaptive Server Anywhere) | Yes |
| UNIX | Yes (INTERSOLV) | No |
Using ODBC drivers with PowerBuilder Desktop
Using ODBC drivers that come with Desktop
If you are using PowerBuilder Desktop, you can access data
using the ODBC drivers that are shipped with the product. Unlike
PowerBuilder Enterprise and PowerBuilder Professional, you cannot
use
an ODBC driver obtained from another vendor with PowerBuilder Desktop.
Using existing Microsoft ODBC drivers
If you already have Version 2.0 or higher of any of the following
Microsoft ODBC drivers installed and properly configured, you can
use
these drivers with PowerBuilder Desktop to connect to your data
source:
- Microsoft Access (*.MDB)
- Microsoft Btrieve (*.DDF)
- Microsoft dBASE (*.DBF)
- Microsoft Excel (*.XLS)
- Microsoft FoxPro (*.DBF)
- Microsoft Paradox (*.DB)
- Microsoft Text (*.CSV, *.TXT)
Using INTERSOLV drivers is recommended PowerBuilder Desktop on the Windows platform comes with INTERSOLV ODBC
drivers for all of these data sources except Access
.
Since the INTERSOLV drivers have been tested to work with PowerBuilder
Desktop, you should use the INTERSOLV drivers whenever possible
to access these data sources.
Getting help with ODBC drivers
To ensure that you have up-to-date and accurate information
about using your ODBC driver with PowerBuilder, get help as needed
by doing one or more of the following:
| To get help on | Do this |
|---|---|
| Using the ODBC Data Source Administrator | Click the Help button on each tab |
| Completing the ODBC setup dialog box for your driver |
Click the Help button (if present) in the ODBC setup dialog box for your driver |
| Using Adaptive Server Anywhere | See the Adaptive Server Anywhere documentation |
| Using an ODBC driver obtained from a vendor other than Sybase |
See the vendor’s documentation for that driver |
| Troubleshooting your ODBC connection | Check for a FaxLine document that describes how to connect to your ODBC data source. Updated information about connectivity issues is available on the Sybase Customer Service and Support website |