Skip Headers
JD Edwards EnterpriseOne Tools Development Tools: Data Access Tools Guide
Release 8.98 Update 4

Part Number E14702-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

3 Working with Table Input/Output

This chapter contains the following topics:

3.1 Understanding Table I/O

Use the Table I/O feature in Event Rules Design to create instructions that perform table input and output. The Table I/O feature enables you to access a table through event rules. You can use the Table I/O feature to:

Table I/O event rules enable event rule support for database access.

3.1.1 Available Operations

This table describes the Table I/O features and the operations that you can perform:

Operation Description
FetchSingle Combines the Select and Fetch options in a basic operation. Indexed columns are used for the Select, and non-indexed columns are used for the Fetch. The operation opens a table for input or output but does not close it. All tables that do not use handles close automatically when the form that uses them is closed.
Insert Inserts a new row.
Update Updates an existing row. Only those columns mapped (presently in tables with or without handles) are updated. You can do partial key updates with tables and handles to tables. If you do not specify all the keys, then several records might be updated.
Delete Deletes one or more rows in a table.
Open Opens a table or business view.
Close Closes a table or business view.
Select Selects one or more rows for a subsequent FetchNext operation.
SelectAll Selects all rows for a subsequent FetchNext operation.
FetchNext Fetches rows that you specify. You can fetch multiple records with multiple FetchNext operations or with a FetchNext operation in a loop.

3.1.2 Valid Mapping Operators

The Mapping form displays available objects that you can map to selected table columns. For SELECT statements, the available objects are used to build a WHERE clause. For FETCH statements, the available objects are used to receive data fetched from the database.

Key columns have an asterisk (*) next to them.

This table describes the operators that you can use for mapping specific table I/O operations:

Table I/O Operation Mapping Operators
FetchSingle Index Fields: =, <, <=, >, >=, !=, Like

Non-Index Fields: Copy Target

Insert All Fields: Copy Source
Update Index Fields: =

Non-Index Fields: Copy Source

Delete All Fields: =
Open NA
Close NA
Select All Fields: =, <, <=, >, >=, !=, Like
SelectAll NA

3.2 Understanding Buffered Inserts

You can use buffered inserts to improve the performance of inserting hundreds or thousands of records into a single database table as long as you don't need immediate feedback if an insertion failure occurs. You cannot use buffered inserts with interactive applications but you can use them with:

Buffered inserts are not available with an Access database, post-insert triggers, or multiple-table views. They are available only with these databases:

The JD Edwards EnterpriseOne database middleware delivers records to the database management system one buffer load at a time.

When you request buffering, the database records are inserted individually and the buffer is automatically flushed when it fills; that is, the JD Edwards EnterpriseOne database middleware delivers the buffer to the database management system. The buffer can also be explicitly flushed. For example, the buffer flushes automatically when you commit a transaction or when you close a table or business view. The business function, table conversion engine, or Table I/O feature can explicitly request that the buffer be flushed as well.

3.2.1 Buffered Insert Error Messaging

Use caution when deciding to use buffered inserts. The system provides no immediate feedback while using buffered inserts if an insertion fails. If an insertion fails, the error appears in the log file. Consequently, buffered inserts are used primarily with batch applications.

Unless you are using the Oracle's JD Edwards EnterpriseOne Table Conversion application, you must request more detailed information from the middleware to get detailed error messages. In the JD Edwards EnterpriseOne Table Conversion application, the table conversion engine automatically performs this task. You can enable tracing to receive more detailed error messages. Otherwise, you get an error message that the insert failed. You should clear the output tables so that you do not receive duplicate error logging.

After you set up buffered inserts, you can use Special Operations to flush the buffers or get error messages.

3.3 Understanding Special Operations

In addition to the basic and advanced operations available on the Insert TableIO Operation form, there are two special operations:

3.3.1 Get Error Data Event Rules Example

This example illustrates how you can use CO ERROR_DETAILS_AVAILABLE:

F0101.Insert //Attempt an insert
  SL AgingDaysAP1 -> TK Address Number
  RC Page - -⇒
⇒
⇒
⇒
⇒
⇒
⇒
> TK Tax ID
  SL TargetEnvironment -> TK Description - Compressed
If SV File_IO_⇒
Status is equal to CO ERROR_DETAILS_AVAILABLE
 F0101.Get Error Data //Failed with⇒
 errors so get errors
      //Map values used in insert to the
      //specified⇒
 fields.
   SL AgingDaysAP1 <- TK Address Number
   RC TESTT <- TK Tax ID
   SL⇒
 ReportName <- TK Description - Compressed
End If

3.4 Understanding Handles

In JD Edwards EnterpriseOne table I/O operations, the term handle refers to a type of file pointer. This file pointer connects the application or Universal Batch Engine (UBE) with the JD Edwards EnterpriseOne database middleware that communicates with the database manager. Handles point to a database table, and they are references to an address within the database middleware. Handles enable you to perform these operations, which cannot be performed using non-handle table I/O operations:

If you pass a handle to a form or a named event rule, a HANDLE data item must be included as a member in the data structure for the form or named event rule. In the form interconnect or business function call, you must assign a handle value from the event rules to the HANDLE data structure member. You can use this handle in the form or named event rule in the same way that you use any other table I/O handle.

You must explicitly open and close handles, unlike other table I/O operations in which the system implicitly opens or closes the table for you. You must open a handle before you can use it. All of the table I/O operations, except Open, work the same for handles as they do for tables and business views. When you are finished using a handle, you must explicitly close it. Close the handle in the same way that you close a table or business view, except select the Handle option.

In order to use handles in table I/O event rules, you must:

You might be able to find an existing handle for the table I/O. Whether you use an existing handle or create a new handle, you must create a handle variable in Event Rules Design. Create a handle variable in the same way that you create other variables. You can use any scope that is necessary.

After you create a handle variable, you must explicitly open the handle. Then, after performing the required table I/O, you must explicitly close it.

See Also:

3.5 Working with Table I/O

This section lists the prerequisites and discusses how to:

3.5.1 Prerequisites

Before creating table I/O event rules, ensure that you:

  • Create a batch application.

  • If you want to update the database, ensure that the batch application is defined as an update report.

3.5.2 Creating Table I/O Event Rules

Open a batch application in Oracle's J.D. Edwards EnterpriseOne Report Design Aid (RDA) and access Event Rules Design.

  1. Click the Table I/O button.

  2. On the Insert TableIO Operation form, select an operation from Basic Operations, Advanced Operations, or Special Operations and click Next.

  3. On the Data Source form, select a table, business view, or handle as the data source and click Next.

  4. On the Mapping form, select fields from the Available Objects list to map to columns in the mapping.

    The key field must be mapped. Map as many other fields as required to ensure that the correct data is associated with the correct record.

  5. Click the Operation for each map until you locate the operator that you want.

    The default operator is equal.

  6. Click Finish to save the operation and return to Event Rules Design.

3.5.3 Using Buffered Inserts in Table I/O

Open a batch application in RDA and access Event Rules Design.

  1. Click the Table I/O button.

  2. On the Insert TableIO Operation form, select Open under Advanced Operations and click Next.

  3. On the Data Source form, select the table that you want to use and click Advanced Options.

  4. On the Advanced Options form, select Buffer Inserts and click OK.

3.5.4 Using Special Operations for Buffered Inserts

Open a batch application in RDA and access Event Rules Design.

  1. At the point in which you want to perform either a Flush Insert Buffer or Get Error Data operation, click the Table I/O button.

  2. On the Insert TableIO Operation form, select one of these options under Special Operations:

    • Flush Insert Buffer

    • Get Error Data

3.5.5 Creating Handles

In JD Edwards Solution Explorer access EnterpriseOne Life Cycle Tools, Application Development, Data Dictionary Design, select Work With Data Dictionary Items to access the Work With Data Items form.

Figure 3-1 Data Item Specifications form

Description of Figure 3-1 follows
Description of "Figure 3-1 Data Item Specifications form"

Data Item

Enter the name of the data item. This is a 32-character alphabetical field that does not allow spaces or special characters.

Alias

Enter a unique identifier.

Glossary Group

Select a user-defined code (UDC) (H95/DG) that indicates the type of data item. A handle is glossary group D, Primary Data Elements.

Description

Enter a meaningful description of the data item.

Product Code

Select a UDC (98/SY) from the range of values reserved for clients: 55–59.

Product System Code

Select a UDC (98/SY) that represents the JD Edwards EnterpriseOne system where the data is used. This value is used for reporting and jargon purposes. Example values include:

01: Oracle's JD Edwards EnterpriseOne Address Book

03B: Oracle's JD Edwards EnterpriseOne Accounts Receivable

04: Oracle's JD Edwards EnterpriseOne Accounts Payable

09: Oracle's JD Edwards EnterpriseOne General Accounting

11: Oracle's JD Edwards EnterpriseOne Multicurrency

Data Type

Select the style or classification of data. Examples include, string, date, character, and identifier. A handle is an identifier data type.

Size

Enter the field size of the data item.

Class

Select a class that defines the essential attributes and characteristics of the data item. Examples include, name, email, and table handle.

Control Type

Select the type of graphical user control that is associated with the data item. Examples include button, radio button, and user-defined code.

Row Description

Enter the description of the data item as it will appear in a group report.

Column Title

Enter the description of the data item as it will appear in a columnar report.

3.5.6 Using Handles with Table I/O

Open a batch application in RDA and access Event Rules Design.

  1. Create an event rule variable using the appropriate handle data dictionary item.

  2. Click the Table I/O button.

  3. Select Open under Advanced Operations and click Next.

  4. On the Data Source form, select the Handles tab.

  5. Select the handle that you want to open and click Next.

  6. Select a variable that contains the name of the environment in which you want to open the table and click Finish.

    If you want to open the table in the login environment, select the system value SL LoginEnvironment. System values also exist for the source and target environments if you use the Table I/O in Table Conversion feature.

  7. Click the Table I/O button.

  8. Select Close and click Next.

  9. On the Data Source form, select the Handles tab.

  10. Select the handle that you want to close, and click Finish.