54c123f85ed2bc67f0f2d4bc9b1cfc87bb437462
[sip-router] / src / modules / sqlops / sql_api.c
1 /*
2  * Copyright (C) 2008 Elena-Ramona Modroiu (asipto.com)
3  *
4  * This file is part of kamailio, a free SIP server.
5  *
6  * Kamailio is free software; you can redistribute it and/or modify
7  * it under the terms of the GNU General Public License as published by
8  * the Free Software Foundation; either version 2 of the License, or
9  * (at your option) any later version
10  *
11  * Kamailio is distributed in the hope that it will be useful,
12  * but WITHOUT ANY WARRANTY; without even the implied warranty of
13  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14  * GNU General Public License for more details.
15  *
16  * You should have received a copy of the GNU General Public License
17  * along with this program; if not, write to the Free Software
18  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
19  */
20
21 /*! \file
22  * \ingroup sqlops
23  * \brief Kamailio SQL-operations :: API
24  *
25  * - Module: \ref sqlops
26  */
27
28
29 #include "../../core/mem/mem.h"
30 #include "../../core/dprint.h"
31 #include "../../core/hashes.h"
32 #include "../../core/ut.h"
33 #include "../../lib/srdb1/db_ut.h"
34 #include "../../core/xavp.h"
35
36 #include "sql_api.h"
37
38 sql_con_t *_sql_con_root = NULL;
39 sql_result_t *_sql_result_root = NULL;
40
41 static char _sql_empty_buf[1];
42
43 sql_con_t* sql_get_connection(str *name)
44 {
45         sql_con_t *sc;
46         unsigned int conid;
47
48         conid = core_case_hash(name, 0, 0);
49
50         sc = _sql_con_root;
51         while(sc)
52         {
53                 if(conid==sc->conid && sc->name.len==name->len
54                                 && strncmp(sc->name.s, name->s, name->len)==0)
55                         return sc;
56                 sc = sc->next;
57         }
58         return NULL;
59 }
60
61 int sql_init_con(str *name, str *url)
62 {
63         sql_con_t *sc;
64         unsigned int conid;
65
66         *_sql_empty_buf = '\0';
67
68         conid = core_case_hash(name, 0, 0);
69
70         sc = _sql_con_root;
71         while(sc)
72         {
73                 if(conid==sc->conid && sc->name.len==name->len
74                                 && strncmp(sc->name.s, name->s, name->len)==0)
75                 {
76                         LM_ERR("duplicate connection name\n");
77                         return -1;
78                 }
79                 sc = sc->next;
80         }
81         sc = (sql_con_t*)pkg_malloc(sizeof(sql_con_t));
82         if(sc==NULL)
83         {
84                 LM_ERR("no pkg memory\n");
85                 return -1;
86         }
87         memset(sc, 0, sizeof(sql_con_t));
88         sc->conid = conid;
89         sc->name = *name;
90         sc->db_url = *url;
91         sc->next = _sql_con_root;
92         _sql_con_root = sc;
93
94         return 0;
95 }
96
97 int pv_parse_con_name(pv_spec_p sp, str *in)
98 {
99         sql_con_t *con;
100
101         if(sp==NULL || in==NULL || in->len<=0)
102                 return -1;
103
104         con = sql_get_connection(in);
105         if (con==NULL) {
106                 LM_ERR("invalid connection [%.*s]\n", in->len, in->s);
107                 return -1;
108         }
109
110         sp->pvp.pvn.type = PV_NAME_INTSTR;
111         sp->pvp.pvn.u.isname.type = AVP_VAL_STR;
112         sp->pvp.pvn.u.isname.name.s = *in;
113         return 0;
114 }
115
116 int pv_get_sqlrows(struct sip_msg *msg,  pv_param_t *param,
117                 pv_value_t *res)
118 {
119         sql_con_t *con;
120         str* sc;
121
122         sc = &param->pvn.u.isname.name.s;
123         con = sql_get_connection(sc);
124         if(con==NULL)
125         {
126                 LM_ERR("invalid connection [%.*s]\n", sc->len, sc->s);
127                 return -1;
128         }
129
130         if (!DB_CAPABILITY(con->dbf, DB_CAP_AFFECTED_ROWS))
131         {
132                 LM_ERR("con: %p database module does not have DB_CAP_AFFECTED_ROWS [%.*s]\n",
133                        con, sc->len, sc->s);
134                 return -1;
135         }
136
137         return pv_get_sintval(msg, param, res, con->dbf.affected_rows(con->dbh));
138 }
139
140 int sql_connect(int mode)
141 {
142         sql_con_t *sc;
143         sc = _sql_con_root;
144         LM_DBG("trying to connect to database with mode %d\n", mode);
145         while(sc)
146         {
147                 if (db_bind_mod(&sc->db_url, &sc->dbf))
148                 {
149                         LM_DBG("database module not found for [%.*s]\n",
150                                         sc->name.len, sc->name.s);
151                         return -1;
152                 }
153                 if (!DB_CAPABILITY(sc->dbf, DB_CAP_RAW_QUERY))
154                 {
155                         LM_ERR("database module does not have DB_CAP_ALL [%.*s]\n",
156                                         sc->name.len, sc->name.s);
157                         return -1;
158                 }
159                 sc->dbh = sc->dbf.init(&sc->db_url);
160                 if (sc->dbh==NULL)
161                 {
162                         if(!mode) {
163                                 LM_ERR("failed to connect to the database [%.*s]\n",
164                                                 sc->name.len, sc->name.s);
165                                 return -1;
166                         } else {
167                                 LM_INFO("failed to connect to the database [%.*s] - trying next\n",
168                                                 sc->name.len, sc->name.s);
169                         }
170                 }
171                 sc = sc->next;
172         }
173         return 0;
174 }
175
176 int sql_reconnect(sql_con_t *sc)
177 {
178         if(sc==NULL) {
179                 LM_ERR("connection structure not initialized\n");
180                 return -1;
181         }
182         if (sc->dbh!=NULL) {
183                 /* already connected */
184                 return 0;
185         }
186         sc->dbh = sc->dbf.init(&sc->db_url);
187         if (sc->dbh==NULL) {
188                 LM_ERR("failed to connect to the database [%.*s]\n",
189                                         sc->name.len, sc->name.s);
190                 return -1;
191         }
192         return 0;
193 }
194
195 void sql_disconnect(void)
196 {
197         sql_con_t *sc;
198         sc = _sql_con_root;
199         while(sc)
200         {
201                 if (sc->dbh!=NULL)
202                         sc->dbf.close(sc->dbh);
203                 sc->dbh= NULL;
204                 sc = sc->next;
205         }
206 }
207
208 sql_result_t* sql_get_result(str *name)
209 {
210         sql_result_t *sr;
211         unsigned int resid;
212
213         resid = core_case_hash(name, 0, 0);
214
215         sr = _sql_result_root;
216         while(sr)
217         {
218                 if(sr->resid==resid && sr->name.len==name->len
219                                 && strncmp(sr->name.s, name->s, name->len)==0)
220                         return sr;
221                 sr = sr->next;
222         }
223         sr = (sql_result_t*)pkg_malloc(sizeof(sql_result_t) + name->len);
224         if(sr==NULL)
225         {
226                 LM_ERR("no pkg memory\n");
227                 return NULL;
228         }
229         memset(sr, 0, sizeof(sql_result_t));
230         memcpy(sr+1, name->s, name->len);
231         sr->name.s = (char *)(sr + 1);
232         sr->name.len = name->len;
233         sr->resid = resid;
234         sr->next = _sql_result_root;
235         _sql_result_root = sr;
236         return sr;
237 }
238
239 void sql_reset_result(sql_result_t *res)
240 {
241         int i, j;
242         if(res->cols)
243         {
244                 for(i=0; i<res->ncols; i++)
245                         if(res->cols[i].name.s!=NULL)
246                                 pkg_free(res->cols[i].name.s);
247                 pkg_free(res->cols);
248                 res->cols = NULL;
249         }
250         if(res->vals)
251         {
252                 for(i=0; i<res->nrows; i++)
253                 {
254                         if(res->vals[i])
255                         {
256                                 for(j=0; j<res->ncols; j++)
257                                 {
258                                         if(res->vals[i][j].flags&PV_VAL_STR
259                                                         && res->vals[i][j].value.s.len>0)
260                                                 pkg_free(res->vals[i][j].value.s.s);
261                                 }
262                                 pkg_free(res->vals[i]);
263                         }
264                 }
265                 pkg_free(res->vals);
266                 res->vals = NULL;
267         }
268         res->nrows = 0;
269         res->ncols = 0;
270 }
271
272 int sql_do_query(sql_con_t *con, str *query, sql_result_t *res)
273 {
274         db1_res_t* db_res = NULL;
275         int i, j;
276         str sv;
277
278         if(res) sql_reset_result(res);
279
280         if(query==NULL)
281         {
282                 LM_ERR("bad parameters\n");
283                 return -1;
284         }
285         if(con->dbf.raw_query(con->dbh, query, &db_res)!=0)
286         {
287                 LM_ERR("cannot do the query [%.*s]\n",
288                                 (query->len>64)?64:query->len, query->s);
289                 return -1;
290         }
291
292         if(db_res==NULL || RES_ROW_N(db_res)<=0 || RES_COL_N(db_res)<=0)
293         {
294                 LM_DBG("no result after query\n");
295                 if (db_res) con->dbf.free_result(con->dbh, db_res);
296                 return 2;
297         }
298         if(!res)
299         {
300                 LM_DBG("no sqlresult parameter, ignoring result from query\n");
301                 con->dbf.free_result(con->dbh, db_res);
302                 return 3;
303         }
304
305         res->ncols = RES_COL_N(db_res);
306         res->nrows = RES_ROW_N(db_res);
307         LM_DBG("rows [%d] cols [%d]\n", res->nrows, res->ncols);
308
309         res->cols = (sql_col_t*)pkg_malloc(res->ncols*sizeof(sql_col_t));
310         if(res->cols==NULL)
311         {
312                 res->ncols = 0;
313                 res->nrows = 0;
314                 LM_ERR("no more memory\n");
315                 return -1;
316         }
317         memset(res->cols, 0, res->ncols*sizeof(sql_col_t));
318         for(i=0; i<res->ncols; i++)
319         {
320                 res->cols[i].name.len = (RES_NAMES(db_res)[i])->len;
321                 res->cols[i].name.s = (char*)pkg_malloc((res->cols[i].name.len+1)
322                                 *sizeof(char));
323                 if(res->cols[i].name.s==NULL)
324                 {
325                         LM_ERR("no more memory\n");
326                         goto error;
327                 }
328                 memcpy(res->cols[i].name.s, RES_NAMES(db_res)[i]->s,
329                                 res->cols[i].name.len);
330                 res->cols[i].name.s[res->cols[i].name.len]='\0';
331                 res->cols[i].colid = core_case_hash(&res->cols[i].name, 0, 0);
332         }
333
334         res->vals = (sql_val_t**)pkg_malloc(res->nrows*sizeof(sql_val_t*));
335         if(res->vals==NULL)
336         {
337                 LM_ERR("no more memory\n");
338                 goto error;
339         }
340         memset(res->vals, 0, res->nrows*sizeof(sql_val_t*));
341         for(i=0; i<res->nrows; i++)
342         {
343                 res->vals[i] = (sql_val_t*)pkg_malloc(res->ncols*sizeof(sql_val_t));
344                 if(res->vals[i]==NULL)
345                 {
346                         LM_ERR("no more memory\n");
347                         goto error;
348                 }
349                 memset(res->vals[i], 0, res->ncols*sizeof(sql_val_t));
350                 for(j=0; j<res->ncols; j++)
351                 {
352                         if(RES_ROWS(db_res)[i].values[j].nul)
353                         {
354                                 res->vals[i][j].flags = PV_VAL_NULL;
355                                 continue;
356                         }
357                         sv.s = NULL;
358                         sv.len = 0;
359                         switch(RES_ROWS(db_res)[i].values[j].type)
360                         {
361                                 case DB1_STRING:
362                                         res->vals[i][j].flags = PV_VAL_STR;
363                                         sv.s=
364                                                 (char*)RES_ROWS(db_res)[i].values[j].val.string_val;
365                                         sv.len=strlen(sv.s);
366                                 break;
367                                 case DB1_STR:
368                                         res->vals[i][j].flags = PV_VAL_STR;
369                                         sv.len=
370                                                 RES_ROWS(db_res)[i].values[j].val.str_val.len;
371                                         sv.s=
372                                                 (char*)RES_ROWS(db_res)[i].values[j].val.str_val.s;
373                                 break;
374                                 case DB1_BLOB:
375                                         res->vals[i][j].flags = PV_VAL_STR;
376                                         sv.len=
377                                                 RES_ROWS(db_res)[i].values[j].val.blob_val.len;
378                                         sv.s=
379                                                 (char*)RES_ROWS(db_res)[i].values[j].val.blob_val.s;
380                                 break;
381                                 case DB1_INT:
382                                         res->vals[i][j].flags = PV_VAL_INT;
383                                         res->vals[i][j].value.n
384                                                 = (int)RES_ROWS(db_res)[i].values[j].val.int_val;
385                                 break;
386                                 case DB1_DATETIME:
387                                         res->vals[i][j].flags = PV_VAL_INT;
388                                         res->vals[i][j].value.n
389                                                 = (int)RES_ROWS(db_res)[i].values[j].val.time_val;
390                                 break;
391                                 case DB1_BITMAP:
392                                         res->vals[i][j].flags = PV_VAL_INT;
393                                         res->vals[i][j].value.n
394                                                 = (int)RES_ROWS(db_res)[i].values[j].val.bitmap_val;
395                                 break;
396                                 case DB1_BIGINT:
397                                         res->vals[i][j].flags = PV_VAL_STR;
398                                         res->vals[i][j].value.s.len = 21*sizeof(char);
399                                         res->vals[i][j].value.s.s
400                                                 = (char*)pkg_malloc(res->vals[i][j].value.s.len);
401                                         if(res->vals[i][j].value.s.s==NULL)
402                                         {
403                                                 LM_ERR("no more memory\n");
404                                                 goto error;
405                                         }
406                                         db_longlong2str(RES_ROWS(db_res)[i].values[j].val.ll_val,
407                                                         res->vals[i][j].value.s.s, &res->vals[i][j].value.s.len);
408                                 break;
409                                 default:
410                                         res->vals[i][j].flags = PV_VAL_NULL;
411                         }
412                         if(res->vals[i][j].flags == PV_VAL_STR && sv.s)
413                         {
414                                 if(sv.len<=0)
415                                 {
416                                         res->vals[i][j].value.s.s = _sql_empty_buf;
417                                         res->vals[i][j].value.s.len = 0;
418                                         continue;
419                                 }
420                                 res->vals[i][j].value.s.s
421                                         = (char*)pkg_malloc((sv.len+1)*sizeof(char));
422                                 if(res->vals[i][j].value.s.s==NULL)
423                                 {
424                                         LM_ERR("no more memory\n");
425                                         goto error;
426                                 }
427                                 memcpy(res->vals[i][j].value.s.s, sv.s, sv.len);
428                                 res->vals[i][j].value.s.s[sv.len] = '\0';
429                                 res->vals[i][j].value.s.len = sv.len;
430                         }
431                 }
432         }
433
434         con->dbf.free_result(con->dbh, db_res);
435         return 1;
436
437 error:
438         con->dbf.free_result(con->dbh, db_res);
439         sql_reset_result(res);
440         return -1;
441 }
442
443 int sql_do_query_async(sql_con_t *con, str *query)
444 {
445         if(query==NULL)
446         {
447                 LM_ERR("bad parameters\n");
448                 return -1;
449         }
450         if(con->dbf.raw_query_async==NULL) {
451                 LM_ERR("the db driver module doesn't support async query\n");
452                 return -1;
453         }
454         if(con->dbf.raw_query_async(con->dbh, query)!=0)
455         {
456                 LM_ERR("cannot do the query\n");
457                 return -1;
458         }
459         return 1;
460 }
461
462 int sql_exec_xquery(struct sip_msg *msg, sql_con_t *con, str *query,
463                 str *xavp)
464 {
465         db1_res_t* db_res = NULL;
466         sr_xavp_t *row = NULL;
467         sr_xval_t val;
468         int i, j;
469
470         if(msg==NULL || query==NULL || xavp==NULL)
471         {
472                 LM_ERR("bad parameters\n");
473                 return -1;
474         }
475
476         if(con->dbf.raw_query(con->dbh, query, &db_res)!=0)
477         {
478                 LM_ERR("cannot do the query\n");
479                 return -1;
480         }
481
482         if(db_res==NULL || RES_ROW_N(db_res)<=0 || RES_COL_N(db_res)<=0)
483         {
484                 LM_DBG("no result after query\n");
485                 con->dbf.free_result(con->dbh, db_res);
486                 return 2;
487         }
488
489         for(i=RES_ROW_N(db_res)-1; i>=0; i--)
490         {
491                 row = NULL;
492                 for(j=RES_COL_N(db_res)-1; j>=0; j--)
493                 {
494                         if(RES_ROWS(db_res)[i].values[j].nul)
495                         {
496                                 val.type = SR_XTYPE_NULL;
497                         } else
498                         {
499                                 switch(RES_ROWS(db_res)[i].values[j].type)
500                                 {
501                                         case DB1_STRING:
502                                                 val.type = SR_XTYPE_STR;
503                                                 val.v.s.s=
504                                                         (char*)RES_ROWS(db_res)[i].values[j].val.string_val;
505                                                 val.v.s.len=strlen(val.v.s.s);
506                                         break;
507                                         case DB1_STR:
508                                                 val.type = SR_XTYPE_STR;
509                                                 val.v.s.len=
510                                                         RES_ROWS(db_res)[i].values[j].val.str_val.len;
511                                                 val.v.s.s=
512                                                         (char*)RES_ROWS(db_res)[i].values[j].val.str_val.s;
513                                         break;
514                                         case DB1_BLOB:
515                                                 val.type = SR_XTYPE_STR;
516                                                 val.v.s.len=
517                                                         RES_ROWS(db_res)[i].values[j].val.blob_val.len;
518                                                 val.v.s.s=
519                                                         (char*)RES_ROWS(db_res)[i].values[j].val.blob_val.s;
520                                         break;
521                                         case DB1_INT:
522                                                 val.type = SR_XTYPE_INT;
523                                                 val.v.i
524                                                         = (int)RES_ROWS(db_res)[i].values[j].val.int_val;
525                                         break;
526                                         case DB1_DATETIME:
527                                                 val.type = SR_XTYPE_INT;
528                                                 val.v.i
529                                                         = (int)RES_ROWS(db_res)[i].values[j].val.time_val;
530                                         break;
531                                         case DB1_BITMAP:
532                                                 val.type = SR_XTYPE_INT;
533                                                 val.v.i
534                                                         = (int)RES_ROWS(db_res)[i].values[j].val.bitmap_val;
535                                         break;
536                                         case DB1_BIGINT:
537                                                 val.type = SR_XTYPE_LLONG;
538                                                 val.v.ll
539                                                         = RES_ROWS(db_res)[i].values[j].val.ll_val;
540                                         break;
541                                         default:
542                                                 val.type = SR_XTYPE_NULL;
543                                 }
544                         }
545                         /* Add column to current row, under the column's name */
546                         LM_DBG("Adding column: %.*s\n", RES_NAMES(db_res)[j]->len, RES_NAMES(db_res)[j]->s);
547                         xavp_add_value(RES_NAMES(db_res)[j], &val, &row);
548                 }
549                 /* Add row to result xavp */
550                 val.type = SR_XTYPE_XAVP;
551                 val.v.xavp = row;
552                 LM_DBG("Adding row\n");
553                 xavp_add_value(xavp, &val, NULL);
554         }
555
556         con->dbf.free_result(con->dbh, db_res);
557         return 1;
558 }
559
560
561 int sql_do_xquery(struct sip_msg *msg, sql_con_t *con, pv_elem_t *query,
562                 pv_elem_t *res)
563 {
564         str sv, xavp;
565         if(msg==NULL || query==NULL || res==NULL)
566         {
567                 LM_ERR("bad parameters\n");
568                 return -1;
569         }
570         if(pv_printf_s(msg, query, &sv)!=0)
571         {
572                 LM_ERR("cannot print the sql query\n");
573                 return -1;
574         }
575
576         if(pv_printf_s(msg, res, &xavp)!=0)
577         {
578                 LM_ERR("cannot print the result parameter\n");
579                 return -1;
580         }
581         return sql_exec_xquery(msg, con, &sv, &xavp);
582 }
583
584
585 int sql_do_pvquery(struct sip_msg *msg, sql_con_t *con, pv_elem_t *query,
586                 pvname_list_t *res)
587 {
588         db1_res_t* db_res = NULL;
589         pvname_list_t* pv;
590         str sv;
591         int i, j;
592
593         if(msg==NULL || query==NULL || res==NULL)
594         {
595                 LM_ERR("bad parameters\n");
596                 return -1;
597         }
598         if(pv_printf_s(msg, query, &sv)!=0)
599         {
600                 LM_ERR("cannot print the sql query\n");
601                 return -1;
602         }
603
604         if(con->dbf.raw_query(con->dbh, &sv, &db_res)!=0)
605         {
606                 LM_ERR("cannot do the query\n");
607                 return -1;
608         }
609
610         if(db_res==NULL || RES_ROW_N(db_res)<=0 || RES_COL_N(db_res)<=0)
611         {
612                 LM_DBG("no result after query\n");
613                 con->dbf.free_result(con->dbh, db_res);
614                 return 2;
615         }
616
617         for(i=RES_ROW_N(db_res)-1; i>=0; i--)
618         {
619                 pv = res;
620                 for(j=0; j<RES_COL_N(db_res); j++)
621                 {
622                         if (pv == NULL) {
623                                 LM_ERR("Missing pv spec for column %d\n", j+1);
624                                 goto error;
625                         }
626                         if (db_val2pv_spec(msg, &RES_ROWS(db_res)[i].values[j], &pv->sname) != 0) {
627                                 LM_ERR("Failed to convert value for column %.*s (row %d)\n",
628                                        RES_NAMES(db_res)[j]->len, RES_NAMES(db_res)[j]->s, i);
629                                 goto error;
630                         }
631                         pv = pv->next;
632                 }
633         }
634
635         con->dbf.free_result(con->dbh, db_res);
636         return 1;
637
638 error:
639         con->dbf.free_result(con->dbh, db_res);
640         return -1;
641 }
642
643
644 int sql_parse_param(char *val)
645 {
646         str name;
647         str tok;
648         str in;
649         char *p;
650
651         /* parse: name=>db_url*/
652         in.s = val;
653         in.len = strlen(in.s);
654         p = in.s;
655
656         while(p<in.s+in.len && (*p==' ' || *p=='\t' || *p=='\n' || *p=='\r'))
657                 p++;
658         if(p>in.s+in.len || *p=='\0')
659                 goto error;
660         name.s = p;
661         while(p < in.s + in.len)
662         {
663                 if(*p=='=' || *p==' ' || *p=='\t' || *p=='\n' || *p=='\r')
664                         break;
665                 p++;
666         }
667         if(p>in.s+in.len || *p=='\0')
668                 goto error;
669         name.len = p - name.s;
670         if(*p!='=')
671         {
672                 while(p<in.s+in.len && (*p==' ' || *p=='\t' || *p=='\n' || *p=='\r'))
673                         p++;
674                 if(p>in.s+in.len || *p=='\0' || *p!='=')
675                         goto error;
676         }
677         p++;
678         if(*p!='>')
679                 goto error;
680         p++;
681         while(p<in.s+in.len && (*p==' ' || *p=='\t' || *p=='\n' || *p=='\r'))
682                 p++;
683         tok.s = p;
684         tok.len = in.len + (int)(in.s - p);
685
686         LM_DBG("cname: [%.*s] url: [%.*s]\n", name.len, name.s, tok.len, tok.s);
687
688         return sql_init_con(&name, &tok);
689 error:
690         LM_ERR("invalid sqlops parameter [%.*s] at [%d]\n", in.len, in.s,
691                         (int)(p-in.s));
692         return -1;
693 }
694
695 void sql_destroy(void)
696 {
697         sql_result_t *r;
698         sql_result_t *r0;
699         
700         sql_disconnect();
701
702         r=_sql_result_root;
703         while(r)
704         {
705                 r0 = r->next;
706                 sql_reset_result(r);
707                 pkg_free(r);
708                 r = r0;
709         }
710         _sql_result_root = NULL;
711 }
712
713 /**
714  *
715  */
716 int sqlops_do_query(str *scon, str *squery, str *sres)
717 {
718         sql_con_t *con = NULL;
719         sql_result_t *res = NULL;
720
721         if (scon == NULL || scon->s == NULL)
722         {
723                 LM_ERR("invalid connection name\n");
724                 goto error;
725         }
726
727         con = sql_get_connection(scon);
728         if(con==NULL)
729         {
730                 LM_ERR("invalid connection [%.*s]\n", scon->len, scon->s);
731                 goto error;
732         }
733         /* Result parameter is optional */
734         if (sres && sres->s)
735         {
736                 res = sql_get_result(sres);
737                 if(res==NULL)
738                 {
739                         LM_ERR("invalid result [%.*s]\n", sres->len, sres->s);
740                         goto error;
741                 }
742         }
743         if(sql_do_query(con, squery, res)<0)
744                 goto error;
745
746         return 0;
747 error:
748         return -1;
749 }
750
751 /**
752  *
753  */
754 int sqlops_get_value(str *sres, int i, int j, sql_val_t **val)
755 {
756         sql_result_t *res = NULL;
757
758         if (sres == NULL || sres->s == NULL)
759         {
760                 LM_ERR("invalid result name\n");
761                 goto error;
762         }
763
764         res = sql_get_result(sres);
765         if(res==NULL)
766         {
767                 LM_ERR("invalid result [%.*s]\n", sres->len, sres->s);
768                 goto error;
769         }
770         if(i>=res->nrows)
771         {
772                 LM_ERR("row index out of bounds [%d/%d]\n", i, res->nrows);
773                 goto error;
774         }
775         if(j>=res->ncols)
776         {
777                 LM_ERR("column index out of bounds [%d/%d]\n", j, res->ncols);
778                 goto error;
779         }
780         *val = &res->vals[i][j];
781
782         return 0;
783 error:
784         return -1;
785 }
786
787 /**
788  *
789  */
790 int sqlops_is_null(str *sres, int i, int j)
791 {
792         sql_result_t *res = NULL;
793
794         if (sres == NULL || sres->s == NULL)
795         {
796                 LM_ERR("invalid result name\n");
797                 goto error;
798         }
799
800         res = sql_get_result(sres);
801         if(res==NULL)
802         {
803                 LM_ERR("invalid result [%.*s]\n", sres->len, sres->s);
804                 goto error;
805         }
806         if(i>=res->nrows)
807         {
808                 LM_ERR("row index out of bounds [%d/%d]\n", i, res->nrows);
809                 goto error;
810         }
811         if(j>=res->ncols)
812         {
813                 LM_ERR("column index out of bounds [%d/%d]\n", j, res->ncols);
814                 goto error;
815         }
816         if(res->vals[i][j].flags&PV_VAL_NULL)
817                 return 1;
818         return 0;
819 error:
820         return -1;
821 }
822
823 /**
824  *
825  */
826 int sqlops_get_column(str *sres, int i, str *col)
827 {
828         sql_result_t *res = NULL;
829
830         if (sres == NULL || sres->s == NULL)
831         {
832                 LM_ERR("invalid result name\n");
833                 goto error;
834         }
835
836         res = sql_get_result(sres);
837         if(res==NULL)
838         {
839                 LM_ERR("invalid result [%.*s]\n", sres->len, sres->s);
840                 goto error;
841         }
842         if(i>=res->ncols)
843         {
844                 LM_ERR("column index out of bounds [%d/%d]\n", i, res->ncols);
845                 goto error;
846         }
847         *col = res->cols[i].name;
848         return 0;
849 error:
850         return -1;
851 }
852
853 /**
854  *
855  */
856 int sqlops_num_columns(str *sres)
857 {
858         sql_result_t *res = NULL;
859
860         if (sres == NULL || sres->s == NULL)
861         {
862                 LM_ERR("invalid result name\n");
863                 goto error;
864         }
865
866         res = sql_get_result(sres);
867         if(res==NULL)
868         {
869                 LM_ERR("invalid result [%.*s]\n", sres->len, sres->s);
870                 goto error;
871         }
872         return res->ncols;
873 error:
874         return -1;
875 }
876
877 /**
878  *
879  */
880 int sqlops_num_rows(str *sres)
881 {
882         sql_result_t *res = NULL;
883
884         if (sres == NULL || sres->s == NULL)
885         {
886                 LM_ERR("invalid result name\n");
887                 goto error;
888         }
889
890         res = sql_get_result(sres);
891         if(res==NULL)
892         {
893                 LM_ERR("invalid result [%.*s]\n", sres->len, sres->s);
894                 goto error;
895         }
896         return res->nrows;
897 error:
898         return -1;
899 }
900
901 /**
902  *
903  */
904 void sqlops_reset_result(str *sres)
905 {
906         sql_result_t *res = NULL;
907
908         if (sres == NULL || sres->s == NULL)
909         {
910                 LM_ERR("invalid result name\n");
911                 return;
912         }
913
914         res = sql_get_result(sres);
915         if(res==NULL)
916         {
917                 LM_ERR("invalid result [%.*s]\n", sres->len, sres->s);
918                 return;
919         }
920         sql_reset_result(res);
921
922         return;
923 }
924
925 /**
926  *
927  */
928 int sqlops_do_xquery(sip_msg_t *msg, str *scon, str *squery, str *xavp)
929 {
930         sql_con_t *con = NULL;
931
932         if (scon == NULL || scon->s == NULL)
933         {
934                 LM_ERR("invalid connection name\n");
935                 goto error;
936         }
937
938         con = sql_get_connection(scon);
939         if(con==NULL)
940         {
941                 LM_ERR("invalid connection [%.*s]\n", scon->len, scon->s);
942                 goto error;
943         }
944         if(sql_exec_xquery(msg, con, squery, xavp)<0)
945                 goto error;
946
947         return 0;
948 error:
949         return -1;
950 }
951