smsd/services/odbc.c

   1 /**
   2  * ODBC database backend
   3  *
   4  * Part of Gammu project
   5  *
   6  * Copyright (C) 2011 Michal Čihař
   7  *
   8  * Licensed under GNU GPL version 2 or later
   9  */
  10
  11 #include <gammu.h>
  12
  13 #ifdef WIN32
  14 #include <windows.h>
  15 #ifndef __GNUC__
  16 #pragma comment(lib, "libodbc32.lib")
  17 #endif
  18 #endif
  19
  20 #include <stdio.h>
  21 #include <sql.h>
  22 #include <sqlext.h>
  23
  24 #include "../core.h"
  25 #include "sql.h"
  26 #include "sql-core.h"
  27
  28 static void SMSDODBC_LogError(GSM_SMSDConfig * Config, SQLRETURN origret, SQLSMALLINT handle_type, SQLHANDLE handle, const char *message)
  29 {
  30         SQLINTEGER       i = 0;
  31         SQLINTEGER       native;
  32         SQLCHAR  state[ 7 ];
  33         SQLCHAR  text[256];
  34         SQLSMALLINT      len;
  35         SQLRETURN        ret;
  36
  37         SMSD_Log(DEBUG_ERROR, Config, "%s, Code = %d, ODBC diagnostics:", message, (int)origret);
  38
  39         do {
  40                 ret = SQLGetDiagRec(handle_type, handle, ++i, state, &native, text, sizeof(text), &len );
  41                 if (SQL_SUCCEEDED(ret)) {
  42                         SMSD_Log(DEBUG_ERROR, Config, "%s:%ld:%ld:%s\n", state, (long)i, (long)native, text);
  43                 }
  44         } while (ret == SQL_SUCCESS);
  45 }
  46
  47 long long SMSDODBC_GetNumber(GSM_SMSDConfig * Config, SQL_result *res, unsigned int field)
  48 {
  49         SQLRETURN ret;
  50         SQLINTEGER value;
  51
  52         ret = SQLGetData(res->odbc, field + 1, SQL_C_SLONG, &value, 0, NULL);
  53         if (!SQL_SUCCEEDED(ret)) {
  54                 SMSDODBC_LogError(Config, ret, SQL_HANDLE_STMT, res->odbc, "SQLGetData(long) failed");
  55                 return -1;
  56         }
  57         return value;
  58 }
  59
  60 time_t SMSDODBC_GetDate(GSM_SMSDConfig * Config, SQL_result *res, unsigned int field)
  61 {
  62         SQL_TIMESTAMP_STRUCT sqltime;
  63         GSM_DateTime DT;
  64         SQLRETURN ret;
  65
  66         ret = SQLGetData(res->odbc, field + 1, SQL_C_TYPE_TIMESTAMP, &sqltime, 0, NULL);
  67         if (!SQL_SUCCEEDED(ret)) {
  68                 SMSDODBC_LogError(Config, ret, SQL_HANDLE_STMT, res->odbc, "SQLGetData(timestamp) failed");
  69                 return -1;
  70         }
  71
  72         DT.Year = sqltime.year;
  73         DT.Month = sqltime.month;
  74         DT.Day = sqltime.day;
  75         DT.Hour = sqltime.hour;
  76         DT.Minute = sqltime.minute;
  77         DT.Second = sqltime.second;
  78
  79         return Fill_Time_T(DT);
  80 }
  81
  82 const char *SMSDODBC_GetString(GSM_SMSDConfig * Config, SQL_result *res, unsigned int field)
  83 {
  84         SQLLEN sqllen;
  85         int size;
  86         SQLRETURN ret;
  87         char shortbuffer[1];
  88
  89         if (field > SMSD_ODBC_MAX_RETURN_STRINGS) {
  90                 SMSD_Log(DEBUG_ERROR, Config, "Field %d returning NULL, too many fields!", field);
  91                 return NULL;
  92         }
  93
  94         /* Figure out string length */
  95         ret = SQLGetData(res->odbc, field + 1, SQL_C_CHAR, shortbuffer, 0, &sqllen);
  96         if (!SQL_SUCCEEDED(ret)) {
  97                 SMSDODBC_LogError(Config, ret, SQL_HANDLE_STMT, res->odbc, "SQLGetData(string,0) failed");
  98                 return NULL;
  99         }
 100
 101         /*
 102          * This hack seems to be needed to avoid type breakage on Win64, don't ask me why.
 103          *
 104          * Might be actually bug in MinGW compiler, but when using SQLLEN type bellow
 105          * anything fails (it does not match to SQL_NULL_DATA and realloc always fails).
 106          */
 107         size = sqllen;
 108
 109         /* Did not we get NULL? */
 110         if (size == SQL_NULL_DATA) {
 111                 SMSD_Log(DEBUG_SQL, Config, "Field %d returning NULL", field);
 112                 return NULL;
 113         }
 114
 115         /* Allocate string */
 116         Config->conn.odbc.retstr[field] = realloc(Config->conn.odbc.retstr[field], size + 1);
 117         if (Config->conn.odbc.retstr[field] == NULL) {
 118                 SMSD_Log(DEBUG_ERROR, Config, "Field %d returning NULL, failed to allocate %d bytes of memory", field, size + 1);
 119                 return NULL;
 120         }
 121
 122         /* Actually grab result from database */
 123         ret = SQLGetData(res->odbc, field + 1, SQL_C_CHAR, Config->conn.odbc.retstr[field], size + 1, &sqllen);
 124         if (!SQL_SUCCEEDED(ret)) {
 125                 SMSDODBC_LogError(Config, ret, SQL_HANDLE_STMT, res->odbc, "SQLGetData(string) failed");
 126                 return NULL;
 127         }
 128
 129         SMSD_Log(DEBUG_SQL, Config, "Field %d returning string \"%s\"", field, Config->conn.odbc.retstr[field]);
 130
 131         return Config->conn.odbc.retstr[field];
 132 }
 133
 134 gboolean SMSDODBC_GetBool(GSM_SMSDConfig * Config, SQL_result *res, unsigned int field)
 135 {
 136         long long intval;
 137         const char * charval;
 138
 139         /* Try to get numeric value first */
 140         intval = SMSDODBC_GetNumber(Config, res, field);
 141         if (intval == -1) {
 142                 /* If that fails, fall back to string and parse it */
 143                 charval = SMSDODBC_GetString(Config, res, field);
 144                 return GSM_StringToBool(charval);
 145         }
 146         return intval ? TRUE : FALSE;
 147 }
 148
 149 /* Disconnects from a database */
 150 void SMSDODBC_Free(GSM_SMSDConfig * Config)
 151 {
 152         int field;
 153
 154         SQLDisconnect(Config->conn.odbc.dbc);
 155         SQLFreeHandle(SQL_HANDLE_ENV, Config->conn.odbc.env);
 156
 157         for (field = 0; field < SMSD_ODBC_MAX_RETURN_STRINGS; field++) {
 158                 if (Config->conn.odbc.retstr[field] != NULL) {
 159                         free(Config->conn.odbc.retstr[field]);
 160                         Config->conn.odbc.retstr[field] = NULL;
 161                 }
 162         }
 163 }
 164
 165 /* Connects to database */
 166 static SQL_Error SMSDODBC_Connect(GSM_SMSDConfig * Config)
 167 {
 168         SQLRETURN ret;
 169         int field;
 170         char driver_name[1000];
 171         SQLSMALLINT len;
 172
 173         for (field = 0; field < SMSD_ODBC_MAX_RETURN_STRINGS; field++) {
 174                 Config->conn.odbc.retstr[field] = NULL;
 175         }
 176
 177         ret = SQLAllocHandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &Config->conn.odbc.env);
 178         if (!SQL_SUCCEEDED(ret)) {
 179                 SMSDODBC_LogError(Config, ret, SQL_HANDLE_ENV, Config->conn.odbc.env, "SQLAllocHandle(ENV) failed");
 180                 return SQL_FAIL;
 181         }
 182
 183         ret = SQLSetEnvAttr (Config->conn.odbc.env, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
 184         if (!SQL_SUCCEEDED(ret)) {
 185                 SMSDODBC_LogError(Config, ret, SQL_HANDLE_ENV, Config->conn.odbc.env, "SQLSetEnvAttr failed");
 186                 return SQL_FAIL;
 187         }
 188
 189         ret = SQLAllocHandle (SQL_HANDLE_DBC, Config->conn.odbc.env, &Config->conn.odbc.dbc);
 190         if (!SQL_SUCCEEDED(ret)) {
 191                 SMSDODBC_LogError(Config, ret, SQL_HANDLE_ENV, Config->conn.odbc.env, "SQLAllocHandle(DBC) failed");
 192                 return SQL_FAIL;
 193         }
 194
 195         ret = SQLConnect(Config->conn.odbc.dbc,
 196                           (SQLCHAR*)Config->host, SQL_NTS,
 197                           (SQLCHAR*)Config->user, SQL_NTS,
 198                           (SQLCHAR*)Config->password, SQL_NTS);
 199         if (!SQL_SUCCEEDED(ret)) {
 200                 SMSDODBC_LogError(Config, ret, SQL_HANDLE_DBC, Config->conn.odbc.dbc, "SQLConnect failed");
 201                 return SQL_FAIL;
 202         }
 203
 204         ret = SQLGetInfo(Config->conn.odbc.dbc, SQL_DRIVER_NAME, driver_name, sizeof(driver_name), &len);
 205         if (!SQL_SUCCEEDED(ret)) {
 206                 SMSDODBC_LogError(Config, ret, SQL_HANDLE_DBC, Config->conn.odbc.dbc, "SQLGetInfo failed");
 207                 return SQL_FAIL;
 208         } else{
 209                 SMSD_Log(DEBUG_NOTICE, Config, "Connected to driver %s", driver_name);
 210         }
 211
 212
 213         return SQL_OK;
 214 }
 215
 216 static SQL_Error SMSDODBC_Query(GSM_SMSDConfig * Config, const char *query, SQL_result * res)
 217 {
 218         SQLRETURN ret;
 219
 220         ret = SQLAllocHandle(SQL_HANDLE_STMT, Config->conn.odbc.dbc, &res->odbc);
 221         if (!SQL_SUCCEEDED(ret)) {
 222                 return SQL_FAIL;
 223         }
 224
 225         ret = SQLExecDirect (res->odbc, (SQLCHAR*)query, SQL_NTS);
 226         /*
 227          * If SQLExecDirect executes a searched update, insert, or delete
 228          * statement that does not affect any rows at the data source, the call
 229          * to SQLExecDirect returns SQL_NO_DATA.
 230          */
 231         if (SQL_SUCCEEDED(ret) || ret == SQL_NO_DATA) {
 232                 return SQL_OK;
 233         }
 234
 235         SMSDODBC_LogError(Config, ret, SQL_HANDLE_STMT, res->odbc, "SQLExecDirect failed");
 236         return SQL_FAIL;
 237 }
 238
 239 /* free sql results */
 240 void SMSDODBC_FreeResult(GSM_SMSDConfig * Config, SQL_result *res)
 241 {
 242         SQLFreeHandle (SQL_HANDLE_STMT, res->odbc);
 243 }
 244
 245 /* set pointer to next row */
 246 int SMSDODBC_NextRow(GSM_SMSDConfig * Config, SQL_result *res)
 247 {
 248         SQLRETURN ret;
 249
 250         ret = SQLFetch(res->odbc);
 251
 252         if (!SQL_SUCCEEDED(ret)) {
 253                 if (ret != SQL_NO_DATA) {
 254                         SMSDODBC_LogError(Config, ret, SQL_HANDLE_STMT, res->odbc, "SQLFetch failed");
 255                 }
 256                 return 0;
 257         }
 258         return 1;
 259 }
 260
 261 /* quote strings */
 262 char * SMSDODBC_QuoteString(GSM_SMSDConfig * Config, const char *string)
 263 {
 264         char *encoded_text = NULL;
 265         size_t i, len, pos = 0;
 266         char quote = '"';
 267
 268         const char *driver_name;
 269
 270         if (Config->sql != NULL) {
 271                 driver_name = Config->sql;
 272         } else {
 273                 driver_name = Config->driver;
 274         }
 275
 276         if (strcasecmp(driver_name, "access") == 0) {
 277                 quote = '\'';
 278         }
 279
 280         len = strlen(string);
 281
 282         encoded_text = (char *)malloc((len * 2) + 3);
 283         encoded_text[pos++] = quote;
 284         for (i = 0; i < len; i++) {
 285                 if (string[i] == quote || string[i] == '\\') {
 286                         encoded_text[pos++] = '\\';
 287                 }
 288                 encoded_text[pos++] = string[i];
 289         }
 290         encoded_text[pos++] = quote;
 291         encoded_text[pos] = '\0';
 292         return encoded_text;
 293 }
 294
 295 /* LAST_INSERT_ID */
 296 unsigned long long SMSDODBC_SeqID(GSM_SMSDConfig * Config, const char *id)
 297 {
 298         SQLRETURN ret;
 299         SQLHSTMT stmt;
 300         SQLINTEGER value;
 301
 302         ret = SQLAllocHandle(SQL_HANDLE_STMT, Config->conn.odbc.dbc, &stmt);
 303         if (!SQL_SUCCEEDED(ret)) {
 304                 return 0;
 305         }
 306
 307         ret = SQLExecDirect (stmt, (SQLCHAR*)"SELECT @@IDENTITY", SQL_NTS);
 308         if (!SQL_SUCCEEDED(ret)) {
 309                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
 310                 return 0;
 311         }
 312
 313         ret = SQLFetch(stmt);
 314         if (!SQL_SUCCEEDED(ret)) {
 315                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
 316                 return 0;
 317         }
 318
 319         ret = SQLGetData(stmt, 1, SQL_C_SLONG, &value, 0, NULL);
 320         if (!SQL_SUCCEEDED(ret)) {
 321                 SQLFreeHandle (SQL_HANDLE_STMT, stmt);
 322                 return 0;
 323         }
 324         SQLFreeHandle (SQL_HANDLE_STMT, stmt);
 325
 326         return value;
 327 }
 328
 329 unsigned long SMSDODBC_AffectedRows(GSM_SMSDConfig * Config, SQL_result *res)
 330 {
 331         SQLRETURN ret;
 332         SQLLEN count;
 333
 334         ret = SQLRowCount (res->odbc, &count);
 335         if (!SQL_SUCCEEDED(ret)) {
 336                 SMSDODBC_LogError(Config, ret, SQL_HANDLE_DBC, Config->conn.odbc.dbc, "SQLRowCount failed");
 337                 return 0;
 338         }
 339         return count;
 340 }
 341
 342 struct GSM_SMSDdbobj SMSDODBC = {
 343         SMSDODBC_Connect,
 344         SMSDODBC_Query,
 345         SMSDODBC_Free,
 346         SMSDODBC_FreeResult,
 347         SMSDODBC_NextRow,
 348         SMSDODBC_SeqID,
 349         SMSDODBC_AffectedRows,
 350         SMSDODBC_GetString,
 351         SMSDODBC_GetNumber,
 352         SMSDODBC_GetDate,
 353         SMSDODBC_GetBool,
 354         SMSDODBC_QuoteString,
 355 };
 356
 357 /* How should editor hadle tabs in this file? Add editor commands here.
 358  * vim: noexpandtab sw=8 ts=8 sts=8:
 359  */