lorpar Report post Posted 03/12/2014 07:31 PM I'm now working on a supposedly very simple insert, using the DB Query module availoable in VG script desginer. First of all I need to state that I work in a Win Server 2008 R4 (X64) and MySQL, but I have MySql ODBC 32 bit drivers installed and the DSN was created using SysWOW64/ODBC Admin (meaning that it is a 32 bit DSN). The DSN is perfectly visible/listed in ODBC Admin and is working fine (when using the test option). I tried to put serveral kinds of "Connect Strings", being (in the order) the following: DSN=MyDSNConnect_32 ODBC;DSN=MyDSNConnect_32; ODBC;DSN=MyDSNConnect_32;UID=root;PWD=mypass; DSN=MyDSNConnect_32;UID=root;PWD=mypass; In no case I could connect with my DB. Tha last message (I can fin in VGEngine log) is that: "DSN not specified, using a DSN-less ODBC connection". So my question is. Which is the correct "Connection String" in case I use MySQL ODBC, and I have a 32 DSN correctly set up using 32 bit ODBC driver ?? The funny thing is that the vbscript module can see (and now works fine) the DSNs without problems and works with it. Share this post Link to post
SupportTeam Report post Posted 03/12/2014 08:36 PM Could you please post the vgEngine log extracts from your system which capture the system startup and the running of the Database Query module. We can then see what is happening on your system. Share this post Link to post
lorpar Report post Posted 03/12/2014 08:47 PM Attached please find: 1) screen capture of my DB Query Module 2) VGEngine.log (zipped) Thanks LP 0312_1742_vgEngine.zip Share this post Link to post
SupportTeam Report post Posted 03/12/2014 09:31 PM Is the DSN set up as a "User DSN" or "System or File DSN" ? Can you please try setting up the DSNs under both User and System/File types, and have both types set up in both 32 bit and 64 bit ODBC managers. Share this post Link to post
lorpar Report post Posted 03/12/2014 09:48 PM It was as User DNS. Now I set up also as System DNS (with same DSN name, thus I 32 bit user DSN and 32 bit system DSN with same name). Still gives some error ... attached please find new VGEngine, now with both DSN enabled. 0312_1840_vgEngine.zip Share this post Link to post
lorpar Report post Posted 03/12/2014 10:10 PM Sorry I've seen now that you required to set up also 64 bit ODBC DNS ... this I did not do. Is it necessary taking in account that Voiceguide is a 32 bit app ? Share this post Link to post
lorpar Report post Posted 03/12/2014 10:22 PM Attached screen capture of my 32bit and 64bit ODBC Administrators ... with list of enabled DSN. The 32 bit one was run starting from syswow64 directory. The 64 bit one was started from admin tools menu. Hope this will be useful. Share this post Link to post
SupportTeam Report post Posted 03/13/2014 12:43 AM Looks like the DSN connection issue is now resolved. We can see in the trace that VoiceGuide is connecting to the ODBC data source. There now seems to be a problem with the SQL and the use the <BLOB> within the SQL. From 0312_1840_vgEngine trace: 184051.623 7 3 1 state [Save 1] DB Query 184051.624 7 3 1 rv replace start ------------------------------ INSERT INTO denuncias (codice,data,senha,tratada,resposta1) VALUES ('$RV[RecordCode]', '$RV[DataHj]', '$RV[SenhaRem]',0,<BLOB><file>$RV[Rec]</file></BLOB>); ------------------------------ 184051.629 7 rv dbg rv_retrieve did not find [Rec] ( 52 65 63) 184051.629 7 3 1 rv replace end [INSERT INTO denuncias (codice,data,senha,tratada,resposta1) VALUES ('621', '2014-3-12 18:40:43', '89439',0,<BLOB><file></file></BLOB>); ] 184051.629 7 3 1 db strDatabaseName=[] strDbConnectString=[DSN=DiskFraude_Local_32;Uid=root;Pwd=MYPASS;] sAdoNetFactoriesAvailable=[|System.Data.Odbc|System.Data.OleDb|System.Data.OracleClient|System.Data.SqlClient|System.Data.SQLite|Npgsql|] 184051.629 7 3 1 db did not find ado.net data provider : 184051.629 7 3 1 db PlayOnHoldFile 184051.629 7 3 1 moh file not specified 184051.629 7 3 1 db using ado.net ODBC data provider : OdbcConnection/OdbcCommand/etc 184051.629 7 3 1 db suggested ODBC connect string format if System DSN defined: DSN=ddd;Uid=xxx;Pwd=yyy; 184051.630 7 3 1 db odbc RunQuery_Odbc [Save 1], db=[], connect=[DSN=DiskFraude_Local_32;Uid=root;Pwd=MYPASS;], sql=[INSERT INTO denuncias (codice,data,senha,tratada,resposta1) VALUES ('621', '2014-3-12 18:40:43', '89439',0,<BLOB><file></file></BLOB>); ] 184051.631 7 3 1 db odbc RunQuery_OleDb blob: file=[], dbcolumn=[] 184051.631 7 3 1 odbc DSN not specified, using a DSN-less ODBC connection. 184051.631 7 3 1 db odbc thread init (threadpool) 184051.631 7 3 1 db odbc thread started (threadpool) 184051.631 7 3 1 t timer set 30 sec : EV_TIMEOUT_HANGUP 184051.634 4 3 1 db odbc connection create call. 184051.634 4 3 1 db odbc connection open call. 184051.649 4 3 1 db odbc connection open returned. 184051.649 4 3 1 db odbc m_strSQL=[INSERT INTO denuncias (codice,data,senha,tratada,resposta1) VALUES ('621', '2014-3-12 18:40:43', '89439',0,<BLOB><file></file></BLOB>); ] 184051.688 4 3 1 ERROR v7.4.5182.41999 (2014-03-10 23:19:58.19) ConnectAndRun_Odbc : ERROR [42000] [MySQL][ODBC 5.2(w) Driver][mysqld-5.6.16-log]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 '<BLOB><file></file></BLOB>)' at line 1 184055.930 7 3 1 state [Save 1] DB Query 184055.930 7 3 1 rv replace start ------------------------------ INSERT INTO denuncias (codice,data,senha,tratada,resposta1) VALUES ('$RV[RecordCode]', '$RV[DataHj]', '$RV[SenhaRem]',0,<BLOB><file>$RV[Rec]</file></BLOB>); ------------------------------ 184055.930 7 rv dbg rv_retrieve did not find [Rec] ( 52 65 63) 184055.930 7 3 1 rv replace end [INSERT INTO denuncias (codice,data,senha,tratada,resposta1) VALUES ('621', '2014-3-12 18:40:43', '89439',0,<BLOB><file></file></BLOB>); ] 184055.930 7 3 1 db strDatabaseName=[] strDbConnectString=[DSN=DiskFraude_Local_32;Uid=root;Pwd=MYPASS;] sAdoNetFactoriesAvailable=[|System.Data.Odbc|System.Data.OleDb|System.Data.OracleClient|System.Data.SqlClient|System.Data.SQLite|Npgsql|] 184055.930 7 3 1 db did not find ado.net data provider : 184055.930 7 3 1 db PlayOnHoldFile 184055.930 7 3 1 moh file not specified 184055.930 7 3 1 db using ado.net ODBC data provider : OdbcConnection/OdbcCommand/etc 184055.930 7 3 1 db suggested ODBC connect string format if System DSN defined: DSN=ddd;Uid=xxx;Pwd=yyy; 184055.930 7 3 1 db odbc RunQuery_Odbc [Save 1], db=[], connect=[DSN=DiskFraude_Local_32;Uid=root;Pwd=MYPASS;], sql=[INSERT INTO denuncias (codice,data,senha,tratada,resposta1) VALUES ('621', '2014-3-12 18:40:43', '89439',0,<BLOB><file></file></BLOB>); ] 184055.930 7 3 1 db odbc RunQuery_OleDb blob: file=[], dbcolumn=[] 184055.930 7 3 1 odbc DSN not specified, using a DSN-less ODBC connection. 184055.930 7 3 1 db odbc thread init (threadpool) 184055.930 7 3 1 db odbc thread started (threadpool) 184055.930 25 3 1 db odbc connection create call. 184055.930 7 3 1 t timer set 30 sec : EV_TIMEOUT_HANGUP 184055.930 25 3 1 db odbc connection open call. 184055.932 25 3 1 db odbc connection open returned. 184055.932 25 3 1 db odbc m_strSQL=[INSERT INTO denuncias (codice,data,senha,tratada,resposta1) VALUES ('621', '2014-3-12 18:40:43', '89439',0,<BLOB><file></file></BLOB>); ] 184055.932 25 3 1 ERROR v7.4.5182.41999 (2014-03-10 23:19:58.19) ConnectAndRun_Odbc : ERROR [42000] [MySQL][ODBC 5.2(w) Driver][mysqld-5.6.16-log]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 '<BLOB><file></file></BLOB>)' at line 1 184101.960 7 3 1 state [Save 1] DB Query 184101.960 7 3 1 rv replace start ------------------------------ INSERT INTO denuncias (codice,data,senha,tratada,resposta1) VALUES ('$RV[RecordCode]', '$RV[DataHj]', '$RV[SenhaRem]',0,<BLOB><file>$RV[Rec]</file></BLOB>); ------------------------------ 184101.964 7 rv dbg rv_retrieve did not find [Rec] ( 52 65 63) 184101.964 7 3 1 rv replace end [INSERT INTO denuncias (codice,data,senha,tratada,resposta1) VALUES ('621', '2014-3-12 18:40:43', '89439',0,<BLOB><file></file></BLOB>); ] 184101.964 7 3 1 db strDatabaseName=[] strDbConnectString=[DSN=DiskFraude_Local_32;Uid=root;Pwd=MYPASS;] sAdoNetFactoriesAvailable=[|System.Data.Odbc|System.Data.OleDb|System.Data.OracleClient|System.Data.SqlClient|System.Data.SQLite|Npgsql|] 184101.964 7 3 1 db did not find ado.net data provider : 184101.964 7 3 1 db PlayOnHoldFile 184101.964 7 3 1 moh file not specified 184101.964 7 3 1 db using ado.net ODBC data provider : OdbcConnection/OdbcCommand/etc 184101.964 7 3 1 db suggested ODBC connect string format if System DSN defined: DSN=ddd;Uid=xxx;Pwd=yyy; 184101.964 7 3 1 db odbc RunQuery_Odbc [Save 1], db=[], connect=[DSN=DiskFraude_Local_32;Uid=root;Pwd=MYPASS;], sql=[INSERT INTO denuncias (codice,data,senha,tratada,resposta1) VALUES ('621', '2014-3-12 18:40:43', '89439',0,<BLOB><file></file></BLOB>); ] 184101.964 7 3 1 db odbc RunQuery_OleDb blob: file=[], dbcolumn=[] 184101.964 7 3 1 odbc DSN not specified, using a DSN-less ODBC connection. 184101.964 7 3 1 db odbc thread init (threadpool) 184101.964 7 3 1 db odbc thread started (threadpool) 184101.964 4 3 1 db odbc connection create call. 184101.964 4 3 1 db odbc connection open call. 184101.964 7 3 1 t timer set 30 sec : EV_TIMEOUT_HANGUP 184101.965 4 3 1 db odbc connection open returned. 184101.965 4 3 1 db odbc m_strSQL=[INSERT INTO denuncias (codice,data,senha,tratada,resposta1) VALUES ('621', '2014-3-12 18:40:43', '89439',0,<BLOB><file></file></BLOB>); ] 184101.966 4 3 1 ERROR v7.4.5182.41999 (2014-03-10 23:19:58.19) ConnectAndRun_Odbc : ERROR [42000] [MySQL][ODBC 5.2(w) Driver][mysqld-5.6.16-log]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 '<BLOB><file></file></BLOB>)' at line 1 and previously the error (as seen in 0312_1742_vgEngine trace) was: 174239.355 4 3 1 db odbc connection create call. 174239.356 4 3 1 db odbc connection open call. 174239.360 4 3 1 ERROR v7.4.5182.41999 (2014-03-10 23:19:58.19) ConnectAndRun_Odbc init : ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified We will look further into use of BLOB with ODBC/MySQL and advise. Share this post Link to post
SupportTeam Report post Posted 03/13/2014 12:50 AM Looks like BLOB inserts/retrieval is only supported when connecting to databases using ADO.NET Data Providers. Please connect to your database using the ADO.NET Data Provider and you should then be able to insert and retrieve BLOB data. Share this post Link to post
lorpar Report post Posted 03/13/2014 01:22 AM Sirs, I need to observe that the line you mentioned (which I also noted) is using and reporting an ODBC driver which is not installed in my system (MySQL ODBC 5.2(w)) ... and this is a safe way to get an error. On my system I just have ODBC 5.2 Unicode (which is newer, I was using ver. 5.2w last year). Furthermore it seems to me that the log still reports the message "DSN not specified, using a DSN-less ODBC connection." which let me suppose that it is still not finding DSN, for some unknown reason. Anyway I' Share this post Link to post
SupportTeam Report post Posted 03/13/2014 01:45 AM All database connections made in both uploaded traces connect using the DSN and through the Windows' ODBC system. The log entry that you mention is misleading in this case. We will have this log entry modified in future releases. VoiceGuide does not install any ODBC drivers. Share this post Link to post
lorpar Report post Posted 03/13/2014 02:05 AM OK ... Then I downloaded (from mysql site) and installed the last version of .net connector (I did not have it installed since was just using ODBC). I changed the connection configuration of my DB Query module (see image attached) in order to use ADO according to the instructions in your help page, I saved the new script and turned on VG IVR. I called using linphone with direct IP (it's my way to test the script) ... and still not working. The log says something about a mysql syntax error ... which I can not find (my Insert is taked exactly from your help examples). Attached please find the image of my new DB Query module and the new VGEngine log file, now using ADO.NET ... 0312_2255_vgEngine.zip Share this post Link to post
SupportTeam Report post Posted 03/13/2014 03:12 AM Trace shows that Result Variable $RV[Rec] is not defined. From looking at the call it looks like the recording's filename was saved in $RV[Resposta 1] So the SQL should be: INSERT INTO denuncias (codice,data,senha,tratada,resposta1) VALUES ('$RV[RecordCode]', '$RV[DataHj]', '$RV[senhaRem]',0,<BLOB><file>$RV[Resposta 1]</file></BLOB>); Please try using that SQL and if you still experience problems with BLOB insert please start a new thread on that topic and post associated vgEngine trace files. 225522.882 7 3 1 state [Save 1] DB Query 225522.882 7 3 1 rv replace start ------------------------------ INSERT INTO denuncias (codice,data,senha,tratada,resposta1) VALUES ('$RV[RecordCode]', '$RV[DataHj]', '$RV[SenhaRem]',0,<BLOB><file>$RV[Rec]</file></BLOB>); ------------------------------ 225522.888 7 rv dbg rv_retrieve did not find [Rec] ( 52 65 63) 225522.888 7 3 1 rv replace end [INSERT INTO denuncias (codice,data,senha,tratada,resposta1) VALUES ('625', '2014-3-12 22:55:14', '19819',0,<BLOB><file></file></BLOB>); ] 225522.888 7 3 1 db strDatabaseName=[MySql.Data.MySqlClient] strDbConnectString=[Server=localhost;Database=diskfraude_voip;Uid=root;Pwd=MYPASS;] sAdoNetFactoriesAvailable=[|System.Data.Odbc|System.Data.OleDb|System.Data.OracleClient|System.Data.SqlClient|MySql.Data.MySqlClient|System.Data.SQLite|Npgsql|] 225522.888 7 3 1 db PlayOnHoldFile 225522.888 7 3 1 moh file not specified 225522.888 7 3 1 db using ado.net data provider factory : MySql.Data.MySqlClient 225522.889 7 3 1 db fctry RunQuery_AdoNetFactory Save 1, MySql.Data.MySqlClient, Server=localhost;Database=diskfraude_voip;Uid=root;Pwd=MYPASS;, INSERT INTO denuncias (codice,data,senha,tratada,resposta1) VALUES ('625', '2014-3-12 22:55:14', '19819',0,<BLOB><file></file></BLOB>); 225522.890 7 3 1 db fctry RunQuery_OleDb blob: file=[], dbcolumn=[] 225522.890 7 3 1 db fctry thread init (threadpool) 225522.890 7 3 1 db fctry thread started (threadpool) 225522.890 7 3 1 t timer set 30 sec : EV_TIMEOUT_HANGUP 225522.895 24 3 1 db fctry connection create call. 225522.912 24 3 1 db fctry connection open call. 225523.023 24 3 1 db fctry connection open returned. 225523.024 24 3 1 db fctry m_strSQL=[INSERT INTO denuncias (codice,data,senha,tratada,resposta1) VALUES ('625', '2014-3-12 22:55:14', '19819',0,<BLOB><file></file></BLOB>); ] 225523.028 24 3 1 ERROR v7.4.5182.41999 (2014-03-10 23:19:58.19) ConnectAndRun_AdoNetFactory : 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 '<BLOB><file></file></BLOB>)' at line 1 Share this post Link to post
lorpar Report post Posted 03/13/2014 12:15 PM Ok ... working now, but just with ADO.NET ! Thanks Share this post Link to post
