Hi Bogdan.
I have just analyzed some more information about the case, as you asked me. See
below the queries that cause the error.
In addiction, in the past I had changed the file that you can find attached in
this message (it is for sqlite). There was an error in such file that prevented
me to execute these complex queiries. After discussing in this list, a solution
was proposed to such file, as you can see. Could you inspect the file and tell
me if there is some problem there? Any hint will be very helpful!
P.S.: before the error for the queries there is no another error just before as
seen in the log.
route{
...
if (has_totag()) {
if (loose_route()) {
...
} else {
if ( is_method("ACK") ) {
if ( t_check_trans() ) {
# non loose-route, but stateful ACK; must be an ACK after
# a 487 or e.g. 404 from upstream server
t_relay();
# if a call is rejected or the caller gives up waiting for
an answer, we have this 2 queries being executed:
avp_db_query("DELETE FROM acc WHERE time IN (SELECT time
FROM acc UNION SELECT time FROM missed_calls ORDER BY time DESC LIMIT 300
OFFSET 150)");
avp_db_query("DELETE FROM missed_calls WHERE time IN
(SELECT time FROM acc UNION SELECT time FROM missed_calls ORDER BY time DESC
LIMIT 300 OFFSET 150)");
exit;
} else {
# ACK without matching transaction ->
# ignore and discard
exit;
}
}
sl_send_reply("404","Not here");
}
exit;
}
...
if (is_method("INVITE")) {
...
}
if (is_method("REGISTER"))
{
...
# this query executes whenever the client does a SIP REGISTER:
avp_db_query("UPDATE location SET callerName='$fn' WHERE id =
last_insert_rowid()");
exit;
}
if ($rU==NULL) {
...
# when routing via usrloc, log the missed calls also
if (($fU==$avp(I_A)) || ($fU==$avp(I_B)) ){
setflag(ACC_MISSED);
}
route(relay);
}
route[relay] {
# for INVITEs enable some additional helper routes
if (is_method("INVITE")) {
#if (isflagset(NAT)) {
#rtpproxy_offer("ro");
#}
t_on_branch("per_branch_ops");
t_on_reply("handle_nat");
t_on_failure("missed_call");
}
...
#this queries execute whenever a peer is calling, or a peer answer, o a
call is terminated.
avp_db_query("DELETE FROM acc WHERE time IN (SELECT time FROM acc UNION
SELECT time FROM missed_calls ORDER BY time DESC LIMIT 300 OFFSET 150)");
avp_db_query("DELETE FROM missed_calls WHERE time IN (SELECT time FROM acc
UNION SELECT time FROM missed_calls ORDER BY time DESC LIMIT 300 OFFSET 150)");
exit;
}
Thanks a lot!
RODRIGO PIMENTA CARVALHO
Inatel Competence Center
Software
Ph: +55 35 3471 9200 RAMAL 979
________________________________
De: [email protected] <[email protected]> em nome
de Rodrigo Pimenta Carvalho <[email protected]>
Enviado: quarta-feira, 24 de fevereiro de 2016 09:07
Para: Bogdan-Andrei Iancu; OpenSIPS users mailling list
Cc: Daniel Lopes Fússia
Assunto: Re: [OpenSIPS-Users] Avp_db_query causing error with invalid parameter
value. How to fix it?
Hi Bogdan.
I will do this check and let you know the result. Wait some minutes, please.
Thanks.
RODRIGO PIMENTA CARVALHO
Inatel Competence Center
Software
Ph: +55 35 3471 9200 RAMAL 979
________________________________
De: Bogdan-Andrei Iancu <[email protected]>
Enviado: terça-feira, 23 de fevereiro de 2016 19:19
Para: OpenSIPS users mailling list
Cc: Daniel Lopes Fússia; Rodrigo Pimenta Carvalho
Assunto: Re: [OpenSIPS-Users] Avp_db_query causing error with invalid parameter
value. How to fix it?
Hi Rodrigo,
Can you identify the actual query (like maybe printing something in the script
via xlog) that generates this error ? also, are there any previous errors just
above this one ?
Regards,
Bogdan-Andrei Iancu
OpenSIPS Founder and Developer
http://www.opensips-solutions.com
[http://www.opensips-solutions.com/imgs/opensips-solutions-logo.gif]<http://www.opensips-solutions.com/>
Home — OpenSIPS Solutions<http://www.opensips-solutions.com/>
www.opensips-solutions.com
OpenSIPS is a mature Open Source implementation of a SIP server. OpenSIPS is
more than a SIP proxy/router as it includes application-level functionalities.
On 23.02.2016 22:06, Rodrigo Pimenta Carvalho wrote:
Hi.
Most of my queries in avp_db_query is causing the following error:
"ERROR:db_sqlite:db_sqlite_free_result: invalid parameter value"
How to fix it? I'm sure that all SQL queries is correct, as I have tested it in
an isolated way direct over the database. In addiction, all SQL commands with
avp_db_query can change the data in the database correctly. The unique issue is
the "ERROR" seen in the log.
The documentation doesn't comment on it.
Any hint will be very helpful!
Thanks a lot.
RODRIGO PIMENTA CARVALHO
Inatel Competence Center
Software
Ph: +55 35 3471 9200 RAMAL 979
_______________________________________________
Users mailing list
[email protected]<mailto:[email protected]>
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
/**
*
* Copyright (C) 2015 OpenSIPS Foundation
*
* This file is part of opensips, a free SIP server.
*
* opensips is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation; either version 2 of the License, or
* (at your option) any later version
*
* opensips is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
*
* History
* -------
* 2015-02-18 initial version (Ionut Ionita)
*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
#include "../../mem/mem.h"
#include "../../dprint.h"
#include "../../db/db_query.h"
#include "../../db/db_async.h"
#include "../../db/db_ut.h"
#include "../../db/db_insertq.h"
#include "../../db/db_res.h"
#include "my_con.h"
#include "val.h"
#include "res.h"
#include "row.h"
#include "dbase.h"
#define COUNT_QUERY "select count(*)"
#define COUNT_BUF_SIZE 2048
#define SQL_BUF_LEN 65536
static str query_holder = {NULL,0};
extern int db_sqlite_alloc_limit;
char count_buf[COUNT_BUF_SIZE]="select count(*)";
str count_str = {count_buf, sizeof(COUNT_QUERY)-1};
static inline int db_copy_rest_of_count(const str* query_holder, str* count_query);
static int db_sqlite_store_result(const db_con_t* _h, db_res_t** _r, const db_val_t* v, const int n);
#ifdef SQLITE_BIND
static int db_sqlite_bind_values(sqlite3_stmt* stmt, const db_val_t* _v, const int _n);
#endif
static int db_sqlite_submit_dummy_query(const db_con_t* _h, const str* _s)
{
query_holder = *_s;
return 0;
}
/**
* Initialize the database module.
* No function should be called before this
* \param _url URL used for initialization
* \return zero on success, negative value on failure
*/
db_con_t* db_sqlite_init(const str* _url)
{
return db_do_init(_url, (void *)db_sqlite_new_connection);
}
/**
* Shut down the database module.
* No function should be called after this
* \param _h handle to the closed connection
* \return zero on success, negative value on failure
*/
void db_sqlite_close(db_con_t* _h)
{
db_do_close(_h, db_sqlite_free_connection);
}
static inline int db_copy_rest_of_count(const str* query_holder, str* count_query)
{
char* found;
const str searched_str = {" from ", sizeof(" from ")-1};
count_query->len = sizeof(COUNT_QUERY)-1;
if ((found=str_strstr(query_holder, &searched_str)) != NULL) {
const int len=query_holder->len-(found-query_holder->s);
/* check for overflow */
if (len > COUNT_BUF_SIZE-(sizeof(COUNT_QUERY)-1)) {
LM_ERR("query too big! try reducing the size of your query!"
"Current max size [%d]!\n", COUNT_BUF_SIZE);
return -1;
}
memcpy(count_query->s+count_query->len, found, len);
count_query->len += len;
return 0;
}
return -1;
}
static inline int
db_sqlite_get_query_rows(const db_con_t* _h, const str* query, const db_val_t* _v, const int _n)
{
int ret;
sqlite3_stmt* stmt;
again:
ret=sqlite3_prepare_v2(CON_CONNECTION(_h), query->s, query->len, &stmt, NULL);
if (ret == SQLITE_BUSY)
goto again;
if (ret != SQLITE_OK) {
LM_ERR("failed to prepare query\n");
return -1;
}
#ifdef SQLITE_BIND
if (db_sqlite_bind_values(stmt, _v, _n) != SQLITE_OK) {
LM_ERR("failed to bind values\n");
return -1;
}
#endif
again2:
ret=sqlite3_step(stmt);
if (ret == SQLITE_BUSY)
goto again2;
if (ret != SQLITE_ROW) {
LM_ERR("failed to fetch query size\n");
return -1;
}
ret=sqlite3_column_int(stmt, 0);
sqlite3_finalize(stmt);
return ret;
}
int db_sqlite_query(const db_con_t* _h, const db_key_t* _k, const db_op_t* _op,
const db_val_t* _v, const db_key_t* _c, const int _n, const int _nc,
const db_key_t _o, db_res_t** _r)
{
int ret=-1;
#ifdef SQLITE_BIND
db_ps_t ps;
CON_SET_CURR_PS(_h, &ps);
#else
CON_RESET_CURR_PS(_h);
#endif
//CON_RAW_QUERY(_h) = 0;
ret = db_do_query(_h, _k, _op, _v, _c, _n, _nc, _o, NULL,
db_sqlite_val2str, db_sqlite_submit_dummy_query, NULL);
if (ret != 0) {
if (_r)
*_r = NULL;
return ret;
}
if (db_copy_rest_of_count(&query_holder, &count_str)) {
LM_ERR("failed to build row counter query\n");
return -1;
}
again:
ret=sqlite3_prepare_v2(CON_CONNECTION(_h),
query_holder.s, query_holder.len, &CON_SQLITE_PS(_h), NULL);
if (ret==SQLITE_BUSY)
goto again;
if (ret!=SQLITE_OK)
LM_ERR("failed to prepare: (%s)\n", sqlite3_errmsg(CON_CONNECTION(_h)));
#ifdef SQLITE_BIND
if (db_sqlite_bind_values(CON_SQLITE_PS(_h), _v, _n) != SQLITE_OK) {
LM_ERR("failed to bind values\n");
return -1;
}
#endif
if (_r) {
ret = db_sqlite_store_result(_h, _r, _v, _n);
CON_SQLITE_PS(_h) = NULL;
} else {
/* need to fetch now the total number of rows in query
* because later won't have the query string */
CON_PS_ROWS(_h) = db_sqlite_get_query_rows(_h, &count_str, _v, _n);
}
return ret;
}
/**
* Gets a partial result set.
* \param _h structure representing the database connection
* \param _r pointer to a structure representing the result
* \param nrows number of fetched rows
* \return zero on success, negative value on failure
*/
int db_sqlite_fetch_result(const db_con_t* _h, db_res_t** _r, const int nrows)
{
int ret;
int rows, i;
sqlite3_stmt* stmt;
if (!_h || !_r || nrows < 0) {
LM_ERR("Invalid parameter value\n");
return -1;
}
/* exit if the fetch count is zero */
if (nrows == 0) {
db_free_result(*_r);
*_r = 0;
return 0;
}
if(*_r==0) {
/* Allocate a new result structure */
*_r = db_new_result();
if (*_r == 0) {
LM_ERR("no memory left\n");
return -2;
}
if (db_sqlite_get_columns(_h, *_r) < 0) {
LM_ERR("error while getting column names\n");
return -4;
}
RES_NUM_ROWS(*_r) = CON_PS_ROWS(_h);
if (!RES_NUM_ROWS(*_r)) {
LM_DBG("no rows returned from the query\n");
RES_ROWS(*_r) = 0;
return 0;
}
} else {
/* free old rows */
if(RES_ROWS(*_r)!=0)
db_free_rows(*_r);
RES_ROWS(*_r) = 0;
RES_ROW_N(*_r) = 0;
}
/* determine the number of rows remaining to be processed */
rows = RES_NUM_ROWS(*_r) - RES_LAST_ROW(*_r);
/* If there aren't any more rows left to process, exit */
if(rows<=0)
return 0;
/* if the fetch count is less than the remaining rows to process */
/* set the number of rows to process (during this call) equal
to the fetch count */
if(nrows < rows)
rows = nrows;
RES_ROW_N(*_r) = rows;
if (db_sqlite_allocate_rows(*_r, rows)!=0) {
LM_ERR("no memory left\n");
return -5;
}
i = 0;
ret=-1;
stmt = CON_SQLITE_PS(_h);
while (ret != SQLITE_DONE) {
if (i == nrows) {
RES_LAST_ROW(*_r) = i - 1;
break;
}
ret = sqlite3_step(stmt);
if (ret == SQLITE_DONE) {
RES_ROW_N(*_r) = RES_LAST_ROW(*_r) = RES_NUM_ROWS(*_r) = i;
sqlite3_finalize(CON_SQLITE_PS(_h));
CON_SQLITE_PS(_h) = NULL;
break;
}
if (i >= RES_ROW_N(*_r) && i < nrows) {
db_sqlite_realloc_rows(*_r, RES_ROW_N(*_r) + db_sqlite_alloc_limit);
RES_ROW_N(*_r) += db_sqlite_alloc_limit;
}
if ((ret=db_sqlite_convert_row(_h, *_r, &(RES_ROWS(*_r)[i]))) < 0) {
LM_ERR("error while converting row #%d\n", i);
RES_ROW_N(*_r) = i;
db_free_rows(*_r);
return -4;
}
i++;
}
return 0;
}
/**
* Execute a raw SQL query.
* \param _h handle for the database
* \param _s raw query string
* \param _r result set for storage
* \return zero on success, negative value on failure
*/
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))){
//if (!str_strstr(_s, &select_str)) {
/* not a select statement; can execute the query and exit*/
memcpy(sql_str, _s->s, _s->len);
sql_str[_s->len] = '\0';
//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;
}
//CON_RAW_QUERY(_h) = 1;
if (db_copy_rest_of_count(_s, &count_str)) {
LM_ERR("failed to build count str!\n");
return -1;
}
again:
ret=sqlite3_prepare_v2(CON_CONNECTION(_h),
_s->s, _s->len, &CON_SQLITE_PS(_h), NULL);
if (ret==SQLITE_BUSY)
goto again;
if (ret!=SQLITE_OK)
LM_ERR("failed to prepare: (%s)\n",
sqlite3_errmsg(CON_CONNECTION(_h)));
if (_r) {
ret = db_sqlite_store_result(_h, _r, NULL, 0);
} else {
/* need to fetch now the total number of rows in query
* because later won't have the query string */
CON_PS_ROWS(_h) = db_sqlite_get_query_rows(_h, &count_str, NULL, 0);
}
return ret;
}
/**
* Insert a row into a specified table.
* \param _h structure representing database connection
* \param _k key names
* \param _v values of the keys
* \param _n number of key=value pairs
* \return zero on success, negative value on failure
*/
int db_sqlite_insert(const db_con_t* _h, const db_key_t* _k, const db_val_t* _v, const int _n)
{
int ret=-1;
sqlite3_stmt* stmt;
#ifdef SQLITE_BIND
db_ps_t ps;
CON_SET_CURR_PS(_h, &ps);
#else
CON_RESET_CURR_PS(_h);
#endif
ret = db_do_insert(_h, _k, _v, _n, db_sqlite_val2str,
db_sqlite_submit_dummy_query);
if (ret != 0) {
return ret;
}
again:
ret=sqlite3_prepare_v2(CON_CONNECTION(_h),
query_holder.s, query_holder.len, &stmt, NULL);
if (ret==SQLITE_BUSY)
goto again;
if (ret!=SQLITE_OK)
LM_ERR("failed to prepare: (%s)\n",
sqlite3_errmsg(CON_CONNECTION(_h)));
#ifdef SQLITE_BIND
if ((ret=db_sqlite_bind_values(stmt, _v, _n)) != SQLITE_OK) {
LM_ERR("failed to bind values (%d)\n", ret);
return -1;
}
#endif
again2:
ret = sqlite3_step(stmt);
if (ret==SQLITE_BUSY)
goto again2;
if (ret != SQLITE_DONE) {
LM_ERR("insert query failed %s\n", sqlite3_errmsg(CON_CONNECTION(_h)));
return -1;
}
sqlite3_finalize(stmt);
return 0;
}
/**
* Delete a row from the specified table
* \param _h structure representing database connection
* \param _k key names
* \param _o operators
* \param _v values of the keys that must match
* \param _n number of key=value pairs
* \return zero on success, negative value on failure
*/
int db_sqlite_delete(const db_con_t* _h, const db_key_t* _k, const db_op_t* _o,
const db_val_t* _v, const int _n)
{
int ret;
sqlite3_stmt* stmt;
#ifdef SQLITE_BIND
db_ps_t ps;
CON_SET_CURR_PS(_h, &ps);
#else
CON_RESET_CURR_PS(_h);
#endif
ret = db_do_delete(_h, _k, _o, _v, _n, db_sqlite_val2str,
db_sqlite_submit_dummy_query);
if (ret != 0) {
return ret;
}
again:
ret=sqlite3_prepare_v2(CON_CONNECTION(_h),
query_holder.s, query_holder.len, &stmt, NULL);
if (ret==SQLITE_BUSY)
goto again;
if (ret!=SQLITE_OK)
LM_ERR("failed to prepare: (%s)\n",
sqlite3_errmsg(CON_CONNECTION(_h)));
#ifdef SQLITE_BIND
if (db_sqlite_bind_values(stmt, _v, _n) != SQLITE_OK) {
LM_ERR("failed to bind values\n");
return -1;
}
#endif
again2:
ret = sqlite3_step(stmt);
if (ret==SQLITE_BUSY)
goto again2;
if (ret != SQLITE_DONE) {
LM_ERR("insert query failed %s\n", sqlite3_errmsg(CON_CONNECTION(_h)));
return -1;
}
sqlite3_finalize(stmt);
return 0;
}
/**
* Update some rows in the specified table
* \param _h structure representing database connection
* \param _k key names
* \param _o operators
* \param _v values of the keys that must match
* \param _uk updated columns
* \param _uv updated values of the columns
* \param _n number of key=value pairs
* \param _un number of columns to update
* \return zero on success, negative value on failure
*/
int db_sqlite_update(const db_con_t* _h, const db_key_t* _k, const db_op_t* _o,
const db_val_t* _v, const db_key_t* _uk, const db_val_t* _uv, const int _n,
const int _un)
{
int ret;
sqlite3_stmt* stmt;
#ifdef SQLITE_BIND
db_ps_t ps;
CON_SET_CURR_PS(_h, &ps);
#else
CON_RESET_CURR_PS(_h);
#endif
ret = db_do_update(_h, _k, _o, _v, _uk, _uv, _n, _un,
db_sqlite_val2str, db_sqlite_submit_dummy_query);
if (ret != 0) {
return ret;
}
again:
ret=sqlite3_prepare_v2(CON_CONNECTION(_h),
query_holder.s, query_holder.len, &stmt, NULL);
if (ret==SQLITE_BUSY)
goto again;
if (ret!=SQLITE_OK)
LM_ERR("failed to prepare: (%s)\n",
sqlite3_errmsg(CON_CONNECTION(_h)));
#ifdef SQLITE_BIND
if (db_sqlite_bind_values(stmt, _uv, _un) != SQLITE_OK
&& db_sqlite_bind_values(stmt, _v, _n)) {
LM_ERR("failed to bind values\n");
return -1;
}
#endif
again2:
ret = sqlite3_step(stmt);
if (ret==SQLITE_BUSY)
goto again2;
if (ret != SQLITE_DONE) {
LM_ERR("insert query failed %s\n", sqlite3_errmsg(CON_CONNECTION(_h)));
return -1;
}
sqlite3_finalize(stmt);
return 0;
}
/**
* Just like insert, but replace the row if it exists.
* \param _h database handle
* \param _k key names
* \param _v values of the keys that must match
* \param _n number of key=value pairs
* \return zero on success, negative value on failure
*/
int db_sqlite_replace(const db_con_t* _h, const db_key_t* _k, const db_val_t* _v, const int _n)
{
int ret;
sqlite3_stmt* stmt;
#ifdef SQLITE_BIND
db_ps_t ps;
CON_SET_CURR_PS(_h, &ps);
#else
CON_RESET_CURR_PS(_h);
#endif
ret = db_do_replace(_h, _k, _v, _n, db_sqlite_val2str,
db_sqlite_submit_dummy_query);
if (ret != 0) {
return ret;
}
again:
ret=sqlite3_prepare_v2(CON_CONNECTION(_h),
query_holder.s, query_holder.len, &stmt, NULL);
if (ret==SQLITE_BUSY)
goto again;
if (ret!=SQLITE_OK)
LM_ERR("failed to prepare: (%s)\n",
sqlite3_errmsg(CON_CONNECTION(_h)));
#ifdef SQLITE_BIND
if (db_sqlite_bind_values(stmt, _v, _n) != SQLITE_OK) {
LM_ERR("failed to bind values\n");
return -1;
}
#endif
again2:
ret = sqlite3_step(stmt);
if (ret==SQLITE_BUSY)
goto again2;
if (ret != SQLITE_DONE) {
LM_ERR("insert query failed %s\n", sqlite3_errmsg(CON_CONNECTION(_h)));
return -1;
}
sqlite3_finalize(stmt);
return 0;
}
/**
* Returns the last inserted ID.
* \param _h database handle
* \return returns the ID as integer or returns 0 if the previous statement
* does not use an AUTO_INCREMENT value.
*/
int db_last_inserted_id(const db_con_t* _h)
{
if (!_h) {
LM_ERR("invalid parameter value\n");
return -1;
}
return sqlite3_last_insert_rowid(CON_CONNECTION(_h));
}
/**
* Insert a row into a specified table, update on duplicate key.
* \param _h structure representing database connection
* \param _k key names
* \param _v values of the keys
* \param _n number of key=value pairs
*/
int db_insert_update(const db_con_t* _h, const db_key_t* _k, const db_val_t* _v,
const int _n)
{
#define SQL_BUF_LEN 65536
int off, ret;
static str sql_str;
static char sql_buf[SQL_BUF_LEN];
sqlite3_stmt* stmt;
if ((!_h) || (!_k) || (!_v) || (!_n)) {
LM_ERR("invalid parameter value\n");
return -1;
}
#ifdef SQLITE_BIND
db_ps_t ps;
CON_SET_CURR_PS(_h, &ps);
#endif
ret = snprintf(sql_buf, SQL_BUF_LEN, "insert into %.*s (",
CON_TABLE(_h)->len, CON_TABLE(_h)->s);
if (ret < 0 || ret >= SQL_BUF_LEN) goto error;
off = ret;
ret = db_print_columns(sql_buf + off, SQL_BUF_LEN - off, _k, _n);
if (ret < 0) return -1;
off += ret;
ret = snprintf(sql_buf + off, SQL_BUF_LEN - off, ") values (");
if (ret < 0 || ret >= (SQL_BUF_LEN - off)) goto error;
off += ret;
ret = db_print_values(_h, sql_buf + off, SQL_BUF_LEN - off, _v, _n,
db_sqlite_val2str);
if (ret < 0) return -1;
off += ret;
*(sql_buf + off++) = ')';
ret = snprintf(sql_buf + off, SQL_BUF_LEN - off, " on duplicate key update ");
if (ret < 0 || ret >= (SQL_BUF_LEN - off)) goto error;
off += ret;
ret = db_print_set(_h, sql_buf + off, SQL_BUF_LEN - off, _k, _v, _n,
db_sqlite_val2str);
if (ret < 0) return -1;
off += ret;
sql_str.s = sql_buf;
sql_str.len = off;
again:
ret=sqlite3_prepare_v2(CON_CONNECTION(_h),
sql_str.s, sql_str.len, &stmt, NULL);
if (ret==SQLITE_BUSY)
goto again;
if (ret!=SQLITE_OK)
LM_ERR("failed to prepare: (%s)\n",
sqlite3_errmsg(CON_CONNECTION(_h)));
#ifdef SQLITE_BIND
if (db_sqlite_bind_values(stmt, _v, _n) != SQLITE_OK) {
LM_ERR("failed to bind values\n");
return -1;
}
#endif
again2:
ret = sqlite3_step(stmt);
if (ret==SQLITE_BUSY)
goto again2;
if (ret != SQLITE_DONE) {
LM_ERR("insert query failed %s\n", sqlite3_errmsg(CON_CONNECTION(_h)));
return -1;
}
sqlite3_finalize(stmt);
return 0;
#undef SQL_BUF_LEN
error:
LM_ERR("error while preparing insert_update operation\n");
return -1;
}
/**
* Release a result set from memory.
* \param _h handle to the database
* \param _r result set that should be freed
* \return zero on success, negative value on failure
*/
int db_sqlite_free_result(db_con_t* _h, db_res_t* _r)
{
int i;
int j;
db_val_t* v;
db_row_t* res_col;
if ((!_h) || (!_r)) {
LM_ERR("invalid parameter value\n");
return -1;
}
if (RES_ROWS(_r)) {
LM_DBG("freeing rows at %p\n", RES_ROWS(_r));
for (i = 0; i < RES_ROW_N(_r); i++) {
for (j = 0; j < RES_COL_N(_r); j++) {
res_col = &_r->rows[i];
v = &res_col->values[j];
if (VAL_NULL(v))
continue;
/* only allocated types; STR and BLOB;*/
if (VAL_TYPE(v) == DB_STR) {
pkg_free(VAL_STR(v).s);
VAL_STR(v).s = 0;
} else if (VAL_TYPE(v) == DB_BLOB) {
pkg_free(VAL_BLOB(v).s);
VAL_BLOB(v).s = 0;
}
}
}
pkg_free(_r->rows[0].values);
pkg_free(_r->rows);
RES_ROWS(_r) = NULL;
}
RES_ROW_N(_r) = 0;
pkg_free(_r);
_r = NULL;
return 0;
}
/**
* Retrieve a result set
* \param _h handle to the database
* \param _r result set that should be retrieved
* \return zero on success, negative value on failure
*/
static int db_sqlite_store_result(const db_con_t* _h, db_res_t** _r, const db_val_t* _v, const int _n)
{
int rows;
if ((!_h) || (!_r)) {
LM_ERR("invalid parameter value\n");
return -1;
}
*_r = db_new_result();
if (*_r == 0) {
LM_ERR("no memory left\n");
return -2;
}
rows=db_sqlite_get_query_rows(_h, &count_str, _v, _n);
/* reset the length to initial for future uses */
if (rows < 0) {
LM_ERR("failed to fetch number of rows\n");
return -1;
}
/* trying to fetch all rows
* these values are not final values as, in the
* meantime, the db can be changed by another process */
RES_NUM_ROWS(*_r) = RES_ROW_N(*_r) = rows;
if (db_sqlite_convert_result(_h, *_r) < 0) {
LM_ERR("error while converting result\n");
pkg_free(*_r);
*_r = 0;
return -4;
}
return 0;
}
/**
* Store the name of table that will be used by subsequent database functions
* \param _h database handle
* \param _t table name
* \return zero on success, negative value on failure
*/
int db_sqlite_use_table(db_con_t* _h, const str* _t)
{
return db_use_table(_h, _t);
}
#ifdef SQLITE_BIND
static int db_sqlite_bind_values(sqlite3_stmt* stmt, const db_val_t* v, const int n)
{
int i, ret;
if (n>0 && v) {
for (i=0; i<n; i++) {
if (VAL_NULL(v+i)) {
ret=sqlite3_bind_null(stmt, i+1);
goto check_ret;
}
switch(VAL_TYPE(v+i)) {
/* every param has '+1' index because in sqlite the leftmost
* parameter has index '1' */
case DB_INT:
ret=sqlite3_bind_int(stmt, i+1, VAL_INT(v+i));
break;
case DB_BIGINT:
ret=sqlite3_bind_int64(stmt, i+1, VAL_BIGINT(v+i));
break;
case DB_DOUBLE:
ret=sqlite3_bind_double(stmt, i+1, VAL_DOUBLE(v+i));
break;
case DB_STRING:
ret=sqlite3_bind_text(stmt, i+1, VAL_STRING(v+i),
strlen(VAL_STRING(v+i)), SQLITE_STATIC);
break;
case DB_STR:
ret=sqlite3_bind_text(stmt, i+1, VAL_STR(v+i).s,
VAL_STR(v+i).len, SQLITE_STATIC);
break;
case DB_DATETIME:
ret=sqlite3_bind_int64(stmt, i+1, (long int)VAL_TIME(v+i));
break;
case DB_BLOB:
ret=sqlite3_bind_blob(stmt, i+1, (void*)VAL_BLOB(v+i).s,
VAL_BLOB(v+i).len, SQLITE_STATIC);
break;
case DB_BITMAP:
ret=sqlite3_bind_int(stmt, i+1, (int)VAL_BITMAP(v+i));
break;
default:
LM_BUG("invalid db type\n");
return 1;
}
check_ret:
if (ret != SQLITE_OK) {
return ret;
}
}
}
return SQLITE_OK;
}
#endif
_______________________________________________
Users mailing list
[email protected]
http://lists.opensips.org/cgi-bin/mailman/listinfo/users
