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.

Executing queries

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.

2 commands executed.  Execution time 0.12 seconds.  
select * from customer where custnum = 14
 
custnum last
name
first
name
street city state zip e-mail
14 Cyprus Jethro 88 Jane Ave Horton MO 57990 billyray@juno.com
1

select * from orders where custnum = 14
 
ordnum custnum ord
date
payment
type
ship
street
ship
city
ship
state
ship
zip
1014 14 2002-10-28 11:04:51 MC        
1020 14 2002-12-18 06:29:33 MC        
1

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"

Multiple rows of -- no_result messages can be used to build up complex no result messages. Query variable values can also be included:

-- no_result <p>No records found for [customer ID]</p>
-- no_result <p>Please try a different value.</p>

A global "no result" message can be added to the qlr_info table. The keyword reference is no_result. If present, it will be applied to all select queries that do not produce any results. If the query itself contains a --no_result entry, it will override the global messaging.

Obtaining query execution statistics:

To display statistics associated with the execution of a single SELECT query, the text query_stats can be added after the query text:

select first_name,last_name from customer where last_name = "asher";
query_stats

Note: The query must end with a semi-colon and the query_stats text is not commented text.

To display how long it takes to execute individual queries in a batch query, -- qlr_query_time can be added to the top of the query:

-- qlr_query_time
select first_name,last_name from customer where last_name = "asher";
select * from customer where last_name='Jones';
update customer set zip_code='13760' where city='Endwell' and state='ny';

Using batch mode to create procedures:

The batch mode capability of the  Query  panel can also be used to create database engine procedures. For example, when using MySQL version 5.0 or greater, the following procedure can be defined:

delimiter !
create procedure circle_area (in r double, out a double)
begin
set a = r * r * pi();
end
!>
delimiter ;

Since the procedure itself contains a semi-colon delimiter, a new temporary delimiter needed to be defined. When defining a new delimiter, it must be limited to one character. A database error message associated to the delimiter definition may be generated, but the procedure is still created.

To access the above MySQL procedure, the following could be entered into the query text area after the procedure:

call circle_area(22, @a);
select @a;
Query Explain Plan

When certain database engines are being used, QLR Manager will display an  Explain Query  button below the query text area. This is only available when not using an ODBC connection.

The Explain Plan will provide the User with information about query performance. It is up to the User to understand how to interpret the Explain Plan for their database engine. As of this version, QLR Manager supports Explain Plans for DB2, MySQL, Oracle, PostgreSQL and SQLite3.

In order to support Explain Plans in DB2, the proper tables need to be created. This can be accomplished by running the following procedure:

CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', CAST (NULL AS VARCHAR(128)), CAST (NULL AS VARCHAR(128)))

This will install the required tables in the SYSTOOLS schema (e.g. SYSTOOLS.EXPLAIN_STREAM).

Query variables

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:

Please provide values for these variables:
 
Variable Value
part number
minimum quantity

     
 

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.

A few things to note about query variables:
 •  

In those rare instances where the query may contain strings of text that look like a query variable, but should not be treated as a query variable, check the Ignore query variables checkbox found under the  Reset Query  button. The query text will be executed "as is".

If it is necessary to selectively ignore query variable tagging, it can be accomplished by using the --ignore_query_variables_on and --ignore_query_variables_off comments. The following example will treat [Select_date] as a query variable and allow for the proper interpretation of a regular expression in the query text:

SELECT *
FROM mydb.mytable
WHERE date = [Select_date]
--ignore_query_variables_on
AND name REGEXP 'sms-[0-9][0-9][0-9]'
--ignore_query_variables_off
 •  If the same query variable name is used more than once, only one input prompt will be generated for the User to enter data.
 •  The use of @5 on the end of the minimum_quantity variable specifies that the maximum input size for that variable is 5 characters. A two digit number, such as @43 is also acceptable. When no "@" is used, the default input size is 20 characters, with no maximum input length.
 •  The value of the query variable is remembered throughout the User's session. If the same query variable is encountered again, the input field will be primed with the value that was last entered.
 •  There is an exact replacement of the query variable name with the input value. In the example above, the '[part_number@12]' variable is surrounded by quotes in the query, since the partnum field is a character field. Had [part_number@12] not been surrounded in quotes, the User would have been required to place the quotes in the input field when they provided the value for this variable.
 •  When underscores ( _ ) are used in defining query variables, they will be replaced with a blank space when presented as the input label in the query variable data entry panel.
 •  Clicking the  Cancel  button will return the User to the  Query  panel.
 •  The  Reset  button will reset the input value fields to blank, or if predefined input controls were used, their original defaults.
 •  Clicking the  Continue  button will run the query after performing the variable value substitution.
 •  Note: The User supplied value for query variables can be referenced in titles, footers and breaks in a report by simply including the query variable name (without the @length value) in the appropriate section of the Layout panel. For example, to include the minimum quantity variable from above, place [minimum_quantity], or the older method of **minimum_quantity, in the desired location within the title, footer or break. This technique can be used to reference all query variables for use in the report layout.

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.

Changing the query variable prompt:

The -- query_var_prompt keyword and associated text can be used with a SELECT query prior to the select statement. This keyword allows the query author to alter the text that is displayed at the top of the Query Variable input panel. This option is only valid if query variables are found within the query text. Following is an example of the keyword usage:

-- query_var_prompt <b style="color:#FF0000">Please provide the Product ID</b>
select first_name,last_name from inventory where prodid = '[product id]';

Overriding the query variable delimiters:

The beginning and ending query variable delimiters within a query can be overridden when necessary. This is accomplished by adding a QLR comment to the query. The comment contains the keyword of query_var_brackets, followed by the desired beginning and ending characters. Please note that these two characters must be different characters in order to work properly. Here are two acceptable examples:

-- query_var_brackets <>
-- query_var_brackets ` ~

The following query would be able to work properly, prompting the User to provide the cust num on the query variable input panel, and inserting the text of [example text] into the database for that customer number.

-- query_var_brackets $ #
update customer set address_line1 = '[example text]' where custnum= $cust num#

Note: Only one set of replacement brackets can be specified per query. If a query contains multiple SQL statements, only one instance of query_var_brackets can be used.

Dynamically assigning a Layout:

The Report Layout can be dynamically assigned from within the query using the use_layout keyword. It supports the use of embedded query variable names. This allows layouts to be dynamically assigned based on values provided for the query variables as the query is executed.

-- use_layout patrick.state tax [state]
select * from taxdb.taxes where state='[state]' order by 1,2,3

If the value provided for the [state] query variable prompt was AZ, then the Layout applied to the query results would be patrick.state tax AZ.

The typical format to reference an existing layout is owner.name. If no period delimiter is found, the currently connected User ID is used as the owner. If a layout was specified using the  Query  panel's Run with Layout option, or it was assigned in a Macro or Menu item, it will be overridden by the -- use_layout value when present.

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.

Query variables with input controls:

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:

access input controls

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

If the User wishes to use an input control that was created and saved under a different User ID, the input control must be prefixed with the owning User ID followed by a period. For example, [joe01.account month] will find the input control named "account month", that belongs to User ID "joe01".

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}.

The User Session query

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';

Below is a helpful query for DB2 to avoid always having to prefix table names with the current schema:

SET CURRENT SCHEMA = '[qlr_userschema]';

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.

Using an existing layout

Run with Layout

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.

In addition to layouts created by users, there are two options that are listed under a special Owner named *QLR Manager*.
 •   The first is called "Based On Query", which is the default selection under most circumstances. When selected, no layout is applied and the report format is based upon the attributes of the data selected in the query.
 •  The other option is called "Current layout". This uses the existing layout attributes as found in the  Layout  panel. If data in the  Layout  panel does not yet exist, this choice will not be displayed.

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.

Query performance

This is an overview of how QLR Manager behaves when a SELECT query is executed.

First, QLR Manager tracks the amount of time it takes to execute the query. There is nothing that can be done by QLR to speed up query execution. This takes place within the database engine.

Next, an array of data is cached. The amount of data is dependent upon two factors. The first is how long it took the query to execute. QLR will spend the greater of 2 seconds or 30% of the query execution time building the data array. Longer executing queries are given more time to populate the data array. The second factor is the amount of memory that has been defined for data caching. The amount of memory is determined when the User first connects to QLR Manager. The application checks the PHP memory_limit setting and then performs a small "speed test" of the server. The maximum amount of memory that can be allocated is determined by the qlr.ini setting called cacheMB.

If the Report data cache Report data cache1.9 MB icon is visible beneath the  Log off  button, this is the visual queue for the User that this feature is available. When available, there is no need to execute the query a second time to change the sort order of the data. This can be accomplished in the  Layout  panel using the Display order entry in the Report Columns section. The calculated cache size can be overridden by adding a comment to the start of the query, such as -- set_cache 15, but can be no larger than the value of the cacheMB setting in the qlr.ini file. It would be rare that the data array memory limit would be reached with the initial query execution, unless it is a very long running query. It would be more common that the time limit stops the data caching process. The goal of QLR Manager is to quickly display the initial results of the query to the User, and at the same time store data for further manipulation. It is a balancing act, as QLR Manager does not know the intentions of the User. It could be that the User takes a quick look at the data that was generated and decides to change the query and execute it again.

Based on the amount of bytes used to build up the initial data array, and the total rows of data in the report, QLR will estimate if it can add all of the remaining report rows to the data array. If so, and the query is not being run with an existing Layout, the Auto arrange checkbox found in the Report Body section of the  Layout  panel will be checked. This process will not change the Auto arrange setting for saved Layouts.

There are several Layout actions that will trigger QLR Manager to complete the data array: Defining a Table or Pivot layout, Auto arrange is checked and there are either Breaks or Groups defined, or the User requests to sort the report data using the Display order entry in Report Column section of the Layout.

If the Auto arrange option is checked, and the Layout contains either Break or Group Actions, then QLR Manager will fetch the rest of the rows of report information to complete the data array. Once fetched, the User can page between the  Layout  and  Report  panels to quickly apply changes to the Layout, and see the resulting impact to the Report. The Auto arrange feature will automatically order the report columns and sort the report data based on the Break and Group settings.

The tradeoff is that the larger the data array becomes, the slower QLR Manager will respond. This is very dependent on the server's processing speed and the amount of memory allowed in the php.ini file's memory_limit setting. If the cache size of the data array is defined too large, the PHP environment in which QLR Manager executes may run out of memory.

An example of setting the amount of data to cache within a query:

-- set_cache 15
select po_num, po_item_num, amount from purchase_order_detail

Executing SQL Server stored procedures

The  Query  panel can be used to execute SQL Server stored procedures. The execution is similar to a batch query. The procedure is first initialized, values are then assigned to variables that are found within the procedure, and then finally, the procedure is executed.

The execution is accomplished by using several QLR Manager keywords:
 •   proc_init [procedure name]
 •   proc_bind [variable name] | [variable value] | [sql server variable type]
 •   proc_exec

For example, suppose a stored procedure called add_user was created with the following SQL Server code. In this first example, the procedure does not contain any variables:

CREATE PROC add_user

AS

INSERT INTO dbo.myusers(first_name, last_name, age)
VALUES('jim', 'doyle', 25)
Notice the "dbo." prefix. This will allow you to find the proc after you create it.

This could be executed from the  Query  panel by entering:

proc_init add_user;
proc_exec;

or as

exec sa.dbo.add_user;   -- assuming the proc was created by the sa ID

Suppose the above procedure was changed to include variables to add different users. It would look like this:

CREATE PROC dbo.add_user

@first varchar(20),
@last  varchar(20),
@age   tinyint

AS

INSERT INTO myusers(first_name, last_name, age)
VALUES(@first, @last, @age)

This could be executed from the  Query  panel as follows:

proc_init sa.dbo.add_user;
proc_bind first | Nick | sqlvarchar;
proc_bind last | Zhang | sqlvarchar;
proc_bind age | 35 | sqlint2;
proc_exec;

In this second example, the procedure contains variables for the first name, last name, and the person's age. The proc_bind keyword has been used to define the values to be assigned to each of these variables when the procedure is executed. The first argument is the name of the variable found in the procedure. It is then followed by the pipe "|" character, which is used as a delimiter to separate each argument. The second argument is the value to be assigned to the variable. It is then followed by the "|" delimiter as well. The final argument is an indicator that tells SQL Server what sort of data is being passed into the procedure. Valid data type entries are:

 •   SQLBIT for bit data
 •   SQLCHAR for nchar data
 •   SQLFLT8 for decimal, money, smallmoney, numeric or real data
 •   SQLINT2 for int and tinyint data
 •   SQLINT4 for datetime, smalldatetime or timestamp data
 •   SQLVARCHAR for binary, image, nvarchar, sql_variant or varbinary data
 •   SQLTEXT for ntext

The use of query variables is also permitted. The text in the  Query  panel can also be entered as follows. The person executing the query would then be prompted to enter the values for each of the variables.

proc_init add_user;
proc_bind first | {first name} | sqlvarchar;
proc_bind last | {last name} | sqlvarchar;
proc_bind age | {age} | sqlint2;
proc_exec;

A proc may also contain a query to output information when it is finished executing. In the example below, the query select * from myusers has been added to the end of the procedure.

CREATE PROC add_user

@first varchar(20),
@last  varchar(20),
@age   tinyint

AS

INSERT INTO myusers(first_name, last_name, age)
VALUES(@first, @last, @age)

select * from myusers

When the above is executed, the contents of the query select * from myusers will be displayed in the  Report  panel. The output can be formatted by applying a layout using the Run with Layout option in the  Query  panel. However, this is not the optimal way of formatting the output since the  Layout  panel cannot be accessed to apply layout characteristics to a batch query.

The suggested method of generating a report after a procedure has been executed is to suppress the messages generated by the proc commands and end the batch job with the query that will generate the desired report, such as:

-- suppress_on
proc_init add_user;
proc_bind first | {first name} | sqlvarchar;
proc_bind last | {last name} | sqlvarchar;
proc_bind age | {age} | sqlint2;
proc_exec;
-- suppress_off
select * from myusers;

Using this approach will execute the stored procedure and provide full control in creating a formatted report.