About this chapter
This chapter gives an introduction to the ODBC interface and then describes how to prepare to use the data source, how to define the data source, and how to define the ODBC database profile. It also describes how to use the SAP SQL Anywhere ODBC driver.
For more information
This chapter gives general information about preparing to use and defining each ODBC data source. For more detailed information, use the online Help provided by the driver vendor, as described in Displaying Help for ODBC drivers. This Help provides important details about using the data source.
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.
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 in to a DBMS
-
SQL syntax based on the X/Open and SQL Access Group (SAG) CAE specification (1992)
-
A standard representation for datatypes
-
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) that implements ODBC function calls. The application invokes the ODBC driver to access a particular data source.
Accessing Unicode data
Using the ODBC interface, PowerBuilder can connect, save, and retrieve data in both ANSI/DBCS and Unicode databases but does not convert data between Unicode and ANSI/DBCS. When character data or command text is sent to the database, PowerBuilder sends a Unicode string. The driver must guarantee that the data is saved as Unicode data correctly. When PowerBuilder retrieves character data, it assumes the data is Unicode.
A Unicode database is a database whose character set is set to a Unicode format, such as UTF-8, UTF-16, UCS-2, or UCS-4. All data must be in Unicode format, and any data saved to the database must be converted to Unicode data implicitly or explicitly.
A database that uses ANSI (or DBCS) as its character set might use special datatypes to store Unicode data. Columns with these datatypes can store only Unicode data. Any data saved into such a column must be converted to Unicode explicitly. This conversion must be handled by the database server or client.
What you can do
The following ODBC connectivity features are available in PowerBuilder:
-
Connect to a SQL Anywhere standalone database (including the Demo Database) using the SQL Anywhere ODBC driver and the ODBC interface
-
Create and delete local SQL Anywhere databases
For instructions, see the section called “Creating and deleting a SQL Anywhere database” in Users Guide.
-
Connect to an installed SAP IQ database client through the ODBC interface.
-
Use Level 1 or later ODBC-compliant drivers obtained from vendors other than SAP to access your data
-
Use Microsoft's ODBC Data Source Administrator to define ODBC data sources
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 might 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 figure shows the general components of an ODBC connection.
Figure: 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 |
SAP |
Calls ODBC functions to submit SQL statements, catalog requests, and retrieve results from a data source. PowerBuilder 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 application. If necessary, translates an application's request so that it conforms to the SQL syntax supported by the back-end database. See 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. |
When PowerBuilder is connected to an ODBC data source, you might 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
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 the Microsoft Access driver.
Figure: Single-tier 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 SAP SQL Anywhere driver.
Figure: Multi-tier ODBC driver
You can access data in PowerBuilder with ODBC drivers obtained from vendors other than SAP, such as 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.
PowerBuilder can access many data sources for which ODBC-compliant drivers exist. However, ODBC drivers manufactured by different vendors might 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 whether 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 datatypes
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 ISO Call Level Interface (CLI) and X/Open CLI specification
-
Level 1
Includes all Core API functions and several extended functions usually available in an OLTP relational DBMS
-
Level 2
Includes all Core and Level 1 API functions and additional extended functions
To ensure the proper ODBC driver API conformance level:
-
Appeon recommends that the ODBC drivers you use with PowerBuilder meet Level 1 or higher API conformance requirements. However, PowerBuilder might 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 datatypes that meets a basic level of ODBC conformance
-
Core
Includes all Minimum SQL grammar and additional statements and datatypes that roughly correspond to the X/Open and SAG CAE specification (1992)
-
Extended
Includes all Minimum and Core SQL grammar and an extended set of statements and datatypes that support common DBMS extensions to SQL
You can use the ODBC driver for the SQL Anywhere developer edition from SAP, provided with PowerBuilder, to access data. Other SAP database clients also include ODBC drivers that you can access through the PowerBuilder ODBC interface. See your database documentation for details.
PowerBuilder also let you access data with any Level 1 or higher ODBC-compliant drivers obtained from a vendor other than SAP. In most cases, these drivers will work with PowerBuilder.
Using existing Microsoft ODBC drivers
If you already have version 2.0 or later of any of the following Microsoft ODBC drivers installed and properly configured, you can use these drivers with PowerBuilder 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) |
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 SQL Anywhere |
See the SQL Anywhere documentation. |
|
Using an ODBC driver obtained from a vendor other than SAP |
See the vendor's documentation for that driver. |
|
Troubleshooting your ODBC connection |
Check for a technical document that describes how to connect to your ODBC data source. |
The first step in connecting to an ODBC data source is preparing the data source. This ensures that you are able to connect to the data source and use your data in PowerBuilder.
You prepare to use a data source outside PowerBuilder before you start the product, define the data source, and connect to it. The requirements differ for each data source, but in general, preparing to use a data source involves the following steps.
To prepare to use an ODBC data source with PowerBuilder:
-
If network software is required to access the data source, make sure it is properly installed and configured at your site and on the client workstation.
-
If database software is required, make sure it is properly installed and configured on your computer or network server.
-
Make sure the required data files are present on your computer or network server.
-
Make sure the names of tables and columns you want to access follow standard SQL naming conventions.
Avoid using blank spaces or database-specific reserved words in table and column names. Be aware of the case-sensitivity options of the DBMS. It is safest to use all uppercase characters when naming tables and columns that you want to access in PowerBuilder.
-
If your database requires it, make sure the tables you want to access have unique indexes.
-
Install both of the following using the PowerBuilder Setup program:
-
The ODBC driver that accesses your data source
-
The ODBC interface
-
Each ODBC data source requires a corresponding ODBC driver to access it. When you define an ODBC data source, you provide information about the data source that the driver requires in order to connect to it. Defining an ODBC data source is often called configuring the data source.
After you prepare to use the data source, you must define it using Microsoft's ODBC Data Source Administrator utility. This utility can be accessed from the Control Panel in Windows or PowerBuilder's Database painter.
The rest of this section describes what you need to know to define an ODBC data source in order to access it in the PowerBuilder development environment.
When you access an ODBC data source in PowerBuilder, there are several initialization files and registry entries on your computer that work with the ODBC interface and driver to make the connection.
Contents
PBODB.ini is installed in the %AppeonInstallPath%\Common\PowerBuilder\Runtime [version] or %AppeonInstallPath%\Common\PowerBuilder\Runtime [version]\x64 directory. The first time the user opens PowerBuilder, the file is copied to the initialization path (specified in the PowerBuilder IDE: Tools | System Options dialog box) which is by default AppData\Local\Appeon\PowerBuilder 19.0 in the user's profile folder (for example, under C:\users\[username]). This copy is used when running PowerBuilder. PowerBuilder uses PBODB.ini to maintain access to extended functionality in the back-end DBMS, for which ODBC does not provide an API call. Examples of extended functionality are SQL syntax or DBMS-specific function calls.
Editing
In most cases, you do not need to edit PBODB.ini. In certain situations, however, you might need to add functions to PBODB.ini for your back-end DBMS. Be sure to edit the copy in your user profile folder, not the original copy.
For instructions, see Adding Functions to the PBODB Initialization File
Contents
The ODBCINST initialization information is located in the HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI registry key. When you install an ODBC-compliant driver, ODBCINST.INI is automatically updated with a description of the driver.
This description includes:
-
The DBMS or data source associated with the driver
-
The drive and directory of the driver and setup DLLs (for some data sources, the driver and setup DLLs are the same)
-
Other driver-specific connection parameters
Editing
You do not need to edit the registry key directly to modify connection information. If your driver uses the information in the ODBCINST.INI registry key, the key is automatically updated when you install the driver. This is true whether the driver is supplied by SAP or another vendor.
Contents
ODBC initialization information is located in the HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI registry key. When you define a data source for a particular ODBC driver, the driver writes the values you specify in the ODBC setup dialog box to the ODBC.INI registry key.
The ODBC.INI key contains subkeys named for each defined data source. Each subkey contains the values specified for that data source in the ODBC setup dialog box. The values might vary for each data source but generally include the following:
-
Database
-
Driver
-
Optional description
-
DBMS-specific connection parameters
Editing
Do not edit the ODBC subkey directly to modify connection information. Instead, use a tool designed to define ODBC data sources and the ODBC configuration automatically, such as the ODBC Data Source Administrator.
Contents
Database profiles for all data sources are stored in the registry in HKEY_CURRENT_USER\SOFTWARE\Sybase\PowerBuilder\19.0\DatabaseProfiles.
Editing
You should not need to edit the profiles directly to modify connection information. These files are updated automatically when PowerBuilder creates the database profile as part of the ODBC data source definition.
You can also edit the profile in the Database Profile Setup dialog box or complete the Database Preferences dialog box in PowerBuilder to specify other connection parameters stored in the registry. (For instructions, see Setting Additional Connection Parameters)
Example
The following example shows a portion of the database profile for an Demo Database data source:
DBMS=ODBC DBParm=ConnectString='DSN=PB Demo DB V2019R3 DB;UID=dba;PWD=00c61737' Prompt=0
This registry entry example shows the two most important values in a database profile for an ODBC data source:
-
DBMS
The DBMS value (ODBC) indicates that you are using the ODBC interface to connect to the data source.
-
DBParm
The ConnectString DBParm parameter controls your ODBC data source connection. The connect string must specify the DSN (data source name) value, which tells ODBC which data source you want to connect to. When you select a database profile to connect to a data source, ODBC looks in the ODBC.INI registry key for a subkey that corresponds to the data source name in your profile. ODBC then uses the information in the subkey to load the required libraries to connect to the data source. The connect string can also contain the UID (user ID) and PWD (password) values needed to access the data source.
When you define an ODBC data source in PowerBuilder, each data source name must be unique. You can, however, define multiple data sources that access the same data, as long as the data sources have unique names.
For example, assume that your data source is a SQL Anywhere database located in C:\SQL Anywhere\SALES.DB. Depending on your application, you might want to specify different sets of connection parameters for accessing the database, such as different passwords and user IDs.
To do this, you can define two ODBC data sources named Sales1 and Sales2 that specify the same database (C:\SQL Anywhere\SALES.DB) but use different user IDs and passwords. When you connect to the data source using a profile created for either of these data sources, you are using different connection parameters to access the same data.
Figure: Using two data sources to access a database
The online Help for ODBC drivers in PowerBuilder is provided by the driver vendors. It gives help on:
-
Completing the ODBC setup dialog box to define the data source
-
Using the ODBC driver to access the data source
Use the following procedure to display vendor-supplied Help when you are in the ODBC setup dialog box for ODBC drivers.
What is an ODBC translator?
Some ODBC drivers allow you to specify a translator when you define the data source. An ODBC translator is a DLL that translates data passing between an application and a data source. Typically, translators are used to translate data from one character set to another.
What you do
Follow these steps to select a translator for your ODBC driver.
To select a translator when using an ODBC driver:
-
In the ODBC setup dialog box for your driver, display the Select Translator dialog box.
The way you display the Select Translator dialog box depends on the driver and Windows platform you are using. Click Help in your driver's setup dialog box for instructions on displaying the Select Translator dialog box.
In the Select Translator dialog box, the translators listed are determined by the values in your ODBCINST.INI registry key.
-
From the Installed Translators list, select a translator to use.
If you need help using the Select Translator dialog box, click Help.
-
Click OK.
The Select Translator dialog box closes and the driver performs the translation.
To define a connection through the ODBC interface, you must create a database profile by supplying values for at least the basic connection parameters in the Database Profile Setup - ODBC dialog box. You can then select this profile at any time to connect to your data source in the development environment.
For information on how to define a database profile, see Using database profiles.
This section describes how to prepare and define an SAP SQL Anywhere data source in order to connect to it using the SQL Anywhere ODBC driver.
SQL Anywhere includes two database servers -- a personal database server and a network database server. For information about using SAP SQL Anywhere, see the SQL Anywhere documentation.
The SQL Anywhere ODBC driver supports connection to local and remote databases created with the following:
-
PowerBuilder running on your computer
-
SQL Anywhere version 17.x
-
SQL Anywhere version 16.x
-
SQL Anywhere version 12.x
The following figure shows the basic software components required to connect to a SQL Anywhere data source in PowerBuilder.
Figure: Components of a SQL Anywhere connection
Before you define and connect to a SQL Anywhere data source in PowerBuilder, follow these steps to prepare the data source.
To prepare a SQL Anywhere data source:
-
Make sure the database file for the SQL Anywhere data source already exists. You can create a new database by:
-
Launching the Create SQL Anywhere Database utility. You can access this utility from the Utilities folder for the ODBC interface in the Database profile or Database painter when PowerBuilder is running on your computer.
This method creates a local SQL Anywhere database on your computer, and also creates the data source definition and database profile for this connection. (For instructions, see the section called “Creating and deleting a SQL Anywhere database” in Users Guide.)
-
Creating the database some other way, such as with PowerBuilder running on another user's computer or by using SQL Anywhere outside PowerBuilder. (For instructions, see the SQL Anywhere documentation.)
-
-
Make sure you have the log file associated with the SQL Anywhere database so that you can fully recover the database if it becomes corrupted.
If the log file for the SQL Anywhere database does not exist, the SQL Anywhere database engine creates it. However, if you are copying or moving a database from another computer or directory, you should copy or move the log file with it.
When you create a local SQL Anywhere database, PowerBuilder automatically creates the data source definition and database profile for you. Therefore, you need only use the following procedure to define a SQL Anywhere data source when you want to access a SQL Anywhere database not created using PowerBuilder on your computer.
To define a SQL Anywhere data source for the SQL Anywhere driver:
-
Select Create ODBC Data Source from the list of ODBC utilities in the Database Profiles dialog box or the Database painter.
-
Select User Data Source and click Next.
-
On the Create New Data Source page, select the SQL Anywhere driver and click Finish.
The ODBC Configuration for SQL Anywhere dialog box displays:
-
You must supply the following values:
-
Data source name on the ODBC tab page
-
User ID and password on the Login tab page
-
Database file on the Database tab page
Use the Help button to get information about fields in the dialog box.
-
-
(Optional) To select an ODBC translator to translate your data from one character set to another, click the Select button on the ODBC tab.
-
Click OK to save the data source definition.
Specifying a Start Line value
When the SQL Anywhere ODBC driver cannot find a running personal or network database server using the PATH variable and Database Name setting, it uses the commands specified in the Start Line field to start the database servers.
Specify one of the following commands in the Start Line field on the Database tab page, where n is the version of SQL Anywhere you are using.
|
Specify this command |
To |
|---|---|
|
dbengn.exe |
Start the personal database server and the database specified in the Database File box |
|
rtengn.exe |
Start the restricted runtime database server and the database specified in the Database File box |
For information on completing the ODBC Configuration For SQL Anywhere dialog box, see the SQL Anywhere documentation.
When you work with a SQL Anywhere table in the DataWindow, Data Pipeline, or Database painter, the default behavior is to treat any column named timestamp as a SQL Anywhere Transact-SQL -- special timestamp column.
Creating special timestamp columns
You can create a Transact-SQL special timestamp column in a SQL Anywhere table.
To create a Transact-SQL special timestamp column in a SQL Anywhere table in PowerBuilder:
-
Give the name timestamp to any column having a timestamp datatype that you want treated as a Transact-SQL special timestamp column. Do this in one of the following ways:
-
In the painter -- Select timestamp as the column name. (For instructions, see Users Guide.)
-
In a SQL CREATE TABLE statement -- Follow the CREATE TABLE example.
-
-
Specify timestamp as the default value for the column. Do this in one of the following ways:
-
In the painter -- Select timestamp as the default value for the column. (For instructions, see Users Guide.)
-
In a SQL CREATE TABLE statement -- Follow the CREATE TABLE example.
-
-
If you are working with the table in the Data Pipeline painter, select the initial value exclude for the special timestamp column from the drop-down list in the Initial Value column of the workspace.
You must select exclude as the initial value to exclude the special timestamp column from INSERT or UPDATE statements.
For instructions, see the section called “Modifying the data pipeline definition” in Users Guide.
The following CREATE TABLE statement defines a SQL Anywhere table named timesheet containing three columns: employee_ID (integer datatype), hours (decimal datatype), and timestamp (timestamp datatype and timestamp default value):
CREATE TABLE timesheet ( employee_ID INTEGER, hours DECIMAL, timestamp TIMESTAMP default timestamp )
Not using special timestamp columns
If you want to change the default behavior, you can specify that PowerBuilder not treat SQL Anywhere columns named timestamp as Transact-SQL special timestamp columns.
To specify that PowerBuilder not treat columns named timestamp as a Transact-SQL special timestamp column:
-
Edit the SAP SQL Anywhere section of the PBODB initialization file to change the value of SQLSrvrTSName from 'Yes' to 'No'.
After making changes in the initialization file, you must reconnect to the database to have them take effect. See Adding Functions to the PBODB Initialization File
For instructions on connecting to the ODBC data source, see Connecting to a database.
PowerBuilder apps can connect with the PostgreSQL 10, 11, or 12 (32-bit and 64-bit) (ANSI and Unicode) database through the PostgreSQL ODBC driver.
PBODB.ini must be configured first in order for connecting with the PostgreSQL database through ODBC interface. Search "PostgreSQL" in PBODB.ini to view the parameter list, related syntax, and functions. For how to configure the PBODB.ini file, see Adding Functions to the PBODB Initialization File.
Note
When the PostgreSQL database is used in the remote procedure call (RPC), use the inout keyword to define the stored procedure (using out keyword cannot return data), and use subroutine (rather than function) to define the RPC function.
PostgreSQL stored procedure is not fully supported, for example, PostgreSQL stored procedure with parameters cannot be used as the data source for DataWindow objects. When using PostgreSQL stored procedure as the data source for DataWindow objects, make sure to set the StripParmNames connection parameter to Yes (or select the "strip parameter names" option in the syntax section of the database profile setup). PostgreSQL stored procedure syntax cannot be previewed in the Database Painter or the DataWindow data source.
PostgreSQL supports the auto-increment column with the following two methods:
-
Method 1: GetIdentity='Select currval(''GEN_&TableName'')'
-
Method 2: GetIdentity='Select currval(''&TableName._&ColumnName._seq')'
Method 2 uses the serial to create the auto-increment column.
These methods require that the sequence name follows the name conversions specified by GetIdentity in pbodb.ini, for example, gen_TableName, TableName_ColumnName_seq. If the sequence name is not in the required format, the identity value cannot be obtained automatically.
These methods also require that the DelimitIdentifier property ("Enclose Table and Column Names in Quotes" option) set to NO. If it is set to YES, the identity value cannot be returned.
