Using the JDBC Interface

About this chapter

This chapter describes the JDBC interface and explains how to prepare to use this interface and how to define the JDBC database profile.

For more information

For more detailed information about JDBC, go to the Java website at http://java.sun.com/products/jdbc/overview.html.

About the JDBC interface

You can access a wide variety of databases through JDBC in PowerBuilder. This section describes what you need to know to use JDBC connections to access your data in PowerBuilder.

What is JDBC?

The JDBC API

Java Database Connectivity (JDBC) is a standard application programming interface (API) that allows a Java application to access any database that supports Structured Query Language (SQL) as its standard data access language.

The JDBC API includes classes for common SQL database activities that allow you to open connections to databases, execute SQL commands, and process results. Consequently, Java programs have the capability to use the familiar SQL programming model of issuing SQL statements and processing the resulting data. The JDBC classes are included in Java 1.1+ and Java 2 as the java.sql package.

The JDBC API defines the following:

  • A library of JDBC function calls that connect to a database, execute SQL statements, and retrieve results

  • A standard way to connect and log in to a DBMS

  • SQL syntax based on the X/Open SQL Call Level Interface or X/Open and SQL Access Group (SAG) CAE specification (1992)

  • A standard representation for datatypes

  • A standard set of error codes

How JDBC APIs are implemented

JDBC API implementations fall into two broad categories: those that communicate with an existing ODBC driver (a JDBC-ODBC bridge) and those that communicate with a native database API (a JDBC driver that converts JDBC calls into the communications protocol used by the specific database vendor). The PowerBuilder implementation of the JDBC interface can be used to connect to any database for which a JDBC-compliant driver exists.

The PowerBuilder JDB interface

A Java Virtual Machine (JVM) is required to interpret and execute the bytecode of a Java program. The PowerBuilder JDB interface supports the Sun Java Runtime Environment (JRE) versions 1.6 and later.

Using the JDBC interface

You can use the JDBC interface to develop the client/server applications. If a client is already running a JVM (in a running Web browser or inside the operating system), the use of the JDBC interface to access a database does not require the client-side installation and administration of a database driver, which is required when using ODBC.

Components of a JDBC connection

How a JDBC connection is made

In PowerBuilder when you access a database through the JDBC interface, your connection goes through several layers before reaching the database. 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 JDBC is a standard API, PowerBuilder uses the same interface to access every JDBC-compliant database driver.

The following figure shows the general components of a JDBC connection.

Figure: Components of a JDBC connection

The JDBC DLL

PowerBuilder provides the pbjdb.dll. This DLL runs with the Sun Java Runtime Environment (JRE) versions 1.1 and later.

PowerBuilder Java package

PowerBuilder includes a small package of Java classes that gives the JDBC interface the level of error-checking and efficiency (SQLException catching) found in other PowerBuilder interfaces. The package is called pbjdbc12.jar and is found in %systemdrive%\Program Files (x86)\Appeon\Common\PowerBuilder\Runtime [version].

The Java Virtual Machine

The Java Virtual Machine (JVM) is a component of Java development software. When you install PowerBuilder, the Sun Java Development Kit (JDK), including the Java Runtime Environment (JRE), is installed on your system in %AppeonInstallPath%\PowerBuilder [version]\IDE. For PowerBuilder 2019 R3, JDK 1.6 is installed. This version of the JVM is started when you use a JDBC connection or any other process that requires a JVM and is used throughout the PowerBuilder session.

If you need to use a different JVM, see the instructions in Preparing to use the JDBC interface. For more information about how the JVM is started, see the chapter on deploying your application in Application Techniques.

The JDBC drivers

The JDBC interface can communicate with any JDBC-compliant driver including SAP jConnect for JDBC (available with SAP ASE, IQ, and SA database clients) and the Oracle and IBM Informix JDBC drivers. These drivers are native-protocol, all-Java drivers -- that is, they convert JDBC calls into the SQL syntax supported by the databases.

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.

JDBC registry entries

When you access data through the PowerBuilder JDBC interface, PowerBuilder uses an internal registry to maintain definitions of SQL syntax, DBMS-specific function calls, and default DBParm parameter settings for the back-end DBMS. This internal registry currently includes subentries for SQL Anywhere, Adaptive Server Enterprise, and Oracle databases.

In most cases you do not need to modify the JDBC entries. However, if you do need to customize the existing entries or add new entries, you can make changes to the system registry by editing the registry directly or executing a registry file. Changes you introduce in the system registry override the PowerBuilder internal registry entries. See the egreg.txt file in %AppeonInstallPath%\PowerBuilder [version]\IDE for an example of a registry file you could execute to change entry settings.

Supported versions for JDBC

The PowerBuilder JDBC interface uses the pbjdb.dll to access a database through a JDBC driver.

To use the JDBC interface to access the jConnect driver, use jConnect Version 4.2 or higher. For information on jConnect, see your SAP documentation.

To use the JDBC interface to access the Oracle JDBC driver, use Oracle 8 JDBC driver Version 8.0.4 or higher. For information on the Oracle JDBC driver, see your Oracle documentation.

Supported JDBC datatypes

Like ODBC, the JDBC interface compiles, sorts, presents, and uses a list of datatypes that are native to the back-end database to emulate as much as possible the behavior of a native interface.

Preparing to use the JDBC interface

Before you define the interface and connect to a database through the JDBC interface, follow these steps to prepare the database for use:

  1. Configure the database server for its JDBC connection and install its JDBC-compliant driver and network software.

  2. Install the JDBC driver.

  3. Set or verify the settings in the CLASSPATH environment variable and the Java tab of the System Options dialog box.

Step 1: Configure the database server

You must configure the database server to make JDBC connections as well as install the JDBC driver and network software.

To configure the database server for its JDBC connection:

  1. Make sure the database server is configured to make JDBC connections. For configuration instructions, see your database vendor's documentation.

  2. Make sure the appropriate JDBC driver software is installed and running on the database server.

    The driver vendor's documentation should provide the driver name, URL format, and any driver-specific properties you need to specify in the database profile. For notes about the jConnect driver, see Configuring the jConnect driver.

  3. Make sure the required network software (such as TCP/IP) is installed and running on your computer and is properly configured so that you can connect to the database server at your site.

    You must install the network communication driver that supports the network protocol and operating system platform you are using.

    For installation and configuration instructions, see your network or database administrator.

Step 2: Install the JDBC driver

If you install PowerBuilder using the PowerBuilder Installer (an online setup program), the JDBC driver is automatically installed. If you install PowerBuilder using the downloaded installation package (an offline setup program), select JDBC Drivers from the list of components.

Step 3: Verify or set the settings in the CLASSPATH variable and Java tab

Verify that the settings in the PATH and CLASSPATH environment variables or the Classpaths list on the Java tab of the PowerBuilder System Options dialog box point to the appropriate, fully qualified file names, or set them.

If you are using the JDK installed with PowerBuilder, you do not need to make any changes to these environment variables.

If you are using JDK 1.6 or later, you do not need to include any Sun Java VM packages in your CLASSPATH variable, but your PATH environment variable must include an entry for the Sun Java VM library, jvm.dll (for example, path\JDK16\JRE\bin\client).

Configuring the jConnect driver

If you are using the SAP jConnect driver, make sure to complete the required configuration steps such as installing the JDBC stored procedures in Adaptive Server databases. Also, verify that the CLASSPATH environment variable on your computer or the Classpaths list on the Java tab of the PowerBuilder System Options dialog box includes an entry pointing to the location of the jConnect driver.

For example, if you are using jConnect 6.05, you should include an entry similar to the following:

C:\Program Files\SAP\jConnect-6.05\classes\jconn3.jar

For more information about configuring jConnect, see the jConnect for JDBC documentation.

Defining the JDBC interface

Defining the profile

To define a connection through the JDBC interface, you must create a database profile by supplying values for at least the basic connection parameters in the Database Profile Setup - JDBC dialog box. You can then select this profile at any time to connect to your database in the development environment.

For information on how to define a database profile, see Using database profiles.

Specifying connection parameters

To provide maximum flexibility (as provided in the JDBC API), the JDBC interface supports database connections made with different combinations of connection parameters:

  • Driver name, URL, and Properties

    You should specify values for this combination of connection parameters if you need to define driver-specific properties. When properties are defined, you must also define the user ID and password in the properties field.

    For example, when connecting to the jConnect driver, enter the following values in the Driver-Specific Properties field:

    SQLINITSTRING=set TextSize 32000; user=system; password=manager;
  • Driver name, URL, User ID, and Password

    You should specify values for this combination of connection parameters if you do not need to define any driver-specific properties.

    Driver Name: com.sybase.jdbc3.jdbc.SybDriver
    URL: jdbc:sybase:Tds:localhost:2638
    Login ID:    dba
    Password:    sql
  • Driver name and URL

    You should specify values for this combination of connection parameters when the user ID and password are included as part of the URL.

    For example, when connecting to the Oracle JDBC driver, the URL can include the user ID and password:

    jdbc:oracle:thin:userid/password@host:port:dbname

Specifying properties when connecting to jConnect

If you plan to use the blob datatype in PowerBuilder, you should be aware that jConnect imposes a restriction on blob size. Consequently, before you make your database connection from PowerBuilder, you might want to reset the blob size to a value greater than the maximum size you plan to use.

To set blob size, define the jConnect property SQLINITSTRING in the Driver-Specific Properties box on the Connection page. The SQLINITSTRING property is used to define commands to be passed to the back-end database server: SQLINITSTRING=set TextSize 32000;

Remember that if you define a property in the Driver-Specific Properties box, you must also define the user ID and password in this box.