In Exportizer Pro and Exportizer Enterprise, it is possible to build and execute custom SQL queries.

SQL queries give you ability to:

• View only needed table fields / columns.
• Use expressions as fields.
• Combine data from several tables using joins, unions etc.
• View only records that meet user specified criteria.
• Use parameters and macros. SQL query parameters and macros allow reusing the same SQL text to produce different criteria.

You can use SQL queries either in Exportizer GUI or in action files and command line.

SQL Editor

Basic SQL Query

If you are new to SQL, start from this:

SELECT * FROM <table_name>
WHERE <column_name1> = ... AND <column_name2> = ...

Here, <table_name> is the name of the table to export data from, and <column_name1> and <column_name2> are the names of columns in the table.

Supported Types of SQL Commands

Exportizer allows to execute not only SELECT queries, but SQL commands of all other types, supported by the connected database, for example:

• Other DML queries like INSERT, UPDATE, DELETE, MERGE;
• DDL commands for creating or altering tables, views, stored procedures etc.);
• Commands to manage user permissions;
• Other types of commands, supported by your database.

Note: SQL commands are executed either by your selected database engine (e.g. ADO) or by the database server (if the current database is a server database like Oracle, SQL Server, MySQL etc.). Please refer to your database documentation for details of specific SQL commands and their syntax.

Executing Query

To execute an SQL command, type or load its text in the SQL editor and then click Execute Query button or select the corresponding item from the Query menu. After successful execution, if your query is of SELECT type, its result will be shown in the data grid below.

Please note if the SQL editor contains selected text, only selected query will be executed.

You can work with the SELECT query results in the same way as you do with tables, including exporting or copying data etc.

It is also possible to execute multi-command scripts here. Make sure you correctly separate the commands in the script; in most databases, semicolon is used to separate them.

After executing a query or a script, some statistical information will be available on the Statistics page. For scripts, statistics for every command in it will be available too. For Oracle and PostgreSQL databases, the server output will appear on the Output page (if the corresponding database option is set).

Note: Exportizer executes SQL commands in auto-commit mode.

Queries with Parameters and Macros

Parameters and macros are substitution variables in queries. They improve flexibility of query execution by allowing to change the query dynamically (without changing of SQL text) before the execution. These variables get their values immediately before query execution either by user input or command line parameters.

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 database server or database engine 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 there. Although macros are often more convenient to use, using parameters is preferable as they help in performance optimization and protecting against SQL injection.

If parameter or macro is not predefined one and its value was not defined in command line, the user will be prompted to enter it in a separate window.

Macros

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 customer_type in (<<CUST_TYPE_LIST>>)

The value for this macro can be, for example, the following string:

3, 8, 12, 5

Note that such result cannot be produced using parameters.

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_OF_PAYMENT parameter:

SELECT *
FROM payments
WHERE pay_date = :DATE_OF_PAYMENT

When the application executes such a query, it suggests user to enter parameter value and data type, and then continues execution.

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 do not begin 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.

Copyright (C) 2024 Vitalii Levchenko Software. All rights reserved.