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

Podrigal, Aron aronp at guaranteedplus.com
Tue Nov 3 20:48:28 CET 2015


Yes. That's correct.

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

>
> 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://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>
> 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> 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 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 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://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>
>>> 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> 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 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 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> 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 RAMAL 979
>>>>>>
>>>>>> _______________________________________________
>>>>>> Users mailing list
>>>>>> 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
>>>>> http://lists.opensips.org/cgi-bin/mailman/listinfo/users
>>>>>
>>>>>
>>>>
>>>> _______________________________________________
>>>> Users mailing list
>>>> 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
>>> 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'
>
>
> _______________________________________________
> Users mailing list
> 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'
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.opensips.org/pipermail/users/attachments/20151103/678a6c0b/attachment-0001.htm>


More information about the Users mailing list