在PB7中，通过JDBC,JDS或JDM接口与数据库互连

Document:

　　Summary

　　This document contains information on how to prepare and define the JDBC JDS and JDM interfaces that are shipped with PowerBuilder 7.

　　This document is a supplement to the connection information found in the PowerBuilder Connecting to Your Database manual. It goes into more detail in regard how to connect to the new JDBC JDS and JDM interfaces and supplies up to date information.

　　What is the JDBC API?

　　Java Database Connectivity (JDBC) is a standard Java API (application programming interface) for database-independent connectivity between Java applications and a wide range of databases. It consists of a set of classes and interfaces written in Java. It can establish a connection with a database, send SQL statements and process the results. It is defined by JavaSoft (http://java.sun.com/products/jdbc).

　　The JDBC API defines Java classes to represent database connections, SQL statements, result sets, database metadata, etc. It allows a programmer writing in the Java programming language to issue SQL statements and process the results. JDBC is the primary API for database access in the Java programming language. The JDBC API is implemented via a driver manager that can support multiple drivers connecting to different databases. The JDBC classes are included in Java 1.1 as the java.sql package.

　　Why use the JDBC Interfaces?

　　Depending on which product you are using, the use of the JDBC interface enables you to develop several types of components and/or applications in PowerBuilder:

?Thin client/server applications: If a client is already running a Java VM (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 such as what is required when using ODBC. The major advantage of using JDBC over ODBC drivers in PowerBuilder is zero client site configurations.
?DataWindow objects to be used in a Java application: Use of the JDBC interface promotes consistency in data types and behavior when developing an application that uses a DataWindow JavaBeans component.
?DataWindow objects to be used in a DataWindow Web control for ActiveX: Use of the JDBC interface (in this case JDM is required) does not require the installation of a database driver on the client, since the JDBC driver can be downloaded along with the Web control ActiveX in a CAB file.
?Transactional components to be deployed on EAServer (Jaguar CTS) that access a database via the Jaguar JDBC interface: Use of the JDBC interface (JDS is required) allows a PowerBuilder transactional component to share the same transaction as a PowerJ component.

　　What are the JDBC JDS and JDM interfaces?

　　The third-party JDBC driver is 100% Java byte code and it requires a Java Virtual Machine (VM) to run the code. The two most popular VMs support different APIs to invoke Java methods. These are Sun VM JDK (or Java Runtime Environment JRE 1.1.7) and Microsoft VM (Microsoft SDK for Java version 3.1). Therefore, we have provided two versions of JDBC interfaces, JDS for Sun VM and JDM for Microsoft VM. Both interfaces provide connectivity to databases that comply with the JDBC API. The PowerBuilder JDBC interface includes the following:

?PBJDM70.DLL: JDM to run with Microsoft's Java Virtual Machine version 3.1 or higher.
?PBJDS70.DLL: JDS to run with Sun's Java Runtime Environment (JRE) version 1.1.7.
?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 the other PowerBuilder interfaces. The package is called classes.zip and is found in \Program Files\Sybase\Shared\PowerBuilder.

　　Preparing to use the JDBC interfaces

　　Depending upon how you are developing your applications and which vendor you are using, you must follow these steps in order to connect to JDBC JDM or JDBC JDS:

?Configure the database server for its JDBC connection and install its JDBC-compliant driver and network software.
?Verify that a Java VM is installed on your machine.
?Install the JDBC interface.
? Set the PATH and CLASSPATH environment variables
?Define a Database Profile from PowerBuilder

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

　　You must obtain the database server software from your vendor. For configuration instructions, see your database vendor's documentation.

　　You must obtain the appropriate JDBC-compliant driver software from your vendor. PowerBuilder supports any JDBC-compliant third party driver including Oracle JDBC driver version 8.03 or higher and Sybase jConnect version 4.0. jConnect can be installed from the EAStudio setup when the Adaptive Server Anywhere database is selected.

　　To use the jConnect driver: In order to use jConnect as a JDBC-compliant driver, you need to execute the proper JDBC stored procedures on the Adaptive Server databases.

For more information on this, consult the jConnect documentation.

　　To use Oracle JDBC driver: Please consult Oracle's documentation.

For installation and instructions for your network software, see your network administrator.

　　 Verify that Java VM is installed on your machine

　　The following VMs are available free of charge from their respective vendors:

　　?For Microsoft VM: Microsoft VM that supports Microsoft SDK for Java 3.1 should be installed. http://www.microsoft.com/java/download.htm

　　?For Sun Java Runtime Environment (JRE) version 1.1.7: http://java.sun.com/products

　　Install the JDBC interface

　　(From this point on, this description assumes that default locations were used during the install).

　　Only PowerBuilder Enterprise includes the JDM and JDS interfaces. To install the JDM and JDS interfaces, choose the Custom installation option and select the JDM and JDS interfaces. The PBJDM70.DLL and PBJDS70.DLL will be installed into \Program Files\Sybase\Shared. The PB.INI should contain entries for JDM and JDS if these were selected for installation.

　　Set the PATH and CLASSPATH environment variables

　　For Windows 95 and Windows 98: Open autoexec.bat file and add or change the PATH and CLASSPATH statements as follows:

　　1. Start the system editor. Choose "Start", "Run" and enter sysedit, then click OK. The system editor starts up with displaying AUTOEXEC.BAT.
　　OR
In Explorer, right mouse click on autoexec.bat, and select Edit.

　　2. Look for the PATH and CLASSPATH statements. You may need to add CLASSPATH. Add the appropriate entries described below.

　　For Windows NT: Change the PATH and CLASSPATH variables as follows:

　　1. Go to Control Panel, Select System, and Select Environment Tab.
　　2. From System Variable section, select PATH variable, in the value field, at the end, add the appropriate entries described below.
　　3. Select CLASSPATH variable from the System Variable section, and at the end of the value field add the appropriate entries described below.

PATH and CLASSPATH Entries:
For Sun JDK: (where x is a version number, currently 1.1)
?Path= C:\Program Files\JavaSoft\JRE\x.x\bin
ClassPath= C:\Program Files\JavaSoft\JRE\x.x\lib\rt.jar;

For Microsoft Java VM:
Make sure that msjava.dll is installed in your WinNT\System32 directory on Windows NT4.0 or Windows\System directory on Windows95/98. A msjava.dll should be in the PATH and not in the CLASSPATH.

For jConnect:
?ClassPath= C:\Program Files\Sybase\Adaptive Server Anywhere 6.0\jconnect\classes;

For Powerbuilder:
?Set ClassPath= C:\Program Files\Sybase\Shared\PowerBuilder\classes.zip

　　Define a Database Profile from PowerBuilder:

　　To define a profile, the minimum parameters required under the Connection Tab are Profile Name, Driver Name, URL, Login ID and Password.

　　?Driver Name: should be provided by your JDBC driver's vendor. For example,

com.sybase.jdbc.SybDriver or
oracle.jdbc.driver.OracleDriver

2. On the Login tab, enter Userid: dba, Password: sql

3. On the Database tab, type in or browse for the desired database file, e.g. asademo.db.

4. In the Interactive SQL window, choose File->Open from a menu. Browse and find the stored procedure sql_anywhere.sql in \Program Files\Sybase\Adaptive Server Anywhere 6.0\jconnect\sp. Click the Execute button to execute the script. Close the Interactive SQL window.

　　Example Database Profile parameters:

Driver: com.sybase.jdbc.SybDriver
URL: jdbc:sybase:Tds:localhost:2638
UserId: dba
Password: sql

　　Example Database Profile parameters: (where database is tsdata, userid is tslogin2 and password is tspass2)

　　Example Database Profile parameters: (where Database Server (machine name) is ora80nt)

　　 "JavaException:
　　Unable to find java PbjdbcSession.class. Check CLASSPATH."
　　This error occurs when the classes.zip in \Program Files\Sybase\Shared\PowerBuilder is not in the CLASSPATH. Also the JRE (rt.jar or classes.zip) may not be in the CLASSPATH.

　　 "PowerBuilder
　　JZ0D5: Error loading protocol com.sybase.tds.TDS"
　　This happens at the PowerBuilder Database Profile when you type in the URL with the TDS (all uppercase) instead of Tds. The URL is case sensitive.

　　 "PowerBuilder
　　Non SQL error: Could not load class oracle.jdbc.driver.OracleDriver"
　　This error occurs when you try to create a database profile for Oracle 8.0.4 when the Oracle Thin JDBC compliant driver is not installed or it is not in the CLASSPATH.

　　 "PowerBuilder
　　JZ00L: Login failed. Examine the SQL warning channel to this exception for the reason(s)"
　　This error occurs if you enter an incorrect password and/or login ID in the Password and Login ID fields of the Database Profile.

　　 "PowerBuilder
　　JZ006: Caught IOE exception java.net.ConnectException:Connect refused"
　　This error occurs when the Adaptive Server Anywhere (ASA) database was not started before trying to connect to the database via the Database Profile.

　　 "PowerBuilder
　　Non SQLError
　　Could not load class Driver_name"
This error occurs if you enter an incorrect Driver and URL in the Database Profile.

　　Frequently Asked Questions

Q: Does PowerBuilder 7 support Oracle 's JDBC-compliant Thin driver and OCI driver via JDBC JDS/JDM?

A: We support both the Thin and the OCI drivers. The Thin driver is preferable because the OCI driver routes all JDBC calls through C/C++ to access the OCI API. However, both drivers should work.

Q: When should I use the JDM interface and when should I use JDS?

A: When you are developing a DataWindow object to be used in a DataWindow Web control for Microsoft ActiveX, you must use the JDM version of the JDBC interface.
If you are developing a PowerBuilder transactional component to be deployed on Jaguar CTS that will use the Jaguar JDBC interface (this allows the PowerBuilder transactional component to share the same transaction as a PowerJ component), you must use the JDS version of the JDBC interface.

Q: Is there a tool that PowerBuilder provides to trace any database connection through the JDBC interface?

A: Yes, the JDBC Driver Manager Trace tool is used to trace any database connection that you access in PowerBuilder through the JDBC interface. The trace tool is also available on all supported PowerBuilder platforms.

　　The JDBC Driver Manager Trace logs errors and informational messages originating from the Driver object currently loaded (such as Sybase's jConnect JDBC driver) when PowerBuilder connects to a database via the JDBC interface. It writes this information to a default log file named JDBC.LOG or to a log file that you specify. The amount of trace output varies depending on the JDBC driver being used.

　　The JDBC Driver Manager Trace tool cannot trace connections through one of the native database interfaces.