Hi list,
Perhaps this fits better into a bug report, but I'd like to know if somebody out there experienced a similar issue. I am executing a "select count" query using the avp_db_query function, but it is not storing the results in any avp. Other queries are working fine. I use db_mysql connected with a mysql 5.1 server.
I have tested this with kamailio-3.1.0 and kamailio-3.1.3, both didn't work. In an old box (version 1.3.x), the same query was returning the correct value.
--------------------------------------------------- Here is the case which is not working:
Code: avp_delete("$avp(s:count)"); $var(ret) = avp_db_query("SELECT count(*) FROM subscriber where username='foo'", "$avp(s:count)"); xlog("L_INFO", "var(ret)=$var(ret) avp(s:count)=$avp(s:count)");
Output: INFO: <script>: var(ret)=1 avp(s:count)=<null>
(notice the return code 1, which means that the query was successfull and there should be some output value stored in the avp, but it is <null>)
---------------------------------------------------
And here is one case which works as expected:
$var(ret) = avp_db_query("SELECT id FROM subscriber where username='foo'", "$avp(s:count)");
Output: INFO: <script>: var(ret)=1 avp(s:count)=11
---------------------------------------------------
Thanks in advance for any feedback.
Best regards, Ricardo Andrade.
Hello,
On 6/29/11 7:35 PM, Andrade Ricardo (CI/AFU1) wrote:
Hi list,
Perhaps this fits better into a bug report, but I'd like to know if somebody out there experienced a similar issue. I am executing a "select count" query using the avp_db_query function, but it is not storing the results in any avp. Other queries are working fine. I use db_mysql connected with a mysql 5.1 server.
I have tested this with kamailio-3.1.0 and kamailio-3.1.3, both didn't work. In an old box (version 1.3.x), the same query was returning the correct value.
Here is the case which is not working:
Code: avp_delete("$avp(s:count)"); $var(ret) = avp_db_query("SELECT count(*) FROM subscriber where username='foo'", "$avp(s:count)"); xlog("L_INFO", "var(ret)=$var(ret) avp(s:count)=$avp(s:count)");
Output: INFO:<script>: var(ret)=1 avp(s:count)=<null>
(notice the return code 1, which means that the query was successfull and there should be some output value stored in the avp, but it is<null>)
And here is one case which works as expected:
$var(ret) = avp_db_query("SELECT id FROM subscriber where username='foo'", "$avp(s:count)");
Output: INFO:<script>: var(ret)=1 avp(s:count)=11
Thanks in advance for any feedback.
It might be related to the type of the value returned by mysql library for 'count(*)' - the avps support only integer or string values.
Have you tried also with sql_query(), is it giving the same result?
Cheers, Daniel
On Wednesday 29 June 2011, Andrade Ricardo (CI/AFU1) wrote:
Perhaps this fits better into a bug report, but I'd like to know if somebody out there experienced a similar issue. I am executing a "select count" query using the avp_db_query function, but it is not storing the results in any avp. Other queries are working fine. I use db_mysql connected with a mysql 5.1 server.
I have tested this with kamailio-3.1.0 and kamailio-3.1.3, both didn't work. In an old box (version 1.3.x), the same query was returning the correct value.
Here is the case which is not working:
Code: avp_delete("$avp(s:count)"); $var(ret) = avp_db_query("SELECT count(*) FROM subscriber where username='foo'", "$avp(s:count)"); xlog("L_INFO", "var(ret)=$var(ret) avp(s:count)=$avp(s:count)");
Output: INFO: <script>: var(ret)=1 avp(s:count)=<null>
(notice the return code 1, which means that the query was successfull and there should be some output value stored in the avp, but it is <null>)
The return type of the COUNT() function is a BIGINT, which is ignored in kamailio. The attached patch converts the lower 32 bits of the result to an int usable by kamailio. I'll push this eventually.
On 7/5/11 11:39 AM, Alex Hermann wrote:
On Wednesday 29 June 2011, Andrade Ricardo (CI/AFU1) wrote:
Perhaps this fits better into a bug report, but I'd like to know if somebody out there experienced a similar issue. I am executing a "select count" query using the avp_db_query function, but it is not storing the results in any avp. Other queries are working fine. I use db_mysql connected with a mysql 5.1 server.
I have tested this with kamailio-3.1.0 and kamailio-3.1.3, both didn't work. In an old box (version 1.3.x), the same query was returning the correct value.
Here is the case which is not working:
Code: avp_delete("$avp(s:count)"); $var(ret) = avp_db_query("SELECT count(*) FROM subscriber where username='foo'", "$avp(s:count)"); xlog("L_INFO", "var(ret)=$var(ret) avp(s:count)=$avp(s:count)");
Output: INFO:<script>: var(ret)=1 avp(s:count)=<null>
(notice the return code 1, which means that the query was successfull and there should be some output value stored in the avp, but it is<null>)
The return type of the COUNT() function is a BIGINT, which is ignored in kamailio. The attached patch converts the lower 32 bits of the result to an int usable by kamailio. I'll push this eventually.
Thanks for quick reaction and patch. Go ahead and commit it.
Cheers, Daniel
Does sqlops handle bigint these days? It's the same srdb1 API for Pg as avp_db_query(), right? So perhaps that's the wrong question.
-- Alex Balashov - Principal Evariste Systems LLC 260 Peachtree Street NW Suite 2200 Atlanta, GA 30303 Tel: +1-678-954-0670 Fax: +1-404-961-1892 Web: http://www.evaristesys.com/
On Jul 5, 2011, at 5:51 AM, Daniel-Constantin Mierla miconda@gmail.com wrote:
On 7/5/11 11:39 AM, Alex Hermann wrote:
On Wednesday 29 June 2011, Andrade Ricardo (CI/AFU1) wrote:
Perhaps this fits better into a bug report, but I'd like to know if somebody out there experienced a similar issue. I am executing a "select count" query using the avp_db_query function, but it is not storing the results in any avp. Other queries are working fine. I use db_mysql connected with a mysql 5.1 server.
I have tested this with kamailio-3.1.0 and kamailio-3.1.3, both didn't work. In an old box (version 1.3.x), the same query was returning the correct value.
Here is the case which is not working:
Code: avp_delete("$avp(s:count)"); $var(ret) = avp_db_query("SELECT count(*) FROM subscriber where username='foo'", "$avp(s:count)"); xlog("L_INFO", "var(ret)=$var(ret) avp(s:count)=$avp(s:count)");
Output: INFO:<script>: var(ret)=1 avp(s:count)=<null>
(notice the return code 1, which means that the query was successfull and there should be some output value stored in the avp, but it is<null>)
The return type of the COUNT() function is a BIGINT, which is ignored in kamailio. The attached patch converts the lower 32 bits of the result to an int usable by kamailio. I'll push this eventually.
Thanks for quick reaction and patch. Go ahead and commit it.
Cheers, Daniel
-- Daniel-Constantin Mierla -- http://www.asipto.com http://linkedin.com/in/miconda -- http://twitter.com/miconda
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
On 7/5/11 12:04 PM, Alex Balashov wrote:
Does sqlops handle bigint these days? It's the same srdb1 API for Pg as avp_db_query(), right? So perhaps that's the wrong question.
sqlops handle bigint but looking in the code it converts it to string. Then it can be converted to int via transformation {s.int}. Hmm, wonder which is the better approach...
The difference is that avp_query() stores directly the result values in avps, while sqlops uses an internal structure which is accessed by $dbr(...) variables. For now it tries to convert to int/string, but it is more flexible overall.
Cheers, Daniel
-- Alex Balashov - Principal Evariste Systems LLC 260 Peachtree Street NW Suite 2200 Atlanta, GA 30303 Tel: +1-678-954-0670 Fax: +1-404-961-1892 Web: http://www.evaristesys.com/
On Jul 5, 2011, at 5:51 AM, Daniel-Constantin Mierlamiconda@gmail.com wrote:
On 7/5/11 11:39 AM, Alex Hermann wrote:
On Wednesday 29 June 2011, Andrade Ricardo (CI/AFU1) wrote:
Perhaps this fits better into a bug report, but I'd like to know if somebody out there experienced a similar issue. I am executing a "select count" query using the avp_db_query function, but it is not storing the results in any avp. Other queries are working fine. I use db_mysql connected with a mysql 5.1 server.
I have tested this with kamailio-3.1.0 and kamailio-3.1.3, both didn't work. In an old box (version 1.3.x), the same query was returning the correct value.
Here is the case which is not working:
Code: avp_delete("$avp(s:count)"); $var(ret) = avp_db_query("SELECT count(*) FROM subscriber where username='foo'", "$avp(s:count)"); xlog("L_INFO", "var(ret)=$var(ret) avp(s:count)=$avp(s:count)");
Output: INFO:<script>: var(ret)=1 avp(s:count)=<null>
(notice the return code 1, which means that the query was successfull and there should be some output value stored in the avp, but it is<null>)
The return type of the COUNT() function is a BIGINT, which is ignored in kamailio. The attached patch converts the lower 32 bits of the result to an int usable by kamailio. I'll push this eventually.
Thanks for quick reaction and patch. Go ahead and commit it.
Cheers, Daniel
-- Daniel-Constantin Mierla -- http://www.asipto.com http://linkedin.com/in/miconda -- http://twitter.com/miconda
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
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
Hi Daniel, Hi Alex,
Thanks! That looks great. Once commited, I'll test it, too.
By the way, I've just tried with sql_query. It didn't work as well. Do you think it is fixed together with Alex's change?
Shouldn't there be a warning/error message if the returned data type is not supported?
Code: sql_query("openserdb", "SELECT count(*) FROM subscriber where username='foo'", "ra"); $avp(s:value) = $dbr(ra=>[0,0]); xlog("L_INFO", "avp(s:value)=$avp(s:value)\n"); sql_result_free("ra");
Output: avp(s:value)=
Mit freundlichen Grüßen / Best regards
Ricardo Keigo de Sales Andrade
Robert Bosch GmbH (CI/AFU) Postfach 30 02 20 70442 Stuttgart GERMANY www.bosch.com
Tel. +49(711)811-3607004 Mobil +49(172)1081152 ricardo.andrade@br.bosch.com
Sitz: Stuttgart, Registergericht: Amtsgericht Stuttgart, HRB 14000; Aufsichtsratsvorsitzender: Hermann Scholl; Geschäftsführung: Franz Fehrenbach, Siegfried Dais; Stefan Asenkerschbaumer, Bernd Bohr, Rudolf Colm, Volkmar Denner, Wolfgang Malchow, Peter Marks, Uwe Raschke, Wolf-Henning Scheider, Peter Tyroller
-----Ursprüngliche Nachricht----- Von: Daniel-Constantin Mierla [mailto:miconda@gmail.com] Gesendet: Dienstag, 5. Juli 2011 11:51 An: SIP Router - Kamailio (OpenSER) and SIP Express Router (SER) - Users Mailing List Cc: Alex Hermann; Andrade Ricardo (CI/AFU1) Betreff: Re: [SR-Users] "select count(*)" with avp_db_query always returns null
On 7/5/11 11:39 AM, Alex Hermann wrote:
On Wednesday 29 June 2011, Andrade Ricardo (CI/AFU1) wrote:
Perhaps this fits better into a bug report, but I'd like to know if somebody out there experienced a similar issue. I am executing a "select count" query using the avp_db_query function, but it is not storing the results in any avp. Other queries are working fine. I use db_mysql connected with a mysql 5.1 server.
I have tested this with kamailio-3.1.0 and kamailio-3.1.3, both didn't work. In an old box (version 1.3.x), the same query was returning the correct value.
Here is the case which is not working:
Code: avp_delete("$avp(s:count)"); $var(ret) = avp_db_query("SELECT count(*) FROM subscriber where username='foo'", "$avp(s:count)"); xlog("L_INFO", "var(ret)=$var(ret) avp(s:count)=$avp(s:count)");
Output: INFO:<script>: var(ret)=1 avp(s:count)=<null>
(notice the return code 1, which means that the query was successfull and there should be some output value stored in the avp, but it is<null>)
The return type of the COUNT() function is a BIGINT, which is ignored in kamailio. The attached patch converts the lower 32 bits of the result to an int usable by kamailio. I'll push this eventually.
Thanks for quick reaction and patch. Go ahead and commit it.
Cheers, Daniel
-- Daniel-Constantin Mierla -- http://www.asipto.com http://linkedin.com/in/miconda -- http://twitter.com/miconda
Hello,
On 7/5/11 12:07 PM, Andrade Ricardo (CI/AFU1) wrote:
Hi Daniel, Hi Alex,
Thanks! That looks great. Once commited, I'll test it, too.
By the way, I've just tried with sql_query. It didn't work as well. Do you think it is fixed together with Alex's change?
Shouldn't there be a warning/error message if the returned data type is not supported?
Code: sql_query("openserdb", "SELECT count(*) FROM subscriber where username='foo'", "ra"); $avp(s:value) = $dbr(ra=>[0,0]); xlog("L_INFO", "avp(s:value)=$avp(s:value)\n"); sql_result_free("ra");
Output: avp(s:value)=
interesting, I just looked in the code and bigints should be converted to string values. Can you test if ($dbr(ra=>rows) >0) to be sure there was a row returned by the query.
Cheers, Daniel
Hi Daniel,
Code: sql_query("openserdb", "SELECT count(*) FROM subscriber where username='foo'", "ra"); $avp(s:value) = $dbr(ra=>[0,0]); xlog("L_INFO", "avp(s:value)=$avp(s:value) (avp(s:value){s.int})=$(avp(s:value){s.int}) dbr(ra=>rows)=$dbr(ra=>rows)"); sql_result_free("ra");
Output: avp(s:value)= (avp(s:value){s.int})= dbr(ra=>rows)=1
I am testing only with kamailio-3.1.0 right now.
Mit freundlichen Grüßen / Best regards
Ricardo Keigo de Sales Andrade
Robert Bosch GmbH (CI/AFU) Postfach 30 02 20 70442 Stuttgart GERMANY www.bosch.com
Tel. +49(711)811-3607004 Mobil +49(172)1081152 ricardo.andrade@br.bosch.com
Sitz: Stuttgart, Registergericht: Amtsgericht Stuttgart, HRB 14000; Aufsichtsratsvorsitzender: Hermann Scholl; Geschäftsführung: Franz Fehrenbach, Siegfried Dais; Stefan Asenkerschbaumer, Bernd Bohr, Rudolf Colm, Volkmar Denner, Wolfgang Malchow, Peter Marks, Uwe Raschke, Wolf-Henning Scheider, Peter Tyroller
-----Ursprüngliche Nachricht----- Von: Daniel-Constantin Mierla [mailto:miconda@gmail.com] Gesendet: Dienstag, 5. Juli 2011 12:21 An: SIP Router - Kamailio (OpenSER) and SIP Express Router (SER) - Users Mailing List Cc: Andrade Ricardo (CI/AFU1) Betreff: Re: [SR-Users] "select count(*)" with avp_db_query always returns null
Hello,
On 7/5/11 12:07 PM, Andrade Ricardo (CI/AFU1) wrote:
Hi Daniel, Hi Alex,
Thanks! That looks great. Once commited, I'll test it, too.
By the way, I've just tried with sql_query. It didn't work as well. Do you think it is fixed together with Alex's change?
Shouldn't there be a warning/error message if the returned data type is not supported?
Code: sql_query("openserdb", "SELECT count(*) FROM subscriber where username='foo'", "ra"); $avp(s:value) = $dbr(ra=>[0,0]); xlog("L_INFO", "avp(s:value)=$avp(s:value)\n"); sql_result_free("ra");
Output: avp(s:value)=
interesting, I just looked in the code and bigints should be converted to string values. Can you test if ($dbr(ra=>rows) >0) to be sure there was a row returned by the query.
Cheers, Daniel
-- Daniel-Constantin Mierla -- http://www.asipto.com http://linkedin.com/in/miconda -- http://twitter.com/miconda
-
Alex Balashov -
Alex Hermann -
Andrade Ricardo (CI/AFU1) -
Daniel-Constantin Mierla