This is the User Manual for the QueryDB web service. The document explains the operations available, as well as the possible filtering and sorting operations.
Note that all of the examples in this document will derive their data from the table in Figure 1.
Figure 1
Configuration
Before you can begin using QueryDB to query a SQL database, the database being queried needs to be added to the configuration/qdabra.dbxl/enumdb/databases node in web.config. The SQL server indicated during installation (where QdabraDBXL and QdabraSamples are located) is already configured to use QueryDB, but any additional databases must be added individually, like this: <database alias="DB_Alias" name="SampleDB" server="default" />.
Accessing a remote database
In order to allow QueryDB to access a database that lies on a different server, you must add server and database aliases to the web.config file. Look for instructions within the configuration/qdabra.dbxl/enumdb/servers and configuration/qdabra.dbxl/enumdb/databases nodes.
The database alias must be placed within pound signs when using QueryBuilder to build the queryxml. Note that you must also provide the requester machine read access in the remote database.
QueryDB methods
There are four methods within QueryDB, each of them allowing the user to query the database in different ways.
1. GetColumnsXMLStrQuery
GetColumnsXMLStrQuery returns an array of all columns of the specified rows of the specified table. The user must provide an xml query, generated by Qdabra’a Query Builder, which details database, table, retrieved columns, sorting and filtering.
Depending on the query generated by Query Builder, the user can enable rules within the form in order to use runtime data to affect the query. Of course the simple scenario is also possible, where Query Builder generates a static xml query.
2. GetDropdownEntries
GetDropdownEntries allows a user to retrieve a list of values and easily bind them to a dropdown control. The user must provide values for database, table and valueColumn. By default, the values will be sorted according to valueColumn in ascending order, or by displayColumn if provided. Besides the optional displayColumn, the user can also indicate sortOrder (ascending or descending) and a filterXML. Here is the full information regarding the parameters:
To illustrate the use of this method, we will query our sample data to list the ProjectName column, sorted by Estimated_length_months in descending order. The parameters required for this operation are seen in Figure 2 and result of that query is seen below, in Figure 3. Note that if no sortColumn and no sortOrder are provided, the method will automatically sort by valueColumn, ascending.
Figure 2
Figure 3
The filtering parameter, filterXML, will be discussed later on in this document. Note that, because this method accepts parameters for sorting in the Data Connection Wizard, the user must not use a sorting operation within the filterXML.
3. GetColumnsXMLQuery
GetColumnsXmlQuery is meant for use in code exclusively and behaves in the same way as GetColumnsXmlStrQuery.
4. DescribeDatabaseSchema
DescribeDatabaseSchema scans the schema in the specified connection string and returns an XML Document describing it. The dataSource parameter should be a connection string as passed to the SqlConnection constructor the .NET framework.
XML Query
When using GetColumnsXMLStrQuery, the user can perform fine-grained filtering by using a XML Query, which will have the following format:
The simplest query that can be used is one that will pull down an entire table from a given database. The format for this query is <query database='<database name>' table='<table name>'></query> and the result is seen in Figure 4.
Figure 4
While more fine-grained xml queries can be generated manually, Qdabra provides the Query Builder tool, which allows the user to easily generate a desired xml query. The Query Builder tool will be discussed in an upcoming section.
maxrows
The maxrows parameter allows the user to limit the number of rows that are returned by the query. This is especially useful when a query might return a large amount of results. The format used for this is <query maxrows=’x’>, where x is the number of rows to be returned.
Filtering
There are various types of comparison operations available for the <filter> node of queryXML.
a. Comparison operators: lt, le, eq, ne, ge, gt
The first set of comparison operators -namely <lt>, <le>, <eq>, <ne>, <ge> and <gt>- all use similar formats. The comparisons can take place between a column and a value, or between a column and another column. The examples below will illustrate some of the uses of these operators. Each sample will be accompanied by a table showing the result.
<lt>
<!—- lt stands for “less than”. In the example
below we compare a column and a value. -->
<column name="Actual_length_months"/>
<value type="int">12</value>
</lt>
<le>
<!—- le stands for “less than or equal”. In the
example below we compare a columns and a value. -->
<column name="Actual_length_months"/>
<value type="int">18</value>
</le>
<eq>
<!—- eq stands for “equal”. In the example below
we compare two columns. -->
<column name="Actual_length_months"/>
<column name="Estimated_length_months"/>
</eq>
<ne>
<!—- ne stands for “not equal”. In the example
below we compare a column and an alphanumeric value. -->
<column name="Client"/>
<value type="varchar">Client1</value>
</ne>
<ge>
<!-- ge stands for “greater than or equal”. In
the example below we compare a column and a value. -->
<column name=" Actual_length_months"/>
<value type="int">12</value>
</ge>
<gt>
<!-- gt stands for “greater than”. In the example
below we compare a column and a value. -->
<column name=" Actual_length_months"/>
<value type="int">18</value>
</gt>
b. Comparison operator: in
The use of <in> is slightly different from the above set of comparison operators. This operator requires the user to specify a column and at least one value. It then selects rows that contain the value(s) listed in the specified column.
The following example illustrates the XML query when using <in>:
<in>
<column name="Client"/>
<values>
<value>Client1</value>
<value>Client2</value>
<value>Client3</value>
</values>
</in>
c. NULL values
One useful and common filtering operation involves the search for NULL values in your database. In order to do this, we will need to use <ne> and <eq>.
NULL values can be excluded, as shown below:
<ne>
<column name="Client"/>
<value nil="true"/>
</ne>
NULL values can also be included:
<eq>
<column name="Client"/>
<value nil="true"/>
</eq>
d. Using LIKE and wildcards
The <like> filter can search for patterns in the data columns by using wildcard characters:
<like>
<column name="ColumnName"/>
<value>Pattern</value>
</like>
‘Pattern’ is the specific string of characters to search for in the column “ColumnName” and can include the following four wildcard characters.
- % will search for any string of characters. For example, the pattern %1 will yield the following result:
- A pattern consisting only of % will yield all results in the queried table.
- An underscore (_) represents any single character. The example below shows the result obtained if we filter using ‘Project_’.
- The square brackets can denote any single character within the specified range. For example, we can search based on a set; [1-3] would be equivalent to [123]. When the pattern is %[1-2] we will obtain all Client entries that send with the numbers 1 or 2.
- The square brackets in combination with the carrot (^) will allow the user to search for any single character not within the specified range. To illustrate this, we implement the opposite filter (<value>%[^1-2]</value>) to the one shown above.
e. Using CONTAINS
The <contains> filter allows you to perform keyword searches in your database, in the same way you should use it in a SQL statement (see http://msdn2.microsoft.com/en-us/library/ms187787.aspx). You cannot use <contains> on columns that are not full-text indexed. Full text searches using allow you to also use the NEAR operation, as well as searching for phrases enclosed between double quotes, and wildcards (*) for prefix searches.
Finally, note that performing OR/AND operations within a <contains> filter produces better performance than performing the same operation across two filters. For example, we will obtain the same result with either of the filtering operations below, but the first will give you better performance.
Option 1:
<or>
<contains>
<column name="Client"/>
<value>Client1</value>
</contains>
<contains>
<column name="Client"/>
<value>Client2</value>
</contains>
</or>
Option 2:
<contains>
<column name="Client"/>
<value>Client1 OR Client2</value>
</contains>
Sorting
QueryDB’s GetColumnsXMLQuery and GetColumnsXMLStrQuery allow users to sort the results. When using multiple entries, the results will be sorted by each of the columns listed, in the order listed. Sorting by:
<sort>
<column name="Actual_length_months" order="ASC"/>
<column name="Estimated_length_months" order="ASC"/>
</sort>
Yields:
