Filling Nested Arrays with Values from Databases: A Comprehensive Guide

1. Filling Nested Arrays With Values From Tables This Learning Module describes the use of EGL and data access operations to fill nested arrays. It coincidentally shows how to turn a nested array data structure into a nested JSF dataTable.

2.  Another OPTIONAL Workshop – Filling a Nested Array of Records From the Database Problem - Need to fill a nested array of records. Example “Detail report of a customers orders” Customer > Orders > OrderItems > Item Development choices: • Table join - to do the database access. With an additional iteration (for loop) over the join record, to parse out individual array records in the hierarchy • Multiple cursor operation: 1. get customers ... for each customer 2. get orders ... for each order 3. get OrderItems, etc. • Be suspicious of dichotomous thinking, (making things black & white) there's probably not a one-size-fits-all answer. • Setting aside development time, performance trade offs include: • Table join - would be better DBMS performance - but could lead to worse Server Performance • Multiple cursors - would be better server performance But if DB2 is remote, what’s the penalty for all those cursor set opens/loops/closes) • Let’s try this out, and see how it would be to develop

3.  OPTIONAL Workshop – Filling a Nested Array of Records From the Database – 1 of 5 Steps: • Open Customer.egl (it’s under: \EGLSource\eglderbyr7.data\ • Scroll to the end of the file • From the Notes section of this slide, copy and paste the three new record declarations found into Customer.egl – as new basicRecords • Save and close Customer.egl In preparation for the next slide – do the following: • Open CustomerLib.egl\EGLSource\eglderbyr7.access\ • Scroll to the end of the file • From the Notes section in the next slide, copy and paste the function which is shown on the next slide somewhere inside the Library part of the file • Save, Generate (Ctrl/G) and Close CustomerLib.egl

4.  OPTIONAL Workshop – Filling a Nested Array of Records From the Database – 2 of 5 • Open CustomerLib.egl\EGLSource\eglderbyr7.access\ • Scroll to the end of the file • From the Notes section, copy and paste the function  inside the Library part of the file – as a new call-able function • Save and… • Generate (Ctrl/G) Read the comments carefully, to understand the various elements of this nested array population logic 

5.  OPTIONAL Workshop – Filling a Nested Array of Records From the Database – 3 of 5 From Project Explorer: Create a new .JSP page under \WebContent\, named: customerPurchaseOrderPage.jsp • From Page Designer, “Edit Page Code” • From the Notes section, copy and paste the replacement code for the boiler-plate handler  • Read the new code to understand its functionality • Save • Return to the .JSP in the Content Area

6.  OPTIONAL Workshop – Filling a Nested Array of Records From the Database – 4 of 5 From Page Designer/Page Data: • Drag & Drop custSearchID onto the page Make it an input field • Drag & Drop the customerPurchaseOrderSearch() function on top of the Submit button • Optionally add an HTML Horizontal Rule • Drag & Drop the cust record onto the page, below the submit button • Optionally make all of the fields Read-only (output)

7.  OPTIONAL Workshop – Filling a Nested Array of Records From the Database – 5 of 5 Run the page on the server  Try different customer IDs Note: If time permits, work with your instructor to add some pizz-azz to the output: