Implemented DB driver call PrepareString; alarms table converted to new format
authorVictor Kirhenshtein <victor@netxms.org>
Fri, 14 Aug 2009 05:46:12 +0000 (05:46 +0000)
committerVictor Kirhenshtein <victor@netxms.org>
Fri, 14 Aug 2009 05:46:12 +0000 (05:46 +0000)
16 files changed:
include/netxmsdb.h
include/nms_util.h
src/libnetxms/string.cpp
src/server/core/alarm.cpp
src/server/core/nxcore.vcproj
src/server/dbdrv/mssql/mssql.cpp
src/server/dbdrv/mssql/mssqldrv.h
src/server/dbdrv/mysql/mysql.cpp
src/server/dbdrv/odbc/odbc.cpp
src/server/dbdrv/oracle/oracle.cpp
src/server/dbdrv/pgsql/pgsql.cpp
src/server/dbdrv/sqlite/sqlite.cpp
src/server/include/dbdrv.h
src/server/include/nxsrvapi.h
src/server/libnxsrv/db.cpp
src/server/tools/nxdbmgr/upgrade.cpp

index 7208f6d..b1d0cea 100644 (file)
@@ -24,6 +24,6 @@
 #ifndef _netxmsdb_h
 #define _netxmsdb_h
 
-#define DB_FORMAT_VERSION   201
+#define DB_FORMAT_VERSION   202
 
 #endif
index dff621c..791f1ab 100644 (file)
@@ -246,6 +246,7 @@ public:
        static const int npos;
 
    String();
+       String(const String &src);
    ~String();
 
    void SetBuffer(TCHAR *pszBuffer);
index 1fe4a5e..368b079 100644 (file)
@@ -43,6 +43,17 @@ String::String()
 
 
 //
+// Copy constructor
+//
+
+String::String(const String &src)
+{
+       m_dwBufSize = src.m_dwBufSize;
+       m_pszBuffer = (src.m_pszBuffer != NULL) ? (TCHAR *)nx_memdup(src.m_pszBuffer, src.m_dwBufSize * sizeof(TCHAR)) : NULL;
+}
+
+
+//
 // Destructor
 //
 
@@ -69,7 +80,7 @@ const String& String::operator =(const TCHAR *pszStr)
 // Operator +=
 //
 
-const String&  String::operator +=(const TCHAR *pszStr)
+const String& String::operator +=(const TCHAR *pszStr)
 {
    DWORD dwLen;
 
index 302aadc..7ec662e 100644 (file)
@@ -127,16 +127,13 @@ BOOL AlarmManager::Init(void)
          m_pAlarmList[i].dwSourceEventCode = DBGetFieldULong(hResult, i, 2);
          m_pAlarmList[i].qwSourceEventId = DBGetFieldUInt64(hResult, i, 3);
          DBGetField(hResult, i, 4, m_pAlarmList[i].szMessage, MAX_DB_STRING);
-         DecodeSQLString(m_pAlarmList[i].szMessage);
          m_pAlarmList[i].nOriginalSeverity = (BYTE)DBGetFieldLong(hResult, i, 5);
          m_pAlarmList[i].nCurrentSeverity = (BYTE)DBGetFieldLong(hResult, i, 6);
          DBGetField(hResult, i, 7, m_pAlarmList[i].szKey, MAX_DB_STRING);
-         DecodeSQLString(m_pAlarmList[i].szKey);
          m_pAlarmList[i].dwCreationTime = DBGetFieldULong(hResult, i, 8);
          m_pAlarmList[i].dwLastChangeTime = DBGetFieldULong(hResult, i, 9);
          m_pAlarmList[i].nHelpDeskState = (BYTE)DBGetFieldLong(hResult, i, 10);
          DBGetField(hResult, i, 11, m_pAlarmList[i].szHelpDeskRef, MAX_HELPDESK_REF_LEN);
-         DecodeSQLString(m_pAlarmList[i].szHelpDeskRef);
          m_pAlarmList[i].dwAckByUser = DBGetFieldULong(hResult, i, 12);
          m_pAlarmList[i].dwRepeatCount = DBGetFieldULong(hResult, i, 13);
          m_pAlarmList[i].nState = (BYTE)DBGetFieldLong(hResult, i, 14);
@@ -232,28 +229,20 @@ void AlarmManager::NewAlarm(TCHAR *pszMsg, TCHAR *pszKey, int nState,
       }
 
       // Save alarm to database
-      pszExpMsg = EncodeSQLString(alarm.szMessage);
-      pszExpKey = EncodeSQLString(alarm.szKey);
-      pszEscRef = EncodeSQLString(alarm.szHelpDeskRef);
       sprintf(szQuery, "INSERT INTO alarms (alarm_id,creation_time,last_change_time,"
                        "source_object_id,source_event_code,message,original_severity,"
                        "current_severity,alarm_key,alarm_state,ack_by,hd_state,"
                        "hd_ref,repeat_count,term_by,timeout,timeout_event,source_event_id) VALUES "
-                       "(%d,%d,%d,%d,%d,'%s',%d,%d,'%s',%d,%d,%d,'%s',%d,%d,%d,%d,"
-#ifdef _WIN32
-                       "%I64d)",
-#else
-                       "%lld)",
-#endif
+                       "(%d,%d,%d,%d,%d,%s,%d,%d,%s,%d,%d,%d,%s,%d,%d,%d,%d," UINT64_FMT ")",
               alarm.dwAlarmId, alarm.dwCreationTime, alarm.dwLastChangeTime,
-              alarm.dwSourceObject, alarm.dwSourceEventCode, pszExpMsg,
-              alarm.nOriginalSeverity, alarm.nCurrentSeverity, pszExpKey,
-              alarm.nState, alarm.dwAckByUser, alarm.nHelpDeskState, pszEscRef,
+                                 alarm.dwSourceObject, alarm.dwSourceEventCode,
+                                 (const TCHAR *)DBPrepareString(alarm.szMessage),
+              alarm.nOriginalSeverity, alarm.nCurrentSeverity,
+                                 (const TCHAR *)DBPrepareString(alarm.szKey),
+              alarm.nState, alarm.dwAckByUser, alarm.nHelpDeskState,
+                                 (const TCHAR *)DBPrepareString(alarm.szHelpDeskRef),
               alarm.dwRepeatCount, alarm.dwTermByUser, alarm.dwTimeout,
                                  alarm.dwTimeoutEvent, alarm.qwSourceEventId);
-      free(pszExpMsg);
-      free(pszExpKey);
-      free(pszEscRef);
       QueueSQLRequest(szQuery);
 
       // Notify connected clients about new alarm
@@ -425,17 +414,16 @@ void AlarmManager::DeleteAlarm(DWORD dwAlarmId)
 
 void AlarmManager::UpdateAlarmInDB(NXC_ALARM *pAlarm)
 {
-   char szQuery[1024], *pszEscRef;
+   char szQuery[1024];
 
-   pszEscRef = EncodeSQLString(pAlarm->szHelpDeskRef);
    sprintf(szQuery, "UPDATE alarms SET alarm_state=%d,ack_by=%d,term_by=%d,"
                     "last_change_time=%d,current_severity=%d,repeat_count=%d,"
-                    "hd_state=%d,hd_ref='%s',timeout=%d,timeout_event=%d WHERE alarm_id=%d",
+                    "hd_state=%d,hd_ref=%s,timeout=%d,timeout_event=%d WHERE alarm_id=%d",
            pAlarm->nState, pAlarm->dwAckByUser, pAlarm->dwTermByUser,
            pAlarm->dwLastChangeTime, pAlarm->nCurrentSeverity,
-           pAlarm->dwRepeatCount, pAlarm->nHelpDeskState, pszEscRef,
+           pAlarm->dwRepeatCount, pAlarm->nHelpDeskState, 
+                         (const TCHAR *)DBPrepareString(pAlarm->szHelpDeskRef),
            pAlarm->dwTimeout, pAlarm->dwTimeoutEvent, pAlarm->dwAlarmId);
-   free(pszEscRef);
    QueueSQLRequest(szQuery);
 }
 
index 165daaa..a41039b 100644 (file)
                                >\r
                        </File>\r
                        <File\r
+                               RelativePath="..\..\..\include\nms_util.h"\r
+                               >\r
+                       </File>\r
+                       <File\r
                                RelativePath="..\..\..\include\nxclapi.h"\r
                                >\r
                        </File>\r
index 4662740..a245ecc 100644 (file)
@@ -109,10 +109,64 @@ extern "C" int EXPORT drvAPIVersion = DBDRV_API_VERSION;
 
 
 //
+// Prepare string for using in SQL query - enclose in quotes and escape as needed
+//
+
+extern "C" TCHAR EXPORT *DrvPrepareString(const TCHAR *str)
+{
+       int len = (int)_tcslen(str) + 4;   // + two quotes, N prefix, and \0 at the end
+       int bufferSize = len + 128;
+       TCHAR *out = (TCHAR *)malloc(bufferSize * sizeof(TCHAR));
+       _tcscpy(out, _T("N'"));
+
+       const TCHAR *src = str;
+       int outPos;
+       for(outPos = 2; *src != NULL; src++)
+       {
+               if (*src < 32)
+               {
+                       TCHAR buffer[32];
+
+                       _sntprintf(buffer, 32, _T("'+nchar(%d)+N'"), *src);
+                       int l = (int)_tcslen(buffer);
+
+                       len += l;
+                       if (len >= bufferSize)
+                       {
+                               bufferSize += 128;
+                               out = (TCHAR *)realloc(out, bufferSize * sizeof(TCHAR));
+                       }
+                       memcpy(&out[outPos], buffer, l * sizeof(TCHAR));
+                       outPos += l;
+               }
+               else if (*src == _T('\''))
+               {
+                       len++;
+                       if (len >= bufferSize)
+                       {
+                               bufferSize += 128;
+                               out = (TCHAR *)realloc(out, bufferSize * sizeof(TCHAR));
+                       }
+                       out[outPos++] = _T('\'');
+                       out[outPos++] = _T('\'');
+               }
+               else
+               {
+                       out[outPos++] = *src;
+               }
+       }
+       out[outPos++] = _T('\'');
+       out[outPos++] = 0;
+
+       return out;
+}
+
+
+//
 // Initialize driver
 //
 
-extern "C" BOOL EXPORT DrvInit(char *szCmdLine)
+extern "C" BOOL EXPORT DrvInit(const char *cmdLine)
 {
    BOOL bResult = FALSE;
 
@@ -138,35 +192,35 @@ extern "C" void EXPORT DrvUnload(void)
 // Connect to database
 //
 
-extern "C" DB_CONNECTION EXPORT DrvConnect(char *szHost, char *szLogin,
-                                           char *szPassword, char *szDatabase)
+extern "C" DB_CONNECTION EXPORT DrvConnect(const char *host, const char *login,
+                                           const char *password, const char *database)
 {
    LOGINREC *loginrec;
    MSDB_CONN *pConn = NULL;
    PDBPROCESS hProcess;
 
    loginrec = dblogin();
-   if (!strcmp(szLogin, "*"))
+   if (!strcmp(login, "*"))
    {
       DBSETLSECURE(loginrec);
    }
    else
    {
-      DBSETLUSER(loginrec, szLogin);
-      DBSETLPWD(loginrec, szPassword);
+      DBSETLUSER(loginrec, login);
+      DBSETLPWD(loginrec, password);
    }
    DBSETLAPP(loginrec, "NetXMS");
    DBSETLTIME(loginrec, CONNECT_TIMEOUT);
-   hProcess = dbopen(loginrec, szHost);
+   hProcess = dbopen(loginrec, host);
 
    if (hProcess != NULL)
    {
       dbsetuserdata(hProcess, NULL);
 
       // Change to specified database
-      if (szDatabase != NULL)
+      if (database != NULL)
       {
-         if (dbuse(hProcess, szDatabase) != SUCCEED)
+         if (dbuse(hProcess, database) != SUCCEED)
          {
             dbclose(hProcess);
             hProcess = NULL;
@@ -179,10 +233,10 @@ extern "C" DB_CONNECTION EXPORT DrvConnect(char *szHost, char *szLogin,
          pConn->hProcess = hProcess;
          pConn->mutexQueryLock = MutexCreate();
          pConn->bProcessDead = FALSE;
-         nx_strncpy(pConn->szHost, szHost, MAX_CONN_STRING);
-         nx_strncpy(pConn->szLogin, szLogin, MAX_CONN_STRING);
-         nx_strncpy(pConn->szPassword, szPassword, MAX_CONN_STRING);
-         nx_strncpy(pConn->szDatabase, CHECK_NULL_EX(szDatabase), MAX_CONN_STRING);
+         nx_strncpy(pConn->szHost, host, MAX_CONN_STRING);
+         nx_strncpy(pConn->szLogin, login, MAX_CONN_STRING);
+         nx_strncpy(pConn->szPassword, password, MAX_CONN_STRING);
+         nx_strncpy(pConn->szDatabase, CHECK_NULL_EX(database), MAX_CONN_STRING);
                        pConn->szErrorText[0] = 0;
 
          dbsetuserdata(hProcess, pConn);
index 441aa4e..7710ea0 100644 (file)
@@ -23,6 +23,8 @@
 #ifndef _mssqldrv_h_
 #define _mssqldrv_h_
 
+#define _CRT_SECURE_NO_WARNINGS
+
 #ifdef _WIN32
 #include <winsock2.h>
 #include <windows.h>
index 15a7a8b..3e71c2f 100644 (file)
@@ -32,6 +32,79 @@ int EXPORT drvAPIVersion = DBDRV_API_VERSION;
 
 
 //
+// Prepare string for using in SQL query - enclose in quotes and escape as needed
+//
+
+#define UPDATE_LENGTH \
+                               len++; \
+                               if (len >= bufferSize - 1) \
+                               { \
+                                       bufferSize += 128; \
+                                       out = (TCHAR *)realloc(out, bufferSize * sizeof(TCHAR)); \
+                               }
+
+extern "C" TCHAR EXPORT *DrvPrepareString(const TCHAR *str)
+{
+       int len = (int)_tcslen(str) + 3;   // + two quotes and \0 at the end
+       int bufferSize = len + 128;
+       TCHAR *out = (TCHAR *)malloc(bufferSize * sizeof(TCHAR));
+       out[0] = _T('\'');
+
+       const TCHAR *src = str;
+       int outPos;
+       for(outPos = 1; *src != NULL; src++)
+       {
+               switch(*src)
+               {
+                       case _T('\''):
+                               out[outPos++] = _T('\'');
+                               out[outPos++] = _T('\'');
+                               UPDATE_LENGTH;
+                               break;
+                       case _T('\r'):
+                               out[outPos++] = _T('\\');
+                               out[outPos++] = _T('\r');
+                               UPDATE_LENGTH;
+                               break;
+                       case _T('\n'):
+                               out[outPos++] = _T('\\');
+                               out[outPos++] = _T('\n');
+                               UPDATE_LENGTH;
+                               break;
+                       case _T('\b'):
+                               out[outPos++] = _T('\\');
+                               out[outPos++] = _T('\b');
+                               UPDATE_LENGTH;
+                               break;
+                       case _T('\t'):
+                               out[outPos++] = _T('\\');
+                               out[outPos++] = _T('\t');
+                               UPDATE_LENGTH;
+                               break;
+                       case 26:
+                               out[outPos++] = _T('\\');
+                               out[outPos++] = _T('Z');
+                               break;
+                       case _T('\\'):
+                               out[outPos++] = _T('\\');
+                               out[outPos++] = _T('\\');
+                               UPDATE_LENGTH;
+                               break;
+                       default:
+                               out[outPos++] = *src;
+                               break;
+               }
+       }
+       out[outPos++] = _T('\'');
+       out[outPos++] = 0;
+
+       return out;
+}
+
+#undef UPDATE_LENGTH
+
+
+//
 // Initialize driver
 //
 
@@ -95,7 +168,7 @@ extern "C" DB_CONNECTION EXPORT DrvConnect(char *szHost, char *szLogin, char *sz
        pConn->mutexQueryLock = MutexCreate();
 
    // Switch to UTF-8 encoding
-   mysql_query(pMySQL, "SET NAMES 'utf8'");
+   mysql_set_character_set(pMySQL, "utf8");
        
        return (DB_CONNECTION)pConn;
 }
index 80d98cf..27de63e 100644 (file)
@@ -97,6 +97,44 @@ static DWORD GetSQLErrorInfo(SQLSMALLINT nHandleType, SQLHANDLE hHandle, TCHAR *
 
 
 //
+// Prepare string for using in SQL query - enclose in quotes and escape as needed
+//
+
+extern "C" TCHAR EXPORT *DrvPrepareString(const TCHAR *str)
+{
+       int len = (int)_tcslen(str) + 3;   // + two quotes and \0 at the end
+       int bufferSize = len + 128;
+       TCHAR *out = (TCHAR *)malloc(bufferSize * sizeof(TCHAR));
+       out[0] = _T('\'');
+
+       const TCHAR *src = str;
+       int outPos;
+       for(outPos = 1; *src != NULL; src++)
+       {
+               if (*src == _T('\''))
+               {
+                       len++;
+                       if (len >= bufferSize)
+                       {
+                               bufferSize += 128;
+                               out = (TCHAR *)realloc(out, bufferSize * sizeof(TCHAR));
+                       }
+                       out[outPos++] = _T('\'');
+                       out[outPos++] = _T('\'');
+               }
+               else
+               {
+                       out[outPos++] = *src;
+               }
+       }
+       out[outPos++] = _T('\'');
+       out[outPos++] = 0;
+
+       return out;
+}
+
+
+//
 // Initialize driver
 //
 
index ed90662..49599fe 100644 (file)
@@ -33,6 +33,44 @@ int EXPORT drvAPIVersion = DBDRV_API_VERSION;
 
 
 //
+// Prepare string for using in SQL query - enclose in quotes and escape as needed
+//
+
+extern "C" TCHAR EXPORT *DrvPrepareString(const TCHAR *str)
+{
+       int len = (int)_tcslen(str) + 3;   // + two quotes and \0 at the end
+       int bufferSize = len + 128;
+       TCHAR *out = (TCHAR *)malloc(bufferSize * sizeof(TCHAR));
+       out[0] = _T('\'');
+
+       const TCHAR *src = str;
+       int outPos;
+       for(outPos = 1; *src != NULL; src++)
+       {
+               if (*src == _T('\''))
+               {
+                       len++;
+                       if (len >= bufferSize)
+                       {
+                               bufferSize += 128;
+                               out = (TCHAR *)realloc(out, bufferSize * sizeof(TCHAR));
+                       }
+                       out[outPos++] = _T('\'');
+                       out[outPos++] = _T('\'');
+               }
+               else
+               {
+                       out[outPos++] = *src;
+               }
+       }
+       out[outPos++] = _T('\'');
+       out[outPos++] = 0;
+
+       return out;
+}
+
+
+//
 // Initialize driver
 //
 
index 15826f1..d047be6 100644 (file)
@@ -37,6 +37,69 @@ int EXPORT drvAPIVersion = DBDRV_API_VERSION;
 
 
 //
+// Prepare string for using in SQL query - enclose in quotes and escape as needed
+//
+
+extern "C" TCHAR EXPORT *DrvPrepareString(const TCHAR *str)
+{
+       int len = (int)_tcslen(str) + 3;   // + two quotes and \0 at the end
+       int bufferSize = len + 128;
+       TCHAR *out = (TCHAR *)malloc(bufferSize * sizeof(TCHAR));
+       out[0] = _T('\'');
+
+       const TCHAR *src = str;
+       int outPos;
+       for(outPos = 1; *src != NULL; src++)
+       {
+               if (*src < 32)
+               {
+                       TCHAR buffer[8];
+
+                       _sntprintf(buffer, 8, _T("\\%03o"), *src);
+                       len += 4;
+                       if (len >= bufferSize)
+                       {
+                               bufferSize += 128;
+                               out = (TCHAR *)realloc(out, bufferSize * sizeof(TCHAR));
+                       }
+                       memcpy(&out[outPos], buffer, 4 * sizeof(TCHAR));
+                       outPos += 4;
+               }
+               else if (*src == _T('\''))
+               {
+                       len++;
+                       if (len >= bufferSize)
+                       {
+                               bufferSize += 128;
+                               out = (TCHAR *)realloc(out, bufferSize * sizeof(TCHAR));
+                       }
+                       out[outPos++] = _T('\'');
+                       out[outPos++] = _T('\'');
+               }
+               else if (*src == _T('\\'))
+               {
+                       len++;
+                       if (len >= bufferSize)
+                       {
+                               bufferSize += 128;
+                               out = (TCHAR *)realloc(out, bufferSize * sizeof(TCHAR));
+                       }
+                       out[outPos++] = _T('\\');
+                       out[outPos++] = _T('\\');
+               }
+               else
+               {
+                       out[outPos++] = *src;
+               }
+       }
+       out[outPos++] = _T('\'');
+       out[outPos++] = 0;
+
+       return out;
+}
+
+
+//
 // Initialize driver
 //
 
@@ -85,6 +148,14 @@ extern "C" DB_CONNECTION EXPORT DrvConnect(
                }
                else
                {
+                       PGresult        *pResult;
+
+                       pResult = PQexec(pConn->pHandle, "SET standard_conforming_strings TO off");
+                       PQclear(pResult);
+                       
+                       pResult = PQexec(pConn->pHandle, "SET escape_string_warning TO off");
+                       PQclear(pResult);
+
                pConn->mutexQueryLock = MutexCreate();
          pConn->pFetchBuffer = NULL;
                }
index c86c502..5187ad9 100644 (file)
@@ -181,6 +181,44 @@ static int ExecCommand(SQLITE_CONN *pConn, int nCmd, const char *pszQuery, TCHAR
 
 
 //
+// Prepare string for using in SQL query - enclose in quotes and escape as needed
+//
+
+extern "C" TCHAR EXPORT *DrvPrepareString(const TCHAR *str)
+{
+       int len = (int)_tcslen(str) + 3;   // + two quotes and \0 at the end
+       int bufferSize = len + 128;
+       TCHAR *out = (TCHAR *)malloc(bufferSize * sizeof(TCHAR));
+       out[0] = _T('\'');
+
+       const TCHAR *src = str;
+       int outPos;
+       for(outPos = 1; *src != NULL; src++)
+       {
+               if (*src == _T('\''))
+               {
+                       len++;
+                       if (len >= bufferSize)
+                       {
+                               bufferSize += 128;
+                               out = (TCHAR *)realloc(out, bufferSize * sizeof(TCHAR));
+                       }
+                       out[outPos++] = _T('\'');
+                       out[outPos++] = _T('\'');
+               }
+               else
+               {
+                       out[outPos++] = *src;
+               }
+       }
+       out[outPos++] = _T('\'');
+       out[outPos++] = 0;
+
+       return out;
+}
+
+
+//
 // Initialize driver
 //
 
index 86ad9eb..6a53300 100644 (file)
@@ -30,7 +30,7 @@
 // API version
 //
 
-#define DBDRV_API_VERSION           6
+#define DBDRV_API_VERSION           7
 
 
 //
index 8a3f415..bcca2ad 100644 (file)
@@ -517,6 +517,7 @@ void LIBNXSRV_EXPORTABLE DBUnloadDriver(void);
 int LIBNXSRV_EXPORTABLE DBGetSchemaVersion(DB_HANDLE conn);
 int LIBNXSRV_EXPORTABLE DBGetSyntax(DB_HANDLE conn);
 
+String LIBNXSRV_EXPORTABLE DBPrepareString(const TCHAR *str);
 TCHAR LIBNXSRV_EXPORTABLE *EncodeSQLString(const TCHAR *pszIn);
 void LIBNXSRV_EXPORTABLE DecodeSQLString(TCHAR *pszStr);
 
index e5e682f..0d28e53 100644 (file)
@@ -67,6 +67,7 @@ static int (* m_fpDrvGetColumnCount)(DB_RESULT);
 static const char* (* m_fpDrvGetColumnName)(DB_RESULT, int);
 static int (* m_fpDrvGetColumnCountAsync)(DB_ASYNC_RESULT);
 static const char* (* m_fpDrvGetColumnNameAsync)(DB_ASYNC_RESULT, int);
+static TCHAR* (* m_fpDrvPrepareString)(const TCHAR *) = NULL;
 
 
 //
@@ -150,6 +151,7 @@ BOOL LIBNXSRV_EXPORTABLE DBInit(BOOL bnxlog_write, BOOL bLogErrors, BOOL bDumpSQ
    m_fpDrvCommit = (DWORD (*)(DB_CONNECTION))DLGetSymbolAddrEx(m_hDriver, "DrvCommit");
    m_fpDrvRollback = (DWORD (*)(DB_CONNECTION))DLGetSymbolAddrEx(m_hDriver, "DrvRollback");
    m_fpDrvUnload = (void (*)(void))DLGetSymbolAddrEx(m_hDriver, "DrvUnload");
+   m_fpDrvPrepareString = (TCHAR* (*)(const TCHAR *))DLGetSymbolAddrEx(m_hDriver, "DrvPrepareString");
    if ((fpDrvInit == NULL) || (m_fpDrvConnect == NULL) || (m_fpDrvDisconnect == NULL) ||
        (m_fpDrvQuery == NULL) || (m_fpDrvSelect == NULL) || (m_fpDrvGetField == NULL) ||
        (m_fpDrvGetNumRows == NULL) || (m_fpDrvFreeResult == NULL) || 
@@ -158,7 +160,8 @@ BOOL LIBNXSRV_EXPORTABLE DBInit(BOOL bnxlog_write, BOOL bLogErrors, BOOL bDumpSQ
        (m_fpDrvBegin == NULL) || (m_fpDrvCommit == NULL) || (m_fpDrvRollback == NULL) ||
                 (m_fpDrvGetColumnCount == NULL) || (m_fpDrvGetColumnName == NULL) ||
                 (m_fpDrvGetColumnCountAsync == NULL) || (m_fpDrvGetColumnNameAsync == NULL) ||
-       (m_fpDrvGetFieldLength == NULL) || (m_fpDrvGetFieldLengthAsync == NULL))
+       (m_fpDrvGetFieldLength == NULL) || (m_fpDrvGetFieldLengthAsync == NULL) ||
+                (m_fpDrvPrepareString == NULL))
    {
       if (m_bnxlog_write)
          nxlog_write(MSG_DBDRV_NO_ENTRY_POINTS, EVENTLOG_ERROR_TYPE, "s", g_szDbDriver);
@@ -1052,6 +1055,18 @@ BOOL LIBNXSRV_EXPORTABLE DBRollback(DB_HANDLE hConn)
 
 
 //
+// Prepare string for using int SQL statement
+//
+
+String LIBNXSRV_EXPORTABLE DBPrepareString(const TCHAR *str)
+{
+       String out;
+       out.SetBuffer(m_fpDrvPrepareString(str));
+       return out;
+}
+
+
+//
 // Characters to be escaped before writing to SQL
 //
 
index 048cfe1..b2243df 100644 (file)
@@ -103,6 +103,75 @@ static BOOL SetPrimaryKey(const TCHAR *table, const TCHAR *key)
 
 
 //
+// Convert strings
+//
+
+static BOOL ConvertStrings(const TCHAR *table, const TCHAR *idColumn, const TCHAR *column)
+{
+       DB_RESULT hResult;
+       TCHAR *query;
+       int queryLen = 512;
+       BOOL success = FALSE;
+
+       query = (TCHAR *)malloc(queryLen);
+       _sntprintf(query, queryLen, _T("SELECT %s,%s FROM %s"), idColumn, column, table);
+       hResult = SQLSelect(query);
+       if (hResult == NULL)
+       {
+               free(query);
+               return FALSE;
+       }
+
+       int count = DBGetNumRows(hResult);
+       for(int i = 0; i < count; i++)
+       {
+               INT64 id = DBGetFieldInt64(hResult, i, 0);
+               TCHAR *value = DBGetField(hResult, i, 1, NULL, 0);
+               DecodeSQLString(value);
+               String newValue = DBPrepareString(value);
+               if ((int)newValue.Size() + 256 > queryLen)
+               {
+                       queryLen = newValue.Size() + 256;
+                       query = (TCHAR *)realloc(query, queryLen);
+               }
+               _sntprintf(query, queryLen, _T("UPDATE %s SET %s=%s WHERE %s=") INT64_FMT, table, column,
+                          (const TCHAR *)newValue, idColumn, id);
+               if (!SQLQuery(query))
+                       goto cleanup;
+       }
+       success = TRUE;
+
+cleanup:
+       DBFreeResult(hResult);
+       return success;
+}
+
+
+//
+// Upgrade from V201 to V202
+//
+
+static BOOL H_UpgradeFromV201(int currVersion, int newVersion)
+{
+       if (!ConvertStrings("alarms", "alarm_id", "message"))
+      if (!g_bIgnoreErrors)
+         return FALSE;
+       if (!ConvertStrings("alarms", "alarm_id", "alarm_key"))
+      if (!g_bIgnoreErrors)
+         return FALSE;
+       if (!ConvertStrings("alarms", "alarm_id", "hd_ref"))
+      if (!g_bIgnoreErrors)
+         return FALSE;
+
+       if (!SQLQuery(_T("UPDATE metadata SET var_value='202' WHERE var_name='SchemaVersion'")))
+      if (!g_bIgnoreErrors)
+         return FALSE;
+
+   return TRUE;
+}
+
+
+//
 // Upgrade from V200 to V201
 //
 
@@ -4075,6 +4144,7 @@ static struct
        { 92, 200, H_UpgradeFromV92orV93 },
        { 93, 201, H_UpgradeFromV92orV93 },
        { 200, 201, H_UpgradeFromV200 },
+       { 201, 202, H_UpgradeFromV201 },
    { 0, NULL }
 };