[OpenSIPS-Users] Why does avp_db_query rise an error when my SQL is correct?

[Rodrigo Pimenta Carvalho]

Hi Aron.


Thank you for helping me again!


The function of macro CON_RAW_QUERY(_h) does not exist in the OpenSIPS 2.2 source code. It is not declared anywhere.


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?




Can a git command shows me what are the differences between you and my clone?




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: quinta-feira, 5 de novembro de 2015 19:03
Para: OpenSIPS users mailling list
Assunto: Re: [OpenSIPS-Users] Why does avp_db_query rise an error when my SQL is correct?



On Thu, Nov 5, 2015 at 3:45 PM, Rodrigo Pimenta Carvalho <pimenta at inatel.br<mailto:pimenta at inatel.br>> wrote:



Hi.


My SQL query is:


UPDATE GeneralConfigurations set Value = 'casa' where Attribute = 'Exists_Intercom_Call'


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.


However, when this query is passed to avp_db_query, the OpenSIPS logs:


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


Nov  5 18:25:53 [5655] DBG:avpops:ops_dbquery_avps: query [UPDATE GeneralConfigurations set Value = 'casa' where Attribute = 'Exists_Intercom_Call']
Nov  5 18:25:53 [5655] ERROR:db_sqlite:db_sqlite_raw_query: query failed: near "UPDATE": syntax error

Nov  5 18:25:53 [5655] ERROR:avpops:db_query_avp: raw_query failed: db0(usr_preferences) UPDATE GeneralConfigurations set Value =...


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


Can someone visualize what is the problem?  Help, please.


Observations:


1. I'm using OpenSIPS 2.2 and SQLite 3.


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:



..............................................................................................................................................................



int db_sqlite_raw_query(const db_con_t* _h, const str* _s, db_res_t** _r)
{
        static char sql_str[SQL_BUF_LEN];
        int ret=-1;
        char* errmsg;
//      str select_str={"select", 6};

       CON_RESET_CURR_PS(_h);
       if (_s->len + 1 > SQL_BUF_LEN) {
         LM_ERR("query too big! try reducing the size of your query!"
        "Current max size [%d]!\n", SQL_BUF_LEN);
        return -1;
        }
        memcpy(sql_str, _s->s, _s->len);
        sql_str[_s->len] = '\0';

       char *saveptr;
       char *cpFrstWrd;
       char *cpScndWrd;

       cpFrstWrd = strtok_r(sql_str, " ", &saveptr);
       cpScndWrd = strtok_r(NULL, " ", &saveptr);


       if (!((strncasecmp(cpFrstWrd, "SELECT", 6)==0)   || (strncasecmp(cpScndWrd, "SELECT", 6)==0))){   // now it will refuse queries with SELECT just when SELECT is in the beginning of the query.

       //if (!str_strstr(_s, &select_str)) {
                /* not a select statement; can execute the query and exit*/

Try adding here:

CON_RAW_QUERY(_h) = 1;

                if (sqlite3_exec(CON_CONNECTION(_h),
                        sql_str, NULL, NULL, &errmsg)) {
                        LM_ERR("query failed: %s\n", errmsg);
                        return -2;
                }

                return 0;
        }

// more logic follows here....


................................................................................................................................................



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'

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.opensips.org/pipermail/users/attachments/20151106/45f6c3cc/attachment.htm>