2
* libdbi - database independent abstraction layer for C.
3
* Copyright (C) 2001-2004, David Parker and Mark Tobenkin.
4
* http://libdbi.sourceforge.net
6
* This library is free software; you can redistribute it and/or
7
* modify it under the terms of the GNU Lesser General Public
8
* License as published by the Free Software Foundation; either
9
* version 2.1 of the License, or (at your option) any later version.
11
* This library 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 GNU
14
* Lesser General Public License for more details.
16
* You should have received a copy of the GNU Lesser General Public
17
* License along with this library; if not, write to the Free Software
18
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
20
* dbd_oracle.c: Oracle database support
21
* Copyright (C) 2003-2004, Christian M. Stamgren <christian@stamgren.com>
22
* http://libdbi-drivers.sourceforge.net
30
#define _GNU_SOURCE /* we need asprintf */
38
#include <dbi/dbi-dev.h>
43
#include "dbd_oracle.h"
44
#include "oracle_charsets.h"
46
static const dbi_info_t driver_info = {
48
"Oracle database support (using Oracle Call Interface)",
49
"Christian M. Stamgren <christian@stamgren.com>",
50
"http://libdbi-drivers.sourceforge.net",
51
"dbd_Oracle v" VERSION,
55
static const char *custom_functions[] = {NULL};
56
static const char *reserved_words[] = ORACLE_RESERVED_WORDS;
59
void _translate_oracle_type(int fieldtype, ub1 scale, unsigned short *type, unsigned int *attribs);
60
void _get_field_info(dbi_result_t *result);
61
void _get_row_data(dbi_result_t *result, dbi_row_t *row, unsigned long long rowidx);
62
unsigned long long _oracle_query_to_longlong(dbi_conn_t *conn, const char *sql_cmd);
63
void _checkerr(OCIError * errhp, sword status);
65
void dbd_register_driver(const dbi_info_t **_driver_info, const char ***_custom_functions,
66
const char ***_reserved_words)
68
*_driver_info = &driver_info;
69
*_custom_functions = custom_functions;
70
*_reserved_words = reserved_words;
73
int dbd_initialize(dbi_driver_t *driver)
75
return OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0,
76
(dvoid * (*)(dvoid *, size_t)) 0,
77
(dvoid * (*)(dvoid *, dvoid *, size_t))0,
78
(void (*)(dvoid *, dvoid *)) 0 );
81
int dbd_connect(dbi_conn_t *conn)
83
Oraconn *Oconn = malloc( sizeof( Oraconn ));
85
const char *username = dbi_conn_get_option(conn, "username");
86
const char *password = dbi_conn_get_option(conn, "password");
87
const char *sid = dbi_conn_get_option(conn, "dbname");
89
if(! sid ) sid = getenv("ORACLE_SID");
91
if(OCIEnvCreate ((OCIEnv **) &(Oconn->env), OCI_DEFAULT, (dvoid *)0, 0, 0, 0, (size_t)0, (dvoid **)0)) {
92
_dbd_internal_error_handler(conn, "Connect::Unable to initialize enviroment", 0);
95
if( (OCIHandleAlloc( (dvoid *) Oconn->env, (dvoid **) &(Oconn->err), OCI_HTYPE_ERROR,
96
(size_t) 0, (dvoid **) 0)) ||
97
(OCIHandleAlloc( (dvoid *) Oconn->env, (dvoid **) &(Oconn->svc), OCI_HTYPE_SVCCTX,
98
(size_t) 0, (dvoid **) 0))) {
99
_dbd_internal_error_handler(conn, "Connect::Unable to allocate handlers.", 0);
102
if( OCILogon(Oconn->env, Oconn->err, &(Oconn->svc), username,
103
strlen(username), password, strlen(password), sid, strlen(sid))) {
104
_dbd_internal_error_handler(conn, "Connect::Unable to login to the database.", 0);
108
conn->connection = (void *)Oconn;
109
if (sid) conn->current_db = strdup(sid);
114
int dbd_disconnect(dbi_conn_t *conn)
116
Oraconn *Oconn = conn->connection;
119
OCILogoff(Oconn->svc,
121
OCIHandleFree((dvoid *) Oconn->svc, OCI_HTYPE_SVCCTX);
122
OCIHandleFree((dvoid *) Oconn->err, OCI_HTYPE_ERROR);
125
if(conn->current_db) free(conn->current_db);
126
free(conn->connection);
127
conn->connection = NULL;
132
int dbd_fetch_row(dbi_result_t *result, unsigned long long rownum)
134
dbi_row_t *row = NULL;
136
if (result->result_state == NOTHING_RETURNED) return -1;
138
if (result->result_state == ROWS_RETURNED) {
139
row = _dbd_row_allocate(result->numfields);
140
_get_row_data(result, row, rownum);
141
_dbd_row_finalize(result, row, rownum);
148
int dbd_free_query(dbi_result_t *result)
150
if (result->result_handle) OCIHandleFree((dvoid *)result->result_handle, OCI_HTYPE_STMT);
151
result->result_handle = NULL;
156
int dbd_goto_row(dbi_result_t *result, unsigned long long row)
162
int dbd_get_socket(dbi_conn_t *conn)
164
return 0; /* Oracle can't do that.. */
168
* Use this function with care... It might bite you.
169
* There can be N-type columns in queries that have a diffrent Charset,
170
* a session might be altered to use another charset as output or
171
* there might be values converted using the Oracle convert() function in the query.
173
const char *dbd_get_encoding(dbi_conn_t *conn){
174
OraText buf[OCI_NLS_MAXBUFSZ];
178
Oraconn *Oconn = conn->connection;
180
if( (ret = OCINlsGetInfo(Oconn->env, Oconn->err, buf,
181
OCI_NLS_MAXBUFSZ, OCI_NLS_CHARACTER_SET)) != OCI_SUCCESS) {
182
_checkerr(Oconn->err, ret);
184
if (!buf) return NULL;
186
while (*oracle_encoding_hash[i]) {
187
if (!strcmp(oracle_encoding_hash[i], buf)) {
188
return oracle_encoding_hash[i+1];
196
dbi_result_t *dbd_list_dbs(dbi_conn_t *conn, const char *pattern)
198
return NULL; /* Oracle can't do that */
202
dbi_result_t *dbd_list_tables(dbi_conn_t *conn, const char *db, const char *pattern)
208
* We just ignore the db param,
209
* Oracle can't read from diffrent databases at runtime.
211
if (pattern == NULL) {
212
asprintf(&sql_cmd, "SELECT table_name FROM user_tables");
213
res = dbd_query(conn, sql_cmd);
218
asprintf(&sql_cmd, "SELECT table_name FROM user_tables WHERE table_name LIKE '%s'",pattern);
219
res = dbd_query(conn, sql_cmd);
226
int dbd_quote_string(dbi_driver_t *driver, const char *orig, char *dest)
231
const char *escaped = "\'\"\\";
232
len = _dbd_quote_chars(dest, orig, escaped);
240
dbi_result_t *dbd_query_null(dbi_conn_t *conn, const char unsigned *statement, unsigned long st_length)
246
dbi_result_t *result;
247
ub4 numrows = 0, affectedrows = 0;
248
Oraconn *Oconn = conn->connection;
253
OCIHandleAlloc( (dvoid *) Oconn->env, (dvoid **) &stmt,
254
OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);
256
if( OCIStmtPrepare(stmt, Oconn->err, (char *) statement,
257
(ub4) st_length, (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)) {
261
OCIAttrGet(stmt, OCI_HTYPE_STMT, (dvoid *) &stmttype,
262
(ub4 *) 0, (ub4) OCI_ATTR_STMT_TYPE, Oconn->err);
264
OCIStmtExecute(Oconn->svc, stmt, Oconn->err,
265
(ub4) (stmttype == OCI_STMT_SELECT ? 0 : 1),
266
(ub4) 0, (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL,
267
OCI_STMT_SCROLLABLE_READONLY);
270
if( stmttype == OCI_STMT_SELECT) {
272
OCIAttrGet (stmt, OCI_HTYPE_STMT, (dvoid *) &numfields,
273
(ub4 *) 0, (ub4) OCI_ATTR_PARAM_COUNT, Oconn->err);
277
* To find out how many rows there is in a result set we need to call
278
* OCIStmtFetch2() with OCI_FETCH_LAST and then use OCIAttrGet()
279
* with OCI_ATTR_CURRENT_POSITION, This is really not that great
280
* because it might be very very very slow..... But It's the only way I know.
281
* It would be really great if libdbi didn't have to know how large a result set is
282
* at this early point.
286
* We need to have atleast one define before fetching. Duh!!!
288
OCIDefineByPos(stmt, &defnp, Oconn->err, 1, (dvoid *) ¬used,
289
(sword) sizeof(sword), SQLT_CHR, (dvoid *) 0, (ub2 *)0,
290
(ub2 *)0, OCI_DEFAULT);
293
status = OCIStmtFetch2(stmt, Oconn->err,
294
(ub4)1, OCI_FETCH_LAST, 0, OCI_DEFAULT);
296
status = OCIAttrGet (stmt, OCI_HTYPE_STMT, (dvoid *) &numrows,
297
(ub4 *) 0, (ub4) OCI_ATTR_CURRENT_POSITION, Oconn->err);
298
_checkerr(Oconn->err, status);
300
/* cache should be about 20% of all rows. */
301
if(dbi_conn_get_option_numeric(conn, "oracle_prefetch_rows")) {
302
cache_rows = (ub4)numrows/5;
303
OCIAttrSet(stmt, OCI_HTYPE_STMT,
304
&cache_rows, sizeof(cache_rows), OCI_ATTR_PREFETCH_ROWS,
308
/* howto handle affectedrows? */
312
result = _dbd_result_create(conn, (void *)stmt, numrows , affectedrows);
313
_dbd_result_set_numfields(result, numfields);
314
_get_field_info(result);
319
dbi_result_t *dbd_query(dbi_conn_t *conn, const char *statement)
321
return dbd_query_null(conn, statement, strlen(statement));
325
char *dbd_select_db(dbi_conn_t *conn, const char *db)
327
return NULL; /* Oracle can't do that .... */
330
int dbd_geterror(dbi_conn_t *conn, int *errno, char **errstr)
334
Oraconn *Oconn = conn->connection;
337
if (!conn->connection) {
338
*errstr = strdup("Unable to connect to database.");
341
OCIErrorGet((dvoid *)Oconn->err, (ub4) 1, (text *) NULL, &errcode, errbuf,
342
(ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
343
*errstr = strdup(errbuf);
350
unsigned long long dbd_get_seq_last(dbi_conn_t *conn, const char *sequence)
352
unsigned long long retval = 0;
353
char *sql_cmd = NULL;
355
asprintf(&sql_cmd, "SELECT %s.currval FROM dual", sequence);
357
retval = _oracle_query_to_longlong(conn, sql_cmd);
358
if(sql_cmd) free(sql_cmd);
363
unsigned long long dbd_get_seq_next(dbi_conn_t *conn, const char *sequence)
365
unsigned long long retval = 0;
366
char *sql_cmd = NULL;
368
asprintf(&sql_cmd, "SELECT %s.nextval FROM dual", sequence);
370
retval = _oracle_query_to_longlong(conn, sql_cmd);
371
if(sql_cmd) free(sql_cmd);
377
int dbd_ping(dbi_conn_t *conn)
379
unsigned long long retval = 0;
380
char *sql_cmd = NULL;
381
Oraconn *Oconn = conn->connection;
383
asprintf(&sql_cmd, "SELECT 1 from dual");
385
retval = _oracle_query_to_longlong(conn, sql_cmd);
386
if(sql_cmd) free(sql_cmd);
388
if(!retval) { /* We need to reconnect! */
389
dbd_disconnect(conn);
390
retval = dbd_connect(conn);
392
return (retval ? 0 : retval);
398
/* CORE ORACLE DATA FETCHING STUFF */
399
void _translate_oracle_type(int fieldtype, ub1 scale, unsigned short *type, unsigned int *attribs)
401
unsigned int _type = 0;
402
unsigned int _attribs = 0;
408
_type = DBI_TYPE_INTEGER;
409
_attribs |= DBI_INTEGER_SIZE8;
412
_type = DBI_TYPE_DECIMAL;
413
_attribs |= DBI_DECIMAL_SIZE8;
417
* We use size8 becouse we don't know if it
421
_type = DBI_TYPE_DECIMAL;
422
_attribs |= DBI_DECIMAL_SIZE8;
424
_type = DBI_TYPE_INTEGER;
425
_attribs |= DBI_INTEGER_SIZE8;
431
_type = DBI_TYPE_BINARY;
443
_type = DBI_TYPE_STRING;
451
void _get_field_info(dbi_result_t *result)
453
unsigned int idx = 0;
454
unsigned short fieldtype;
455
unsigned int fieldattribs;
462
Oraconn *Oconn = (Oraconn *)result->conn->connection;
464
while (idx < result->numfields) {
467
OCIParamGet((dvoid *)result->result_handle, OCI_HTYPE_STMT, Oconn->err, (dvoid **)¶m,
470
OCIAttrGet((dvoid*) param, (ub4) OCI_DTYPE_PARAM,
471
(dvoid*) &otype,(ub4 *) 0, (ub4) OCI_ATTR_DATA_TYPE,
472
(OCIError *) Oconn->err );
474
OCIAttrGet((dvoid*) param, (ub4) OCI_DTYPE_PARAM,
475
(dvoid**) &col_name,(ub4 *) &col_name_len, (ub4) OCI_ATTR_NAME,
476
(OCIError *) Oconn->err );
478
if(otype == 2) { /* we got SQLT_NUM */
479
OCIAttrGet((dvoid*) param, (ub4) OCI_DTYPE_PARAM,
480
(dvoid**) &scale,(ub4 *) 0, (ub4) OCI_ATTR_SCALE,
481
(OCIError *) Oconn->err );
484
_translate_oracle_type(otype, scale, &fieldtype, &fieldattribs);
485
_dbd_result_add_field(result, idx, (char *)col_name, fieldtype, fieldattribs);
491
void _get_row_data(dbi_result_t *result, dbi_row_t *row, unsigned long long rowidx)
493
OCIStmt *stmt = result->result_handle;
494
OCIDefine *defnp = (OCIDefine *) 0;
496
Oraconn *Oconn = result->conn->connection;
497
int curfield = 0, length = 0;
498
unsigned long sizeattrib,slen;
500
char *ptr, *cols[result->numfields];
504
* Prefetch all cols as char *'s
505
* This might not be all that good ... lets revisit
506
* this when some Oracle guru starts sending in patches.
508
while(curfield < result->numfields) {
510
OCIParamGet(stmt, OCI_HTYPE_STMT, Oconn->err, (dvoid **)¶m,
513
OCIAttrGet((dvoid*) param, (ub4) OCI_DTYPE_PARAM,
514
(dvoid*) &length,(ub4 *) 0, (ub4) OCI_ATTR_DATA_SIZE,
515
(OCIError *) Oconn->err );
516
cols[curfield] = (char *)malloc(length+1);
518
OCIDefineByPos(stmt, &defnp, Oconn->err, curfield+1, cols[curfield],
519
(sword) length+1, SQLT_STR, (dvoid *) 0, (ub2 *)0,
520
(ub2 *)0, OCI_DEFAULT);
523
switch (result->field_types[curfield]) {
524
case DBI_TYPE_BINARY:
525
case DBI_TYPE_STRING:
526
row->field_sizes[curfield] = length;
530
row->field_sizes[curfield] = 0;
537
status = OCIStmtFetch2(stmt, Oconn->err, 1, OCI_FETCH_ABSOLUTE, rowidx+1, OCI_DEFAULT);
540
while (curfield < result->numfields) {
542
data = &row->field_values[curfield];
544
switch (result->field_types[curfield]) {
545
case DBI_TYPE_INTEGER:
546
sizeattrib = _isolate_attrib(result->field_attribs[curfield], DBI_INTEGER_SIZE1, DBI_INTEGER_SIZE8);
547
switch (sizeattrib) {
548
case DBI_INTEGER_SIZE1:
549
case DBI_INTEGER_SIZE2:
550
case DBI_INTEGER_SIZE3:
551
case DBI_INTEGER_SIZE4:
552
case DBI_INTEGER_SIZE8:
554
data->d_longlong = (long long) atoll(cols[curfield]);
559
case DBI_TYPE_DECIMAL:
560
sizeattrib = _isolate_attrib(result->field_attribs[curfield], DBI_DECIMAL_SIZE4, DBI_DECIMAL_SIZE8);
561
switch (sizeattrib) {
562
case DBI_DECIMAL_SIZE4:
563
case DBI_DECIMAL_SIZE8:
565
data->d_double = (double) strtod(cols[curfield], NULL);
566
//fprintf(stderr, "Double: %s:%f\n", cols[curfield], data->d_double);
572
case DBI_TYPE_STRING:
574
slen = row->field_sizes[curfield];
576
data->d_string = malloc(row->field_sizes[curfield]+1);
577
memcpy(data->d_string, cols[curfield],row->field_sizes[curfield]);
578
data->d_string[slen] = '\0';
579
if (dbi_conn_get_option_numeric(result->conn, "oracle_chop_blanks") == 1) {
580
ptr = data->d_string;
582
if(ptr != NULL && *ptr != '\0')
583
while(slen && ptr[slen - 1] == ' ')
586
ptr[slen] = '\0'; /* Chop blanks */
587
row->field_sizes[curfield] = slen; /* alter field length */
590
case DBI_TYPE_BINARY:
591
data->d_string = malloc(row->field_sizes[curfield]);
592
memcpy(data->d_string, cols[curfield],row->field_sizes[curfield]);
595
if (cols[curfield]) free(cols[curfield]);
600
unsigned long long _oracle_query_to_longlong(dbi_conn_t *conn, const char *query)
602
OCIStmt *stmt = NULL;
604
OCIDefine *defnp = NULL;
606
Oraconn *Oconn = conn->connection;
608
OCIHandleAlloc( (dvoid *) Oconn->env, (dvoid **) &stmt,
609
OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);
610
OCIStmtPrepare(stmt, Oconn->err, (char *) query,
611
(ub4) strlen(query), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
613
OCIDefineByPos(stmt, &defnp, Oconn->err, 1, (dvoid *) &val,
614
(sword) sizeof(sword), SQLT_INT, (dvoid *) 0, (ub2 *)0,
615
(ub2 *)0, OCI_DEFAULT);
617
OCIStmtExecute(Oconn->svc, stmt, Oconn->err,
618
(ub4) 1, (ub4) 0, (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL,
621
OCIStmtFetch(stmt, Oconn->err, (ub4) 1, (ub4) OCI_FETCH_NEXT,
624
(void) OCIHandleFree((dvoid *) stmt, OCI_HTYPE_STMT);
626
return (unsigned long long) (val ? val : 0);
630
void _checkerr(OCIError * errhp, sword status)
640
case OCI_SUCCESS_WITH_INFO:
641
(void) fprintf(stderr,"Error - OCI_SUCCESS_WITH_INFO\n");
644
(void) fprintf(stderr, "Error - OCI_NEED_DATA\n");
647
(void) fprintf(stderr,"Error - OCI_NODATA\n");
650
(void) OCIErrorGet (errhp, (ub4) 1, (text *) NULL, &errcode,
651
errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
653
(void) fprintf(stderr,"Error - %s Code: %d\n", errbuf, errcode);
655
case OCI_INVALID_HANDLE:
656
(void) fprintf(stderr,"Error - OCI_INVALID_HANDLE\n");
658
case OCI_STILL_EXECUTING:
659
(void) fprintf(stderr,"Error - OCI_STILL_EXECUTE\n");