DriveWorks Pro 15: QueryDataValues [send feedback...]

Introduction

Runs a query against a table in a database and, if multiple results are returned, returns them as an array. DriveWorks will treat arrays of data returned from the QueryDataValues function as a table, meaning the name of the control or variable returning the array can be used in any DriveWorks function that has Table Name as an argument.

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.

Syntax

QueryDataValues( ["Connection String"] , ["SQL Command"] , ["Username"], [",Password"], [Include Column Headings])

Connection String

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.

SQL Command

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

Username (optional)

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

Password (optional)

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

Include Column Headers (optional)

Whether the column headings should be included in the results. By default column headings will not be included.

Include Column Headers

Meaning
TRUEWill include the table headers in the result.
FALSEWill not include table headers in the result.

Complete Examples

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.

What to do once the array has been passed into DriveWorks

Sample data in database and being retrieved by a QueryDataValues function into a Variable (named DWVariableData for the purpose of the following examples)

CustomerName

ContactNameCityStateCountry
Amazon WarriorsFrank DoranStrasbourgAlsaceFrance
TT TigersMalcolm SavidgeOldenburgLower SaxonyGermany
Super JetsAnne BeggTrevisoVenetoItaly
Munich MarshallsHelen LiddellMunichBavariaGermany
Super LeagueGerald HowarthGranadaAndaluciaSpain
Shelley GiantsRichard ShepherdLe MansSartheFrance
Boston BullsGraham BradyHanoverLower SaxonyGermany
Cambridge CatsMark TamiToulouseHaute GaronneFrance
Ohio RockersJudy MallaberArnhemGelderlandNetherlands
Notts StagsMichael WeirMarseilleProvenceFrance
Ruling ClassAlan ReidNantesPay de la LoireFrance
Zone 10Howard FlightBesanconFranche-ComteFrance
Lymm LazersGeoff HoonLille NordPas de CalaisFrance
Spectacular PlayersDamian GreenCelleLower SaxonyGermany
English StarsDavid HeyesUnaKreissunaGermany

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 teh 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|Ohio Rockers|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

A dynamic value (text or numeric) determined by a rule.


See also

How To: Troubleshoot SQL Connection

Table of Contents