Kannel: Open Source WAP and SMS gateway  svn-r5335
dbpool_oracle.c
Go to the documentation of this file.
1 /* ====================================================================
2  * The Kannel Software License, Version 1.0
3  *
4  * Copyright (c) 2001-2018 Kannel Group
5  * Copyright (c) 1998-2001 WapIT Ltd.
6  * All rights reserved.
7  *
8  * Redistribution and use in source and binary forms, with or without
9  * modification, are permitted provided that the following conditions
10  * are met:
11  *
12  * 1. Redistributions of source code must retain the above copyright
13  * notice, this list of conditions and the following disclaimer.
14  *
15  * 2. Redistributions in binary form must reproduce the above copyright
16  * notice, this list of conditions and the following disclaimer in
17  * the documentation and/or other materials provided with the
18  * distribution.
19  *
20  * 3. The end-user documentation included with the redistribution,
21  * if any, must include the following acknowledgment:
22  * "This product includes software developed by the
23  * Kannel Group (http://www.kannel.org/)."
24  * Alternately, this acknowledgment may appear in the software itself,
25  * if and wherever such third-party acknowledgments normally appear.
26  *
27  * 4. The names "Kannel" and "Kannel Group" must not be used to
28  * endorse or promote products derived from this software without
29  * prior written permission. For written permission, please
30  * contact org@kannel.org.
31  *
32  * 5. Products derived from this software may not be called "Kannel",
33  * nor may "Kannel" appear in their name, without prior written
34  * permission of the Kannel Group.
35  *
36  * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
37  * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
38  * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
39  * DISCLAIMED. IN NO EVENT SHALL THE KANNEL GROUP OR ITS CONTRIBUTORS
40  * BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY,
41  * OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT
42  * OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR
43  * BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
44  * WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE
45  * OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE,
46  * EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
47  * ====================================================================
48  *
49  * This software consists of voluntary contributions made by many
50  * individuals on behalf of the Kannel Group. For more information on
51  * the Kannel Group, please see <http://www.kannel.org/>.
52  *
53  * Portions of this software are based upon software originally written at
54  * WapIT Ltd., Helsinki, Finland for the Kannel project.
55  */
56 
57 /*
58  * dbpool_oracle.c - implement Oracle operations for generic database connection pool
59  *
60  * Note: Oracle 8i and 9i support tested. This implementation will not
61  * work with Oracle 7 version. Please do not use Oracle 9i-rc2 OCI
62  * libraries on Linux, due to strange memory problems. If you do not
63  * believe me, just check it youself with valgrind ;)
64  *
65  * Alexander Malysh <a.malysh@centrium.de>
66  * Robert Gałach <robert.galach@my.tenbit.pl>
67  * 2004 Added support for binding variables.
68  */
69 
70 #ifdef HAVE_ORACLE
71 
72 #include <oci.h>
73 
74 /* forward declaration */
75 static int oracle_select(void *theconn, const Octstr *sql, List *binds, List **res);
76 
77 struct ora_conn {
78  /* environment handle */
79  OCIEnv *envp;
80  /* context handle */
81  OCISvcCtx *svchp;
82  /* error handle */
83  OCIError *errhp;
84 };
85 
86 /* This function prints the error */
87 static void oracle_checkerr(OCIError *errhp, sword status)
88 {
89  text errbuf[512];
90  sb4 errcode = 0;
91 
92  switch (status) {
93  case OCI_SUCCESS:
94  break;
95  case OCI_SUCCESS_WITH_INFO:
96  error(0, "Error - OCI_SUCCESS_WITH_INFO");
97  break;
98  case OCI_NEED_DATA:
99  error(0, "Error - OCI_NEED_DATA");
100  break;
101  case OCI_NO_DATA:
102  error(0, "Error - OCI_NODATA");
103  break;
104  case OCI_ERROR:
105  if (errhp == NULL) break;
106  (void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
107  errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
108  error(0, "Error - %.*s", 512, errbuf);
109  break;
110  case OCI_INVALID_HANDLE:
111  error(0, "Error - OCI_INVALID_HANDLE");
112  break;
113  case OCI_STILL_EXECUTING:
114  error(0, "Error - OCI_STILL_EXECUTE");
115  break;
116  case OCI_CONTINUE:
117  error(0, "Error - OCI_CONTINUE");
118  break;
119  default:
120  break;
121  }
122 }
123 
124 
125 /*
126  * Malloc callback function to get tracking of OCI allocs.
127  */
128 static void *oracle_malloc(void *ctx, size_t size)
129 {
130  void *ret = gw_malloc(size);
131  debug("dbpool.oracle",0,"oracle_malloc called size=%ld @%08lx", (long) size,
132  (long) ret);
133  return ret;
134 }
135 
136 
137 /*
138  * Free callback function to get tracking of OCI allocs.
139  */
140 static void oracle_free(void *ctx, void *ptr)
141 {
142  debug("dbpool.oracle",0,"oracle_free called @%08lx", (long) ptr);
143  gw_free(ptr);
144 }
145 
146 
147 /*
148  * Realloc callback function to get tracking of OCI allocs.
149  */
150 static void *oracle_realloc(void *ctx, void *ptr, size_t size)
151 {
152  void *ret = gw_realloc(ptr, size);
153  debug("dbpool.oracle",0,"oracle_realloc called size=%ld", (long) size);
154  return ret;
155 }
156 
157 static void* oracle_open_conn(const DBConf *db_conf)
158 {
159  OracleConf *cfg = db_conf->oracle;
160  sword errorcode = 0;
161  text version[512];
162  struct ora_conn *conn = gw_malloc(sizeof(struct ora_conn));
163 
164  gw_assert(conn != NULL);
165  memset(conn, 0, sizeof(struct ora_conn));
166 
167  debug("dbpool.oracle",0,"oracle_open_conn called");
168 
169  /* init OCI environment */
170  errorcode = OCIEnvCreate(&conn->envp,
171  OCI_THREADED|OCI_ENV_NO_MUTEX,
172  NULL,
173  oracle_malloc,
174  oracle_realloc,
175  oracle_free,
176  0,0);
177  if (errorcode != OCI_SUCCESS) {
178  oracle_checkerr(NULL, errorcode);
179  error(0, "Got error while OCIEnvCreate %d", errorcode);
180  gw_free(conn);
181  return NULL;
182  }
183 
184  debug("dbpool.oracle",0,"oci environment created");
185 
186  /* allocate error handle */
187  errorcode = OCIHandleAlloc(conn->envp, (dvoid**) &conn->errhp,
188  OCI_HTYPE_ERROR, 0, 0);
189  if (errorcode != OCI_SUCCESS) {
190  oracle_checkerr(NULL, errorcode);
191  OCIHandleFree(conn->envp, OCI_HTYPE_ENV);
192  gw_free(conn);
193  return NULL;
194  }
195 
196  debug("dbpool.oracle",0,"oci error handle allocated");
197 
198  /* open oracle user session */
199  errorcode = OCILogon(conn->envp, conn->errhp, &conn->svchp,
200  (unsigned char*)octstr_get_cstr(cfg->username), octstr_len(cfg->username),
201  (unsigned char*)octstr_get_cstr(cfg->password), octstr_len(cfg->password),
202  (unsigned char*)octstr_get_cstr(cfg->tnsname), octstr_len(cfg->tnsname));
203 
204  if (errorcode != OCI_SUCCESS) {
205  oracle_checkerr(conn->errhp, errorcode);
206  OCIHandleFree(conn->errhp, OCI_HTYPE_ERROR);
207  OCIHandleFree(conn->envp, OCI_HTYPE_ENV);
208  gw_free(conn);
209  return NULL;
210  }
211 
212  debug("dbpool.oracle",0,"connected to database");
213 
214  errorcode = OCIServerVersion(conn->svchp, conn->errhp, version,
215  sizeof(version), OCI_HTYPE_SVCCTX);
216  if (errorcode != OCI_SUCCESS) {
217  oracle_checkerr(conn->errhp, errorcode);
218  } else {
219  info(0, "Connected to: %s", version);
220  }
221 
222  return conn;
223 }
224 
225 
226 static void oracle_close_conn(void *theconn)
227 {
228  struct ora_conn *conn = (struct ora_conn*) theconn;
229 
230  gw_assert(conn != NULL);
231 
232  if (conn->svchp != NULL)
233  oracle_checkerr(conn->errhp, OCILogoff(conn->svchp, conn->errhp));
234 
235  OCIHandleFree(conn->errhp, OCI_HTYPE_ERROR);
236  OCIHandleFree(conn->envp, OCI_HTYPE_ENV);
237  /* OCITerminate(OCI_DEFAULT); */
238 
239  gw_free(conn);
240 }
241 
242 
243 static int oracle_check_conn(void *theconn)
244 {
245  int ret;
246 
247 #ifdef HAVE_OCIPING
248  struct ora_conn *conn = (struct ora_conn*) theconn;
249  sword result;
250 
251  gw_assert(conn != NULL);
252 
253  result = OCIPing(conn->svchp, conn->errhp, OCI_DEFAULT);
254  if (result != OCI_SUCCESS) {
255  oracle_checkerr(conn->errhp, result);
256  ret = -1;
257  } else {
258  ret = 0;
259  }
260 #else
261  Octstr *sql;
262  List *res;
263  /* TODO Check for appropriate OCI function */
264  sql = octstr_create("SELECT 1 FROM DUAL");
265 
266  ret = oracle_select(conn, sql, NULL, &res);
267  if (ret != -1 && gwlist_len(res) > 0) {
268  List *row = gwlist_extract_first(res);
270  }
271  if (ret != -1)
272  gwlist_destroy(res, NULL);
273 
274  octstr_destroy(sql);
275 #endif
276 
277  return ret;
278 }
279 
280 
281 static void oracle_conf_destroy(DBConf *theconf)
282 {
283  OracleConf *conf = theconf->oracle;
284 
285  octstr_destroy(conf->username);
286  octstr_destroy(conf->password);
287  octstr_destroy(conf->tnsname);
288 
289  gw_free(conf);
290  gw_free(theconf);
291 }
292 
293 
294 static int oracle_select(void *theconn, const Octstr *sql, List *binds, List **res)
295 {
296  List *row;
297  OCIStmt *stmt;
298  OCIParam *dparam;
299  sword status;
300  ub4 columns;
301  ub4 i;
302  struct data_s {
303  text *data;
304  ub2 size;
305  sb2 ind;
306  ub2 type;
307  };
308  struct data_s *data;
309  struct ora_conn *conn = (struct ora_conn*) theconn;
310  int binds_len = (binds ? gwlist_len(binds) : 0);
311 
312  *res = NULL;
313 
314  /* allocate statement handle */
315  status = OCIHandleAlloc(conn->envp, (dvoid**)&stmt, OCI_HTYPE_STMT, 0,0);
316  if (OCI_SUCCESS != status) {
317  oracle_checkerr(conn->errhp, status);
318  return -1;
319  }
320  /* prepare statement */
321  status = OCIStmtPrepare(stmt, conn->errhp, (unsigned char*)octstr_get_cstr(sql),
322  octstr_len(sql), OCI_NTV_SYNTAX, OCI_DEFAULT);
323  if (OCI_SUCCESS != status) {
324  oracle_checkerr(conn->errhp, status);
325  OCIHandleFree(stmt, OCI_HTYPE_STMT);
326  return -1;
327  }
328 
329  /* bind variables */
330  for (i = 0; i < binds_len; i++) {
331  OCIBind *bndhp = NULL;
332  Octstr *bind = gwlist_get(binds, i);
333  status = OCIBindByPos(stmt, &bndhp,
334  conn->errhp, (i+1), (dvoid *) octstr_get_cstr(bind),
335  (sword) octstr_len(bind)+1, SQLT_STR, (dvoid *) 0, (ub2 *)0,
336  (ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT);
337  if (OCI_SUCCESS != status) {
338  oracle_checkerr(conn->errhp, status);
339  OCIHandleFree(stmt, OCI_HTYPE_STMT);
340  return -1;
341  }
342  }
343  /* execute our statement */
344  status = OCIStmtExecute(conn->svchp, stmt, conn->errhp, 0, 0, NULL, NULL,
345  OCI_DEFAULT);
346  if (OCI_SUCCESS != status && OCI_NO_DATA != status) {
347  oracle_checkerr(conn->errhp, status);
348  OCIHandleFree(stmt, OCI_HTYPE_STMT);
349  return -1;
350  }
351  /* receive column count */
352  status = OCIAttrGet(stmt, OCI_HTYPE_STMT, &columns, 0, OCI_ATTR_PARAM_COUNT,
353  conn->errhp);
354  if (status != OCI_SUCCESS) {
355  oracle_checkerr(conn->errhp, status);
356  OCIHandleFree(stmt, OCI_HTYPE_STMT);
357  return -1;
358  }
359 
360  debug("dbpool.oracle",0,"SQL has %d columns", columns);
361 
362  /* allocate array of pointers */
363  debug("dbpool.oracle",0,"alloc size=%ld",sizeof(text*)*columns);
364  data = gw_malloc(sizeof(struct data_s)*columns);
365 
366  debug("dbpool.oracle",0,"retrieve data_size");
367  /* retrieve data size for every column and allocate it */
368  for (i=0 ; i < columns; i++) {
369  OCIDefine *defh;
370 
371  status = OCIParamGet(stmt, OCI_HTYPE_STMT, conn->errhp,
372  (dvoid**) &dparam, i+1);
373  if (status != OCI_SUCCESS) {
374  oracle_checkerr(conn->errhp, status);
375  columns = i;
376  for (i = 0; i < columns; i++)
377  gw_free(data[i].data);
378  gw_free(data);
379  OCIHandleFree(stmt, OCI_HTYPE_STMT);
380  return -1;
381  }
382 
383  status = OCIAttrGet(dparam, OCI_DTYPE_PARAM, (dvoid*) &data[i].size,
384  0, OCI_ATTR_DATA_SIZE, conn->errhp);
385  if (status != OCI_SUCCESS) {
386  oracle_checkerr(conn->errhp, status);
387  columns = i;
388  for (i = 0; i < columns; i++)
389  gw_free(data[i].data);
390  gw_free(data);
391  OCIHandleFree(stmt, OCI_HTYPE_STMT);
392  return -1;
393  }
394 
395  status = OCIAttrGet(dparam, OCI_DTYPE_PARAM, (dvoid*) &data[i].type,
396  0, OCI_ATTR_DATA_TYPE, conn->errhp);
397  if (status != OCI_SUCCESS) {
398  oracle_checkerr(conn->errhp, status);
399  columns = i;
400  for (i = 0; i < columns; i++)
401  gw_free(data[i].data);
402  gw_free(data);
403  OCIHandleFree(stmt, OCI_HTYPE_STMT);
404  return -1;
405  }
406 
407  /* convert all data types to C-Strings except DATE */
408  if (data[i].type != SQLT_DAT) {
409  data[i].size++; /* terminating zero */
410  data[i].type = SQLT_STR;
411  }
412 
413  debug("dbpool.oracle",0,"alloc size=%d", data[i].size);
414  data[i].data = gw_malloc(data[i].size);
415 
416  /* bind allocated values to statement handle */
417  status = OCIDefineByPos(stmt, &defh, conn->errhp, i+1, data[i].data,
418  data[i].size, data[i].type, &data[i].ind,
419  0, 0, OCI_DEFAULT);
420  if (status != OCI_SUCCESS) {
421  oracle_checkerr(conn->errhp, status);
422  columns = i;
423  for (i = 0; i <= columns; i++)
424  gw_free(data[i].data);
425  gw_free(data);
426  OCIHandleFree(stmt, OCI_HTYPE_STMT);
427  return -1;
428  }
429  }
430 
431  *res = gwlist_create();
432  /* fetch data */
433  while ((status = OCIStmtFetch(stmt, conn->errhp, 1,
434  OCI_FETCH_NEXT, OCI_DEFAULT)) == OCI_SUCCESS ||
435  status == OCI_SUCCESS_WITH_INFO) {
436 
437  row = gwlist_create();
438  for (i = 0; i < columns; i++) {
439  if (data[i].data == NULL || data[i].ind == -1) {
440  gwlist_insert(row, i, octstr_create(""));
441  } else {
442  gwlist_insert(row, i, octstr_create_from_data((const char*)data[i].data, data[i].size));
443  }
444  /* debug("dbpool.oracle",0,"inserted value = '%s'",
445  octstr_get_cstr(gwlist_get(row,i))); */
446  }
447  gwlist_append(*res, row);
448  }
449 
450  /* ignore OCI_NO_DATA error */
451  if (status != OCI_NO_DATA) {
452  List *row;
453  oracle_checkerr(conn->errhp, status);
454  for (i = 0; i < columns; i++)
455  gw_free(data[i].data);
456  gw_free(data);
457  while ((row = gwlist_extract_first(*res)) != NULL)
459  gwlist_destroy(*res, NULL);
460  *res = NULL;
461  OCIHandleFree(stmt, OCI_HTYPE_STMT);
462  return -1;
463  }
464 
465  for (i = 0; i < columns; i++)
466  gw_free(data[i].data);
467 
468  gw_free(data);
469  OCIHandleFree(stmt, OCI_HTYPE_STMT);
470 
471  return 0;
472 }
473 
474 
475 static int oracle_update(void *theconn, const Octstr *sql, List *binds)
476 {
477  OCIStmt *stmt;
478  sword status;
479  ub4 rows = 0, i;
480  struct ora_conn *conn = (struct ora_conn*) theconn;
481  int binds_len = (binds ? gwlist_len(binds) : 0);
482 
483  /* allocate statement handle */
484  status = OCIHandleAlloc(conn->envp, (dvoid**)&stmt, OCI_HTYPE_STMT, 0,0);
485  if (OCI_SUCCESS != status) {
486  oracle_checkerr(conn->errhp, status);
487  return -1;
488  }
489  debug("dbpool.oracle",0,"OCIStmt allocated");
490  /* prepare statement */
491  status = OCIStmtPrepare(stmt, conn->errhp, (unsigned char*)octstr_get_cstr(sql),
492  octstr_len(sql), OCI_NTV_SYNTAX, OCI_DEFAULT);
493  if (OCI_SUCCESS != status) {
494  oracle_checkerr(conn->errhp, status);
495  OCIHandleFree(stmt, OCI_HTYPE_STMT);
496  return -1;
497  }
498  debug("dbpool.oracle",0,"OCIStmtPrepare done");
499 
500  /* bind variables */
501  for (i = 0; i < binds_len; i++) {
502  Octstr *bind = gwlist_get(binds, i);
503  OCIBind *bndhp = NULL;
504  status = OCIBindByPos(stmt, &bndhp,
505  conn->errhp, (i+1), (dvoid *) octstr_get_cstr(bind),
506  (sword) octstr_len(bind)+1, SQLT_STR, (dvoid *) 0, (ub2 *)0,
507  (ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT);
508  if (OCI_SUCCESS != status) {
509  oracle_checkerr(conn->errhp, status);
510  OCIHandleFree(stmt, OCI_HTYPE_STMT);
511  return -1;
512  }
513  }
514 
515  /* execute our statement */
516  status = OCIStmtExecute(conn->svchp, stmt, conn->errhp, 1, 0, NULL, NULL,
517  /*OCI_DEFAULT*/ OCI_COMMIT_ON_SUCCESS);
518  if (OCI_SUCCESS != status && OCI_NO_DATA != status) {
519  oracle_checkerr(conn->errhp, status);
520  OCIHandleFree(stmt, OCI_HTYPE_STMT);
521  return -1;
522  }
523  debug("dbpool.oracle",0,"OCIStmtExecute done");
524  /* retrieve #rows processed so far */
525  status = OCIAttrGet(stmt, OCI_HTYPE_STMT, &rows, 0, OCI_ATTR_ROW_COUNT,
526  conn->errhp);
527  if (status != OCI_SUCCESS) {
528  oracle_checkerr(conn->errhp, status);
529  /* we doesn't return error here, because sql is executed and commited already */
530  }
531  debug("dbpool.oracle",0,"rows processed = %d", rows);
532 
533  OCIHandleFree(stmt, OCI_HTYPE_STMT);
534 
535  return (int) rows;
536 }
537 
538 static struct db_ops oracle_ops = {
539  .open = oracle_open_conn,
540  .close = oracle_close_conn,
541  .check = oracle_check_conn,
542  .conf_destroy = oracle_conf_destroy,
543  .select = oracle_select,
544  .update = oracle_update
545 };
546 
547 #endif
void error(int err, const char *fmt,...)
Definition: log.c:648
void info(int err, const char *fmt,...)
Definition: log.c:672
Octstr * password
Definition: dbpool.h:119
int size
Definition: wsasm.c:84
gw_assert(wtls_machine->packet_to_send !=NULL)
void gwlist_append(List *list, void *item)
Definition: list.c:179
long gwlist_len(List *list)
Definition: list.c:166
void * gwlist_get(List *list, long pos)
Definition: list.c:292
int type
Definition: smsc_cimd2.c:215
static Cfg * cfg
Definition: opensmppbox.c:95
#define octstr_get_cstr(ostr)
Definition: octstr.h:233
Octstr * username
Definition: dbpool.h:118
void * gwlist_extract_first(List *list)
Definition: list.c:305
char * text
Definition: smsc_cimd2.c:921
void octstr_destroy(Octstr *ostr)
Definition: octstr.c:324
#define octstr_create(cstr)
Definition: octstr.h:125
void octstr_destroy_item(void *os)
Definition: octstr.c:336
void gwlist_insert(List *list, long pos, void *item)
Definition: list.c:214
Definition: dbpool.h:164
long octstr_len(const Octstr *ostr)
Definition: octstr.c:342
Octstr * tnsname
Definition: dbpool.h:120
Definition: octstr.c:118
void debug(const char *place, int err, const char *fmt,...)
Definition: log.c:726
#define gwlist_create()
Definition: list.h:136
#define octstr_create_from_data(data, len)
Definition: octstr.h:134
Definition: list.c:102
void *(* open)(const DBConf *conf)
Definition: dbpool_p.h:73
OracleConf * oracle
Definition: dbpool.h:168
void gwlist_destroy(List *list, gwlist_item_destructor_t *destructor)
Definition: list.c:145
See file LICENSE for details about the license agreement for using, modifying, copying or deriving work from this software.