1 /* Copyright (C) 2000-2015 Lavtech.com corp. All rights reserved.
2
3 This program is free software; you can redistribute it and/or modify
4 it under the terms of the GNU General Public License as published by
5 the Free Software Foundation; either version 2 of the License, or
6 (at your option) any later version.
7
8 This program is distributed in the hope that it will be useful,
9 but WITHOUT ANY WARRANTY; without even the implied warranty of
10 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
11 GNU General Public License for more details.
12
13 You should have received a copy of the GNU General Public License
14 along with this program; if not, write to the Free Software
15 Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
16 */
17
18 #include "udm_config.h"
19
20 #ifdef HAVE_SQL
21
22 /*
23 #define DEBUG_SQL
24 */
25
26 #define DEBUG_ERR_QUERY
27
28
29
30 #include <stdio.h>
31 #include <stdlib.h>
32 #include <string.h>
33 #include <sys/types.h>
34 #ifdef HAVE_UNISTD_H
35 #include <unistd.h>
36 #endif
37
38 #include "udm_common.h"
39 #include "udm_utils.h"
40 #include "udm_vars.h"
41 #include "udm_sqldbms.h"
42 #include "udm_xmalloc.h"
43
44 #if (HAVE_PGSQL)
45 #include <libpq-fe.h>
46 #endif
47
48 #ifdef WIN32
49 #include <process.h>
50 #endif
51
52 static char udm_hex_digits[]= "0123456789ABCDEF";
53
54
DecodeHexStr(const char * src,UDM_STR * dst,size_t size)55 void DecodeHexStr (const char *src, UDM_STR *dst, size_t size)
56 {
57 if (!(dst->str= (char*) UdmMalloc(size / 2 + 1)))
58 {
59 dst->length= 0;
60 return;
61 }
62 dst->length= UdmHexDecode(dst->str, src, size);
63 dst->str[dst->length]= '\0';
64 }
65
66
67 const char *
UdmSQLTypeToStr(udm_sqltype_t type)68 UdmSQLTypeToStr(udm_sqltype_t type)
69 {
70 switch (type)
71 {
72 case UDM_SQLTYPE_LONGVARBINARY : return "LONGVARBINARY";
73 case UDM_SQLTYPE_LONGVARCHAR : return "LONGVARCHAR";
74 case UDM_SQLTYPE_VARCHAR : return "VARCHAR";
75 case UDM_SQLTYPE_INT32 : return "INT";
76 case UDM_SQLTYPE_UNKNOWN : break;
77 }
78 return "UNKNOWN_TYPE";
79 }
80
81
82
83 /***************************************************************/
84
85
86 udm_rc_t
UdmSQLResFreeGeneric(UDM_SQLRES * res)87 UdmSQLResFreeGeneric(UDM_SQLRES *res)
88 {
89 size_t i;
90 size_t nitems;
91 if(res)
92 {
93 if(res->Items)
94 {
95 nitems = res->nCols * res->nRows;
96 for(i=0;i<nitems;i++)
97 {
98 if (res->Items[i].str)
99 UDM_FREE(res->Items[i].str);
100 }
101 UDM_FREE(res->Items);
102 }
103 }
104 return UDM_OK;
105 }
106
107
108
109
110 /***********************************************************************/
111
112 /*
113 * Wrappers for different databases
114 *
115 * UdmDBEscStr();
116 * UdmSQLBinEscStr();
117 * UdmSQLQuery();
118 * UdmSQLValue();
119 * UdmSQLLen();
120 * UdmSQLFree();
121 * UdmSQLClose();
122 */
123
124
125 udm_rc_t
UdmSQLDropTableIfExists(UDM_SQL * db,const char * name)126 UdmSQLDropTableIfExists(UDM_SQL *db, const char *name)
127 {
128 char qbuf[128];
129 udm_rc_t rc;
130 int have_if_exists= db->flags & UDM_SQL_HAVE_DROP_IF_EXISTS;
131 const char *if_exists= have_if_exists ? "IF EXISTS " : "";
132
133 if (db->DBType == UDM_DB_MSSQL)
134 {
135 /*
136 Another option:
137 IF OBJECT_ID(N'tempdb..#temptable', N'U') IS NOT NULL
138 DROP TABLE #temptable;
139 */
140 udm_snprintf(qbuf, sizeof(qbuf),
141 "IF EXISTS "
142 "(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES "
143 "WHERE TABLE_NAME='%s') "
144 "DROP TABLE %s", name, name);
145 return UdmSQLQuery(db, NULL, qbuf);
146 }
147 if (!have_if_exists)
148 db->flags|= UDM_SQL_IGNORE_ERROR;
149 udm_snprintf(qbuf, sizeof(qbuf), "DROP TABLE %s%s", if_exists, name);
150 rc= UdmSQLQuery(db, NULL, qbuf);
151 if (!have_if_exists)
152 db->flags^= UDM_SQL_IGNORE_ERROR;
153
154 /*
155 TODO:
156
157 SQLite3: DROP TABLE IF EXISTS t1; (DONE)
158 Sybase ? Perhaps simimalr to MSSQL
159 Oracle ?
160 Ibase ?
161 Mimer ?
162 DB2 ?
163 */
164 return rc;
165 }
166
167
168 /*
169 Bind type for long varchar columns, like urlinfo.sval
170 */
171 udm_sqltype_t
UdmSQLLongVarCharBindType(UDM_SQL * db)172 UdmSQLLongVarCharBindType(UDM_SQL *db)
173 {
174 udm_sqldbtype_t dbtype= db->DBType;
175 int bindtype=
176 db->DBDriver == UDM_DBAPI_ORACLE8 ? UDM_SQLTYPE_LONGVARCHAR :
177 (dbtype == UDM_DB_MSSQL ||
178 dbtype == UDM_DB_SYBASE ||
179 dbtype == UDM_DB_SQLITE3 ||
180 dbtype == UDM_DB_MONETDB ?
181 UDM_SQLTYPE_VARCHAR : UDM_SQLTYPE_LONGVARCHAR);
182 #ifdef HAVE_UNIXODBC
183 if (dbtype == UDM_DB_SYBASE && db->DBDriver == UDM_DBAPI_ODBC)
184 bindtype= UDM_SQLTYPE_LONGVARCHAR;
185 #endif
186 return bindtype;
187 }
188
189
190 udm_rc_t
UdmSQLTableTruncateOrDelete(UDM_SQL * db,const char * name)191 UdmSQLTableTruncateOrDelete(UDM_SQL *db, const char *name)
192 {
193 char qbuf[128];
194 if (db->flags & UDM_SQL_HAVE_TRUNCATE)
195 udm_snprintf(qbuf, sizeof(qbuf), "TRUNCATE TABLE %s", name);
196 else
197 udm_snprintf(qbuf, sizeof(qbuf), "DELETE FROM %s", name);
198 return UdmSQLQuery(db,NULL,qbuf);
199 }
200
201
202 static size_t
UdmSQLEscStrMonet(UDM_SQL * db,char * to,const char * from,size_t len)203 UdmSQLEscStrMonet(UDM_SQL *db, char *to, const char *from, size_t len)
204 {
205 char *to0= to;
206 /* Escape single quote with single quote, backslash with backslash */
207 for ( ; len && *from; from++, len--)
208 {
209 switch (*from)
210 {
211 case '\\':
212 case '\'':
213 /* Note that no break here!*/
214 *to++= *from;
215 default:
216 *to++= *from;
217 }
218 }
219 *to= '\0';
220 return to - to0;
221 }
222
223
224 static size_t
UdmSQLEscStrStandard(UDM_SQL * db,char * to,const char * from,size_t len)225 UdmSQLEscStrStandard(UDM_SQL *db, char *to,const char *from,size_t len)
226 {
227 char *to0= to;
228 for ( ; len && *from; from++, len--)
229 {
230 switch (*from)
231 {
232 case '\'':
233 /* Note that no break here!*/
234 *to++= *from;
235 default:
236 *to++= *from;
237 }
238 }
239 *to= '\0';
240 return to - to0;
241 }
242
243
244 static size_t
UdmSQLEscStrPgSQL(UDM_SQL * db,char * to,const char * from,size_t len)245 UdmSQLEscStrPgSQL(UDM_SQL *db, char *to,const char *from,size_t len)
246 {
247 char *to0= to;
248 udm_sqldbtype_t DBType= db->DBType;
249 const char *fend= from + len;
250
251 while (*from && from < fend)
252 {
253 #ifdef WIN32
254 /*
255 A workaround against a bug in SQLExecDirect
256 in PostgreSQL ODBC driver. It produces
257 an error when meets a question mark in a string
258 constant. For some reasons question marks are
259 considered as parameters. This should not happen
260 with SQLExecDirect.
261 Let's escape question mark using \x3F notation.
262 */
263 if (DBType == UDM_DB_PGSQL &&
264 (*from == '?' ||
265 *from == '{' ||
266 *from == '}'))
267 {
268 *to++= '\\';
269 *to++= 'x';
270 *to++= '3';
271 *to++= 'F';
272 from++;
273 continue;
274 }
275 #endif
276 /*
277 "ODBC escape convert error" is returned when '{' or '}' are not escaped.
278 */
279 if (DBType == UDM_DB_PGSQL && (*from == '{' || *from == '}'))
280 {
281 *to++= '\\';
282 *to++= 'x';
283 *to++= '7';
284 *to++= udm_hex_digits[*from & 0x0F];
285 from++;
286 continue;
287 }
288 switch(*from)
289 {
290 case '\'':
291 case '\\':
292 *to='\\';to++;
293 default:*to=*from;
294 }
295 to++;from++;
296 }
297 *to= '\0';
298 return to - to0;
299 }
300
301
302 size_t
UdmSQLEscStrGeneric(UDM_SQL * db,char * to,const char * from,size_t len)303 UdmSQLEscStrGeneric(UDM_SQL *db, char *to,const char *from,size_t len)
304 {
305 switch (db->DBType)
306 {
307 case UDM_DB_MYSQL:
308 return UdmSQLEscStrPgSQL(db, to, from, len);
309 case UDM_DB_MONETDB:
310 return UdmSQLEscStrMonet(db, to, from, len);
311 case UDM_DB_PGSQL:
312 /*
313 Older version also did PgSQL-style escaping for:
314 UDM_DB_SOLID, UDM_DB_VIRT, UDM_DB_ORACLE7.
315 Restore PgSQL-style escaping if there are trobles with
316 */
317 return (db->version < 90000) ?
318 UdmSQLEscStrPgSQL(db, to, from, len) :
319 UdmSQLEscStrStandard(db, to, from, len);
320 default:
321 return UdmSQLEscStrStandard(db, to, from, len);
322 }
323 }
324
325
326 size_t
UdmSQLEscStr(UDM_SQL * db,char * to,const char * from,size_t len)327 UdmSQLEscStr(UDM_SQL *db, char *to, const char *from, size_t len)
328 {
329 UDM_ASSERT(from);
330 UDM_ASSERT(to);
331 return db->handler.EscStr(db, to, from, len);
332 }
333
334
335 static int
UdmSQLEscStrLength(UDM_SQL * db,size_t srclen)336 UdmSQLEscStrLength(UDM_SQL *db, size_t srclen)
337 {
338 return ((db->DBType == UDM_DB_PGSQL) ? 4 : 2) * srclen + 1;
339 }
340
341
342 char *
UdmSQLEscStrAlloc(UDM_SQL * db,const char * src,size_t srclen)343 UdmSQLEscStrAlloc(UDM_SQL *db, const char *src, size_t srclen)
344 {
345 char *dst;
346 if(!src ||
347 !(dst= (char*) UdmMalloc(UdmSQLEscStrLength(db, srclen))))
348 return NULL;
349 db->handler.EscStr(db, dst, src, srclen);
350 return dst;
351 }
352
353
354 /*
355 SQL-Escape string to DSTR
356 dstr - a pointer to a initialized DSTR
357 */
358 udm_rc_t
UdmSQLEscDSTR(UDM_SQL * db,UDM_DSTR * dstr,const char * src,size_t srclen)359 UdmSQLEscDSTR(UDM_SQL *db, UDM_DSTR *dstr, const char *src, size_t srclen)
360 {
361 udm_rc_t rc;
362 if (UDM_OK != (rc= UdmDSTRAlloc(dstr, UdmSQLEscStrLength(db, srclen))))
363 return rc;
364 dstr->Val.length= db->handler.EscStr(db, dstr->Val.str, src, srclen);
365 return rc;
366 }
367
368
369
370 static char
371 dangerous_character[256]=
372 {
373 /*00*/ 1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
374 /*10*/ 1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
375 /*20*/ 0,1,1,1,1,0,1,1,1,1,0,0,0,0,0,0, /* !"#$%&'()*+,-./ */
376 /*30*/ 0,0,0,0,0,0,0,0,0,0,0,1,1,0,1,0, /* 0123456789:;<=>? */
377 /*40*/ 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, /* @ABCDEFGHIJKLMNO */
378 /*50*/ 0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,0, /* PQRSTUVWXYZ[\]^_ */
379 /*60*/ 1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, /* `abcdefghijklmno */
380 /*70*/ 0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,1, /* pqrstuvwxyz{|}~ */
381 /*80*/ 3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
382 /*90*/ 3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
383 /*A0*/ 3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
384 /*B0*/ 3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
385 /*C0*/ 3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
386 /*D0*/ 3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
387 /*E0*/ 3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,
388 /*F0*/ 3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3
389 };
390
391
392 /*
393 Escape simple strings, not containing dangerous characters.
394 Used for things like user limit names, user score names, etc.
395 */
396 char *
UdmSQLEscStrSimple(UDM_SQL * db,char * dst,const char * src,size_t len)397 UdmSQLEscStrSimple(UDM_SQL *db, char *dst, const char *src, size_t len)
398 {
399 size_t multiply= 1;
400 const char *srcend= src + len;
401 char *dst0;
402 /*
403 Backslash, quote and double quote character are not allowed,
404 to avoid SQL injection.
405 */
406 UDM_ASSERT(!dangerous_character['%']); /* Need this for "indexer -u url% */
407 UDM_ASSERT(!dangerous_character['?']); /* "indexer -u http://host/?a=b" */
408 UDM_ASSERT(!dangerous_character['=']); /* "indexer -u http://host/?a=b" */
409
410 UDM_ASSERT(dangerous_character['"']);
411 UDM_ASSERT(dangerous_character['\'']);
412 UDM_ASSERT(dangerous_character['\\']);
413
414 if (!dst && !(dst= (char*) UdmMalloc(len * multiply + 1)))
415 return NULL;
416 for ( dst0= dst, srcend= src + len; src < srcend; src++)
417 {
418 *dst++= dangerous_character[(unsigned char) *src] ? '?' : *src;
419 }
420 *dst= '\0';
421 return dst0;
422 }
423
424
425 /*
426 Escape a byte for PgSQL bytea encoding
427 */
428 static inline size_t
UdmSQLBinEscCharForPg(char * dst,unsigned int ch)429 UdmSQLBinEscCharForPg(char *dst, unsigned int ch)
430 {
431 if (ch >= 0x20 && ch <= 0x7F && ch != '\'' && ch != '\\')
432 {
433 *dst= ch;
434 return 1;
435 }
436
437 dst[4]= udm_hex_digits[ch & 0x07]; ch /= 8;
438 dst[3]= udm_hex_digits[ch & 0x07]; ch /= 8;
439 dst[2]= udm_hex_digits[ch & 0x07];
440 dst[1]= '\\';
441 dst[0]= '\\';
442 return 5;
443 }
444
445
446 size_t
UdmSQLBinEscStr(UDM_SQL * db,char * dst,size_t dstlen,const char * src0,size_t len)447 UdmSQLBinEscStr(UDM_SQL *db, char *dst, size_t dstlen, const char *src0, size_t len)
448 {
449 const unsigned char *src= (const unsigned char*) src0;
450 UDM_ASSERT(dst != NULL);
451
452 if (db->DBType == UDM_DB_PGSQL)
453 {
454 char *dst0;
455 for (dst0= dst ; len > 0 ; len--, src++)
456 dst+= UdmSQLBinEscCharForPg(dst, *src);
457 *dst= '\0';
458 return dst - dst0;
459 }
460 UdmSQLEscStr(db, dst, src0, len);
461 return 0;
462 }
463
464
465 udm_rc_t
UdmSQLBegin(UDM_SQL * db)466 UdmSQLBegin(UDM_SQL *db)
467 {
468 return db->handler.Begin(db);
469 }
470
471 udm_rc_t
UdmSQLCommit(UDM_SQL * db)472 UdmSQLCommit(UDM_SQL *db)
473 {
474 return db->handler.Commit(db);
475 }
476
477 udm_rc_t
UdmSQLExecDirect(UDM_SQL * db,UDM_SQLRES * R,const char * q)478 UdmSQLExecDirect(UDM_SQL *db, UDM_SQLRES *R, const char *q)
479 {
480 return db->handler.ExecDirect(db, R, q);
481 }
482
483 udm_rc_t
UdmSQLPrepare(UDM_SQL * db,const char * q)484 UdmSQLPrepare(UDM_SQL *db, const char *q)
485 {
486 return db->handler.Prepare(db, q);
487 }
488
489 udm_rc_t
UdmSQLExecute(UDM_SQL * db)490 UdmSQLExecute(UDM_SQL *db)
491 {
492 return db->handler.Exec(db);
493 }
494
495 udm_rc_t
UdmSQLStmtFree(UDM_SQL * db)496 UdmSQLStmtFree(UDM_SQL *db)
497 {
498 return db->handler.StmtFree(db);
499 }
500
501 udm_rc_t
UdmSQLBindParameter(UDM_SQL * db,int pos,const void * data,int size,udm_sqltype_t type)502 UdmSQLBindParameter(UDM_SQL *db, int pos, const void *data, int size, udm_sqltype_t type)
503 {
504 return db->handler.Bind(db, pos, data, size, type);
505 }
506
507 udm_rc_t
UdmSQLRenameTable(UDM_SQL * db,const char * from,const char * to)508 UdmSQLRenameTable(UDM_SQL *db, const char *from, const char *to)
509 {
510 return db->handler.RenameTable(db, from, to);
511 }
512
513 udm_rc_t
UdmSQLCopyStructure(UDM_SQL * db,const char * from,const char * to)514 UdmSQLCopyStructure(UDM_SQL *db, const char *from, const char *to)
515 {
516 return db->handler.CopyStructure(db, from, to);
517 }
518
519 udm_rc_t
UdmSQLLockOrBegin(UDM_SQL * db,const char * param)520 UdmSQLLockOrBegin(UDM_SQL *db, const char *param)
521 {
522 return db->handler.LockOrBegin(db, param);
523 }
524
525 udm_rc_t
UdmSQLUnlockOrCommit(UDM_SQL * db)526 UdmSQLUnlockOrCommit(UDM_SQL *db)
527 {
528 return db->handler.UnlockOrCommit(db);
529 }
530
531
532 UDM_API(udm_rc_t)
UdmSQLQuery(UDM_SQL * db,UDM_SQLRES * SQLRes,const char * query)533 UdmSQLQuery(UDM_SQL *db, UDM_SQLRES *SQLRes, const char * query)
534 {
535 UDM_SQLRES res;
536
537 /* FIXME: call UdmSQLFree at exit if SQLRes = NULL */
538 if (! SQLRes) SQLRes = &res;
539
540 db->handler.Query(db, SQLRes, query);
541
542 if (db->errcode && (db->flags & UDM_SQL_IGNORE_ERROR))
543 db->errcode= 0;
544
545 #ifdef DEBUG_ERR_QUERY_XXX
546 if (db->errcode)
547 fprintf(stderr, "{%s:%d} Query: %s\n\n", file, line, query);
548 #endif
549
550 return db->errcode ? UDM_ERROR : UDM_OK;
551 }
552
553
554 UDM_API(size_t)
UdmSQLNumRows(UDM_SQLRES * res)555 UdmSQLNumRows(UDM_SQLRES * res)
556 {
557 return(res?res->nRows:0);
558 }
559
UdmSQLNumCols(UDM_SQLRES * res)560 size_t UdmSQLNumCols(UDM_SQLRES * res)
561 {
562 return(res?res->nCols:0);
563 }
564
565 UDM_API(const char *)
UdmSQLValue(UDM_SQLRES * res,size_t i,size_t j)566 UdmSQLValue(UDM_SQLRES * res,size_t i,size_t j){
567
568 #if HAVE_PGSQL
569 if (res->db->DBDriver == UDM_DBAPI_PGSQL && !res->Items)
570 return(PQgetvalue((PGresult*)res->specific,(int)(i),(int)(j)));
571 #endif
572
573 if (i<res->nRows)
574 {
575 size_t offs=res->nCols*i+j;
576 return res->Items[offs].str;
577 }
578 else
579 {
580 return NULL;
581 }
582 }
583
584
585 UDM_API(udm_rc_t)
UdmSQLFetchRowSimple(UDM_SQL * db,UDM_SQLRES * res,UDM_STR * buf)586 UdmSQLFetchRowSimple (UDM_SQL *db, UDM_SQLRES *res, UDM_STR *buf)
587 {
588 size_t j;
589 size_t offs = res->nCols * res->curRow;
590
591 if (res->curRow >= res->nRows)
592 return UDM_ERROR;
593
594 for (j = 0; j < res->nCols; j++)
595 {
596 buf[j]= res->Items[offs + j];
597 }
598 res->curRow++;
599
600 return(UDM_OK);
601 }
602
603
604 UDM_API(udm_rc_t)
UdmSQLStoreResultSimple(UDM_SQL * db,UDM_SQLRES * res)605 UdmSQLStoreResultSimple(UDM_SQL *db, UDM_SQLRES *res)
606 {
607 return UDM_OK;
608 }
609
610
611 UDM_API(udm_rc_t)
UdmSQLFreeResultSimple(UDM_SQL * db,UDM_SQLRES * res)612 UdmSQLFreeResultSimple(UDM_SQL *db, UDM_SQLRES *res)
613 {
614 if (res->Fields)
615 {
616 size_t i;
617 for(i=0;i<res->nCols;i++)
618 {
619 /*
620 printf("%s(%d)\n",res->Fields[i].sqlname,res->Fields[i].sqllen);
621 */
622 UDM_FREE(res->Fields[i].sqlname);
623 }
624 UDM_FREE(res->Fields);
625 }
626
627 #if HAVE_PGSQL
628 if (res->db->DBDriver == UDM_DBAPI_PGSQL)
629 {
630 PQclear((PGresult*)res->specific);
631 /*
632 Continue to UdmSQLResFreeGeneric() to
633 free allocated memory if we have bytea datatype
634 */
635 }
636 #endif
637
638 UdmSQLResFreeGeneric(res);
639
640 return UDM_OK;
641 }
642
643
644 UDM_API(void)
UdmSQLFree(UDM_SQLRES * res)645 UdmSQLFree(UDM_SQLRES * res)
646 {
647 res->db->handler.FreeResult(res->db, res);
648 }
649
650
UdmSQLLen(UDM_SQLRES * res,size_t i,size_t j)651 size_t UdmSQLLen(UDM_SQLRES * res,size_t i,size_t j)
652 {
653 size_t offs=res->nCols*i+j;
654 #if HAVE_PGSQL
655 if (res->db->DBDriver == UDM_DBAPI_PGSQL && !res->Items)
656 return PQgetlength((PGresult*)res->specific, i, j);
657 #endif
658 return res->Items[offs].length;
659 }
660
661
662 void
UdmSQLValueToConstStr(UDM_CONST_STR * str,UDM_SQLRES * res,size_t row,size_t col)663 UdmSQLValueToConstStr(UDM_CONST_STR *str,
664 UDM_SQLRES *res, size_t row, size_t col)
665 {
666 str->str= UdmSQLValue(res, row, col);
667 str->length= UdmSQLLen(res, row, col);
668 }
669
670
671 udm_rc_t
UdmSQLClose(UDM_SQL * db)672 UdmSQLClose(UDM_SQL *db)
673 {
674 udm_rc_t rc;
675 if (!db->connected)
676 return UDM_OK;
677 rc= db->handler.Close(db);
678 db->connected= UDM_FALSE;
679 return rc;
680 }
681
682
683 void
UdmSQLResListInit(UDM_SQLRESLIST * List)684 UdmSQLResListInit(UDM_SQLRESLIST *List)
685 {
686 bzero((void*)List, sizeof(*List));
687 }
688
689
690 udm_rc_t
UdmSQLResListAdd(UDM_SQLRESLIST * List,UDM_SQLRES * Res)691 UdmSQLResListAdd(UDM_SQLRESLIST *List, UDM_SQLRES *Res)
692 {
693 size_t nbytes= (List->nitems + 1) * sizeof(UDM_SQLRES);
694 if (!(List->Item= (UDM_SQLRES*) UdmRealloc(List->Item, nbytes)))
695 return UDM_ERROR;
696 List->Item[List->nitems]= Res[0];
697 List->nitems++;
698 return UDM_OK;
699 }
700
701
702 void
UdmSQLResListFree(UDM_SQLRESLIST * List)703 UdmSQLResListFree(UDM_SQLRESLIST *List)
704 {
705 size_t i;
706 for (i= 0; i < List->nitems; i++)
707 {
708 UdmSQLFree(&List->Item[i]);
709 }
710 UdmFree(List->Item);
711 UdmSQLResListInit(List);
712 }
713
714
715 udm_rc_t
UdmSQLQueryOneRowInt(UDM_SQL * db,int * res,const char * qbuf)716 UdmSQLQueryOneRowInt(UDM_SQL *db, int *res, const char *qbuf)
717 {
718 UDM_SQLRES sqlRes;
719 udm_rc_t rc;
720 if (UDM_OK != (rc= UdmSQLQuery(db, &sqlRes, qbuf)))
721 return rc;
722 if (UdmSQLNumRows(&sqlRes) < 1)
723 {
724 rc= UDM_ERROR;
725 *res= 0;
726 udm_snprintf(db->errstr, sizeof(db->errstr),
727 "Query should have returned one row");
728 }
729 else
730 *res= UDM_ATOI(UdmSQLValue(&sqlRes, 0, 0));
731 UdmSQLFree(&sqlRes);
732 return rc;
733 }
734
735
736 static const char*
737 odbc_params[UDM_SQL_MAX_BIND_PARAM]=
738 {
739 "?","?","?","?","?","?","?","?",
740 "?","?","?","?","?","?","?","?",
741 "?","?","?","?","?","?","?","?",
742 "?","?","?","?","?","?","?","?",
743 "?","?","?","?","?","?","?","?",
744 "?","?","?","?","?","?","?","?",
745 "?","?","?","?","?","?","?","?",
746 "?","?","?","?","?","?","?","?",
747 };
748
749
750 static const char*
751 oracle_params[UDM_SQL_MAX_BIND_PARAM]=
752 {
753 ":1", ":2", ":3", ":4", ":5", ":6", ":7", ":8", ":9",
754 ":10",":11",":12",":13",":14",":15",":16",":17",":18",":19",
755 ":20",":21",":22",":23",":24",":25",":26",":27",":28",":29",
756 ":30",":31",":32",":33",":34",":35",":36",":37",":38",":39",
757 ":40",":41",":42",":43",":44",":45",":46",":47",":48",":49",
758 ":50",":51",":52",":53",":54",":55",":56",":57",":58",":59",
759 ":60",":61",":62",":63",":64"
760 };
761
762
763 static const char*
764 pgsql_params[UDM_SQL_MAX_BIND_PARAM]=
765 {
766 "$1", "$2", "$3", "$4", "$5", "$6", "$7", "$8", "$9",
767 "$10","$11","$12","$13","$14","$15","$16","$17","$18","$19",
768 "$20","$21","$22","$23","$24","$25","$26","$27","$28","$29",
769 "$30","$31","$32","$33","$34","$35","$36","$37","$38","$39",
770 "$40","$41","$42","$43","$44","$45","$46","$47","$48","$49",
771 "$50","$51","$52","$53","$54","$55","$56","$57","$58","$59",
772 "$60","$61","$62","$63","$64"
773 };
774
775
776 const char *
UdmSQLParamPlaceHolder(UDM_SQL * db,size_t i)777 UdmSQLParamPlaceHolder(UDM_SQL *db, size_t i)
778 {
779 UDM_ASSERT(i < UDM_SQL_MAX_BIND_PARAM);
780 if (db->handler.Bind == UdmSQLBindGeneric)
781 return odbc_params[i - 1];
782 if (db->DBDriver == UDM_DBAPI_ORACLE8)
783 return oracle_params[i - 1];
784 if (db->DBDriver == UDM_DBAPI_PGSQL)
785 return pgsql_params[i - 1];
786 return odbc_params[i - 1];
787 }
788
789
790 /*
791 Generic prepared statement API for libraries
792 not supporting their own prepared statements.
793 */
794
795 #define UDM_GENERIC_MAX_BIND_PARAM UDM_SQL_MAX_BIND_PARAM
796
797
798 typedef struct generic_sql_ps_st
799 {
800 char *sql;
801 int nParams;
802 int paramTypes[UDM_GENERIC_MAX_BIND_PARAM];
803 const void *paramValues[UDM_GENERIC_MAX_BIND_PARAM];
804 int paramLengths[UDM_GENERIC_MAX_BIND_PARAM];
805 } UDM_PS;
806
807
808 static UDM_PS *
alloc_ps(UDM_SQL * db)809 alloc_ps(UDM_SQL *db)
810 {
811 if (db->ps)
812 return (UDM_PS*) db->ps;
813 if ((db->ps= (UDM_PS*) UdmMalloc(sizeof(UDM_PS))))
814 return (UDM_PS*) db->ps;
815 return NULL;
816 }
817
818
819 udm_rc_t
UdmSQLPrepareGeneric(UDM_SQL * db,const char * query)820 UdmSQLPrepareGeneric(UDM_SQL *db, const char *query)
821 {
822 UDM_PS *ps= alloc_ps(db);
823
824 if (!ps)
825 return UDM_ERROR;
826
827 ps->nParams= 0;
828
829 /*
830 TODO: add automatic connecting here
831 */
832 if (!(ps->sql= UdmStrdup(query)))
833 {
834 udm_snprintf(db->errstr, sizeof(db->errstr),
835 "UdmSQLPrepareGeneric: could not allocate memory");
836 return UDM_ERROR;
837 }
838 return UDM_OK;
839 }
840
841
842 udm_rc_t
UdmSQLBindGeneric(UDM_SQL * db,int pos,const void * data,int size,udm_sqltype_t type)843 UdmSQLBindGeneric(UDM_SQL *db, int pos, const void *data, int size, udm_sqltype_t type)
844 {
845 UDM_PS *ps= (UDM_PS*) db->ps;
846 UDM_ASSERT(ps->sql);
847
848 if (!ps)
849 return UDM_ERROR;
850
851 if (ps->nParams < pos)
852 ps->nParams= pos;
853 pos--;
854 ps->paramValues[pos]= data;
855 ps->paramLengths[pos]= (int) size;
856 ps->paramTypes[pos]= type;
857 return UDM_OK;
858 }
859
860
861 static size_t
prepared_query_length(const char * sql,UDM_PS * param)862 prepared_query_length(const char *sql, UDM_PS *param)
863 {
864 int i;
865 size_t len= strlen(param->sql) + 1;
866
867 for (i= 0; i < param->nParams; i++)
868 {
869 switch (param->paramTypes[i])
870 {
871 case UDM_SQLTYPE_LONGVARBINARY:
872 case UDM_SQLTYPE_LONGVARCHAR :
873 case UDM_SQLTYPE_VARCHAR:
874 len+= param->paramLengths[i] < 0 ?
875 4 :/* NULL in ODBC*/
876 param->paramLengths[i] * 10 + 10; /* TODO: proper constant */
877 break;
878 case UDM_SQLTYPE_INT32:
879 len+= 21;
880 break;
881 }
882 }
883 return len;
884 }
885
886
887 static size_t
prepared_query_add_params(UDM_SQL * db,char * dst,size_t dstlen,UDM_PS * param,size_t param_num)888 prepared_query_add_params(UDM_SQL *db, char *dst, size_t dstlen,
889 UDM_PS *param, size_t param_num)
890 {
891 char *dst0= dst;
892 int is_bin= (param->paramTypes[param_num] == UDM_SQLTYPE_LONGVARBINARY);
893 size_t srclen= param->paramLengths[param_num];
894 const char *src= (const char *) param->paramValues[param_num];
895
896 if (!srclen)
897 {
898 /* Note, srclen is never 0 when type is UDM_SQLTYPE_INT32 */
899 if (db->DBType == UDM_DB_MIMER)
900 *dst++= 'X';
901 *dst++= '\'';
902 *dst++= '\'';
903 goto ret;
904 }
905
906 switch (param->paramTypes[param_num])
907 {
908 case UDM_SQLTYPE_LONGVARBINARY:
909 case UDM_SQLTYPE_LONGVARCHAR :
910 case UDM_SQLTYPE_VARCHAR:
911 if (is_bin && (db->flags & UDM_SQL_HAVE_0xHEX))
912 {
913 /*
914 MSSQL: 0x20C883 notation (0xHEX)
915 Sybase: 0x20C883 notation (0xHEX)
916 Access: 0x20C883 notation (0xHEX)
917 */
918 *dst++= '0';
919 *dst++= 'x';
920 dst+= UdmHexEncode(dst, src, srclen);
921 }
922 else if (is_bin && (db->flags & UDM_SQL_HAVE_STDHEX))
923 {
924 /*
925 SQLite3: X'20C883' (STDHEX)
926 */
927 *dst++= 'X';
928 *dst++= '\'';
929 dst+= UdmHexEncode(dst, src, srclen);
930 *dst++= '\'';
931 }
932 else if (is_bin && (db->DBType == UDM_DB_ORACLE8))
933 {
934 if (param->paramLengths[param_num] < 0) /* Oracle via ODBC */
935 {
936 strcpy(dst, "NULL");
937 dst+= 4;
938 }
939 else
940 {
941 /* Oracle: '20C883', i.e. binary notation by default */
942 *dst++= '\'';
943 dst+= UdmHexEncode(dst, src, srclen);
944 *dst++= '\'';
945 }
946 }
947 else
948 {
949 /*
950 TODO:
951 PgSQL: E'\x20\xC8\x83' notation
952 SQLite: ???
953 */
954 if (db->DBType == UDM_DB_PGSQL && db->version >= 80101)
955 *dst++= 'E';
956 *dst++= '\'';
957 if (is_bin)
958 UdmSQLBinEscStr(db, dst, dstlen, src, srclen); /* TODO: get rid of strlen below*/
959 else
960 UdmSQLEscStr(db, dst, src, srclen);
961 dst+= strlen(dst);
962 *dst++= '\'';
963 }
964 break;
965 case UDM_SQLTYPE_INT32:
966 return sprintf(dst, "%d", *((const int*) src));
967 break;
968 }
969
970 ret:
971 *dst= '\0';
972 return dst - dst0;
973 }
974
975
976 udm_rc_t
UdmSQLExecGeneric(UDM_SQL * db)977 UdmSQLExecGeneric(UDM_SQL *db)
978 {
979 UDM_PS *ps= (UDM_PS*) db->ps;
980 char *qbuf, *dst;
981 const char *src;
982 size_t qlen= prepared_query_length(ps->sql, ps);
983 size_t param_num= 0;
984 udm_rc_t rc;
985 UDM_SQLRES SQLRes;
986
987 if (!(qbuf= (char*) UdmMalloc(qlen)))
988 {
989 udm_snprintf(db->errstr, sizeof(db->errstr),
990 "UdmSQLExecGeneric: Failed to allocated buffer %d bytes",
991 (int) qlen);
992 return UDM_ERROR;
993 }
994
995 for (src= ps->sql, dst= qbuf; *src; src++)
996 {
997 if (*src == '?')
998 {
999 size_t len= prepared_query_add_params(db, dst, qlen, ps, param_num);
1000 param_num++;
1001 dst+= len;
1002 }
1003 else
1004 {
1005 *dst++= *src;
1006 }
1007 }
1008 *dst= '\0';
1009
1010 rc= UdmSQLExecDirect(db, &SQLRes, qbuf);
1011
1012 UdmSQLFree(&SQLRes);
1013 UdmFree(qbuf);
1014
1015 return rc;
1016 }
1017
1018
1019 udm_rc_t
UdmSQLStmtFreeGeneric(UDM_SQL * db)1020 UdmSQLStmtFreeGeneric(UDM_SQL *db)
1021 {
1022 UDM_PS *ps= (UDM_PS*) db->ps;
1023 UDM_ASSERT(ps->sql);
1024 UDM_FREE(ps->sql);
1025 UDM_FREE(db->ps);
1026 return UDM_OK;
1027 }
1028
1029
1030 /*
1031
1032 Prepared statement API
1033
1034 Name Native Generic None Default
1035 ---- ------ ------- ---- -------
1036 Oracle Yes No No Generic
1037 MSSQL TODO ? ? None
1038 Sybase TODO ? ? None
1039 MySQL Yes Yes Yes Native|Generic (client version dependent)
1040 PgSQL Yes Yes Yes Native|Generic (client version dependend)
1041 IBase Yes No No Native
1042 SQLite TODO Yes No Generic
1043 SQLite3 TODO Yes Yes Generic
1044
1045 ODBC-DB2 Yes No No Native
1046 ODBC-MIMER Yes No No Native
1047 ODBC-ORACLE Yes No No Native
1048 ODBC-MSQQL Yes ? Yes None
1049 ODBC-SYBASE Yes ? Yes None
1050 ODBC-MYSQL Yes Yes Yes Native
1051 ODDB-IBASE Yes No No Native
1052
1053 ODBC-CACHE Yes ? ? Native
1054 ODBC-VIRT Yes ? ? Native
1055 ODBC-Solid ? ? ? Native
1056 ODBC-SapDB ? ? ? Native
1057 ODBC-ACCESS ? ? ? Native
1058
1059 */
1060
1061
1062 udm_rc_t
UdmSQLLockOrBeginGeneric(UDM_SQL * db,const char * query)1063 UdmSQLLockOrBeginGeneric(UDM_SQL *db, const char *query)
1064 {
1065 return db->handler.Begin(db);
1066 }
1067
1068 udm_rc_t
UdmSQLUnlockOrCommitGeneric(UDM_SQL * db)1069 UdmSQLUnlockOrCommitGeneric(UDM_SQL *db)
1070 {
1071 return db->handler.Commit(db);
1072 }
1073
1074 #endif /* HAVE_SQL */
1075