[OpenSIPS-Users] Can I use 'UPDATE' in avp_db_query ?

Rodrigo Pimenta Carvalho pimenta at inatel.br
Tue Nov 3 20:44:19 CET 2015


Thank you very much!!

I have to try it tomorrow, cause I have to leave for the day now.


By the way: you have used _s->s. The SQLite documentation says that such parameter should end with \0, shouldn't it? Should you have created a new string ending with \0, to be used here?


According to a developer from my team (Daniel Fússia), maybe the following "patch" code should be considered:



+                                            /* fix possible non '\0' terminated table name */

+                                            char stable[512];

+                                            memcpy(stable, _s->s, _s->len);

+                                            stable[_s->len] = '\0';

+                                            if (sqlite3_exec(CON_CONNECTION(_h),

-                                                                                                            query_holder.s, NULL, NULL, &errmsg)) {

+                                                                                                           stable, NULL, NULL, &errmsg)) {




What about?


Regards.




RODRIGO PIMENTA CARVALHO
Inatel Competence Center
Software
Ph: +55 35 3471 9200 RAMAL 979


________________________________
De: users-bounces at lists.opensips.org <users-bounces at lists.opensips.org> em nome de Podrigal, Aron <aronp at guaranteedplus.com>
Enviado: terça-feira, 3 de novembro de 2015 17:29
Para: OpenSIPS users mailling list
Assunto: Re: [OpenSIPS-Users] Can I use 'UPDATE' in avp_db_query ?

Here is how to compile and install

cd /usr/local/src/
git clone https://github.com/ar45/opensips -b fix_sqlite_delete_update_avp_query
[https://avatars1.githubusercontent.com/u/4923160?v=3&s=400]<https://github.com/ar45/opensips>

ar45/opensips
opensips - OpenSIPS is a GPL implementation of a multi-functionality SIP Server that targets to deliver a high-level technical solution (performance, security and quality) to be used in professiona...
Leia mais...<https://github.com/ar45/opensips>

cd opensips
make menuconfig

Then configure your compile options and install location and compile it.


On Tue, Nov 3, 2015 at 2:25 PM, Podrigal, Aron <aronp at guaranteedplus.com<mailto:aronp at guaranteedplus.com>> wrote:
That's right. I can't believe I looked over that. I updated the pull request.

On Tue, Nov 3, 2015 at 1:38 PM, Rodrigo Pimenta Carvalho <pimenta at inatel.br<mailto:pimenta at inatel.br>> wrote:
Hi Podrigal.


Yes. The query doesn't have to return a value. It is Ok. But, the query also doesn't take effect in the database.


By the way, take a look in the code you have just pointed me in [1].


That function has the parameter _s  :              int db_sqlite_raw_query(const db_con_t* _h, const str* _s, db_res_t** _r).

However, such parameter is not passed to the sqlite3_exec. See:


sqlite3_exec(CON_CONNECTION(_h), query_holder.s, NULL, NULL, &errmsg)


query_holder.s was used in place of _s.


Could it be a bug in the dbase.c file? What do you think?


Best regards.



RODRIGO PIMENTA CARVALHO
Inatel Competence Center
Software
Ph: +55 35 3471 9200<tel:%2B55%2035%203471%209200> RAMAL 979


________________________________
De: users-bounces at lists.opensips.org<mailto:users-bounces at lists.opensips.org> <users-bounces at lists.opensips.org<mailto:users-bounces at lists.opensips.org>> em nome de Podrigal, Aron <aronp at guaranteedplus.com<mailto:aronp at guaranteedplus.com>>
Enviado: terça-feira, 3 de novembro de 2015 16:11
Para: OpenSIPS users mailling list
Assunto: Re: [OpenSIPS-Users] Can I use 'UPDATE' in avp_db_query ?

Here [1] is where your issue is. That it simply executes the query but returns no results. I guess we need to have some conditional to run the update function for update statements instead.

[1] https://github.com/OpenSIPS/opensips/blob/98dccc53f4c25da6edf756333ef45a93bc2f41c7/modules/db_sqlite/dbase.c#L336
[https://avatars1.githubusercontent.com/u/3853758?v=3&s=400]<https://github.com/OpenSIPS/opensips/blob/98dccc53f4c25da6edf756333ef45a93bc2f41c7/modules/db_sqlite/dbase.c#L336>

OpenSIPS/opensips
opensips - OpenSIPS is a GPL implementation of a multi-functionality SIP Server that targets to deliver a high-level technical solution (performance, security and quality) to be used in professiona...
Leia mais...<https://github.com/OpenSIPS/opensips/blob/98dccc53f4c25da6edf756333ef45a93bc2f41c7/modules/db_sqlite/dbase.c#L336>




On Tue, Nov 3, 2015 at 1:03 PM, Patrick Wakano <pwakano at gmail.com<mailto:pwakano at gmail.com>> wrote:
I think Opensips lexer is converting your 'yes' to the integer 1 when running the SQL query in your DB.
See this thread:
http://lists.opensips.org/pipermail/users/2015-February/030946.html

Try to use something different from 'yes' to see what happens.

Patrick


On Tue, Nov 3, 2015 at 3:50 PM, Rodrigo Pimenta Carvalho <pimenta at inatel.br<mailto:pimenta at inatel.br>> wrote:

Hi Aron.


Do you know whether it is necessary to commit the modifications made by avp_db_query("Update....") ?

Is there some kind of ' commit' command to be executed in the OpenSIPS script?


Any hint will be very helpful!


Thanks a lot.


RODRIGO PIMENTA CARVALHO
Inatel Competence Center
Software
Ph: +55 35 3471 9200<tel:%2B55%2035%203471%209200> RAMAL 979


________________________________
De: users-bounces at lists.opensips.org<mailto:users-bounces at lists.opensips.org> <users-bounces at lists.opensips.org<mailto:users-bounces at lists.opensips.org>> em nome de Podrigal, Aron <aronp at guaranteedplus.com<mailto:aronp at guaranteedplus.com>>
Enviado: terça-feira, 3 de novembro de 2015 15:26
Para: OpenSIPS users mailling list
Assunto: Re: [OpenSIPS-Users] Can I use 'UPDATE' in avp_db_query ?

Try removing the second argument "$avp(CallMaxDuration)" from avp_db_query() Because the database backend does not return any results.

On Tue, Nov 3, 2015 at 8:50 AM, Rodrigo Pimenta Carvalho <pimenta at inatel.br<mailto:pimenta at inatel.br>> wrote:

Dear OpenSIPS-users,


In the OpenSIPS database I have created some new tables, for my project. I can read from such tables successfully.  For example, the instruction


 avp_db_query("select Value from GeneralConfigurations where Attribute = 'CallMaxDuration'", "$avp(CallMaxDuration)");


run and works very well. It put the right value in the apv!


However, when the following instruction is executed, it doesn't take any effect in the database:


 avp_db_query("update GeneralConfigurations set Value = 'yes' where Attribute = 'Exists_Intercom_Call'", "$avp(UpdateResult)");


The log says:

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------


Nov  3 11:32:52 [29114] DBG:avpops:ops_dbquery_avps: query [update GeneralConfigurations set Value = 'yes' where Attribute = 'Exists_Intercom_Call']
Nov  3 11:32:52 [29114] DBG:avpops:db_query_avp: no result after query
Nov  3 11:32:52 [29114] DBG:avpops:db_close_query: close avp query
Nov  3 11:32:52 [29114] ERROR:db_sqlite:db_sqlite_free_result: invalid parameter value


------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


But the query UPDATE is correct, because I have already tested it directly in the database.


So, am I doing something wrong? What exactly does mean that error "invalid parameter value" in this case? Can I use UPDATE in an avp_db_query?


Any hint will be very helpful!


Best regards.








RODRIGO PIMENTA CARVALHO
Inatel Competence Center
Software
Ph: +55 35 3471 9200<tel:%2B55%2035%203471%209200> RAMAL 979

_______________________________________________
Users mailing list
Users at lists.opensips.org<mailto:Users at lists.opensips.org>
http://lists.opensips.org/cgi-bin/mailman/listinfo/users




--
Aron Podrigal
-
'1000001', '1110010', '1101111', '1101110'   '1010000', '1101111', '1100100', '1110010', '1101001', '1100111', '1100001', '1101100'

P: '2b', '31', '33', '34', '37', '34', '35', '38', '36', '30', '39', '39'


_______________________________________________
Users mailing list
Users at lists.opensips.org<mailto:Users at lists.opensips.org>
http://lists.opensips.org/cgi-bin/mailman/listinfo/users



_______________________________________________
Users mailing list
Users at lists.opensips.org<mailto:Users at lists.opensips.org>
http://lists.opensips.org/cgi-bin/mailman/listinfo/users




--
Aron Podrigal
-
'1000001', '1110010', '1101111', '1101110'   '1010000', '1101111', '1100100', '1110010', '1101001', '1100111', '1100001', '1101100'

P: '2b', '31', '33', '34', '37', '34', '35', '38', '36', '30', '39', '39'


_______________________________________________
Users mailing list
Users at lists.opensips.org<mailto:Users at lists.opensips.org>
http://lists.opensips.org/cgi-bin/mailman/listinfo/users




--
Aron Podrigal
-
'1000001', '1110010', '1101111', '1101110'   '1010000', '1101111', '1100100', '1110010', '1101001', '1100111', '1100001', '1101100'

P: '2b', '31', '33', '34', '37', '34', '35', '38', '36', '30', '39', '39'




--
Aron Podrigal
-
'1000001', '1110010', '1101111', '1101110'   '1010000', '1101111', '1100100', '1110010', '1101001', '1100111', '1100001', '1101100'

P: '2b', '31', '33', '34', '37', '34', '35', '38', '36', '30', '39', '39'

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.opensips.org/pipermail/users/attachments/20151103/ac60eaaf/attachment-0001.htm>


More information about the Users mailing list