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

Fri Nov 6 12:58:31 CET 2015

Hi Aron.

I found the error here.

The function strtok_r changes the original query causing the error of syntax.

Now I have fixed the code and avp_db_query began to work very well to update  my table in SQLite.

So, my next step will be to test a more complex SQL query using avp_db_query.

P.S.: it was not necessary to use CON_RAW_QUERY(_h). Does it make sense for you?

Thanks.

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 Rodrigo Pimenta Carvalho <pimenta at inatel.br>
Enviado: sexta-feira, 6 de novembro de 2015 09:21
Para: OpenSIPS users mailling list
Assunto: Re: [OpenSIPS-Users] Why does avp_db_query rise an error when my SQL is correct?

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/45981547/attachment-0001.htm>