The Advantage Data Architect includes a number of useful and interesting utilities that you can use to build and test SQL statements. These include the Advantage Query Builder, the Native SQL Utility, and the ODBC SQL Utility. Each of these utilities is discussed in the following sections.
The Advantage Query Builder is a handy little utility for creating basic SQL SELECT statements. It provides a graphical user interface (GUI) that permits you to select fields and join tables visually. You can use this visual depiction to generate a SQL SELECT statement, which you can then test.
The following steps demonstrate how to use the Advantage Query Builder:
Select Tools | Advantage Query Builder from the Advantage Data Architect main menu. The Select Database dialog box is displayed:
Use the Select Database dialog box to select the directory or data dictionary that contains the tables you want to include in your query. Click OK when you are done. If you are opening a data dictionary that requires you to log in, you will also need to enter the user name and password into the displayed dialog box and click OK to continue to the Advantage Query Builder, shown in Figure 9-1.
Figure 9-1: The Advantage Query Builder
The Advantage Query Builder contains four sections. The table list in the right- hand pane contains all tables and views in your data dictionary. The bottom pane contains the work area where you will refine and display the query, the large section in the middle of the Advantage Query Builder contains the modeling area, and there is a toolbar along the top.
Begin your query by dragging one or more tables or views into the modeling area. In this case, first drag the CUSTOMER table into the modeling area, and then drag the INVOICE table. Each table appears as a window in the modeling area. Each window contains an asterisk, as well as a row for each field in the table.
To select fields, click to the left of the * to select all fields from that table, or click to the left of individual field names to select specific fields. When a field is selected, a checkmark appears to the left of the field, and the field name appears in the Columns tab of the work area. Select the Customer ID, First Name, and Last Name fields from the CUSTOMER table, and the Invoice No, Employee ID, and Invoice Date fields from the INVOICE table.
If you have added two or more tables to the work area, you need to join them. To join two tables, drag from the field on which you want to link from one table, and drop it onto the corresponding field in the table to which you want to link. In this case, click the Customer ID field in the CUSTOMER table, and while keeping the mouse button depressed, drag to the Customer ID field of the INVOICE table, and then release the mouse button. A line will connect the two fields in the modeling area, as shown in Figure 9-2.
Figure 9-2: A query being prepared in the modeling area
You perform the rest of the configuration, if needed, using the work area. If you want the query to sort by one or more fields, right-click the Sort row for those fields and select either Ascending or Descending, depending on which direction you want to sort by. In this case, right-click the Sort row for the Customer ID column, and select Ascending.
If you want to calculate an aggregate statistic, right-click the Function row associated with the field you want to aggregate across, and select one of the available functions. If you use an aggregate function, you need to group by the fields across which you want the aggregate calculated. To group on fields, right-click on the Group row for the fields you want to group on, and select Group. In this example, we are not going to use aggregate functions or groups.
When you are ready to create your query, click the Generate SQL button in the Advantage Query Builder toolbar. The Advantage Query Builder examines your model, and then displays the generated SQL in the SQL pane of the work area, as shown in Figure 9-3.
Figure 9-3: Generated SQL appears in the SQL pane of the work area.
You can use the generated SQL in the SQL pane as is, or you can make changes to it, if necessary. To test the query that appears in the SQL pane of the work area, click the Run Current SQL button in the toolbar. The query result will appear in the results pane of the work area, as shown in Figure 9-4.
Figure 9-4: The result set selected by the SQL statement shown in Figure 9-3.
When you link tables, the join produced by the Advantage Query Builder is based on the left-to-right order of the tables in the modeling area.
If your model is a complicated one, and you want to work with it again later, you can click the Save Model button in the toolbar. To open a previously saved model, click the Open Model button in the toolbar.
If you want to save the SQL that appears in the SQL pane of the work area, click the Save SQL button in the toolbar. Alternatively, you can copy the SQL from the SQL pane to the clipboard, and paste it wherever you need it.
You can also save the query result to a new table (ADT or DBF) by selecting the Save Results button in the toolbar.
While the Advantage Query Builder is a nice little utility for creating simple SELECT statements, it is a limited tool with a few quirky behaviors. Consequently, in many cases, you will need to modify the SQL that the Advantage Query Builder generates, or build your queries manually instead.
The Native SQL Utility, shown in Figure 9-5, is a valuable tool for testing SQL statements. To display the Native SQL Utility, select Tools | Native SQL Utility from the Advantage Data Architect main menu.
Figure 9-5: The Native SQL Utility
You must connect to a data dictionary or a directory before you can execute a SQL statement. To connect, select the Connection Type of Alias, Path, or Dictionary, and then specify the alias, path, or dictionary you want to connect to. Then click the Connect button. If you are connecting to a dictionary that requires login, you will also be prompted to enter your user name and password.
If you want to control exactly how you connect, click the Query Options dialog box in the Native SQL Utility toolbar to display the Options dialog box shown in Figure 9-6. Click OK after changing any settings in the Query Options dialog box.
Figure 9-6: Options dialog box for SQL queries
Use the Options dialog box prior to connecting to your database. If you use the Options dialog box to modify the settings used for a connection, but you are already connected to a database or a directory, disconnect and then reconnect to use the new settings.
You use the Native SQL Utility by entering one or more SQL statements into the SQL pane, and then click the Execute SQL button. You can also control which statement or multiple statements to execute by highlighting it first before clicking Execute SQL. If your SQL statement or statements produce a result set, the returned records are shown in the results pane. In response to each SQL statement you execute, information about the query is displayed in the message area, which is located between the SQL editor and the results pane. Also, the length of time that ADS required to execute the query appears in the status bar, as shown in Figure 9-7.
Figure 9-7: The results of a query displayed in the results pane
If a SQL query takes more than two seconds to complete, a progress bar showing the progress of the query execution is displayed. Click Cancel SQL if you want to cancel the execution of a query in progress. This is useful for queries that are taking a long time to execute and either you do not want to wait for it to finish execution, or you suspect that something is wrong with the query even though it is syntactically correct. You can check the syntax of your SQL statements before you execute them by clicking Verify Syntax.
The toolbar buttons in the Native SQL Utility provide a number of additional features. Open Script allows you to load text files containing SQL or to load previously saved SQL files into the SQL pane for testing. Close Script closes the current SQL file if one has been opened in the SQL pane. Save Current Script to File (CTRL-S) and Save Script As permit you to save the current SQL into a file (with a .sql extension). Click Print SQL Statements to print the statements in the SQL pane. Clear All SQL Statements clears the contents of the SQL pane.
Query Options displays the Query Options dialog box described earlier in this section. Editor Options allows you to configure the SQL editor using the Editor Options dialog box. Query Builder displays the Advantage Query Builder described earlier. Use the Table Passwords option to enter passwords for any encrypted free tables that are involved in your SQL statements.
Once you remotely connect to ADS from within the Native SQL Utility, the following toolbar buttons are displayed: Begin Transaction, Abort Transaction, and Commit Transaction allow you to run SQL queries within a transaction for a user account connected to ADS remotely. Finally, Data Dictionary Links displays the Active Links dialog box, which allows you to view active data dictionary links and to define data dictionary links, permitting you to execute queries involving multiple data dictionaries.
The ODBC SQL Utility is a tool for testing queries using the Advantage ODBC driver. To display the ODBC SQL Utility, select Tools | ODBC SQL Utility from the Advantage Data Architect main menu. The ODBC SQL Utility is shown in Figure 9-8.
Figure 9-8: The ODBC SQL Utility
Before you can test SQL statements using the ODBC SQL Utility, you must connect to a data dictionary or a directory using the Advantage ODBC driver. If you are connecting to a data dictionary, make sure to check the Login Prompt checkbox. Once you click Connect, the ODBC SQL Utility will prompt you for a valid user name and password for the data dictionary.
Like with the Native SQL Utility, you can then enter SQL statements in the SQL pane, and then test them by clicking the Execute SQL button. Messages concerning the SQL statement are displayed in the message area, which is located between the SQL pane and the results area, as shown in Figure 9-9.
Figure 9-9: A query has been executed from the ODBC SQL Utility.
Most developers prefer to use the Native SQL Utility over the ODBC SQL Utility. The Native SQL Utility provides more options than does the ODBC SQL Utility, and it also tells you how long ADS took to execute the query. The Native SQL Utility uses the ACE API to execute your queries, which is installed automatically when you install the Advantage Data Architect. With the ODBC SQL Utility, you must install the Advantage ODBC driver and configure a data source name before you can use the utility.
In the next chapter, you will learn how to use many of the more common SQL statements.