schema-related information (like schema version, DB syntax, etc.) moved from "config...
[public/netxms.git] / src / server / libnxsrv / db.cpp
CommitLineData
5039dede
AK
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
30TCHAR LIBNXSRV_EXPORTABLE g_szDbDriver[MAX_PATH] = _T("");
31TCHAR LIBNXSRV_EXPORTABLE g_szDbDrvParams[MAX_PATH] = _T("");
32TCHAR LIBNXSRV_EXPORTABLE g_szDbServer[MAX_PATH] = _T("127.0.0.1");
33TCHAR LIBNXSRV_EXPORTABLE g_szDbLogin[MAX_DB_LOGIN] = _T("netxms");
34TCHAR LIBNXSRV_EXPORTABLE g_szDbPassword[MAX_DB_PASSWORD] = _T("");
35TCHAR LIBNXSRV_EXPORTABLE g_szDbName[MAX_DB_NAME] = _T("netxms_db");
36
37
38//
39// Static data
40//
41
42static BOOL m_bnxlog_write = FALSE;
43static BOOL m_bLogSQLErrors = FALSE;
44static BOOL m_bDumpSQL = FALSE;
45static int m_nReconnect = 0;
46static MUTEX m_mutexReconnect = INVALID_MUTEX_HANDLE;
47static HMODULE m_hDriver = NULL;
48static DB_CONNECTION (* m_fpDrvConnect)(const char *, const char *, const char *, const char *) = NULL;
49static void (* m_fpDrvDisconnect)(DB_CONNECTION) = NULL;
50static DWORD (* m_fpDrvQuery)(DB_CONNECTION, WCHAR *, TCHAR *) = NULL;
51static DB_RESULT (* m_fpDrvSelect)(DB_CONNECTION, WCHAR *, DWORD *, TCHAR *) = NULL;
52static DB_ASYNC_RESULT (* m_fpDrvAsyncSelect)(DB_CONNECTION, WCHAR *, DWORD *, TCHAR *) = NULL;
53static BOOL (* m_fpDrvFetch)(DB_ASYNC_RESULT) = NULL;
54static LONG (* m_fpDrvGetFieldLength)(DB_RESULT, int, int) = NULL;
55static WCHAR* (* m_fpDrvGetField)(DB_RESULT, int, int, WCHAR *, int) = NULL;
56static WCHAR* (* m_fpDrvGetFieldAsync)(DB_ASYNC_RESULT, int, WCHAR *, int) = NULL;
57static int (* m_fpDrvGetNumRows)(DB_RESULT) = NULL;
58static void (* m_fpDrvFreeResult)(DB_RESULT) = NULL;
59static void (* m_fpDrvFreeAsyncResult)(DB_ASYNC_RESULT) = NULL;
60static DWORD (* m_fpDrvBegin)(DB_CONNECTION) = NULL;
61static DWORD (* m_fpDrvCommit)(DB_CONNECTION) = NULL;
62static DWORD (* m_fpDrvRollback)(DB_CONNECTION) = NULL;
63static void (* m_fpDrvUnload)(void) = NULL;
64static void (* m_fpEventHandler)(DWORD, TCHAR *);
ea6f474a 65static int (* m_fpDrvGetColumnCount)(DB_RESULT);
00d17c5a 66static const char* (* m_fpDrvGetColumnName)(DB_RESULT, int);
ea6f474a 67static int (* m_fpDrvGetColumnCountAsync)(DB_ASYNC_RESULT);
00d17c5a 68static const char* (* m_fpDrvGetColumnNameAsync)(DB_ASYNC_RESULT, int);
5039dede
AK
69
70
71//
72// Get symbol address and log errors
73//
74
75static 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
91BOOL 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");
ea6f474a 141 m_fpDrvGetColumnCount = (int (*)(DB_RESULT))DLGetSymbolAddrEx(m_hDriver, "DrvGetColumnCount");
00d17c5a 142 m_fpDrvGetColumnName = (const char* (*)(DB_RESULT, int))DLGetSymbolAddrEx(m_hDriver, "DrvGetColumnName");
ea6f474a 143 m_fpDrvGetColumnCountAsync = (int (*)(DB_ASYNC_RESULT))DLGetSymbolAddrEx(m_hDriver, "DrvGetColumnCountAsync");
00d17c5a 144 m_fpDrvGetColumnNameAsync = (const char* (*)(DB_ASYNC_RESULT, int))DLGetSymbolAddrEx(m_hDriver, "DrvGetColumnNameAsync");
5039dede
AK
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) ||
ea6f474a
VK
157 (m_fpDrvGetColumnCount == NULL) || (m_fpDrvGetColumnName == NULL) ||
158 (m_fpDrvGetColumnCountAsync == NULL) || (m_fpDrvGetColumnNameAsync == NULL) ||
5039dede
AK
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
189void 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
201DB_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
211DB_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
245void 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
266static 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
302BOOL 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
340BOOL 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
352DB_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
388DB_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
ea6f474a
VK
396//
397// Get number of columns
398//
399
400int LIBNXSRV_EXPORTABLE DBGetColumnCount(DB_RESULT hResult)
401{
402 return m_fpDrvGetColumnCount(hResult);
403}
404
405
406//
407// Get column name
408//
409
410BOOL LIBNXSRV_EXPORTABLE DBGetColumnName(DB_RESULT hResult, int column, TCHAR *buffer, int bufSize)
411{
00d17c5a 412 const char *name;
ea6f474a
VK
413
414 name = m_fpDrvGetColumnName(hResult, column);
415 if (name != NULL)
416 {
417#ifdef UNICODE
00d17c5a 418 MultiByteToWideChar(CP_ACP, MB_PRECOMPOSED, name, -1, buffer, bufSize);
ea6f474a 419 buffer[bufSize - 1] = 0;
00d17c5a
VK
420#else
421 nx_strncpy(buffer, name, bufSize);
ea6f474a
VK
422#endif
423 }
424 return name != NULL;
425}
426
427
428//
429// Get column count for async request
430//
431
432int 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
442BOOL LIBNXSRV_EXPORTABLE DBGetColumnNameAsync(DB_ASYNC_RESULT hResult, int column, TCHAR *buffer, int bufSize)
443{
00d17c5a 444 const char *name;
ea6f474a
VK
445
446 name = m_fpDrvGetColumnNameAsync(hResult, column);
447 if (name != NULL)
448 {
449#ifdef UNICODE
00d17c5a 450 MultiByteToWideChar(CP_ACP, MB_PRECOMPOSED, name, -1, buffer, bufSize);
ea6f474a 451 buffer[bufSize - 1] = 0;
00d17c5a
VK
452#else
453 nx_strncpy(buffer, name, bufSize);
ea6f474a
VK
454#endif
455 }
456 return name != NULL;
457}
458
459
5039dede
AK
460//
461// Get field's value
462//
463
464TCHAR 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 {
e24544f3 525 nLen = (int)wcslen(pwszData) + 1;
5039dede
AK
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
546DWORD 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
565QWORD 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
584LONG 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
597INT64 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
610double 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
623DWORD 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
636BOOL 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
669BOOL 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
701int 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
713void 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
4c4c9b03 724DB_ASYNC_RESULT LIBNXSRV_EXPORTABLE DBAsyncSelectEx(DB_HANDLE hConn, const TCHAR *szQuery, TCHAR *errorText)
5039dede
AK
725{
726 DB_RESULT hResult;
727 DWORD dwError;
728 INT64 ms;
5039dede
AK
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
4c4c9b03
VK
763DB_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
5039dede
AK
770
771//
772// Fetch next row from asynchronous SELECT result
773//
774
775BOOL 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
785TCHAR 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
814DWORD 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
832QWORD 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
850LONG 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
862INT64 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
874double 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
886DWORD 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
899void 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
910BOOL 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
947BOOL 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
970BOOL 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
993static 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
1004TCHAR 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
1047void 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}
28f5b9a4
VK
1071
1072
1073//
1074// Get database schema version
1075// Will return 0 for unknown and -1 in case of SQL errors
1076//
1077
1078int 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
1120int 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}