3 Working with Table Input/Output

This chapter contains the following topics:

• Section 3.1, "Understanding Table I/O"

• Section 3.2, "Understanding Buffered Inserts"

• Section 3.3, "Understanding Special Operations"

• Section 3.4, "Understanding Handles"

• Section 3.5, "Working with Table I/O"

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:

• Retrieve records.

• Update or delete records.

• Add records.

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:

• Table conversions

• Table I/O

• Batch processes

• Business functions

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

• Oracle (V8 and later)

• DB2/400

• SQL Server

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:

• Flush Insert Buffer

  To maintain data integrity, you should flush the insert buffer before you perform any operations other than an insert. If you fail to do so, the results of recent inserts might not be reflected in other operations, and the operations might not work properly.

  When you use the flush insert buffer option for a specific table, you must flush the buffer before you close the table.

• Get Error Data

  To retrieve errors for records that the system did not insert properly, use the Get Error Data option. Depending on when the buffers are flushed, or when you begin another insert, you might overwrite the error information for a specific insert. If error information is critical, retrieve the information before the next insert begins.

  If you need to perform special error handling, set the error handling up after each table I/O insert and each Flush Insert Buffer option. Always retrieve the error information before you begin the next table operation.

  Note:

  The return code of each table I/O statement is stored in system variable SV File_IO_Status. If an Insert or Flush Insert Buffer operation failed, the SV File_IO_Status system variable might contain a value of CO ERROR_DETAILS_AVAILABLE. If so, you need to run the Get Error Data table I/O special operation to retrieve the error data. The Get Error Data operation returns the values used in the insert for all the requested columns.

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:

• Concurrently open multiple instances of a single table or business view.

• Open a table or business view in an environment other than the environment to which you are signed in.

  This feature is particularly helpful when you receive an upgrade to JD Edwards EnterpriseOne software or when you need to convert data from another system into JD Edwards EnterpriseOne software.

• Pass handles into a form, named event rule, or business function so that you do not need to open a table or business view more than once.

  Note:

  You cannot use handles in transaction processing.

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:

• Define the handle in the data dictionary.

• Create a handle variable in event rules.

• Open the handle explicitly.

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:

• "Creating a Data Dictionary Item" in the JD Edwards EnterpriseOne Tools Development Tools: Data Dictionary Guide

3.5 Working with Table I/O

This section lists the prerequisites and discusses how to:

• Create table I/O event rules.

• Use buffered inserts in table I/O.

• Use special operations for buffered inserts.

• Create handles.

• Use handles with table I/O.

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 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.