<div dir="ltr">You have to define the table structure with the right column types (including the mandatory KEY field), using the auxiliary db schema file [1].<div><br></div><div>Selecting all fields is never a good idea with Cassandra since the purpose of using SELECT in sqlops is to get the value of certain field into a pseudovariable. If you are selecting all of the fields, how do you know what column filled what pvar?<br>
<div><br></div><div>[1] <a href="http://kamailio.org/docs/modules/devel/modules/db_cassandra#idp2531032">http://kamailio.org/docs/modules/devel/modules/db_cassandra#idp2531032</a><br></div></div></div><div class="gmail_extra">
<br><br><div class="gmail_quote">On Wed, Mar 12, 2014 at 4:52 AM, jay binks <span dir="ltr"><<a href="mailto:jaybinks@gmail.com" target="_blank">jaybinks@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">So after moving to GIT head ( was using a version from a few weeks ago )<div>Im now getting heaps of these</div><div><br></div><div><div> 0(29141) ALERT: <script>: SELECT * FROM ns_ua_blacklist WHERE key = 'Cisco/SPA525G-7.4.6' limit 1; </div>
<div> 0(29141) ERROR: db_cassandra [dbcassa_base.cpp:649]: cql_get_columns(): No column with name [KEY] found</div><div> 0(29141) ERROR: db_cassandra [dbcassa_base.cpp:907]: db_cassa_raw_query(): Error getting column names.</div>
<div> 0(29141) ERROR: avpops [avpops_db.c:333]: db_query_avp(): cannot do the query</div></div><div><br></div><div>seemingly after this patch.</div><div>ill trace it through and see if I can produce a fix but I thought id just report it at least..</div>
<div><br></div><div>ALSO there are a few "<span style="font-family:arial,sans-serif;font-size:13px">LM_ERR" in there that need \n on the end..</span></div><div><span style="font-family:arial,sans-serif;font-size:13px">( ill include this in my patch I guess )</span></div>
<div><span style="font-family:arial,sans-serif;font-size:13px"><br></span></div></div><div class="gmail_extra"><div><div class="h5"><br><br><div class="gmail_quote">On 5 March 2014 21:39, Daniel-Constantin Mierla <span dir="ltr"><<a href="mailto:miconda@gmail.com" target="_blank">miconda@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Module: sip-router<br>
Branch: 4.1<br>
Commit: 520276f856474521cb419c11fa185ce9d6a8a285<br>
URL: <a href="http://git.sip-router.org/cgi-bin/gitweb.cgi/sip-router/?a=commit;h=520276f856474521cb419c11fa185ce9d6a8a285" target="_blank">http://git.sip-router.org/cgi-bin/gitweb.cgi/sip-router/?a=commit;h=520276f856474521cb419c11fa185ce9d6a8a285</a><br>
<br>
Author: Carlos Ruiz Diaz <<a href="mailto:carlos.ruizdiaz@gmail.com" target="_blank">carlos.ruizdiaz@gmail.com</a>><br>
Committer: Daniel-Constantin Mierla <<a href="mailto:miconda@gmail.com" target="_blank">miconda@gmail.com</a>><br>
Date: Tue Jan 14 10:04:53 2014 -0300<br>
<br>
db_cassandra: bug fixes for raw queries using sqlops<br>
<br>
(cherry picked from commit 2f9e8d2e122edc5b7950ee6ab0991851c9cd7bbd)<br>
<br>
---<br>
<br>
modules/db_cassandra/dbcassa_base.cpp | 210 +++++++++++++++++++++++++++------<br>
1 files changed, 175 insertions(+), 35 deletions(-)<br>
<br>
diff --git a/modules/db_cassandra/dbcassa_base.cpp b/modules/db_cassandra/dbcassa_base.cpp<br>
index e9d3a32..ffa9e3b 100644<br>
--- a/modules/db_cassandra/dbcassa_base.cpp<br>
+++ b/modules/db_cassandra/dbcassa_base.cpp<br>
@@ -252,6 +252,7 @@ static int cassa_convert_result(db_key_t qcol, std::vector<oac::ColumnOrSuperCol<br>
res_col = result[idx_rescol].column;<br>
<br>
col_val.s = (char*)res_col.value.c_str();<br>
+<br>
if(!col_val.s) {<br>
LM_DBG("Column not found in result %.*s- NULL\n", qcol->len, qcol->s);<br>
sr_cell->nul = 1;<br>
@@ -585,7 +586,7 @@ ColumnVecPtr cassa_translate_query(const db1_con_t* _h, const db_key_t* _k,<br>
* \param _r result set for storage<br>
* \return zero on success, negative value on failure<br>
*/<br>
-int cql_get_columns(oac::CqlResult& _cql_res, db1_res_t* _r)<br>
+int cql_get_columns(oac::CqlResult& _cql_res, db1_res_t* _r, dbcassa_table_p tbc)<br>
{<br>
std::vector<oac::CqlRow> res_cql_rows = _cql_res.rows;<br>
int rows_no = res_cql_rows.size();<br>
@@ -630,14 +631,86 @@ int cql_get_columns(oac::CqlResult& _cql_res, db1_res_t* _r)<br>
/* The pointer that is here returned is part of the result structure. */<br>
RES_NAMES(_r)[col]->s = (char*) res_cql_rows[0].columns[col].name.c_str();<br>
RES_NAMES(_r)[col]->len = strlen(RES_NAMES(_r)[col]->s);<br>
- RES_TYPES(_r)[col] = DB1_STR;<br>
<br>
+ /* search the column in table schema to get the type */<br>
+ dbcassa_column_p colp = cassa_search_col(tbc, (db_key_t) RES_NAMES(_r)[col]);<br>
+ if(!colp) {<br>
+ LM_ERR("No column with name [%.*s] found\n", RES_NAMES(_r)[col]->len, RES_NAMES(_r)[col]->s);<br>
+ RES_COL_N(_r) = col;<br>
+ db_free_columns(_r);<br>
+ return -4;<br>
+ }<br>
+<br>
+ RES_TYPES(_r)[col] = colp->type;<br>
+<br>
+ LM_DBG("Column with name [%.*s] found: %d\n", RES_NAMES(_r)[col]->len, RES_NAMES(_r)[col]->s, colp->type);<br>
LM_DBG("RES_NAMES(%p)[%d]=[%.*s]\n", RES_NAMES(_r)[col], col,<br>
RES_NAMES(_r)[col]->len, RES_NAMES(_r)[col]->s);<br>
}<br>
return 0;<br>
}<br>
<br>
+static int cassa_convert_result_raw(db_val_t* sr_cell, str *col_val) {<br>
+<br>
+ if(!col_val->s) {<br>
+ LM_DBG("Column not found in result - NULL\n");<br>
+ sr_cell->nul = 1;<br>
+ return 0;<br>
+ }<br>
+ col_val->len = strlen(col_val->s);<br>
+<br>
+ sr_cell->nul = 0;<br>
+ sr_cell->free = 0;<br>
+<br>
+ switch (sr_cell->type) {<br>
+ case DB1_INT:<br>
+ if(str2int(col_val, (unsigned int*)&sr_cell->val.int_val) < 0) {<br>
+ LM_ERR("Wrong value [%s] - len=%d, expected integer\n", col_val->s, col_val->len);<br>
+ return -1;<br>
+ }<br>
+ break;<br>
+ case DB1_BIGINT:<br>
+ if(sscanf(col_val->s, "%lld", &sr_cell->val.ll_val) < 0) {<br>
+ LM_ERR("Wrong value [%s], expected integer\n", col_val->s);<br>
+ return -1;<br>
+ }<br>
+ break;<br>
+ case DB1_DOUBLE:<br>
+ if(sscanf(col_val->s, "%lf", &sr_cell->val.double_val) < 0) {<br>
+ LM_ERR("Wrong value [%s], expected integer\n", col_val->s);<br>
+ return -1;<br>
+ }<br>
+ break;<br>
+ case DB1_STR:<br>
+ pkg_str_dup(&sr_cell->val.str_val, col_val);<br>
+ sr_cell->free = 1;<br>
+ break;<br>
+ case DB1_STRING:<br>
+ col_val->len++;<br>
+ pkg_str_dup(&sr_cell->val.str_val, col_val);<br>
+ sr_cell->val.str_val.len--;<br>
+ sr_cell->val.str_val.s[col_val->len-1]='\0';<br>
+ sr_cell->free = 1;<br>
+ break;<br>
+ case DB1_BLOB:<br>
+ pkg_str_dup(&sr_cell->val.blob_val, col_val);<br>
+ sr_cell->free = 1;<br>
+ break;<br>
+ case DB1_BITMAP:<br>
+ if(str2int(col_val, &sr_cell->val.bitmap_val) < 0) {<br>
+ LM_ERR("Wrong value [%s], expected integer\n", col_val->s);<br>
+ return -1;<br>
+ }<br>
+ break;<br>
+ case DB1_DATETIME:<br>
+ if(sscanf(col_val->s, "%ld", (long int*)&sr_cell->val.time_val) < 0) {<br>
+ LM_ERR("Wrong value [%s], expected integer\n", col_val->s);<br>
+ return -1;<br>
+ }<br>
+ break;<br>
+ }<br>
+ return 0;<br>
+}<br>
<br>
<br>
/**<br>
@@ -649,35 +722,33 @@ int cql_get_columns(oac::CqlResult& _cql_res, db1_res_t* _r)<br>
* \param _r result set for storage<br>
* \return zero on success, negative value on failure<br>
*/<br>
+<br>
int cql_convert_row(oac::CqlResult& _cql_res, db1_res_t* _r)<br>
{<br>
std::vector<oac::CqlRow> res_cql_rows = _cql_res.rows;<br>
- int rows_no = res_cql_rows.size();<br>
- int cols_no = res_cql_rows[0].columns.size();<br>
- str col_val;<br>
-<br>
- RES_ROW_N(_r) = rows_no;<br>
-<br>
- if (db_allocate_rows(_r) < 0) {<br>
- LM_ERR("Could not allocate rows.\n");<br>
- return -1;<br>
- }<br>
-<br>
- for(int ri=0; ri < rows_no; ri++) {<br>
- if (db_allocate_row(_r, &(RES_ROWS(_r)[ri])) != 0) {<br>
- LM_ERR("Could not allocate row.\n");<br>
- return -2;<br>
- }<br>
-<br>
- /* complete the row with the columns */<br>
+ int rows_no = res_cql_rows.size();<br>
+ int cols_no = res_cql_rows[0].columns.size();<br>
+ str col_val;<br>
+ RES_ROW_N(_r) = rows_no;<br>
+<br>
+ if (db_allocate_rows(_r) < 0) {<br>
+ LM_ERR("Could not allocate rows.\n");<br>
+ return -1;<br>
+ }<br>
+<br>
+ for(int ri=0; ri < rows_no; ri++) {<br>
+ if (db_allocate_row(_r, &(RES_ROWS(_r)[ri])) != 0) {<br>
+ LM_ERR("Could not allocate row.\n");<br>
+ return -2;<br>
+ }<br>
+<br>
+ // complete the row with the columns<br>
for(int col = 0; col< cols_no; col++) {<br>
- RES_ROWS(_r)[ri].values[col].type = DB1_STR;<br>
-<br>
col_val.s = (char*)res_cql_rows[ri].columns[col].value.c_str();<br>
- col_val.len = strlen(col_val.s);<br>
- pkg_str_dup(&RES_ROWS(_r)[ri].values[col].val.str_val, &col_val);<br>
- RES_ROWS(_r)[ri].values[col].free = 1;<br>
- RES_ROWS(_r)[ri].values[col].nul = 0;<br>
+ col_val.len = strlen(col_val.s);<br>
+<br>
+ RES_ROWS(_r)[ri].values[col].type = RES_TYPES(_r)[col];<br>
+ cassa_convert_result_raw(&RES_ROWS(_r)[ri].values[col], &col_val);<br>
<br>
LM_DBG("Field index %d. %s = %s.\n", col,<br>
res_cql_rows[ri].columns[col].name.c_str(),<br>
@@ -685,13 +756,62 @@ int cql_convert_row(oac::CqlResult& _cql_res, db1_res_t* _r)<br>
}<br>
}<br>
return 0;<br>
-}<br>
-<br>
+}<br>
<br>
/*<br>
* The functions for the DB Operations: query, delete, update.<br>
* */<br>
<br>
+/*<br>
+ * Extracts table name from DML query being used<br>
+ *<br>
+ * */<br>
+static int get_table_from_query(const str *cql, str *table) {<br>
+<br>
+ char *ptr = cql->s,<br>
+ *begin = NULL;<br>
+<br>
+ if (cql->s[0] == 's' || cql->s[0] == 'S') {<br>
+ ptr = strcasestr(cql->s, "from");<br>
+ ptr += sizeof(char) * 4;<br>
+ }<br>
+ else if (cql->s[0] == 'u' || cql->s[0] == 'U') {<br>
+ ptr = cql->s + sizeof("update") - 1;<br>
+ }<br>
+ else if (cql->s[0] == 'd' || cql->s[0] == 'D') {<br>
+ ptr = strcasestr(cql->s, "from");<br>
+ ptr += sizeof(char) * 4;<br>
+ }<br>
+ else if (cql->s[0] == 'i' || cql->s[0] == 'I') {<br>
+ ptr = strcasestr(cql->s, "into");<br>
+ ptr += sizeof(char) * 4;<br>
+ }<br>
+ else<br>
+ goto error;<br>
+<br>
+ while (*ptr == ' ' && (ptr - cql->s) <= cql->len) {<br>
+ ptr++;<br>
+ }<br>
+<br>
+ begin = ptr;<br>
+ ptr = strchr(begin, ' ');<br>
+<br>
+ if (ptr == NULL)<br>
+ ptr = cql->s + cql->len;<br>
+<br>
+ if (ptr - begin <= 0)<br>
+ goto error;<br>
+<br>
+ table->s = begin;<br>
+ table->len = ptr - begin;<br>
+<br>
+ return 0;<br>
+<br>
+error:<br>
+ LM_ERR("Unable to determine operation in cql [%*s]\n", cql->len, cql->s);<br>
+ return -1;<br>
+}<br>
+<br>
/**<br>
* Execute a raw SQL query.<br>
* \param _h handle for the database<br>
@@ -702,14 +822,29 @@ int cql_convert_row(oac::CqlResult& _cql_res, db1_res_t* _r)<br>
int db_cassa_raw_query(const db1_con_t* _h, const str* _s, db1_res_t** _r)<br>
{<br>
db1_res_t* db_res = 0;<br>
+ str table_name;<br>
+ dbcassa_table_p tbc;<br>
+ std::vector<oac::CqlRow> res_cql_rows;<br>
<br>
- if (!_h || !CON_TABLE(_h) || !_r) {<br>
+ if (!_h || !_r) {<br>
LM_ERR("Invalid parameter value\n");<br>
return -1;<br>
}<br>
- LM_DBG("query table=%s\n", _h->table->s);<br>
+<br>
+ if (get_table_from_query(_s, &table_name) < 0) {<br>
+ LM_ERR("Error parsing table name in CQL string");<br>
+ return -1;<br>
+ }<br>
+<br>
+ LM_DBG("query table=%.*s\n", table_name.len, table_name.s);<br>
LM_DBG("CQL=%s\n", _s->s);<br>
<br>
+ tbc = dbcassa_db_get_table(&CON_CASSA(_h)->db_name, &table_name);<br>
+ if(!tbc) {<br>
+ LM_ERR("table %.*s does not exist!\n", table_name.len, table_name.s);<br>
+ return -1;<br>
+ }<br>
+<br>
std::string cql_query(_s->s);<br>
<br>
oac::CqlResult cassa_cql_res;<br>
@@ -728,9 +863,11 @@ int db_cassa_raw_query(const db1_con_t* _h, const str* _s, db1_res_t** _r)<br>
<br>
if (!cassa_cql_res.__isset.rows) {<br>
LM_ERR("The resultype rows was not set, no point trying to parse result.\n");<br>
- return -1;<br>
+ goto error;<br>
}<br>
<br>
+ res_cql_rows = cassa_cql_res.rows;<br>
+<br>
/* TODO Handle the other types */<br>
switch(cassa_cql_res.type) {<br>
case 1: LM_DBG("Result set is an ROW Type.\n");<br>
@@ -741,8 +878,6 @@ int db_cassa_raw_query(const db1_con_t* _h, const str* _s, db1_res_t** _r)<br>
break;<br>
}<br>
<br>
- std::vector<oac::CqlRow> res_cql_rows = cassa_cql_res.rows;<br>
-<br>
db_res = db_new_result();<br>
if (!db_res) {<br>
LM_ERR("no memory left\n");<br>
@@ -754,10 +889,10 @@ int db_cassa_raw_query(const db1_con_t* _h, const str* _s, db1_res_t** _r)<br>
RES_ROW_N(db_res) = 0;<br>
RES_COL_N(db_res)= 0;<br>
*_r = db_res;<br>
- return 0;<br>
+ goto done;<br>
}<br>
<br>
- if (cql_get_columns(cassa_cql_res, db_res) < 0) {<br>
+ if (cql_get_columns(cassa_cql_res, db_res, tbc) < 0) {<br>
LM_ERR("Error getting column names.");<br>
goto error;<br>
}<br>
@@ -768,12 +903,17 @@ int db_cassa_raw_query(const db1_con_t* _h, const str* _s, db1_res_t** _r)<br>
}<br>
<br>
*_r = db_res;<br>
+done:<br>
+ dbcassa_lock_release(tbc);<br>
+<br>
LM_DBG("Exited with success\n");<br>
return 0;<br>
<br>
error:<br>
if(db_res)<br>
db_free_result(db_res);<br>
+<br>
+ dbcassa_lock_release(tbc);<br>
return -1;<br>
}<br>
<br>
<br>
<br>
_______________________________________________<br>
sr-dev mailing list<br>
<a href="mailto:sr-dev@lists.sip-router.org" target="_blank">sr-dev@lists.sip-router.org</a><br>
<a href="http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev" target="_blank">http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev</a><br>
</blockquote></div><br><br clear="all"><div><br></div></div></div><span class="HOEnZb"><font color="#888888">-- <br>Sincerely<br><br>Jay
</font></span></div>
<br>_______________________________________________<br>
sr-dev mailing list<br>
<a href="mailto:sr-dev@lists.sip-router.org">sr-dev@lists.sip-router.org</a><br>
<a href="http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev" target="_blank">http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev</a><br>
<br></blockquote></div><br><br clear="all"><div><br></div>-- <br><div dir="ltr">Carlos<div><a href="http://caruizdiaz.com" target="_blank">http://caruizdiaz.com</a></div><div><a href="http://ngvoice.com" target="_blank">http://ngvoice.com</a></div>
<div>+595981146623</div></div>
</div>