The Eclipse Data Perspective and Database Explorer

1. The Eclipse Data Perspective and Database Explorer This section describes how to use the Eclipse Data Perspective, and how to combine the tools in the Database Explorer with EGL for productive SQL development and testing,

2. The Data Perspective*** The Data Perspective allows you to access your data sources, and to view and add test rows, edit your tables, create and test/execute SQL statements interactively. • Access the Data Perspective from the Window menu • Window > Open Perspective > Other… > Data • The Data Perspective has only a few views and tools you will work with: • Database Explorer • Shows Connections – which contain • Schemas and tables • Processing options • Data Output • Shows the results of running SQL queries • Data Project Explorer • For Data Analysts • SQL Statement Wizard • For building SQL statements interactively *** Notes: The Data Perspective is only available if you have installed RBD v7.1 with IBM’s RAD, RDi or RDz products

3.  The Data Perspective – Connect to EGLDerbyR7 All of your work in the Data Perspective is done through a database Connection. In this course our Database Explorer screen captures show four connections – your machine may have more or less, depending on the databases you have access to. • In the Data Perspective, you can define a new Connection, or reconnect to a database through an existing connection (like the one you’ve been using in debug, and used for the SQL Access Application import earlier in this course) • From the Database Explorer • Right-click over EGLDerbyR7 • Select Reconnect • Answer OK to prompts • You are “connected” and can: • Expand folders • Use the Data Perspective • Tools • Views • Wizards 1. 2.

4.  The Data Perspective – Database Diagram • So what can we do with the Data Perspective? Let’s start by having a look at our tables and their relationships. • From the Database Explorer • Expand the folders: EGLDerbyR7 > Schemas > EGL • Right-click over EGL and select: Add to Overview Diagram • Select only the EGL tables (elements) and check: Infer implicit relationships (if it’s not already checked)

5.  The Data Perspective – Database Diagram The Diagram Tool shows all of the tables selected through our connection, and their relationships as defined by Primary/Foreign key SQL specifications. This allows us to understand things like how to join tables, and where data exists in relation to other table values. • We can also use the Diagram Tool to navigate in the Database Explorer • Right-click over Customer and select: • Navigate > Show in > Database Explorer

6.  Database Explorer – Exploring the Customer Table The Database Explorer allows you to: • View a table’s schema • Columns and Referential Integrity Constraints and Indexes • Through the Context Menu: • Create the SQL data definition language statements to create the table • From Data > • View the table contents (Sample Contents) • Edit table values • Extract and load the table using a comma-delimited file Database Explorer - Context Menu Options Exploring the Customer table

7.  Database Explorer – Sample Contents When you are testing your SQL statements, it is extremely important to be able to view the row and column values in your tables. Sample Contents provides this view. • Note that this is essentially a Select * from <schema.table> • If your table contains millions of rows… you will wait a long time to see your results • Even if your table contains say, only 50,000 rows • So for production work, consider using the SQL Scrapbook to write a query that filters the result table (next topic)  Optional: Right-click over the Sample Contents rows, and select Save as to your PC’s hard-drive. Note that you can do this with any results set – including the results of SQL queries you code and test.

8.  Database Explorer – Open SQL Scrapbook An SQL Scrapbook is a file that contains interactive - not embedded - SQL statements (SQL statements without host-variables). These statements can be tested in the Data Perspective before embedding them in EGL data access functions. For complex SQL logic, this is recommended “best practice”. To open and work with the SQL Scrapbook: • Right-click over the database icon • Select: Open SQL Scrapbook • From the Create SQL Scrapbook Page wizard: • Expand your project and browse to: EGLWeb/EGLSource/ • Specify a folder and a filename: /sql/myStmt

9.  Database Explorer – Create and Run a SQL Scrapbook Statement  You can code your SQL statements using Content Assist – to get the table and table column names (Ctrl/Spacebar) Right-click (context Menu) Select Run SQL View the Results

10.  Database Explorer – Copy/Paste an Existing SQL Statement - 1 of 2 • You can use the SQL Scrapbook to test your EGL/SQL statements out prior to testing them at the EGL procedural logic level. • Steps: • From the Workbench, click back over to the Web Perspective (but do NOT close the Data Perspective) • Open the CustomerLib.egl • Find the GetBestCustomers(..) function shown below • Expand the select statement – and copy the select order by clauses as shown Select And Copy This

11.  Database Explorer – Copy/Paste an Existing SQL Statement - 2 of 2 • From the Workbench, click back to the Data Perspective • If you still have your other SQL Scrapbook page open, select and delete your existing statement and paste in the copied SQL code • You could also follow the steps on a previous slide to create a new SQL Scrapbook page – then paste the copied statement in • Right-click and run the SQL Statement and view results in the Data Output tab

12.  Database Explorer – Build SQL – New SQL Statement  Database Explorer also has a simple, but effective SQL statement builder – that can provide assistance in creating your SQL statements: • Right-click over the database, and select New SQL Statement... • Select the Statement template you wish to use, and click OK • From the middle-pane of the editor, right-click and select Add Table… • Select a table and press OK

13.  Database Explorer – New SQL Statement  You will be shown a template statement with your selected table. 1. Choose (check) Columns for your table • Each column is added to the SELECT clause 2. From the Conditions tab, select: Columns, Operator Type the value (if a literal) select AND/OR, etc. Note that the common SQL Where Clause Operators are available from the combo-box

14.  Database Explorer – Run New SQL Statement  Right-click in the top pane and select Run SQL. The Data Output Tab will show you any rows returned. *** Notes: The row(s) you get back may be different, depending on the state of your table rows after the preceding workshops

15.  Database Explorer – Table Edit – 1 of 2  Another very common requirement for SQL programming is to customize your test data  Right-click over a table and select: Data > Edit

16.  Database Explorer – Table Edit – 2 of 2 • The table editor allows you to: • Modify row and column values • Add (insert) new rows • Delete Rows • Set field values to null • All values are saved (committed) or not (rolled-back) at once – Or when you press Ctrl/S

17.  Data Perspective – Workshop • If you haven’t been following along and doing all of activities shown on the previous slides with your instructor, do the following now: • Open the Data Perspective • Connect to: EGLDerbyR7 • View all of the tables and their relationships through the Overview Diagram • Show the Customer table’s Sample Contents • Code a few simple SQL statements (or copy/paste from the CustomerLib) and run them in a SQL Scrapbook • Create a simple new SQL statement using the statement builder • Edit the Customer table: • Add a few new rows • Update some values • Delete one or two rows • Save your changes – Ctrl/S

18. Topic Summary • Now that you have completed this topic, you should be able to: • Launch the Data Perspective • Connect to a Data Source • Use the Database Explorer to: • View tables and relationships • View sample table rows and values • Edit (create, update, delete) row values • Test interactive SQL • Code/Build SQL statements • Generate Table DDL Summary