Hello, we are trying to connect our Kamailio SIP to an Oracle database. We tried both db_oracle and db_unixodbc modules to connect to the DB. With db_oracle, Kamailio core dumps when trying to access the active_watchers table (caused by the oracle CLOB?). Here is the kamailio logs: Apr 29 13:54:53 newvm1 /usr/sbin/kamailio[23665]: INFO: <core> [db_query.c:131]: db_do_query_internal(): DB-QUERY: select presentity_uri,callid,to_tag ,from_tag,event from active_watchers where updated=:1 AND event=:2 Apr 29 13:54:53 newvm1 /usr/sbin/kamailio[23665]: INFO: db_oracle [res.c:238]: get_columns(): COLUMN PRESENTITY_URI TYPE 4 SIZE 129 Apr 29 13:54:53 newvm1 /usr/sbin/kamailio[23665]: INFO: db_oracle [res.c:238]: get_columns(): COLUMN CALLID TYPE 4 SIZE 256 Apr 29 13:54:53 newvm1 /usr/sbin/kamailio[23665]: INFO: db_oracle [res.c:238]: get_columns(): COLUMN TO_TAG TYPE 4 SIZE 65 Apr 29 13:54:53 newvm1 /usr/sbin/kamailio[23665]: INFO: db_oracle [res.c:238]: get_columns(): COLUMN FROM_TAG TYPE 4 SIZE 65 Apr 29 13:54:53 newvm1 /usr/sbin/kamailio[23665]: INFO: db_oracle [res.c:238]: get_columns(): COLUMN EVENT TYPE 4 SIZE 65 Apr 29 13:54:53 newvm1 /usr/sbin/kamailio[23665]: INFO: db_oracle [res.c:378]: get_rows(): OCIStmtFetch2() returned 100 Apr 29 13:54:53 newvm1 /usr/sbin/kamailio[23667]: CRITICAL: <core> [pass_fd.c:275]: receive_fd(): EOF on 16 Apr 29 13:54:53 newvm1 /usr/sbin/kamailio[23667]: CRITICAL: <core> [pass_fd.c:275]: receive_fd(): EOF on 14 What is really strange is that this table is accessed several times before the crash, without any problem. The other point is that this table is absolutely empty... With db_unixodbc, all requests having dates check fail because because the date format is not understood by oracle. The date should be converted using the oracle to_date(date, format) funtion to work... Here are the Kamilio traces: Apr 29 14:10:38 newvm1 /usr/sbin/kamailio[17021]: ERROR: usrloc [dlist.c:183]: get_all_db_ucontacts(): query error Apr 29 14:10:38 newvm1 /usr/sbin/kamailio[17021]: ERROR: nathelper [nathelper.c:2089]: nh_timer(): failed to fetch contacts Apr 29 14:10:39 newvm1 /usr/sbin/kamailio[17021]: ERROR: db_unixodbc [dbase.c:133]: db_unixodbc_submit_query(): rv=-1. Query= select received,contact,socket,cflags,path,ruid from$ Apr 29 14:10:39 newvm1 /usr/sbin/kamailio[17021]: ERROR: db_unixodbc [connection.c:220]: db_unixodbc_extract_error(): unixodbc:SQLExecDirect=HY000:1:933:[Oracle][ODBC][Ora]ORA-00$ Apr 29 14:10:39 newvm1 /usr/sbin/kamailio[17021]: ERROR: <core> [db_query.c:132]: db_do_query_internal(): error while submitting query I tried the option modparam("db_unixodbc", "use_escape_common", 1), but without success. Has anyone been able to make Kamailio 4.4.0 work with Oracle 11g, either with db_oracle or with db_unixodbc module? Regards Giovanni -- View this message in context: http://sip-router.1086192.n5.nabble.com/Kamailio-and-Oracle-database-tp1479… Sent from the Users mailing list archive at Nabble.com. _______________________________________________ SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list sr-users(a)lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users

Hello Daniel, thx for your quick answer. After having failed with db_oracle module, we effectively tried db_unixodbc. But even with unixodbc, we got some strange errors when kamailio tries to do queries including dates in the where clause: Example of an error reported by kamailio: May 2 08:30:14 newvm1 /usr/sbin/kamailio[26298]: ERROR: nathelper [nathelper.c:2089]: nh_timer(): failed to fetch contacts May 2 08:30:15 newvm1 /usr/sbin/kamailio[26298]: ERROR: db_unixodbc [dbase.c:133]: db_unixodbc_submit_query(): rv=-1. Query= select received,contact,socket,cflags,path,ruid from location *where expires>''2016-05-02 06:30:15'' *AND partition=11 AND keepalive=1 May 2 08:30:15 newvm1 /usr/sbin/kamailio[26298]: ERROR: db_unixodbc [connection.c:220]: db_unixodbc_extract_error(): unixodbc:SQLExecDirect=HY000:1:933:[Oracle][ODBC][Ora]ORA-00933: SQL command not properly ended#012 May 2 08:30:15 newvm1 /usr/sbin/kamailio[26298]: ERROR: <core> [db_query.c:132]: db_do_query_internal(): error while submitting query As you can see, the date in the request seems has a double quote surrounding it : where expires>''2016-05-02 06:30:15''. In fact it is 2 times the character <'>. If I execute the above request in the isql tool provided by unixODBC, it also fails. If I remove the double quote around the date, it works: select received,contact,socket,cflags,path,ruid from location *where expires>'2016-05-02 06:30:15' *AND partition=11 AND keepalive=1; Do you have an idea on how to correct this problem? Thx Regards Giovanni -- View this message in context: http://sip-router.1086192.n5.nabble.com/Kamailio-and-Oracle-database-tp1479… Sent from the Users mailing list archive at Nabble.com. _______________________________________________ SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list sr-users(a)lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users

Hello, It is two single quote characters (‘). If I try with one double quote character (“), it also fails. The only way to make it work is to have one single quote character surrounding the date. Regards Giovanni *From:*sr-users [mailto:sr-users-bounces@lists.sip-router.org] *On Behalf Of *Daniel-Constantin Mierla *Sent:* lundi 2 mai 2016 09:49 *To:* Kamailio (SER) - Users Mailing List *Subject:* Re: [SR-Users] Kamailio and Oracle database Hello, are there two single quote characters or one double quotes character? Can you test if it works with double quotes instead of single quotes? Cheers, Daniel On 02/05/16 09:42, gmele wrote: Hello Daniel, thx for your quick answer. After having failed with db_oracle module, we effectively tried db_unixodbc. But even with unixodbc, we got some strange errors when kamailio tries to do queries including dates in the where clause: Example of an error reported by kamailio: May 2 08:30:14 newvm1 /usr/sbin/kamailio[26298]: ERROR: nathelper [nathelper.c:2089]: nh_timer(): failed to fetch contacts May 2 08:30:15 newvm1 /usr/sbin/kamailio[26298]: ERROR: db_unixodbc [dbase.c:133]: db_unixodbc_submit_query(): rv=-1. Query= select received,contact,socket,cflags,path,ruid from location *where expires>''2016-05-02 06:30:15'' *AND partition=11 AND keepalive=1 May 2 08:30:15 newvm1 /usr/sbin/kamailio[26298]: ERROR: db_unixodbc [connection.c:220]: db_unixodbc_extract_error(): unixodbc:SQLExecDirect=HY000:1:933:[Oracle][ODBC][Ora]ORA-00933: SQL command not properly ended#012 May 2 08:30:15 newvm1 /usr/sbin/kamailio[26298]: ERROR: <core> [db_query.c:132]: db_do_query_internal(): error while submitting query As you can see, the date in the request seems has a double quote surrounding it : where expires>''2016-05-02 06:30:15''. In fact it is 2 times the character <'>. If I execute the above request in the isql tool provided by unixODBC, it also fails. If I remove the double quote around the date, it works: select received,contact,socket,cflags,path,ruid from location *where expires>'2016-05-02 06:30:15' *AND partition=11 AND keepalive=1; Do you have an idea on how to correct this problem? Thx Regards Giovanni -- View this message in context: http://sip-router.1086192.n5.nabble.com/Kamailio-and-Oracle-database-tp1479… Sent from the Users mailing list archive at Nabble.com. _______________________________________________ SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list sr-users(a)lists.sip-router.org <mailto:sr-users@lists.sip-router.org> http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users -- Daniel-Constantin Mierla http://www.asipto.com http://twitter.com/#!/miconda <http://twitter.com/#%21/miconda> - http://www.linkedin.com/in/miconda Kamailio World Conference, Berlin, May 18-20, 2016 - http://www.kamailioworld.com

Thx Daniel! I will get the code from git. What do I need to recompile? Only unixodbc module? Any other lib? We are using installation done with the official rpm and would like to keep an environment as clean as possible and just change the modified libs. Thx for this quick support. Regards Giovanni *From:*Daniel-Constantin Mierla [mailto:miconda@gmail.com] *Sent:* lundi 2 mai 2016 10:49 *To:* Mele Giovanni; Kamailio (SER) - Users Mailing List *Subject:* Re: [SR-Users] Kamailio and Oracle database Hello, hopefully I found and fixed the issue. The usrloc was setting the value for time already quoted and then the db api was quoting again. I guess you were using db-only mode for usrloc. Can you try again with latest git branch 4.4 or master? Report back the results in order to know if the issue was fixed or not. Cheers, Daniel On 02/05/16 09:59, Mele Giovanni wrote: Hello, It is two single quote characters (‘). If I try with one double quote character (“), it also fails. The only way to make it work is to have one single quote character surrounding the date. Regards Giovanni *From:*sr-users [mailto:sr-users-bounces@lists.sip-router.org] *On Behalf Of *Daniel-Constantin Mierla *Sent:* lundi 2 mai 2016 09:49 *To:* Kamailio (SER) - Users Mailing List *Subject:* Re: [SR-Users] Kamailio and Oracle database Hello, are there two single quote characters or one double quotes character? Can you test if it works with double quotes instead of single quotes? Cheers, Daniel On 02/05/16 09:42, gmele wrote: Hello Daniel, thx for your quick answer. After having failed with db_oracle module, we effectively tried db_unixodbc. But even with unixodbc, we got some strange errors when kamailio tries to do queries including dates in the where clause: Example of an error reported by kamailio: May 2 08:30:14 newvm1 /usr/sbin/kamailio[26298]: ERROR: nathelper [nathelper.c:2089]: nh_timer(): failed to fetch contacts May 2 08:30:15 newvm1 /usr/sbin/kamailio[26298]: ERROR: db_unixodbc [dbase.c:133]: db_unixodbc_submit_query(): rv=-1. Query= select received,contact,socket,cflags,path,ruid from location *where expires>''2016-05-02 06:30:15'' *AND partition=11 AND keepalive=1 May 2 08:30:15 newvm1 /usr/sbin/kamailio[26298]: ERROR: db_unixodbc [connection.c:220]: db_unixodbc_extract_error(): unixodbc:SQLExecDirect=HY000:1:933:[Oracle][ODBC][Ora]ORA-00933: SQL command not properly ended#012 May 2 08:30:15 newvm1 /usr/sbin/kamailio[26298]: ERROR: <core> [db_query.c:132]: db_do_query_internal(): error while submitting query As you can see, the date in the request seems has a double quote surrounding it : where expires>''2016-05-02 06:30:15''. In fact it is 2 times the character <'>. If I execute the above request in the isql tool provided by unixODBC, it also fails. If I remove the double quote around the date, it works: select received,contact,socket,cflags,path,ruid from location *where expires>'2016-05-02 06:30:15' *AND partition=11 AND keepalive=1; Do you have an idea on how to correct this problem? Thx Regards Giovanni -- View this message in context: http://sip-router.1086192.n5.nabble.com/Kamailio-and-Oracle-database-tp1479… Sent from the Users mailing list archive at Nabble.com. _______________________________________________ SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list sr-users(a)lists.sip-router.org <mailto:sr-users@lists.sip-router.org> http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users -- Daniel-Constantin Mierla http://www.asipto.com http://twitter.com/#!/miconda <http://twitter.com/#%21/miconda> - http://www.linkedin.com/in/miconda Kamailio World Conference, Berlin, May 18-20, 2016 - http://www.kamailioworld.com -- Daniel-Constantin Mierla http://www.asipto.com http://twitter.com/#!/miconda <http://twitter.com/#%21/miconda> - http://www.linkedin.com/in/miconda Kamailio World Conference, Berlin, May 18-20, 2016 - http://www.kamailioworld.com

Hello Daniel, I patched our kamailio 4.4.0 with the following modified files: lib/srdb1/db_ut.h lib/srdb1/db_ut.c modules/urloc/dlist.c I recompiled and replaced libsrdb1.so.1.0 and usrloc.so. Now when kamailio starts, it still reports error: May 2 11:33:43 newvm1 /usr/sbin/kamailio[15899]: ERROR: db_unixodbc [dbase.c:133]: db_unixodbc_submit_query(): rv=-1. Query= *select received,contact,socket,cflags,path,ruid from location where expires>'2016-05-02 09:33:43* It seems that your patch has removed a part of the query: the query is missing the last quote of the date and the rest of the where clause: * ' *AND partition=11 AND keepalive=1* Regards Giovanni -- View this message in context: http://sip-router.1086192.n5.nabble.com/Kamailio-and-Oracle-database-tp1479… Sent from the Users mailing list archive at Nabble.com. _______________________________________________ SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list sr-users(a)lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users

Hello, it seems kamailio has a problem inserting a value in the oracle BLOB column BODY in the PRESENTITY table. I executed the command without the BLOB value and all is working fine. As soon as I try to insert the BLOB, it fails. Is there some incompatibility handling Oracle blobs? Regards Giovanni -- View this message in context: http://sip-router.1086192.n5.nabble.com/Kamailio-and-Oracle-database-tp1479… Sent from the Users mailing list archive at Nabble.com. _______________________________________________ SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list sr-users(a)lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users

Hello Daniel, I found the problem: kamctl tool for oracle created a BLOB for the BODY column of table PRESENTITY. I changed it to CLOB and now it works better.

I still have some errors in a query to replace watchers (see below). I'm trying to investigate the problem before boring you with that. Here is the the error I get: Query= replace watchers(presentity_uri,watcher_username,watcher_domain,event,status,inserted_time,reason ) values ('sip:frederic.mathys@ims-nagra.test','giovanni.mele','ims-nagra.test','presence',1,1462195290,'') May 2 15:21:30 newvm1 /usr/sbin/kamailio[560]: ERROR: db_unixodbc [connection.c:220]: db_unixodbc_extract_error(): unixodbc:SQLExecDirect=42000:1:900:[Oracle][ODBC][Ora]ORA-00900: invalid SQL statement#012 I fail to understand where is the where clause of the replace query...

Hello Daniel, I'm now investigating the presence notification module problem and the odbc query: replace watchers (presentity_uri,watcher_username,watcher_domain,event,status,inserted_time,reason ) values ('sip:xxx@zzzzzzz','yyyyy','zzzzzzzz,'presence',1,1462197118,''); This query will insert a new row if it doesn't exist and replace it if it exists in the table. This requests fails with unixodbc and oracle as it seems it is not supported. Can you confirm me if the odbc query replace is supported for oracle? Didn't find anything on the web.

Is there the possiblity to split this query in 2: first a select, then an insert or update?

Hello, on the master branch (development). Then the commits will be backported to stable branches using "git cherry-pick". An important detail is to format the commit log messages as recommended by the guidelines at: - https://www.kamailio.org/wiki/devel/github-contributions#commit_message_for… For example, if you committed to db_oracle module, then the commit message must start like: db_oracle: ... Cheers, Daniel On 09/05/16 16:36, gmele wrote: -- Daniel-Constantin Mierla http://www.asipto.com http://twitter.com/#!/miconda - http://www.linkedin.com/in/miconda Kamailio World Conference, Berlin, May 18-20, 2016 - http://www.kamailioworld.com

Hello Daniel, I see that the unixodbc changes have not been merged in branch 4.4. Should I have done something on my side to have it merged on the 4.4 branch? Thx Giovanni -- View this message in context: http://sip-router.1086192.n5.nabble.com/Kamailio-and-Oracle-database-tp1479… Sent from the Users mailing list archive at Nabble.com. _______________________________________________ SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list sr-users(a)lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users