Database Query

The Database module will access and/or update information in databases. The information retrieved will then be available in other parts of the script. Data can be retrieved and updated in the following data sources:

• Access 
• Excel 
• SQL Server
• Oracle
• MySQL
• any ODBC data source
• FoxPro
• dBase
• Paradox
• any ISAM data source

If this module is used in the script then Microsoft's MDAC database drivers are should also be installed on the system. These are available from the VoiceGuide download page at http://www.VoiceGuide.com/

 

Module Parameters

Database or ODBC Source	Database filename, or the data source name (DSN) of an ODBC data source.
Connect String	Used if any special connect parameters are needed to connect to the selected database or ODBC data source.
Query Statement	The SQL statement to run, or the rage of cells to retrieve from Excel. Any valid SQL statement can be used, Excel cell rage query syntax is: SheetName$StartCell,EndCell

Result Variables can be used in all of the above fields.

 

Connect String

The Connect String argument is expressed in two parts: the database type, followed by a semicolon (;) and the optional arguments. You must first provide the database type, such as "ODBC;" or "Excel 5.0;". The optional arguments follow in no particular order, separated by semicolons. One of the parameters may be the password (if one is assigned). For example:

Excel 5.0;PWD=mypassword

Some examples illustrating the different settings which can be used  to access various data sources.

Access database :
Database	C:\Clients.mdb
Connect String
Query	SELECT PinNumber FROM Customers WHERE CustomerID='0045'
Password protected MS Access :
Database	C:\Clients.mdb
Connect String	;PWD=mypassword
Query	SELECT PinNumber FROM Customers WHERE CustomerID='0045'
Excel file :
Database	C:\My Documents\Roster.xls
Connect String
Query	Sheet1$B3,C5
Password protected Excel file :
Database	C:\My Documents\Roster.xls
Connect String	Excel 8.0;PWD=mypassword
Query	Sheet1$B3,C5
Access database (ODBC) :
Database	Products
Connect String	ODBC;DRIVER={Microsoft Access Driver (*.mdb)}
Query	SELECT UnitsInStock, UnitPrice FROM Goods WHERE ProductID='$RV[Get Product Code]'
ODBC Data Source (Oracle) :
Database	Production
Connect String	ODBC;DSN=Production;UID=user;PWD=password;SRVR=t:machine:p66;
Query	SELECT PinNumber FROM Customers WHERE CustomerID='0045'
ODBC Data Source (MSSQL) :
Database	Northwind
Connect String	ODBC;UID=sa;PWD=;
Query	UPDATE Products SET UnitPrice=$RV[NewPrice] WHERE ProductID=$RV[GetProductId];

VoiceGuide uses DAO 3.6 to connect to databases, with all ODBC data sources using an ODBCDirect Workspace, and all other databases using a Jet Workspace. For more information on supported databases and Connect Parameters syntax used for them please consult Microsoft's DAO 3.6 reference manual (and the OpenDatabase() function in particular)

A step by step guide to setting up your ODBC data source is provided here.

More examples of ODBC connect strings can be found here: http://www.connectionstrings.com

 

ODBC Connect Strings

Some examples of the ODBC Connect Strings are above, and there should be some help available in the your databases Help file. Many users have found that they need to include the name of the driver in the connect string – this is the same name as is displayed in the column “Driver” to the right of your Data Source in the “ODBC Data Source Administrator”.

Eg:  When working with Access via ODBC the connect string would be:

ODBC;DRIVER={Microsoft Access Driver (*.mdb)}

 

Query Results

The results of the database query are accessible using Result Variables. The following Result Variables can be used to access data retrieved by a Db Query module:

$RV[ModuleTitle_ColumnIndex_RowIndex]

If more then one item was requested in the query, the Column Index can be used to access the individual items in each retrieved row.

eg: module LookupTelFax uses a query: SELECT Tel, Fax FROM Customer WHERE CustId='$RV[Get CustId]' . We can use $RV[LookupTelFax_1_1] to access the contents of the Tel column and use LookupTelFax_2_1 to access he contents of the Fax column for customer 1. We can use $RV[LookupTelFax_1_2] to access the contents of the Tel column for customer 2, etc.

ModuleTitle_RowCount

Will return the number of entries which were returned.

 

Paths:

Three paths can be selected from this module:

True	Some data was retrieved.
False	No data was retrieved.
Timeout	Data retrieval took too long and a specified Timeout path is taken.

 

Microsoft Access

Access example 1 : Retrieve data

Lets have an application where after entering the product code, the caller would like to know how many items are available in stock and what is their price. Lets look at an example database Products.mdb:

Looking at this database we see that we have a database called "Products", which has a table called "Goods" and in that table we see 5 items, along with their stock count and price.

To retrieve the "Units In Stock" and "Unit Price" fields for a particular Product ID we would run a following SQL statement:

SELECT UnitsInStock, UnitPrice FROM Goods WHERE Product ID='$RV[Get Product Code]'

Take note that the actual field names used by Access can be different from the displayed column headers. Open the table in Design mode to see what the actual field names are.

The module's Properties page would look like this:

The number of units in stock can now be accessed using Result Variable $RV[Access Query_1_1] and the Price can be accessed using the Result Variable $RV[Access Query_2_1]

When playing back the decimal number, the "Amount Cents, Decimal Point" setting should be used.

This example can be found in the "..\scripts\Customer Enquiry" directory.

 

SQL Commands

For a complete reference SQL see the Access Help files, or other Database reference books. The few examples below are included to demonstrate simple applications.

SQL Example 1 : Retrieve Data

As an example, lets have an application where after entering the postal code, the caller would like to know how many clients live in this postal code area, and then be told their telephone number, and amounts which they owe.

Suppose the caller has entered the postal code code in a Get Numbers module titled 'Ask for ZIP'. We can now use a Result Variable to use this value in a query.

The settings below will retrieve selected details of all clients living in this ZIP code:

Database	c:\vg\scripts\Client Enquiry\clients.mdb
Query Statement	SELECT AddressId, BalanceOwing, WorkPhone, MobilePhone FROM Addresses WHERE PostalCode='$RV[Ask for ZIP]'

The count of how many clients were found to live in this postal code is accessible via $RV[Get Client Details_RowCount]. The first clients details would be accessible using $RV[Get Client Details_1_1] through to $RV[Get Client Details_4_1] - We can now use Say Number modules to speak the information to the caller.

 

SQL Example 2 : Insert Data

The following SQ statement shows how to insert data into Payments table. In this example the Payments table has the following fields (amongst others): CustomerID, PayAmount, CardNumber, ExpDate. The values to be inserted into the database have been entered by the user in previous modules and Result Variables are used to in the expression. The result variables will be replaced by VoiceGuide by the actual entered data before the SQL statement is executed.

INSERT INTO Payments (CustomerID, PayAmount, CardNumber, ExpDate) VALUES ('$RV[GetCustId]', '$RV[GetPayAmount]', '$RV[GetCardNumber]', '$RV[GetExpiryDate]')

 

SQL Example 3 : Update Data

The following SQL statement shows how to modify data in the Payments table. The Payments table has has the following fields (amongst others) CustomerID, PaymentStatus. The result variables will be replaced by VoiceGuide by the actual entered data before the SQL statement is executed.

UPDATE Payments SET PaymentStatus='Paid' WHERE CustomerID='$RV[GetCustId]'

In the examples above we have used single quotes as part of the WHERE and VALUES sections of the SQL statement. Single quotes should be used when referring to string or text fields in the database. When referring to number fields the single quotes should not be used: eg:

UPDATE Products SET UnitPrice=$RV[AddToPrice] WHERE ProductID=$RV[GetProductId]

 

Books on SQL

A pretty good book on SQL is:
"SQL Queries for Mere Mortals" by Michael J. Hernandez, John Viescas
ISBN 0201433362

 

Microsoft Excel

The DB Query module can also retrieve data directly from an Excel file.

Lets have an application which allows people to query a daily roster:

The caller would enter their Roster Number - which corresponds to the row in the Excel spreadsheet, and then we could retrieve the information in two cells 'start time' and the 'end time' using the following query:

Sheet2$B$RV[Get Roster Number]:C$RV[Get Roster Number]

The Start Time would be accessed using $RV[Excel Query_1_1] and the End Time would be accessed using $RV[Excel Query_2_1]

If we had each day of the week on a different page, we could also ask the customer to enter the day that they want to enquire about, and then our Query would be:

Sheet$RV[Get Day]$B$RV[Get Roster Number]:C$RV[Get Roster Number]

This example can be found in the "..\scripts\Customer Enquiry" directory.