Browse code

db_oracle: fix raw sql queries

Ovidiu Sas authored on 21/04/2017 16:34:01
Showing 1 changed files
... ...
@@ -216,7 +216,7 @@ static int db_oracle_submit_query(const db1_con_t* _h, const str* _s)
216 216
 			(unsigned)hc);
217 217
 		return -1;
218 218
 	}
219
-	
219
+
220 220
 	if (!pqd->_rs) {
221 221
 		/*
222 222
 		 * This method is at ~25% faster as set OCI_COMMIT_ON_SUCCESS
... ...
@@ -373,8 +373,7 @@ int db_oracle_raw_query(const db1_con_t* _h, const str* _s, db1_res_t** _r)
373 373
 	int len;
374 374
 	const char *p;
375 375
 
376
-	if (!_h || !_s || !_s->s) {
377
-badparam:
376
+	if (!_h || !_r || !_s || !_s->s) {
378 377
 		LM_ERR("invalid parameter value\n");
379 378
 		return -1;
380 379
 	}
... ...
@@ -387,7 +386,6 @@ badparam:
387 386
 	while (len && *p == ' ') ++p, --len;
388 387
 #define _S_DIFF(p, l, S) (l <= sizeof(S)-1 || strncasecmp(p, S, sizeof(S)-1))
389 388
 	if (!_S_DIFF(p, len, "select ")) {
390
-		if (!_r) goto badparam;
391 389
 		cb._rs = &reshp;
392 390
 	} else {
393 391
 		if (	_S_DIFF(p, len, "insert ")
... ...
@@ -398,10 +396,11 @@ badparam:
398 396
 			return -2;
399 397
 		}
400 398
 #undef _S_DIFF
401
-		if (_r) goto badparam;
402 399
 		cb._rs = NULL;
403 400
 	}
404 401
 
402
+	CON_ORA(_h)->pqdata = &cb;
403
+	CON_ORA(_h)->bindpos = 0;
405 404
 	len = db_do_raw_query(_h, _s, _r, db_oracle_submit_query, db_oracle_store_result);
406 405
 	CON_ORA(_h)->pqdata = NULL;	/* paranoid for next call */
407 406
 	return len;
... ...
@@ -488,7 +487,7 @@ int db_oracle_update(const db1_con_t* _h, const db_key_t* _k, const db_op_t* _o,
488 487
 {
489 488
 	query_data_t cb;
490 489
 	int rc;
491
-	
490
+
492 491
 	if (!_h || !CON_TABLE(_h)) {
493 492
 		LM_ERR("invalid parameter value\n");
494 493
 		return -1;
Browse code

core, lib, modules: updated include paths for header files

Daniel-Constantin Mierla authored on 07/12/2016 11:07:22
Showing 1 changed files
... ...
@@ -27,8 +27,8 @@
27 27
 #include <stdlib.h>
28 28
 #include <time.h>
29 29
 #include <oci.h>
30
-#include "../../mem/mem.h"
31
-#include "../../dprint.h"
30
+#include "../../core/mem/mem.h"
31
+#include "../../core/dprint.h"
32 32
 #include "../../lib/srdb1/db_pool.h"
33 33
 #include "../../lib/srdb1/db_ut.h"
34 34
 #include "../../lib/srdb1/db_res.h"
Browse code

core, lib, modules: restructured source code tree

- new folder src/ to hold the source code for main project applications
- main.c is in src/
- all core files are subfolder are in src/core/
- modules are in src/modules/
- libs are in src/lib/
- application Makefiles are in src/
- application binary is built in src/ (src/kamailio)

Daniel-Constantin Mierla authored on 07/12/2016 11:03:51
Showing 1 changed files
1 1
new file mode 100644
... ...
@@ -0,0 +1,518 @@
1
+/*
2
+ * $Id$
3
+ *
4
+ * Oracle module core functions
5
+ *
6
+ * Copyright (C) 2007,2008 TRUNK MOBILE
7
+ *
8
+ * This file is part of Kamailio, a free SIP server.
9
+ *
10
+ * Kamailio is free software; you can redistribute it and/or modify
11
+ * it under the terms of the GNU General Public License as published by
12
+ * the Free Software Foundation; either version 2 of the License, or
13
+ * (at your option) any later version
14
+ *
15
+ * Kamailio is distributed in the hope that it will be useful,
16
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
17
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
18
+ * GNU General Public License for more details.
19
+ *
20
+ * You should have received a copy of the GNU General Public License
21
+ * along with this program; if not, write to the Free Software
22
+ * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
23
+ */
24
+
25
+#include <stdio.h>
26
+#include <string.h>
27
+#include <stdlib.h>
28
+#include <time.h>
29
+#include <oci.h>
30
+#include "../../mem/mem.h"
31
+#include "../../dprint.h"
32
+#include "../../lib/srdb1/db_pool.h"
33
+#include "../../lib/srdb1/db_ut.h"
34
+#include "../../lib/srdb1/db_res.h"
35
+#include "../../lib/srdb1/db_query.h"
36
+#include "val.h"
37
+#include "ora_con.h"
38
+#include "res.h"
39
+#include "asynch.h"
40
+#include "dbase.h"
41
+
42
+
43
+#define	MAX_BIND_HANDLES 128
44
+
45
+char st_buf[STATIC_BUF_LEN];
46
+
47
+
48
+/*
49
+ * Make error message. Always return negative value
50
+ */
51
+int sql_buf_small(void)
52
+{
53
+	LM_ERR("static buffer too small\n");
54
+	return -11;
55
+}
56
+
57
+/*
58
+ * Decode error
59
+ */
60
+static char errbuf[512];
61
+
62
+static const char* db_oracle_errorinfo(ora_con_t* con)
63
+{
64
+	sword errcd;
65
+	if (OCIErrorGet(con->errhp, 1, NULL, &errcd,
66
+			(OraText*)errbuf, sizeof(errbuf),
67
+			OCI_HTYPE_ERROR) != OCI_SUCCESS) errbuf[0] = '\0';
68
+	else switch (errcd) {
69
+	case 28:	/* your session has been killed */
70
+	case 30:	/* session ID does not exists */
71
+	case 31:	/* session marked for kill */
72
+	case 41:	/* active time limit exceeded session terminated */
73
+	case 107:	/* failed to connect to oracle listener */
74
+	case 115:	/* connection refused; dispatcher table is full */
75
+	case 1033:	/* init/shutdown in progress */
76
+	case 1034:	/* not available (startup) */
77
+	case 1089:	/* server shutdown */
78
+	case 1090:	/* shutdown wait after command */
79
+	case 1092:	/* oracle instance terminated. Disconnection forced */
80
+	case 1573:	/* shutdown instance, no futher change allowed */
81
+	case 2049:	/* timeout: distributed transaction waiting lock */
82
+	case 3113:	/* EOF on communication channel */
83
+	case 3114:	/* not connected */
84
+	case 3135:	/* lost connection */
85
+	case 6033:	/* connect failed, partner rejected connection */
86
+	case 6034:	/* connect failed, partner exited unexpectedly */
87
+	case 6037:	/* connect failed, node unrecheable */
88
+	case 6039:	/* connect failed */
89
+	case 6042:	/* msgrcv failure (DNT) */
90
+	case 6043:	/* msgsend failure (DNT) */
91
+	case 6107:	/* network server not found */
92
+	case 6108:	/* connect to host failed */
93
+	case 6109:	/* msgrcv failure (TCP) */
94
+	case 6110:	/* msgsend failure (TCP) */
95
+	case 6114:	/* SID lookup failure */
96
+	case 6124:	/* TCP timeout */
97
+	case 6135:	/* connect rejected; server is stopping (TCP) */
98
+	case 6144:	/* SID unavaliable (TCP) */
99
+	case 6413:	/* connection not open */
100
+	case 12150:	/* tns can't send data, probably disconnect */
101
+	case 12152:	/* tns unable to send break message */
102
+	case 12153:	/* tns not connected */
103
+	case 12161:	/* tns internal error */
104
+	case 12170:	/* tns connect timeout */
105
+	case 12224:	/* tns no listener */
106
+	case 12225:	/* tns destination host unrecheable */
107
+	case 12230:	/* tns network error */
108
+	case 12525:	/* tns (internal) timeout */
109
+	case 12521:	/* tns can't resolve db name */
110
+	case 12537:	/* tns connection cloed */
111
+	case 12541:	/* tns not running */
112
+	case 12543:	/* tns destination host unrecheable */
113
+	case 12547:	/* tns lost contact */
114
+	case 12560:	/* tns protocol(transport) error */
115
+	case 12561:	/* tns unknown error */
116
+	case 12608:	/* tns send timeount */
117
+	case 12609:	/* tns receive timeount */
118
+	    LM_ERR("conneciom dropped\n");
119
+	    db_oracle_disconnect(con);
120
+	default:
121
+		break;
122
+	}
123
+
124
+	return errbuf;
125
+}
126
+
127
+const char* db_oracle_error(ora_con_t* con, sword status)
128
+{
129
+	switch (status) {
130
+		case OCI_SUCCESS:
131
+			return "internal (success)";
132
+
133
+		case OCI_SUCCESS_WITH_INFO:
134
+		case OCI_ERROR:
135
+			return db_oracle_errorinfo(con);
136
+
137
+		case OCI_NEED_DATA:
138
+			return "need data";
139
+
140
+		case OCI_NO_DATA:
141
+			return "no data";
142
+
143
+		case OCI_INVALID_HANDLE:
144
+			return "invalid handle";
145
+
146
+		case OCI_STILL_EXECUTING:	// ORA-3123
147
+			return "executing (logic)";
148
+
149
+		case OCI_CONTINUE:
150
+			return "continue (library)";
151
+
152
+		default:
153
+			snprintf(errbuf, sizeof(errbuf),
154
+				"unknown status %u", status);
155
+			return errbuf;
156
+	}
157
+}
158
+
159
+
160
+/*
161
+ * Initialize database module
162
+ * No function should be called before this
163
+ */
164
+db1_con_t* db_oracle_init(const str* _url)
165
+{
166
+	return db_do_init(_url, (void *)db_oracle_new_connection);
167
+}
168
+
169
+
170
+/*
171
+ * Shut down database module
172
+ * No function should be called after this
173
+ */
174
+void db_oracle_close(db1_con_t* _h)
175
+{
176
+	db_do_close(_h, db_oracle_free_connection);
177
+}
178
+
179
+
180
+/*
181
+ * Release a result set from memory
182
+ */
183
+int db_oracle_free_result(db1_con_t* _h, db1_res_t* _r)
184
+{
185
+	if (!_h || !_r) {
186
+		LM_ERR("invalid parameter value\n");
187
+		return -1;
188
+	}
189
+
190
+	if (db_free_result(_r) < 0)
191
+	{
192
+		LM_ERR("failed to free result structure\n");
193
+		return -1;
194
+	}
195
+	return 0;
196
+}
197
+
198
+
199
+/*
200
+ * Send an SQL query to the server
201
+ */
202
+static int db_oracle_submit_query(const db1_con_t* _h, const str* _s)
203
+{
204
+	OCIBind* bind[MAX_BIND_HANDLES];
205
+	OCIDate odt[sizeof(bind)/sizeof(bind[0])];
206
+	str tmps;
207
+	sword status;
208
+	int pass;
209
+	ora_con_t* con = CON_ORA(_h);
210
+	query_data_t* pqd = con->pqdata;
211
+	size_t hc = pqd->_n + pqd->_nw;
212
+	OCIStmt *stmthp;
213
+
214
+	if (hc >= sizeof(bind)/sizeof(bind[0])) {
215
+		LM_ERR("too many bound. Rebuild with MAX_BIND_HANDLES >= %u\n",
216
+			(unsigned)hc);
217
+		return -1;
218
+	}
219
+	
220
+	if (!pqd->_rs) {
221
+		/*
222
+		 * This method is at ~25% faster as set OCI_COMMIT_ON_SUCCESS
223
+		 * in StmtExecute
224
+		 */
225
+		tmps.len = snprintf(st_buf, sizeof(st_buf),
226
+			"begin %.*s; commit write batch nowait; end;",
227
+			_s->len, _s->s);
228
+		if ((unsigned)tmps.len >= sizeof(st_buf))
229
+			return sql_buf_small();
230
+		tmps.s = st_buf;
231
+		_s = &tmps;
232
+	}
233
+
234
+	pass = 1;
235
+	if (!con->connected) {
236
+		status = db_oracle_reconnect(con);
237
+		if (status != OCI_SUCCESS) {
238
+			LM_ERR("can't restore connection: %s\n", db_oracle_error(con, status));
239
+			return -2;
240
+		}
241
+		LM_INFO("connection restored\n");
242
+		--pass;
243
+	}
244
+repeat:
245
+	stmthp = NULL;
246
+	status = OCIHandleAlloc(con->envhp, (dvoid**)(dvoid*)&stmthp,
247
+		    OCI_HTYPE_STMT, 0, NULL);
248
+	if (status != OCI_SUCCESS)
249
+		goto ora_err;
250
+	status = OCIStmtPrepare(stmthp, con->errhp, (text*)_s->s, _s->len,
251
+		OCI_NTV_SYNTAX, OCI_DEFAULT);
252
+	if (status != OCI_SUCCESS)
253
+		goto ora_err;
254
+
255
+	if (hc) {
256
+		bmap_t bmap;
257
+		size_t pos = 1;
258
+		int i;
259
+
260
+		memset(bind, 0, hc*sizeof(bind[0]));
261
+		for (i = 0; i < pqd->_n; i++) {
262
+			if (db_oracle_val2bind(&bmap, &pqd->_v[i], &odt[pos]) < 0)
263
+				goto bind_err;
264
+			status = OCIBindByPos(stmthp, &bind[pos], con->errhp,
265
+				pos, bmap.addr, bmap.size, bmap.type,
266
+				NULL, NULL, NULL, 0, NULL, OCI_DEFAULT);
267
+			if (status != OCI_SUCCESS)
268
+				goto ora_err;
269
+			++pos;
270
+		}
271
+		for (i = 0; i < pqd->_nw; i++) {
272
+			if (db_oracle_val2bind(&bmap, &pqd->_w[i], &odt[pos]) < 0) {
273
+bind_err:
274
+				OCIHandleFree(stmthp, OCI_HTYPE_STMT);
275
+				LM_ERR("can't map values\n");
276
+				return -3;
277
+			}
278
+			status = OCIBindByPos(stmthp, &bind[pos], con->errhp,
279
+				pos, bmap.addr, bmap.size, bmap.type,
280
+				NULL, NULL, NULL, 0, NULL, OCI_DEFAULT);
281
+			if (status != OCI_SUCCESS)
282
+				goto ora_err;
283
+			++pos;
284
+		}
285
+	}
286
+
287
+	// timelimited operation
288
+	status = begin_timelimit(con, 0);
289
+	if (status != OCI_SUCCESS) goto ora_err;
290
+	do status = OCIStmtExecute(con->svchp, stmthp, con->errhp,
291
+		!pqd->_rs, 0, NULL, NULL,
292
+		pqd->_rs ? OCI_STMT_SCROLLABLE_READONLY : OCI_DEFAULT);
293
+	while (wait_timelimit(con, status));
294
+	if (done_timelimit(con, status)) goto stop_exec;
295
+	switch (status)	{
296
+	case OCI_SUCCESS_WITH_INFO:
297
+		LM_WARN("driver: %s\n", db_oracle_errorinfo(con));
298
+		//PASS THRU
299
+	case OCI_SUCCESS:
300
+		if (pqd->_rs)
301
+			*pqd->_rs = stmthp;
302
+		else
303
+			OCIHandleFree(stmthp, OCI_HTYPE_STMT);
304
+		return 0;
305
+	default:
306
+	    pass = -pass;
307
+	    break;
308
+	}
309
+
310
+ora_err:
311
+	LM_ERR("driver: %s\n", db_oracle_error(con, status));
312
+stop_exec:
313
+	if (stmthp)
314
+		OCIHandleFree(stmthp, OCI_HTYPE_STMT);
315
+	if (pass == -1 && !con->connected) {
316
+		/* Attemtp to reconnect */
317
+		if (db_oracle_reconnect(con) == OCI_SUCCESS) {
318
+			LM_NOTICE("attempt repeat after reconnect\n");
319
+			pass = 0;
320
+			goto repeat;
321
+		}
322
+		LM_ERR("connection loss\n");
323
+	}
324
+	return -4;
325
+}
326
+
327
+
328
+/*
329
+ * Query table for specified rows
330
+ * _h: structure representing database connection
331
+ * _k: key names
332
+ * _op: operators
333
+ * _v: values of the keys that must match
334
+ * _c: column names to return
335
+ * _n: number of key=values pairs to compare
336
+ * _nc: number of columns to return
337
+ * _o: order by the specified column
338
+ */
339
+int db_oracle_query(const db1_con_t* _h, const db_key_t* _k, const db_op_t* _op,
340
+		const db_val_t* _v, const db_key_t* _c, int _n, int _nc,
341
+		const db_key_t _o, db1_res_t** _r)
342
+{
343
+	query_data_t cb;
344
+	OCIStmt* reshp;
345
+	int rc;
346
+
347
+	if (!_h || !CON_TABLE(_h) || !_r) {
348
+		LM_ERR("invalid parameter value\n");
349
+		return -1;
350
+	}
351
+
352
+	cb._rs = &reshp;
353
+	cb._v  = _v;
354
+	cb._n  = _n;
355
+	cb._w  = NULL;
356
+	cb._nw = 0;
357
+	CON_ORA(_h)->pqdata = &cb;
358
+	CON_ORA(_h)->bindpos = 0;
359
+	rc = db_do_query(_h, _k, _op, _v, _c, _n, _nc, _o, _r,
360
+		db_oracle_val2str, db_oracle_submit_query, db_oracle_store_result);
361
+	CON_ORA(_h)->pqdata = NULL;	/* paranoid for next call */
362
+	return rc;
363
+}
364
+
365
+
366
+/*
367
+ * Execute a raw SQL query
368
+ */
369
+int db_oracle_raw_query(const db1_con_t* _h, const str* _s, db1_res_t** _r)
370
+{
371
+	query_data_t cb;
372
+	OCIStmt* reshp;
373
+	int len;
374
+	const char *p;
375
+
376
+	if (!_h || !_s || !_s->s) {
377
+badparam:
378
+		LM_ERR("invalid parameter value\n");
379
+		return -1;
380
+	}
381
+
382
+
383
+	memset(&cb, 0, sizeof(cb));
384
+
385
+	p = _s->s;
386
+	len = _s->len;
387
+	while (len && *p == ' ') ++p, --len;
388
+#define _S_DIFF(p, l, S) (l <= sizeof(S)-1 || strncasecmp(p, S, sizeof(S)-1))
389
+	if (!_S_DIFF(p, len, "select ")) {
390
+		if (!_r) goto badparam;
391
+		cb._rs = &reshp;
392
+	} else {
393
+		if (	_S_DIFF(p, len, "insert ")
394
+		    && 	_S_DIFF(p, len, "delete ")
395
+		    && 	_S_DIFF(p, len, "update "))
396
+		{
397
+			LM_ERR("unknown raw_query: '%.*s'\n", _s->len, _s->s);
398
+			return -2;
399
+		}
400
+#undef _S_DIFF
401
+		if (_r) goto badparam;
402
+		cb._rs = NULL;
403
+	}
404
+
405
+	len = db_do_raw_query(_h, _s, _r, db_oracle_submit_query, db_oracle_store_result);
406
+	CON_ORA(_h)->pqdata = NULL;	/* paranoid for next call */
407
+	return len;
408
+}
409
+
410
+
411
+/*
412
+ * Insert a row into specified table
413
+ * _h: structure representing database connection
414
+ * _k: key names
415
+ * _v: values of the keys
416
+ * _n: number of key=value pairs
417
+ */
418
+int db_oracle_insert(const db1_con_t* _h, const db_key_t* _k, const db_val_t* _v,
419
+		int _n)
420
+{
421
+	query_data_t cb;
422
+	int rc;
423
+
424
+	if (!_h || !CON_TABLE(_h)) {
425
+		LM_ERR("invalid parameter value\n");
426
+		return -1;
427
+	}
428
+
429
+	cb._rs = NULL;
430
+	cb._v  = _v;
431
+	cb._n  = _n;
432
+	cb._w  = NULL;
433
+	cb._nw = 0;
434
+	CON_ORA(_h)->pqdata = &cb;
435
+	CON_ORA(_h)->bindpos = 0;
436
+	rc = db_do_insert(_h, _k, _v, _n, db_oracle_val2str, db_oracle_submit_query);
437
+	CON_ORA(_h)->pqdata = NULL;	/* paranoid for next call */
438
+	return rc;
439
+}
440
+
441
+
442
+/*
443
+ * Delete a row from the specified table
444
+ * _h: structure representing database connection
445
+ * _k: key names
446
+ * _o: operators
447
+ * _v: values of the keys that must match
448
+ * _n: number of key=value pairs
449
+ */
450
+int db_oracle_delete(const db1_con_t* _h, const db_key_t* _k, const db_op_t* _o,
451
+		const db_val_t* _v, int _n)
452
+{
453
+	query_data_t cb;
454
+	int rc;
455
+
456
+	if (!_h || !CON_TABLE(_h)) {
457
+		LM_ERR("invalid parameter value\n");
458
+		return -1;
459
+	}
460
+
461
+	cb._rs = NULL;
462
+	cb._v  = _v;
463
+	cb._n  = _n;
464
+	cb._w  = NULL;
465
+	cb._nw = 0;
466
+	CON_ORA(_h)->pqdata = &cb;
467
+	CON_ORA(_h)->bindpos = 0;
468
+	rc = db_do_delete(_h, _k, _o, _v, _n, db_oracle_val2str, db_oracle_submit_query);
469
+	CON_ORA(_h)->pqdata = NULL;	/* paranoid for next call */
470
+	return rc;
471
+}
472
+
473
+
474
+/*
475
+ * Update some rows in the specified table
476
+ * _h: structure representing database connection
477
+ * _k: key names
478
+ * _o: operators
479
+ * _v: values of the keys that must match
480
+ * _uk: updated columns
481
+ * _uv: updated values of the columns
482
+ * _n: number of key=value pairs
483
+ * _un: number of columns to update
484
+ */
485
+int db_oracle_update(const db1_con_t* _h, const db_key_t* _k, const db_op_t* _o,
486
+		const db_val_t* _v, const db_key_t* _uk, const db_val_t* _uv,
487
+		int _n, int _un)
488
+{
489
+	query_data_t cb;
490
+	int rc;
491
+	
492
+	if (!_h || !CON_TABLE(_h)) {
493
+		LM_ERR("invalid parameter value\n");
494
+		return -1;
495
+	}
496
+
497
+	cb._rs = NULL;
498
+	cb._v  = _uv;
499
+	cb._n  = _un;
500
+	cb._w  = _v;
501
+	cb._nw = _n;
502
+	CON_ORA(_h)->pqdata = &cb;
503
+	CON_ORA(_h)->bindpos = 0;
504
+	rc = db_do_update(_h, _k, _o, _v, _uk, _uv, _n, _un,
505
+			db_oracle_val2str, db_oracle_submit_query);
506
+	CON_ORA(_h)->pqdata = NULL;	/* paranoid for next call */
507
+	return rc;
508
+}
509
+
510
+
511
+/*
512
+ * Store name of table that will be used by
513
+ * subsequent database functions
514
+ */
515
+int db_oracle_use_table(db1_con_t* _h, const str* _t)
516
+{
517
+	return db_use_table(_h, _t);
518
+}