DbQuery (DriveWorks Documentation)

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.

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

Rule	Meaning
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")

Server	Database Name	Query	Outcome
MYSERVER	Customers	SELECT ([CustomerName]) FROM [Customers] WHERE [Country] = 'Germany' ORDER BY [CustomerName] ASC	Will 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"}
MYSERVER	Customers	SELECT ([ContactName]) FROM [Customers] WHERE [CustomerName] = 'Cambridge Cats' ORDER BY [ContactName] ASC	Will 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"}

Customers Table