1 /* $Cambridge: exim/src/src/lookups/oracle.c,v 1.6 2009/11/16 19:50:38 nm4 Exp $ */
3 /*************************************************
4 * Exim - an Internet mail transport agent *
5 *************************************************/
7 /* Copyright (c) University of Cambridge 1995 - 2009 */
8 /* See the file NOTICE for conditions of use and distribution. */
10 /* Interface to an Oracle database. This code was originally supplied by
11 Paul Kelly, but I have hacked it around for various reasons, and tried to add
12 some comments from my position of Oracle ignorance. */
18 /* The Oracle system headers */
24 #define PARSE_NO_DEFER 0 /* parse straight away */
25 #define PARSE_V7_LNG 2
26 #define MAX_ITEM_BUFFER_SIZE 1024 /* largest size of a cell of data */
27 #define MAX_SELECT_LIST_SIZE 32 /* maximum number of columns (not rows!) */
29 /* Paul's comment on this was "change this to 512 for 64bit cpu", but I don't
30 understand why. The Oracle manual just asks for 256 bytes.
32 That was years ago. Jin Choi suggested (March 2007) that this change should
33 be made in the source, as at worst it wastes 256 bytes, and it saves people
34 having to discover about this for themselves as more and more systems are
35 64-bit. So I have changed 256 to 512. */
39 /* Internal/external datatype codes */
48 /* ORACLE error codes used in demonstration programs */
50 #define VAR_NOT_IN_LIST 1007
51 #define NO_DATA_FOUND 1403
53 typedef struct Ora_Describe {
56 sb1 buf[MAX_ITEM_BUFFER_SIZE];
64 typedef struct Ora_Define {
65 ub1 buf[MAX_ITEM_BUFFER_SIZE];
69 ub2 col_retlen, col_retcode;
72 /* Structure and anchor for caching connections. */
74 typedef struct oracle_connection {
75 struct oracle_connection *next;
77 struct cda_def *handle;
81 static oracle_connection *oracle_connections = NULL;
87 /*************************************************
88 * Set up message after error *
89 *************************************************/
91 /* Sets up a message from a local string plus whatever Oracle gives.
94 oracle_handle the handle of the connection
96 msg local text message
100 oracle_error(struct cda_def *oracle_handle, int rc, uschar *msg)
103 oerhms(oracle_handle, rc, tmp, sizeof(tmp));
104 return string_sprintf("ORACLE %s: %s", msg, tmp);
109 /*************************************************
110 * Describe and define the select list items *
111 *************************************************/
113 /* Figures out sizes, types, and numbers.
118 desc descriptions put here
120 Returns: number of fields
124 describe_define(Cda_Def *cda, Ora_Define *def, Ora_Describe *desc)
126 sword col, deflen, deftyp;
128 static sword numwidth = 8;
130 /* Describe the select-list items. */
132 for (col = 0; col < MAX_SELECT_LIST_SIZE; col++)
134 desc[col].buflen = MAX_ITEM_BUFFER_SIZE;
136 if (odescr(cda, col + 1, &desc[col].dbsize,
137 &desc[col].dbtype, &desc[col].buf[0],
138 &desc[col].buflen, &desc[col].dsize,
139 &desc[col].precision, &desc[col].scale,
140 &desc[col].nullok) != 0)
142 /* Break on end of select list. */
143 if (cda->rc == VAR_NOT_IN_LIST) break; else return -1;
146 /* Adjust sizes and types for display, handling NUMBER with scale as float. */
148 if (desc[col].dbtype == NUMBER_TYPE)
150 desc[col].dbsize = numwidth;
151 if (desc[col].scale != 0)
153 defptr = (ub1 *)&def[col].flt_buf;
154 deflen = (sword) sizeof(float);
156 desc[col].dbtype = FLOAT_TYPE;
160 defptr = (ub1 *)&def[col].int_buf;
161 deflen = (sword) sizeof(sword);
163 desc[col].dbtype = INT_TYPE;
168 if (desc[col].dbtype == DATE_TYPE)
169 desc[col].dbsize = 9;
170 if (desc[col].dbtype == ROWID_TYPE)
171 desc[col].dbsize = 18;
172 defptr = def[col].buf;
173 deflen = desc[col].dbsize > MAX_ITEM_BUFFER_SIZE ?
174 MAX_ITEM_BUFFER_SIZE : desc[col].dbsize + 1;
175 deftyp = STRING_TYPE;
176 desc[col].dbtype = STRING_TYPE;
179 /* Define an output variable */
181 if (odefin(cda, col + 1,
182 defptr, deflen, deftyp,
183 -1, &def[col].indp, (text *) 0, -1, -1,
184 &def[col].col_retlen,
185 &def[col].col_retcode) != 0)
187 } /* Loop for each column */
194 /*************************************************
196 *************************************************/
198 /* See local README for interface description. */
201 oracle_open(uschar *filename, uschar **errmsg)
203 return (void *)(1); /* Just return something non-null */
208 /*************************************************
210 *************************************************/
212 /* See local README for interface description. */
217 oracle_connection *cn;
218 while ((cn = oracle_connections) != NULL)
220 oracle_connections = cn->next;
221 DEBUG(D_lookup) debug_printf("close ORACLE connection: %s\n", cn->server);
228 /*************************************************
229 * Internal search function *
230 *************************************************/
232 /* This function is called from the find entry point to do the search for a
236 query the query string
237 server the server string
238 resultptr where to store the result
239 errmsg where to point an error message
240 defer_break TRUE if no more servers are to be tried after DEFER
242 The server string is of the form "host/dbname/user/password", for compatibility
243 with MySQL and pgsql, but at present, the dbname is not used. This string is in
244 a nextinlist temporary buffer, so can be overwritten.
246 Returns: OK, FAIL, or DEFER
250 perform_oracle_search(uschar *query, uschar *server, uschar **resultptr,
251 uschar **errmsg, BOOL *defer_break)
254 struct cda_def *oracle_handle = NULL;
255 Ora_Describe *desc = NULL;
256 Ora_Define *def = NULL;
263 unsigned int num_fields = 0;
264 uschar *result = NULL;
265 oracle_connection *cn = NULL;
266 uschar *server_copy = NULL;
270 /* Disaggregate the parameters from the server argument. The order is host,
271 database, user, password. We can write to the string, since it is in a
272 nextinlist temporary buffer. The copy of the string that is used for caching
273 has the password removed. This copy is also used for debugging output. */
275 for (i = 3; i > 0; i--)
277 uschar *pp = Ustrrchr(server, '/');
280 *errmsg = string_sprintf("incomplete ORACLE server data: %s", server);
286 if (i == 3) server_copy = string_copy(server); /* sans password */
288 sdata[0] = server; /* What's left at the start */
290 /* If the database is the empty string, set it NULL - the query must then
293 if (sdata[1][0] == 0) sdata[1] = NULL;
295 /* See if we have a cached connection to the server */
297 for (cn = oracle_connections; cn != NULL; cn = cn->next)
299 if (strcmp(cn->server, server_copy) == 0)
301 oracle_handle = cn->handle;
307 /* If no cached connection, we must set one up */
311 DEBUG(D_lookup) debug_printf("ORACLE new connection: host=%s database=%s "
312 "user=%s\n", sdata[0], sdata[1], sdata[2]);
314 /* Get store for a new connection, initialize it, and connect to the server */
316 oracle_handle = store_get(sizeof(struct cda_def));
317 hda = store_get(HDA_SIZE);
318 memset(hda,'\0',HDA_SIZE);
321 * Perform a default (blocking) login
323 * sdata[0] = tnsname (service name - typically host name)
324 * sdata[1] = dbname - not used at present
325 * sdata[2] = username
329 if(olog(oracle_handle, hda, sdata[2], -1, sdata[3], -1, sdata[0], -1,
330 (ub4)OCI_LM_DEF) != 0)
332 *errmsg = oracle_error(oracle_handle, oracle_handle->rc,
333 US"connection failed");
334 *defer_break = FALSE;
335 goto ORACLE_EXIT_NO_VALS;
338 /* Add the connection to the cache */
340 cn = store_get(sizeof(oracle_connection));
341 cn->server = server_copy;
342 cn->handle = oracle_handle;
343 cn->next = oracle_connections;
345 oracle_connections = cn;
348 /* Else use a previously cached connection - we can write to the server string
349 to obliterate the password because it is in a nextinlist temporary buffer. */
354 debug_printf("ORACLE using cached connection for %s\n", server_copy);
357 /* We have a connection. Open a cursor and run the query */
359 cda = store_get(sizeof(Cda_Def));
361 if (oopen(cda, oracle_handle, (text *)0, -1, -1, (text *)0, -1) != 0)
363 *errmsg = oracle_error(oracle_handle, cda->rc, "failed to open cursor");
364 *defer_break = FALSE;
365 goto ORACLE_EXIT_NO_VALS;
368 if (oparse(cda, (text *)query, (sb4) -1,
369 (sword)PARSE_NO_DEFER, (ub4)PARSE_V7_LNG) != 0)
371 *errmsg = oracle_error(oracle_handle, cda->rc, "query failed");
372 *defer_break = FALSE;
374 goto ORACLE_EXIT_NO_VALS;
377 /* Find the number of fields returned and sort out their types. If the number
378 is one, we don't add field names to the data. Otherwise we do. */
380 def = store_get(sizeof(Ora_Define)*MAX_SELECT_LIST_SIZE);
381 desc = store_get(sizeof(Ora_Describe)*MAX_SELECT_LIST_SIZE);
383 if ((num_fields = describe_define(cda,def,desc)) == -1)
385 *errmsg = oracle_error(oracle_handle, cda->rc, "describe_define failed");
386 *defer_break = FALSE;
392 *errmsg = oracle_error(oracle_handle, cda->rc, "oexec failed");
393 *defer_break = FALSE;
397 /* Get the fields and construct the result string. If there is more than one
398 row, we insert '\n' between them. */
400 while (cda->rc != NO_DATA_FOUND) /* Loop for each row */
403 if(cda->rc == NO_DATA_FOUND) break;
405 if (result != NULL) result = string_cat(result, &ssize, &offset, "\n", 1);
407 /* Single field - just add on the data */
410 result = string_cat(result, &ssize, &offset, def[0].buf, def[0].col_retlen);
412 /* Multiple fields - precede by file name, removing {lead,trail}ing WS */
414 else for (i = 0; i < num_fields; i++)
417 uschar *s = US desc[i].buf;
419 while (*s != 0 && isspace(*s)) s++;
421 while (slen > 0 && isspace(s[slen-1])) slen--;
422 result = string_cat(result, &ssize, &offset, s, slen);
423 result = string_cat(result, &ssize, &offset, US"=", 1);
425 /* int and float type wont ever need escaping. Otherwise, quote the value
426 if it contains spaces or is empty. */
428 if (desc[i].dbtype != INT_TYPE && desc[i].dbtype != FLOAT_TYPE &&
429 (def[i].buf[0] == 0 || strchr(def[i].buf, ' ') != NULL))
432 result = string_cat(result, &ssize, &offset, "\"", 1);
433 for (j = 0; j < def[i].col_retlen; j++)
435 if (def[i].buf[j] == '\"' || def[i].buf[j] == '\\')
436 result = string_cat(result, &ssize, &offset, "\\", 1);
437 result = string_cat(result, &ssize, &offset, def[i].buf+j, 1);
439 result = string_cat(result, &ssize, &offset, "\"", 1);
442 else switch(desc[i].dbtype)
445 sprintf(CS tmp, "%d", def[i].int_buf);
446 result = string_cat(result, &ssize, &offset, tmp, Ustrlen(tmp));
450 sprintf(CS tmp, "%f", def[i].flt_buf);
451 result = string_cat(result, &ssize, &offset, tmp, Ustrlen(tmp));
455 result = string_cat(result, &ssize, &offset, def[i].buf,
460 *errmsg = string_sprintf("ORACLE: unknown field type %d", desc[i].dbtype);
461 *defer_break = FALSE;
466 result = string_cat(result, &ssize, &offset, " ", 1);
470 /* If result is NULL then no data has been found and so we return FAIL.
471 Otherwise, we must terminate the string which has been built; string_cat()
472 always leaves enough room for a terminating zero. */
477 *errmsg = "ORACLE: no data found";
482 store_reset(result + offset + 1);
485 /* Get here by goto from various error checks. */
489 /* Close the cursor; don't close the connection, as it is cached. */
495 /* Non-NULL result indicates a sucessful result */
504 DEBUG(D_lookup) debug_printf("%s\n", *errmsg);
505 return yield; /* FAIL or DEFER */
512 /*************************************************
514 *************************************************/
516 /* See local README for interface description. The handle and filename
517 arguments are not used. Loop through a list of servers while the query is
518 deferred with a retryable error. */
521 oracle_find(void *handle, uschar *filename, uschar *query, int length,
522 uschar **result, uschar **errmsg, BOOL *do_cache)
526 uschar *list = oracle_servers;
529 do_cache = do_cache; /* Placate picky compilers */
531 DEBUG(D_lookup) debug_printf("ORACLE query: %s\n", query);
533 while ((server = string_nextinlist(&list, &sep, buffer, sizeof(buffer))) != NULL)
536 int rc = perform_oracle_search(query, server, result, errmsg, &defer_break);
537 if (rc != DEFER || defer_break) return rc;
540 if (oracle_servers == NULL)
541 *errmsg = "no ORACLE servers defined (oracle_servers option)";
548 /*************************************************
549 * Quote entry point *
550 *************************************************/
552 /* The only characters that need to be quoted (with backslash) are newline,
553 tab, carriage return, backspace, backslash itself, and the quote characters.
554 Percent and underscore are not escaped. They are only special in contexts where
555 they can be wild cards, and this isn't usually the case for data inserted from
556 messages, since that isn't likely to be treated as a pattern of any kind.
559 s the string to be quoted
560 opt additional option text or NULL if none
562 Returns: the processed string or NULL for a bad option
566 oracle_quote(uschar *s, uschar *opt)
573 if (opt != NULL) return NULL; /* No options are recognized */
575 while ((c = *t++) != 0)
576 if (strchr("\n\t\r\b\'\"\\", c) != NULL) count++;
578 if (count == 0) return s;
579 t = quoted = store_get((int)strlen(s) + count + 1);
581 while ((c = *s++) != 0)
583 if (strchr("\n\t\r\b\'\"\\", c) != NULL)
588 case '\n': *t++ = 'n';
590 case '\t': *t++ = 't';
592 case '\r': *t++ = 'r';
594 case '\b': *t++ = 'b';
607 static lookup_info _lookup_info = {
608 US"oracle", /* lookup name */
609 lookup_querystyle, /* query-style lookup */
610 oracle_open, /* open function */
611 NULL, /* check function */
612 oracle_find, /* find function */
613 NULL, /* no close function */
614 oracle_tidy, /* tidy function */
615 oracle_quote /* quoting function */
619 #define oracle_lookup_module_info _lookup_module_info
622 static lookup_info *_lookup_list[] = { &_lookup_info };
623 lookup_module_info oracle_lookup_module_info = { LOOKUP_MODULE_INFO_MAGIC, _lookup_list, 1 };
625 /* End of lookups/oracle.c */