< Previous | Next >

Lesson 6: Executing arbitrary SQL code

In this lesson, you learn more flexible ways of working with SQL code in EGL.
Up to this point, you have been limited to using SQL statements that match an EGL statement. For example, you can edit the SQL code associated with a get statement, but the SQL code is limited to a SELECT statement. You cannot, for example, put a SQL DELETE statement in the explicit SQL code for a get statement. Even though you can edit the explicit SQL, the SQL code must still be appropriate for the EGL keyword.

Then how do you use the SQL statements that have no direct EGL analogue? You can execute SQL code that is not associated with an EGL keyword either directly through the workbench tooling, or with the EGL execute statement.

Using the SQL builder to create SQL statements

The workbench includes several tools to help you work with databases outside of an EGL application. In this section, you work with the sample database more directly with the SQL builder.
  1. Switch to the Data perspective:
    1. Click Window > Open Perspective > Other.
    2. In the Open Perspective window, click Data.
    3. Click OK.
    The Data perspective opens, which includes views used for working with databases. You will be working mostly with the Database Explorer view.
  2. In the Database Explorer view, expand Connections. The Database Explorer view shows your project's connection to the database as EGLDerbyR7, along with a sample Derby connection used for testing:
    The Database Explorer view, showing the EGLDerbyR7 connection
  3. Right-click the EGLDerbyR7 connection and then click Reconnect. Now that the Database Explorer view is connected to the database, you can work with the database using the tools in the Data perspective. However, since Derby only allows one connection to a database at a time, as long as the Database Explorer view is connected, your EGL programs will not be able to connect to the database.
  4. In the Database Authorization window, click OK.
  5. Expand EGLDerbyR7 > EGLDerbyR7 > Schemas > EGL > Tables. Now the Database Explorer view lists the tables in the database that this tutorial is concerned with. The other tables are metadata and are not important for this tutorial.
    The Database Explorer view showing the tables in the database
  6. Right-click the CUSTOMERS table and then click Data > Sample Contents. The Data Output view shows the data in the CUSTOMERS table:
    The Data Output view showing the CUSTOMER table
    The Data Output view has run a default SELECT statement, like the implicit SELECT statement behind an EGL get statement. To work with the database more directly, and to test SQL code before adding it to EGL programs, you can use the SQL Builder to write and run SQL commands interactively.
  7. In the Database Explorer view, right-click the EGLDerbyR7 database, represented by the blue database icon and then click New SQL Statement.
    The Database Explorer view showing the New SQL Statement menu option
  8. In the New SQL Statement window, give the statement the name sqlStatements.
  9. Leave the Statement Template field set to SELECT and the Edit Using radio button group set to SQL builder.
    The New SQL Statement window
  10. Click OK. The SQL builder opens. From this editor, you can create SQL statements and test them before you add them to your application.
  11. As an example, paste the default SQL statement for the Customer record into the SQL builder. You can copy this statement (or any other SQL statement) from explicit SQL in the EGL editor, or use the following code:
    select
  EGL.CUSTOMER.CUSTOMER_ID, EGL.CUSTOMER.FIRST_NAME, 
  EGL.CUSTOMER.LAST_NAME, EGL.CUSTOMER.PASSWORD, 
  EGL.CUSTOMER.PHONE, EGL.CUSTOMER.EMAIL_ADDRESS, 
  EGL.CUSTOMER.STREET, EGL.CUSTOMER.APARTMENT, 
  EGL.CUSTOMER.CITY, EGL.CUSTOMER."STATE", 
  EGL.CUSTOMER.POSTALCODE, EGL.CUSTOMER.DIRECTIONS
from EGL.CUSTOMER
order by
  EGL.CUSTOMER.CUSTOMER_ID asc
    The SQL builder shows the SQL code in various different ways:
    • The code editor at the top of the SQL builder allows you to edit SQL statements just like in the EGL editor. However this editor has extra options for dealing with SQL statements; for example, when you paste the default SQL statement from the EGL code into this editor, the SQL builder simplifies and reformats it:
      The SQL builder showing the SQL code to be edited
      Also, this SQL editor provides content assist for SQL statements. Just like EGL content assist, you can press Ctrl+Space for a list of suggestions.
    • The second section from the top shows a graphical view of the tables involved in the statement and their columns. You can select or remove columns from the SELECT statement by clearing the check boxes next to the columns, and you can add a new table to the statement by right-clicking the editor area and then clicking Add Table. You can also see a list of options for a table by right-clicking the table:
      Right-click menu in the graphical area of the SQL builder
    • The section at the bottom shows a tabular view of the statement. You can use the templates in the tables to guide you through editing the statement.
      The tabular statement editor area of the SQL builder
    For example, you can use the tabular area of the SQL builder to add a WHERE clause to the SELECT statement in the editor:
  12. Go to the Conditions tab of the SQL builder.
  13. On the Conditions tab, add a new WHERE clause to the statement:
    1. On the Conditions tab, click an empty row in the Column column.
    2. In the blank cell under Column, select CUSTOMER.POSTALCODE.
    3. Under Operator, select LIKE.
    4. Under Value, type the following code: 
      1%
      The percent symbol (%) is a wildcard in SQL. This WHERE clause limits the statement to customers with a postal code that begins with the number 1.
    The selection condition looks like this in the tabular area:
    The new selection condition in the SQL builder
    When you are finished adding the condition and set the focus elsewhere, the SQL builder updates the code of the SELECT statement in the editor:
    The code of the SQL statement in the SQL builder
  14. Right-click the code editor at the top of the SQL builder and then click Run SQL.
    The Run SQL context menu option, launched from the code editing area of the SQL builder
    The Data Output view shows the results of the statement. In this case, it shows the customers with a postal code beginning with 1:
    The Data Output view showing the results of the statement
    If you are familiar with SQL, you can write your own SQL statements here and test them before using them in an application.
  15. When you are finished with the SQL builder, you can save the code to a file or discard it.
  16. As a more powerful example, create an SQL statement that will make a new table by copying the design for the CUSTOMER table:
    1. In the Database Explorer view, right-click the CUSTOMER table and then click Generate DDL.
    2. In the Generate DDL window, select the CREATE statements check box and clear the other check boxes.
      The Generate DDL window
    3. Click Next,
    4. On the Objects page, click Select All.
    5. Click Next.
    6. Select your EGLSQL project in the Folder field.
    7. On the Save and Run DDL file page, name the file createtable.sql.
    8. Clear the Run DDL on server check box.
    9. Select the Open DDL file for editing check box. The Save and Run DDL page looks like this:
      The second last page of the Generate DDL wizard
    10. Click Next.
    11. Click Finish.
    The new SQL statement opens in the SQL statement editor, which works like the SQL builder but has only the code editing area and not the additional graphical areas.
  17. Edit the statement to change the name of the table that is created to EGL.CUSTOMERTEMP rather than CUSTOMER. The edited SQL code looks like this:
    CREATE TABLE EGL.CUSTOMERTEMP (
    CUSTOMER_ID INTEGER NOT NULL,
    FIRST_NAME VARCHAR(30),
    LAST_NAME VARCHAR(30),
    PASSWORD CHAR(8),
    PHONE VARCHAR(14),
    EMAIL_ADDRESS VARCHAR(50),
    STREET VARCHAR(30),
    APARTMENT VARCHAR(10),
    CITY VARCHAR(30),
    STATE CHAR(2),
    POSTALCODE VARCHAR(10),
    DIRECTIONS VARCHAR(255)
  );
  18. Save the statement.
  19. Disconnect from the database by right-clicking the EGLDerbyR7 database in the Database Explorer view and then clicking Disconnect.
Now you have a SQL statement that will create a new table for the database. However, EGL does not have a statement that is equivalent to the SQL CREATE statement, and you cannot insert this statement into the explicit SQL for a statement such as get because get expects a result set from the database. In the next section, you will use execute to run this SQL statement in EGL.

Using execute

You could run the new CREATE statement using the tools in the Data perspective, but sometimes you may need to run custom SQL code in your EGL application. Obviously, any time you insert customized SQL code into an EGL application, you should test it carefully to make sure it does what you expect it to do.
The EGL execute statement lets you run SQL code without dealing with SQLRecord variables. You can use execute to run a variety of SQL statements, including CREATE, ALTER, and DROP TABLE, INSERT, DELETE, and UPDATE. For example, you can remove a table from the database like this:
execute #sql{
  DROP TABLE OLD_TABLE;
}

The execute statement doesn't support some SQL statements, most notably SELECT and OPEN. For a list of which SQL statements work or do not work with execute, see execute considerations for SQL.

You can also use execute to call a stored procedure:
execute #sql{ 
  call aStoredProcedure( :parameterVar) 
};
Derby does not support stored procedures in the same way as most database management systems, so this tutorial will not cover stored procedures. Instead, in this section, you will use execute to create a table in the database and populate it with data without using any SQLRecord parts.
  1. Return to the EGL perspective.
  2. Create a new Library part in a file named CustomerTableOperations.egl in a package named libraries.
  3. Insert new functions into the library named execCreateTable, execInsertIntoTable, and execDropTable, with each function receiving a StatusRec record variable as a parameter: 
    function execCreateTable(status StatusRec inOut)
  
end

function execInsertIntoTable(status StatusRec inOut)
  
end

function execDropTable(status StatusRec inOut)
  
end
    The StatusRec record variable records information about the success or failure of database operations.
  4. If you did not use content assist to add the parameter, add the following import statement to the top of the file, just below the package statement: 
    import eglderbyr7.StatusRec;
  5. Using an execute statement in the execCreateTable function, run the CREATE TABLE statement you created in the previous section: 
    function execCreateTable(status StatusRec inOut)
  try
    execute 
      #sql{
        CREATE TABLE EGL.CUSTOMERTEMP (
            CUSTOMER_ID INTEGER NOT NULL,
            FIRST_NAME VARCHAR(30),
            LAST_NAME VARCHAR(30),
            PASSWORD CHAR(8),
            PHONE VARCHAR(14),
            EMAIL_ADDRESS VARCHAR(50),
            STREET VARCHAR(30),
            APARTMENT VARCHAR(10),
            CITY VARCHAR(30),
            STATE CHAR(2),
            POSTALCODE VARCHAR(10),
            DIRECTIONS VARCHAR(255)
          )
        };
  onException (exception SQLException)
    ConditionHandlingLib.HandleException(status, exception);
  end
  
end
    This function creates the table and then handles any errors using the HandleSuccess function that is created along with the data parts and logic parts from the database.
  6. If you did not use content assist to add this code, add the following import statement to the top of the file: 
    import eglderbyr7.ConditionHandlingLib;
  7. In the execDropTable function, add an execute statement that removes the table from the database: 
    function execDropTable(status StatusRec inOut)
  try  
    execute #sql{
     DROP TABLE EGL.CUSTOMERTEMP
       } ;
    onException (exception SQLException)
      ConditionHandlingLib.HandleException(status, exception);
    end
end
  8. In the execInsertIntoTable function, add an execute statement that moves records from the CUSTOMER table into the CUSTOMERTEMP table: 
    function execInsertIntoTable(status StatusRec inOut)
  try  
    execute #sql{
    INSERT INTO EGL.CUSTOMERTEMP 
      SELECT * FROM EGL.CUSTOMER
       } ;
  onException (exception SQLException)
    ConditionHandlingLib.HandleException(status, exception);
  end
end
    This code merely copies each record from CUSTOMER into CUSTOMERTEMP. If you want, you can add a WHERE clause to select only certain records to copy.
  9. Save and generate the library.
  10. Create a new Program part in a file named duplicateTable.egl in the programs package.
  11. In the new program, call the functions that create the new table and insert records into it: 
    package programs;

import eglderbyr7.StatusRec;
import libraries.CustomerTableOperations;

program duplicateTable type BasicProgram {}
  
  status StatusRec;
  
  function main()
    CustomerTableOperations.execCreateTable(status);
    CustomerTableOperations.execInsertIntoTable(status);
  end
  
end
  12. Generate and run the program.
  13. Return to the Data perspective and reconnect to the database in the Database Explorer view.
  14. As you did in the previous section, preview the data in the new table to see the new table and its data:
    1. Expand EGLDerbyR7 > EGLDerbyR7 > Schemas > EGL > Tables.
    2. Right-click the CUSTOMERTEMP table and then click Data > Sample Contents.
Now you can see the data in the new table:
Data Output view showing the new table
Now you can create other statements to work with the new table, or you can call the execDropTable function to remove it from the database. For example, you might execute the ALTER TABLE and CREATE INDEX statements that were created along with the CREATE TABLE statement, but if you do, you must rename the constraint and index, because Derby does not allow duplicate names for primary key constraints. Remember to disconnect from the database in the Database Explorer view before running any EGL code that accesses the database.

Here is the complete code of the two files used in this lesson. If you see any errors marked by red X symbols in either file, make sure your code matches this code:Completed CustomerTableOperations.egl and duplicateTable.egl files after lesson 6.

Lesson checkpoint

In this lesson, you learned about some of the database management tools in the workbench and how you can combine those tools with the execute statement to run a wide variety of SQL statements within an EGL application.
For more detail on the data access tools in the workbench, see Working with SQL statements. For more information on the EGL execute statement, see execute.
< Previous | Next >
Feedback