Runs a query against a Table in a database and, if results are returned, returns them as an Array.
See the majority of functions listed under the Table heading in the topic Common Functions.
The result can also be used in the Items property of the Data Table control.
Please also see How To: Work With Arrays, for more information about how arrays are constructed.
The QueryDataValues Function means multiple Database Connections to the same dataset can be avoided.
This Function will only re-evaluate itself when a value used within one of its arguments changes. Please see How To: Force Views From A Database To Refresh When The Data Has Changed. for more information.
QueryDataValues([Connection String],[SQL Command],[Username],[Password],[Include Column Headings])
Where
Connection String is the String containing the information required to connect to either an ODBC or SQL.
SQL Command is the Command to execute in SQL such as SELECT *
Username is the name of the user to connect to the Database as (optional).
Password is the Password of the user to connect to the database as (optional).
Include Column Headings is whether to include the Column Headings or not.
QueryDataValues supports both DSN and direct SQL connection strings.
Using the QueryData wizard select the type of connection and then follow the wizard to make the Connection String.
Example Connection Strings
| ODBC Connection String | Meaning |
|---|---|
| "Customers" | Customers is the System DSN that connects to the data source |
| Direct SQL Connection String | Meaning |
| "Server=MySQLServer\SQLEXPRESS;Database="Customers;" | MySQLServer is the name of the machine that hosts the SQL database. Use \SQLExpress if the database is one of the compact editions. Customers is the name of the database on the SQl Server to connect to. |
Is a valid SQL Query string that can filter, group and sort the data being queried. Note the DriveWorks QueryData wizard will build this query string.
| SQL Command | Meaning |
|---|---|
| "SELECT [CustomerName] FROM [Customers]" | CustomerName is the field in the Customers table to retrieve the records from. |
| "SELECT [CustomerName], [ContactName] , [City] , [State] , [Country] FROM [Customers]" | CustomerName, ContactName, City, State and Country are the fields in the Customers table to retrieve the records from. |
| "SELECT * FROM [Customers]" | * will retrieve all records in the Customers table |
| "SELECT DISTINCT [CustomerName], [ContactName] , [City] , [State] , [Country] FROM [Customers]" | Grouping Use SELECT DISTINCT to ignore duplicate records Use SELECT MIN to retrieve the smallest value Use SELECT MAX to retrieve the largest value Use SELECT COUNT to retrieve the number of rows that match certain criteria |
| "SELECT [ContactName] FROM [Customers] WHERE [CustomerName] = '" & CustomersReturn & "' " | Select Statement Conditions allow the data being retrieved to be filtered to minimize the amount of data being loaded into the Project CustomerName is the field in the database to compare to the value in the CustomerReturn user form control. |
| "SELECT [ContactName] FROM [Customers] WHERE [CustomerName] = '" & CustomersReturn & "' ORDER BY [ContactName] ASC" | Sorting allows the data being retrieved to be sorted in ascending or descending order ContactName is the field to sort the data by ASC - sort Ascending DESC - sort Descending |
The username to gain access to the database (optional)
| Username | Meaning |
|---|---|
| "MyUserName" | MyUserName is the Username that is allowed access to the database when using SQL Authentication |
The password to gain access to the database (optional)
| Password | Meaning |
|---|---|
| "MyPassword" | MyPassword is the password that is allowed access to the database when using SQL Authentication |
Whether the column headings should be included in the results. By default column headings will not be included.
| Include Column Headers | Meaning |
|---|---|
| TRUE | Will include the table headers in the result. |
| FALSE | Will not include table headers in the result. |
The example below combines the above examples to show how the Function should appear in the Rules Builder
| Example | Meaning |
|---|---|
| QueryDataValues("Customers","SELECT DISTINCT [CustomerName], [ContactName] , [City] , [State] , [Country] FROM [Customers]","","",FALSE) | Connects to the Customers DSN and retrieves distinct CustomerName, ContactName, City, State and Country records from the Customers table. Will not include the header rows in the result. |
| QueryDataValues("Server=MySQLServer\SQLEXPRESS;Database=Customers;","SELECT [ContactName], [City] , [State] , [Country] FROM [Customers] WHERE [CustomerName] = '" & CustomersReturn & "' ORDER BY [ContactName] ASC","","",TRUE) | Connects to the Customers SQL database and retrieves distinct ContactName, City, State and Country records from the Customers table where the CustomerName field equals the value in the CustomersReturn user form control. The retrieved data is sorted in ascending order according to the data in the ContactName field. Will include the header rows in the result. |
Use the QueryData Function Wizard to build the SQL command and then copy and paste into the QueryDataValues argument.
Sample data in database and being retrieved by a QueryDataValues function into a Variable (named DWVariableData for the purpose of the following examples)
| CustomerName | ContactName | City | State | Country |
|---|---|---|---|---|
| Amazon Warriors | Frank Doran | Strasbourg | Alsace | France |
| TT Tigers | Malcolm Savidge | Oldenburg | Lower Saxony | Germany |
| Super Jets | Anne Begg | Treviso | Veneto | Italy |
| Munich Marshalls | Helen Liddell | Munich | Bavaria | Germany |
| Super League | Gerald Howarth | Granada | Andalucia | Spain |
| Shelley Giants | Richard Shepherd | Le Mans | Sarthe | France |
| Boston Bulls | Graham Brady | Hanover | Lower Saxony | Germany |
| Cambridge Cats | Mark Tami | Toulouse | Haute Garonne | France |
| Ohio Rockers | Judy Mallaber | Arnhem | Gelderland | Netherlands |
| Notts Stags | Michael Weir | Marseille | Provence | France |
| Ruling Class | Alan Reid | Nantes | Pay de la Loire | France |
| Zone 10 | Howard Flight | Besancon | Franche-Comte | France |
| Lymm Lazers | Geoff Hoon | Lille Nord | Pas de Calais | France |
| Spectacular Players | Damian Green | Celle | Lower Saxony | Germany |
| English Stars | David Heyes | Una | Kreissuna | Germany |
Once the Variable DWVariableData has had the QueryDataValues Function applied to retrieve the data above further Functions can be created on Form Controls, Variables and Model, Drawing and Document rules that extract data from the Array.
Any Table Function that extracts data from an Array will require column or row index numbers to locate the data. The Index Numbers are in the same order as the data appears in the database. An understanding of the structure of the data being retrieved is required.
From the above sample data, which is presented exactly as it appears in the database, CustomerName is column 1, ContactName is column 2, etc.
| Function | Meaning |
|---|---|
| ListAllDistinct(DWVariableData,1) | Will list all records from column 1 of the DWVariableData Array, multiple values will be separated by the pipe | symbol for presenting in a List Control. Result Amazon Warriors|TT Tigers|Super Jets|Munich Marshalls|Super League|Shelley Giants|Boston Bulls|Cambridge Cats|OhioRockers|Notts Stags|Ruling Class|Zone 10|Lymm Lazers|Spectacular Players|English Stars |
| VLookup(CustomersReturn,DWVariableData,3,FALSE) | Will look for the value coming from the Form Control CustomersReturn, in the first column of the DWVariableData Array, and return the corresponding result from column 3 (City) when an exact match (FALSE) is found. Result when CustomersReturn is Munich Marshalls Munich |
| GetTableValue(DWVariableData,2,4) | Will extract the record from the second (2) row of the State column (4) from the DWVariableData Array. Result Lower Saxony |
See also