Home Search

DriveWorks Pro 21
DbQuery

Send Feedback

DbQuery

Runs a query against a table in a database, returning the result as a headed table.

When this function is used in a variable or control it will be executed as soon as a specification is started.

If you need to execute the function at a specific time consider using a Specification Macro.

If you need the results to be refreshed (because of user selections on the form) please see the topic How To: Force a data refresh when data has changed.

Syntax

DbQuery( ["Connection String"], ["SQL Command"], ["User Name"], ["Password"])

Where:

Connection String

Connection String is the string that will connect to the required database:

DBQuery supports both DSN and direct SQL connection strings.

If using ODBC, a valid data source name is required, for example:

ODBC Connection String
"SystemDSNName"

If using SQL Server, a valid SQL connection string is required, for example:

SQL Connection String
"Server= ServerName;Database= DatabaseName"

SQL Command

SQL Command is a valid SQL Query command to execute, for example:

SQL SELECT Command
"SELECT FROM Country WHERE Currency = 'Dollar'"

User Name

User Name (Optional) is the user name for the database

Password

Password (Optional) is the password for the database

Examples

RuleMeaning
DbQuery("Server=MYSERVER;Database=Customers;",@"SELECT DISTINCT ([Country]) FROM [Customers] ORDER BY [Country] ASC")Will connect to the SQL Server MYSERVER and the database Customers. A headed table of country names, excluding duplicates will be retrieved, in ascending order. The retrieved table will be: {"Country";"France";"Germany";"Italy";"Spain";"Netherlands"}
Querying Multiple Columns

The query string alters slightly when querying multiple columns.

Where a single Select column contains parenthesis around the field name, multiple Select columns do not require these.

Using the example in the table above:

SELECT DISTINCT ([Country])

Will become:

SELECT DISTINCT [Country],[ContactName]

So the full rule will be:

DbQuery("Server=MYSERVER;Database=Customers;",@"SELECT DISTINCT [Country],[ContactName] FROM [Customers] ORDER BY [Country] ASC")

Example Outcomes

ServerDatabase NameQueryOutcome
MYSERVERCustomersSELECT ([CustomerName]) FROM [Customers] WHERE [Country] = 'Germany' ORDER BY [CustomerName] ASCWill connect to the SQL Server MYSERVER and the database Customers. A headed list of customers from Germany will be retrieved, in ascending order. The retrieved list will be: {"CustomerName";"Boston Bulls";"English Stars";"Munich Marshalls";"Spectacular Players";"TT Tigers"}
MYSERVERCustomersSELECT ([ContactName]) FROM [Customers] WHERE [CustomerName] = 'Cambridge Cats' ORDER BY [ContactName] ASCWill connect to the SQL Server MYSERVER and the database Customers. A headed list of contact names will be retrieved, for the Cambridge Cats customer, in ascending order. The retrieved list will be: {"ContactName";"Howard Flight";"Mark Tami"}

Example Data

Customers Table

CustomerNameContactNameCountry
Amazon WarriorsFrank DoranFrance
TT TigersMalcolm SavidgeGermany
Super JetsAnne BeggItaly
Munich MarshallsHelen LiddellGermany
Super LeagueGerald HowarthSpain
Shelley GiantsRichard ShepherdFrance
Boston BullsGraham BradyGermany
Cambridge CatsMark TamiFrance
Ohio RockersJudy MallaberNetherlands
Notts StagsMichael WeirFrance
Ruling ClassAlan ReidFrance
Cambridge CatsHoward FlightFrance
Lymm LazersGeoff HoonFrance
Spectacular PlayersDamian GreenGermany
English StarsDavid HeyesGermany