How the Database Query Tool Works
SQL Editor and SQL Syntax Highlighting
Running Scripts with Multiple SQL Statements
Querying Several Databases at Once
Queries with Parameters and Macros
How the Database Query Tool Works
Like most of SQL query tools, the Database Tour does not execute your database queries directly. It makes some checks and then passes the queries for execution to the core application intended for doing this: 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.
This topic describes the common technique and Database Tour specific capabilities which helps you to build your queries and get their results. You will not find SQL tutorial here (at least because it is impossible to know all details of SQL for all existing types of databases). For more details about writing the SQL queries, please read your database documentation.
SQL Query Windows
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. The place where you type your database query 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.
- Output. This page is shown for Oracle databases, opened by FD interface. Here, the content of the server output is written (i.e. feedback sent using DBMS_OUTPUT package).
- History. This page contains SQL execution history for this SQL window.
SQL Editor and SQL Syntax Highlighting
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.
Editing SQL-text:
Query Execution
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 (or below the query text depending on the corresponding option). 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 for BDE connections, check Live Queries option before opening the query. 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 application 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 application's closing. Please consider this information when working with multiple instances of the application. When the application 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 a 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.
Running Scripts with Multiple SQL Statements
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 connection level.
| Database type | Default SQL statement separator |
|---|---|
| Interbase | Caret sign (^) |
| Firebird | Caret sign (^) |
| Oracle | Combination of the semicolon (;) and SQL Plus style separator (a slash (/) at the beginning of the new line after the statement; to specify it in the database settings, use a <sqlplus> macro). If you rely on defaults, it is recommended to use a SQL Plus style separator to separate PL/SQL blocks (where the semicolon is a part of the language) and a semicolon in plain SQL. |
| SQL Server | A GO word at the beginning of the new line after the statement; to specify it in the database settings, use a <mssqlgo> macro. |
| Other | Semicolon (;) |
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 process continues with the next statement.
Querying Several Databases at Once
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
WHERE ...
Queries with Parameters and Macros
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 application 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 application 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 application, 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 application 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 the 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.
Using Master Source
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.
See also
