schema-related information (like schema version, DB syntax, etc.) moved from "config...
[public/netxms.git] / src / server / libnxsrv / db.cpp
1 /*
2 ** NetXMS - Network Management System
3 ** Copyright (C) 2003, 2004, 2005, 2006, 2007, 2008 Victor Kirhenshtein
4 **
5 ** This program is free software; you can redistribute it and/or modify
6 ** it under the terms of the GNU General Public License as published by
7 ** the Free Software Foundation; either version 2 of the License, or
8 ** (at your option) any later version.
9 **
10 ** This program is distributed in the hope that it will be useful,
11 ** but WITHOUT ANY WARRANTY; without even the implied warranty of
12 ** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 ** GNU General Public License for more details.
14 **
15 ** You should have received a copy of the GNU General Public License
16 ** along with this program; if not, write to the Free Software
17 ** Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
18 **
19 ** File: db.cpp
20 **
21 **/
22
23 #include "libnxsrv.h"
24
25
26 //
27 // Global variables
28 //
29
30 TCHAR LIBNXSRV_EXPORTABLE g_szDbDriver[MAX_PATH] = _T("");
31 TCHAR LIBNXSRV_EXPORTABLE g_szDbDrvParams[MAX_PATH] = _T("");
32 TCHAR LIBNXSRV_EXPORTABLE g_szDbServer[MAX_PATH] = _T("127.0.0.1");
33 TCHAR LIBNXSRV_EXPORTABLE g_szDbLogin[MAX_DB_LOGIN] = _T("netxms");
34 TCHAR LIBNXSRV_EXPORTABLE g_szDbPassword[MAX_DB_PASSWORD] = _T("");
35 TCHAR LIBNXSRV_EXPORTABLE g_szDbName[MAX_DB_NAME] = _T("netxms_db");
36
37
38 //
39 // Static data
40 //
41
42 static BOOL m_bnxlog_write = FALSE;
43 static BOOL m_bLogSQLErrors = FALSE;
44 static BOOL m_bDumpSQL = FALSE;
45 static int m_nReconnect = 0;
46 static MUTEX m_mutexReconnect = INVALID_MUTEX_HANDLE;
47 static HMODULE m_hDriver = NULL;
48 static DB_CONNECTION (* m_fpDrvConnect)(const char *, const char *, const char *, const char *) = NULL;
49 static void (* m_fpDrvDisconnect)(DB_CONNECTION) = NULL;
50 static DWORD (* m_fpDrvQuery)(DB_CONNECTION, WCHAR *, TCHAR *) = NULL;
51 static DB_RESULT (* m_fpDrvSelect)(DB_CONNECTION, WCHAR *, DWORD *, TCHAR *) = NULL;
52 static DB_ASYNC_RESULT (* m_fpDrvAsyncSelect)(DB_CONNECTION, WCHAR *, DWORD *, TCHAR *) = NULL;
53 static BOOL (* m_fpDrvFetch)(DB_ASYNC_RESULT) = NULL;
54 static LONG (* m_fpDrvGetFieldLength)(DB_RESULT, int, int) = NULL;
55 static WCHAR* (* m_fpDrvGetField)(DB_RESULT, int, int, WCHAR *, int) = NULL;
56 static WCHAR* (* m_fpDrvGetFieldAsync)(DB_ASYNC_RESULT, int, WCHAR *, int) = NULL;
57 static int (* m_fpDrvGetNumRows)(DB_RESULT) = NULL;
58 static void (* m_fpDrvFreeResult)(DB_RESULT) = NULL;
59 static void (* m_fpDrvFreeAsyncResult)(DB_ASYNC_RESULT) = NULL;
60 static DWORD (* m_fpDrvBegin)(DB_CONNECTION) = NULL;
61 static DWORD (* m_fpDrvCommit)(DB_CONNECTION) = NULL;
62 static DWORD (* m_fpDrvRollback)(DB_CONNECTION) = NULL;
63 static void (* m_fpDrvUnload)(void) = NULL;
64 static void (* m_fpEventHandler)(DWORD, TCHAR *);
65 static int (* m_fpDrvGetColumnCount)(DB_RESULT);
66 static const char* (* m_fpDrvGetColumnName)(DB_RESULT, int);
67 static int (* m_fpDrvGetColumnCountAsync)(DB_ASYNC_RESULT);
68 static const char* (* m_fpDrvGetColumnNameAsync)(DB_ASYNC_RESULT, int);
69
70
71 //
72 // Get symbol address and log errors
73 //
74
75 static void *DLGetSymbolAddrEx(HMODULE hModule, const TCHAR *pszSymbol)
76 {
77 void *pFunc;
78 char szErrorText[256];
79
80 pFunc = DLGetSymbolAddr(hModule, pszSymbol, szErrorText);
81 if ((pFunc == NULL) && m_bnxlog_write)
82 nxlog_write(MSG_DLSYM_FAILED, EVENTLOG_WARNING_TYPE, "ss", pszSymbol, szErrorText);
83 return pFunc;
84 }
85
86
87 //
88 // Load and initialize database driver
89 //
90
91 BOOL LIBNXSRV_EXPORTABLE DBInit(BOOL bnxlog_write, BOOL bLogErrors, BOOL bDumpSQL,
92 void (* fpEventHandler)(DWORD, TCHAR *))
93 {
94 BOOL (* fpDrvInit)(char *);
95 DWORD *pdwAPIVersion;
96 char szErrorText[256];
97 static DWORD dwVersionZero = 0;
98
99 m_bnxlog_write = bnxlog_write;
100 m_bLogSQLErrors = bLogErrors && bnxlog_write;
101 m_bDumpSQL = bDumpSQL;
102 m_fpEventHandler = fpEventHandler;
103 m_nReconnect = 0;
104 m_mutexReconnect = MutexCreate();
105
106 // Load driver's module
107 m_hDriver = DLOpen(g_szDbDriver, szErrorText);
108 if (m_hDriver == NULL)
109 {
110 if (m_bnxlog_write)
111 nxlog_write(MSG_DLOPEN_FAILED, EVENTLOG_ERROR_TYPE, "ss", g_szDbDriver, szErrorText);
112 return FALSE;
113 }
114
115 // Check API version supported by driver
116 pdwAPIVersion = (DWORD *)DLGetSymbolAddr(m_hDriver, "drvAPIVersion", NULL);
117 if (pdwAPIVersion == NULL)
118 pdwAPIVersion = &dwVersionZero;
119 if (*pdwAPIVersion != DBDRV_API_VERSION)
120 {
121 if (m_bnxlog_write)
122 nxlog_write(MSG_DBDRV_API_VERSION_MISMATCH, EVENTLOG_ERROR_TYPE, "sdd",
123 g_szDbDriver, DBDRV_API_VERSION, *pdwAPIVersion);
124 DLClose(m_hDriver);
125 m_hDriver = NULL;
126 return FALSE;
127 }
128
129 // Import symbols
130 fpDrvInit = (BOOL (*)(char *))DLGetSymbolAddrEx(m_hDriver, "DrvInit");
131 m_fpDrvConnect = (DB_CONNECTION (*)(const char *, const char *, const char *, const char *))DLGetSymbolAddrEx(m_hDriver, "DrvConnect");
132 m_fpDrvDisconnect = (void (*)(DB_CONNECTION))DLGetSymbolAddrEx(m_hDriver, "DrvDisconnect");
133 m_fpDrvQuery = (DWORD (*)(DB_CONNECTION, WCHAR *, TCHAR *))DLGetSymbolAddrEx(m_hDriver, "DrvQuery");
134 m_fpDrvSelect = (DB_RESULT (*)(DB_CONNECTION, WCHAR *, DWORD *, TCHAR *))DLGetSymbolAddrEx(m_hDriver, "DrvSelect");
135 m_fpDrvAsyncSelect = (DB_ASYNC_RESULT (*)(DB_CONNECTION, WCHAR *, DWORD *, TCHAR *))DLGetSymbolAddrEx(m_hDriver, "DrvAsyncSelect");
136 m_fpDrvFetch = (BOOL (*)(DB_ASYNC_RESULT))DLGetSymbolAddrEx(m_hDriver, "DrvFetch");
137 m_fpDrvGetFieldLength = (LONG (*)(DB_RESULT, int, int))DLGetSymbolAddrEx(m_hDriver, "DrvGetFieldLength");
138 m_fpDrvGetField = (WCHAR* (*)(DB_RESULT, int, int, WCHAR *, int))DLGetSymbolAddrEx(m_hDriver, "DrvGetField");
139 m_fpDrvGetFieldAsync = (WCHAR* (*)(DB_ASYNC_RESULT, int, WCHAR *, int))DLGetSymbolAddrEx(m_hDriver, "DrvGetFieldAsync");
140 m_fpDrvGetNumRows = (int (*)(DB_RESULT))DLGetSymbolAddrEx(m_hDriver, "DrvGetNumRows");
141 m_fpDrvGetColumnCount = (int (*)(DB_RESULT))DLGetSymbolAddrEx(m_hDriver, "DrvGetColumnCount");
142 m_fpDrvGetColumnName = (const char* (*)(DB_RESULT, int))DLGetSymbolAddrEx(m_hDriver, "DrvGetColumnName");
143 m_fpDrvGetColumnCountAsync = (int (*)(DB_ASYNC_RESULT))DLGetSymbolAddrEx(m_hDriver, "DrvGetColumnCountAsync");
144 m_fpDrvGetColumnNameAsync = (const char* (*)(DB_ASYNC_RESULT, int))DLGetSymbolAddrEx(m_hDriver, "DrvGetColumnNameAsync");
145 m_fpDrvFreeResult = (void (*)(DB_RESULT))DLGetSymbolAddrEx(m_hDriver, "DrvFreeResult");
146 m_fpDrvFreeAsyncResult = (void (*)(DB_ASYNC_RESULT))DLGetSymbolAddrEx(m_hDriver, "DrvFreeAsyncResult");
147 m_fpDrvBegin = (DWORD (*)(DB_CONNECTION))DLGetSymbolAddrEx(m_hDriver, "DrvBegin");
148 m_fpDrvCommit = (DWORD (*)(DB_CONNECTION))DLGetSymbolAddrEx(m_hDriver, "DrvCommit");
149 m_fpDrvRollback = (DWORD (*)(DB_CONNECTION))DLGetSymbolAddrEx(m_hDriver, "DrvRollback");
150 m_fpDrvUnload = (void (*)(void))DLGetSymbolAddrEx(m_hDriver, "DrvUnload");
151 if ((fpDrvInit == NULL) || (m_fpDrvConnect == NULL) || (m_fpDrvDisconnect == NULL) ||
152 (m_fpDrvQuery == NULL) || (m_fpDrvSelect == NULL) || (m_fpDrvGetField == NULL) ||
153 (m_fpDrvGetNumRows == NULL) || (m_fpDrvFreeResult == NULL) ||
154 (m_fpDrvUnload == NULL) || (m_fpDrvAsyncSelect == NULL) || (m_fpDrvFetch == NULL) ||
155 (m_fpDrvFreeAsyncResult == NULL) || (m_fpDrvGetFieldAsync == NULL) ||
156 (m_fpDrvBegin == NULL) || (m_fpDrvCommit == NULL) || (m_fpDrvRollback == NULL) ||
157 (m_fpDrvGetColumnCount == NULL) || (m_fpDrvGetColumnName == NULL) ||
158 (m_fpDrvGetColumnCountAsync == NULL) || (m_fpDrvGetColumnNameAsync == NULL) ||
159 (m_fpDrvGetFieldLength == NULL))
160 {
161 if (m_bnxlog_write)
162 nxlog_write(MSG_DBDRV_NO_ENTRY_POINTS, EVENTLOG_ERROR_TYPE, "s", g_szDbDriver);
163 DLClose(m_hDriver);
164 m_hDriver = NULL;
165 return FALSE;
166 }
167
168 // Initialize driver
169 if (!fpDrvInit(g_szDbDrvParams))
170 {
171 if (m_bnxlog_write)
172 nxlog_write(MSG_DBDRV_INIT_FAILED, EVENTLOG_ERROR_TYPE, "s", g_szDbDriver);
173 DLClose(m_hDriver);
174 m_hDriver = NULL;
175 return FALSE;
176 }
177
178 // Success
179 if (m_bnxlog_write)
180 nxlog_write(MSG_DBDRV_LOADED, EVENTLOG_INFORMATION_TYPE, "s", g_szDbDriver);
181 return TRUE;
182 }
183
184
185 //
186 // Notify driver of unload
187 //
188
189 void LIBNXSRV_EXPORTABLE DBUnloadDriver(void)
190 {
191 m_fpDrvUnload();
192 DLClose(m_hDriver);
193 MutexDestroy(m_mutexReconnect);
194 }
195
196
197 //
198 // Connect to database
199 //
200
201 DB_HANDLE LIBNXSRV_EXPORTABLE DBConnect(void)
202 {
203 return DBConnectEx(g_szDbServer, g_szDbName, g_szDbLogin, g_szDbPassword);
204 }
205
206
207 //
208 // Connect to database using provided parameters
209 //
210
211 DB_HANDLE LIBNXSRV_EXPORTABLE DBConnectEx(const TCHAR *pszServer, const TCHAR *pszDBName,
212 const TCHAR *pszLogin, const TCHAR *pszPassword)
213 {
214 DB_CONNECTION hDrvConn;
215 DB_HANDLE hConn = NULL;
216
217 hDrvConn = m_fpDrvConnect(pszServer, pszLogin, pszPassword, pszDBName);
218 if (hDrvConn != NULL)
219 {
220 hConn = (DB_HANDLE)malloc(sizeof(struct db_handle_t));
221 if (hConn != NULL)
222 {
223 hConn->hConn = hDrvConn;
224 hConn->mutexTransLock = MutexCreateRecursive();
225 hConn->nTransactionLevel = 0;
226 hConn->pszDBName = (pszDBName == NULL) ? NULL : _tcsdup(pszDBName);
227 hConn->pszLogin = (pszLogin == NULL) ? NULL : _tcsdup(pszLogin);
228 hConn->pszPassword = (pszPassword == NULL) ? NULL : _tcsdup(pszPassword);
229 hConn->pszServer = (pszServer == NULL) ? NULL : _tcsdup(pszServer);
230 DbgPrintf(4, _T("New DB connection opened: handle=%p"), hConn);
231 }
232 else
233 {
234 m_fpDrvDisconnect(hDrvConn);
235 }
236 }
237 return hConn;
238 }
239
240
241 //
242 // Disconnect from database
243 //
244
245 void LIBNXSRV_EXPORTABLE DBDisconnect(DB_HANDLE hConn)
246 {
247 if (hConn == NULL)
248 return;
249
250 DbgPrintf(4, _T("DB connection %p closed"), hConn);
251
252 m_fpDrvDisconnect(hConn->hConn);
253 MutexDestroy(hConn->mutexTransLock);
254 safe_free(hConn->pszDBName);
255 safe_free(hConn->pszLogin);
256 safe_free(hConn->pszPassword);
257 safe_free(hConn->pszServer);
258 free(hConn);
259 }
260
261
262 //
263 // Reconnect to database
264 //
265
266 static void DBReconnect(DB_HANDLE hConn)
267 {
268 int nCount;
269
270 m_fpDrvDisconnect(hConn->hConn);
271 for(nCount = 0; ; nCount++)
272 {
273 hConn->hConn = m_fpDrvConnect(hConn->pszServer, hConn->pszLogin,
274 hConn->pszPassword, hConn->pszDBName);
275 if (hConn->hConn != NULL)
276 break;
277 if (nCount == 0)
278 {
279 MutexLock(m_mutexReconnect, INFINITE);
280 if ((m_nReconnect == 0) && (m_fpEventHandler != NULL))
281 m_fpEventHandler(DBEVENT_CONNECTION_LOST, NULL);
282 m_nReconnect++;
283 MutexUnlock(m_mutexReconnect);
284 }
285 ThreadSleepMs(1000);
286 }
287 if (nCount > 0)
288 {
289 MutexLock(m_mutexReconnect, INFINITE);
290 m_nReconnect--;
291 if ((m_nReconnect == 0) && (m_fpEventHandler != NULL))
292 m_fpEventHandler(DBEVENT_CONNECTION_RESTORED, NULL);
293 MutexUnlock(m_mutexReconnect);
294 }
295 }
296
297
298 //
299 // Perform a non-SELECT SQL query
300 //
301
302 BOOL LIBNXSRV_EXPORTABLE DBQueryEx(DB_HANDLE hConn, const TCHAR *szQuery, TCHAR *errorText)
303 {
304 DWORD dwResult;
305 INT64 ms;
306 #ifdef UNICODE
307 #define pwszQuery szQuery
308 #else
309 WCHAR *pwszQuery = WideStringFromMBString(szQuery);
310 #endif
311
312 MutexLock(hConn->mutexTransLock, INFINITE);
313 if (m_bDumpSQL)
314 ms = GetCurrentTimeMs();
315
316 dwResult = m_fpDrvQuery(hConn->hConn, pwszQuery, errorText);
317 if (dwResult == DBERR_CONNECTION_LOST)
318 {
319 DBReconnect(hConn);
320 dwResult = m_fpDrvQuery(hConn->hConn, pwszQuery, errorText);
321 }
322
323 #ifndef UNICODE
324 free(pwszQuery);
325 #endif
326
327 if (m_bDumpSQL)
328 {
329 ms = GetCurrentTimeMs() - ms;
330 DbgPrintf(9, _T("%s sync query: \"%s\" [%d ms]"), (dwResult == DBERR_SUCCESS) ? _T("Successful") : _T("Failed"), szQuery, ms);
331 }
332
333 MutexUnlock(hConn->mutexTransLock);
334 if ((dwResult != DBERR_SUCCESS) && m_bLogSQLErrors)
335 nxlog_write(MSG_SQL_ERROR, EVENTLOG_ERROR_TYPE, "ss", szQuery, errorText);
336 return dwResult == DBERR_SUCCESS;
337 #undef pwszQuery
338 }
339
340 BOOL LIBNXSRV_EXPORTABLE DBQuery(DB_HANDLE hConn, const TCHAR *query)
341 {
342 TCHAR errorText[DBDRV_MAX_ERROR_TEXT];
343
344 return DBQueryEx(hConn, query, errorText);
345 }
346
347
348 //
349 // Perform SELECT query
350 //
351
352 DB_RESULT LIBNXSRV_EXPORTABLE DBSelectEx(DB_HANDLE hConn, const TCHAR *szQuery, TCHAR *errorText)
353 {
354 DB_RESULT hResult;
355 DWORD dwError;
356 INT64 ms;
357 #ifdef UNICODE
358 #define pwszQuery szQuery
359 #else
360 WCHAR *pwszQuery = WideStringFromMBString(szQuery);
361 #endif
362
363 MutexLock(hConn->mutexTransLock, INFINITE);
364 if (m_bDumpSQL)
365 ms = GetCurrentTimeMs();
366 hResult = m_fpDrvSelect(hConn->hConn, pwszQuery, &dwError, errorText);
367 if ((hResult == NULL) && (dwError == DBERR_CONNECTION_LOST))
368 {
369 DBReconnect(hConn);
370 hResult = m_fpDrvSelect(hConn->hConn, pwszQuery, &dwError, errorText);
371 }
372
373 #ifndef UNICODE
374 free(pwszQuery);
375 #endif
376
377 if (m_bDumpSQL)
378 {
379 ms = GetCurrentTimeMs() - ms;
380 DbgPrintf(9, _T("%s sync query: \"%s\" [%d ms]"), (hResult != NULL) ? _T("Successful") : _T("Failed"), szQuery, (DWORD)ms);
381 }
382 MutexUnlock(hConn->mutexTransLock);
383 if ((hResult == NULL) && m_bLogSQLErrors)
384 nxlog_write(MSG_SQL_ERROR, EVENTLOG_ERROR_TYPE, "ss", szQuery, errorText);
385 return hResult;
386 }
387
388 DB_RESULT LIBNXSRV_EXPORTABLE DBSelect(DB_HANDLE hConn, const TCHAR *query)
389 {
390 TCHAR errorText[DBDRV_MAX_ERROR_TEXT];
391
392 return DBSelectEx(hConn, query, errorText);
393 }
394
395
396 //
397 // Get number of columns
398 //
399
400 int LIBNXSRV_EXPORTABLE DBGetColumnCount(DB_RESULT hResult)
401 {
402 return m_fpDrvGetColumnCount(hResult);
403 }
404
405
406 //
407 // Get column name
408 //
409
410 BOOL LIBNXSRV_EXPORTABLE DBGetColumnName(DB_RESULT hResult, int column, TCHAR *buffer, int bufSize)
411 {
412 const char *name;
413
414 name = m_fpDrvGetColumnName(hResult, column);
415 if (name != NULL)
416 {
417 #ifdef UNICODE
418 MultiByteToWideChar(CP_ACP, MB_PRECOMPOSED, name, -1, buffer, bufSize);
419 buffer[bufSize - 1] = 0;
420 #else
421 nx_strncpy(buffer, name, bufSize);
422 #endif
423 }
424 return name != NULL;
425 }
426
427
428 //
429 // Get column count for async request
430 //
431
432 int LIBNXSRV_EXPORTABLE DBGetColumnCountAsync(DB_ASYNC_RESULT hResult)
433 {
434 return m_fpDrvGetColumnCountAsync(hResult);
435 }
436
437
438 //
439 // Get column name for async request
440 //
441
442 BOOL LIBNXSRV_EXPORTABLE DBGetColumnNameAsync(DB_ASYNC_RESULT hResult, int column, TCHAR *buffer, int bufSize)
443 {
444 const char *name;
445
446 name = m_fpDrvGetColumnNameAsync(hResult, column);
447 if (name != NULL)
448 {
449 #ifdef UNICODE
450 MultiByteToWideChar(CP_ACP, MB_PRECOMPOSED, name, -1, buffer, bufSize);
451 buffer[bufSize - 1] = 0;
452 #else
453 nx_strncpy(buffer, name, bufSize);
454 #endif
455 }
456 return name != NULL;
457 }
458
459
460 //
461 // Get field's value
462 //
463
464 TCHAR LIBNXSRV_EXPORTABLE *DBGetField(DB_RESULT hResult, int iRow, int iColumn,
465 TCHAR *pszBuffer, int nBufLen)
466 {
467 #ifdef UNICODE
468 if (pszBuffer != NULL)
469 {
470 return m_fpDrvGetField(hResult, iRow, iColumn, pszBuffer, nBufLen);
471 }
472 else
473 {
474 LONG nLen;
475 WCHAR *pszTemp;
476
477 nLen = m_fpDrvGetFieldLength(hResult, iRow, iColumn);
478 if (nLen == -1)
479 {
480 pszTemp = NULL;
481 }
482 else
483 {
484 nLen++;
485 pszBuffer = (WCHAR *)malloc(nLen * sizeof(WCHAR));
486 m_fpDrvGetField(hResult, iRow, iColumn, pszTemp, nLen);
487 }
488 return pszTemp;
489 }
490 #else
491 WCHAR *pwszData, *pwszBuffer;
492 char *pszRet;
493 int nLen;
494
495 if (pszBuffer != NULL)
496 {
497 pwszBuffer = (WCHAR *)malloc(nBufLen * sizeof(WCHAR));
498 pwszData = m_fpDrvGetField(hResult, iRow, iColumn, pwszBuffer, nBufLen);
499 if (pwszData != NULL)
500 {
501 WideCharToMultiByte(CP_ACP, WC_COMPOSITECHECK | WC_DEFAULTCHAR,
502 pwszData, -1, pszBuffer, nBufLen, NULL, NULL);
503 pszRet = pszBuffer;
504 }
505 else
506 {
507 pszRet = NULL;
508 }
509 free(pwszBuffer);
510 }
511 else
512 {
513 nLen = m_fpDrvGetFieldLength(hResult, iRow, iColumn);
514 if (nLen == -1)
515 {
516 pszRet = NULL;
517 }
518 else
519 {
520 nLen++;
521 pwszBuffer = (WCHAR *)malloc(nLen * sizeof(WCHAR));
522 pwszData = m_fpDrvGetField(hResult, iRow, iColumn, pwszBuffer, nLen);
523 if (pwszData != NULL)
524 {
525 nLen = (int)wcslen(pwszData) + 1;
526 pszRet = (char *)malloc(nLen);
527 WideCharToMultiByte(CP_ACP, WC_COMPOSITECHECK | WC_DEFAULTCHAR,
528 pwszData, -1, pszRet, nLen, NULL, NULL);
529 }
530 else
531 {
532 pszRet = NULL;
533 }
534 free(pwszBuffer);
535 }
536 }
537 return pszRet;
538 #endif
539 }
540
541
542 //
543 // Get field's value as unsigned long
544 //
545
546 DWORD LIBNXSRV_EXPORTABLE DBGetFieldULong(DB_RESULT hResult, int iRow, int iColumn)
547 {
548 LONG iVal;
549 DWORD dwVal;
550 TCHAR *pszVal, szBuffer[256];
551
552 pszVal = DBGetField(hResult, iRow, iColumn, szBuffer, 256);
553 if (pszVal == NULL)
554 return 0;
555 iVal = _tcstol(pszVal, NULL, 10);
556 memcpy(&dwVal, &iVal, sizeof(LONG)); // To prevent possible conversion
557 return dwVal;
558 }
559
560
561 //
562 // Get field's value as unsigned 64-bit int
563 //
564
565 QWORD LIBNXSRV_EXPORTABLE DBGetFieldUInt64(DB_RESULT hResult, int iRow, int iColumn)
566 {
567 INT64 iVal;
568 QWORD qwVal;
569 TCHAR *pszVal, szBuffer[256];
570
571 pszVal = DBGetField(hResult, iRow, iColumn, szBuffer, 256);
572 if (pszVal == NULL)
573 return 0;
574 iVal = _tcstoll(pszVal, NULL, 10);
575 memcpy(&qwVal, &iVal, sizeof(INT64)); // To prevent possible conversion
576 return qwVal;
577 }
578
579
580 //
581 // Get field's value as signed long
582 //
583
584 LONG LIBNXSRV_EXPORTABLE DBGetFieldLong(DB_RESULT hResult, int iRow, int iColumn)
585 {
586 TCHAR *pszVal, szBuffer[256];
587
588 pszVal = DBGetField(hResult, iRow, iColumn, szBuffer, 256);
589 return pszVal == NULL ? 0 : _tcstol(pszVal, NULL, 10);
590 }
591
592
593 //
594 // Get field's value as signed 64-bit int
595 //
596
597 INT64 LIBNXSRV_EXPORTABLE DBGetFieldInt64(DB_RESULT hResult, int iRow, int iColumn)
598 {
599 TCHAR *pszVal, szBuffer[256];
600
601 pszVal = DBGetField(hResult, iRow, iColumn, szBuffer, 256);
602 return pszVal == NULL ? 0 : _tcstoll(pszVal, NULL, 10);
603 }
604
605
606 //
607 // Get field's value as double
608 //
609
610 double LIBNXSRV_EXPORTABLE DBGetFieldDouble(DB_RESULT hResult, int iRow, int iColumn)
611 {
612 TCHAR *pszVal, szBuffer[256];
613
614 pszVal = DBGetField(hResult, iRow, iColumn, szBuffer, 256);
615 return pszVal == NULL ? 0 : _tcstod(pszVal, NULL);
616 }
617
618
619 //
620 // Get field's value as IP address
621 //
622
623 DWORD LIBNXSRV_EXPORTABLE DBGetFieldIPAddr(DB_RESULT hResult, int iRow, int iColumn)
624 {
625 TCHAR *pszVal, szBuffer[256];
626
627 pszVal = DBGetField(hResult, iRow, iColumn, szBuffer, 256);
628 return pszVal == NULL ? INADDR_NONE : ntohl(_t_inet_addr(pszVal));
629 }
630
631
632 //
633 // Get field's value as integer array from byte array encoded in hex
634 //
635
636 BOOL LIBNXSRV_EXPORTABLE DBGetFieldByteArray(DB_RESULT hResult, int iRow, int iColumn,
637 int *pnArray, int nSize, int nDefault)
638 {
639 char pbBytes[128];
640 BOOL bResult;
641 int i, nLen;
642 TCHAR *pszVal, szBuffer[256];
643
644 pszVal = DBGetField(hResult, iRow, iColumn, szBuffer, 256);
645 if (pszVal != NULL)
646 {
647 StrToBin(pszVal, (BYTE *)pbBytes, 128);
648 nLen = (int)strlen(pszVal) / 2;
649 for(i = 0; (i < nSize) && (i < nLen); i++)
650 pnArray[i] = pbBytes[i];
651 for(; i < nSize; i++)
652 pnArray[i] = nDefault;
653 bResult = TRUE;
654 }
655 else
656 {
657 for(i = 0; i < nSize; i++)
658 pnArray[i] = nDefault;
659 bResult = FALSE;
660 }
661 return bResult;
662 }
663
664
665 //
666 // Get field's value as GUID
667 //
668
669 BOOL LIBNXSRV_EXPORTABLE DBGetFieldGUID(DB_RESULT hResult, int iRow,
670 int iColumn, uuid_t guid)
671 {
672 TCHAR *pszVal, szBuffer[256];
673 BOOL bResult;
674
675 pszVal = DBGetField(hResult, iRow, iColumn, szBuffer, 256);
676 if (pszVal != NULL)
677 {
678 if (uuid_parse(pszVal, guid) == 0)
679 {
680 bResult = TRUE;
681 }
682 else
683 {
684 uuid_clear(guid);
685 bResult = FALSE;
686 }
687 }
688 else
689 {
690 uuid_clear(guid);
691 bResult = FALSE;
692 }
693 return bResult;
694 }
695
696
697 //
698 // Get number of rows in result
699 //
700
701 int LIBNXSRV_EXPORTABLE DBGetNumRows(DB_RESULT hResult)
702 {
703 if (hResult == NULL)
704 return 0;
705 return m_fpDrvGetNumRows(hResult);
706 }
707
708
709 //
710 // Free result
711 //
712
713 void LIBNXSRV_EXPORTABLE DBFreeResult(DB_RESULT hResult)
714 {
715 if (hResult != NULL)
716 m_fpDrvFreeResult(hResult);
717 }
718
719
720 //
721 // Asyncronous SELECT query
722 //
723
724 DB_ASYNC_RESULT LIBNXSRV_EXPORTABLE DBAsyncSelectEx(DB_HANDLE hConn, const TCHAR *szQuery, TCHAR *errorText)
725 {
726 DB_RESULT hResult;
727 DWORD dwError;
728 INT64 ms;
729 #ifdef UNICODE
730 #define pwszQuery szQuery
731 #else
732 WCHAR *pwszQuery = WideStringFromMBString(szQuery);
733 #endif
734
735 MutexLock(hConn->mutexTransLock, INFINITE);
736 if (m_bDumpSQL)
737 ms = GetCurrentTimeMs();
738 hResult = m_fpDrvAsyncSelect(hConn->hConn, pwszQuery, &dwError, errorText);
739 if ((hResult == NULL) && (dwError == DBERR_CONNECTION_LOST))
740 {
741 DBReconnect(hConn);
742 hResult = m_fpDrvAsyncSelect(hConn->hConn, pwszQuery, &dwError, errorText);
743 }
744
745 #ifndef UNICODE
746 free(pwszQuery);
747 #endif
748
749 if (m_bDumpSQL)
750 {
751 ms = GetCurrentTimeMs() - ms;
752 DbgPrintf(9, _T("%s async query: \"%s\" [%d ms]"), (hResult != NULL) ? _T("Successful") : _T("Failed"), szQuery, (DWORD)ms);
753 }
754 if (hResult == NULL)
755 {
756 MutexUnlock(hConn->mutexTransLock);
757 if (m_bLogSQLErrors)
758 nxlog_write(MSG_SQL_ERROR, EVENTLOG_ERROR_TYPE, _T("ss"), szQuery, errorText);
759 }
760 return hResult;
761 }
762
763 DB_ASYNC_RESULT LIBNXSRV_EXPORTABLE DBAsyncSelect(DB_HANDLE hConn, const TCHAR *query)
764 {
765 TCHAR errorText[DBDRV_MAX_ERROR_TEXT];
766
767 return DBAsyncSelectEx(hConn, query, errorText);
768 }
769
770
771 //
772 // Fetch next row from asynchronous SELECT result
773 //
774
775 BOOL LIBNXSRV_EXPORTABLE DBFetch(DB_ASYNC_RESULT hResult)
776 {
777 return m_fpDrvFetch(hResult);
778 }
779
780
781 //
782 // Get field's value from asynchronous SELECT result
783 //
784
785 TCHAR LIBNXSRV_EXPORTABLE *DBGetFieldAsync(DB_ASYNC_RESULT hResult, int iColumn, TCHAR *pBuffer, int iBufSize)
786 {
787 #ifdef UNICODE
788 return m_fpDrvGetFieldAsync(hResult, iColumn, pBuffer, iBufSize);
789 #else
790 WCHAR *pwszBuffer;
791 char *pszRet;
792
793 pwszBuffer = (WCHAR *)malloc(iBufSize * sizeof(WCHAR));
794 if (m_fpDrvGetFieldAsync(hResult, iColumn, pwszBuffer, iBufSize) != NULL)
795 {
796 WideCharToMultiByte(CP_ACP, WC_COMPOSITECHECK | WC_DEFAULTCHAR,
797 pwszBuffer, -1, pBuffer, iBufSize, NULL, NULL);
798 pszRet = pBuffer;
799 }
800 else
801 {
802 pszRet = NULL;
803 }
804 free(pwszBuffer);
805 return pszRet;
806 #endif
807 }
808
809
810 //
811 // Get field's value as unsigned long from asynchronous SELECT result
812 //
813
814 DWORD LIBNXSRV_EXPORTABLE DBGetFieldAsyncULong(DB_ASYNC_RESULT hResult, int iColumn)
815 {
816 LONG iVal;
817 DWORD dwVal;
818 TCHAR szBuffer[64];
819
820 if (DBGetFieldAsync(hResult, iColumn, szBuffer, 64) == NULL)
821 return 0;
822 iVal = _tcstol(szBuffer, NULL, 10);
823 memcpy(&dwVal, &iVal, sizeof(LONG)); // To prevent possible conversion
824 return dwVal;
825 }
826
827
828 //
829 // Get field's value as unsigned 64-bit int from asynchronous SELECT result
830 //
831
832 QWORD LIBNXSRV_EXPORTABLE DBGetFieldAsyncUInt64(DB_ASYNC_RESULT hResult, int iColumn)
833 {
834 INT64 iVal;
835 QWORD qwVal;
836 TCHAR szBuffer[64];
837
838 if (DBGetFieldAsync(hResult, iColumn, szBuffer, 64) == NULL)
839 return 0;
840 iVal = _tcstoll(szBuffer, NULL, 10);
841 memcpy(&qwVal, &iVal, sizeof(INT64)); // To prevent possible conversion
842 return qwVal;
843 }
844
845
846 //
847 // Get field's value as signed long from asynchronous SELECT result
848 //
849
850 LONG LIBNXSRV_EXPORTABLE DBGetFieldAsyncLong(DB_RESULT hResult, int iColumn)
851 {
852 TCHAR szBuffer[64];
853
854 return DBGetFieldAsync(hResult, iColumn, szBuffer, 64) == NULL ? 0 : _tcstol(szBuffer, NULL, 10);
855 }
856
857
858 //
859 // Get field's value as signed 64-bit int from asynchronous SELECT result
860 //
861
862 INT64 LIBNXSRV_EXPORTABLE DBGetFieldAsyncInt64(DB_RESULT hResult, int iColumn)
863 {
864 TCHAR szBuffer[64];
865
866 return DBGetFieldAsync(hResult, iColumn, szBuffer, 64) == NULL ? 0 : _tcstoll(szBuffer, NULL, 10);
867 }
868
869
870 //
871 // Get field's value as signed long from asynchronous SELECT result
872 //
873
874 double LIBNXSRV_EXPORTABLE DBGetFieldAsyncDouble(DB_RESULT hResult, int iColumn)
875 {
876 TCHAR szBuffer[64];
877
878 return DBGetFieldAsync(hResult, iColumn, szBuffer, 64) == NULL ? 0 : _tcstod(szBuffer, NULL);
879 }
880
881
882 //
883 // Get field's value as IP address from asynchronous SELECT result
884 //
885
886 DWORD LIBNXSRV_EXPORTABLE DBGetFieldAsyncIPAddr(DB_RESULT hResult, int iColumn)
887 {
888 TCHAR szBuffer[64];
889
890 return DBGetFieldAsync(hResult, iColumn, szBuffer, 64) == NULL ? INADDR_NONE :
891 ntohl(inet_addr(szBuffer));
892 }
893
894
895 //
896 // Free asynchronous SELECT result
897 //
898
899 void LIBNXSRV_EXPORTABLE DBFreeAsyncResult(DB_HANDLE hConn, DB_ASYNC_RESULT hResult)
900 {
901 m_fpDrvFreeAsyncResult(hResult);
902 MutexUnlock(hConn->mutexTransLock);
903 }
904
905
906 //
907 // Begin transaction
908 //
909
910 BOOL LIBNXSRV_EXPORTABLE DBBegin(DB_HANDLE hConn)
911 {
912 DWORD dwResult;
913 BOOL bRet = FALSE;
914
915 MutexLock(hConn->mutexTransLock, INFINITE);
916 if (hConn->nTransactionLevel == 0)
917 {
918 dwResult = m_fpDrvBegin(hConn->hConn);
919 if (dwResult == DBERR_CONNECTION_LOST)
920 {
921 DBReconnect(hConn);
922 dwResult = m_fpDrvBegin(hConn->hConn);
923 }
924 if (dwResult == DBERR_SUCCESS)
925 {
926 hConn->nTransactionLevel++;
927 bRet = TRUE;
928 }
929 else
930 {
931 MutexUnlock(hConn->mutexTransLock);
932 }
933 }
934 else
935 {
936 hConn->nTransactionLevel++;
937 bRet = TRUE;
938 }
939 return bRet;
940 }
941
942
943 //
944 // Commit transaction
945 //
946
947 BOOL LIBNXSRV_EXPORTABLE DBCommit(DB_HANDLE hConn)
948 {
949 BOOL bRet = FALSE;
950
951 MutexLock(hConn->mutexTransLock, INFINITE);
952 if (hConn->nTransactionLevel > 0)
953 {
954 hConn->nTransactionLevel--;
955 if (hConn->nTransactionLevel == 0)
956 bRet = (m_fpDrvCommit(hConn->hConn) == DBERR_SUCCESS);
957 else
958 bRet = TRUE;
959 MutexUnlock(hConn->mutexTransLock);
960 }
961 MutexUnlock(hConn->mutexTransLock);
962 return bRet;
963 }
964
965
966 //
967 // Begin transaction
968 //
969
970 BOOL LIBNXSRV_EXPORTABLE DBRollback(DB_HANDLE hConn)
971 {
972 BOOL bRet = FALSE;
973
974 MutexLock(hConn->mutexTransLock, INFINITE);
975 if (hConn->nTransactionLevel > 0)
976 {
977 hConn->nTransactionLevel--;
978 if (hConn->nTransactionLevel == 0)
979 bRet = (m_fpDrvRollback(hConn->hConn) == DBERR_SUCCESS);
980 else
981 bRet = TRUE;
982 MutexUnlock(hConn->mutexTransLock);
983 }
984 MutexUnlock(hConn->mutexTransLock);
985 return bRet;
986 }
987
988
989 //
990 // Characters to be escaped before writing to SQL
991 //
992
993 static TCHAR m_szSpecialChars[] = _T("\x01\x02\x03\x04\x05\x06\x07\x08")
994 _T("\x09\x0A\x0B\x0C\x0D\x0E\x0F\x10")
995 _T("\x11\x12\x13\x14\x15\x16\x17\x18")
996 _T("\x19\x1A\x1B\x1C\x1D\x1E\x1F")
997 _T("#%\"\\'\x7F");
998
999
1000 //
1001 // Escape some special characters in string for writing into database
1002 //
1003
1004 TCHAR LIBNXSRV_EXPORTABLE *EncodeSQLString(const TCHAR *pszIn)
1005 {
1006 char *pszOut;
1007 int iPosIn, iPosOut, iStrSize;
1008
1009 if ((pszIn != NULL) && (*pszIn != 0))
1010 {
1011 // Allocate destination buffer
1012 iStrSize = (int)_tcslen(pszIn) + 1;
1013 for(iPosIn = 0; pszIn[iPosIn] != 0; iPosIn++)
1014 if (_tcschr(m_szSpecialChars, pszIn[iPosIn]) != NULL)
1015 iStrSize += 2;
1016 pszOut = (char *)malloc(iStrSize);
1017
1018 // Translate string
1019 for(iPosIn = 0, iPosOut = 0; pszIn[iPosIn] != 0; iPosIn++)
1020 if (strchr(m_szSpecialChars, pszIn[iPosIn]) != NULL)
1021 {
1022 pszOut[iPosOut++] = _T('#');
1023 pszOut[iPosOut++] = bin2hex(pszIn[iPosIn] >> 4);
1024 pszOut[iPosOut++] = bin2hex(pszIn[iPosIn] & 0x0F);
1025 }
1026 else
1027 {
1028 pszOut[iPosOut++] = pszIn[iPosIn];
1029 }
1030 pszOut[iPosOut] = 0;
1031 }
1032 else
1033 {
1034 // Encode empty strings as #00
1035 pszOut = (TCHAR *)malloc(4 * sizeof(TCHAR));
1036 _tcscpy(pszOut, _T("#00"));
1037 }
1038 return pszOut;
1039 }
1040
1041
1042 //
1043 // Restore characters encoded by EncodeSQLString()
1044 // Characters are decoded "in place"
1045 //
1046
1047 void LIBNXSRV_EXPORTABLE DecodeSQLString(TCHAR *pszStr)
1048 {
1049 int iPosIn, iPosOut;
1050
1051 if (pszStr == NULL)
1052 return;
1053
1054 for(iPosIn = 0, iPosOut = 0; pszStr[iPosIn] != 0; iPosIn++)
1055 {
1056 if (pszStr[iPosIn] == _T('#'))
1057 {
1058 iPosIn++;
1059 pszStr[iPosOut] = hex2bin(pszStr[iPosIn]) << 4;
1060 iPosIn++;
1061 pszStr[iPosOut] |= hex2bin(pszStr[iPosIn]);
1062 iPosOut++;
1063 }
1064 else
1065 {
1066 pszStr[iPosOut++] = pszStr[iPosIn];
1067 }
1068 }
1069 pszStr[iPosOut] = 0;
1070 }
1071
1072
1073 //
1074 // Get database schema version
1075 // Will return 0 for unknown and -1 in case of SQL errors
1076 //
1077
1078 int LIBNXSRV_EXPORTABLE DBGetSchemaVersion(DB_HANDLE conn)
1079 {
1080 DB_RESULT hResult;
1081 int version = 0;
1082
1083 // Read schema version from 'metadata' table, where it should
1084 // be stored starting from schema version 87
1085 // We ignore SQL error in this case, because table 'metadata'
1086 // may not exist in old schema versions
1087 hResult = DBSelect(conn, _T("SELECT var_value FROM metadata WHERE var_name='SchemaVersion'"));
1088 if (hResult != NULL)
1089 {
1090 if (DBGetNumRows(hResult) > 0)
1091 version = DBGetFieldLong(hResult, 0, 0);
1092 DBFreeResult(hResult);
1093 }
1094
1095 // If database schema version is less than 87, version number
1096 // will be stored in 'config' table
1097 if (version == 0)
1098 {
1099 hResult = DBSelect(conn, _T("SELECT var_value FROM config WHERE var_name='DBFormatVersion'"));
1100 if (hResult != NULL)
1101 {
1102 if (DBGetNumRows(hResult) > 0)
1103 version = DBGetFieldLong(hResult, 0, 0);
1104 DBFreeResult(hResult);
1105 }
1106 else
1107 {
1108 version = -1;
1109 }
1110 }
1111
1112 return version;
1113 }
1114
1115
1116 //
1117 // Get database syntax
1118 //
1119
1120 int LIBNXSRV_EXPORTABLE DBGetSyntax(DB_HANDLE conn)
1121 {
1122 DB_RESULT hResult;
1123 TCHAR syntaxId[256];
1124 BOOL read = FALSE;
1125 int syntax;
1126
1127 // Get database syntax
1128 hResult = DBSelect(conn, _T("SELECT var_value FROM metadata WHERE var_name='Syntax'"));
1129 if (hResult != NULL)
1130 {
1131 if (DBGetNumRows(hResult) > 0)
1132 {
1133 DBGetField(hResult, 0, 0, syntaxId, sizeof(syntaxId));
1134 read = TRUE;
1135 }
1136 else
1137 {
1138 _tcscpy(syntaxId, _T("UNKNOWN"));
1139 }
1140 DBFreeResult(hResult);
1141 }
1142
1143 // If database schema version is less than 87, syntax
1144 // will be stored in 'config' table, so try it
1145 if (!read)
1146 {
1147 hResult = DBSelect(conn, _T("SELECT var_value FROM config WHERE var_name='DBSyntax'"));
1148 if (hResult != NULL)
1149 {
1150 if (DBGetNumRows(hResult) > 0)
1151 {
1152 DBGetField(hResult, 0, 0, syntaxId, sizeof(syntaxId));
1153 read = TRUE;
1154 }
1155 else
1156 {
1157 _tcscpy(syntaxId, _T("UNKNOWN"));
1158 }
1159 DBFreeResult(hResult);
1160 }
1161 }
1162
1163 if (!_tcscmp(syntaxId, _T("MYSQL")))
1164 {
1165 syntax = DB_SYNTAX_MYSQL;
1166 }
1167 else if (!_tcscmp(syntaxId, _T("PGSQL")))
1168 {
1169 syntax = DB_SYNTAX_PGSQL;
1170 }
1171 else if (!_tcscmp(syntaxId, _T("MSSQL")))
1172 {
1173 syntax = DB_SYNTAX_MSSQL;
1174 }
1175 else if (!_tcscmp(syntaxId, _T("ORACLE")))
1176 {
1177 syntax = DB_SYNTAX_ORACLE;
1178 }
1179 else if (!_tcscmp(syntaxId, _T("SQLITE")))
1180 {
1181 syntax = DB_SYNTAX_SQLITE;
1182 }
1183 else
1184 {
1185 syntax = DB_SYNTAX_UNKNOWN;
1186 }
1187
1188 return syntax;
1189 }