SQL queries allow you to retrieve any kind of data from the database, therefore they are usually used as report data sources.
You can write the queries either when creating new report or when editing existing report (using Data Source page).
The queries should be written using rules defined for open database: for example, if you open Oracle database, write your query by Oracle database rules etc.
SQL editor and SQL syntax highlighting
Querying several databases at once
Queries with parameters and macros
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 (using the corresponding button or by dragging the file from Windows Explorer). Entered SQL text can be saved to file or printed.
The SQL editor supports SQL syntax highlighting. Use Options | Editor Options... to change the parameters of the syntax highlighting. When printing SQL text, the syntax highlighting and colors are kept.
Dynamic SQL code completion simplifies writing SQL code:
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.
If you need to choose either to use macros and parameters, use parameters. They are processed by the server and can produce optimized query execution plan.
If parameter or macro is not predefined one and its value was not defined in command line or report, the user will be prompted to enter it in a separate window. There is an ability to specify default value for parameter and the prompting text, which will be shown to user when entering parameter value.
Parameters in SQL
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 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 other data types there may be problems, therefore please don't confuse parameters and macros.
Macros in SQL
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 the variable list of values cannot be produced using SQL parameters.
See also
