The
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
The
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:
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
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
-- 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"
Using batch mode to create procedures:
The batch mode capability of the
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;
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.
| • | 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 If it is necessary to selectively ignore query variable tagging, it can be accomplished by using
the 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 |
| • | The |
| • | Clicking the |
| • | 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.
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.
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
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
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
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}.
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
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
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
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| • | 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 |
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.
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
1.9 MB
icon is visible beneath the
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
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 via the Layout's Report Column Display order settings.
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
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
The
| • | 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 myusers(first_name, last_name, age)
VALUES('jim', 'doyle', 25)
This could be executed from the
proc_init add_user; proc_exec;
Suppose the above procedure was changed to include variables to add different users. It would look like this:
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)
This could be executed from the
proc_init 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
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
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.