Home Search

DriveWorks Pro 21
QueryDataValues

Send Feedback

QueryDataValues

Runs a query against a Table in a database and, if results are returned, returns them as an Array.

Although the data is returned as an array, the result can be used in most DriveWorks Functions that have Table or Table Name as an argument.

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.

Syntax

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.

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 StringMeaning
"Customers"Customers is the System DSN that connects to the data source
Direct SQL Connection StringMeaning
"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 CommandMeaning
"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)

UsernameMeaning
"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)

PasswordMeaning
"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 HeadersMeaning
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

ExampleMeaning
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)

CustomerNameContactNameCityStateCountry
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 the database, CustomerName is column 1, ContactName is column 2, etc.

FunctionMeaning
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

How To: Troubleshoot SQL Connection