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-tp14799... Sent from the Users mailing list archive at Nabble.com.

---

SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list sr-users@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users

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-tp147990p148005.html

Sent from the Users mailing list archive at Nabble.com.



_______________________________________________

SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list

sr-users@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

Hello,

you have to recompile everything, there was a patch in an internal library. You can also build the rpms by yourself.

Cheers, Daniel

On 02/05/16 10:53, Mele Giovanni wrote:

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-tp147990p148005.html

    Sent from the Users mailing list archive at Nabble.com.



    _______________________________________________

    SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list

    sr-users@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,

indeed, the length without quotes was not adjusted. I pushed another patch, can you try again?

Cheers, Daniel

On 02/05/16 11:42, gmele wrote:

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-tp14799... Sent from the Users mailing list archive at Nabble.com.

---

SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list sr-users@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-tp14799... Sent from the Users mailing list archive at Nabble.com.

Hello Daniel,

It seems oracle is expecting the body value converted as an hex string. i.e:

<?xml version="1.0" encoding="UTF-8"?><presence xmlns:dm="urn:ietf:params:xml:ns:pidf:data-model" xmlns:rpid="urn:ietf:params:xml:ns:pidf:rpid" entity="sip:giovanni.mele@ims-nagra.test" xmlns="urn:ietf:params:xml:ns:pidf"> <tuple id="d5zdxq"> <status> <basic>open</basic> </status> <contact priority="0.8">sip:giovanni.mele@ims-nagra.test</contact> <timestamp>2016-05-02T11:06:54Z</timestamp> </tuple></presence>

Should be

3c3f786d6c2076657273696f6e3d22312e302220656e636f64696e673d225554462d38223f3e3c70726573656e636520786d6c6e733a646d3d2275726e3a696574663a706172616d733a786d6c3a6e733a706964663a646174612d6d6f64656c2220786d6c6e733a727069643d2275726e3a696574663a706172616d733a786d6c3a6e733a706964663a727069642220656e746974793d227369703a67696f76616e6e692e6d656c6540696d732d6e616772612e746573742220786d6c6e733d2275726e3a696574663a706172616d733a786d6c3a6e733a70696466223e203c7475706c652069643d2264357a647871223e20203c7374617475733e2020203c62617369633e6f70656e3c2f62617369633e20203c2f7374617475733e20203c636f6e74616374207072696f726974793d22302e38223e7369703a67696f76616e6e692e6d656c6540696d732d6e616772612e746573743c2f636f6e746163743e20203c74696d657374616d703e323031362d30352d30325431313a30363a35345a3c2f74696d657374616d703e203c2f7475706c653e3c2f70726573656e63653e

If I execute the following query in isql:

insert into presentity (domain,username,event,etag,sender,body,received_time,priority,expires ) values ('ims-nagra.test','giovanni.mele','presence','a.1462186899.8822.1.0','','3c3f786d6c2076657273696f6e3d22312e302220656e636f64696e673d225554462d38223f3e3c70726573656e636520786d6c6e733a646d3d2275726e3a696574663a706172616d733a786d6c3a6e733a706964663a646174612d6d6f64656c2220786d6c6e733a727069643d2275726e3a696574663a706172616d733a786d6c3a6e733a706964663a727069642220656e746974793d227369703a67696f76616e6e692e6d656c6540696d732d6e616772612e746573742220786d6c6e733d2275726e3a696574663a706172616d733a786d6c3a6e733a70696466223e203c7475706c652069643d2264357a647871223e20203c7374617475733e2020203c62617369633e6f70656e3c2f62617369633e20203c2f7374617475733e20203c636f6e74616374207072696f726974793d22302e38223e7369703a67696f76616e6e692e6d656c6540696d732d6e616772612e746573743c2f636f6e746163743e20203c74696d657374616d703e323031362d30352d30325431313a30363a35345a3c2f74696d657374616d703e203c2f7475706c653e3c2f70726573656e63653e',1462187244,0,1462190844);

it works....

But if you convert it in hex string when inserting, I suppose you will have to do the reverse operation when reading it from the db...

Regards

Giovanni

From: sr-users [mailto:sr-users-bounces@lists.sip-router.org] On Behalf Of Daniel-Constantin Mierla Sent: lundi 2 mai 2016 13:57 To: Kamailio (SER) - Users Mailing List Subject: Re: [SR-Users] Kamailio and Oracle database

Hello,

I wrote in a previous email as well -- it seems that oracle doesn't like '\n' characters in the xml doc for presence. Can you check the docs for oracle and see if needs some special format in such case or maybe a special column definition? The xml is sent by the client, kamailio just has to store it in db. Cheers, Daniel On 02/05/16 13:25, gmele wrote:

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-tp14799...

Sent from the Users mailing list archive at Nabble.com.

_______________________________________________

SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list

sr-users@lists.sip-router.orgmailto: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://www.linkedin.com/in/miconda

Kamailio World Conference, Berlin, May 18-20, 2016 - http://www.kamailioworld.com

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?

Thx

Regards

Giovanni

-- View this message in context: http://sip-router.1086192.n5.nabble.com/Kamailio-and-Oracle-database-tp14799... Sent from the Users mailing list archive at Nabble.com.

Hello,

On 03/05/16 08:18, gmele wrote:

Hello Daniel,

I add a look to the db_unixodbc and the presence modules. To correct the ODBC replace query that is visibly not supported by Oracle, I would like to add a new parameter in the db_unixodbc module : replaceQuery = {0:1}. 0 = not supported, 1 = supported (default).

Now, in the presence module, I found this comment :

if (pa_dbf.replace != NULL) { if(pa_dbf.replace(pa_db, db_keys, db_vals, n_query_cols, 2, 0) < 0) ... } else { */* If you use insert() instead of replace() be prepared for some * DB error messages. There is a lot of time between the * query() that indicated there was no matching entry in the DB * and this insert(), so on a multi-user system it is entirely * possible (even likely) that a record will be added after the * query() but before this insert(). */* if(pa_dbf.insert(pa_db, db_keys, db_vals, n_query_cols )< 0)

What will happen if we do inserts only? Will the table grow? Is there a purge mechanism somewhere?

Would it be possible in the replace method to do an update and in case of error, do an insert?

What is your opinion?

you can add that parameter to control exporting the replace support - make a pull request on github and it will be merged. Name the parameter replace_query as the other parameters for db_unixodbc use the patterns with underscore and lower cases and format the message as suggested at:

- https://www.kamailio.org/wiki/devel/github-contributions#commit_message_form...

Cheers, Daniel

Hello Daniel,

I've updated the db_unixodbc module and tested it on our kamailio server: all is working fine now and the watcher table is correctly updated.

Now, I'd like to push my modification on github. I'm totally new in using github and need some help here.

I've created a github account, created a local kamailio repository, made the changes on the main branch and committed them locally. Now, when I want to push them on github, I get the following error:

$ git push error: The requested URL returned error: 403 Forbidden while accessing https://giovannimele@github.com/kamailio/kamailio.git/info/refs fatal: HTTP request failed

What should I do to correct this and be able to push my modifications on git?

Thx and sorry for this stupid question

Regards

Giovanni

-- View this message in context: http://sip-router.1086192.n5.nabble.com/Kamailio-and-Oracle-database-tp14799... Sent from the Users mailing list archive at Nabble.com.

Hello,

thx, I'll do it!

Tell me: do you want me to work on the main branch or on version 4.4.0?

Regards

Giovanni

-- View this message in context: http://sip-router.1086192.n5.nabble.com/Kamailio-and-Oracle-database-tp14799... Sent from the Users mailing list archive at Nabble.com.

To conclude the thread -- db_unixodbc got support for alternative "replace"query implementation that works for oracle, the patch being merged.

Cheers, Daniel

On 09/05/16 16:39, Daniel-Constantin Mierla wrote:

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_form...

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:

...

Hello,

thx, I'll do it!

Tell me: do you want me to work on the main branch or on version 4.4.0?

Regards

Giovanni

-- View this message in context: http://sip-router.1086192.n5.nabble.com/Kamailio-and-Oracle-database-tp14799... Sent from the Users mailing list archive at Nabble.com.

---

SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list 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://www.linkedin.com/in/miconda Kamailio World Conference, Berlin, May 18-20, 2016 - http://www.kamailioworld.com

Hello Giovanni,

it was a bit too late to get it in 4.4.1 -- the main reason for that is that we do not add new features in stable branches and also avoid breaking config file and database compatibility. This is not really a new feature, but it introduces a new config mod param and didn't want to rush backporting it without letting brew a bit on devel branch and be sure it doesn't have side effects. It will get in 4.4 branch soon and it will be part of 4.4.2.

Cheers, Daniel

On 11/05/16 08:25, gmele wrote:

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-tp14799... Sent from the Users mailing list archive at Nabble.com.

---

SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list sr-users@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users

Hello,

On 02/05/16 15:25, gmele wrote:

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.

OK, good to know -- perhaps we have to change the definition for oracle sql scripts.

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...

Does oracle support replace sql statement?

Cheers, Daniel