Using QM Query

Before we can examine the use of QM Query, we must become more familiar with it. To launch QM Query, issue the command:

      STRQM 

The Query Management Query is displayed. Select Option 1 to maintain the queries and the Work with Query Manager Queries panel (Figure 25.36) is displayed.

Figure 25.36: Working with queries in QM Query.

Press F19 until the Query Creation mode is set to SQL. Type ‘1’ on the option line to enter the name of the query and press Enter. A blank SQL entry screen is displayed. Enter any SQL statement in the source file; press F3 to exit and save. Then run the desired statement. The query can be run from the command line or from within a CL program using the following syntax:

      STRQMQRY QMQRY(your-query) 

Following these steps allows you to create and execute the QM queries discussed throughout the remainder of this chapter.

Use QM Query to Perform Any SQL Statement

QM Query provides an excellent tool for developing SQL-based reports, but it is not limited to that role. QM Query can perform other SQL statements as needed. The simplest and perhaps most powerful use of this tool is to create a query that will perform any requested statement. As shown in Figure 25.37, languages such as CL, which have no direct support for SQL, could then make calls to this generic query to execute SQL statements when needed.

Figure 25.37: CL Command using QM Query to execute SQL statements.

To create this tool, start by creating a QM Query named ADHOC (feel free to change the name to anything you like). The complete source code for the ADHOC query is:

      &STATEMENT 

The variable &STATEMENT must be passed into the query at run time. The variable names can be up to 30 characters long and should be keyed in uppercase. Up to 50 different variables may be defined for each query. Exit and save the query. It might seem overly simple, but this very flexible query will perform whatever query statement is passed to it as a parameter. To run this query, issue a command such as:

      STRQMQRY QMQRY(ADHOC) SETVAR(STATEMENT      'UPDATE ITEMMAST SET IMPRICE = IMPRICE * 1.1') 

Notice that the SETVAR keyword is used to load the &STATEMENT variable. Variable names will be converted to uppercase if not enclosed in quotes, and the ‘&’ should not be coded on the STRQMQRY command. It is only used within the source for the query. The data associated with the variable has a maximum length of 55 characters. If more than 55 characters of data are needed, multiple variables can be strung together to form a complete statement.

This command can be run from a command line or from within a CL program to add 10 percent to the price of every item in the Item Master File. The most important thing to note is that any SQL statement may be issued here, making this a remarkably valuable, yet simple tool for running SQL statements from within other programs.

Passing Character Literals within Parameters to QM Query

The previous example shows how to pass an entire SQL statement to a QM Query for processing. That works quite well until a literal value must be passed, as in the following statement:

      UPDATE TRANFILE SET POSTED = 'Y' WHERE POSTED = ' ' 

Because single quotes are used both to delimit the text of the SQL statement and the character literals within the statement, great care must be taken to avoid confusion. If entering the entire statement as a literal, enter the command as follows:

      STRQMQRY QMQRY(ADHOC) SETVAR((STATEMENT      'UPDATE ITEMMAST SET POSTED=      "Y" WHERE POSTED = " " )) 

Two single quotes are used on each side of the literal values in the statement. They will be replaced with a single quote mark in the SQL statement being executed:

      UPDATE ITEMMAST SET POSTED= 'Y' WHERE POSTED = ' ' 

Using this technique, complex SQL statements that might otherwise be difficult to code can easily be executed.

Passing Character Literals from CL Programs to QM Query

Character literals may be delimited using single quotes, but when creating a CL program, the issue becomes increasingly difficult because the character literals must often be enclosed within other character fields. To execute the same statement, a CL program that uses the ADHOC QM Query might look like Figure 25.38.

Figure 25.38: Passing character literals to ADHOC query.

This sample program contains two variables: &SQL, which is a large character field designed to contain the SQL statement, and &QUOTE, which is a 1-byte character field that is automatically loaded with a single quote. Notice the four quotes in the VALUE keyword. The first and fourth quotes mark the beginning and end of the string, while the second and third quotes form a pair within the string. This pair is translated into a single quote. This technique allows the variable &QUOTE to be used anywhere a single quote is needed. The CHANGE VARIABLE command assembles the SQL instruction, which is then passed to the ADHOC QM query within &SQL. That SQL statement is then executed.

Obviously, more complex logic could be used to create the SQL statement, but the fundamental process of executing the statement remains the same.

Passing Multiple Parameters to a QM Query

The previous example showed how to run any desired SQL statement using a QM Query but, at times, something more specific may be required, either to gain more control or perhaps to simplify the process. The following example shows how to create a QM query that deletes records from any table that meets a specific condition. The QM Query used for this example (DELRCDS) is more complex than ADHOC. Its syntax is:

      DELETE FROM &FILE WHERE &FIELD &CONDITION &VALUE 

Any program that calls this query must pass to it the name of the file to update, the field name to test, the condition to test for, and the value to be tested for. Any records that meet the search criteria are deleted.

The CL program syntax is shown in Figure 25.39:

Figure 25.39: Passing multiple parameters to QM Query.

This handy and simple tool allows any CL program to easily delete selected records from any file. Given that CL lacks any file update capability, this is a very useful option. In this example, four different parameters are passed to the QM Query and the SQL statement is partially written in advance. In this case, the statement is always a delete, no matter what parameters are passed. Compared to the previous examples, this technique allows more control and more security by strictly limiting what the query can do.