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 #if (HAVE_ODBC)
21
22
23 #include <stdio.h>
24 #include <stdlib.h>
25 #include <string.h>
26 #include <sys/types.h>
27 #ifdef HAVE_UNISTD_H
28 #include <unistd.h>
29 #endif
30
31 #include "udm_common.h"
32 #include "udm_sqldbms.h"
33 #include "udm_utils.h"
34 #include "udm_vars.h"
35 #include "udm_xmalloc.h"
36
37 #if HAVE_IODBC
38 #include <sql.h>
39 #include <sqlext.h>
40 #endif
41
42 #if HAVE_EASYSOFT
43 #include <sql.h>
44 #include <sqlext.h>
45 #endif
46
47 #if HAVE_VIRT
48 #include <iodbc.h>
49 #include <isql.h>
50 #include <isqlext.h>
51 #endif
52
53 #if HAVE_UNIXODBC
54 #include <sql.h>
55 #include <sqlext.h>
56 #endif
57
58 #if HAVE_SAPDB
59 #include <WINDOWS.H>
60 #include <sql.h>
61 #include <sqlext.h>
62 #endif
63
64 #if HAVE_SOLID
65 #include <cli0cli.h>
66 #endif
67
68 #if HAVE_DB2
69 #include <sqlcli1.h>
70 #endif
71
72 #ifdef WIN32
73 #include <process.h>
74 #endif
75
76
77 /************************** ODBC ***********************************/
78 #if HAVE_DB2
79 typedef SQLHANDLE HENV;
80 typedef SQLHANDLE HDBC;
81 typedef SQLHANDLE HSTMT;
82 #endif
83
84 typedef struct udm_odbc_bind_st
85 {
86 SQLLEN size;
87 SQLPOINTER data;
88 #if defined(__x86_64__)
89 udm_uint8 int8buf;
90 #endif
91 } UDM_ODBC_BINDBUF;
92
93
94 typedef struct udm_odbc_stmt_st
95 {
96 HSTMT hstmt;
97 UDM_ODBC_BINDBUF BindBuf[UDM_SQL_MAX_BIND_PARAM];
98 } UDM_ODBC_STMT;
99
100
101 typedef struct udm_odbc_conn_st
102 {
103 HDBC hDbc;
104 HENV hEnv;
105 UDM_ODBC_STMT stmt;
106 } UDM_ODBC_CONN;
107
108
109 typedef struct udm_odbc_field_st
110 {
111 SQLCHAR Name[32];
112 SQLSMALLINT NameLen;
113 SQLSMALLINT Type;
114 SQLULEN Size;
115 SQLSMALLINT Scale;
116 SQLSMALLINT Nullable;
117 } UDM_ODBC_FIELD;
118
119
120
121 static inline int
SQL_OK(int rc)122 SQL_OK(int rc)
123 {
124 return rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO;
125 }
126
127
128 static UDM_ODBC_CONN*
UdmOB(UDM_SQL * db)129 UdmOB(UDM_SQL *db)
130 {
131 return (UDM_ODBC_CONN*) db->specific;
132 }
133
134
135 static void
UdmSetErrorCode(UDM_SQL * db,int errcode)136 UdmSetErrorCode(UDM_SQL *db, int errcode)
137 {
138 db->errcode= errcode;
139 }
140
141
142 static void
UdmODBCDisplayError(UDM_SQL * db,HSTMT * hstmt,const char * prefix)143 UdmODBCDisplayError(UDM_SQL *db, HSTMT *hstmt, const char *prefix)
144 {
145 UCHAR szSqlState[10];
146 UCHAR szErrMsg[400];
147 SDWORD naterr;
148 SWORD length;
149 RETCODE rc=SQL_SUCCESS;
150 size_t len= strlen(prefix);
151 UDM_ODBC_CONN *sdb= UdmOB(db);
152
153 strcpy(db->errstr, prefix);
154
155 while (1)
156 {
157 rc= SQLError(sdb->hEnv, sdb->hDbc, hstmt, szSqlState,
158 &naterr,szErrMsg,sizeof(szErrMsg)-1,&length);
159 if ((SQL_SUCCESS != rc) && (SQL_SUCCESS_WITH_INFO != rc))
160 break;
161 szErrMsg[sizeof(szErrMsg)-1]='\0';
162 len+= sprintf(db->errstr+len, "[SQLSTATE:%s]%s",szSqlState,szErrMsg);
163 }
164 }
165
166
167 static int
UdmODBCTypeIsBinary(int Type)168 UdmODBCTypeIsBinary(int Type)
169 {
170 return Type == SQL_BINARY ||
171 Type == SQL_VARBINARY ||
172 Type == SQL_LONGVARBINARY ||
173 Type == -98/* DB2 SQL_BLOB */;
174 }
175
176
177 static int
IsHexEncodedBinary(UDM_SQL * db,int SourceType,int TargetType)178 IsHexEncodedBinary(UDM_SQL *db, int SourceType, int TargetType)
179 {
180 /*
181 MonetDB does not encode binary values to HEX when bind type is SQL_C_CHAR
182 */
183 return UdmODBCTypeIsBinary(SourceType) &&
184 TargetType != SQL_C_BINARY &&
185 db->DBType != UDM_DB_MONETDB;
186 }
187
188
189 static SQLSMALLINT
TargetFetchType(UDM_SQL * db,SQLSMALLINT Type)190 TargetFetchType(UDM_SQL *db, SQLSMALLINT Type)
191 {
192 if (Type == SQL_FLOAT)
193 return SQL_C_FLOAT;
194
195 if (Type == SQL_DOUBLE)
196 return SQL_C_DOUBLE;
197
198 if (UdmODBCTypeIsBinary(Type))
199 {
200 switch (db->DBType)
201 {
202 case UDM_DB_PGSQL: /* bytea */
203 case UDM_DB_MSSQL: /* blob */
204 case UDM_DB_IBASE: /* blob */
205 case UDM_DB_ORACLE8: /* blob */
206 case UDM_DB_SYBASE: /* image/varbinary */
207 case UDM_DB_DB2:
208 case UDM_DB_MYSQL:
209 return SQL_C_BINARY;
210 case UDM_DB_SQLITE:
211 case UDM_DB_SQLITE3:
212 case UDM_DB_SOLID:
213 case UDM_DB_VIRT:
214 case UDM_DB_SAPDB:
215 case UDM_DB_ACCESS:
216 case UDM_DB_MIMER:
217 case UDM_DB_CACHE:
218 case UDM_DB_MONETDB:
219 /*
220 SQLGetData as SQL_C_BINARY does not work with MonetDB:
221 "Restricted data type attribute violation" error is returned
222 */
223 return SQL_CHAR;
224 }
225 }
226
227 #ifdef HAVE_UNIXODBC
228 /*
229 For some weird reasons, Sybase native (i.e. not FreeTDS)
230 ODBC driver in conjunction with unixODBC always returns 4
231 in pcbValue if get_type is SQL_CHAR. Let's fetch as SQL_C_SLONG.
232 */
233 if (Type == SQL_INTEGER && db->DBType == UDM_DB_SYBASE)
234 return SQL_C_SLONG;
235 #endif
236
237 if (Type == SQL_WLONGVARCHAR && db->DBType == UDM_DB_MYSQL)
238 {
239 /*
240 Workaround for a bug in MyODBC driver.
241 It reports "TEXT BINARY" as SQL_WLONGVARCHAR.
242 */
243 return SQL_C_BINARY;
244 }
245 return SQL_CHAR;
246 }
247
248
249 static udm_rc_t
execDB(UDM_SQL * db,UDM_SQLRES * result,const char * sqlstr)250 execDB(UDM_SQL *db, UDM_SQLRES *result, const char *sqlstr)
251 {
252 RETCODE dbrc;
253 SWORD iResColumns;
254 SQLLEN iRowCount;
255 static char bindbuf[(int)(64L * 1024L - 16L)];
256 UDM_ODBC_CONN *sdb= UdmOB(db);
257 HSTMT hstmt;
258 udm_rc_t rc= UDM_OK;
259
260 if(!strcmp(sqlstr,"COMMIT"))
261 {
262 if (!SQL_OK(dbrc= SQLTransact(sdb->hEnv, sdb->hDbc, SQL_COMMIT)))
263 {
264 UdmSetErrorCode(db, 1);
265 UdmODBCDisplayError(db, SQL_NULL_HSTMT, "SQLTransact:");
266 return UDM_ERROR;
267 }
268 else
269 {
270 UdmSetErrorCode(db, 0);
271 return UDM_OK;
272 }
273 }
274
275 if (!SQL_OK(dbrc= SQLAllocStmt(sdb->hDbc, &hstmt)))
276 {
277 UdmSetErrorCode(db, 1);
278 UdmODBCDisplayError(db, SQL_NULL_HSTMT, "SQLAllocStmt:");
279 return UDM_ERROR;
280 }
281
282 #ifdef HAVE_GCC_PRAGMA_PUSH
283 #pragma GCC diagnostic push
284 #pragma GCC diagnostic ignored "-Wcast-qual"
285 #endif
286 if (!SQL_OK(dbrc= SQLExecDirect(hstmt, (SQLCHAR *)sqlstr, SQL_NTS)))
287 {
288 if (dbrc == SQL_NO_DATA)
289 goto ex;
290 UdmODBCDisplayError(db, hstmt, "SQLExecDirect:");
291 rc= UDM_ERROR;
292 goto ex;
293 }
294 #ifdef HAVE_GCC_PRAGMA_PUSH
295 #pragma GCC diagnostic pop
296 #endif
297
298 if (!SQL_OK(dbrc= SQLNumResultCols(hstmt, &iResColumns)))
299 {
300 UdmODBCDisplayError(db, hstmt, "SQLNumResultCols:");
301 rc= UDM_ERROR;
302 goto ex;
303 }
304
305 if (!iResColumns)
306 {
307 if (!SQL_OK(dbrc= SQLRowCount(hstmt, &iRowCount)))
308 {
309 UdmODBCDisplayError(db, hstmt, "SQLRowCount:");
310 rc= UDM_ERROR;
311 goto ex;
312 }
313 }
314 else
315 {
316 size_t res_count;
317 int col;
318 size_t mRows;
319
320 UDM_ODBC_FIELD Field[128];
321
322 result->nRows= 0;
323 result->nCols= iResColumns;
324 result->Items= NULL;
325
326 result->Fields=(UDM_SQLFIELD*)UdmMalloc(result->nCols*sizeof(UDM_SQLFIELD));
327 bzero(result->Fields, result->nCols*sizeof(UDM_SQLFIELD));
328
329 for(col=0; col < iResColumns; col++)
330 {
331 SQLDescribeCol(hstmt, col+1,
332 Field[col].Name,
333 (SQLSMALLINT) sizeof(Field[col].Name) - 1,
334 &Field[col].NameLen,
335 &Field[col].Type,
336 &Field[col].Size,
337 &Field[col].Scale,
338 &Field[col].Nullable);
339 result->Fields[col].sqlname= (char*)UdmStrdup((const char*)Field[col].Name);
340 }
341
342 dbrc= SQL_NO_DATA_FOUND;
343 for (mRows=0, res_count=0;
344 (db->res_limit ? (res_count < db->res_limit) : 1);
345 res_count++)
346 {
347 int i;
348
349 if (!SQL_OK(dbrc= SQLFetch(hstmt)))
350 {
351 if (dbrc != SQL_NO_DATA_FOUND)
352 {
353 UdmODBCDisplayError(db, hstmt, "SQLFetch:");
354 if (db->DBType == UDM_DB_DB2) /* FIXME: check with all databases */
355 {
356 rc= UDM_ERROR;
357 goto ex;
358 }
359 }
360 break;
361 }
362 if (mRows <= (result->nRows+1) * iResColumns)
363 {
364 mRows+= 1024 * iResColumns;
365 result->Items=(UDM_STR*)UdmXrealloc(result->Items,mRows*sizeof(UDM_STR));
366 }
367
368 for (i= 0; i < iResColumns; i++)
369 {
370 size_t offs=result->nRows*iResColumns+i;
371 const char *p= "";
372 char *tmp= NULL;
373 SQLLEN pcbValue= 0; /* DB2 Linux driver needs 0 */
374 SQLSMALLINT get_type= TargetFetchType(db, Field[i].Type);
375
376 dbrc= SQLGetData(hstmt,i+1,get_type,bindbuf,sizeof(bindbuf),&pcbValue);
377 if (dbrc == SQL_SUCCESS_WITH_INFO)
378 {
379 if (pcbValue <= 0)
380 {
381 udm_snprintf(db->errstr, sizeof(db->errstr),
382 "execDB: Unexpected pcbValue %d", (int) pcbValue);
383 return UDM_ERROR;
384 }
385
386 if (pcbValue > (SQLLEN) sizeof(bindbuf))
387 {
388 char *dst;
389 SQLLEN tmp_pcbValue, remainingLength;
390 SQLLEN copiedLength= sizeof(bindbuf);
391 int reserved_for_null= 0;
392
393 if (IsHexEncodedBinary(db, Field[i].Type, get_type))
394 {
395 int null_length= bindbuf[copiedLength - 2] == '\0' ? 2 :
396 bindbuf[copiedLength - 1] == '\0' ? 1 : 0;
397 copiedLength-= null_length;
398 reserved_for_null= null_length > 0 ? 1 : 0;
399 }
400
401 /*
402 When fetching hex encoded binary data,
403 need extra byte for the null terminator.
404 */
405 if (!(p= tmp= (char*) UdmMalloc((pcbValue + reserved_for_null))))
406 {
407 p= "";
408 pcbValue= 0;
409 goto decode;
410 }
411 memcpy(tmp, bindbuf, copiedLength);
412 dst= tmp + copiedLength;
413 remainingLength= pcbValue - copiedLength;
414
415 dbrc= SQLGetData(hstmt, i + 1, get_type, dst,
416 remainingLength + reserved_for_null,
417 &tmp_pcbValue);
418 if (dbrc != SQL_SUCCESS || tmp_pcbValue <= 0)
419 {
420 udm_snprintf(db->errstr, sizeof(db->errstr),
421 "execDB: SQLGetData returned unexpected code (%d) or pcbValue (%d)",
422 dbrc, (int) tmp_pcbValue);
423 return UDM_ERROR;
424 }
425 }
426 else
427 {
428 p= "";
429 pcbValue= 0;
430 }
431 }
432 else if (dbrc == SQL_SUCCESS)
433 {
434 if (pcbValue == SQL_NULL_DATA)
435 {
436 bindbuf[0]= '\0';
437 pcbValue= 0;
438 }
439 else if (get_type == SQL_C_FLOAT)
440 {
441 float num;
442 memcpy(&num, bindbuf, sizeof(num));
443 pcbValue= sprintf(bindbuf, "%f", num);
444 }
445 else if (get_type == SQL_C_DOUBLE)
446 {
447 double num;
448 memcpy(&num, bindbuf, sizeof(num));
449 pcbValue= sprintf(bindbuf, "%f", num);
450 }
451 else if (get_type == SQL_C_SLONG)
452 {
453 long int num;
454 memcpy(&num, bindbuf, sizeof(num));
455 pcbValue= sprintf(bindbuf, "%ld", num);
456 }
457 p= bindbuf;
458 }
459 else if (dbrc == SQL_NO_DATA)
460 {
461 /* MSSQL returns this in case of an empty IMAGE value */
462 /* Do nothing */
463 }
464 else if (UdmODBCTypeIsBinary(Field[i].Type) &&
465 dbrc == SQL_ERROR &&
466 db->DBType == UDM_DB_MONETDB)
467 {
468 /* SQLGetData returns SQL_ERROR on empty BLOB for MonetDB */
469 bindbuf[0]= '\0';
470 pcbValue= 0;
471 }
472 else
473 {
474 UdmODBCDisplayError(db, hstmt, "Unknown data type:");
475 rc= UDM_ERROR;
476 goto ex;
477 }
478
479 decode:
480 if (IsHexEncodedBinary(db, Field[i].Type, get_type))
481 {
482 DecodeHexStr(p, &result->Items[offs], pcbValue);
483 }
484 else
485 {
486 result->Items[offs].str= (char*)malloc(pcbValue+1);
487 memcpy(result->Items[offs].str, p, pcbValue);
488 result->Items[offs].str[pcbValue]='\0';
489 result->Items[offs].length= pcbValue;
490 }
491 if (tmp)
492 UdmFree(tmp);
493 }
494 result->nRows++;
495 }
496 }
497
498 ex:
499 SQLFreeStmt(hstmt, SQL_DROP);
500 db->res_limit=0;
501 db->errcode= (rc == UDM_OK) ? 0 : 1;
502 return rc;
503 }
504
505
506 static udm_rc_t
UdmODBCGetVersion(UDM_SQL * db)507 UdmODBCGetVersion(UDM_SQL *db)
508 {
509 UDM_ODBC_CONN *sdb= UdmOB(db);
510 char version[128];
511 SQLSMALLINT DummyLen;
512 SQLRETURN rc= SQLGetInfo(sdb->hDbc, SQL_DBMS_VER,
513 version, sizeof(version), &DummyLen);
514 if (SQL_OK(rc))
515 {
516 version[sizeof(version) - 1]= '\0';
517 if (!strncmp(version, "PostgreSQL ", 11))
518 {
519 int a, b, c;
520 if (3 == sscanf(version + 11, "%d.%d.%d", &a, &b, &c))
521 db->version= a * 10000 + b * 100 + c;
522 }
523 return UDM_OK;
524 }
525 db->version= 0;
526 return UDM_ERROR;
527 }
528
529
530 static udm_rc_t
UdmODBCConnect(UDM_SQL * db)531 UdmODBCConnect(UDM_SQL *db)
532 {
533 char DSN[512]="";
534 const char* DBUser= UdmVarListFindStr(&db->Vars,"DBUser",NULL);
535 const char* DBPass= UdmVarListFindStr(&db->Vars,"DBPass",NULL);
536 const char* DBName= UdmVarListFindStr(&db->Vars, "DBName", "mnogosearch");
537 #if HAVE_SOLID || HAVE_SAPDB
538 const char* DBHost= UdmVarListFindStr(&db->Vars, "DBHost", "localhost");
539 int DBPort= UdmVarListFindInt(&db->Vars, "DBPort", 0);
540 #endif
541 UDM_SQLRES SQLRes;
542 UDM_ODBC_CONN *sdb= (UDM_ODBC_CONN*) UdmMalloc(sizeof(UDM_ODBC_CONN));
543 db->specific= (void*) sdb;
544
545 sdb->hDbc= SQL_NULL_HDBC;
546 sdb->hEnv= SQL_NULL_HENV;
547 sdb->stmt.hstmt= SQL_NULL_HSTMT;
548
549 #if (HAVE_SOLID)
550 udm_snprintf(DSN, sizeof(DSN)-1, "tcp %s %d", DBHost, DBPort?DBPort:1313);
551 #elif (HAVE_SAPDB)
552 udm_snprintf(DSN, sizeof(DSN)-1, "%s:%s", DBHost, DBName ? DBName : "");
553 #else
554 strncpy(DSN, DBName ? DBName : "", sizeof(DSN)-1);
555 #endif
556
557 #ifdef HAVE_GCC_PRAGMA_PUSH
558 #pragma GCC diagnostic push
559 #pragma GCC diagnostic ignored "-Wcast-qual"
560 #endif
561 if (SQL_SUCCESS != (db->errcode= SQLAllocEnv(&sdb->hEnv)))
562 return UDM_ERROR;
563 if (SQL_SUCCESS != (db->errcode= SQLAllocConnect(sdb->hEnv, &sdb->hDbc)))
564 return UDM_ERROR;
565 if (SQL_SUCCESS != (db->errcode= SQLSetConnectOption(sdb->hDbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_ON)))
566 return UDM_ERROR;
567 if (!SQL_OK(db->errcode = SQLConnect(sdb->hDbc,
568 (SQLCHAR *) DSN, SQL_NTS,
569 (SQLCHAR *) DBUser, SQL_NTS,
570 (SQLCHAR*) DBPass, SQL_NTS)))
571 return UDM_ERROR;
572 #ifdef HAVE_GCC_PRAGMA_PUSH
573 #pragma GCC diagnostic pop
574 #endif
575
576 UdmSetErrorCode(db, 0);
577 db->connected= UDM_TRUE;
578
579 /*
580 if (db->DBType == UDM_DB_DB2)
581 {
582 1253 = SQL_ATTR_LONGDATA_COMPAT
583 int rc= SQLSetConnectAttr(sdb->hDbc, 1253, (SQLPOINTER) 1, 0);
584 }
585 */
586 if (db->DBType == UDM_DB_ORACLE8)
587 execDB(db, &SQLRes, "ALTER SESSION SET NLS_NUMERIC_CHARACTERS='. '");
588
589 /*
590 MySQL ODBC Driver does not allow to do SET NAMES
591 */
592 if (/*db->DBType == UDM_DB_MYSQL || */db->DBType == UDM_DB_PGSQL)
593 {
594 const char* setnames= UdmVarListFindStr(&db->Vars, "setnames", NULL);
595 if (setnames)
596 {
597 char qbuf[64];
598 udm_snprintf(qbuf, sizeof(qbuf), "SET NAMES '%s'", setnames);
599 if (UDM_OK != execDB(db, &SQLRes, qbuf))
600 return UDM_ERROR;;
601 }
602 }
603
604 UdmODBCGetVersion(db);
605 return UDM_OK;
606 }
607
608
609 static udm_rc_t
UdmODBCClose(UDM_SQL * db)610 UdmODBCClose(UDM_SQL *db)
611 {
612 if (db->connected)
613 {
614 UDM_ODBC_CONN *sdb= UdmOB(db);
615 db->errcode= SQLTransact(sdb->hEnv, sdb->hDbc, SQL_COMMIT);
616 if(SQL_SUCCESS != db->errcode)
617 goto ret;
618 db->errcode= SQLDisconnect(sdb->hDbc );
619 if(SQL_SUCCESS != db->errcode)
620 goto ret;
621 db->errcode= SQLFreeConnect(sdb->hDbc);
622 if(SQL_SUCCESS != db->errcode)
623 goto ret;
624 db->errcode= SQLFreeEnv(sdb->hEnv);
625 if(SQL_SUCCESS != db->errcode)
626 goto ret;
627 ret:
628 db->connected= UDM_FALSE;
629 UDM_FREE(db->specific);
630 }
631 return UDM_OK;
632 }
633
634
635 static udm_rc_t
UdmODBCExecDirect(UDM_SQL * db,UDM_SQLRES * res,const char * qbuf)636 UdmODBCExecDirect(UDM_SQL *db,UDM_SQLRES *res, const char *qbuf)
637 {
638 udm_rc_t rc;
639
640 if (res)
641 {
642 bzero((void*) res, sizeof(UDM_SQLRES));
643 res->db= db;
644 }
645
646 if (!db->connected)
647 {
648 UdmODBCConnect(db);
649 if (db->errcode)
650 {
651 if (!db->errstr[0])
652 UdmODBCDisplayError(db, SQL_NULL_HSTMT, "");
653 return UDM_ERROR;
654 }
655 else
656 {
657 db->connected= UDM_TRUE;
658 }
659 }
660
661 rc= execDB(db,res,qbuf);
662 if ((db->errcode))
663 {
664 if(strstr(db->errstr,"[SQLSTATE:23000]")) /* MS Access */
665 {
666 UdmSetErrorCode(db, 0);
667 rc= UDM_OK;
668 }
669 #if 0
670 /* Cannot use S1000: it's returned by unixODBC in many cases */
671 else if(strstr(db->errstr,"[SQLSTATE:S1000]"))
672 { /* Oracle 8i */
673 UdmSetErrorCode(db, 0);
674 rc= UDM_OK;
675 }
676 #endif
677 else if(strstr(db->errstr,"uplicat")) /* PgSQL,MySQL*/
678 {
679 UdmSetErrorCode(db, 0);
680 rc= UDM_OK;
681 }
682 else if(strstr(db->errstr,"nique")) /* Solid, Virtuoso */
683 {
684 UdmSetErrorCode(db, 0);
685 rc= UDM_OK;
686 }
687 else if(strstr(db->errstr,"UNIQUE")) /* Mimer */
688 {
689 UdmSetErrorCode(db, 0);
690 rc= UDM_OK;
691 }
692 else if (strstr(db->errstr, "PRIMARY KEY")) /* MonetDB */
693 {
694 UdmSetErrorCode(db, 0);
695 rc= UDM_OK;
696 }
697 else if (db->DBType == UDM_DB_MONETDB &&
698 strstr(db->errstr, "General error"))
699 {
700 /*
701 MonetDB returns "General error" in case of primary key
702 violation in some cases.
703 */
704 fprintf(stderr, "MONETDB FIXME: %s\n%s\n", db->errstr, qbuf);
705 UdmSetErrorCode(db, 0);
706 rc= UDM_OK;
707 }
708 else
709 {
710 UdmSetErrorCode(db, 1);
711 }
712 }
713 return rc;
714 }
715
716
717 static udm_rc_t
UdmODBCBegin(UDM_SQL * db)718 UdmODBCBegin(UDM_SQL *db)
719 {
720 UDM_ODBC_CONN *sdb;
721 if (!db->connected)
722 {
723 UdmODBCConnect(db);
724 if(db->errcode)
725 {
726 UdmODBCDisplayError(db, SQL_NULL_HSTMT, "");
727 return UDM_ERROR;
728 }
729 else
730 {
731 db->connected= UDM_TRUE;
732 }
733 }
734 sdb= UdmOB(db);
735 db->errcode= SQLSetConnectOption(sdb->hDbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF);
736 if (SQL_SUCCESS != db->errcode)
737 {
738 UdmODBCDisplayError(db, SQL_NULL_HSTMT, "SQLSetConnectOption(SQL_AUTOCOMMIT_OFF) failed");
739 return UDM_ERROR;
740 }
741 return UDM_OK;
742 }
743
744
745 static udm_rc_t
UdmODBCCommit(UDM_SQL * db)746 UdmODBCCommit(UDM_SQL *db)
747 {
748 UDM_ODBC_CONN *sdb= UdmOB(db);
749 udm_rc_t rc;
750 if (UDM_OK != (rc= UdmODBCExecDirect(db,NULL,"COMMIT")))
751 return rc;
752 if (!SQL_OK(db->errcode= SQLSetConnectOption(sdb->hDbc,
753 SQL_AUTOCOMMIT,
754 SQL_AUTOCOMMIT_ON)))
755 {
756 UdmODBCDisplayError(db, SQL_NULL_HSTMT, "SQLSetConnectOption(SQL_AUTOCOMMIT_ON) failed");
757 return UDM_ERROR;
758 }
759 return UDM_OK;
760 }
761
762
763 static udm_rc_t
UdmODBCPrepare(UDM_SQL * db,const char * query)764 UdmODBCPrepare(UDM_SQL *db, const char *query)
765 {
766 UDM_ODBC_CONN *sdb= UdmOB(db);
767 UDM_ODBC_STMT *stmt= &sdb->stmt;
768 SQLRETURN dbrc;
769 UdmSetErrorCode(db, 0);
770 db->errstr[0] = 0;
771 dbrc= SQLAllocStmt(sdb->hDbc, &stmt->hstmt);
772
773 if (!SQL_OK(dbrc))
774 {
775 UdmSetErrorCode(db, dbrc);
776 UdmODBCDisplayError(db, SQL_NULL_HSTMT, "SQLAllocStmt: ");
777 return(UDM_ERROR);
778 }
779 #ifdef HAVE_GCC_PRAGMA_PUSH
780 #pragma GCC diagnostic push
781 #pragma GCC diagnostic ignored "-Wcast-qual"
782 #endif
783 dbrc= SQLPrepare(stmt->hstmt, (SQLCHAR*) query, SQL_NTS);
784 #ifdef HAVE_GCC_PRAGMA_PUSH
785 #pragma GCC diagnostic pop
786 #endif
787 if (!SQL_OK(dbrc))
788 {
789 UdmSetErrorCode(db, dbrc);
790 UdmODBCDisplayError(db, stmt->hstmt, "SQLPrepare: ");
791 return(UDM_ERROR);
792 }
793 return(UDM_OK);
794 }
795
796
797 static int
UdmSQLType2ODBCType(udm_sqltype_t udm_type)798 UdmSQLType2ODBCType(udm_sqltype_t udm_type)
799 {
800 switch (udm_type)
801 {
802 case UDM_SQLTYPE_LONGVARBINARY: return SQL_LONGVARBINARY;
803 case UDM_SQLTYPE_LONGVARCHAR : return SQL_LONGVARCHAR;
804 case UDM_SQLTYPE_VARCHAR : return SQL_VARCHAR;
805 case UDM_SQLTYPE_INT32 : return SQL_INTEGER;
806 default : break;
807 }
808 return SQL_UNKNOWN_TYPE;
809 }
810
811
812 static int
UdmSQLParamLen2ODBC(int udm_len)813 UdmSQLParamLen2ODBC(int udm_len)
814 {
815 switch (udm_len)
816 {
817 case UDM_SQL_NULL_DATA : return SQL_NULL_DATA;
818 case UDM_SQL_DATA_AT_EXEC : return SQL_DATA_AT_EXEC;
819 case UDM_SQL_NTS : return SQL_NTS;
820 }
821 return udm_len;
822 }
823
824
825 static udm_rc_t
UdmODBCBind(UDM_SQL * db,int position,const void * data,int size,udm_sqltype_t type)826 UdmODBCBind(UDM_SQL *db, int position, const void *data, int size, udm_sqltype_t type)
827 {
828 UDM_ODBC_CONN *sdb= UdmOB(db);
829 UDM_ODBC_STMT *stmt= &sdb->stmt;
830 SQLRETURN dbrc;
831 int ctype= SQL_C_DEFAULT;
832 int odbctype= UdmSQLType2ODBCType(type);
833 stmt->BindBuf[position].size= UdmSQLParamLen2ODBC(size);
834 #ifdef HAVE_GCC_PRAGMA_PUSH
835 #pragma GCC diagnostic push
836 #pragma GCC diagnostic ignored "-Wcast-qual"
837 #endif
838 stmt->BindBuf[position].data= (SQLPOINTER) data;
839 #ifdef HAVE_GCC_PRAGMA_PUSH
840 #pragma GCC diagnostic pop
841 #endif
842 UdmSetErrorCode(db, 0);
843 db->errstr[0] = 0;
844
845 #if defined(__x86_64__)
846 /* Virtuoso ODBC driver wants a 64-bit number when binding SQL_INTEGER */
847 if (type == UDM_SQLTYPE_INT32 && db->DBType == UDM_DB_VIRT)
848 {
849 stmt->BindBuf[position].int8buf= *((const int *) data);
850 stmt->BindBuf[position].data= (SQLPOINTER) &stmt->BindBuf[position].int8buf;
851 data= (SQLPOINTER) &stmt->BindBuf[position].int8buf;
852 }
853 #endif
854
855 #ifdef HAVE_GCC_PRAGMA_PUSH
856 #pragma GCC diagnostic push
857 #pragma GCC diagnostic ignored "-Wcast-qual"
858 #endif
859 dbrc= SQLBindParameter(stmt->hstmt, position, SQL_PARAM_INPUT, ctype,
860 odbctype, 0 /* Column size */, 0 /* Decimal digits */,
861 /*&BindBuf[position]*/ (char*) data, size < 0 ? 0 : size,
862 &stmt->BindBuf[position].size);
863 #ifdef HAVE_GCC_PRAGMA_PUSH
864 #pragma GCC diagnostic pop
865 #endif
866
867 #ifdef WIN32
868 /* s_size= SQL_DATA_AT_EXEC; */
869 #endif
870
871 if (!SQL_OK(dbrc))
872 {
873 UdmSetErrorCode(db, dbrc);
874 UdmODBCDisplayError(db, stmt->hstmt, "UdmSQLBindParameter: ");
875 return(UDM_ERROR);
876 }
877 return(UDM_OK);
878 }
879
880
881 static udm_rc_t
UdmODBCStmtFree(UDM_SQL * db)882 UdmODBCStmtFree(UDM_SQL *db)
883 {
884 UDM_ODBC_CONN *sdb= UdmOB(db);
885 UDM_ODBC_STMT *stmt= &sdb->stmt;
886 int rc= SQLFreeStmt(stmt->hstmt, SQL_DROP);
887 return SQL_OK(rc) ? UDM_OK : UDM_ERROR;
888 }
889
890
891 static udm_rc_t
UdmODBCExec(UDM_SQL * db)892 UdmODBCExec(UDM_SQL *db)
893 {
894 UDM_ODBC_CONN *sdb= UdmOB(db);
895 UDM_ODBC_STMT *stmt= &sdb->stmt;
896 SQLRETURN dbrc;
897 int put_data= 0;
898 UDM_ODBC_BINDBUF *Buf;
899
900 UdmSetErrorCode(db, 0);
901 db->errstr[0]= 0;
902 dbrc= SQLExecute(stmt->hstmt);
903
904 #ifdef WIN32
905 put_data= 1;
906 #endif
907
908 if (!put_data || dbrc != SQL_NEED_DATA)
909 goto check_err;
910
911 for (dbrc= SQLParamData(stmt->hstmt, (SQLPOINTER*) &Buf);
912 dbrc == SQL_NEED_DATA;
913 dbrc= SQLParamData(stmt->hstmt, (SQLPOINTER*) &Buf))
914 {
915 if (SQL_ERROR == (dbrc= SQLPutData(stmt->hstmt, Buf->data, Buf->size)))
916 goto check_err;
917 }
918
919 check_err:
920
921 if (!SQL_OK(dbrc))
922 {
923 UdmODBCDisplayError(db, stmt->hstmt, "UdmODBCExec: ");
924 UdmSetErrorCode(db, dbrc);
925 return UDM_ERROR;
926 }
927 return UDM_OK;
928 }
929
930
931 static udm_rc_t
UdmODBCRenameTable(UDM_SQL * db,const char * from,const char * to)932 UdmODBCRenameTable(UDM_SQL *db, const char *from, const char *to)
933 {
934 if (db->DBType == UDM_DB_MYSQL ||
935 db->DBType == UDM_DB_PGSQL ||
936 db->DBType == UDM_DB_ORACLE8)
937 {
938 char buf[256];
939 udm_snprintf(buf, sizeof(buf), "ALTER TABLE %s RENAME TO %s", from, to);
940 return UdmSQLExecDirect(db, NULL, buf);
941 }
942 else if (db->DBType == UDM_DB_DB2)
943 {
944 char buf[256];
945 udm_snprintf(buf, sizeof(buf), "RENAME TABLE %s TO %s", from, to);
946 return UdmSQLExecDirect(db, NULL, buf);
947 }
948 else if (db->DBType == UDM_DB_MSSQL)
949 {
950 char buf[256];
951 udm_snprintf(buf, sizeof(buf), "sp_rename %s, %s", from, to);
952 return UdmSQLExecDirect(db, NULL, buf);
953 }
954 sprintf(db->errstr, "This database type does not support RENAME TABLE");
955 return UDM_ERROR;
956 }
957
958
959 /*
960 Copy table structure from another table, without indexes.
961 */
962 static udm_rc_t
UdmODBCCopyStructure(UDM_SQL * db,const char * from,const char * to)963 UdmODBCCopyStructure(UDM_SQL *db, const char *from, const char *to)
964 {
965 char buf[256];
966 switch (db->DBType)
967 {
968 case UDM_DB_MYSQL:
969 udm_snprintf(buf, sizeof(buf),
970 "CREATE TABLE %s MAX_ROWS=300000000 AVG_ROW_LENGTH=512 "
971 "SELECT * FROM %s LIMIT 0", to, from);
972 return UdmSQLExecDirect(db, NULL, buf);
973
974 case UDM_DB_PGSQL:
975 udm_snprintf(buf, sizeof(buf), "CREATE TABLE %s (LIKE %s)", to, from);
976 return UdmSQLExecDirect(db, NULL, buf);
977
978 case UDM_DB_ORACLE8:
979 udm_snprintf(buf, sizeof(buf),
980 "CREATE TABLE %s AS SELECT * FROM %s WHERE 1=0", to, from);
981 return UdmSQLExecDirect(db, NULL, buf);
982
983 case UDM_DB_DB2:
984 udm_snprintf(buf, sizeof(buf), "CREATE TABLE %s LIKE %s", to, from);
985 return UdmSQLExecDirect(db, NULL, buf);
986
987 case UDM_DB_MSSQL:
988 udm_snprintf(buf, sizeof(buf), "SELECT TOP 0 * INTO %s FROM %s", to, from);
989 return UdmSQLExecDirect(db, NULL, buf);
990 default: break;
991 }
992 sprintf(db->errstr, "This database type does not support"
993 "<CREATE TABLE name LIKE> or <CREATE TABLE name AS SELECT>");
994 return UDM_ERROR;
995 }
996
997
998 static udm_rc_t
UdmODBCLockOrBegin(UDM_SQL * db,const char * param)999 UdmODBCLockOrBegin(UDM_SQL *db, const char *param)
1000 {
1001 if (db->DBType == UDM_DB_MYSQL)
1002 {
1003 char buf[128];
1004 udm_snprintf(buf, sizeof(buf), "LOCK TABLE %s", param);
1005 return UdmSQLExecDirect(db, NULL, buf);
1006 }
1007 return UdmSQLBegin(db);
1008 }
1009
1010
1011 static udm_rc_t
UdmODBCUnlockOrCommit(UDM_SQL * db)1012 UdmODBCUnlockOrCommit(UDM_SQL *db)
1013 {
1014 if (db->DBType == UDM_DB_MYSQL)
1015 return UdmSQLExecDirect(db, NULL, "UNLOCK TABLES");
1016 else
1017 return UdmSQLCommit(db);
1018 }
1019
1020
1021 const UDM_SQLDB_HANDLER udm_sqldb_odbc_handler =
1022 {
1023 UdmSQLEscStrGeneric,
1024 UdmODBCExecDirect,
1025 UdmODBCConnect,
1026 UdmODBCClose,
1027 UdmODBCBegin,
1028 UdmODBCCommit,
1029 UdmODBCPrepare,
1030 UdmODBCBind,
1031 UdmODBCExec,
1032 UdmODBCStmtFree,
1033 UdmSQLFetchRowSimple,
1034 UdmSQLStoreResultSimple,
1035 UdmSQLFreeResultSimple,
1036 UdmODBCExecDirect,
1037 UdmODBCRenameTable,
1038 UdmODBCCopyStructure,
1039 UdmODBCLockOrBegin,
1040 UdmODBCUnlockOrCommit,
1041 };
1042
1043 #endif /* HAVE_ODBC */
1044
1045