Mysql

[mehdi_h]

Hi

 

I have installed Mysql to use it as an external outbound database.

I seems that in VG7, the connection information have changed, no more in vg.ini file.

Could you please provide me with an example (piece of config.xml) that shows how i shoul call the msql connection.

 

 

Thank you for your help

mh

 

[mehdi_h]

I mean I dont know what to put inthese fields?

 

 

<OutDialQue_ADODB_Provider>SQLOLEDB</OutDialQue_ADODB_Provider>

<OutDialQue_Database>OutDialQue</OutDialQue_Database>

 

 

Tkx

Mh

 

 

[mehdi_h]

<Dialer>

 

<Notes>

 

VistaDB.NET20:

Data Source='" + sDbOutDialQue_Fname + "';Open Mode=NonexclusiveReadWrite

Data Source='" + sDbFileName + "'"; //; LCID=1033; Password=\"s$;2'!dS64\"; Encrypt = TRUE;

 

MySQL:

Prefix: SELECT

Suffix: LIMIT 1

ODBC connect string:

{MySQL ODBC 3.51 Driver};Server=127.0.0.1;Port=3306;Database=OutDialQue;User=voiceguide;Password=

oiceguide;Option=3;

 

</Notes>

 

<OutDialQue_ADODB_Provider>System.Data.SqlClient</OutDialQue_ADODB_Provider>

<OutDialQue_Database>moussaz</OutDialQue_Database>

<OutDialQue_ConnectString>Driver={MySQL ODBC 3.51 Driver};Server=127.0.0.1;Port=3306;Database=OutDialQue;User=voiceguide;Password=

oiceguide;Option=3;</OutDialQue_ConnectString>

<OutDialQue_SqlPrefix>SELECT</OutDialQue_SqlPrefix>

<OutDialQue_SqlSuffix>LIMIT 1</OutDialQue_SqlSuffix>

</Dialer>

[SupportTeam]

When using MySQL you should install an MySQL ADO.NET Data Provider.

Then specify the name of the provider in the <OutDialQue_ADODB_Provider> section. The Trace file for the Telephone Number Loader lists all of the installed providers, so if you are not sure then just look at that trace after installing the MySQL ADO.NET Data Provider and you will be able to deduce which of the installed ones it is. (System.Data.SqlClient is used for MS SQL Server)

Then specify the connect string in <OutDialQue_ConnectString>. Documentation which comes with the Data Provider should have information on what connect strings it accepts.

The below are correct:

<OutDialQue_SqlPrefix>SELECT</OutDialQue_SqlPrefix>
<OutDialQue_SqlSuffix>LIMIT 1</OutDialQue_SqlSuffix>

[mehdi_h]

Well I thought I could use the Mysql provided in the Wampserver (WAMP5)?

 

Tkx

MH

[mehdi_h]

Ok will try that.. Tkx

 

[mehdi_h]

I still cant' connect, could you please have a look on the attached log file.

I can't find what I'm doing wrong , certainly in the connection string but no more idea.

 

 

Thank you for your help.

MH

1001_0255_vgDialListLoad.txt

[SupportTeam]

It does look like the connection string is not right or maybe you need to correctly configure the permissions/users on the MySQL.

 

You will need to look at the documentation which comes with the MySQL Data Provider, and maybe ask some questions on their support/community forums to determine what connection string should be used with their product.

 

 

025530.234 0 ado.db factory classes installed on this system:

025530.234 0 System.Data.Odbc : Odbc Data Provider, .Net Framework Data Provider for Odbc

025530.234 0 System.Data.OleDb : OleDb Data Provider, .Net Framework Data Provider for OleDb

025530.234 0 System.Data.OracleClient : OracleClient Data Provider, .Net Framework Data Provider for Oracle

025530.234 0 System.Data.SqlClient : SqlClient Data Provider, .Net Framework Data Provider for SqlServer

025530.234 0 VistaDB.NET20 : VistaDB Data Provider, .Net Framework Data Provider for VistaDB

025530.234 0 MySql.Data.MySqlClient : MySQL Data Provider, .Net Framework Data Provider for MySQL

025530.234 0 config.xml read: /VoiceGuideConfig/Dialer

025530.234 ReadXmlNodesSubEntryintoString(OutDialQue_QueryPortToUseTable) node not found in: System.Xml.XmlElement

025530.234 db config bConfigXml_sDialer_OutDialQue_QueryPortToUseTable = true ()

025530.234 0 vgDb_ProviderInvariant Db_Open call

025530.250 0 Db_Open GetFactory call [MySql.Data.MySqlClient]

025530.296 0 Db_Open sDbName=[moussaz]

025530.296 0 Db_Open CreateConnection call

025530.625 0 Db_Open CreateConnection returned. conn=MySql.Data.MySqlClient.MySqlConnection, this=.

025530.625 0 Db_Open sConnString [server=127.0.0.1;Port=3306;Database=myDataBase;] => [server=127.0.0.1;Port=3306;Database=myDataBase;]

025530.812 0 Db_Open conn.Open call

025531.468 0 Db_Open conn.Open : Access denied for user ''@'localhost' to database 'mydatabase'

 

 

 

[mehdi_h]

Hi

 

I'm facing difficulties still on that point. It seems that I know have the correct connection string. But still not possible to connect.

Do you have an idea rearding the new log file.

 

thanks

Mh

1002_2350_vgDialListLoad.txt

[mehdi_h]

000847.656 0 vgDb_ProviderInvariant Db_Open call

000847.671 0 Db_Open GetFactory call [MySql.Data.MySqlClient]

000847.671 0 Db_Open sDbName=[OutDialQue]

000847.671 0 Db_Open CreateConnection call

000847.718 0 Db_Open CreateConnection returned. conn=MySql.Data.MySqlClient.MySqlConnection, this=.

000847.718 0 Db_Open sConnString [Database="OutDialQue";Data Source="127.0.0.1";User Id="moussa";Password="moussa";] => [Database="OutDialQue";Data Source="127.0.0.1";User Id="moussa";Password="moussa";]

000847.750 0 Db_Open conn.Open call

000847.828 0 Db_Open conn.Open : Unknown database 'outdialque'

000847.828 0 Db_CreateTables start. Database=[OutDialQue]

000847.828 0 sql create database command read from [C:\Program Files\VoiceGuide\system\setup\Db_Create.sql]

 

it seems that the system tries to open the database which is still not created...

 

 

[mehdi_h]

Please don't take previous messages into account

I have created the "OutDialQue" prior and I thought the system would create the tabes itself. It seems it is not the case. Please have a look on the last logfile.

 

 

1003_0022_vgDialListLoad.txt

[mehdi_h]

 

002217.953 0 ERROR 7.0.3191.32564 Db_CreateTables_ExecuteNonQuery : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTITY(1, 1) PRIMARY KEY,

GUID UniqueIdentifier,

PhoneNumber NText,

Phon' at line 3

à MySql.Data.MySqlCl

[SupportTeam]

OK, I see that you are using the following settings to connect to MySQL, correct?

 

<OutDialQue_ADODB_Provider>MySql.Data.MySqlClient</OutDialQue_ADODB_Provider>

<OutDialQue_Database>OutDialQue</OutDialQue_Database>

<OutDialQue_ConnectString>Database="$DATABASE";Data Source="127.0.0.1";User Id="acb";Password="xyz";</OutDialQue_ConnectString>

<OutDialQue_SqlPrefix>SELECT</OutDialQue_SqlPrefix>

<OutDialQue_SqlSuffix>LIMIT 1</OutDialQue_SqlSuffix>

 

The error returned by the database shows that MySQL does not like the default SQL Command that VoiceGuide uses to automatically create the tables.

 

Go to VoiceGudie's \system\setup\ subdirectory and make a copy of file Db_Create.sql, naming it Db_Create_System.Data.SqlClient.sql

 

Then the SQL Command in in Db_Create_System.Data.SqlClient.sql will need to be changed to create tables using commands that MySQL accepts.

 

I suspect that the change needed here is:

 

ID Int IDENTITY(1, 1) PRIMARY KEY,

 

change to:

 

ID Int AUTO_INCREMENT,

 

But you may need to consult your database help file here as well.

 

Just by looking at rest of the SQL Command I'm not sure if MySQL now supports the UniqueIdentifier field type, which VoiceGuide uses. If it does not then we would need to have a workaround incorporated into VoiceGuide for systems which choose to use MySQL as it's Dialer Database. We will setup a MySQL based system here to test with and will report on our results.

 

 

Note that you can just take the contents of the file Db_Create and run the SQL Command directly in your database's administration/query command window. This may let you debug the SQL command better.

 

BTW: The filename containing the table creating commands is in format Db_Create_ADODB_ProviderName.sql When VoiceGuide or Telephone Number Loader starts and detects that the Dialer database is not set up it will look for file named Db_Create_ADODB_ProviderName.sql and run the SQL Command in that file. If Db_Create_ADODB_ProviderName.sql is not present then the contents of file Db_Create.sql will be used.

[mehdi_h]

OK thank you I'm trying that and let U know

 

If I create the DB manuallay with all tables and fields I will not face these kind of problems anymore while Vg dialer will run ?

 

Tkx

MH

 

ps: Mysql is important as alternative external DB

[mehdi_h]

ID Int IDENTITY(1, 1) PRIMARY KEY,

GUID UniqueIdentifier,

 

in Mysql, if I want to setup a uniqueIdentifier, I must setup also "GUID AUTO_INCREMENT" which means 2 times AUTO_INCREMENT fields, which is not possible.

 

 

any advise?

 

 

[mehdi_h]

You are right Mysql doeq not support Uniqueidentifier . I'm a bit blocked with the GUID.

 

Thanks you for any help

[SupportTeam]

Looks like we will need to look into removing the GUID field if MySQL is used.

 

We should have a version which can use MySQL available for you early next week.

[mehdi_h]

It would be great!

Are you going to post if it in the forum?

 

 

Mh

[SupportTeam]

We made necessary changes to allow MySQL to be used as an external dialer database.

Please download and update to this version: [old link removed]

Then read the MySQL notes in the "External Database Source" section of the Help file which ships with that version.

Please post here is you have any questions.

[mehdi_h]

Thank you very much!!!!!! I will let you know.

 

 

In the mean time I have the follwing question.

 

I going to make some sql actions :

 

- add new entries

- remove entries

- change activation date

- change Retriesleft

- and so on.

 

- When I'm doing that on CallQue table, does it mean that I have to do it also exactly the same way on the other PorTOUse table.

- When a call is made is made and finished, it despears froms the callQue table, does it also desapear from the Porttouse table.

- My global question is wah is the PortOUse table behaviours regaring the outgoing calls

 

Thanks

Mh

 

 

 

[mehdi_h]

Forget my previous post, you have made what I was expecting. I will Disable to PorToUse table at all, perfect!!

 

Thkx

Mh

[mehdi_h]

Hi,

 

I have succededd connectiong to mysql. The point now is:

 

- When I'm uploading entries using Data list Loader, I have NULL values in all fields.

- I have set <OutDialQue_PortToUse_LinkField> to Disable in the <Dalier> section and it seems that PortTOuse table is populated anyway. Please advise

 

 

<OutDialQue_ADODB_Provider>MySql.Data.MySqlClient</OutDialQue_ADODB_Provider>

<OutDialQue_Database>vgDb_Mysql</OutDialQue_Database>

<OutDialQue_PortToUse_LinkField>Disable</OutDialQue_PortToUse_LinkField>

<OutDialQue_ConnectString>Database="$DATABASE";Data Source="127.0.0.1";User Id="moussa";Password="moussa";</OutDialQue_ConnectString>

<OutDialQue_SqlPrefix>SELECT</OutDialQue_SqlPrefix>

<OutDialQue_SqlSuffix>LIMIT 1</OutDialQue_SqlSuffix>

 

 

 

Please find files in attachement

 

Thxk for your help

MH

1009_1246_vgDialListLoad.txt

[SupportTeam]

Trace shows that you are using an old version MySQL ADO.NET provider: v5.0.9.0

 

You need to use v5.2.3. See instructions in VoiceGuide Help file.

 

After you update to the right version of the MySQL ADO.NET provider you need to let the new VoiceGuide version create the tables in MySQL. ie: You need to drop any tables created by the previous versions of VoiceGuide/provider and let this new version create it's own tables.

 

Do not use the <OutDialQue_PortToUse_LinkField> initially. It should work without the <OutDialQue_PortToUse_LinkField> being set in Config.xml.

[mehdi_h]

Perfect! It works. I'm doing tests and let you know.

 

Thank you very much for your great and efficient support. Amazing reactivity !

 

Mh

[mehdi_h]

the PortTouse is useful when we want to use specific ports for calls right ? It is the old LineSelection fied?

 

 

If I delete entries in CallQue table I have to do it also in the PortToUse table?

 

I ask this question because when call are completed, there are automatically removed from the callQue table, I would to understrand the beahaviour of PortToUse table?

 

Thkx

[SupportTeam]

Thank you very much for your great and efficient support. Amazing reactivity !

Thank you

 

the PortTouse is useful when we want to use specific ports for calls right ?

Yes.

 

It is the old LineSelection fied?

Sort of. In his new version you can only use port numbers to select the ports to make calls out on.

 

If I delete entries in CallQue table I have to do it also in the PortToUse table?

When VoiceGuide removes from CallQue it's corresponding PortToUse entrie(s) are also deleted. CallQue.ID=PortToUse.CallID and/or CallQue.GUID=PortToUse.CallGUID depending on Database used. In case of MySQL CallQue.ID=PortToUse.CallID.

 

VoiceGuide itself deletes calls, so there usually should be no need for you to delete them yourself.

[mehdi_h]

Hi,

 

Just one more little thing. I'm adding entries in the CallQue table using sql scripts. How should I do on PortTousee table ?

 

Do I have to catch the ID that I have to insert in the PortToUse table "manually" ?

 

Tkx

MMH

 

[SupportTeam]

Do I have to catch the ID that I have to insert in the PortToUse table "manually" ?

Yes. Here is the relevant excerpt from the newest Help file that covers this:

 

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.

[mehdi_h]

hi,

 

Now that I'm ready to work with the new V7.0.8 (new board, sr6.0 and mysql external db),

I try to place calles by adding entries in the CallQue and Porttuse tables. The calls do not start, Please find logs in attachement.

 

 

Thank you.

 

Mh

1108_1749_vgEngine.txt

[mehdi_h]

I have reinstalled the version that you provided which deals with mysql (VoiceGuide_7.0.7_fix_mysql. I thought it was in the generic one now) and I don't get the same log behaviour. Please see attachements

 

1108_1837_vgEngine.txt

[SupportTeam]

Lets look at resolving the issue with v7.0.8

 

Please install 7.0.8 and then in VG.INI set

 

VoiceGuide=20

 

then restart VoiceGuide, and let it run for a 10-20 seconds or so and then stop it./

 

Please post the vgEngine trace created and the Config.xml used.

[mehdi_h]

Hi,

 

Here are the log files that you have asked. thanks indeed i'm blocked at this stage right now.

 

Mh

log.zip

[mehdi_h]

for additional information, in the config.xml I saw that when using the connection account "moussa" there was a problem (see logs in the previous post)

Then I have tried using id=root and it seems that the connection has been established. but I have other ERROR in the log file now

 

So please find in attachment the new log files

 

log.zip

[mehdi_h]

Further information. when using the loader to add en tries in Mysql database, all fields are set to "NULL". see attachments.

 

1109_1312_vgDialListLoad.txt

[SupportTeam]

Please download and install this version:

 

[old link removed]

[mehdi_h]

Hi Thank you.

 

I'm doing tests right now. the first test show that when using the VG call loader all fields are set to NULL.

 

Please see attachments.

 

1110_1023_vgDialListLoad.txt

[mehdi_h]

Hi I have done tests. No success,

 

-When adding entries in CallQue with VG call loarder all fields are set to NULL

- When adding manually entries using sql commands, the entry appear in the VG call loader but outbound call are not made . It seems that [ActivateTime] does not have the right format.

Please find logs in attachement

 

 

Tkx

PS: If needed you can access my PC.

log.zip

[mehdi_h]

an idea..

 

I have seen in the logs in this request :

SELECT CallQue.ID, CallQue.GUID, CallQue.PhoneNumber, CallQue.PhoneNumberPrefix, CallQue.ActivateTime, CallQue.CampaignName, CallQue.Priority, CallQue.OnAnswerLive, CallQue.OnAnswerMachine, CallQue.OnAnswerFax, CallQue.OnNotAnswered, CallQue.OnRetriesExhausted, CallQue.AnswerTimeout, CallQue.RetriesLeft, CallQue.RetriesDelay, CallQue.RV, CallQue.CallOptions, CallQue.EscalationCalls FROM CallQue WHERE ( (CallQue.ActivateTime <= @dateNow) AND ((CallQue.TimeStart_Mon <= 1138) AND (CallQue.TimeStop_Mon > 1138)) ) LIMIT 1;]

 

That you are using @dateNow, thiss returns nothing. When replacing @dateNow by Now(), it returns entries...

 

Tkx

 

[mehdi_h]

After other tests. here is the new log file

1110_1304_vgEngine.zip

[SupportTeam]

We tested this latest version with MySQL and it loaded and read calls from the MySQL Dialer database fine.

 

Could you please try stopping VoiceGuide and then dropping all the tables in your database and then just start the Telephone Number Loader app. It will create the tables and you can then try loading calls using same app. Please post trace of that being made.

 

Once the Telephone Number Loader app is working and is displaying the loaded calls then move onto starting VoiceGuide to get the calls made.

[mehdi_h]

hi I'm really sorry, but still no entries loaded except NULL fields

here is the log file.

 

I'm in front of the PC for any other advise.

Ps: I'm using WampServer Version 2.0 (APACHE/PHP/MYSQL)

 

1110_2000_vgDialListLoad.zip

[mehdi_h]

got some news!

 

I saw that I need to install Mysql connector 5.2.3 and not 5.0.9 (link in your post). Now I can load correctly the entries in the database. But calls are still not placed. Please find new logs

 

Mh

 

 

log.zip

[SupportTeam]

The Dial List Loader trace shows that the call you loaded had a call window which starts at 8PM on most days, and current time on machine is 9AM...

[mehdi_h]

GOT IT!!!!!

Indeed, I got French date settings. But on my system it did not work because of the Mysql connector version. That was the probleme for call loading.

 

Tks

 

til the next issue :-)

[mehdi_h]

let me thank you again for your great amazing support and I know what I'm talking about..

[SupportTeam]

let me thank you again for your great amazing support

Thank You