EGL SQL – Advanced Development and Coding Concepts

EGL SQL – Advanced Development and Coding Concepts This Learning Module shows how to understand and use the EGL language abstractions to create advanced SQL database access, including custom cursors, prepared statements and calling stored procedures.

EGL Custom SQL Coding • The implicit and explicit SQL shown in the previous examples were simple and powerful ways of going about coding data access requests, and should be considered “best practice” solutions to requirements – whenever possible, as they increase development productivity and minimize maintenance risk and time. However, there will be many business requests with complex logical requirements that will mandate a lower-level or custom solution to data access. EGL supports this with the following constructs: • Execute • Open • Prepare These keywords are actually used somewhat interchangeably in data access routines, and are best learned through example.

EGL – Execute Keyword • The EGL execute verb allows you a custom coding way to write: • Non-DML statements: • Create Table • Drop Table • DML statements that update your database: • Insert • Update • Delete With execute you: • Have total control over the SQL code – so this is a statement for SQL-experts to use • Do not have to reference sqlRecords – so this makes the database access: • More independent of the table import and schema • More time-consuming to write and test • The format of execute is: execute#sql{ … your native SQL statement … } Notes: • Any valid SQL statement (except for SELECT) • Must surround the statement with curly braces • #sql{ is all four characters together (no spaces)

EGL – Execute Keyword – Examples • Non-DML Examples SQL DML Examples (no sqlRecord)

Workshops for EGL execute statement You will do the following labs, to help you understand the user of the EGL execute SQL I/O statement: • Generate CREATE table – SQL DDL using the Data Tools, in the Data Perspective • Create the following EGL execute #sql{ statements: • Which will create a new (temporary) table • Populate this temporary table with rows from EGL.Customer • Do some EGL DML statements against the table using custom SQL statements • Drop the table • Test your code using the sqlTest program • There will also be an optional workshop that will allow you to combine EGL Debugging and your temporary table work, with the Data Perspective

 Data Perspective – Generate Create Table Statement – 1 of 2  From the Data Perspective – from the Database Explorer • Reconnect to EGLDerbyR7 • Right-click over Customer and select Generate DDL… • From Options select  CREATE statements • From Objects select  all • From Save and Run DDL check  Open DDL file for editing • Click Next then Finish

 Data Perspective – Generate Create Table Statement – 2 of 2  From script1.sql – loaded into the Content Area • Select and copy all of the Create table statement ( you will use this in the next part of this workshop) • Close the Data Perspective • From the Web Perspective, leave script1.sql open !!! • Cause you’ll be coming back to this generated DDL

 Execute Keyword – Create Table In eglderby7.access/CustomerLib.egl • Create a new function, named: execCreateTable as shown below • Notes: • Paste in the generated SQL Create table statement – as shown • Code the execute #sql{ • Change the table name to: EGL.CUSTOMERTEMP • Copy and paste the try, HandleSuccess and onException clauses from other statements • Pass the function (statusStatusRec) as a parameter • Press Ctrl/S – and remove all syntax errors

 Execute Keyword – Insert Into Table In eglderby7.access/CustomerLib.egl • Create another new function, named: execInsertIntoTable as shown below • Notes: • Copy the majority of the code from some other function in CustomerLib.egl …or… you can use Content Assist to build the statement

 Execute Keyword – Drop Table In eglderby7.access/CustomerLib.egl • Create another new function, named: execDropTable as shown below • Notes: • Copy the majority of the code from some other function in CustomerLib.egl …or… you can use Content Assist to build the statement • Be sure to drop EGL.CUSTOMERTEMP …not EGL.CUSTOMER!

 Execute Keyword – Update Table In eglderby7.access/CustomerLib.egl • Create another new function, named: execUpdateSetLevel as shown below • Notes: • Copy the majority of the code from some other function in CustomerLib.egl • Make sure your function parameters are correct • Note the use of the host variable :idIn …in the WHERE clause

 Execute Keyword – Delete From Table In eglderby7.access/CustomerLib.egl • Create another new function, named: execDeleteSetLevel as shown below • Notes: • Copy the majority of the code from some other function in CustomerLib.egl • Make sure your function parameters are correct • Note the use of the host variable :idIn …in the WHERE clause • Generate the CustomerLib – Ctrl/G

 Execute Keyword – Debug using sqlTest.egl Program – 1 of 3 From Project Explorer, open: programs/sqlTest.egl • Create another new function, named: testExecStatements() • Notes: • Ensure that all of your connections to the EGLDerbyR7 are closed (check the Data Perspective) • Use Content Assist to build the CustomerLib. function invoke statements • Add statements to main() in sqlTest.egl that invoke the new function(s) • Add new Break Points as shown • And remove the other Break Points from the program while you’re at it • From Project Explorer – right-click on sqlTest.egl • Debug the EGL program • From Debug • Click through your calls to the execute SQL statements • From the Variables view, monitor the results of each call by expanding status StatusRec • Finish your debug session (run all of the statements in the program) • Reopen the Web Perspective

 Execute Keyword – Debug using sqlTest.egl Program – 2 of 3 How often have you wanted to view the results of SQL statements – WHILE your programs were executing? Using a combination of the Debugger and Data Perspective – for certain kinds of testing use cases this is possible. Let’s see how!  In /programs/sqlTest.egl • In testExecStatements()– comment out the call to:// CustomerLib.execDropTable(status); • Save and Debug sqlTest.egl again • Resume through each break point – this will create and populate the temp table • Continue/Resume until your program/debug session has terminated • Then reopen the Data Perspective Important. Please comment out this line  and read the ***Notes section of this slide

 Execute Keyword – Debug using sqlTest.egl Program – 3 of 3 From the Data Perspective / From the Database Explorer • Reconnect to EGLDerbyR7 • Expand the folders down to…… • Right-Click over CUSTOMERTEMP • Select: Data > Sample Contents • You should see the rows you inserted with your EGL program and CustomerLib function call using execute #sql (minus row #5 … because you deleted it )

EGL – open Keyword • The EGL open verb allows you to create custom SQL cursor processing, where you have complete control over the result set produced by your SELECT statement. This can be very helpful in several types of situations: • You need to process each row returned from the database as soon as it’s returned • You want control over the number of occurrences in the EGL record array created from the result set By using open, you can create an array of n size, open a cursor, and get exactly n rows into the array • The format of open is: open resultSet into <sqlRecord.fld…> with #sql{ … code your SQL statement … }; forEach(from resultSet) //do something - like append to an array //Or update the row…etc. end Notes: • Open creates a resultSet – a temporary area in the database that holds the results of your SELECT statement. A resultSet is like a DB2 cursor (on the mainframe). • You can use get next resultSet … or … a keyword like forEach to return each row in the resultSet for processing

 Optional Workshop - EGL – Open Keyword – with forEach to Process ResultSet  Here’s a live example of an Open Keyword function that uses forEach as well. Code or copy/paste and add this function to: CustomerLib (the code is in the slide ***Notes)  To call this function, make the following changes to sqlTest • Comment out all the code in testExecStatements() • Add a new function • From main() call the new function

 EGL – Open Keyword – with while to Process ResultSet “Conditionally” • You may want more control over the loop that returns the resultSet. You can use While to explicitly bump through the rows in your resultSet – one at a time. Add another function to CustomerLib (the code is in the slide Notes) • To call this function, make the following changes to sqlTest.egl • Comment out all the code in testExecStatements() • Add a new function: • From main() call the new function

 (NOT a workshop) EGL – Delete/Replace from ResultSet • You may want to use the “UPDATE WHERE CURRENT OF CURSOR” construct in your EGL logic. Note that this is especially useful in batch processing. Typical logic flow: • Read a row • Determine if that (specific) row should be updated or deleted • Do so (update or delete the row) – through the cursor being maintained by the DBMS on the row Note from the example that you can either: - Delete an entire row - Replace an entire row - Replace specific columns in a row *** See Notes for: 1. Additional conceptual information on using where current of 2. Example code (as shown here)  to try calling from your sqlTest.egl program

EGL – prepare Keyword • The EGL prepare verb allows you to completely customize your SELECT, FROM, WHERE, and ORDER BY at run-time. It is very useful for requirements like custom search pages, where users may select 0 – n fields for processing, and build their own dynamic query conditions and specify custom sorting results. • The format of prepare is: prepare preparedStmtID from stringVar; open resultSet with preparedStmtID into… forEach (from resultSet) //do something - like append to an array //Or update the row…etc. end Notes: • prepare creates a DBMS preparedStatement. You can think of a preparedStatement as a temporary DECLARE for a custom SELECT statement compiled at run-time. Prepare’d statements can be subsequently opened and fetched (with get next as seen in the previous example) • open creates a resultSet – a temporary area in the database that holds the results of your SELECT statement. It is like a DB2 cursor (on the mainframe). • You can use get next resultSet … or … a keyword like forEach to return the rows for processing *** Notes

 Prepare Keyword – Workshop – 1 of 2 • In CustomerLib, add this function (code in the slide Notes) • Read the comments • Save your work

 Prepare Keyword – Workshop – 2 of 2 • In sqlTest– add the following function: • Note the spaces in the string before WHERE and ORDER BY • Why do you think this is necessary? • Comment out the code in testOpenStatement() • In main() add a statement to invoke: testDynamicSQL(); • Debug sqlTest.egl

 OPTIONAL Workshop – prepare With Dynamic SQL Variables – 1 of 2 In the previous example, although you used a dynamic SQL statement, host variables that would normally be supplied at run-time by (for instance) user choices on a search page – were not part of the example. You can supply host variables in prepare statements in two ways: • By using ? (question mark) placeholders throughout the statement, where the open supplies the values as variables • Through standard EGL variable values – in string fields (not shown below, but shown in an example on the next slide) • The format of prepare that contains dynamically-supplied host variables is: strVar string = “SELECT stuff… FROM stuff…” + “ Where colm = ? And colm = ?”; prepare preparedStmtID from stringVar; //where stringVar is shown above open resultSet with preparedStmtID using var1, var2 for <sqlRecord>; forEach (from resultSet) //do something - like append to an array, etc. end Notes: • Use a question mark – within a string variable to hold a place for an EGL variable, referenced by: with var1… in the open statement • The datatypes of the variables reference by with must match their table column types • You can pass a Schema name as a variable into the statements. This may be necessary for dynamic Schema table identifiers – although there are other techniques *** Notes

 Truly OPTIONAL Workshop – prepare With Dynamic SQL Variables – 2 of 2 Here’s an example of a modified function (the code is in the Notes section of this slide) – that uses question marks (?) as placeholders for variable data, resolved at run-time. Note also that we are dynamically supplying the Schema name. This is a useful technique. • Note: For <recordName>

Miscellaneous EGL/SQL Topics • There are • Reusing production SQL • Null values • Tables that contain reserved words in their names (or in the column names) • Connecting to different databases • Importing records with unqualified table names • Stored Procedures • SQL Performance • SQLLIB • Commit and Rollback • Best Practices • SysVar.sqldata

Reusing Production SQL • In most “Legacy Modernization” projects you will probably want to reuse your production SQL statements. Here’s how this can be accomplished: • Select (get) • Create a new record for each unique combination of joined tables: • SQLRetrieve all tables that participate in the SELECT • HINT: Specify the table alias from the original statement in the Record’s tablenames [[…]] property [“tname”,”alias”], • Create a new function for each production statement • Code get <newSQLRecord>; • Right-click and create either a single-row (Add with into) or multi-row (array add) explicit EGL/SQL statement • #sql{ select • Copy the production SQL DECLARE statement from the original code and paste it over the explicit EGL/SQL statement inside the curly braces • Validate the statement • Test by running in the Data Perspective and Debug the statement, using a batch program • (Alternatively) Use: open resultSet into <sqlRecord.fld…> with #sql{ • Copy/Paste the original cursor DECLARE statement after the with #sql{ curly brace • Reconcile host-variable records (into <sqlRecord.fld…> ) with the original FETCH statement • SQL DDL and Insert/Update/Delete statements (add, replace, delete) • Use:execute #sql{ • Reconcile host-variable requirements with EGL records and variables

Dealing with Table Null Values – isSQLNullable=yes • Null values are the “absence of information”. They are logical values and represent “not/available” …or… “not/applicable” status for a field • If you accept the default setting for SQL nulls as generated by the Data Access wizard, EGL internally maintains a null indicator for each variable that has the following characteristics: • Is in an SQLrecord • Is declared with the property isSQLNullable = yes …or… • Is defined with a ? After the type - CustomerId CustomerId?; • To test for NULL in a nullable host variable, use an EGL if statement. Example: if(orderDB.order_date == null) • You can set-null an SQL table column in either of two ways: • Use an EGL set statement to null a nullable host variable, then write the related SQL record to the database; set orderDB.order_date = null; • Use the appropriate SQL syntax, either by writing an SQL statement from scratch or by customizing an SQL statement that is associated with the EGL add or replace statement. Note that you could also declare a variable and add a question mark to the type, to allow null values in the field

Relational Tables With Reserved Words as Identifiers • With the broad range of technology, it helps to pay close attention to naming conventions, else your code will step on some reserved word somewhere in: • EGL • Java • SOAP • SQL • Easy to say – but harder in reality to achieve. In SQL, if there is a reserved word declared as a table or column name RDBMS’s typically allow you to enclose the reserved word with double-quotes – so that the various parsers don’t get confused. • Here’s an example, from the Customer table • Note the use of the Java escape character \ - to ensure that the double-quote is honored throughout the generated SQL

 Debugging SQL – 1 of 2 • In order to assist you in debugging SQL for your applications you can trace the events that occur at run-time by doing the following: • For Web Applications • Open \WebContent\WEB-INF\web.xml • From the Variables tab, scroll down in the Environment Variables list until you find:EnvEntry vgj.trace.type • Select it, and set its Value to: -1 • Then selectEnvEntry vgj.trace.device.optionand set its value to: 1 • For batch applications • Open \Java Resources: src\rununit.properties • Add two entries: • vgj.trace.type=-1 • vgj.trace.device.option=1

Debugging SQL– 2 of 2 • When you run a page, you will get EGL trace information in the Console that shows the EGL functions, Library calls, and DB return codes  • When you debug a batch program, you will get the same kind of information in the DebuggerConsole

Tables With # signs SQL Names with # Signs • Some (seems especially) RPG/System i users have pound signs in their table column names. • Not a ANSI SQL standard practice, but you can allow them as EGL variables if you set Visual Age Generator – compatibility to true • Under: Windows > Preferences > EGL Set VisualAge Generator compatibility  • Note that this will re-build/re-generate your application. • Note also that by setting VisualAge Generator compatibility, your variable names can contain dashes ( - )

Connecting to Different Databases – 1 of 3 • The sqlLib.connect() system function creates a named connection to a database at run time, and makes that connection active. • Example: • sqllib.setCurrent(…) can be used to specify which connected database is the current one (in use) for a given SQL statement. This allows you to maintain open connections (and threads) among two or more databases and access them in an efficient manner. • You connect to multiple physical database “connections” established either through the .EAR file – or in the application server itself. Here’s how…

Connecting to Different Databases – 2 of 3 • Edit the .EAR file’s Deployment Descriptor. For each unique connection/Data Source/Schema add the following: ***Notes • The JDBC Provider. One per DBMS • Data Source. One per: • Schema/Library • Database – within the DBMS • For the Data Source, use the currentSchema property – if you wish to provide different Schema names for unqualified Tables/Views/Synonyms

Connecting to Different Databases – 3 of 3 • In your EGL logic part, you can connect through the SQL Data Source. Here’s an example – referring back to the previous .EAR file Data Source entries:

currentSchema and iSeries DB2 Access  For the iSeries JDBC drivers, the property "currentSchema" does not exist. You need to use the "libraries" property which provides one library that will be used as the qualifier. If you want to use the library list of the user's job description, then you need to change "libraries" to the value of: *LIBL and the "naming" property to a value of system***Notes

Importing Records with Unqualified Table Names  Recall from a prior section of the course, that when you used the Data Access Application Wizard you checked Qualify table names – and all of the generated statements were fully-qualified. • This worked well for our test database and application, but in many production scenarios the following naming standards are in play: • unitTestSchemaName.Table • integrationTestSchemaName.Table • productionSchemaName.Table Where the same tableName is qualified with different schema names according to the environment you are working within • How to handle this? By defining the: currentSchema property for your JNDI database properties in the EAR or Server’s Deployment descriptor (see below and previous slides) • Then by simply un-checking:Qualify table names - when you do your Data Access import *** Notes

tableNameVariables – a Third Way to Qualify a Table Schema • A final method of programmatically qualifying a table name, with the table schema is to use the EGL/SQL keyword: tableNameVariables= Note the following: tableNameVariables= clause • “myTable” – becomes a variable name • “Customer” – becomes a table alias in the statement produced at run-time column= clause without the Schema-Qualification • CUSTOMER.STREET not EGL.CUSTOMER.STREET To use the tableNameVariables= technique • Declare a variable of type SQLRecord (for the tableNameVariables) record • Define a variable “in scope” of the same name as the variable name in the clause, and assign it a run-time value, with the Schema Name • Use the EGL data access verbs *** Notes

EGL Support for Stored Procedures Many production applications use SQL Stored Procedures – which are mini-programs running in the database that combine SQL with procedural business logic, using input and output parameters. Stored procedures are used for many different purposes but their primary attraction is SQL performance. Reliable benchmarks have been formalized Calling a stored procedure • EGL’s open and execute statements can invoke a stored procedure. • Use open when the procedure returns the results of a query (i.e. a SELECT statement), otherwise use execute. • In both cases the SQL statement is a CALL. // Returns the results of a query (row select) open results with#sql{ call OwnerID.myProc1() }; // Returns nothing – most likely an insert/update/delete/create or drop statement execute#sql{ call OwnerID.myProc2() }; • There are some restrictions on using Open with certain databases. If you are not using DB2, consult your IBM technical contact for details

Arguments to Stored Procedures • Stored procedures have IN, OUT, and INOUT parameters, just like functions in EGL. • You can pass literals or host variables from EGL. • If a parameter is OUT or INOUT, the stored procedure can assign it a value, and you’ll see the new value in EGL after the call returns. a int; b decimal(7,0); open results with #sql{ call myProc3( :a, :b, 'hello' ) }; execute#sql{ call myProc4( :a, :b, 'hello' ) };

Stored Procedure Example – 1 of 2 This function executes SQL to drop an existing stored procedure, then executes a statement to create a stored procedure (using the code shown here). try execute #sql{ drop PROCEDURE spdept3 }; end execute #sql{ CREATE PROCEDURE spdept3 ( in dept varchar(5) ) RESULT SETS 1 LANGUAGE SQL ------------------------------------------------------------------------ -- SQL Stored Procedure ------------------------------------------------------------------------ P1: BEGIN -- Declare cursor DECLARE cursor1 CURSOR with return FOR SELECT * FROM SAMPLE.DEPARTMENT where deptno >= dept; -- Cursor left open for client application OPEN cursor1; END P1 };

Stored Procedure Example – 2 of 2 This code calls the Stored Procedure, passing it a single Char variable, initialized to a department number. Finally, it issues an execute #sql{ statement to drop (delete) the Stored Procedure created in the previous slide deptNbr char(5) = "E01"; try open resultSet1 with #sql{ call spdept3( :deptNbr ) }; writeStdOut("getViaSP3()- dept: \"" + x + "\"" ); forEach (from resultSet1 into dept.deptno, dept.deptname, dept.location); writeStdOut(…show results…); End // end of forEach onException writeStdOut ("getViaSP3()- Problem with call to Stored Procedure" ); exit program; end // end of try block execute #sql{ drop PROCEDURE spdept3 };

SQL Performance – 1 of 3  SQL Performance is a very big topic. If you are responsible for writing EGL code that must generate to high-performance SQL database access statements you should research this topic on the Internet, and with books written by experts such as Joe Celko and Craig Mullins (among others) Background • SQL efficiency is a direct correlation to the # of rows physically accessed by the DBMS versus the # of rows needed by your business logic. As an example, if a list page only displays 10 rows at a time, but accesses 2,000 rows from the database per execution of the SQL function this is probably inefficient. However, if a batch process needs 225,000 rows for its business process, and accesses 300,000 rows to get them, this is relatively efficient. It’s the correlation of # of rows needed, versus DBMS work-to-be-done to get those rows that is the true measure of SQL efficiency. • In order to determine the above, you must fully understand the service level requirements for your database access. Typically, you are concerned with web-page performance and user-wait time. Problems with pages occur when the SQL run to access the data for the page returns thousands (or tens or hundreds of thousands of rows). • So, you will need to understand both the logical AND physical structure of your database – and its choices of data access, in order to completely understand how much effort it takes the database to get the data you need. This can be (read, “usually is”) a very complex and time-consuming process, unique to every different business application • The best we can do in this tutorial is to give you general rules-of-thumb that will make queries “more” efficient. However, it is strongly recommended that you discuss this with your systems database administrator (DBA) – for any/all pages that require maximum SQL performance • Note that there is no end of decent SQL performance articles – mostly database-specific on this topic available freely on the internet – and a # of good books as well. • http://www-128.ibm.com/developerworks/db2/library/techarticle/0210mullins/0210mullins.html • http://www.javaworld.com/javaworld/jw-02-2005/jw-0228-apm.html

SQL Performance – Rules of Thumb – 2 of 3 • For Batch applications that process all or a significant % of table rows, index access is often inefficient. This is because the database must first read the index dataset – then read the table dataset to retrieve each row. Index access is efficient only when your SQL requirements are for a small percentage of the entire table: (typically less than 20%). • For online/pages • Attempt to limit the number of rows retrieved per get statement to the minimum needed per page view. This will require you to do at least two things (neither of which are easy or quick): • Add additional WHERE clause conditions using SQL BETWEEN that reference database index columns – and in your EGL code manage the values so that the database is retrieving as close to the # of records needed per page view as possible • Code your own programmatic paging – by incrementing the values in the SQL BETWEEN clauses per page forward – and decrement the values for page backward. • Separate the database access from the page fields by using basicRecords to render the fields. In this tutorial, you have primarily dragged and dropped database records onto a form. This is not as efficient as doing the database access through sqlRecords, then moving the records return • Using explicit SQL, select only the # of columns you need in your page view (i.e. if you are only showing 4 fields tied to a 30 column table row, delete the other 26 columns. • Attempt to avoid ORDER BY unless it is on an indexed column AND the index exists in the same order you need for your sort process. Obviously this is driven by the business requirements, but database sorting can be very expensive. Statements that force sorts include: • ORDER BY • GROUP BY • TABLE JOINS • UNION • Certain Sub-selects • Avoid using SQL WHERE clause keywords that disallow index access • Computations in the where clause • Like – with the % or underscore before the literal data

SQL Performance – Rules of Thumb – 3 of 3 • Attempt to limit the number of JSF dataTable cells rendered by EGL/JSF • A cell is the intersection of a row and column in a dataTable, so that if you have 5,000 rows and 20 columns, you generating 100,000 cells. This will almost definitely cause severe page performance problems. As a rule of thumb, keep the number of total cells under 10,000 (i.e. 500 rows/20 columns), etc. Large-scale SQL performance improvements have been documented by so doing. • SQL Stored Procedures will always out-perform dynamic SQL access. So for your ultra-performance-intensive pages, consider using SQL Stored Procedures. This has been well-documented in IBM Redbooks • Use the EGL MAXSIZE property - to halt the fetch process from the database on potentially large queries. MAXSIZE, which you specify as a property of a dynamic array interacts with the database engine’s fetch looping, and once reached, closes the cursor and stops the database access process. You should note that the majority of the cost of the database access occurs when a cursor is OPEN’d. However, if your page only renders 10 rows at a time, and you are doing programmatic paging, specifying MAXSIZE = 10 will help performance – somewhat • Use any of your DBMS’s SQL efficiency features. For example DB2 has several keywords that influence the Optimizer to access a stated # of rows: • OPTIMIZE FOR n ROWS; //tells the optimizer you will probably only fetch n rows • FETCH FIRST n ROWS ONLY; //tells the optimizer you will ONLY fetch n rows • FOR READ ONLY //tells the optimizer you will not do any update where current of commands • WITH UR //allows you to read uncommitted rows

EGL’s SQLLIB EGL’s SQLLIB system library provides a number of extremely useful system API calls. We’ve already seen connect. Others include: Check out: • loadTable • unloadTable Example1: Say you needed to: 1. show users rows in a dataTable. 2. Allow them to select certain rows. 3. Write these Rows out to a data file on the Server. You could use unloadTable to do this easily and efficiently, by: 1. Creating a new temporary table. 2. Insert the rows into that table. 3. Unloading the table to the file Example 2 – Reverse. Say you needed to read a .DAT file on a user’s desktop into a table. You could: 1. Use the JSF fileUpload component to transfer the file to the server. 2. Use the LOBLIB.updateBlobToFile API to Write the file to the server. 3. Use loadTable to populate an RDBMS table.

EGL’s System Variables – sysVAR.sqlData and sqlLib.sqlData • sysVar.sqlData and sqlLib.sqlData are structured records that contain data available to your EGL programs used in SQL data access routines (primarily error-handling situations, but not necessarily limited to that). • The variables in both records are updated after your program accesses a relational database. The differences between the two record • The scope of sysVar.sqlData is limited to a program. For example, if programA calls programB and both programs access a relational database, each program has its own copy of sysVar.sqlData. The values set in programB are not available after you return to programA. • The scope of sqlLib.sqlData is global across the programs in the run unit. For example, if programA calls programB and both programs access a relational database, the values set by the last relational database access in programB are available in programA immediately after the return to programA and until the next relational database access. Note – if you are using the Data Access Application wizard function calls, you’ve seen that the built-in library code initializes the statusRecord data after each DBMS SQL access.

EGL’s Commit/Rollback Processing Commit processing is managed by the EGL build file’s SQLCommitControl build descriptor option. • Note that you’ll need to un-check: Show only specified options to see it • You have three alternatives for transaction control – or commit processing in EGL: • AUTOCOMMIT • Which commits after each successful database update operation • NOAUTOCOMMIT • Which does not issue a commit after each successful database operation • You can manually Commit/Rollback with calls to the EGL System Library functions: • syslib.commit(); • syslib.rollback(); • NOCOMMIT • Which never issues a commit, and is used primarily by certain Informix databases that do not allow transaction support • Note that the end of every run-unit in EGL issues an implicit commit – this includes JSFHandlers.

EGL/SQL Summary and Best Practices • The following chart lists the best practices approach to coding SQL and using the EGL SQL tools

EGL SQL – Advanced Development and Coding Concepts