The following functions have been added to DriveWorks 12.
Runs a query against a table in a database, returning the result as a headered table.
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:
"SystemDSNName"If using SQL Server, a valid SQL connection string is required, for example:
"Server= ServerName;Database= DatabaseName"
SQL Command is a valid SQL Query command to execute, for example:
"SELECT FROM Country WHERE Currency = 'Dollar'"
User Name (Optional) is the user name for the database
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 headered table of country names, excluding duplicates will be retrieved, in ascending order. The retrieved table will be: {"Country";"France";"Germany";"Italy";"Spain";"Netherlands"} |
| 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 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"} |
| 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 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"} |
CustomerName | ContactName | Country |
|---|---|---|
| Amazon Warriors | Frank Doran | France |
| TT Tigers | Malcolm Savidge | Germany |
| Super Jets | Anne Begg | Italy |
| Munich Marshalls | Helen Liddell | Germany |
| Super League | Gerald Howarth | Spain |
| Shelley Giants | Richard Shepherd | France |
| Boston Bulls | Graham Brady | Germany |
| Cambridge Cats | Mark Tami | France |
| Ohio Rockers | Judy Mallaber | Netherlands |
| Notts Stags | Michael Weir | France |
| Ruling Class | Alan Reid | France |
| Cambridge Cats | Howard Flight | France |
| Lymm Lazers | Geoff Hoon | France |
| Spectacular Players | Damian Green | Germany |
| English Stars | David Heyes | Germany |
Gets the domain name portion of an email address.
EmailGetDomainName(Email As String)
Where:
Email must be a valid email address.
| Rule | Meaning |
|---|---|
| EmailGetDomainName("fredbloggs@hotmail.com") | Will return the domain name from the given email address i.e. "hotmail.com". |
Gets the user name portion of an email address.
EmailGetUserName(Email As String)
Where:
Email must be a valid email address.
| Rule | Meaning |
|---|---|
| EmailGetUserName("fredbloggs@hotmail.com") | Will return the user name from the given email address i.e. "fredbloggs". |
Resolves a file path relative to the group content folder. Also supports prefixes specified in the FsGetFullPath function.
FsGetFullPathGroupContent(Group Content As String)
Where:
Group Content is the file path to get the full path for.
| Rule | Meaning |
|---|---|
| FsGetFullPathGroupContent("Images") | Will return the full path to the images folder located within the Group Content folder i.e. "D:\DriveWorks\Group Content\Images". |
Checks to see if its first argument is an empty value. If the value isn't an empty value it is returned as-is, otherwise a secondary value is returned.
IfEmpty(Primary Value As String, Alternative Value As String)
Where:
Primary Value is the value that should be tested.
Alternative Value is the value which will be returned if the first value is empty.
| Rule | Meaning |
|---|---|
| IfEmpty(ColorReturn,"Red") | Will return "Red" when the value coming from the control Color is empty. |
Checks to see if its first argument is an error. If the value isn't an error it is returned as-is, otherwise a secondary value is returned.
IfError(Primary Value As String, Error Replacement Value As String)
Where:
Primary Value is the value to check for an error.
Error Replacement Value is the value to use if the primary value is an error.
| Rule | Meaning |
|---|---|
| IfError(DWVariableLength,2475) | Will return 2475 when the value coming from the variable Length is an error. |
Checks to see if its first argument is a boolean. If the value is a boolean it is returned as-is, otherwise a secondary value is returned.
IfNonBoolean(Primary Value As Boolean, Alternative Value As String)
Where:
Primary Value is the value whose type should be tested.
Alternative Value is the value which will be returned if the type of the first value fails the type test.
| Rule | Meaning |
|---|---|
| IfNonBoolean(OptionRequiredReturn,FALSE) | Will return FALSE when the value coming from the control OptionRequired is not a boolean. |
Checks to see if its first argument is a number. If the value is a number it is returned as-is, otherwise a secondary value is returned.
IfNonNumber(Primary Value As String, Alternative Value As String)
Where:
Primary Value is the value whose type should be tested.
Alternative Value is the value which will be returned if the type of the first value fails the type test.
| Rule | Meaning |
|---|---|
| IfNonNumber(DWVariableLength,0) | Will return 0 when the value coming from the variable Length is not a number. |
Returns TRUE if the given value is a boolean and FALSE if not.
IsBoolean(Value As Boolean)
Where:
Value is the value to check.
| Rule | Meaning |
|---|---|
| IsBoolean(OptionRequiredReturn) | Will return TRUE when the value coming from the control OptionRequired is TRUE or FALSE otherwise it will return FALSE. |
Returns TRUE if the given value is either an empty value or a zero-length piece of text.
IsEmpty(Value As String)
Where:
Value is the value to check.
| Rule | Meaning |
|---|---|
| IsEmpty(ColorReturn) | Will return TRUE when the value coming from the control Color is empty. |
Returns TRUE if the given value is not a boolean and FALSE if it is.
IsNonBoolean(Value As String)
Where:
Value is the value to check.
| Rule | Meaning |
|---|---|
| IsNonBoolean(OptionRequiredReturn) | Will return TRUE when the value coming from the control OptionRequired is not a Boolean value. |