<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none;"><!-- P {margin-top:0;margin-bottom:0;} --></style>
</head>
<body dir="ltr">
<div id="divtagdefaultwrapper" style="font-size:12pt;color:#000000;background-color:#FFFFFF;font-family:Calibri,Arial,Helvetica,sans-serif;">
<p><br>
</p>
<p>Thank you very much!!<br>
<br>
I have to try it tomorrow, cause I have to leave for the day now.<br>
<br>
</p>
<p>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?</p>
<p><br>
</p>
<p>According to a developer from my team (Daniel Fússia), maybe the following "patch" code should be considered:<br>
</p>
<br>
<p></p>
<br>
<p></p>
<p class="x_MsoNormal"><span lang="EN-US">+ /* fix possible non '\0' terminated table name */</span></p>
<p class="x_MsoNormal"><span lang="EN-US">+ char stable[512];</span></p>
<p class="x_MsoNormal"><span lang="EN-US">+ memcpy(stable, _s->s, _s->len);</span></p>
<p class="x_MsoNormal"><span lang="EN-US">+ stable[_s->len] = '\0';</span></p>
<p class="x_MsoNormal"><span lang="EN-US">+ if (sqlite3_exec(CON_CONNECTION(_h),</span></p>
<p class="x_MsoNormal"><span lang="EN-US">- query_holder.s, NULL, NULL, &errmsg)) {</span></p>
<p class="x_MsoNormal"><span lang="EN-US">+ stable, NULL, NULL, &errmsg)) {</span></p>
<br>
<p></p>
<p><br>
</p>
<p><br>
</p>
<p>What about?</p>
<p><br>
</p>
<p>Regards.<br>
</p>
<p><br>
</p>
<p><br>
</p>
<p><br>
</p>
<div id="Signature">
<div name="divtagdefaultwrapper" style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:; margin:0">
<div class="BodyFragment"><font size="2">
<div class="PlainText">RODRIGO PIMENTA CARVALHO<br>
Inatel Competence Center<br>
Software<br>
Ph: +55 35 3471 9200 RAMAL 979<br>
</div>
</font></div>
</div>
</div>
<br>
<br>
<div style="color: rgb(0, 0, 0);">
<hr tabindex="-1" style="display:inline-block; width:98%">
<div id="divRplyFwdMsg" dir="ltr"><font style="font-size:11pt" color="#000000" face="Calibri, sans-serif"><b>De:</b> users-bounces@lists.opensips.org <users-bounces@lists.opensips.org> em nome de Podrigal, Aron <aronp@guaranteedplus.com><br>
<b>Enviado:</b> terça-feira, 3 de novembro de 2015 17:29<br>
<b>Para:</b> OpenSIPS users mailling list<br>
<b>Assunto:</b> Re: [OpenSIPS-Users] Can I use 'UPDATE' in avp_db_query ?</font>
<div> </div>
</div>
<div>
<div dir="ltr">
<div>Here is how to compile and install</div>
<div><br>
</div>
<font face="monospace, monospace">cd /usr/local/src/</font>
<div><font face="monospace, monospace">git clone <a id="LPlnk162364" href="https://github.com/ar45/opensips">https://github.com/ar45/opensips</a> -b fix_sqlite_delete_update_avp_query</font>
<div style="margin-top: 20px; margin-bottom: 20px; overflow: auto; width: 100%;" id="LPBorder_GT_14465797078690.43488260453860195">
<table style="border-top: 1px solid rgb(204, 204, 204); border-bottom: 1px solid rgb(204, 204, 204); width: 80%; background-color: rgb(255, 255, 255); position: relative; overflow: auto;" id="LPContainer_14465797078670.06316981911604713">
<tbody>
<tr style="border-spacing: 0px;" valign="top">
<td colspan="1" style="width: 250px; position: relative; display: table-cell; vertical-align: middle; padding-right: 20px;" id="ImageCell_14465797078670.7800774808154484">
<div style="margin-top: 12px; background-color: rgb(255, 255, 255); height: 254px; width: 250px; position: relative; padding: 0px; display: table;" id="LPImageContainer_14465797078670.7151049983090023">
<a target="_blank" href="https://github.com/ar45/opensips" style="display: table-cell; text-align: center;" id="LPImageAnchor_14465797078680.09338265483563457"><img aria-label="Imagem de visualização com link selecionado. Toque duas vezes para abrir o link." style="display: inline-block; margin-left: auto; margin-right: auto; max-width: 250px; max-height: 250px; height: 250px; width: 250px; border-width: 0px;" height="250" width="250" src="https://avatars1.githubusercontent.com/u/4923160?v=3&s=400"></a></div>
</td>
<td>
<div style="top: 0px; margin-top: 8px; font-size: 21px; font-family: "wf_segoe-ui_semilight","Segoe UI Semilight","Segoe WP Semilight","Segoe UI","Segoe WP",Tahoma,Arial,sans-serif; color: rgb(51, 51, 51); margin-left: 14px; margin-right: 14px;" id="LPTitle_14465797078680.8256054507125007">
ar45/opensips</div>
<div style="margin-top: 8px; font-size: 13px; font-family: "wf_segoe-ui_normal","Segoe UI","Segoe WP",Tahoma,Arial,sans-serif; color: rgb(102, 102, 102); margin-left: 14px; margin-right: 14px;" id="LPDescription_14465797078680.31756011789273986">
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...</div>
<div style="margin: 8px 14px 10px; height: 18px; text-overflow: ellipsis; overflow: hidden; white-space: nowrap;" id="LPUrlContainer_14465797078690.6792598432644713">
<a target="_blank" href="https://github.com/ar45/opensips" style="font-size: 11px; font-family: "wf_segoe-ui_normal","Segoe UI","Segoe WP",Tahoma,Arial,sans-serif; text-decoration: none;" id="LPUrlAnchor_14465797078680.26391163799528716">Leia mais...</a></div>
</td>
</tr>
</tbody>
</table>
</div>
</div>
<div><font face="monospace, monospace">cd opensips</font></div>
<div><font face="monospace, monospace">make menuconfig</font></div>
<div><br>
</div>
<div>Then configure your compile options and install location and compile it.</div>
<div><br>
</div>
</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Tue, Nov 3, 2015 at 2:25 PM, Podrigal, Aron <span dir="ltr">
<<a href="mailto:aronp@guaranteedplus.com" target="_blank">aronp@guaranteedplus.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex; border-left:1px #ccc solid; padding-left:1ex">
<div dir="ltr">That's right. I can't believe I looked over that. I updated the pull request.</div>
<div class="HOEnZb">
<div class="h5">
<div class="gmail_extra"><br>
<div class="gmail_quote">On Tue, Nov 3, 2015 at 1:38 PM, Rodrigo Pimenta Carvalho
<span dir="ltr"><<a href="mailto:pimenta@inatel.br" target="_blank">pimenta@inatel.br</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex; border-left:1px #ccc solid; padding-left:1ex">
<div dir="ltr">
<div style="font-size:12pt; color:#000000; background-color:#ffffff; font-family:Calibri,Arial,Helvetica,sans-serif">
Hi Podrigal.<br>
<br>
<p>Yes. The query doesn't have to return a value. It is Ok. But, the query also doesn't take effect in the database.
<br>
</p>
<p><br>
</p>
<p>By the way, take a look in the code you have just pointed me in [1].</p>
<p><br>
</p>
<p>That function has the parameter _s : <i><span>int</span> <span>db_sqlite_raw_query</span>(<span>const</span>
<span>db_con_t</span>* _h, <span>const</span> str* _s, <span>db_res_t</span>** _r).</i></p>
<p>However, such parameter is not passed to the <span>sqlite3_exec</span>. See:</p>
<p><br>
</p>
<p><i>sqlite3_exec(<span>CON_CONNECTION</span>(_h), query_holder.<span>s</span>, <span>
NULL</span>, <span>NULL</span>, &errmsg)</i></p>
<p><i><br>
</i></p>
<p>query_holder.s was used in place of _s. <br>
</p>
<p><br>
</p>
<p>Could it be a bug in the <strong>dbase.c</strong> file? What do you think?</p>
<span>
<p><br>
</p>
<p>Best regards.<br>
</p>
<br>
<p><br>
</p>
<div>
<div name="divtagdefaultwrapper">
<div><font size="2">
<div>RODRIGO PIMENTA CARVALHO<br>
Inatel Competence Center<br>
Software<br>
Ph: <a href="tel:%2B55%2035%203471%209200" value="+553534719200" target="_blank">
+55 35 3471 9200</a> RAMAL 979<br>
</div>
</font></div>
</div>
</div>
<br>
<br>
</span>
<div style="color:rgb(0,0,0)">
<hr style="display:inline-block; width:98%">
<div dir="ltr"><font style="font-size:11pt" color="#000000" face="Calibri, sans-serif"><span><b>De:</b>
<a href="mailto:users-bounces@lists.opensips.org" target="_blank">users-bounces@lists.opensips.org</a> <<a href="mailto:users-bounces@lists.opensips.org" target="_blank">users-bounces@lists.opensips.org</a>> em nome de Podrigal, Aron <<a href="mailto:aronp@guaranteedplus.com" target="_blank">aronp@guaranteedplus.com</a>><br>
</span><b>Enviado:</b> terça-feira, 3 de novembro de 2015 16:11<span><br>
<b>Para:</b> OpenSIPS users mailling list<br>
<b>Assunto:</b> Re: [OpenSIPS-Users] Can I use 'UPDATE' in avp_db_query ?</span></font>
<div> </div>
</div>
<div>
<div dir="ltr"><span>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.
<div><br>
</div>
</span>
<div>[1] <a href="https://github.com/OpenSIPS/opensips/blob/98dccc53f4c25da6edf756333ef45a93bc2f41c7/modules/db_sqlite/dbase.c#L336" target="_blank">https://github.com/OpenSIPS/opensips/blob/98dccc53f4c25da6edf756333ef45a93bc2f41c7/modules/db_sqlite/dbase.c#L336</a>
<div style="margin-top:20px; margin-bottom:20px; overflow:auto; width:100%">
<table style="border-top:1px solid rgb(204,204,204); border-bottom:1px solid rgb(204,204,204); width:80%; background-color:rgb(255,255,255); overflow:auto">
<tbody>
<tr style="border-spacing:0px" valign="top">
<td colspan="1" style="width:250px; display:table-cell; vertical-align:middle; padding-right:20px">
<div style="margin-top:12px; background-color:rgb(255,255,255); min-height:254px; width:250px; padding:0px; display:table">
<a href="https://github.com/OpenSIPS/opensips/blob/98dccc53f4c25da6edf756333ef45a93bc2f41c7/modules/db_sqlite/dbase.c#L336" target="_blank" style="display:table-cell; text-align:center"><img style="display: inline-block; margin-left: auto; margin-right: auto; max-width: 250px; max-height: 250px; min-height: 250px; width: 250px; border-width: 0px; -moz-user-select: none;" height="250" width="250" src="https://avatars1.githubusercontent.com/u/3853758?v=3&s=400"></a></div>
</td>
<td>
<div style="margin-top:8px; font-size:21px; font-family:"wf_segoe-ui_semilight","Segoe UI Semilight","Segoe WP Semilight","Segoe UI","Segoe WP",Tahoma,Arial,sans-serif; color:rgb(51,51,51); margin-left:14px; margin-right:14px">
OpenSIPS/opensips</div>
<div style="margin-top:8px; font-size:13px; font-family:"wf_segoe-ui_normal","Segoe UI","Segoe WP",Tahoma,Arial,sans-serif; color:rgb(102,102,102); margin-left:14px; margin-right:14px">
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...</div>
<div style="margin:8px 14px 10px; min-height:18px; text-overflow:ellipsis; overflow:hidden; white-space:nowrap">
<a href="https://github.com/OpenSIPS/opensips/blob/98dccc53f4c25da6edf756333ef45a93bc2f41c7/modules/db_sqlite/dbase.c#L336" target="_blank" style="font-size:11px; font-family:"wf_segoe-ui_normal","Segoe UI","Segoe WP",Tahoma,Arial,sans-serif; text-decoration:none">Leia
mais...</a></div>
</td>
</tr>
</tbody>
</table>
</div>
<br>
</div>
<div><br>
</div>
</div>
<div>
<div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Tue, Nov 3, 2015 at 1:03 PM, Patrick Wakano <span dir="ltr">
<<a href="mailto:pwakano@gmail.com" target="_blank">pwakano@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex; border-left:1px #ccc solid; padding-left:1ex">
<div dir="ltr">I think Opensips lexer is converting your 'yes' to the integer 1 when running the SQL query in your DB.
<div>See this thread:<br>
</div>
<div>
<div><a href="http://lists.opensips.org/pipermail/users/2015-February/030946.html" target="_blank">http://lists.opensips.org/pipermail/users/2015-February/030946.html</a><br>
</div>
<div><br>
</div>
<div>Try to use something different from 'yes' to see what happens.</div>
<span><font color="#888888">
<div><br>
</div>
<div>Patrick</div>
<div><br>
</div>
</font></span></div>
</div>
<div>
<div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Tue, Nov 3, 2015 at 3:50 PM, Rodrigo Pimenta Carvalho
<span dir="ltr"><<a title="Ctrl+Clique ou toque para seguir o link" href="mailto:pimenta@inatel.br" target="_blank">pimenta@inatel.br</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex; border-left:1px #ccc solid; padding-left:1ex">
<div dir="ltr">
<div style="font-size:12pt; color:#000000; background-color:#ffffff; font-family:Calibri,Arial,Helvetica,sans-serif">
<p>Hi Aron.</p>
<p><br>
</p>
<p>Do you know whether it is necessary to commit the modifications made by avp_db_query("Update....") ?</p>
<p>Is there some kind of ' commit' command to be executed in the OpenSIPS script?</p>
<span>
<p><br>
</p>
<p>Any hint will be very helpful!</p>
<p><br>
</p>
</span>
<p>Thanks a lot.<br>
</p>
<span>
<p><br>
</p>
<div>
<div name="divtagdefaultwrapper">
<div><font size="2">
<div>RODRIGO PIMENTA CARVALHO<br>
Inatel Competence Center<br>
Software<br>
Ph: <a href="tel:%2B55%2035%203471%209200" value="+553534719200" target="_blank">
+55 35 3471 9200</a> RAMAL 979<br>
</div>
</font></div>
</div>
</div>
<br>
<br>
</span>
<div style="color:rgb(0,0,0)">
<hr style="display:inline-block; width:98%">
<div dir="ltr"><font style="font-size:11pt" color="#000000" face="Calibri, sans-serif"><span><b>De:</b>
<a href="mailto:users-bounces@lists.opensips.org" target="_blank">users-bounces@lists.opensips.org</a> <<a href="mailto:users-bounces@lists.opensips.org" target="_blank">users-bounces@lists.opensips.org</a>> em nome de Podrigal, Aron <<a href="mailto:aronp@guaranteedplus.com" target="_blank">aronp@guaranteedplus.com</a>><br>
<b>Enviado:</b> terça-feira, 3 de novembro de 2015 15:26<br>
<b>Para:</b> OpenSIPS users mailling list<br>
</span><b>Assunto:</b> Re: [OpenSIPS-Users] Can I use 'UPDATE' in avp_db_query ?</font>
<div> </div>
</div>
<div>
<div>
<div>
<div dir="ltr">Try removing the second argument <i style="color:rgb(0,0,0); font-family:Calibri,Arial,Helvetica,sans-serif; font-size:16px">"$avp(CallMaxDuration)"</i> from avp_db_query() Because the database backend does not return any results.</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Tue, Nov 3, 2015 at 8:50 AM, Rodrigo Pimenta Carvalho
<span dir="ltr"><<a href="mailto:pimenta@inatel.br" target="_blank">pimenta@inatel.br</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex; border-left:1px #ccc solid; padding-left:1ex">
<div dir="ltr">
<div style="font-size:12pt; color:#000000; background-color:#ffffff; font-family:Calibri,Arial,Helvetica,sans-serif">
<p>Dear OpenSIPS-users,</p>
<p><br>
</p>
<p>In the OpenSIPS database I have created some new tables, for my project. I can read from such tables successfully. For example, the instruction</p>
<p><br>
</p>
<p> <i>avp_db_query("select Value from GeneralConfigurations where Attribute = 'CallMaxDuration'", "$avp(CallMaxDuration)");</i></p>
<p><br>
</p>
<p>run and works very well. It put the right value in the apv!</p>
<p><br>
</p>
<p>However, when the following instruction is executed, it doesn't take any effect in the database:</p>
<p><br>
</p>
<p><i> avp_db_query("update GeneralConfigurations set Value = 'yes' where Attribute = 'Exists_Intercom_Call'", "$avp(UpdateResult)");</i></p>
<p><br>
</p>
<p>The log says:</p>
<p>-----------------------------------------------------------------------------------------------------------------------------------------------------------------------</p>
<p><br>
</p>
<p>Nov 3 11:32:52 [29114] DBG:avpops:ops_dbquery_avps: query [update GeneralConfigurations set Value = 'yes' where Attribute = 'Exists_Intercom_Call']<br>
Nov 3 11:32:52 [29114] DBG:avpops:db_query_avp: no result after query<br>
Nov 3 11:32:52 [29114] DBG:avpops:db_close_query: close avp query<br>
Nov 3 11:32:52 [29114] ERROR:db_sqlite:db_sqlite_free_result: <font color="#FF0000">
invalid parameter value</font></p>
<p><br>
</p>
<p>------------------------------------------------------------------------------------------------------------------------------------------------------------------------------</p>
<p><br>
</p>
<p>But the query UPDATE is correct, because I have already tested it directly in the database.</p>
<p><br>
</p>
<p>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?</p>
<p><br>
</p>
<p>Any hint will be very helpful!</p>
<p><br>
</p>
<p>Best regards.<span><font color="#888888"><br>
</font></span></p>
<span><font color="#888888">
<p><br>
</p>
<p><br>
</p>
<p><br>
</p>
<p><br>
</p>
<p><br>
</p>
<p><br>
</p>
<p><br>
</p>
<div>
<div name="divtagdefaultwrapper">
<div><font size="2">
<div>RODRIGO PIMENTA CARVALHO<br>
Inatel Competence Center<br>
Software<br>
Ph: <a href="tel:%2B55%2035%203471%209200" value="+553534719200" target="_blank">
+55 35 3471 9200</a> RAMAL 979<br>
</div>
</font></div>
</div>
</div>
</font></span></div>
</div>
<br>
_______________________________________________<br>
Users mailing list<br>
<a href="mailto:Users@lists.opensips.org" target="_blank">Users@lists.opensips.org</a><br>
<a href="http://lists.opensips.org/cgi-bin/mailman/listinfo/users" rel="noreferrer" target="_blank">http://lists.opensips.org/cgi-bin/mailman/listinfo/users</a><br>
<br>
</blockquote>
</div>
<br>
<br clear="all">
<div><br>
</div>
-- <br>
<div>
<div dir="ltr">
<div>
<div dir="ltr">Aron Podrigal
<div>-</div>
<div>'1000001', '1110010', '1101111', '1101110' '1010000', '1101111', '1100100', '1110010', '1101001', '1100111', '1100001', '1101100'</div>
<div><br>
</div>
<div>P: '2b', '31', '33', '34', '37', '34', '35', '38', '36', '30', '39', '39'<br>
</div>
<div><br>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<br>
_______________________________________________<br>
Users mailing list<br>
<a href="mailto:Users@lists.opensips.org" target="_blank">Users@lists.opensips.org</a><br>
<a href="http://lists.opensips.org/cgi-bin/mailman/listinfo/users" rel="noreferrer" target="_blank">http://lists.opensips.org/cgi-bin/mailman/listinfo/users</a><br>
<br>
</blockquote>
</div>
<br>
</div>
</div>
</div>
<br>
_______________________________________________<br>
Users mailing list<br>
<a href="mailto:Users@lists.opensips.org" target="_blank">Users@lists.opensips.org</a><br>
<a href="http://lists.opensips.org/cgi-bin/mailman/listinfo/users" rel="noreferrer" target="_blank">http://lists.opensips.org/cgi-bin/mailman/listinfo/users</a><br>
<br>
</blockquote>
</div>
<br>
<br clear="all">
<div><br>
</div>
-- <br>
<div>
<div dir="ltr">
<div>
<div dir="ltr">Aron Podrigal
<div>-</div>
<div>'1000001', '1110010', '1101111', '1101110' '1010000', '1101111', '1100100', '1110010', '1101001', '1100111', '1100001', '1101100'</div>
<div><br>
</div>
<div>P: '2b', '31', '33', '34', '37', '34', '35', '38', '36', '30', '39', '39'<br>
</div>
<div><br>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<br>
_______________________________________________<br>
Users mailing list<br>
<a href="mailto:Users@lists.opensips.org" target="_blank">Users@lists.opensips.org</a><br>
<a href="http://lists.opensips.org/cgi-bin/mailman/listinfo/users" rel="noreferrer" target="_blank">http://lists.opensips.org/cgi-bin/mailman/listinfo/users</a><br>
<br>
</blockquote>
</div>
<br>
<br clear="all">
<div><br>
</div>
-- <br>
<div>
<div dir="ltr">
<div>
<div dir="ltr">Aron Podrigal
<div>-</div>
<div>'1000001', '1110010', '1101111', '1101110' '1010000', '1101111', '1100100', '1110010', '1101001', '1100111', '1100001', '1101100'</div>
<div><br>
</div>
<div>P: '2b', '31', '33', '34', '37', '34', '35', '38', '36', '30', '39', '39'<br>
</div>
<div><br>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</blockquote>
</div>
<br>
<br clear="all">
<div><br>
</div>
-- <br>
<div class="gmail_signature">
<div dir="ltr">
<div>
<div dir="ltr">Aron Podrigal
<div>-</div>
<div>'1000001', '1110010', '1101111', '1101110' '1010000', '1101111', '1100100', '1110010', '1101001', '1100111', '1100001', '1101100'</div>
<div><br>
</div>
<div>P: '2b', '31', '33', '34', '37', '34', '35', '38', '36', '30', '39', '39'<br>
</div>
<div><br>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>