 |
|
||||||||||||||||||
User Specified Dialer Database SourcesVoiceGuide 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. In some circumstances use of another database is preferred (eg SQL Server). VoiceGuide v7 can use any database as a dialer database, with the connection to the database made through any ADO.NET Data Provider. Most databases have an ADO.NET Data Provider available. If the ADO.NET Data Provider is not available for the database that you wish to use, then the ODBC ADO.NET Data Provider can be used, as all databases support ODBC. All that is required for VoiceGuide v7 dialer to use another database for any queued outgoing calls is:
VoiceGuide will create all the necessary Tables/Indexes/etc. the first time it uses the user specified database.
Loading Calls DirectlyThis section outlines how calls need to be loaded into the Dialer Database if you would like your own programs to load the calls instead of using the VoiceGuide Telephone Number Loader or the VoiceGuide COM function or XML file to load the calls. The outbound calls database uses two tables: CallQue and PortToUse Addition of new calls into the system involves placing new entries in both tables, with possible multiple entries in the PortToUse table, depending on what port selections need to be specified. The PortToUse table is used to indicate on which ports the particular call can be made. If a call is allowed to be made on any of the systems ports then a single entry in the PortToUse table needs to be made, with the PortToUse.PortNumber field assigned a value of -1. Otherwise if call can only be made on some of the ports then a new row needs to placed in PortToUse for each port on which the outgoing call is allowed to be made. The telephony ports on the VoiceGuide system are numbered from 1. By default the PortToUse table is used when establishing which call is to be made next. Scheduling information is still included in the CallQue table as there is an option of turning off the PortToUse table use altogether if there is no need to limit the ports on which the calls can be made. It's recommended to use the Dial List Loader application to load some calls into the database and then examine the database tables to how the information is placed in the tables. The SQL statements used can be seen in the vgDialListLoad trace files (see VG's \log\ subdirectory), and it's recommend that these traces be looked at by anybody wanting to see how the Dial List Loader is actually performing the inserts. Here are the two main SQL statements used (with parameter placeholders): INSERT INTO CallQue (GUID, PhoneNumber, PhoneNumberPrefix, ActivateTime, Priority, TimeStart_Mon, TimeStart_Tue, TimeStart_Wed, TimeStart_Thu, TimeStart_Fri, TimeStart_Sat, TimeStart_Sun, TimeStop_Mon, TimeStop_Tue, TimeStop_Wed, TimeStop_Thu, TimeStop_Fri, TimeStop_Sat, TimeStop_Sun, CampaignName, OnAnswerLive, OnAnswerMachine, OnAnswerFax, OnNotAnswered, OnRetriesExhausted, AnswerTimeout, RetriesLeft, RetriesDelay, RV, CallOptions, EscalationCalls) OUTPUT Inserted.ID VALUES (@guid, @strNbrToDial, @strPhoneNumberPrefix, @dateActivateTime, @iPriority, @iTimeStart_Mon, @iTimeStart_Tue, @iTimeStart_Wed, @iTimeStart_Thu, @iTimeStart_Fri, @iTimeStart_Sat, @iTimeStart_Sun, @iTimeStop_Mon, @iTimeStop_Tue, @iTimeStop_Wed, @iTimeStop_Thu, @iTimeStop_Fri, @iTimeStop_Sat, @iTimeStop_Sun, @strCampaignName, @strOnAnswerLive, @strOnAnswerMachine, @strOnAnswerFax, @strOnNotAnswered, @strOnRetriesExhausted, @iAnswerTimeout, @iRetriesLeft, @iRetriesDelay, @strRV, @strOptions, @strEscalationCalls); INSERT INTO PortToUse (CallID, CallGUID, PortNumber, ActivateTime, Priority, TimeStart_Mon, TimeStart_Tue, TimeStart_Wed, TimeStart_Thu, TimeStart_Fri, TimeStart_Sat, TimeStart_Sun, TimeStop_Mon, TimeStop_Tue, TimeStop_Wed, TimeStop_Thu, TimeStop_Fri, TimeStop_Sat, TimeStop_Sun) VALUES (@iCallID, @guid, @iPortNumber, @dateActivateTime, @iPriority, @iTimeStart_Mon, @iTimeStart_Tue, @iTimeStart_Wed, @iTimeStart_Thu, @iTimeStart_Fri, @iTimeStart_Sat, @iTimeStart_Sun, @iTimeStop_Mon, @iTimeStop_Tue, @iTimeStop_Wed, @iTimeStop_Thu, @iTimeStop_Fri, @iTimeStop_Sat, @iTimeStop_Sun); All the call details are first inserted into the CallQue table, and then a subset of call information is inserted into the PortToUse table. After inserting a row into the CallQue table the value of the autogenerated ID field needs to be retrieved and the value of that ID field is used in the CallID field when inserting related rows into the PortToUse table. This can be done differently depending on what databse is used. In the INSERT INTO CallQue example above (which works fine on MS SQL Server) the ID retrieval is done using the SQL command OUTPUT Inserted.ID The ID retrieval can also be be done using this SQL command: SELECT DISTINCT @@identity FROM CallQue The above command can be issued at the same time as the Insert command, like this: INSERT INTO CallQue (...) VALUES (...); SELECT DISTINCT @@identity FROM CallQue An alternative approach instead of placing the CallQue.ID in the PortToUse.CallID column would be to use a GUID to link the PortToUse entries to the CallQue entry. Just generate a GUID and write the same GUID into the PortToUse.CallGUID column and into the CallQue.GUID column. A value of -1 can then be placed in the PortToUse.CallID column.
Using MS SQL Server Express 2008 as the Dialer Database SourceHere is an example Config.xml <Dialer> section for MS SQL Server 2008: <Dialer> After setting the Config.xml to appropriate values you will need to create the database object named vgDb (or whatever the name specified in the <OutDialQue_Database> section is). Use the Microsoft SQL Server Management Studio to create the database object. You can now start the VoiceGuide "Dial List Loader" application. On startup the Dial List Loader will connect to the database object and run the Db_Create.sql script creating the Tables and Indexes. You can then use the Dial List Loader to load new calls into the system, or try loading the calls into the database yourself directly. The <OutDialQue_SqlPrefix> and <OutDialQue_SqlSuffix> values are used when VoiceGuide constructs the SQL query to find the suitable call in the PortToUse table. The default
value used if <OutDialQue_SqlPrefix> is blank is: SELECT TOP 1
Configuring MS SQL ServerThese instructions are for MS SQL Serer 2005, but steps for MS SQL Server 2008 are similar. If the SQL Server is on a system separate to VoiceGuide then a fresh installation of MS SQL Server needs to be configured to allow external connections. Also the system's firewall needs to be set to pass though the external connection requests to the database. Here is a summary of what is needed: Assumption: your SQL Server was
installed on remote server and behind firewall; SQL Instance was
started; and your client app specify correct remote sql instance
name. On your Client Side: telnet <remoteserver> <portnumber>works <portnumber> is the port
that your remote sql instance is listening on.
Using MySQL 5.0 as the Dialer Database SourceWhen using MySQL you should first install the MySQL ADO.NET Data Provider v5.2.3 or newer. See: http://dev.mysql.com/downloads/connector/net/5.2.html Here is an example Config.xml <Dialer> section for MySQL: <Dialer> After setting the Config.xml to appropriate values you will need to create the database (schema) object named vgDb (or whatever the name specified in the <OutDialQue_Database> section is). You can use the MySQL Query Browser to create the database object. You will also need to ensure that the user specified in the Connection string has the rights to fully work with and manage the vgDb schema. Best way to configure and test the ADO.NET connection is to just start the VoiceGuide "Dial List Loader" application. On startup the Dial List Loader will connect to the database object and run the Db_Create_MySql.Data.MySqlClient.sql script creating the Tables and Indexes. You can then use the Dial List Loader to load new calls into the system, or try loading the calls into the database yourself directly. The <OutDialQue_SqlPrefix> and <OutDialQue_SqlSuffix> values are used when VoiceGuide constructs the SQL query to find the suitable call in the PortToUse table. Call PrioritizationPriority ordering is specified by using the ORDER BY clause when selecting calls from the database. This clause is specified in the OutDialQue_SqlSuffix setting in the <Dialer> section of the Config.xml file: <OutDialQue_SqlSuffix>ORDER BY Priority ASC</OutDialQue_SqlSuffix> If you have a large number of calls loaded then ordering the retrieved calls by Priority can degrade call data retrieval speed. If you are seeing excessive call retrieval times from the database then removing this clause would speed up call retrieval. If priority is being enabled and a large number of calls are being loaded then we'd recommend using a sever class database like SQL Server or MySql etc.
Other Notes<OutDialQue_PortToUse_LinkField> options are:
The actual SQL script used by VoiceGuide to automatically create the database tables used by it can be found in VoiceGuide's \system\setup\ subdirectory. The default scripts used is named Db_Create.sql ADO.NET Provider Specific versions of this script can be created. To make a Provider-specific script file the Provider name needs to be specified as part of the filename, as such: Db_Create_ProviderName.sql eg: Db_Create_System.Data.SqlClient.sql
Call Detail Records (CDRs) will also be stored in the database specified in the Config.xml <Dialer> section, in tables CdrIn and CdrOut.
|