TOC PREV NEXT INDEX


Create an SQL Statement

A JDBC connection is not generally useful on its own. To make use of a connection, a statement must be associated with the connection. The next step is to create an SQL statement. To accomplish this:

  1. Drag a Statement Module from the JDBC tab of the Simplicity Enterprise Module Palette to the Modules portion of the Enterprise Composer tree. Drop it just after the JDBCConnection Node.

This adds a Module that can be used to create an SQL query to get information from the database.

  1. Associate the Statement Module with the previous JDBCConnection Module by clicking on the Properties node of the SQLStatement Module and selecting the jDBCConnection item in the list that appears on the SQLStatement Properties sheet on the right hand side of the Enterprise Composer.

This is required because there can be more than one connection to more than one database in a particular servlet and we need to specify which one we intend to use.

Next, you will use the SQL Query Builder node of the SQLStatement Module and the Code Sourcerer to generate SQL statements.

  1. Choose the SQL tab of the Database Editor. On the left side of the panel is a list of all the available groups of databases, in a tree format.
  2. Expand the node of the tree named "PUBLIC", by clicking on the '+' symbol, to see a list of database tables. If you click on one, you will see all the records in the table.

This allows you to peruse your Database in order to become familiar with what data is available where.

  1. Click on "Product_Tbl" (found under "PUBLIC"). You can resize the columns in the table, and the Composer window, if you have difficulty viewing the information in the table.

Next we will build the actual SQL query.

  1. Click on the Code Sourcerer button (the purple button with yellow stars and white lettering above the text areas of the SQL Query Builder properties area).

Normally the Code Sourcerer is used to generate Java code, but since you are using the Code Sourcerer in the SQL Query Builder, a special set of options for generating SQL code will appear. The most common one, 'Search for rows from a database table', will be used in this tutorial.

  1. Press the Next button. The next page lets you choose which table or tables you wish to do your search with.
  2. Select "PRODUCT_TBL" by expanding the "PUBLIC" item and clicking on "PRODUCT_TBL" (You may need to scroll down the tree to see it).
  3. Press Next. The next page lets you decide which columns you wish to display, or if you wish to display them all. In this search we're interested in only three columns.
  4. Select the 'Choose Columns' radio button. The tree of the possible columns will now be enabled.
  5. Select "DESCRIPTION", "PURCHASE_COST", and "QTY_ON_HAND" and then press the Next button.

The final page will let you refine your search by selecting only certain rows to be returned. For this search, we are interested in fulfilling the customer's request, which means that we are only interested in items where the quantity available is greater than or equal to the requested order size. We also want to make sure that we do not exceed the requested maximum price that the customer is willing to pay. Furthermore, we only want to show the customer products that are in the product category that the customer is interested in.

  1. Choose "PRODUCT_CODE" from the popup list on the bottom left of the Code Sourcerer window. The query will be updated dynamically as you choose items.
  2. Choose 'equals' from the popup list in the middle of the bottom row if it is not already showing.
  3. Type "HW" (without the quotes) in the popup list on the right of the bottom row.

This completes the first criterion. It limits the search to rows that have hardware products, which is indicated in this database as any row with a PRODUCT_CODE of HW. The top line of the text area in the Code Sourcerer window should now contain:

PRODUCT_CODE = 'HW'
  1. For the next criterion, click on the Create New button to create a new search criterion.
  2. Choose "PURCHASE_COST" from the popup on the bottom left.
  3. Choose 'is less than or equals' from the middle popup.
  4. Type "1500" in the right popup.

This completes the second criterion, which limits the search to items that cost less than $1500. The upper text area should now look like:

PRODUCT_CODE = 'HW'
PURCHASE_COST <= 1500
  1. For the final criterion, click on the Create New button to create a new search criterion.
  2. Choose "QTY_ON_HAND" from the popup list on the bottom left.
  3. Choose 'is greater than or equals' from the popup list in the middle of the bottom row.
  4. Type "15" into the field on the right of the bottom row. The SQL code limiting your query appears above. It should now read
    PRODUCT_CODE = 'HW'
    PURCHASE_COST <= 1500
    QTY_ON_HAND >= 15

This is the final criterion that we will be searching for.

  1. Press done to finish the Sourcerer.

The SQL code for your command has now been entered into the statements area. It should read

SELECT PURCHASE_COST, QTY_ON_HAND, DESCRIPTION
FROM PUBLIC.PRODUCT_TBL
WHERE PRODUCT_CODE = 'HW'
AND PURCHASE_COST <= 1500
AND QTY_ON_HAND >= 15
  1. Press the execute query button to see the results of your search. You may wish to execute each query at least once. This gives you the chance to see if your query achieves the intended goals without having to wait until the servlet is finished.
  2. Change the name of the query from 'query1' to 'inStock' by typing the new name in the Statements: combobox at the top of the SQL Builder page. You can type over the existing name of query1.

You have now created an SQL search which has successfully queried a database and produced results.

Data Representations, Inc.
http://www.datarepresentations.com
support@datarepresentations.com
sales@datarepresentations.com
TOC PREV NEXT INDEX