|Database Tour Documentation|
Working with SQL Queries
SQL queries are executed either by your database server or database engine (ADO, BDE etc.). Therefore, SQL queries should be written using rules defined for currently opened database: for example, if you open Oracle database, write your query
by Oracle database rules etc.
To create a new SQL window in active database, click New SQL Window button , or choose Query | New... menu, or press Ctrl+Q. You can create any number of SQL windows for each database (you are limited only by operating memory).
In the SQL window, there are several pages:
Edit. Here you write your SQL text.
Result. Here the result of the SELECT query is shown. This page is shown only if the corresponding option of SQL Editor is on; when the option is off (default), the query result is shown at the bottom of the Edit page.
History. This page contains SQL execution history for this SQL window.
SQL editor is based on a 3rd party component called 'SynEdit', which available at www.sourceforge.net.
SQL statement can be entered manually or loaded from a file (including loading from command line) on Edit page. There are several templates of the most often used SQL expressions, which are available by clicking Templates button; select a template and then just edit its SQL text to produce needed SQL expression.
To automatically insert field names into the selected position in a SQL-expression, click the right mouse button and choose Insert Field Names (or use the corresponding button). This function is especially useful in case of large number of fields.
The SQL editor supports SQL syntax highlighting. Use Options | Environment | SQL editor | Font and Color to change the parameters of the syntax highlighting. In addition to this, there is an ability to specify the SQL dialect for the syntax highlighting on the Edit page. Different SQL dialects are usually applied to different database types and have different sets of key words and some other options, which can change the highlighting of some elements in your SQL code. In most cases the SQL dialect is set automatically depending on open database type (e.g. dBase, Oracle, SQL Server). But in some cases you may want to change it to more suitable (for example, change SQL dialect for your SQL Server database from MS SQL 7 to MS SQL 2K and vice versa). Please note that SQL dialect, as the syntax highlighting itself, does not have any influence on query executing, and is used only for convenient showing the SQL code.
It is possible to save an SQL text to a file or print it. When printing SQL text or saving it to RTF or HTML documents, the syntax highlighting and colors are kept.
To execute SQL statement(s), shown in SQL editor, click Execute button.
Note: if there is selected text in SQL editor, then only selected statement(s) will be processed.
If the SELECT query was successfully executed, the result data will be shown on the Result page. Use Tools menu to work with these data (when you on the Result page). Some databases allow to edit the result of SELECT query, if it comply certain criterias such as using only one physical table and not use aggregate functions in the SQL expression. To be able to edit query data, check Live Queries option before opening the query (BDE connections only). After SELECT query execution, if editing buttons (Delete, Edit etc.) above the query data are active, then this means that the query was opened in edit mode. You can export the query data to a new table / file or print it (including building reports).
The program saves the SQL texts of the executed queries (their quantity specified by the corresponding option) and some statistical information about them for each SQL window of each database. This information is available on History page of the corresponding SQL window. It can be copied to clipboard, which allows to automatically create the script with all queries from the history. To exchange SQL texts between different databases, use Export SQL History and Load SQL History buttons. To quickly copy SQL texts from one database to another, open the first database, then go to History page and open the second database holding SHIFT down. To navigate between SQL texts, use the Previous SQL, Next SQL buttons on Edit page, corresponding items from the Query menu or double-click needed row on History page.
History of all SQL windows of all registered databases are saved to a workspace file (full path of the file can be seen from Database | Registered Databases... menu). This saving is done in the following cases: before executing a query, before database closing, and before the program's losing. Please consider this information when working with multiple instances of the program. When the program is restarting, all history of all SQL windows of all databases are loaded from the workspace file into operating memory; then, when certain database is opening, all its SQL windows and their history are restored.
To quickly build query for selecting data from a table, you can just choose Query Data item from context menu of the needed table in the table list.
There is an ability to run multi-statement SQL scripts (scenarios). Each statement in the script must be separated with a statement separator (sometimes called terminator): a certain character or set of characters (without spaces), which is specified on the database level.
To change the SQL statement separator for a certain database, make sure the database is closed, then choose Database | Registered Databases... menu, select the needed database, click Edit button, find the SQL statement separator property, change its value to whatever you need, and click Apply Changes.
There is also an advanced way to execute multi-statement SQL scripts using a drop-down menu near Execute button, where you can also specify a log (result) file to save the information about each statement processing result. Attention! In this mode, when error is occurred during the statement execution, the current statement is skipped and the proccess continues with the next statement.
There is an ability to build SELECT queries, which refer another database from open local database (only BDE connections). Moreover, you can join tables from several different databases (so called heterogeneous joins). Local database is any database with Paradox, dBase, or FoxPro tables, connected via BDE alias or folder name. The external database may be folder reference (for local tables), ODBC DSN or a BDE alias. To refer a table from external database, prefix the table name with colon, external database name, and another colon, and enclose this construction in quotation marks, for example:
SELECT c.custno, o.orderdate
FROM ":CustDSN:customer" c, ":OrderAlias:order" o
Parameters and macros are substitution variables in queries. They improve flexibility of query execution by allowing dynamically change the query (without changing of SQL text) in runtime before the execution. These variables get their values directly before query execution either by user input, command line parameters, report (when the query is executed as part of report), or they can be calculated automatically if they are predefined macros or parameters (see below). Macros are always text variables; their values just replaces macros text in the SQL text before query execution. Unlike macros, parameters have type; their values and types are passed to server and not inserted in SQL text. Macros and parameters inside comments or string literals are ignored by the program SQL engine, but is not recommended to use parameters inside comments, especially in ADO connections. Although macros are often more convenient to use, using parameters are preferable as they help in performance optimization and against SQL injection.
If parameter or macro is not predefined one and its value was not defined in command line or report (Database Tour Pro only), the user will be prompted to enter it in a separate window.
Macros begin with << and end with >>. Macros are defined mainly for substitution when executing queries via command line. In the following SQL example, there is CUST_TYPE_LIST macro:
SELECT * FROM payments
WHERE customertype in (<<CUST_TYPE_LIST>>)
The value for this macro can be, for example, the following string:
3, 8, 12, 5
Note that such thing cannot be produced using parameters.
Parameters begin with colon. Parameters, which contain spaces, must be enclosed in single quotes. Parameterized queries are convenient for using the same SQL statement for many data values. In the following SQL example, there is a DATE parameter:
SELECT * FROM payments
WHERE paydate = :DATE
When the program executes such a query, it suggests user to enter parameter value and data type, and then continues execution. To select parameter data type automatically by the program, you may specify this data type directly in SQL statement in the separate comments right after parameter, as shown in the following example:
SELECT * FROM orders
WHERE orderno < :ORDERNO /* ParamType=Integer */
There are several predefined parameters, which are calculated automatically by the program and does not require user or command line input:
<<SYSTEM_DATE>> Replaced by current date (with Date type)
<<SYSTEM_DATETIME>> Replaced by current date and time (with DateTime type)
<<SYSTEM_TIME>> Replaced by current time (with DateTime type)
<<SYSTEM_YEAR>> Replaced by current year (with Integer type)
<<SYSTEM_MONTH>> Replaced by current month (with Integer type)
<<SYSTEM_DAY>> Replaced by current day (with Integer Type)
If predefined parameters begin not with colon, they are interpreted as predefined macros. In case of integer data, the result will be the same, but in case of their data types there may be problems, therefore please don't confuse parameters and macros.
You can use a master source for query. Master source is the table, opened in the separate window. If SQL text contains a column name of the master table followed by the colon, then this will be interpreted as parameter. Value of this parameter is taken from corresponding field of the master source (such field must exist in the source).
Example (table 1 is used to select data from, table 2 as master source, and column 1 is a column from table 1):
SELECT * FROM <table 1>
WHERE <column 1> = :CUSTOMER
Select master source (table 2) from the table list and right-click it. Choose Open in the Separate Window from the context menu. The table must contain CUSTOMER field. Click the Use Master Source button (on Result page) and execute the query. You will see the record set from the table 1, in which value of column 1 equal to value of the CUSTOMER field from the master source (table 2). When you moved cursor of the table 2 to another record, then the query will automatically reopened to match value of column 1 to the new value of the CUSTOMER field.
Main window after executing a query: