The Basics
This documentation indeed to introduce you the elementary notions used in context with the Visual Query Tool application.
Table of Content
User
There are two categories of users: the designer, and the simple user. Al details are hidden for the user; only the designer can see and modify the text code of SQL tasks, customise parameter pages or change connection specifications.
The designer mode is accessible specifying the /D command line option, only if the package is not protected or if the protection password is known.
There is not a list of authorised users, but it is possible to protect the execution of commands using some incomplete ODBC connection specifications. Selecting the 'Complete Required' connection properties as active, for example in the lack of a mandatory password before establishing the connection, the ODBC will require a complete authentification, enforcing the user to fill the missed parameters.
Connection
The connection is a channel through ODBC to a local or remote data source. Each defined connection is identified by a symbolic name. You can create a new ODBC data source using the ODBC Administrator of operating system.
The application manages all defined connections, used or not by a bindery. Each bindery can use only one connection, but multiple binderies can use the same connection. Through a session a connection is opened only once.
The Connection Manager can be activated using the
toolbar button.
The connection can be defined using the base parameters like Data Source, User
ID and Password, or specifying a connection string. The second way can assure
more flexibility.
Task
The task in the first approximation is a SQL command, i.e. an optionally parameterised SQL (DDL or DML) or PL/SQL statement or query; it accomplishes an elementary processing on a database. The task is not only the SQL text code; it has attached some control information about conditional execution, data displaying and the destination of result data rows.
The essential semantic difference between a statement and a query is that the result of a query can be displayed; the SQL command laying at the base of a query is a select. The application does not recognize the nature of a task, the displaying or not of a result is on the designer option.
A statement, instead of a query, executes a basically processing of data (see insert, delete, update, create temporary table, etc.) and can be any SQL command or a PL/SQL procedure, different of a select, and it is a mistake to request from the application to display it's result.
Command
A command is one or a sequence of tasks, accomplishing a logically unit of data processing or a visualization.
A name identifies logically, and the attached icon visually a command. Each command has an owner folder.
If an optional parameterised conditional expression was attached to a task of a command, it's execution will be determined by the true or false value of this expression evaluated on launching of the command, and only the not disabled tasks will be executed.
Folder
The folder is a logical grouping of commands using the same, totally or partially overlapped parameters from the same shared parameter window, having common logical or functional mean.
A name identifies logically, and the attached icon visually a folder. Each folder has an owner bindery. This structure is displayed in application's outlook bar and in the Folder Datapage, which displays an icon for each owned command (Sample tables).
Bindery
The bindery is a folder container. All included items (tasks, conditional and parameter initialisation expressions) are executed using the same, shared connection. There can exist more binderies sharing the same connection.
A new 'chapter' in the application's outlook bar identifies visually a bindery (MDB Samples).
Package
The package is a repository, identified by a stand-alone operating-system file with .VQT extension. It contains binderies and connection definition(s). The Visual Query Tool application, when opens a package, displays it's content and enables the execution of owned commands.
The package to be opened at start time can be specified in the command line, or later, using the File | Open menu command. By default the application opens the Tutorial.vqt package. Installing the Default Extension Handling option, all files having the .vqt extension double-clicked in Windows Explorer will be opened by Visual Query Tool in user mode.
A package can be protected against unauthorised modification or visualisation with a designer password and against unauthorised execution specifying an incomplete connection with the 'Complete required' properties set.
Session
A session spans until the loading or reloading of a package or until termination of application. During the session the connections are opened on request, and are leaved opened until the session ends. In this moment all pending transactions (if the database engine enables transactional processing) will be closed according to user's explicit option to commit or rollback all changes if there are.
Parameter
In order to make all tasks highly customisable, Visual Query Tool enable the use of formal parameters in the body of tasks and task-related expressions like in execution-condition, parameter initialisation dynamic expressions, and in result destination filename. The formal parameters are the symbolic name of controls delimited by square ([, ]) or curly brackets ({, }) as shown in the following example.
select *
from Orders
where Order_Date between [FROM] and [UNTIL]
order by Order_Date
Control
Each command folder can have attached one customisable parameter page, in order to capture the actual value of task's formal parameters. To build these parameter pages the designer can use windows-like simple (like the SingleLineEdit, DatePicker, CheckBox...) or more sophisticated (like the DateTimePickerShowNone, FileInput, DataListView...) controls.
The parameter page design depends on the specifics of folder's commands. When a command have to been executed, all formal parameters in text of tasks and in conditional expressions are replaced by actual parameters values gave by user.
Each control on a parameter page is identified by a symbolic name. No restriction on usable characters, but avoid using SQL reserved words and symbols with special meanings, like square and curly braces.
All parameters inside task's code text are references to controls on attached Parameter Datapage.
Pre-processing
Pre-processing means the macro processing of a text before execution or evaluation. Texts submit to processing are: task's text code, conditional expressions, dynamic control initialisation expressions and output filenames.
Before execution the text is pre-processed regardless of semantic or syntactic mean of any piece of text and the parameters are replaced with the user-supplied value of the control with the same symbolic name.
For example the task containing [FROM] and [UNITL] will have the following form:
select *
from Orders
where Order_Date between {d '1993-01-01'} and {d '1993-12-31'}
order by Order_Date
The essential difference in the interpretation of square ([, ]) and curly ({, }) brackets are when the referred parameter is of date type or the current value is void.
For date data types the replaced value of a square bracketed reference is a date in canonical form ({d 'yyyy-mm-dd'} for date and {ts 'yyyy-mm-dd hh:mi:ss'} for date-time values), and for curly bracketed a string in ANSII format ('yyyy.mm.dd' for date and 'yyyy.mm.dd hh:mi:ss' for date-time), accepted for date literals too.
For void values like an empty SingleLineEdit, the replaced value of a square bracket reference is a null (four characters) and for curly bracketed a null string (no value).