External OutDialQue Source : ODBC

VoiceGuide v7 will by default use an internal database to store the details of all the outbound calls which are to be made. This is sufficient for most applications as the internal database can easily handle having up to about 100,000 queued calls at any one time.

In some circumstances use of another database is preferred. VoiceGuide v7 can use any ODBC compliant database as an additional OutDialQue source.

All that is required for VoiceGuide v7 dialer to additionally look in another database for any queued outgoing calls is:

1. Create a table CallQue in the new database of choice.
2. Specify the connection string to the new database using entry OutDialQue_ODBC_ConnectString in VG.INI file, in section [VGDialer].
3. Optionally set the OutDialQue_ODBC_SqlPrefix and OutDialQue_ODBC_SqlSuffix fields in VG.INI file, in section [VGDialer]

 

The OutDialQue_ODBC_ConnectString connection script should be specified as an ODBC style connect string. VoiceGuide uses ADO.NET 2.0 System.Data.Odbc.OdbcConnection object to connect to the secondary OutDialQue database.

The OutDialQue_ODBC_SqlPrefix and OutDialQue_ODBC_SqlSuffix entries are used to modify the SQL command sent to the OutDialQue database.

By default the OutDialQue_ODBC_SqlPrefix is assigned a value of  SELECT TOP 1

By default the OutDialQue_ODBC_SqlSuffix is blank.

 

Example: Setting up SQL Server based OutDialQue

Here is a screenshot from SQL Server 2005 showing the created the OutDialQue database and the CallQue table:

The type of each of the columns in the CallQue table can be seen in the screenshot.

The ID column needs to be set as a 'Primary Key' and as an 'Identity'. The ID will then be auto-generated by the database each time a new call is loaded and is used to give each call a unique identifying ID number.

An example VG.INI entry for a SQL Server based data source would be:

OutDialQue_ODBC_ConnectString=Driver={SQL Native Client};Server=127.0.0.1;Database=OutDialQue;Uid=vg;Pwd=vg;

Here is an SQL statement which can be used to insert a new entry into the OutDialQue.CallQue table:

INSERT INTO CallQue
(PhoneNumber, PhoneNumberPrefix, ActivateTime, DayTimeStart, DayTimeStop, DaysCallAllowed, LineSelection,
CampaignName, Priority, OnAnswerLive, OnAnswerMachine, OnAnswerFax, OnNotAnswered, OnRetriesExhausted,
AnswerTimeout, RetriesLeft, RetriesDelay, RV, CallOptions, EscalationCalls)
VALUES
('ext0001@10.1.1.8', '', 0, 0, 2359, '', '',
'', 1, 'C:\\myOnAnswerLive.vgs', 'C:\\myOnAnswerMachine.vgs', 'C:\\myFaxMessage.tiff', 'C:\\myOnNotAnswered.vbs', 'C:\\myOnRetriesExhauste.vbs',
60, 1, 1, '', '', '');

Here is another SQL statement which can be used to insert a new entry into the OutDialQue.CallQue table:

INSERT INTO CallQue
(PhoneNumber, PhoneNumberPrefix, ActivateTime, DayTimeStart, DayTimeStop, DaysCallAllowed, LineSelection,
CampaignName, Priority, OnAnswerLive, OnAnswerMachine, OnAnswerFax, OnNotAnswered, OnRetriesExhausted,
AnswerTimeout, RetriesLeft, RetriesDelay, RV, CallOptions, EscalationCalls)
VALUES
('ext0001@10.1.1.8', '', '2008-05-07 15:30:00', 0, 2359, 'MoTuWeThFr', '',
'reminder', 1, 'C:\\myOnAnswerLive.vgs', 'C:\\myOnAnswerMachine.vgs', 'C:\\myFaxMessage.tiff', 'C:\\myOnNotAnswered.vgs', 'C:\\myOnRetriesExhauste.vgs',
60, 1, 1, '[BookingId]{3412233}', '', '');

 

 

Example: Setting up MySQL based OutDialQue

Here is a script which can be used to create the OutDialQue database and the CallQue table:

CREATE DATABASE IF NOT EXISTS `OutDialQue`
USE `OutDialQue`;

DROP TABLE IF EXISTS `OutDialQue`.`CallQue`;

CREATE TABLE `OutDialQue`.`CallQue` (
`ID` INT NOT NULL AUTO_INCREMENT,
`PhoneNumber` TEXT NULL,
`PhoneNumberPrefix` TEXT NULL,
`ActivateTime` DATETIME NULL,
`DayTimeStart` INT NULL,
`DayTimeStop` INT NULL,
`DaysCallAllowed` TEXT NULL,
`LineSelection` TEXT NULL,
`CampaignName` TEXT NULL,
`Priority` INT NULL,
`OnAnswerLive` TEXT NULL,
`OnAnswerMachine` TEXT NULL,
`OnAnswerFax` TEXT NULL,
`OnNotAnswered` TEXT NULL,
`OnRetriesExhausted` TEXT NULL,
`AnswerTimeout` INT NULL,
`RetriesLeft` INT NULL,
`RetriesDelay` INT NULL,
`RV` TEXT NULL,
`CallOptions` MEDIUMTEXT NULL,
`EscalationCalls` MEDIUMTEXT NULL,
PRIMARY KEY (`ID`),
INDEX `IdxActivateTime` (`ActivateTime`),
INDEX `IdxCallRetriesLeft` (`RetriesLeft`),
INDEX `IdxDayTimeStart` (`DayTimeStart`),
INDEX `IdxDayTimeStop` (`DayTimeStop`),
INDEX `IdxID` (`ID`),
INDEX `IdxPriority` (`Priority`)
)

For comparison, this is VoiceGuide's internal OutDialQue.CallQue table. VoiceGuide uses VistaDB as the internal OutDialQue database.

The VG.INI entries for a MySQL based data source would be:

OutDialQue_ODBC_ConnectString=Driver={MySQL ODBC 3.51 Driver};Server=10.1.1.8;Port=3306;Database=OutDialQue;User=voiceguide;Password=voiceguide;Option=3;
OutDialQue_ODBC_SqlPrefix=SELECT
OutDialQue_ODBC_SqlSuffix=LIMIT 1

Note that MySQL requires that the OutDialQue_ODBC_SqlPrefix and OutDialQue_ODBC_SqlSuffix fields be set to the settings shown above.

The SQL statements which can be used to insert a new entry into the OutDialQue.CallQue table would be the same as in the SQL Server example.