This chapter contains the following topics:
The JD Edwards EnterpriseOne Open Data Access ODBC driver is a read-only driver that is compliant with version 2.5 or higher. Front-end Windows query and reporting tools can use ODA to access the JD Edwards EnterpriseOne database. ODA supports these front-end tools:
Microsoft Query
Microsoft Access
Microsoft Excel
ODBCTEST
Crystal Reports
Microsoft Analysis Service (not certified)
ODA sits between the front-end Query and Reporting tool and the JD Edwards EnterpriseOne-configured ODBC drivers.
To access JD Edwards EnterpriseOne data with the ODA ODBC driver, your system must meet the minimum technical requirements (MTR) for JD Edwards EnterpriseOne. MTRs are updated for each release and are available on the My Oracle Support website. Before you install ODA, ensure that your system meets the specified hardware and software requirements.
Hardware requirements include:
IBM-compatible personal computer.
Hard disk with 6 MB of free disk space.
At least 16 MB of random access memory (RAM).
Software requirements include:
JD Edwards EnterpriseOne.
JD Edwards EnterpriseOne Open Data Access driver (JDEOWODA.dll).
The 32-bit ODBC Driver Manager, version 3.0 or later (ODBC32.dll).
This file is included with the ODBC Database Drivers.
Microsoft Windows 95 or later, or Windows NT 4.0 or later.
Note:
The use of the ODA ODBC driver by 16-bit applications on Windows 95 is not supported.The JD Edwards EnterpriseOne installation installs the components required by ODBC database drivers. You might also find these additional files:
| File | File Name |
|---|---|
| ODA Driver | JDEOWODA.DLL |
| ODA Driver Help | JDEOWODA.HLP |
| Release Notes | README.TXT |
Note:
OLEDB is a driver for SQL Server. However, OLEDB data source is not supported for ODA. If you are using ODA with SQL Server, use ODBC to set up your data source.The JD Edwards EnterpriseOne ODA ODBC driver architecture has five components:
| Component | Description |
|---|---|
| Application | A front-end Query and Reporting tool that calls the ODA driver to access data from the JD Edwards EnterpriseOne database. |
| Manager | Loads and unloads drivers on behalf of an application. Processes ODBC calls or passes them to the ODA driver. |
| JD Edwards EnterpriseOne ODA Driver | Passes some of the ODBC requests directly to the vendor's ODBC driver. If specific data types for JD Edwards EnterpriseOne are used, then the SQL SELECT statement is modified before sending it to the vendor's ODBC driver. After the data is returned from the vendor's ODBC driver, the JD Edwards EnterpriseOne ODA ODBC driver might need to manipulate the data so that it displays correctly in the application. |
| Vendor Driver | Processes ODBC function calls and submits SQL requests to the specific data source. If necessary, the driver modifies an application's request so that the request conforms to the syntax supported by the associated DBMS. |
| Data Source | The data that you want to access, as well as the operating system, DBMS, and network platform for the data. |
This section provides an overview of data sources and discusses how to
Add a data source.
Modify a data source.
Delete a data source.
Configure a data source.
Connect a data source.
Although the ODA driver is automatically registered as part of the installation process, you might need to add a driver data source. You can also add a file data source or a system data source. A system data source can be used by more than one user on the same machine. A system data source is a data source that you have set up with a system data source name (DSN). The system DSN can also be used by a system-wide service, which can then gain access to the data source even if no user is logged on to the machine. You can delete any of the data sources.
After you add a data source, you must configure and connect it. You can modify the configuration and connection setting for an existing data source. For example, you can configure the ODA driver so that you can view currency data in the correct format.
If you use Oracle, you must create another ODBC DSN, named OneWorld ODA Ora, so that you can access the Oracle data source through ODA. Specific information for doing this is included in the online release notes.
You can customize the list of functions that are enabled in ODA. Advanced configuration is optional. If you choose not to customize the list of functions enabled in ODA, the system uses a default list of settings.
You access the ODBC button from the Control Panel on your Windows workstation. When you click the ODBC button, a User Data Sources dialog box appears.
After you add the data source, you must configure it and connect it. This table explains how to navigate on the User Data Sources dialog box to add a data source:
| Function | Navigation on User Data Sources dialog box |
|---|---|
| Add an ODA Driver Data Structure | On the User Data Sources dialog box, click Add. On Add Data Source, select the JD Edwards EnterpriseOne Open Data Access driver from the Installed ODBC Drivers list, and then click Finish. |
| Add a File Data Source | On the User Data Sources dialog box, click the DSN tab. On File Data Sources, click Add. On Add Data Source, select the JD Edwards EnterpriseOne Open Data Access driver from the Installed ODBC Drivers list, and then click Finish. |
| Add a System Data Source | On the User Data Sources dialog box, click the System DSN tab, and then click Add. On system Data Sources, click Add. On Add Data Source, select the JD Edwards EnterpriseOne Open Data Access driver from the Installed ODBC Drivers list, and then click Finish. |
You can modify an existing data source. After you access the appropriate data source, select Configure and then modify the existing configuration settings.
To delete a data source, access the appropriate data source, select remove, and click Yes to confirm the delete.
To modify an existing data source, access the appropriate data source type and then select a data source from the available list. Click Configure. When you add a data structure, the Configure Data Source tab appears. Enter the information as shown in this table, and then click OK:
| Field Name | Description |
|---|---|
| Data Source Name | Specify the name for the JD Edwards EnterpriseOne Open Data Access driver. |
| Description | Specify the description of the driver that you are adding. The Description entry cannot exceed 79 characters. |
After the data source is configured, the Connect form appears. You can also select one or more table and business view display Options. On the Connect form, select one or more of these options:
| Option Name | Description |
|---|---|
| Convert User Defined Codes | Select this option to return the associated description of the user-defined field instead of the user-defined code. The associated description is more descriptive because it is a text description instead of a code that is used for the user-defined code. The default option is to display the associated description instead of the user-defined code. |
| Convert Currency Values | Select this option to convert currency fields to the correct values. |
| Use Long Table or Business View Names | Select this option to view long table or view names. |
| Use Long Column Names | Select this option to view long column names |
| Tables Only | Select this option to view only JD Edwards EnterpriseOne tables. |
| Business Views Only | Select this option to view only JD Edwards EnterpriseOne business views. |
| Tables and Business Views | Select this option to view both JD Edwards EnterpriseOne tables and JD Edwards EnterpriseOne business views. |
This section discusses how to:
Manipulate data.
Use keywords in the connection string.
Run a query using Microsoft Excel.
The JD Edwards EnterpriseOne database contains object and column names, specific data types and security rules that must be converted or applied so that the data is presented correctly. The specific data types and rules include decimal shifting, Julian date, currency, media object, security, and user-defined codes. In some instances, ODA modifies the SQL SELECT statement, as well as the data, so that it appears correctly within the chosen tool. Once the ODA driver is properly installed and an ODBC data source is established, you can use the functionality of the ODA driver. When a SQL connection is established, the environment of the current connection is stored in the system as the database name. SQLGetInfo can access this value later or it can be used for future connections.
You can use these specific JD Edwards EnterpriseOne features with JD Edwards EnterpriseOne ODA:
| Feature | Description |
|---|---|
| Long Table and Business View Names | Long table and business view names enable you to see a descriptive name when you view an object list. You can use either the descriptive names or the original JD Edwards EnterpriseOne object name in the SELECT statement.
Note: This option might not be available for all third-party products (for example, ShowCase STRATEGY products prior to the 2.0 release or Crystal Reports because the long names contain special characters that are not handled correctly by these tools. |
| Long Column Names | Long column names enable you to see a descriptive name when viewing any columns list. You can still use either the descriptive names or the original JD Edwards EnterpriseOne column name. For example, you can use either of these statements to retrieve information from the F0101 table:
|
| Julian Date | Julian date modifies all references to Julian date columns to convert the date to an SQL-92 standard date. The JD Edwards EnterpriseOne Julian date is converted to a standard date value that can be used in date calculations. This feature enables you to use duration or other date calculations in both the SELECT (result data), WHERE, and HAVING clauses and the ORDER BY clause.
The SQL SELECT statement is modified to before a data calculation to convert the JD Edwards EnterpriseOne Julian date column to a standard date. The modification to the SQL SELECT statement is based on the data source that is being accessed because of driver differences in handling date calculations. If the original column value is zero, the date conversion results in a date value of 1899-12-31. To remove these values, this condition should be added to the WHERE clause in the SELECT statement, where DATECOL is the JD Edwards EnterpriseOne Julian date column:
|
| Decimal Shifting | All references to decimal-shifted columns are modified to shift the decimal point to cause the result data to be correct. This feature enables SQL statements that contain complex expressions, aggregates, and filtering to run and return accurate results.
The SQL SELECT statement is modified to divide the column by the appropriate number of decimal places so that the data is returned correctly and to make compare operators work for filtering. |
| Currency | Currency columns are limited to single-column references in the selected columns list. Returned data is converted using the standard JD Edwards EnterpriseOne currency conversion routines. All other references to the currency column in the SQL statement are passed through to the native driver. You must understand how the currency column is used to make effective use of filtering.
Before selected columns are returned, the JD Edwards EnterpriseOne Open Data Access driver converts any currency columns to the correct value. Currency columns used in the WHERE or HAVING clause are processed based on the non-converted currency value. Currency columns in the GROUP BY or ORDER BY clause are grouped and sorted by the non-converted currency value. |
| Media Object | The Media object column, TXVC, in the F00165 table storage is limited to single-column references in the selected columns list. ODA returns media data in plain text or rich text format (RTF) and truncates other binary data, such as an image. The size limitation of the text or RTF is 30,000 characters, and text will be truncated when it reaches this limitation. |
| Column Security | When column security is active, any reference to restricted columns causes an error to be returned when the SELECT statement is examined, including the use of * (asterisk-selecting all columns) in the select clause, as defined by the SQL-92 standards. You will receive an error if you are not authorized for all of the columns in the table. |
| Row Security | When row security is active, the statement is modified to include the appropriate WHERE clause for filtering secured rows. You will only see rows that you are authorized to access along with getting accurate results using aggregate functions-for example, SUM or AVG. |
| User Defined Codes | When user-defined codes (UDCs) are enabled, you see the associated description instead of the internal code when the column data is returned. This processing affects only the returned data and has no effect on the other parts of the Select statement (for example, Where, Order By and so on). This is an optional setting that can be configured when you set up the driver.
Before the UDC is returned to you, the JD Edwards EnterpriseOne Open Data Access driver converts the code to the associated description. The UDC columns used in the WHERE or HAVING clause are selected based on the non-converted code and the UDC columns referenced in the GROUP BY and ORDER BY clause are grouped and sorted by the non-converted code. |
This section discusses keywords that you can use in a connection string when you write your own database applications.
You can use C programming language to write database applications that directly invoke SQL APIs that are supported by ODA, such as SQLDriverConnect and SQLBrowseConnect. This table lists keywords that you use in the connection string when you write your own database applications:
| Key | Value | Description | Input Connection String | Output Connection String |
|---|---|---|---|---|
| CONVERTUDC | Y or N (default value is N) | Convert UDC or not | Optional. If not in the connection string, load from INI/registry settings (JD Edwards EnterpriseOne ODA DSN settings). | From the input string or INI/registry settings. |
| CONVERT CURRENCY | Y or N (default value is N) | Convert currency or not | Optional. If not in the connection string, load from INI/registry settings (JD Edwards EnterpriseOne ODA DSN settings). | From the input string or INI/registry settings. |
| SHIFTDECIMALS | Y or N (default value is Y) | Use decimal shift or not | Optional. If not in the connection string, load from INI/registry settings (JD Edwards EnterpriseOne ODA DSN settings). | From the input string or INI/registry settings. |
| CONVERTJULIAN DATES | Y or N (default value is Y) | Convert Julian dates or not | Optional. If not in the connection string, load from INI/registry settings (JD Edwards EnterpriseOne ODA DSN settings). | From the input string or INI/registry settings. |
| DISPLAYOPTIONS | 0/1/2 (no default value) | Display TBLE, BSFN or both | Optional. If not in the connection string, load from INI/registry settings (JD Edwards EnterpriseOne ODA DSN settings). | From the input string or INI/registry settings. |
| LONGTABLE NAMES | Y or N (default value is Y) | Use long names for tables or not | Optional. If not in the connection string, load from INI/registry settings (JD Edwards EnterpriseOne ODA DSN settings). | From the input string or INI/registry settings. |
| LONGCOLUMN NAMES | Y or N (default value is Y) | Use long names for columns or not | Optional. If not in the connection string, load from INI/registry settings (JD Edwards EnterpriseOne ODA DSN settings). | From the input string or INI/registry settings. |
| UID | <string> | User ID | Required by JDEDriverConnect (SQL_DRIVER_NOPROMPT). | The same as the input if not overwritten by OW login. |
| PWD | <string> | Password | Required by JDEDriverConnect (SQL_DRIVER_NOPROMPT). | The same as the input if not overwritten by OW login. |
| ENVIRONMENT | <string> | Environment | Required by JDEDriverConnect (SQL_DRIVER_NOPROMPT). | The same as the input if not overwritten by OW login. |
| DBQ | <string> | The same as the ENVIRONMENT | Work as ENVIRONMENT, if ENVIRONMENT not specified. | Removed if ENVIRONMENT exists. |
| DSN | <string> | Data source | Optional. Uses DEFAULT if invalid. | Overwritten by login. |
If you use the Microsoft Analysis Service tool, you can use connection string keywords to create a new data source. Use this example to write a connection string:
DSN=OneWorld ODA;DBQ=ADEVHP02;
This section discusses how to use Microsoft Excel to create and run a query.
To run a query using Microsoft Excel:
From the Data menu, select Get External Data.
Select Create New Query.
On the Databases tab, select the appropriate data source (for example, JD Edwards EnterpriseOne Local or JD Edwards EnterpriseOne ODA).
Because Excel uses file data sources, the ODA data source you set up in the 32-bit ODBC Administrator does not appear on the list of databases. You should create a File-type Data Source by selecting New Data Source and then follow the procedures for setting up a data source.
When you select the ODA data source, you might need to log on to JD Edwards EnterpriseOne to use the ODA driver. Once you log on, you will not see the Solution Explorer because it is only activated so that the ODA driver can check security and environment mappings.
The Excel Query Wizard displays a list of available tables in the JD Edwards EnterpriseOne data source. Expanding any table name shows the available columns or fields in each table. If you are using the ODA driver, you see long descriptions of each field (for example, DateUpdated). If not, you see the alpha codes for the fields (for example ABUPMJ).
To translate field and column names from the JD Edwards EnterpriseOne alpha codes, use the F9202 table. Select all rows and sort (on FRDTAI) to create a cross-reference.
The first two letters of all JD Edwards EnterpriseOne column names are the application code, and the remaining letters are in this table as a suffix.
Finish building your query with Query Wizard and save the query.
Run your query and review it in Excel or MicroSoft Query.
After you run a query from Excel, if you view the results using Microsoft Query, results are returned quickly. MicroSoft Query selects a page at a time. If you are working with a large result set, you should close JD Edwards EnterpriseOne and any applications that require a lot of memory so that you can more quickly navigate through the records. If you convert the query results directly into a spreadsheet instead of into Microsoft Query, the process might take significantly longer, and you cannot view the results until the entire file builds.
To verify the outcome of each query, you should run each one first using the non-ODA JD Edwards EnterpriseOne data source and then use the ODA data source and compare the results.
This section discusses error messages that you might receive.
JD Edwards EnterpriseOne Open Data Access driver sends error messages. The messages are placed in the ODBC error message queue where the application can retrieve them using the standard ODBC error mechanism. The JD Edwards EnterpriseOne messages look like this:
[J.D. Edwards][OneWorldODA Driver]MESSAGE TEXT
This is a list of the errors that you can receive from the driver:
| Error Message | Description |
|---|---|
| Configuration Request Error | This error might occur when you add a new data source if you do not provide enough information for the driver and it cannot show a configuration dialog.
You must either pass enough information to the driver or allow the driver to prompt for more information. |
| Option Value Changed | This is an informational message that occurs when you attempt to set a connection or statement option to a value that the driver does not accept. The driver then changes the value to an acceptable default value and uses this message to let you know that the value has changed.
The JD Edwards EnterpriseOne Open Data Access driver changes values in these areas: Setting the row set size to a value other than one. The driver currently only supports single-row row sets. Setting the login time out to a value other than zero. The driver currently only supports zero in this option, which means, timeout disabled. |
| Data Source Name Is Not Valid | The data source you entered is not a valid ODBC data source name. This error occurs when you are adding a new data source or configuring an existing data source. You must enter a name that follows the ODBC data source naming convention. |
| Data Source Does Not Exist | This error occurs when you attempt to use a data source that does not exist. You must enter the name of an existing data source. If you get this error when you attempt to connect to a data source, you might need to create a default data source. |
| Unable to Allocate Memory | The JD Edwards EnterpriseOne Open Data Access driver was not able to allocate enough memory to continue. You must close some applications and try the operation again. Make sure that you meet the minimum system requirements. |
| Invalid Type of Request | You attempted to use a configuration option that is unknown to the driver. The driver supports these options when configuring data sources:
|
| Data Truncated | The conversion of column data resulted in a truncation of the value. You should allocate more room for the column data to avoid this informational message. |
| Syntax Error or Access Violation | The statement contained a syntax error and no further information is available. |
| Unable to Display Connection Dialog | The driver encountered an error when attempting to display the connection dialog. |
| Cross System Joins Not Supported | This error occurs in one of two situations:
You must make sure that you are referencing tables on a single system or a business view that contains tables on a single system. |
| Unable to Connect to the JD Edwards EnterpriseOne Environment | The driver could not establish a connection to the JD Edwards EnterpriseOne environment. This connection is required before a successful connection can be made to this driver. |
| Internal Data Conversion Error | The driver encountered an unknown error during data conversion. |
| Internal Execution Error | The driver experienced an unexpected error during a statement execution. |
| User Defined Code Columns Can Only Be in Simple Column References | A user attempted to use a User Defined Code column in a complex expression. The JD Edwards Enterprise Open Data Access driver only allows such columns to be simple references. |
| Currency Columns Can Only Be in Simple Column References | A user attempted to use a Currency column in a complex expression. The JD Edwards EnterpriseOne Open Data Access driver only allows such columns to be simple references. |
| Media Object Columns Can Only Be in Simple Column References | A user attempted to use a Media Object column in a complex expression. The JD Edwards EnterpriseOne Open Data Access driver only allows such columns to be simple references. |
| Column Security Violation | You attempted to use a column you are not authorized to use. You must remove references to those columns that are secured. |
| Invalid Cursor State | You attempted an operation that was not valid for the state that the driver is in, for example:
|
| Invalid Column Number | You attempted to access a column that was not part of the statements results. |
| Driver Does Not Support the Requested Conversion | An attempt was made to convert a column to a data type not supported by the JD Edwards EnterpriseOne Open Data Access driver. |
| Invalid Date or Time String | An attempt to convert a character column to a date, time, or timestamp value failed because the character column did not contain a valid format. |
| Invalid Numeric String | An attempt to convert a character column to a numeric value failed because the character column did not contain a valid numeric value. |
| Numeric Value Out of Range | An attempt to convert a column to a numeric value failed because the output data type could not accommodate the value in the column. You should use the default data type or select a data type that can accommodate the column value. |
| Data Returned for One or More Columns was Truncated | An attempt to convert a column to a numeric value caused a truncation of decimal digits. The output data type could not accommodate the value in the column. You should use the default data type or select a data type that can accommodate the column value. |
| The Data Cannot be Converted | An attempt to convert a column value failed because the input type could not be converted to output type. You should use the default data type. |
| Statement Must Be a SELECT | The JD Edwards EnterpriseOne Open Data Access driver is read-only and allows only SELECT statements. |
| Attempt to Fetch Before the First Row | An attempt was made to fetch before the beginning of results. The attempt resulted in the first row set being fetched. |
| Option Value Changed | An attempt was made to set a connection, statement, or scroll options to a value that was not allowed. The JD Edwards EnterpriseOne Open Data Access driver substituted a similar value. |
| Fractional Truncation | An attempt to convert a column to a numeric value succeeded with a loss of fractional digits because the output data type could not accommodate the value in the column. You should use the default data type or select a data type that can accommodate the column value. |
| Driver Not Capable | An attempt was made to set a connection, statement, or scroll option that the driver does not allow. |
| Multiple Business Views Referenced | An attempt was made to reference more than one business view in a single SELECT statement. The JD Edwards EnterpriseOne Open Data Access driver restricts the SELECT statement to contain only one business view. |
| Unable to Open Table or Business View | The JD Edwards EnterpriseOne Open Data Access driver was unable to locate the table or business view in the JD Edwards EnterpriseOne database or could not get information pertaining to the table or business view. |
| Server Connection Failed | The JD Edwards EnterpriseOne Open Data Access driver was unable to establish a connection to the server referenced by the tables or business view in the SELECT statement. |
| Business View Contains Invalid Join | The Business View definition contains a join condition that could not be processed by the JD Edwards EnterpriseOne Open Data Access driver. |
| Business View Contains Unsupported UNION Operator | The Business View definition contains the UNION operator, which could not be processed by the JD Edwards EnterpriseOne Open Data Access driver. |