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

DbQuery

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

Syntax

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

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 is a valid SQL Query command to execute, for example:

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

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

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 headered 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 headered 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 headered 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

CustomerName

ContactNameCountry
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


Table of Contents