<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>Hi Aron.</p>
<p><br>
</p>
<p>I found the error here.</p>
<p><br>
</p>
<p>The function strtok_r changes the original query causing the error of syntax.</p>
<p><br>
</p>
<p>Now I have fixed the code and avp_db_query began to work very well to update my table in SQLite.</p>
<p><br>
</p>
<p>So, my next step will be to test a more complex SQL query using avp_db_query.</p>
<p><br>
</p>
<p>P.S.: it was not necessary to use <span class="" style="color:rgb(0,134,179); font-family:Consolas,'Liberation Mono',Menlo,Courier,monospace; font-size:12px; line-height:16.8px; white-space:pre">
CON_RAW_QUERY</span><span style="color:rgb(51,51,51); font-family:Consolas,'Liberation Mono',Menlo,Courier,monospace; font-size:12px; line-height:16.8px; white-space:pre; background-color:rgb(250,250,250)">(_h)</span>. Does it make sense for you?<br>
</p>
<p><br>
</p>
<p>Thanks.<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 Rodrigo Pimenta Carvalho <pimenta@inatel.br><br>
<b>Enviado:</b> sexta-feira, 6 de novembro de 2015 09:21<br>
<b>Para:</b> OpenSIPS users mailling list<br>
<b>Assunto:</b> Re: [OpenSIPS-Users] Why does avp_db_query rise an error when my SQL is correct?</font>
<div> </div>
</div>
<div>
<div id="divtagdefaultwrapper" style="font-size:12pt; color:#000000; background-color:#FFFFFF; font-family:Calibri,Arial,Helvetica,sans-serif">
<p>Hi Aron.</p>
<p><br>
</p>
<p>Thank you for helping me again!</p>
<p><br>
</p>
<p>The function of macro <span class="" style="color:rgb(0,134,179); font-family:Consolas,'Liberation Mono',Menlo,Courier,monospace; font-size:12px; line-height:16.8px; white-space:pre">
CON_RAW_QUERY</span><span style="color:rgb(51,51,51); font-family:Consolas,'Liberation Mono',Menlo,Courier,monospace; font-size:12px; line-height:16.8px; white-space:pre; background-color:rgb(250,250,250)">(_h)</span> does not exist in the OpenSIPS 2.2 source
code. It is not declared anywhere.</p>
<p><br>
</p>
<p>Do you have created such function/macro in your code? If yes, when it will be available in the head (git) of the OpenSIPS project?</p>
<p><br>
</p>
<p><br>
</p>
<p><br>
</p>
<p>Can a git command shows me what are the differences between you and my clone?</p>
<p><br>
</p>
<p><br>
</p>
<p><br>
</p>
<p>Best regards.<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> quinta-feira, 5 de novembro de 2015 19:03<br>
<b>Para:</b> OpenSIPS users mailling list<br>
<b>Assunto:</b> Re: [OpenSIPS-Users] Why does avp_db_query rise an error when my SQL is correct?</font>
<div> </div>
</div>
<div>
<div dir="ltr"><br>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Thu, Nov 5, 2015 at 3:45 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:0px 0px 0px 0.8ex; border-left-width:1px; border-left-color:rgb(204,204,204); border-left-style:solid; padding-left:1ex">
<div dir="ltr">
<div style="font-size:12pt; color:rgb(0,0,0); font-family:Calibri,Arial,Helvetica,sans-serif; background-color:rgb(255,255,255)">
<p><br>
</p>
<p><br>
</p>
<p>Hi.</p>
<p><br>
</p>
<p>My SQL query is: <br>
</p>
<p><br>
</p>
<p><i>UPDATE GeneralConfigurations set Value = 'casa' where Attribute = 'Exists_Intercom_Call'</i></p>
<p><br>
</p>
<p>This query is correct and can execute very well when applied via a Firefox plugin for SQLite. So, I believe that such query is really correct.</p>
<p><br>
</p>
<p>However, when this query is passed to avp_db_query, the OpenSIPS logs:</p>
<p><br>
</p>
<p>-----------------------------------------------------------------------------------------------------------------------</p>
<p><br>
</p>
<p>Nov 5 18:25:53 [5655] DBG:avpops:ops_dbquery_avps: query [UPDATE GeneralConfigurations set Value = 'casa' where Attribute = 'Exists_Intercom_Call']<br>
Nov 5 18:25:53 [5655] ERROR:db_sqlite:db_sqlite_raw_query: query failed: near "UPDATE": syntax error<br>
</p>
<p>Nov 5 18:25:53 [5655] ERROR:avpops:db_query_avp: raw_query failed: db0(usr_preferences) UPDATE GeneralConfigurations set Value =...</p>
<p><br>
</p>
<p>--------------------------------------------------------------------------------------------------------------------------------</p>
<p><br>
</p>
<p>Can someone visualize what is the problem? Help, please.</p>
<p><br>
</p>
<p>Observations:</p>
<p><br>
</p>
<p>1. I'm using OpenSIPS 2.2 and SQLite 3.</p>
<p><br>
</p>
<p>2. I have changed the OpenSIPS dbase.c file on my own. (Following some hints gotten in this discussion list). So, I have changed the function db_sqlite_raw_query and now its code has these instructions:</p>
<p><br>
</p>
<p><br>
</p>
<p>..............................................................................................................................................................</p>
<p><br>
</p>
<p><br>
</p>
<p>int db_sqlite_raw_query(const db_con_t* _h, const str* _s, db_res_t** _r)<br>
{<br>
static char sql_str[SQL_BUF_LEN];<br>
int ret=-1;<br>
char* errmsg;<br>
// str select_str={"select", 6};<br>
<br>
CON_RESET_CURR_PS(_h);<br>
if (_s->len + 1 > SQL_BUF_LEN) {<br>
LM_ERR("query too big! try reducing the size of your query!"<br>
"Current max size [%d]!\n", SQL_BUF_LEN);<br>
return -1;<br>
}<br>
memcpy(sql_str, _s->s, _s->len);<br>
sql_str[_s->len] = '\0';<br>
<br>
char *saveptr;<br>
char *cpFrstWrd;<br>
char *cpScndWrd;<br>
<br>
cpFrstWrd = strtok_r(sql_str, " ", &saveptr);<br>
cpScndWrd = strtok_r(NULL, " ", &saveptr);<br>
<br>
</p>
<p> if (!((strncasecmp(cpFrstWrd, "SELECT", 6)==0) || (strncasecmp(cpScndWrd, "SELECT", 6)==0))){
<b>// now it will refuse queries with SELECT just when SELECT is in the beginning of the query.</b><br>
</p>
//if (!str_strstr(_s, &select_str)) {<br>
/* not a select statement; can execute the query and exit*/<br>
</div>
</div>
</blockquote>
<div><br>
</div>
<div>Try adding here:</div>
<div><br>
</div>
<div><span class="" style="color:rgb(0,134,179); font-family:Consolas,'Liberation Mono',Menlo,Courier,monospace; font-size:12px; line-height:16.8px; white-space:pre">CON_RAW_QUERY</span><span style="color:rgb(51,51,51); font-family:Consolas,'Liberation Mono',Menlo,Courier,monospace; font-size:12px; line-height:16.8px; white-space:pre; background-color:rgb(250,250,250)">(_h)
= </span><span class="" style="color:rgb(0,134,179); font-family:Consolas,'Liberation Mono',Menlo,Courier,monospace; font-size:12px; line-height:16.8px; white-space:pre">1</span><span style="color:rgb(51,51,51); font-family:Consolas,'Liberation Mono',Menlo,Courier,monospace; font-size:12px; line-height:16.8px; white-space:pre; background-color:rgb(250,250,250)">;</span><br>
</div>
<div> </div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex; border-left-width:1px; border-left-color:rgb(204,204,204); border-left-style:solid; padding-left:1ex">
<div dir="ltr">
<div style="font-size:12pt; color:rgb(0,0,0); font-family:Calibri,Arial,Helvetica,sans-serif; background-color:rgb(255,255,255)">
if (sqlite3_exec(CON_CONNECTION(_h),<br>
sql_str, NULL, NULL, &errmsg)) {<br>
LM_ERR("query failed: %s\n", errmsg);<br>
return -2;<br>
}<br>
<br>
return 0;<br>
}<br>
<p></p>
<p>// more logic follows here....</p>
<p><br>
</p>
<p>................................................................................................................................................<br>
</p>
<br>
<p></p>
<p><br>
</p>
<p>Any hint will be very helpful!!</p>
<p><br>
</p>
<p>Best regards.<span class=""><font color="#888888"><br>
</font></span></p>
<span class=""><font color="#888888">
<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">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 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>
</div>
</div>
</div>
</div>
</body>
</html>