Database Query

The Database module will access and/or update information in databases. Any information retrieved will be available to be used by subsequent modules.

VoiceGuide can use ADO.NET or DAO or ODBC drivers/providers to retrieve data from any database. Reading and setting of Data in Excel files is also supported.

 

Module Parameters

Database or ODBC Source	Database filename, or the data source name (DSN) of an ODBC data source, or the ADO.NET Provider Name.
Connect String	Connect String if ODBC source or ADO.NET Provider Name is used in the 'Database' field
Query Statement	The SQL statement to run, or the rage of cells to retrieve from Excel. Any valid SQL statement or Excel cell range query.

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

 

Database or Source

This entry is used to specify one of the below:

• ODBC Data Source name - as named in Windows' ODBC Administrator.

• ADO.NET Provider Name - eg: System.Data.SqlClient

• MS Access filename

• MS Excel filename

 

Connect String

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
ODBC Data Source (MS Access) :
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];
ODBC Data Source (MySQL) :
Database	MySqlDev7system
Connect String	ODBC;Driver={MySQL ODBC 5.1 Driver};Server=10.1.1.8;Database=vgdb;User=voiceguide;Password=voiceguide;Option=3;
Query	SELECT PinNumber FROM Customers WHERE CustCallID='$RV[CIDNUMBER]'
The examples below use ADO.NET data providers and can only be used in VoiceGuide v7:
MS SQL Server :
Database	System.Data.SqlClient
Connect String	Data Source=10.1.1.9,1433;Database=vgDb;User ID=sa;Password=gh85d2fg;
Query	Any SQL statement
MySQL :
Database	MySql.Data.MySqlClient
Connect String	Database="vgDb";Data Source="10.1.1.9";User Id="voiceguide";Password="fnsj84hp";
Query	Any SQL statement
ODBC :
Database	System.Data.Odbc
Connect String	Driver={MySQL ODBC 5.1 Driver};Server=10.1.1.8;Database=vgdb;User=voiceguide;Password=voiceguide;Option=3;
Query	Any SQL statement

VoioceGuide 7 uses ADO.NET to connect to databases.

VoiceGuide 5 and 6 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

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”.

 

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.

The following Result Variables can be used to access the number of database entries which were returned:

$RV[ModuleTitle_RowCount]

 

 

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.

 

Retrieving 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.

 

Inserting 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]')

 

Updating 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]

 

Working with BLOBs

VoiceGuide allows BLOB handling. BLOBs are usually used to store sound or graphics files or other large data files.

To specify a BLOB data element at insert the following needs to be to be specified as part of the SQL statement:

<BLOB><file>filename</file></BLOB>

eg:

INSERT INTO VmMsgs (Vmb, MsgKey, WavBlob) VALUES (1111, '$RV[MsgID]', <BLOB><file>$RV[Rec]</file></BLOB>)

 

To specify a BLOB data element for retrieval the following needs to be to be specified as part of the SQL statement:

<BLOB><dbcolumn>ColumnName</dbcolumn><file>filename</file></BLOB>

eg:

SELECT Vmb, <BLOB><dbcolumn>WavBlob</dbcolumn><file>c:\retrievedBLOB.wav</file></BLOB> FROM VmMsgs WHERE MsgKey='$RV[MsgID]';

 

A sample script which writes and reads .WAV sound files as BLOB to/from database is provided in VoiceGuide's "scripts\Database BLOB WriteRead" subdirectory.

 

Books on SQL

A good introductory 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.

Excel range query specification follows this format :

SheetName$StartCell,EndCell 

 

eg. 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]

If the database query modules title was GetTimes then the Start Time would be accessed using $RV[GetTimes_1_1] and the End Time would be accessed using $RV[GetTimes_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.