We have briefly introduced queries in the preceding chapter on the Queries List. Here, we shall examine the topic of queries in more detail. This is an advanced subject, and if all you are interested in is producing a Report based on a query, you can skim through this material on your way to the next chapter that deals with Reports.
To recap, Alventis supports queries using a large subset of the ANSI SQL-92 language specification. Queries are per-Database, i.e., they have access to the tables of a single Database they belong to. Queries may be based on data tables of the Database. They may also "draw upon" some System tables, e.g., the table of Users. Queries are considered to fall into one of two categories: Select queries (the SELECT statement) and Non-Select queries (everything else, e.g., UPDATE, INSERT, and so on). Alventis automatically creates a set of Default queries for you: one per data table. Default queries may be used as source of Reports based on their respective tables. Default queries can be neither deleted, nor modified.
In the following discussion, we will be using selected SQL query examples, but we will not be explaining their syntax. You can consult the included SQL Reference if something is not clear. If you find the Reference too terse for your liking, you can also find some good books on the SQL language, most of which will apply to Alventis with few modifications.
The Query form is depicted below.
At the top of it, we find the usual DataNav bar. On the right-hand side of the bar, there are 3 buttons that we have not seen before.
Run Query. Click this button to attempt to run the query or, said differently, execute its statement. Depending on the nature of the query and the amount of data it has to process, this may take a short or a long time, so you may have to be patient once you have clicked this button. Note that if the query is of the Non-Select type, it will proceed right away to modifying whatever data you instructed it to modify. There will be no "Are you sure?" prompts and no opportunity to cancel or undo your changes (short of restoring the original data from a backup), so proceed with caution.
Save As. Clicking this button immediately makes a copy of the current query. This makes it easy for you to make a clone of an existing query and apply whatever changes you want to the clone thus preserving the original query intact.
Export Results. This button launches the Data Export Wizard that allows you to export the currently-displayed results produced by your query to a delimited text file. We'll discuss Data Export in a separate chapter.
Just below the toolbar, we find a group of familiar Text Boxes with fairly self-descriptive names. As is usually the case, you can set Name, Caption, and Comment to whatever you find useful.
In the middle of the form we find the two main controls. The Query Box is a plain-text memo control. This is where you write the text of the query. SQL doesn't care about carriage returns, so you can format your query as you see fit.
Just to the right is the Parameters grid. Parameterized queries use parameters in their statements. Such parameters act a little like variables that allow you to leave the query statement unchanged but still modify the query's behavior by setting these parameters to different values. Parameters in SQL are identified by the colon ":" that precedes their names.
The query shown in the above screenshot has one parameter "Subj". Alventis places all parameters it finds in your query into the Parameters grid where you can assign the values you would like them to use when the query is executed. You can also write a little Description of each parameter. This is not used anywhere and is just for your convenience. Parameters are automatically created and deleted based on the query statement you write, you cannot create/delete/rename them in the Parameters grid.
Parameter data types are interpreted according to what the value looks like. Alventis tries to interpret the value as a number, as date/time, and if the above attempts fail, as a string. If you want to force a particular Parameter to be treated as a particular type, you can use the SQL CAST() function. For example, CAST(Xyz AS Integer). To force a value to be interpreted as string, you can simply enclose it in single quotes: '123' would then be seen as string (as opposed to 123 which would be seen as integer).
Parametric Queries are only supported for single Queries, not batch Queries consisting of multiple SQL statements separated by semicolons.
The bottom of the form is occupied by the Output grid. This is where a Select query displays the resulting records. Non-Select queries don't have any result in the form of records, so they will not display anything there. Once you have produced some Output results, you can modify the query if necessary. For your convenience the old Output is not automatically cleared, so that you could refer to it while editing the query. It may no longer be up-to-date though, so Alventis displays a warning to this effect in the Status bar at the very bottom of the form.
The information shown in the Output grid is read-only.
You can usually open the data record displayed in the Output grid by double-clicking it. For this to work, Alventis must be able to figure out the ID value of the record. Simply put: the Output must include the ID field of the main table the query is written against.
If the database engine fails to interpret your query statement due a syntax error, the error will be displayed in the Status bar. If the error makes a specific reference to a particular position in the query statement, Alventis will place the cursor at the "offending" position so that you could see right away where things went wrong.
Saving a query (i.e., posting of its record) saves the query statement, the parameters, and their values. It does not however save the query Output results, if any.