The  Query  panel is where the User creates and executes Structured Query Language (SQL) commands to manipulate information in a database. Prior to issuing these commands, the User must Connect and select a database.

The Query text area supports the input of any standard Structured Query Language (SQL) command. In addition to supporting a single command, numerous commands can be run in batch mode simply by ending each SQL command with a semi-colon (;). Queries are executed by clicking the  Run Query  button or the  Report  tab in the header.

The  Reset Query  button will clear the text displayed in the Query Text area.

Comments can be added to a query as well, and are identified by either a double hyphen (--), or a pound sign (#). Comments can start anywhere on a line, but once a comment is started on a line, the remainder of that line is treated as a comment.

Query examples:

A simple SELECT query:

select * from prices where price > 55

A single SELECT command will produce a report to which a Layout can then be applied.

A more complex batch query containing comments:

# This query updates prices
update prices set price = 27.25 where product_id='44970';
-- Notice that a semi colon ended the prior query
update prices set price = 12.44 where product_id='99123';

When non-SELECT queries are executed, the resulting report will show the query followed by its impact to the database. If a database error is detected, it is highlighted in red, such as:

Database error # 1146. Table 'test.prices' doesn't exist

The size and number of queries that can be entered in a batch is dependent on how much information can be entered into the query area, such as through copying and pasting. This is totally dependent on the computer's operating environment and browser being used. The maximum amount of data that can be stored as a query is 16.77 million characters. Queries larger than this size will be truncated, if entering this much data into the query text area is even possible. Although it is unlikely that a query would be 16 million characters in length, entering something such as 2,000 INSERT commands as a single batch query is acceptable.

Note: The MySQL SET command can be used in batch queries. However, the life of the value assigned to the variable only applies to the current queries present when the  Run Query  button is pressed. More persistent values can be set for all supported database engines by using the user session query.

Text can be copied and pasted into the Query text area.

Batch queries:

Multiple queries, including more than one SELECT query, can be run at the same time. Although a layout cannot be applied to the results, it is a quick way to see information from multiple tables at once. An example is:

select * from customer where custnum = 14;
select * from orders where custnum = 14;

The resulting output is shown below. The results of each select statement are separated by a horizontal rule. Directly under the rule, the query text will appear followed by the results of the query, which can be either a report (for a SELECT query) or a database message (for the results of an action query, such as INSERT, etc). These results cannot be formatted with a layout since multiple queries were run at once. A layout can only be applied to the results of a single SELECT query.

Suppressing batch query messaging:

It may be desirable at times to suppress the messages that batch queries generate. One such time is when batches contain hundreds (or perhaps thousands) of insert queries. Another use is when preliminary processing, such as the creation of temporary tables, needs to take place prior to a report being produced. By suppressing messaging, a report can be produced where a Layout can be created and Report Tools can be accessed.

In order to suppress messages, and turn messaging back on, a pair of comments (-- suppress_on and -- suppress_off) can be used. Here is an example of such an approach in a batch query:

-- here is our batch query.
-- the first step is to create a temporary table in memory to store data
-- this next line turns off message reporting
-- suppress_on
CREATE TEMPORARY TABLE paper_summary
(netid text, first_rdate date, last_rdate date,
 total_count int, total_amt float, avg_cost float);

-- now insert the data into the table.
INSERT into paper_summary select netid, min(rdate), max(rdate), count(*),
       sum(total_amt), sum(total_amt)/count(*)
FROM newspaper
GROUP BY netid;

-- now turn messaging back on to allow the report to be displayed.
-- suppress_off
-- here is the report query.
SELECT
 a.campaign, a.newspaper, b.first_rdate, b.last_rdate, b.total_count,
 Sum(a.count), max(b.avg_cost),max(b.total_amt)

FROM   calls a, paper_summary b
WHERE  a.npid=b.netid  AND  a.campaign  =   'SQ'
GROUP BY  a.campaign, a.newspaper
ORDER BY  a.newspaper

The end result is that all the queries will be executed, but only the report will be displayed as though the SELECT query was run by itself.

Substitute message when no results:

The -- no_result keyword, followed by a message, can be used with a SELECT query prior to the select statement. When no rows or results are produced by the query, the message will be substituted for the output in the  Report  panel. For example, the following will substitute the message No results found:

-- no_result No results found
select first_name,last_name from customer where last_name = "asher"

The default message is blue, but HTML tagging can be used to customize the message as desired. The following would produce No results found in bold red:

-- no_result <font color="#FF0000"><b>No results found</b></font>
select first_name,last_name from customer where last_name = "asher"

It is possible to add prompts to queries that will accept user input variables. These are called "query variables". QLR Manager version 5 introduced a more robust method for declaring input variables in queries and wizards. The recommended method of defining a query variable is to enclose the variable name in brackets, such as [part number].

Note: If the server database is Microsoft SQL Server, the alternate curly bracket must be used, such as {part number}.

The variable name is also used as the prompt text in the query variable input panel. To maintain backward compatibility, the older method of defining a query variable by placing two asterisks ** in front of the variable name, such as **part_number, is still supported.

The older ** method relied on the presence of a blank space to determine the end of the query variable name. Therefore, blank spaces could not be part of a variable name. The new [variable name] and MS SQL Server {variable name} method overcomes this limitation.

The "@" character, followed by a number, is used to specify the maximum input length for the input variable. The @ should be included as part of the variable name when a maximum length for the input value is desired and to provide additional security. See below for tips on query security. When using the newer method of declaring a query variable enclosed in brackets, the @value should also be included within the brackets.

When QLR Manager executes the query, it examines the query text for all unique occurrences of query variables. It then displays an input panel for the User to enter the desired value for each variable found. For example, the following query has two query variables:

select *
from order_item
where partnum = '[part number@12]'
and qty >= [minimum_quantity@5]

When this query is run, it will produce an input panel that looks like:

The default HTML form element for collecting user input for a query variable is a text input. With the Enterprise Edition, QLR Manager also provides for the design of custom input controls using the available HTML form elements which include check boxes, radio buttons, select lists and text areas. For more information about creating these controls, please see Input Controls.

There is a special query variable named [qlr_userid] (all lower case), {qlr_userid} for MS SQL Server, or **qlr_userid if using the older method, that substitutes the current logon ID as its value. A prompt is not produced for the User to input a value. This allows for the creation of queries where the results are based on the User ID running the query. For example, if customers were given a logon ID that was the same as their customer ID, the following query would limit them to seeing only their purchase order data:

select * from purchases_ord where cust_id='[qlr_userid]'

In those instances where the User ID does not map directly to the data (the User ID is not the Customer's ID), a database table can be created that maps one value to the other. A query could be written that uses a tables join between the reference table and the table(s) where the data resides. This would support the inclusion of the proper User ID information to achieve the desired results.

When queries of this nature are utilized in a menu, a robust reporting environment that produces customer specific information can be created with just a few queries.

When using the older method of **variable_name to declare query variables, the ** must have at least one other character following it to be recognized as a query variable. ** entered by itself will not be treated as a query variable.

Read only queries, security, and the @ character:

Using the Enterprise Edition, it is possible to set a user's profile so they cannot edit a query (read only) prior to running it, or even limit them to running queries from a menu without the ability to view the query being executed. If the query includes query variables, it is advisable to use the @ feature to specify a maximum input length for added security. The following illustrates why. The query below looks secure enough, as it prompts a customer for their customer ID and password. The password variable is even enclosed in quotes:

select * from invoices
where custid = [Customer_id]
  and password = '[Password]'

Suppose the Customer's input into the Password prompt is " ' or custid > 0 or custid =' ". If this value is supplied for the query variable, the password part of the query would end up looking like this:

and password = '' or custid > 0 or custid =''

This would have bypassed the use of the quotes around the password and would show every customer's invoice data. Assuming that the @ entries below represent the size of the fields in the invoice table, this query would provide a higher level of security:

select * from invoices
where custid = [Customer_id@7]
  and password = '[Password@8]'

This would have prevented the entry of the lengthy response of " ' or custid > 0 or custid =' ".

Obviously, for users that have edit access to a query, this would not restrict access to the data. They could merely edit the query. The use of the @ length option can also help in improving data input by trusted users.

With the Enterprise Edition, input controls can be created that function like query variables, but instead of being limited to a text input, they can be created to use any of the HTML form elements such as select lists, radio buttons, check boxes, etc. Above the query text area is an Edit Input Controls link that will launch a new window with the available selections required to create an input control, or retrieve and preview an existing control. The input control can be added to the query text in the same way a query variable is added, eg. [name of control], or {name of control} for Microsoft SQL Server. If input controls have already been defined for the logged on user, or other users have shared their input controls, they can be quickly accessed for use in the query by pressing the ctrl key. In supported browsers, a control selection list like the following will become visible:

Clicking  Add Control  with the cursor in the query textarea at the desired location will insert the selected control. For more information about creating and using these controls, please see Input controls.

Note: Multiple query variables or input controls can be used in the same query. QLR Manager will look for all instances of [bracketed] or **variable names when the query is executed and present all the specified input controls to collect the User input required to execute the Query. Query variables used with Microsoft SQL Server must always be enclosed in curly brackets, eg. {variable name}.

There is a special query a user can create named "user session" (all lower case). If a query exists with the name of "user session", the query's contents will be executed when the  Run Query  button is pressed prior to the query in the  Query  panel being executed. This allows for the setting of database commands, such as "alter session" commands in Oracle. An example of the contents of the user session query could be:

alter session set nls_date_format = 'DD-MON-YY HH24:MI:SS';

Since QLR Manager supports batch query processing, multiple commands can be placed into this query, separated by a semi-colon (;).

The user session query is loaded into memory when a user connects from the  Connect  panel. To create the user session query, it is suggested the query be authored and run to ensure it executes properly, then saved as "user session". The User must reconnect from the  Connect  panel to load the latest version of the user session query.

If errors are encountered when the user session query is executed, the error messages will be displayed at the top of the output in the  Report  panel.

The "Run with Layout" option allows the User to apply a layout to the data being selected in a query, formatting the report output prior to it being displayed. The list of available layouts will be based on layouts saved under the current connection ID, or others that have been shared by other users.

Note: If a stored query is retrieved and a layout exists with the same Owner and Name, it will become the default layout used to format the report. This provides an easy means to employ a query/layout naming convention that will automatically find the layout associated with a query.

If the column counts between the query and layout do not agree, the report will be displayed with the default formatting and the report title will inform the User that the column counts do not match.