Using an External Database

[mpcengineering]

Hi there,

 

I was on the forum many moons ago:

 

http://voiceguide.com/forums/index.php?sho...ternal+database

 

And am finally at the stage where the monitoring software I am using has the ability to write to databases via Lua. So to cut a long story short, I want to use an external database in VG7 as an additional source of data to OutDialQue.mdb.

 

So far I have published the SQL database using ODBC successfully (named inm_voice_watch) and have added the following line into vg.ini under the [VGDialer] section:

 

OutDialQue_ODBC_ConnectString={SQL Server};Server=172.17.201.59;Port=80;Database=inm_voice_watch;

 

Could someone confirm the format of the above is correct? I'm also unsure now as to how to go about trying to determine whether voiceguide is seeing this database and if so is it recognising the format of the data correctly. I am inserting the data via Lua scripting using the INSERT INTO sql argument as shown on the help file here:

 

http://www.voiceguide.com/vghelp/source/ht...ialque_odbc.htm

 

The data is being inserted correctly and is viewable from within the database.

 

A couple of limiting factors:

As this is a test setup as yet, I am using the demo version of VG7 and have not yet got a dialogic card. I want to test it with VG7 as we will be using a dialogic card for this setup when it has been determined it will work and and perhaps also VOIP at some point. How can I determine that voiceguide is attempting to dial out or at has least recieved the correct instructions in order to be able to with this setup?

 

Any help much appreciated!

 

Jon

[SupportTeam]

Please see http://www.connectionstrings.com/ to determine the appropriate connection string.

 

It should be something like this:

 

Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword

 

So in VG.INI you should have something like this:

 

OutDialQue_ODBC_ConnectString=Driver={SQL Server};Server=myServerAddress;Database=inm_voice_watch;Uid=myUsername;Pwd=myPas

word;

 

not sure whether you should use:

 

Server=172.17.201.59;Port=80;

 

or:

 

Server=172.17.201.59,80;

 

If VoiceGuide cannot connect to the ODBC source this should be logged in the vgEngine trace file.

 

Trace files are created in VG's \log\ subdirectory.

[mpcengineering]

Thats great, thanks very much. I'll check out the link and also review the logs.

 

Jon

[SupportTeam]

Here are the type of errors that you may see in VoiceGuide's vgEngine log if the ODBC connect string is incorrect:

 

If it cannot connect to SQL server:

 

191105.296 7 0 ERROR v7.0.2846.32492 (Wed 17/10/2007 17:03:04.93) DoWorkFindCall source2odbc ExecuteReader sql:[Driver={MySQL ODBC 3.51 Driver};Server=10.1.1.9;Port=3306;Database=OutDialQue;User=voiceguide;Password=v

iceguide;Option=3;] : ERROR [HY000] [MySQL][ODBC 3.51 Driver]Can't connect to MySQL server on '10.1.1.9' (10061)

ERROR [HY000] [MySQL][ODBC 3.51 Driver]Can't connect to MySQL server on '10.1.1.9' (10061)

 

If the passoword is wrong:

 

191032.250 7 0 ERROR v7.0.2846.32492 (Wed 17/10/2007 17:03:04.93) DoWorkFindCall source2odbc ExecuteReader sql:[Driver={MySQL ODBC 3.51 Driver};Server=10.1.1.8;Port=3306;Database=OutDialQue;User=voiceguide;Password=v

iceguideERR;Option=3;] : ERROR [HY000] [MySQL][ODBC 3.51 Driver]Access denied for user 'voiceguide'@'DEV5' (using password: YES)

ERROR [HY000] [MySQL][ODBC 3.51 Driver]Access denied for user 'voiceguide'@'DEV5' (using password: YES)

 

The ODBC connection is retried every second or so, so this lets you bring the ODBC source up and down without restarting VoiceGuide. VoiceGuide will just log error messags when it cannot connect and will then retry later.

[mpcengineering]

Ok, I've had a good look through the logs for error messages in that format but can't find anything related to an attempt to load an external database source, its almost like it's not processing the OutDialQue_ODBC_ConnectString line in VG.INI. There are a few error messages, but they are related to looking for a dial out line and so on. The only line related to a dial out database is confirmation of finding the existance of OutDialQue.vdb3:

 

104049.052 5 0 outDialQue exists : C:\Program Files\VoiceGuide\data\OutDialQue.vdb3

 

So I'm guessing perhaps its a problem with the VG.INI file declaration? Even if the connect string was incorrect (I have reviewed the correct format from the link you provided) just the fact that a line beginning with OutDialQue_ODBC_ConnectString= is in the [VGDialer] section should trigger a log entry should it not?

 

Any more ideas?

 

Jon

[SupportTeam]

There are a few error messages, but they are related to looking for a dial out line and so on.

Please post the trace file which captures the startup and the error messages which you mention.

 

When posting traces/scripts please .ZIP them up and post them as attachments.

[mpcengineering]

Sure thing, I have done a fresh restart of the IVR service and the appropriate log is attached.

1017_1409_vgEngine.zip

[SupportTeam]

The trace shows this error:

 

140901.564 5 load: C:\Program Files\VoiceGuide\ktTelDialogicSR60.dll, (ktTelDialogicSR60, Version=7.0.2825.25552, Culture=neutral, PublicKeyToken=a250d0d6f733c81d)

140901.908 5 ERROR v7.0.2830.23509 (Mon 01/10/2007 12:03:39.06) Load_Layer_ktTel_DynamicPlugins Assembly.Load : The specified module could not be found. (Exception from HRESULT: 0x8007007E)

 

This shows that DLL appears to be missing from your system...

 

Can you check if if ktTelDialogicSR60.dll is present in VoiceGuide's directory? Is it version 7.0.2825.25552 ?

 

It's probably best for you to reinstall VoiceGuide again (download latest version form our WWW). Remove the current install first (delete the entire directory after uninstall) and then install latest version.

 

Please post trace after the re-install is done.

 

[mpcengineering]

You know what, I had totally forgotten, but I remembered as I went to the download page I had not on the original install put on the hmp drivers also, doh! Anyhow, I now have a clean install of the hmp drivers and voiceguide. The log looks much better now. No errors at all apart from:

 

090547.728 10 dial findcall (odbc) query completed.

090548.745 10 0 ERROR v7.0.2846.32492 (Wed 17/10/2007 17:03:04.93) DoWorkFindCall source2odbc ExecuteReader sql:[Driver={SQL Server};Server=FAC271PC\SQLLUATEST;Database=inm_voice_watch;Uid=MPC-NOTES\john-hu;Pwd=hello;] : ERROR [28000] [Microsoft][ODBC SQL Server Driver]

Login failed for user 'MPC-NOTES\john-hu'. The user is not associated with a trusted SQL Server connection.

ERROR [28000] [Microsoft][ODBC SQL Server Driver][sql Server]Login failed for user 'MPC-NOTES\john-hu'. The user is not associated with a trusted SQL Server connection.

at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)

at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)

at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnect

on owningConnection, DbConnectionPoolGroup poolGroup)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.Odbc.OdbcConnection.Open()

at ..(Object sender, args)

 

Adding Trusted_Connection=yes; to the connect string sorted that out and just:

 

090548.745 10 dial findcall (odbc) query completed.

 

is now reported with no following error messages

 

The final connect string looks to be:

 

OutDialQue_ODBC_ConnectString=Driver={SQL Server};Server=FAC271PC\SQLLUATEST;Database=inm_voice_watch;Uid=SOME-DOMAIN\someuser;Pwd=somepass;Trusted_Connection=yes;

 

with the database using windows authentication

 

The only odd thing now seems to be, if IVR is started with the connect string incorrect, then errors are listed in the log as shown, correcting the string then adds the odbc entry with no following errors. However if I start the service with the connect string initially correct there is no 090548.745 10 dial findcall (odbc) query completed entry listed in the log at all showing successful connection?

 

Perhaps this is normal, but I'll have a play with adding entries to the database, on the fly, I guess what I have to determine now is if voiceguide thinks its got enough information to attempt a dial out.

 

I have attached 2 logs, one for a service start where the connect string is intially incorrect, and a second for a service start where the connect string is initially correct.

 

Thanks again for your help

 

Jon

1018_0927_vgEngine_bad_string_.zip

1018_0929_vgEngine_good_string_.zip

[SupportTeam]

The "dial findcall (odbc) query completed " log entry only appears if the SQL query which looks for data in the databse returned some rows.
Otherwise nothing gets logged. If something was logged every time no data ws found then the log would grow when no action is taken by the system.

For debugging this you would however need access to these log entries.

You may want to update to this version of VG: [old link removed] and then set the VgEngine log level in VG.INI to 11
After VoiceGuide restart you should see a stream of logging relating to the OutDialQue connectivity.

It's probably also a good idea to load some calls using the Dial List Loader application and then use that apps 'Database Tools' window to see how the various fields in the internal OutDialQue table are populated.

[mpcengineering]

Ok, thanks, I will try those suggestions. The only potential problem I have encountered so far is setting a value for the ActivateTime column in the database. As the data type must be set to datetime, I am unable to pass 0 to this column to make the call out immediate, how else am I able to make the call out immediate? Is the method to enter the current date and time into that column instead? That might be more suited anyway as there there would be a paper trail of entry dates and times in the database.

[SupportTeam]

Is the method to enter the current date and time into that column instead?

Yes. Or some past date eg: 2001-01-01 12:00:00

 

as there there would be a paper trail of entry dates and times in the database.

The database entries are deleted once outgoing call is answered. Unanswered calls remain in the database.

 

 

 

[mpcengineering]

Ok, deletion of entries is not a problem, there will be a trail in the monitoring software running the Lua script. I've altered the INSERT INTO command to insert the current date for the activate time, nice to know any previous date/time in the database will be called. Here's whats happenning:

 

If I clear he database and add a new entry into it viam the log adds the following new line which looks good to me?

 

130944.116 11 dial findcall (odbc) query completed.

130944.116 11 0 dial FindCallQueryCompleted some calls found (any lineid search) ID=29 source=[Driver={SQL Server};Server=FAC271PC\SQLLUATEST;Database=inm_voice_watch;Uid=SOME-DOMAIN\someuser;Pwd=somepass;Trusted_Connection=yes;]

130944.116 11 0 dial callque (odbc) update id=29 completed ok 18/10/2007 13:10:43 : 0

130944.116 11 dial findcall (odbc) query completed.

130944.116 11 1 dial StartOutgoingCall call.

130944.131 11 1 dial StartOutgoingCall start tel[07123456789] live[C:\myOnAnswerLive.vgs] am[C:\myOnAnswerMachine.vgs] rv[[bookingId]{3412233}]

130944.131 11 1 rv clear all

130944.131 11 1 rv add [bookingId]{3412233}

130944.131 11 1 rv add [OutDial_RetriesLeft]{1}

130944.131 11 1 dial making call tel[07123456789] live[C:\myOnAnswerLive.vgs] am[C:\myOnAnswerMachine.vgs] OnHangup[] rv[[bookingId]{3412233}[OutDial_RetriesLeft]{1}]

130944.178 11 1 state Dialing 07875737196

130944.178 11 1 timer set 60 EV_TIMEOUT_MAKECALL_NORESPONSE

130944.194 11 2 iDialoutsEnabledOnThisLine=0

130944.194 11 3 iDialoutsEnabledOnThisLine=0

130944.194 11 4 iDialoutsEnabledOnThisLine=0

130944.194 11 0 dial at end of lines list. reset check timer.

 

Basically looks to be a successful attempt at making the call, obviously it fails because the hardware isnt present to make the call and so the entry stays in the database. However adding a second entry endlessly enters the following in the log:

 

131326.697 11 dial findcall (odbc) query completed.

131326.697 11 0 dial FindCallQueryCompleted some calls found (any lineid search) ID=29 source=[Driver={SQL Server};Server=FAC271PC\SQLLUATEST;Database=inm_voice_watch;Uid=SOME-DOMAIN\someuser;Pwd=somepass;Trusted_Connection=yes;]

131326.697 11 1 dial LineStateAppPov=InGuardTimeAfterEndOfCall

131326.697 11 2 iDialoutsEnabledOnThisLine=0

131326.697 11 3 iDialoutsEnabledOnThisLine=0

131326.697 11 4 iDialoutsEnabledOnThisLine=0

131326.697 11 0 dial at end of lines list. reset check timer.

 

Is this the case because it is waiting for the successful completion of the previous failed call before moving onto the next one? At which point will it query the next item in the database?

 

I also tried deleting all rows in the database before adding a new one so that only one row was present at any one time even if the previous call failed but the same above entries still occur. Any ideas on that front?

 

Sorry to spam this thread with so many log entries, hope thats ok

 

Jon

[SupportTeam]

because the hardware isnt present to make the call

How is VoiceGuide starting if there is no hardware? Is it setup right now in VoIP mode?

 

Each time VoiceGuide does a check for presence of outgoing calls it first does a general check to see if any calls are ready to dial and if some calls are found then VG scans the lines to see which lines are free. When an available line is found then a query is made to find a call which can be made on this line. The scan of outgoing channels can be seen here:

 

131326.697 11 1 dial LineStateAppPov=InGuardTimeAfterEndOfCall

131326.697 11 2 iDialoutsEnabledOnThisLine=0

131326.697 11 3 iDialoutsEnabledOnThisLine=0

131326.697 11 4 iDialoutsEnabledOnThisLine=0

 

And as no free channels can be found then no further database queries are made until the next time a general query is started again (1 second later).

 

As there is no hardware to make the outgoing calls on maybe that is why you are seeing first port staying in "InGuardTimeAfterEndOfCall" for longer then it should.

[mpcengineering]

I installed the software for use with a dialogic card, not VoIP, so I guess it's a bit wierd that a dial out attempt was made given your description of how the software queries for a line before it makes a dial attempt. I'm not sure how the software is starting if it needs hardware to do so, but it is Anyway, thanks for your extensive support on helping me test the setup, I think I'm now at the point where I have established it will definitely work and really need to purchase the software and a dialogic card.

 

Thanks again

 

Jon

[SupportTeam]

Guess the "Traditional Dialogic Cards" version was not extensively tested in situations where no cards are present in the system...

From traces it appears that VG does not realize that the first port it opened is not in fact there, but it does report an error when trying to open ports 2, 3 and 4.

 

The next step would be to get a Dialogic card. The Evaluation version of VoiceGuide lets you make up to 10 outbound calls after each restart.

[mpcengineering]

Looks like its time to pass a quote under the noses of those that hold the purse strings