Using the Java Database Connectivity Driver

This chapter discusses:

The Java database connectivity (JDBC) driver.
JDBC driver configuration.
JDBC driver connection details.
JDBC driver security considerations.
SQL grammar.
JDBC driver features.
JDBC driver troubleshooting.
JDBC driver terminology

Using the JDBC Driver

The Oracle JD Edwards EnterpriseOne JDBC driver is a partial Type 3 JDBC driver implementation that provides access to JD Edwards EnterpriseOne application and product data. In addition to masking the details for the many supported databases and platforms that JD Edwards EnterpriseOne products support, the JDBC driver encapsulates additional filtering and processing that must occur in order to preserve data and semantic integrity.

The JDBC driver provides Java applications with a logical connection to JD Edwards EnterpriseOne data. Applications view this logical connection as a normal database connection, despite the fact that specific data source details are hidden. In some cases, the JDBC driver maps a single logical connection to multiple physical data sources. In a sense, the JDBC driver presents the set of data that JD Edwards EnterpriseOne products manage as a database.

When to Use the JDBC Driver

Use the JD Edwards EnterpriseOne JDBC driver if you are developing or using software that requires or expects you to plug in a JDBC driver for data access, and you need to interact with JD Edwards EnterpriseOne application and product data. This list provides some examples about when to use the JDBC driver:

A commercial reporting tool (such as Oracle BI Publisher Enterprise Edition)
A commercial database middleware library (such as TopLink).
A commercial database visualization tool (such as DBVisualizer).

Connection Mode

The JD Edwards EnterpriseOne product suite employs a diverse set of data sources. Specific filtering must occur for certain data sources while others can be used as is. The JDBC driver defines various connection modes that indicate the type of additional filtering and processing that the data requires. Application code designates the connection mode when it establishes new connections.

Currently the only connection mode supported is enterpriseone, which establishes a connection for reading JD Edwards EnterpriseOne enterprise resource planning (ERP) 9.0 data. This connection mode is implemented using JDBj, the Java class library that encapsulates most aspects of ERP data access middleware functionality such as object configuration management (OCM), ERP triggers, ERP business views, ERP row and column security, and decimal scrubbing.

The enterpriseone connection mode provides read only access to ERP data. The concept of connection modes enables the extension of the JD Edwards EnterpriseOne JDBC driver for other JD Edwards EnterpriseOne products as well.

Versions

Currently, only version 1.0 of the driver exists. The JDBC driver is used for read-only operations solely. Driver major version 1 and driver minor version 0 are available programmatically by calling Driver.getMajorVersion and Driver.getMinorVersion respectively.

JDBC Driver Configuration

You configure the JDBC driver by copying the e1jdbc.jar driver jar file to the class path of the application that will use the JDBC driver. The e1jdbc.jar jar file is located in the classes folder of the JD Edwards EnterpriseOne Data Access Server (DAS).

JDBC Driver Connection Details

Java code that uses a JDBC driver must register the driver class name and designate a connection URL and optional connection properties that collectively identify the data source that the JDBC driver is accessing.

Driver Class Name

The name of the main JD Edwards EnterpriseOne JDBC driver class is com.jdedwards.jdbc.driver.Driver. You must register this class with the JDBC Driver Manager before attempting to connect to it.

Example for Registering Driver Class Name

You register the JD Edwards EnterpriseOne JDBC driver using Class.forName. This example shows how to register the JD Edwards EnterpriseOne JDBC driver:

Class.forName("com.jdedwards.jdbc.driver.Driver");

Some environments provide alternate mechanisms for registering JDBC drivers.

Connection URL

The format for the connection URL that the JDBC driver accepts is provided here:

jdbc:oracle:connectionMode://hostname:port/connectionTarget;propertyName1=
(propertyValue1); propertyName2=(propertyValue2);...

The connection mode designates the type of JD Edwards EnterpriseOne product data that you plan to access.

You must pass the following values to DriverManager.getConnection when establishing a JD Edwards EnterpriseOne JDBC connection:

Connection mode: enterpriseone.
Connection target: The ERP environment.
User name and password: The ERP user name and password.

The connection target, user name, and password depend on the connection mode.

Connection Properties

The JDBC driver recognizes several connection properties that you can set when you establish a new connection. You specify these in the connection URL or in the java.util.Properties object that you pass to DriverManager.getConnection. If you specify the same property in both places, the value in the URL takes precedence.

If the property value contains one or more semicolons, you may need use parentheses to delimit the property value. Otherwise, parentheses are optional.

The following table lists the connection properties that the JD Edwards EnterpriseOne JDBC driver recognizes. The set of valid connection properties varies based on the connection mode. The JDBC driver ignores any connection properties that are not listed in the table:

Connection Mode	Property Name	Property Value
enterpriseone	enterpriseone.role	The ERP role, if any. The default is *ALL. This property value applies only if you are accessing ERP 9 or later data.
enterpriseone	impersonate	The user name, which will be substituted for authorization purposes at runtime in a proxy authentication mode. This is discussed in the JDBC Security Considerations section.

Connection Mode

Property Name

Property Value

enterpriseone

enterpriseone.role

The ERP role, if any. The default is *ALL. This property value applies only if you are accessing ERP 9 or later data.

enterpriseone

impersonate

The user name, which will be substituted for authorization purposes at runtime in a proxy authentication mode.

This is discussed in the JDBC Security Considerations section.

Example Showing How to Use Connection Properties

This example code shows how to connect to ERP environment ADEVHPO2I:

Connection connection 
   = DriverManager.getConnection(
   "jdbc:oracle:enterpriseone://ADEVHPO2I;", 
   "myuser", 
   "mypassword");

JDBC Driver Security Considerations

The JD Edwards EnterpriseOne JDBC driver requires a user name and password for authentication. At the same time, the same user name is authorized for the environment and role, which are passed in the connection URL. If you do not specify a role in the connection URL, the system uses *ALL. This model poses a serious security risk and a high maintenance requirement for third-party systems where a single JDBC connection is shared across multiple users.

To alleviate this problem, the JD Edwards EnterpriseOne JDBC driver allows for a proxy authentication model by way of the impersonate connection property. In this model, the authentication and authorization are separated into two steps:

All users are authenticated through the security server with a sign-on EnterpriseOne proxy user name and password.

Important! This user name must be the same as the JDBj Bootstrap session user ID of the Data Access Server instance to which you are connecting.
The impersonate user name that is passed in the connection property, is authorized for the environment and role. If you do not specify a role in the connection URL, the system uses *ALL.

SQL Grammar

The JD Edwards EnterpriseOne JDBC driver supports different flavors of SQL depending on the connection mode.

SQL Grammar for JD Edwards EnterpriseOne Connection Modes

The JDBC driver implements JD Edwards EnterpriseOne connection modes using JDBj, which is a Java data access API. The JDBC driver parses SQL statements and transforms them into JDBj operations.

In general, the JDBC driver in EnterpriseOne connection accepts only SELECT statements. All other operations, such as INSERT, UPDATE, DELETE, ALTER, DROP, and CREATE statements are not supported. If the driver cannot parse the SQL statement, then the JDBC driver throws an SQLException with a message that explains the parsing error.

The following table describes the SQL grammar that the parser recognizes. In this table, SQL keywords are in bold font (SELECT.) SQL keywords are not case sensitive. Rule names are listed in italics (where-clause.) Terminal symbols are noted. Optional clauses are listed in square brackets (,[ order-by-clause ].) Clauses that may repeat 0 or more times are listed in parenthesis followed by an asterisk (( , database-object-with-alias )*.) A vertical bar indicates that one of a set of options is valid (* | fields).

Rule	Definition
select-statement	SELECT fields-clause FROM database-objects [ where-clause ] [ group-by-clause ] [ order-by-clause ]
subquery-clause	SELECT fields-clause FROM database-object-with-alias [ where-clause ] [ group-by-clause ]
database-objects	database-object-with-alias ( , database-object-with-alias )*
database-object-with-alias	database-object [ ID ] Note. ID is a terminal symbol.
database-object	ID Note. ID is a terminal symbol. Database object names are table and business view names. Do not qualify these with an owner or schema. The JDBC driver uses its own data source resolution mechanisms (such as an ERP system’s OCM) to resolve database object name qualifiers. However, if you require a schema to satisfy some third-party software requirements, you qualify the table or business view names with JDE as the schema. JD Edwards EnterpriseOne does not have a schema or catalog concept and this qualification is ignored at runtime.
fields-clause	* \| fields \| field-function-expressions
fields	field ( , field )* \| field AS alias ( , field AS alias )*
field	database-object [ . ID [ . field-instance ]] Note. ID is a terminal symbol. Field names are in the format database-object.field.instance, where database-object and instance are optional. Field names match data dictionary names rather than physical column names. For example, use AN8 (the data dictionary name for address book number) rather than ABAN8 (the physical F0101 column name). Instance is an integer that refers to the instance of a particular field when used in a self-join.
field-instance	INTEGER_LITERAL Note. INTEGER_LITERAL is a terminal symbol.
field-function-expressions	field-function-expression ( , field-function-expression )* \| field-function-expression AS alias( , field-function-expression AS alias )*
field-function-expression	type1-field-function-expression \| type2-field-function-expression \| type3-field-function-expression
type1-field-function-expression	AVG \| COUNT \| SUM [ DISTINCT ] ( field ) Note. See the examples provided in the following table.
type2-field-function-expression	MIN \| MAX ( field ) Note. See the examples provided in the following table.
type3-field-function-expression	*COUNT ( )**
field-reference	field
literals	literal ( , literals )*
literal	STRING_LITERAL \| INTEGER_LITERAL \| FLOATING_POINT_LITERAL \| NULL \| ? Note. STRING_LITERAL, INTEGER_LITERAL, and FLOATING_POINT_LITERAL are terminal symbols.
where-clause	WHERE or-expression
group-by-clause	GROUP BY group-by-fields
order-by-clause	ORDER BY order-by-fields
order-by-fields	order-by-field-and-direction ( , order-by-field-and-direction )*
order-by-field-and-direction	field-reference [ order-by-direction ]
order-by-direction	ASC \| DESC
or-expression	and-expression ( OR and-expression )*
and-expression	not-expression ( AND not-expression )*
not-expression	[ NOT ] sub-expression
sub-expression	exists-clause \| relational-expression \| ( or-expression )
exists-clause	EXISTS ( subquery-clause )
relational-expression	field field-expression \| in-expression \| between-expression \| like-expression \| is-null-expression Note. Inconsistent results might occur if you use a field that requires decimal scrubbing within a relational expression.
field-expression	comparison-op ( ( [ ALL \| ANY ] ( subquery-clause ) ) \| element )
in-expression	[ NOT ] IN ( subquery-clause \| elements )
between-expression	[ NOT ] BETWEEN element AND element
like-expression	[ NOT ] LIKE element
is-null-expression	IS [ NOT ] NULL
elements	element ( , element )*
element	field-reference \| literal
comparison-op	= \| != \| <> \| > \| >= \| < \| <= \| = \| = \| =

The following are some examples of SQL statements that are allowed:

Object Type	Statement
Table	select AN8 from F0101 or select AN8 AS AddressBookNumber from F0101
Select All Table	select * from F0101
Table Join	select avg(t1.an8), min(t1.an8),max(t1.an8), count(t1.Name), sum(t1.an8), avg(distinct t1.an8), count(distinct t1.name),sum(distinct t1.an8), t1.an8 from F0101 t0, F0010 t1 where t0.an8=t1.an8 group by t1.an8
Table Union	select F4211.KCOO, F4211.DOCO, F4211.DCTO , MAX(F4211.LNID), COUNT(F4211.DOCO), MIN(F4211.LNID), min(F4211.AN8) from F4211 group by F4211.LNID,F4211.DOCO, F4211.DCTO,F4211.KCOO UNION select F42119.KCOO, F42119.DOCO, F42119.DCTO , MAX(F42119.LNID), COUNT(F42119.DOCO), MIN(F42119.LNID), min(F42119.AN8) from F42119 group by F42119.LNID, F42119.DOCO, F42119.DCTO,F42119.KCOO order by F4211.DOCO DESC, F4211.KCOO asc
Single Table Business View	select AN8 from V0101C
Multiple Table Business View	select F0101.AN8, F0116.AN8 from V0101JE
Union Business View	select max(F4211.KCOO), max(F4211.KCOO) from V4211A

JDBC Driver Features

The JDBC driver supports different JDBC features depending on the connection mode. In general, the JDBC driver implements the JDBC 3.0 specification as it is defined in Java 2 Platform Standard Edition version 5.0 (also called version 1.5.)

JDBC Features for EnterpriseOne Connection Mode

The JDBC Driver EnterpriseOne connection mode explicitly does not support the following JDBC features:

Catalog methods (in DatabaseMetaData) with the exception of getCatalogs, getSchemas, getTables and getColumns.
Cursor names (Statement.setCursorName and ResultSet.getCursorName).
ResultSetMetaData as returned by PreparedStatement.getMetaData (the same information is available from ResultSet.getMetaData).
Result set holdability (Connection.createStatement, Connection.prepareStatement, Connection.prepareCall, and Statement.getResultSetHoldability).
Savepoints (Connection.setSavepoint and Connection.rollback).
Scrollable result sets (Connection.createStatement, Connection.prepareStatement, Connection.prepareCall, Statement.getResultSetType, and ResultSet.getType).
Stored procedures (Connection.prepareCall).
Type map (Connection.setTypeMap, Connection.getTypeMap, and ResultSet.getObject).
Update operations that involve EnterpriseOne Software data (Statement.executeUpdate, PreparedStatement.executeUpdate, and ResultSet update methods).

In most cases, invoking these features results in an SQLException with a message describing the specific feature that is not supported.

JDBC Driver Troubleshooting

When errors occur, the JDBC driver throws SQLExceptions. In your code, it is helpful to print or log these exceptions so that you can inspect or report them as part of the troubleshooting process. It is especially helpful to inspect entire exception stack traces, because traces include exception messages, class names, lines numbers, and cause exceptions that lead to SQLExceptions.

When you evaluate a series of exceptions in a trace, you should concentrate on the first exception because it is often the cause of subsequent exceptions.

Some example exceptions and their recovery are discussed here.

No Suitable Driver

Exception: java.sql.SQLException: No suitable driver

Cause: The JD Edwards EnterpriseOne JDBC driver uses the JD Edwards EnterpriseOne Data Access Server, which uses the native JDBC drivers to access physical data. If your system administrator has not configured the Data Access Server's class path to include the necessary drivers, then the JDBC driver throws this exception on any attempt to read physical data.

Recovery: Contact your system administrator and ensure that all of the applicable JDBC drivers are included in the Data Access Server's class path.

Data Source for F0010, TBLE Not Found

Exception: com.jdedwards.services.objectlookup.DataSourceNotFoundException: Data source for F0010, TBLE not found. (with a cause message in parenthesis)

Cause: This exception indicates that the JDBC driver cannot access its system tables in ERP mode. Table F0010 is the first system table that the JDBC driver attempts to access. Be sure to check the cause message that is attached to the exception message. The exception trace usually includes a direct cause as well.

Recovery: This exception occurs as a result of configuration errors for the JD Edwards EnterpriseOne Data Access Server. You should check the cause exception and follow the recovery instructions listed for those exceptions. If none apply, contact your system administrator and verify that the [JDBj-BOOTSTRAP DATA SOURCE] section of jdbj.ini file for the Data Access Server references a valid data source. The JDBj-BOOTSTRAP DATA SOURCE section describes the location for the ERP system tables, like F0010.

JDBC Driver Terminology

The following table provider terminology used in this chapter. These terms are not available in the glossary.

connection mode	Connection mode is a term that applies only to the JDBC driver and provides an indication of the type of additional filtering and processing that the JD Edwards EnterpriseOne data that you are accessing requires. Application code designates a connection mode when it establishes each new connection.
connection properties	Properties that applications pass to the JDBC drivers when establishing a new connection in order to configure a particular connection type. The concept of connection properties is a standard JDBC mechanism, but each driver defines its own set of recognized connection properties.
connection URL	A string that identifies a particular data source to which to connect. The concept of a connection URL is a standard JDBC mechanism, but each driver defines its own URL syntax.
driver class name	A string that identifies the primary class for a JDBC driver. You must register this class name with the JDBC driver manager before using it. this is a standard JDBC concept, but each driver defines its own driver class name.
driver manager	The JDBC class that manages multiple registered JDBC drivers and dispatches connection initialization requests to them. the Java driver manager class is java.sql.DriverManager.
ERP data	Data that is managed within an ERP environment.
JDBj	The Java class library that encapsulates most aspects of JD Edwards EnterpriseOne software data access middleware functionality such as OCM, ERP triggers, ERP business views, ERP row security, and decimal scrubbing.
JDBC type 3 driver	JDBC type driver 3 driver is a network-protocol, all-Java driver. This style of driver translates JDBC calls into the middleware vendor's protocol, which is then translated to a DBMS protocol by a middleware server. The middleware provides connectivity to many different databases.