The MetaSQL Editor can be an extremely useful tool for report authors who want to test the accuracy of their MetaSQL statements during the process of writing report definitions. To open the MetaSQL Editor, run it from the location where it is installed. The following screen will appear:

MetaSQL Editor

To perform tests using the MetaSQL Editor, you must first connect to your target database. To connect to a database, select the “Database” option from the “File” submenu, as shown in the following screen:

Connecting to a Database

For the purposes of this example, we will be connecting to an xTuple database. That explains why, when we select the “Connect” option, we are brought to a database login screen which has been customized for the xTuple application:

MetaSQL Editor - Connecting to the Database

Under the OPTIONS button, we specify our server, database, and port connection information. And then, we are asked to enter a valid username and password.

Once we have successfully connected to our target database, we can begin testing the accuracy of embedded MetaSQL statements using the MetaSQL Editor.

For this exercise, we will demonstrate the MetaSQL Editor functionality using a very basic and simple query. The goal of the exercise is to leave you with an understanding of fundamental MetaSQL Editor mechanics. You are free to perform more complex and extensive testing on your own.

Now that we are connected to a database—in this case, an xTuple database—we can write a simple query and then test the results. To enter a query, simply type in the MetaSQL Editor’s main text field, as shown in the following screen:

Simple SQL Query with Embedded MetaSQL

As you can see, the query begins with standard SQL. We are seeking to SELECT four pieces of Item information FROM the item table. Our MetaSQL is inserted within the WHERE clause. The MetaSQL states that the item_number for the selected Item must match a specified item_code. The term item_code is a parameter. We can set different values for the item_code parameter using the MetaSQL Editor—and then run the query to test the results.

However, before we run the query, we must first define the parameter item_code and assign it a value.

Earlier in this chapter we discussed parameters and how they enable reports to respond to dynamic conditions presented by application users. The MetaSQL Editor enables you to simulate these dynamic conditions, by defining parameters and assigning values to them.

Let’s assign a value to the parameter we entered in our MetaSQL example—namely, the parameter item_code. In our example, we are simulating a situation in which an xTuple user inputs an item_code. The item_code, in turn, corresponds to an item_number in the item table.

Before we can test our MetaSQL, we must first create the parameter item_code. To create a parameter, we open the “Parameter List” option from the “View” menu. The following screen will appear:

Parameter List

The parameter list screen displays information on all parameters which have been created for testing purposes, including Active status, parameter name, data type, and value.

To create a new parameter, select the NEW button. The following screen will appear:

Enter New Parameter

When entering a new parameter, you are presented with the following options:

To the far right of the screen, the following buttons are available:

After we have entered the appropriate values and selected the OK button, the following screen appears:

Specify Value for Parameter

As you can see, we are asked to enter a value for the parameter. In this case, we enter YTRUCK1. This is an item_number from our sample database. By entering that value here, we are linking the parameter item_code to item_number YTRUCK1. Selecting the OK button saves our information.

Back on the parameter list, we can verify the information has been entered correctly, as shown in the following screen:

Parameter List Containing Sample Parameter

Now that we have our sample query and corresponding parameter, we are ready to begin our testing.

Before we execute our query, we must first parse it using the MetaSQL parser. As we have discussed previously, the MetaSQL parser interprets MetaSQL statements embedded in a query, evaluates the available parameters, and then produces standard SQL. The resulting standard SQL is then sent to the database for processing.

To parse select the “Parse Query” option from the “Tools” menu. The following screen will appear:

Parsed Query in MetaSQL Editor

Notice that the MetaSQL parser creates a Log Output screen, where messages related to the parsing process will be written. In our case, we get a “Query parsed” message, which means our MetaSQL was parsed successfully.

Once a query has been parsed, we are ready to execute it. Executing a query means the standard SQL produced by the MetaSQL parser is sent to the database for processing. To execute a query, select the “Execute Query” option from the “Tools” menu. The following screen will appear:

Results of Executed Query

When a query is executed successfully, the results will be displayed in a Results Output screen. As you can see in the example, the value returned in the item_number column equals the value assigned to the parameter item_code.

One of the most powerful features of the MetaSQL Editor is the ability to view the standard SQL generated by the MetaSQL parser. This is the same standard SQL the parser sends to the database for processing. This visibility is especially helpful when attempting to troubleshoot lengthy queries having complex MetaSQL statements and numerous parameters. To view the standard SQL output, select the “Executed SQL” option from the “View” menu. The following screen will appear:

Standard SQL Output from MetaSQL Parser

As you can see, the MetaSQL parser has stripped the MetaSQL elements from the original query to produce standard SQL which can be processed by the database.

This concludes the section on using the MetaSQL Editor. Again, we used a very simple query to illustrate the basic mechanics of the MetaSQL Editor. We encourage you to test more complex queries and parameter lists on your own.