2 * @File odbcapiagent.cpp
4 * @Author Lukas Zeller (luz@synthesis.ch)
7 * ODBC based agent (client or server session) implementation
9 * Copyright (c) 2001-2009 by Synthesis AG (www.synthesis.ch)
11 * @Date 2005-10-06 : luz : created from odbcdbagent
14 #include "prefix_file.h"
19 #include "odbcapids.h"
20 #include "odbcapiagent.h"
23 #include "syncsessiondispatch.h"
29 // Support for SySync Diagnostic Tool
32 // execute SQL command
33 int execSQL(int argc, const char *argv[])
37 CONSOLEPRINTF((" execsql <sql statement> [<maxrows>]"));
38 CONSOLEPRINTF((" Execute SQL statement via ODBC on the server database"));
42 TODBCApiAgent *odbcagentP = NULL;
43 const char *sql = NULL;
45 // show only one row by default
50 CONSOLEPRINTF(("argument containing SQL statement required"));
55 // second arg is maxrows
56 StrToLong(argv[1],maxrows);
59 // get ODBC session to work with
60 odbcagentP = dynamic_cast<TODBCApiAgent *>(
61 static_cast<TSyncSessionDispatch *>(getSyncAppBase())->getSySyToolSession()
64 CONSOLEPRINTF(("Config does not contain an ODBC server section"));
69 // - show DB where we will exec it
70 CONSOLEPRINTF(("Connecting to ODBC with connection string = '%s'",odbcagentP->fConfigP->fDBConnStr.c_str()));
73 SQLHSTMT statement=odbcagentP->newStatementHandle(odbcagentP->getODBCConnectionHandle());
76 const int paramsiz=100;
77 SQLCHAR paramval[paramsiz];
78 SQLINTEGER lenorind=SQL_NULL_DATA;
82 SQL_PARAM_OUTPUT, // inout
83 SQL_C_CHAR, // we want it as string
84 SQL_INTEGER, // parameter type
85 paramsiz, // column size
87 ¶mval, // parameter value
88 paramsiz, // value buffer size
89 (SQLLEN*)&lenorind // length or indicator
91 odbcagentP->checkStatementError(res,statement);
94 res = SafeSQLExecDirect(
99 odbcagentP->checkStatementError(res,statement);
101 if (lenorind!=SQL_NULL_DATA || lenorind!=SQL_NO_TOTAL) {
102 CONSOLEPRINTF(("Returned parameter = '%s'",paramval));
105 // - get number of result columns
106 SQLSMALLINT numcolumns;
107 SQLSMALLINT stringlength;
108 // - get number of columns
109 res = SafeSQLNumResultCols(statement,&numcolumns);
110 odbcagentP->checkStatementError(res,statement);
111 // - fetch result row(s)
115 res=SafeSQLFetch(statement);
116 if (!odbcagentP->checkStatementHasData(res,statement))
118 // we have a row to show
119 if (rownum>=maxrows) {
120 CONSOLEPRINTF(("\nMore rows in result than allowed to display"));
125 CONSOLEPRINTF(("\nResult Row #%d: ",rownum));
127 for (int i=1; i<=numcolumns; i++) {
128 // - get name of the column
129 const int maxnamelen=100;
130 SQLCHAR colname[maxnamelen];
131 SQLSMALLINT colnamelen;
133 res=SQLColAttribute (
134 statement, // statement handle
136 SQL_DESC_BASE_COLUMN_NAME, // return column base name
137 colname, // col name return buffer
138 maxnamelen, // col name return buffer size
139 &colnamelen, // no string length expected
140 (SQLLEN*)&dummy // dummy
142 if (res!=SQL_SUCCESS) {
143 strcpy((char *)colname,"<error getting name>");
145 // - get data of the column in this row
146 const int maxdatalen=100;
147 SQLCHAR databuf[maxdatalen];
148 SQLINTEGER actualLength;
150 statement, // statement handle
152 SQL_C_CHAR, // target type: string
153 &databuf, // Target Value buffer pointer
154 maxdatalen, // max size of value
155 (SQLLEN*)&actualLength
157 if (res!=SQL_SUCCESS) break; // no more columns
158 if (actualLength==SQL_NULL_DATA || actualLength==SQL_NO_TOTAL) {
159 strcpy((char *)databuf,"<NULL>");
161 CONSOLEPRINTF((" %3d. %20s : %s",i,colname,databuf));
165 SafeSQLCloseCursor(statement);
166 // dispose statement handle
167 SafeSQLFreeHandle(SQL_HANDLE_STMT,statement);
169 catch (exception &e) {
170 // dispose statement handle
171 SafeSQLCloseCursor(statement);
172 SafeSQLFreeHandle(SQL_HANDLE_STMT,statement);
174 CONSOLEPRINTF(("ODBC Error : %s",e.what()));
178 // dispose statement handle
179 SafeSQLCloseCursor(statement);
180 SafeSQLFreeHandle(SQL_HANDLE_STMT,statement);
182 CONSOLEPRINTF(("ODBC caused unknown exception"));
188 #endif // SYSYNC_TOOL
193 // names for transaction isolation modes
194 const char *TxnIsolModeNames[numTxnIsolModes] = {
195 // Note: these MUST be in the same order as
196 // Win32/ODBC defined SQL_TXN_READ_UNCOMMITTED..SQL_TXN_SERIALIZABLE
197 // bits appear in the transaction bitmasks.
210 TOdbcAgentConfig::TOdbcAgentConfig(TConfigElement *aParentElement) :
211 TCustomAgentConfig(aParentElement)
214 } // TOdbcAgentConfig::TOdbcAgentConfig
217 TOdbcAgentConfig::~TOdbcAgentConfig()
220 } // TOdbcAgentConfig::~TOdbcAgentConfig
224 void TOdbcAgentConfig::clear(void)
227 #ifdef ODBCAPI_SUPPORT
228 // - ODBC data source
232 #ifdef SCRIPT_SUPPORT
233 fAfterConnectScript.erase();
235 // - usually, SQLSetConnectAttr() is not problematic
236 fNoConnectAttrs=false;
237 // - use medium timeout by default
240 fODBCTxnMode=txni_default;
241 // - cursor library usage
242 fUseCursorLib=false; // use driver (this is also the ODBC default)
244 fGetDeviceSQL.erase();
245 fNewDeviceSQL.erase();
246 fSaveNonceSQL.erase();
247 fSaveInfoSQL.erase();
248 fSaveDevInfSQL.erase();
249 fLoadDevInfSQL.erase();
252 fClearTextPw=true; // otherwise, Nonce auth is not possible
253 fMD5UserPass=false; // exclusive with ClearTextPw
256 fGetCurrentDateTimeSQL.erase();
257 // - statement to save log info
258 fWriteLogSQL.erase();
259 #endif // ODBCAPI_SUPPORT
260 fQuotingMode=qm_duplsingle; // default to what was hard-coded before it became configurable in 2.1.1.5
263 } // TOdbcAgentConfig::clear
266 #ifdef SCRIPT_SUPPORT
269 // ODBC agent specific script functions
270 // ====================================
273 class TODBCCommonFuncs {
276 // string DBLITERAL(variant value, string dbfieldtype)
277 static void func_DBLiteral(TItemField *&aTermP, TScriptContext *aFuncContextP)
279 TODBCApiAgent *agentP = static_cast<TODBCApiAgent *>(aFuncContextP->getCallerContext());
280 string tname,literal;
281 TItemField *fieldP = aFuncContextP->getLocalVar(0); // the field to be converted
282 aFuncContextP->getLocalVar(1)->getAsString(tname); // DB type string
283 // search DB type, default to string
285 TDBFieldType dbfty=dbft_string;
286 if (StrToEnum(DBFieldTypeNames,numDBfieldTypes,ty,tname.c_str()))
287 dbfty=(TDBFieldType)ty;
288 // now create literal
290 TOdbcAgentConfig *cfgP = static_cast<TODBCApiAgent *>(aFuncContextP->getCallerContext())->fConfigP;
295 if (agentP->fScriptContextDatastore) {
296 TOdbcDSConfig *dsCfgP = static_cast<TOdbcDSConfig *>(
297 static_cast<TODBCApiDS *>(agentP->fScriptContextDatastore)->fConfigP
299 tctx=dsCfgP->fDataTimeZone;
300 chs=dsCfgP->fDataCharSet;
301 lem=dsCfgP->fDataLineEndMode;
302 qm=dsCfgP->fQuotingMode;
305 tctx=cfgP->fCurrentDateTimeZone;
306 chs=cfgP->fDataCharSet;
307 lem=cfgP->fDataLineEndMode;
308 qm=cfgP->fQuotingMode;
311 agentP->appendFieldValueLiteral(
312 *fieldP,dbfty,0,literal,
317 aTermP->setAsString(literal);
321 #ifdef ODBCAPI_SUPPORT
323 // SETDBCONNECTSTRING(string dbconnectstring)
324 // sets DB connect string, useful in <logininitscript> to switch database
326 static void func_SetDBConnectString(TItemField *&aTermP, TScriptContext *aFuncContextP)
328 TODBCApiAgent *agentP = static_cast<TODBCApiAgent *>(aFuncContextP->getCallerContext());
329 // - close the current script statement (if any)
330 agentP->commitAndCloseScriptStatement();
331 // - close the current connection (if any)
332 agentP->closeODBCConnection(agentP->fODBCConnectionHandle);
333 // - assign new DB connection string which will be used for next connection
334 aFuncContextP->getLocalVar(0)->getAsString(agentP->fSessionDBConnStr); // override default DB connect string from config
335 } // func_SetDBConnectString
338 // SETDBPASSWORD(string password)
339 // sets password, useful when using SETDBCONNECTSTRING() to switch databases
341 static void func_SetDBPassword(TItemField *&aTermP, TScriptContext *aFuncContextP)
343 TODBCApiAgent *agentP = static_cast<TODBCApiAgent *>(aFuncContextP->getCallerContext());
344 // - assign new DB connection password which will be used for next connection
345 aFuncContextP->getLocalVar(0)->getAsString(agentP->fSessionDBPassword); // override default DB password from config
346 } // func_SetDBPassword
348 #endif // ODBCAPI_SUPPORT
352 // integer SQLEXECUTE(string statement)
353 // executes statement, returns 0 or ODBC error code
354 static void func_SQLExecute(TItemField *&aTermP, TScriptContext *aFuncContextP)
357 #ifdef ODBCAPI_SUPPORT
360 TODBCApiAgent *agentP = static_cast<TODBCApiAgent *>(aFuncContextP->getCallerContext());
361 TODBCApiDS *datastoreP = static_cast<TODBCApiDS *>(agentP->fScriptContextDatastore);
362 aFuncContextP->getLocalVar(0)->getAsString(sql); // get SQL to be executed
363 bool ok = true; // assume ok
364 SQLHSTMT stmt = SQL_NULL_HANDLE;
367 // do datastore-level substitutions and parameter mapping
368 datastoreP->resetSQLParameterMaps();
369 datastoreP->DoDataSubstitutions(
371 datastoreP->fConfigP->fFieldMappings.fFieldMapList,
373 false, // not for write
374 false, // not for update
375 aFuncContextP->fTargetItemP // item in this context, if any
377 #ifdef SQLITE_SUPPORT
378 if (!datastoreP->fUseSQLite)
381 #ifdef ODBCAPI_SUPPORT
382 stmt = agentP->getScriptStatement();
383 #endif // ODBCAPI_SUPPORT
387 #ifdef ODBCAPI_SUPPORT
388 // do agent-level substitutions and parameter mapping
389 stmt=agentP->getScriptStatement();
390 agentP->resetSQLParameterMaps();
391 agentP->DoSQLSubstitutions(sql);
393 // %%% no session level SQLEXECUTE for now
394 aTermP->setAsBoolean(false);
395 POBJDEBUGPUTSX(aFuncContextP->getSession(),DBG_ERROR,"SQLEXECUTE() can only be used in datastore context for non-ODBC!");
399 // execute SQL statement
401 if (POBJDEBUGTEST(aFuncContextP->getSession(),DBG_SCRIPTS+DBG_DBAPI)) {
402 POBJDEBUGPUTSX(aFuncContextP->getSession(),DBG_DBAPI,"SQL issued by SQLEXECUTE() script function:");
403 POBJDEBUGPUTSX(aFuncContextP->getSession(),DBG_DBAPI,sql.c_str());
406 POBJDEBUGPUTSX(aFuncContextP->getSession(),DBG_DBAPI,"SQLEXECUTE() executes a statement (hidden because script debugging is off)");
410 datastoreP->prepareSQLStatement(stmt,sql.c_str(),true,NULL);
411 datastoreP->bindSQLParameters(stmt,true);
412 // execute in datastore
413 datastoreP->execSQLStatement(stmt, sql, true, NULL, true);
414 // do datastore-level parameter mapping
415 datastoreP->saveAndCleanupSQLParameters(stmt,true);
417 #ifdef ODBCAPI_SUPPORT
419 agentP->bindSQLParameters(stmt);
421 res = SafeSQLExecDirect(
423 (SQLCHAR *)sql.c_str(),
426 agentP->checkStatementHasData(res,stmt); // treat NO_DATA error as ok
427 // do agent-level parameter mapping
428 agentP->saveAndCleanupSQLParameters(stmt);
430 #endif // ODBCAPI_SUPPORT
432 catch (exception &e) {
433 POBJDEBUGPRINTFX(aFuncContextP->getSession(),DBG_ERROR,(
434 "SQLEXECUTE() caused Error: %s",
440 aTermP->setAsBoolean(ok);
441 }; // func_SQLExecute
444 // integer SQLFETCHROW()
445 // fetches next row, returns true if data found
446 static void func_SQLFetchRow(TItemField *&aTermP, TScriptContext *aFuncContextP)
448 #ifdef ODBCAPI_SUPPORT
451 TODBCApiAgent *agentP = static_cast<TODBCApiAgent *>(aFuncContextP->getCallerContext());
452 TODBCApiDS *datastoreP = static_cast<TODBCApiDS *>(agentP->fScriptContextDatastore);
453 bool ok = true; // assume ok
456 ok=datastoreP->fetchNextRow(agentP->fScriptStatement,true); // always data access
458 #ifdef ODBCAPI_SUPPORT
460 // - fetch result row
461 res=SafeSQLFetch(agentP->getScriptStatement());
462 ok=agentP->checkStatementHasData(res,agentP->getScriptStatement());
466 catch (exception &e) {
467 POBJDEBUGPRINTFX(aFuncContextP->getSession(),DBG_ERROR,(
468 "SQLFETCHROW() caused Error: %s",
474 aTermP->setAsBoolean(ok);
475 }; // func_SQLFetchRow
478 // integer SQLGETCOLUMN(integer index, &field, string dbtype)
479 // gets value of next column into specified variable
480 // returns true if successful
481 static void func_SQLGetColumn(TItemField *&aTermP, TScriptContext *aFuncContextP)
483 TODBCApiAgent *agentP = static_cast<TODBCApiAgent *>(aFuncContextP->getCallerContext());
484 TODBCApiDS *datastoreP = static_cast<TODBCApiDS *>(agentP->fScriptContextDatastore);
486 bool ok = true; // assume ok
488 sInt16 colindex = aFuncContextP->getLocalVar(0)->getAsInteger();
489 // get field reference
490 TItemField *fldP = aFuncContextP->getLocalVar(1);
491 // get DB type for field
493 aFuncContextP->getLocalVar(2)->getAsString(tname); // DB type string
495 TDBFieldType dbfty=dbft_string; // default to string
496 if (StrToEnum(DBFieldTypeNames,numDBfieldTypes,ty,tname.c_str()))
497 dbfty=(TDBFieldType)ty;
499 TOdbcAgentConfig *cfgP = agentP->fConfigP;
502 #ifdef SQLITE_SUPPORT
507 TOdbcDSConfig *dsCfgP = static_cast<TOdbcDSConfig *>(datastoreP->fConfigP);
508 tctx=dsCfgP->fDataTimeZone;
509 chs=dsCfgP->fDataCharSet;
510 uzo=dsCfgP->fUserZoneOutput;
511 #ifdef SQLITE_SUPPORT
512 sqlite=datastoreP->fUseSQLite;
516 tctx=cfgP->fCurrentDateTimeZone;
517 chs=cfgP->fDataCharSet;
519 #ifdef SQLITE_SUPPORT
520 sqlite=false; // no SQLite support at agent level
525 #ifdef SQLITE_SUPPORT
526 if (sqlite && datastoreP) {
527 // - get column value as field
528 if (!agentP->getSQLiteColValueAsField(
529 datastoreP->fSQLiteStmtP,
530 colindex-1, // SQLITE colindex starts at 0, not 1 like in ODBC
534 fldP->unAssign(); // NULL -> unassigned
539 #ifdef ODBCAPI_SUPPORT
540 // - get column value as field
541 if (!agentP->getColumnValueAsField(
542 agentP->getScriptStatement(), colindex, dbfty, fldP,
545 fldP->unAssign(); // NULL -> unassigned
549 catch (exception &e) {
550 POBJDEBUGPRINTFX(aFuncContextP->getSession(),DBG_ERROR,(
551 "SQLGETCOLUMN() caused Error: %s",
557 aTermP->setAsBoolean(ok);
558 }; // func_SQLGetColumn
562 // commits agent-level transactions
563 static void func_SQLCommit(TItemField *&aTermP, TScriptContext *aFuncContextP)
565 TODBCApiAgent *agentP = static_cast<TODBCApiAgent *>(aFuncContextP->getCallerContext());
566 TODBCApiDS *datastoreP = static_cast<TODBCApiDS *>(agentP->fScriptContextDatastore);
568 // we might need finalizing (for SQLite...)
569 datastoreP->finalizeSQLStatement(agentP->fScriptStatement, true);
571 #ifdef ODBCAPI_SUPPORT
573 agentP->commitAndCloseScriptStatement();
575 catch (exception &e) {
576 POBJDEBUGPRINTFX(aFuncContextP->getSession(),DBG_ERROR,(
577 "SQLCOMMIT() caused Error: %s",
581 #endif // ODBCAPI_SUPPORT
586 // rollback agent-level transactions
587 static void func_SQLRollback(TItemField *&aTermP, TScriptContext *aFuncContextP)
589 TODBCApiAgent *agentP = static_cast<TODBCApiAgent *>(aFuncContextP->getCallerContext());
590 TODBCApiDS *datastoreP = static_cast<TODBCApiDS *>(agentP->fScriptContextDatastore);
592 // we might need finalizing (for SQLite...)
593 datastoreP->finalizeSQLStatement(agentP->fScriptStatement, true);
595 #ifdef ODBCAPI_SUPPORT
597 if (agentP->fScriptStatement!=SQL_NULL_HANDLE) {
598 // only roll back if we have used a statement at all in scripts
599 SafeSQLFreeHandle(SQL_HANDLE_STMT,agentP->fScriptStatement);
600 agentP->fScriptStatement=SQL_NULL_HANDLE;
601 SafeSQLEndTran(SQL_HANDLE_DBC,agentP->getODBCConnectionHandle(),SQL_ROLLBACK);
604 catch (exception &e) {
605 POBJDEBUGPRINTFX(aFuncContextP->getSession(),DBG_ERROR,(
606 "SQLROLLBACK() caused Error: %s",
610 #endif // ODBCAPI_SUPPORT
611 }; // func_SQLRollback
613 }; // TODBCCommonFuncs
615 const uInt8 param_DBLiteral[] = { VAL(fty_none), VAL(fty_string) };
616 const uInt8 param_OneString[] = { VAL(fty_string) };
617 const uInt8 param_SQLGetColumn[] = { VAL(fty_integer), REF(fty_none), VAL(fty_string) };
619 // builtin function defs for ODBC database and login contexts
620 const TBuiltInFuncDef ODBCAgentAndDSFuncDefs[] = {
622 { "DBLITERAL", TODBCCommonFuncs::func_DBLiteral, fty_string, 2, param_DBLiteral }, // note that there's a second version of this in ODBCDatastore
623 #ifdef ODBCAPI_SUPPORT
624 { "SETDBCONNECTSTRING", TODBCCommonFuncs::func_SetDBConnectString, fty_none, 1, param_OneString },
625 { "SETDBPASSWORD", TODBCCommonFuncs::func_SetDBPassword, fty_none, 1, param_OneString },
626 #endif // ODBCAPI_SUPPORT
627 { "SQLEXECUTE", TODBCCommonFuncs::func_SQLExecute, fty_integer, 1, param_OneString },
628 { "SQLFETCHROW", TODBCCommonFuncs::func_SQLFetchRow, fty_integer, 0, NULL },
629 { "SQLGETCOLUMN", TODBCCommonFuncs::func_SQLGetColumn, fty_integer, 3, param_SQLGetColumn },
630 { "SQLCOMMIT", TODBCCommonFuncs::func_SQLCommit, fty_none, 0, NULL },
631 { "SQLROLLBACK", TODBCCommonFuncs::func_SQLRollback, fty_none, 0, NULL }
635 #ifdef BASED_ON_BINFILE_CLIENT
637 // Binfile based version just has the SQL access functions
639 // function table for connectionscript
640 const TFuncTable ODBCAgentFuncTable = {
641 sizeof(ODBCAgentAndDSFuncDefs) / sizeof(TBuiltInFuncDef), // size of table
642 ODBCAgentAndDSFuncDefs, // table pointer
643 NULL // no chain func
648 // Full range of functions for non-binfile-based version
650 // chain from ODBC agent funcs to Custom agent Funcs
651 extern const TFuncTable CustomAgentFuncTable2;
652 static void *ODBCAgentChainFunc1(void *&aCtx)
654 // caller context remains unchanged
655 // -> no change needed
656 // next table is Custom Agent's general function table
657 return (void *)&CustomAgentFuncTable2;
658 } // ODBCAgentChainFunc1
660 // function table which is chained from login-context function table
661 const TFuncTable ODBCAgentFuncTable2 = {
662 sizeof(ODBCAgentAndDSFuncDefs) / sizeof(TBuiltInFuncDef), // size of table
663 ODBCAgentAndDSFuncDefs, // table pointer
664 ODBCAgentChainFunc1 // chain to non-ODBC specific agent Funcs
667 // chain from login context agent funcs to general agent funcs
668 extern const TFuncTable ODBCDSFuncTable2;
669 static void *ODBCAgentChainFunc(void *&aCtx)
671 // caller context remains unchanged
672 // -> no change needed
673 // next table is Agent's general function table
674 return (void *)&ODBCAgentFuncTable2;
675 } // ODBCAgentChainFunc
677 // function table for login context scripts
678 // Note: ODBC agent has no login-context specific functions, but is just using those from customImplAgent
679 const TFuncTable ODBCAgentFuncTable = {
680 sizeof(CustomAgentFuncDefs) / sizeof(TBuiltInFuncDef), // size of table
681 CustomAgentFuncDefs, // table pointer
682 ODBCAgentChainFunc // chain to general agent funcs.
686 #endif // not BASED_ON_BINFILE_CLIENT
689 // chain from agent funcs to ODBC local datastore funcs (when chained via ODBCDSFuncTable1
690 extern const TFuncTable ODBCDSFuncTable2;
691 static void *ODBCDSChainFunc1(void *&aCtx)
693 // caller context for datastore-level functions is the datastore pointer
695 aCtx = static_cast<TODBCApiAgent *>(aCtx)->fScriptContextDatastore;
696 // next table is ODBC datastore's
697 return (void *)&ODBCDSFuncTable2;
698 } // ODBCDSChainFunc1
700 // function table for linking in Custom agent level functions between ODBC agent and ODBC DS level functions
701 const TFuncTable ODBCAgentFuncTable3 = {
702 sizeof(CustomAgentAndDSFuncDefs) / sizeof(TBuiltInFuncDef), // size of agent's table
703 CustomAgentAndDSFuncDefs, // table pointer to agent's general purpose (non login-context specific) funcs
704 ODBCDSChainFunc1 // NOW finally chain back to ODBC datastore level DB functions
708 // chain from ODBC agent funcs to generic agent funcs and THEN back to ODBC DS funcs
709 extern const TFuncTable ODBCDSFuncTable2;
710 static void *ODBCDSChainFunc2(void *&aCtx)
712 // caller context remains unchanged
713 // -> no change needed
714 // next table is Agent's general function table
715 return (void *)&ODBCAgentFuncTable3;
716 } // ODBCDSChainFunc2
718 // function table which is used by ODBC datastore scripts to access agent-level funcs and then chain
719 // back to datastore level funcs
720 const TFuncTable ODBCDSFuncTable1 = {
721 sizeof(ODBCAgentAndDSFuncDefs) / sizeof(TBuiltInFuncDef), // size of agent's table
722 ODBCAgentAndDSFuncDefs, // table pointer to agent's general purpose (non login-context specific) funcs
723 ODBCDSChainFunc2 // first chain to Custom Agent level functions, but then back to ODBC datastore level DB functions
729 // config element parsing
730 bool TOdbcAgentConfig::localStartElement(const char *aElementName, const char **aAttributes, sInt32 aLine)
732 // checking the elements
733 #ifdef ODBCAPI_SUPPORT
735 if (strucmp(aElementName,"datasource")==0)
736 expectString(fDataSource);
737 else if (strucmp(aElementName,"dbuser")==0)
738 expectString(fUsername);
739 else if (strucmp(aElementName,"dbpass")==0)
740 expectString(fPassword);
741 else if (strucmp(aElementName,"dbconnectionstring")==0)
742 expectString(fDBConnStr);
744 else if (strucmp(aElementName,"dbtimeout")==0)
745 expectInt32(fODBCTimeout);
746 // - transaction mode
747 else if (strucmp(aElementName,"transactionmode")==0)
748 expectEnum(sizeof(fODBCTxnMode),&fODBCTxnMode,TxnIsolModeNames,numTxnIsolModes);
749 // - preventing use of SQLSetConnectAttr()
750 else if (strucmp(aElementName,"preventconnectattrs")==0)
751 expectBool(fNoConnectAttrs);
752 // - cursor library usage
753 else if (strucmp(aElementName,"usecursorlib")==0)
754 expectBool(fUseCursorLib);
755 #ifdef SCRIPT_SUPPORT
756 else if (strucmp(aElementName,"afterconnectscript")==0)
757 expectScript(fAfterConnectScript,aLine,getAgentFuncTableP());
761 else if (strucmp(aElementName,"getdevicesql")==0)
762 expectString(fGetDeviceSQL);
763 else if (strucmp(aElementName,"newdevicesql")==0)
764 expectString(fNewDeviceSQL);
765 else if (strucmp(aElementName,"savenoncesql")==0)
766 expectString(fSaveNonceSQL);
767 else if (strucmp(aElementName,"saveinfosql")==0)
768 expectString(fSaveInfoSQL);
769 else if (strucmp(aElementName,"savedevinfsql")==0)
770 expectString(fSaveDevInfSQL);
771 else if (strucmp(aElementName,"loaddevinfsql")==0)
772 expectString(fLoadDevInfSQL);
774 else if (strucmp(aElementName,"userkeysql")==0)
775 expectString(fUserKeySQL);
776 else if (strucmp(aElementName,"cleartextpw")==0)
777 expectBool(fClearTextPw);
778 else if (strucmp(aElementName,"md5userpass")==0)
779 expectBool(fMD5UserPass);
780 else if (strucmp(aElementName,"md5hex")==0)
781 expectBool(fMD5UPAsHex);
782 // - database time SQL
783 else if (strucmp(aElementName,"timestampsql")==0)
784 expectString(fGetCurrentDateTimeSQL);
785 // - statement to save log info
786 else if (strucmp(aElementName,"writelogsql")==0)
787 expectString(fWriteLogSQL);
789 #endif // HAS_SQL_ADMIN
790 #endif // ODBCAPI_SUPPORT
792 if (strucmp(aElementName,"quotingmode")==0)
793 expectEnum(sizeof(fQuotingMode),&fQuotingMode,quotingModeNames,numQuotingModes);
796 return inherited::localStartElement(aElementName,aAttributes,aLine);
799 } // TOdbcAgentConfig::localStartElement
803 void TOdbcAgentConfig::localResolve(bool aLastPass)
806 #ifdef ODBCAPI_SUPPORT
807 // check for required settings
808 // - create fDBConnStr from dsn,user,pw if none specified explicitly
809 if (fDBConnStr.empty()) {
810 // Note: we do not add the password here as fDBConnStr is shown in logs.
811 StringObjPrintf(fDBConnStr,"DSN=%s;UID=%s;",fDataSource.c_str(),fUsername.c_str());
814 if (fClearTextPw && fMD5UserPass)
815 throw TConfigParseException("only one of 'cleartextpw' and 'md5userpass' can be set");
818 if (fAutoNonce && !fClearTextPw && !fMD5UserPass)
819 throw TConfigParseException("if 'autononce' is set, 'cleartextpw' or 'md5userpass' MUST be set as well");
821 #endif // SYSYNC_SERVER
824 #ifndef NO_LOCAL_DBLOGIN
825 if (fNoLocalDBLogin && !fUserKeySQL.empty())
826 throw TConfigParseException("'nolocaldblogin' is not allowed when 'userkeysql' is defined");
829 #endif // SYSYNC_CLIENT
830 #endif // HAS_SQL_ADMIN
831 #endif // ODBCAPI_SUPPORT
834 // - Note: this resolves the ancestor's scripts first
835 inherited::localResolve(aLastPass);
836 } // TOdbcAgentConfig::localResolve
840 #ifdef SCRIPT_SUPPORT
842 void TOdbcAgentConfig::ResolveAPIScripts(void)
844 #ifdef ODBCAPI_SUPPORT
845 // afterconnect script has it's own context as it might be called nested in other scripts (e.g. triggered by SQLEXECUTE)
846 TScriptContext *ctxP = NULL;
847 TScriptContext::resolveScript(getSyncAppBase(),fAfterConnectScript,ctxP,NULL);
848 if (ctxP) delete ctxP;
850 } // TOdbcAgentConfig::localResolve
854 /* public TODBCApiAgent members */
857 // private init routine for both client and server constructor
858 TODBCApiAgent::TODBCApiAgent(TSyncAppBase *aAppBaseP, TSyncSessionHandle *aSessionHandleP, cAppCharP aSessionID) :
859 TCustomImplAgent(aAppBaseP, aSessionHandleP, aSessionID)
862 #ifdef ODBCAPI_SUPPORT
863 fODBCConnectionHandle = SQL_NULL_HANDLE;
864 fODBCEnvironmentHandle = SQL_NULL_HANDLE;
865 #ifdef SCRIPT_SUPPORT
866 fScriptStatement = SQL_NULL_HANDLE;
867 fAfterConnectContext = NULL;
869 #endif // ODBCAPI_SUPPORT
870 // get config for agent and save direct link to agent config for easy reference
871 fConfigP = static_cast<TOdbcAgentConfig *>(getRootConfig()->fAgentConfigP);
872 // Note: Datastores are already created from config
873 #ifdef ODBCAPI_SUPPORT
874 // - assign default DB connection string and password
875 fSessionDBConnStr = fConfigP->fDBConnStr;
876 fSessionDBPassword = fConfigP->fPassword;
877 #ifdef SCRIPT_SUPPORT
878 // - rebuild afterconnect script
879 TScriptContext::rebuildContext(getSyncAppBase(),fConfigP->fAfterConnectScript,fAfterConnectContext,this,true);
882 } // TODBCApiAgent::TODBCApiAgent
886 TODBCApiAgent::~TODBCApiAgent()
888 // make sure everything is terminated BEFORE destruction of hierarchy begins
890 } // TODBCApiAgent::~TODBCApiAgent
894 void TODBCApiAgent::TerminateSession()
899 // Note that the following will happen BEFORE destruction of
900 // individual datastores, so make sure datastore have their ODBC
901 // stuff finished before disposing environment
902 InternalResetSession();
903 #ifdef ODBCAPI_SUPPORT
904 #ifdef SCRIPT_SUPPORT
905 // get rid of afterconnect context
906 if (fAfterConnectContext) delete fAfterConnectContext;
909 if (fODBCEnvironmentHandle!=SQL_NULL_HANDLE) {
910 // release the enviroment
911 res=SafeSQLFreeHandle(SQL_HANDLE_ENV,fODBCEnvironmentHandle);
912 if (getODBCError(res,msg,state,SQL_HANDLE_ENV,fODBCEnvironmentHandle)) {
913 DEBUGPRINTFX(DBG_ERROR,("~TODBCApiAgent: SQLFreeHandle(ENV) failed: %s",msg.c_str()));
915 fODBCEnvironmentHandle=SQL_NULL_HANDLE;
918 // Make sure datastores know that the agent will go down soon
919 announceDestruction();
921 inherited::TerminateSession();
922 } // TODBCApiAgent::TerminateSession
927 void TODBCApiAgent::InternalResetSession(void)
929 // reset all datastores now to make sure ODBC is reset before we close the
930 // global connection and the environment handle (if called by destructor)!
931 // (Note: TerminateDatastores() will be called again by ancestors)
932 TerminateDatastores();
933 #ifdef ODBCAPI_SUPPORT
934 #ifdef SCRIPT_SUPPORT
935 // commit connection (possible scripted statements)
936 commitAndCloseScriptStatement();
938 // clear parameter maps
939 resetSQLParameterMaps();
940 // close session level connection if not already closed
941 closeODBCConnection(fODBCConnectionHandle);
942 #endif // ODBCAPI_SUPPORT
943 } // TODBCApiAgent::InternalResetSession
947 void TODBCApiAgent::ResetSession(void)
950 InternalResetSession();
951 // let ancestor do its stuff
952 inherited::ResetSession();
953 } // TODBCApiAgent::ResetSession
956 #ifdef ODBCAPI_SUPPORT
957 #ifdef SCRIPT_SUPPORT
959 // commit and close possibly open script statement
960 void TODBCApiAgent::commitAndCloseScriptStatement(void)
962 if (fODBCConnectionHandle!=SQL_NULL_HANDLE) {
963 // free script statement, if any still open
964 if (fScriptStatement!=SQL_NULL_HANDLE) {
965 PDEBUGPRINTFX(DBG_DBAPI+DBG_EXOTIC,("Script statement exists -> closing it now"));
966 SafeSQLFreeHandle(SQL_HANDLE_STMT,fScriptStatement);
967 fScriptStatement=SQL_NULL_HANDLE;
969 // now commit transaction
970 SafeSQLEndTran(SQL_HANDLE_DBC,fODBCConnectionHandle,SQL_COMMIT);
972 } // TODBCApiAgent::commitAndCloseScriptStatement
975 // get statement for executing scripted SQL
976 HSTMT TODBCApiAgent::getScriptStatement(void)
978 if (fScriptStatement==SQL_NULL_HANDLE) {
979 fScriptStatement=newStatementHandle(getODBCConnectionHandle());
981 return fScriptStatement;
982 } // TODBCApiAgent::getScriptStatement
985 #endif // ODBCAPI_SUPPORT
988 #ifdef ODBCAPI_SUPPORT
990 #if !defined(NO_AV_GUARDING) // && !__option(microsoft_exceptions)
992 // SEH-aware versions of ODBC calls (to avoid that crashing drivers blame our server)
993 // ==================================================================================
995 // Special exception returning the SEH code
996 TODBCSEHexception::TODBCSEHexception(uInt32 aCode)
998 StringObjPrintf(fMessage,"ODBC Driver caused SEH/AV Code=%08lX",aCode);
999 } // TODBCSEHexception::TODBCSEHexception
1003 // define what object is to be thrown
1004 #define SEH_THROWN_OBJECT TODBCSEHexception(GetExceptionCode())
1006 SQLRETURN SafeSQLAllocHandle(
1007 SQLSMALLINT HandleType,
1008 SQLHANDLE InputHandle,
1009 SQLHANDLE * OutputHandlePtr)
1013 #ifndef RELEASE_VERSION
1016 *((char *)(InputHandle)) = 'X';
1018 #error "throw that out!! %%%%"
1021 return SQLAllocHandle(HandleType,InputHandle,OutputHandlePtr);
1023 __except(EXCEPTION_EXECUTE_HANDLER)
1025 throw SEH_THROWN_OBJECT;
1027 } // SafeSQLAllocHandle
1030 SQLRETURN SafeSQLFreeHandle(
1031 SQLSMALLINT HandleType,
1035 return SQLFreeHandle(HandleType,Handle);
1037 __except(EXCEPTION_EXECUTE_HANDLER)
1039 throw SEH_THROWN_OBJECT;
1041 } // SafeSQLFreeHandle
1044 SQLRETURN SafeSQLSetEnvAttr(
1045 SQLHENV EnvironmentHandle,
1046 SQLINTEGER Attribute,
1047 SQLPOINTER ValuePtr,
1048 SQLINTEGER StringLength)
1051 return SQLSetEnvAttr(EnvironmentHandle,Attribute,ValuePtr,StringLength);
1053 __except(EXCEPTION_EXECUTE_HANDLER)
1055 throw SEH_THROWN_OBJECT;
1057 } // SafeSQLSetEnvAttr
1060 SQLRETURN SafeSQLSetConnectAttr(
1061 SQLHDBC ConnectionHandle,
1062 SQLINTEGER Attribute,
1063 SQLPOINTER ValuePtr,
1064 SQLINTEGER StringLength)
1067 return SQLSetConnectAttr(ConnectionHandle,Attribute,ValuePtr,StringLength);
1069 __except(EXCEPTION_EXECUTE_HANDLER)
1071 throw SEH_THROWN_OBJECT;
1073 } // SafeSQLSetConnectAttr
1076 SQLRETURN SafeSQLConnect(
1077 SQLHDBC ConnectionHandle,
1078 SQLCHAR * ServerName,
1079 SQLSMALLINT NameLength1,
1081 SQLSMALLINT NameLength2,
1082 SQLCHAR * Authentication,
1083 SQLSMALLINT NameLength3)
1086 return SQLConnect(ConnectionHandle,ServerName,NameLength1,UserName,NameLength2,Authentication,NameLength3);
1088 __except(EXCEPTION_EXECUTE_HANDLER)
1090 throw SEH_THROWN_OBJECT;
1095 SQLRETURN SafeSQLDriverConnect(
1096 SQLHDBC ConnectionHandle,
1097 SQLHWND WindowHandle,
1098 SQLCHAR * InConnectionString,
1099 SQLSMALLINT StringLength1,
1100 SQLCHAR * OutConnectionString,
1101 SQLSMALLINT BufferLength,
1102 SQLSMALLINT * StringLength2Ptr,
1103 SQLUSMALLINT DriverCompletion)
1106 return SQLDriverConnect(ConnectionHandle,WindowHandle,InConnectionString,StringLength1,OutConnectionString,BufferLength,StringLength2Ptr,DriverCompletion);
1108 __except(EXCEPTION_EXECUTE_HANDLER)
1110 throw SEH_THROWN_OBJECT;
1112 } // SafeSQLDriverConnect
1115 SQLRETURN SafeSQLGetInfo(
1116 SQLHDBC ConnectionHandle,
1117 SQLUSMALLINT InfoType,
1118 SQLPOINTER InfoValuePtr,
1119 SQLSMALLINT BufferLength,
1120 SQLSMALLINT * StringLengthPtr)
1123 return SQLGetInfo(ConnectionHandle,InfoType,InfoValuePtr,BufferLength,StringLengthPtr);
1125 __except(EXCEPTION_EXECUTE_HANDLER)
1127 throw SEH_THROWN_OBJECT;
1132 SQLRETURN SafeSQLEndTran(
1133 SQLSMALLINT HandleType,
1135 SQLSMALLINT CompletionType)
1138 return SQLEndTran(HandleType,Handle,CompletionType);
1140 __except(EXCEPTION_EXECUTE_HANDLER)
1142 throw SEH_THROWN_OBJECT;
1147 SQLRETURN SafeSQLExecDirect(
1148 SQLHSTMT StatementHandle,
1149 SQLCHAR * StatementText,
1150 SQLINTEGER TextLength)
1153 return SQLExecDirect(StatementHandle,StatementText,TextLength);
1155 __except(EXCEPTION_EXECUTE_HANDLER)
1157 throw SEH_THROWN_OBJECT;
1159 } // SafeSQLExecDirect
1164 SQLRETURN SafeSQLExecDirectW(
1165 SQLHSTMT StatementHandle,
1166 SQLWCHAR * StatementText,
1167 SQLINTEGER TextLength)
1170 return SQLExecDirectW(StatementHandle,StatementText,TextLength);
1172 __except(EXCEPTION_EXECUTE_HANDLER)
1174 throw SEH_THROWN_OBJECT;
1176 } // SafeSQLExecDirectW
1181 SQLRETURN SafeSQLFetch(
1182 SQLHSTMT StatementHandle)
1185 return SQLFetch(StatementHandle);
1187 __except(EXCEPTION_EXECUTE_HANDLER)
1189 throw SEH_THROWN_OBJECT;
1194 SQLRETURN SafeSQLNumResultCols(
1195 SQLHSTMT StatementHandle,
1196 SQLSMALLINT * ColumnCountPtr)
1199 return SQLNumResultCols(StatementHandle,ColumnCountPtr);
1201 __except(EXCEPTION_EXECUTE_HANDLER)
1203 throw SEH_THROWN_OBJECT;
1205 } // SafeSQLNumResultCols
1208 SQLRETURN SafeSQLGetData(
1209 SQLHSTMT StatementHandle,
1210 SQLUSMALLINT ColumnNumber,
1211 SQLSMALLINT TargetType,
1212 SQLPOINTER TargetValuePtr,
1213 SQLINTEGER BufferLength,
1214 SQLINTEGER * StrLen_or_IndPtr)
1217 return SQLGetData(StatementHandle,ColumnNumber,TargetType,TargetValuePtr,BufferLength,StrLen_or_IndPtr);
1219 __except(EXCEPTION_EXECUTE_HANDLER)
1221 throw SEH_THROWN_OBJECT;
1226 SQLRETURN SafeSQLCloseCursor(
1227 SQLHSTMT StatementHandle)
1230 return SQLCloseCursor(StatementHandle);
1232 __except(EXCEPTION_EXECUTE_HANDLER)
1234 throw SEH_THROWN_OBJECT;
1236 } // SafeSQLCloseCursor
1239 #endif // AV Guarding
1241 #endif // ODBCAPI_SUPPORT
1244 // append field value as literal to SQL text
1245 // - returns true if field(s) were not empty
1246 // - even non-existing or empty field will append at least NULL or '' to SQL
1247 bool TODBCApiAgent::appendFieldValueLiteral(
1248 TItemField &aField,TDBFieldType aDBFieldType, uInt32 aMaxSize, string &aSQL,
1249 TCharSets aDataCharSet, TLineEndModes aDataLineEndMode, TQuotingModes aQuotingMode,
1250 timecontext_t aTimeContext,
1258 TTimestampField *tsFldP;
1265 bool isempty=aField.isEmpty();
1268 aDBFieldType!=dbft_string
1270 // non-string field does not have a value: NULL
1274 switch (aDBFieldType) {
1275 // numeric time offsets
1276 case dbft_uctoffsfortime_hours:
1277 factor = 0; // special case, float
1279 case dbft_uctoffsfortime_mins:
1282 case dbft_uctoffsfortime_secs:
1283 factor = SecsPerMin;;
1286 factor = -1; // name, not offset
1289 if (!aField.isBasedOn(fty_timestamp))
1290 goto nullfield; // no timestamp -> no zone
1291 tsFldP = static_cast<TTimestampField *>(&aField);
1292 // get name or offset
1293 tctx = tsFldP->getTimeContext();
1296 if (tsFldP->isFloating()) goto nullfield; // floating -> no offset
1297 if (!TzResolveToOffset(tctx,moffs,tsFldP->getTimestampAs(TCTX_UNKNOWN),false,tsFldP->getGZones()))
1298 goto nullfield; // cannot calc offset -> no zone
1300 StringObjAppendPrintf(aSQL,"%g",(sInt32)moffs/60.0); // make hours with fraction
1302 StringObjAppendPrintf(aSQL,"%ld",(long)moffs * factor); // mins or seconds
1306 if (!TCTX_IS_DURATION(tctx) && !TCTX_IS_DATEONLY(tctx) && TCTX_IS_UNKNOWN(tctx))
1307 goto nullfield; // really floating (not duration or dateonly) -> no zone (and not "FLOATING" string we'd get from TimeZoneContextToName)
1308 TimeZoneContextToName(tctx, val, tsFldP->getGZones());
1312 // date and time values
1313 case dbft_lineardate:
1314 case dbft_unixdate_s:
1315 case dbft_unixdate_ms:
1316 case dbft_unixdate_us:
1317 intts=true; // integer timestamp
1318 case dbft_date: // date-only field
1321 case dbft_timefordate:
1325 case dbft_lineartime:
1326 case dbft_unixtime_s:
1328 case dbft_unixtime_ms:
1329 case dbft_unixtime_us:
1330 intts=true; // integer timestamp
1331 case dbft_dateonly: // date-only, but stored as timestamp
1332 case dbft_timestamp:
1336 // get timestamp in DB time zone
1337 if (aField.isBasedOn(fty_timestamp)) {
1338 // get it from field in specified zone (or floating)
1339 ts=static_cast<TTimestampField *>(&aField)->getTimestampAs(aTimeContext,&tctx);
1342 // try to convert ISO8601 string representation
1343 aField.getAsString(val);
1344 ISO8601StrToTimestamp(val.c_str(), ts, tctx);
1345 TzConvertTimestamp(ts,tctx,aTimeContext,getSessionZones(),aTimeContext);
1347 // remove time part on date-only
1349 ts = lineartime2dateonlyTime(ts);
1351 // Timestamp represented as integer in the DB
1352 // - add as integer timestamp
1353 StringObjAppendPrintf(aSQL,PRINTF_LLD,PRINTF_LLD_ARG(lineartimeToDbInt(ts,aDBFieldType)));
1356 // add as ODBC date/time literal
1357 lineartimeToODBCLiteralAppend(ts, aSQL, dat, tim);
1361 // numeric fields are copied to SQL w/o quotes
1362 aField.getAsString(val);
1363 aSQL.append(val); // just append
1366 // BLOBs cannot be written literally (should never occur, as we automatically parametrize them)
1367 throw TSyncException("FATAL: BLOB fields must be written with parameters");
1371 // Database field is string (or unknown), add it as string literal
1372 aField.getAsString(val);
1373 // only net string sizes are counted
1374 sz=val.size(); // net size of string
1375 if (sz>sInt32(aMaxSize)) sz=aMaxSize; // limit to what can be actually stored
1376 aRecordSize+=sz; // add to count
1378 stringToODBCLiteralAppend(
1391 } // field has a value
1393 } // TODBCApiAgent::appendFieldValueLiteral
1396 // - make ODBC string literal from UTF8 string
1397 void TODBCApiAgent::stringToODBCLiteralAppend(
1401 TLineEndModes aLineEndMode,
1402 TQuotingModes aQuotingMode,
1409 aCharSet, // charset
1410 aLineEndMode, // line end mode
1411 aQuotingMode, // quoting mode
1412 aMaxBytes // max size (0 if unlimited)
1415 } // TODBCApiAgent::stringToODBCLiteralAppend
1418 // - make ODBC date/time literals from lineartime_t
1419 void TODBCApiAgent::lineartimeToODBCLiteralAppend(
1420 lineartime_t aTimestamp, string &aString,
1421 bool aWithDate, bool aWithTime,
1422 timecontext_t aTsContext, timecontext_t aDBContext
1425 // make correct zone if needed
1426 if (!TCTX_IS_UNKNOWN(aTsContext)) {
1427 TzConvertTimestamp(aTimestamp,aTsContext,aDBContext,getSessionZones(),TCTX_UNKNOWN);
1429 // calculate components
1430 sInt16 y,mo,d,h,mi,s,ms;
1431 lineartime2date(aTimestamp,&y,&mo,&d);
1432 lineartime2time(aTimestamp,&h,&mi,&s,&ms);
1435 if (aWithDate && aWithTime)
1442 // add date if selected
1444 StringObjAppendPrintf(
1445 aString,"%04d-%02d-%02d",
1449 // add time if selected
1451 if (aWithDate) aString+=' '; // separate
1452 StringObjAppendPrintf(aString,
1456 // microseconds, if any
1458 StringObjAppendPrintf(aString,".%03d",ms);
1463 } // TODBCApiAgent::lineartimeToODBCLiteralAppend
1466 // - make integer-based literals from lineartime_t
1467 void TODBCApiAgent::lineartimeToIntLiteralAppend(
1468 lineartime_t aTimestamp, string &aString,
1469 TDBFieldType aDbfty,
1470 timecontext_t aTsContext, timecontext_t aDBContext
1473 // make correct zone if needed
1474 if (!TCTX_IS_UNKNOWN(aTsContext)) {
1475 TzConvertTimestamp(aTimestamp,aTsContext,aDBContext,getSessionZones(),TCTX_UNKNOWN);
1477 // - add as integer timestamp
1478 StringObjAppendPrintf(aString,PRINTF_LLD,PRINTF_LLD_ARG(lineartimeToDbInt(aTimestamp,aDbfty)));
1479 } // TODBCApiAgent::lineartimeToIntLiteralAppend
1484 // - make ODBC date/time literals from UTC timestamp
1485 void TODBCApiAgent::timeStampToODBCLiteralAppend(lineartime_t aTimeStamp, string &aString, bool aAsUTC, bool aWithDate, bool aWithTime)
1487 if (aTimeStamp!=0) {
1489 aTimeStamp=makeLocalTimestamp(aTimeStamp); // convert to local time
1491 lineartime2tm(aTimeStamp,&tim);
1492 // format as ODBC date/time literal
1493 tmToODBCLiteralAppend(tim,aString,aWithDate,aWithTime);
1496 aString.append("NULL");
1498 } // TODBCApiAgent::timeStampToODBCLiteralAppend
1500 // - make ODBC date/time literals from struct tm
1501 void TODBCApiAgent::tmToODBCLiteralAppend(const struct tm &tim, string &aString, bool aWithDate, bool aWithTime)
1505 if (aWithDate && aWithTime)
1512 // add date if selected
1514 StringObjAppendPrintf(
1515 aString,"%04d-%02d-%02d",
1521 // add time if selected
1523 if (aWithDate) aString+=' '; // separate
1524 StringObjAppendPrintf(aString,
1533 } // TODBCApiAgent::tmToODBCLiteralAppend
1538 // - return quoted version of string if aDoQuote is set
1539 // bfo: Problems with XCode (expicit qualification), already within namespace ?
1540 //const char *sysync::quoteString(string &aIn, string &aOut, TQuotingModes aQuoteMode)
1541 const char *quoteString(string &aIn, string &aOut, TQuotingModes aQuoteMode)
1543 return quoteString(aIn.c_str(),aOut,aQuoteMode);
1544 } // TODBCApiAgent::quoteString
1547 // - return quoted version of string if aDoQuote is set
1548 // bfo: Problems with XCode (expicit qualification), already within namespace ?
1549 //const char *sysync::quoteString(const char *aIn, string &aOut, TQuotingModes aQuoteMode)
1550 const char *quoteString(const char *aIn, string &aOut, TQuotingModes aQuoteMode)
1553 quoteStringAppend(aIn,aOut,aQuoteMode);
1554 return aOut.c_str();
1555 } // TODBCApiAgent::quoteString
1558 // - append quoted version of string if aDoQuote is set
1559 // bfo: Problems with XCode (expicit qualification), already within namespace ?
1560 //void sysync::quoteStringAppend(const char *aIn, string &aOut, TQuotingModes aQuoteMode)
1561 void quoteStringAppend(const char *aIn, string &aOut, TQuotingModes aQuoteMode)
1563 if (!aQuoteMode!=qm_none) aOut.append(aIn);
1565 sInt16 n=strlen(aIn);
1571 chs_ascii, // charset
1572 lem_cstr, // line end mode
1573 aQuoteMode, // quoting mode
1574 0 // max size (0 if unlimited)
1578 } // TODBCApiAgent::quoteStringAppend
1581 // - append quoted version of string if aDoQuote is set
1582 // bfo: Problems with XCode (expicit qualification), already within namespace ?
1583 //void sysync::quoteStringAppend(string &aIn, string &aOut, TQuotingModes aQuoteMode)
1584 void quoteStringAppend(string &aIn, string &aOut, TQuotingModes aQuoteMode)
1586 quoteStringAppend(aIn.c_str(),aOut,aQuoteMode);
1587 } // TODBCApiAgent::quoteStringAppend
1590 // reset all mapped parameters
1591 void TODBCApiAgent::resetSQLParameterMaps(TParameterMapList &aParamMapList)
1593 TParameterMapList::iterator pos;
1594 for (pos=aParamMapList.begin();pos!=aParamMapList.end();++pos) {
1596 if (pos->mybuffer && pos->ParameterValuePtr) {
1597 // delete buffer if we have allocated one
1598 sysync_free(pos->ParameterValuePtr);
1599 pos->ParameterValuePtr=NULL;
1600 pos->mybuffer=false;
1604 aParamMapList.clear();
1605 } // TODBCApiAgent::resetSQLParameterMaps
1608 // parsing of %p(mode,var_or_field[,dbfieldtype[,maxcolsize]]) sequence
1609 bool TODBCApiAgent::ParseParamSubst(
1610 string &aSQL, // string to parse
1611 string::size_type &i, // input=position where % sequence starts in aSQL, output = if result==false: where to continue parsing, else: where to substitute
1612 string::size_type &n, // input=number of chars of % sequence possibly with "(" but nothing more, if result==true: output=number of chars to substitute at i in aSQL
1613 TParameterMapList &aParameterMaps, // parameter maps list to add params to
1614 TMultiFieldItem *aItemP // the involved item for field params
1615 #ifdef SCRIPT_SUPPORT
1616 ,TScriptContext *aScriptContextP // the script context for variable params
1620 string::size_type j,k,h;
1622 // %p(mode,fieldname,dbfieldtype) = field as SQL parameter, where mode can be "i","o" or "io"
1624 // find closing paranthesis
1625 k = aSQL.find(")",j);
1626 if (k==string::npos) { i=j; n=0; return false; } // no closing paranthesis, do not substitute
1628 bool paramin=false,paramout=false;
1631 if (tolower(aSQL[j]=='i')) {
1633 if (tolower(aSQL[j+1])=='o') {
1639 else if (tolower(aSQL[j]=='o')) {
1643 // now get item field or variable name
1644 if (aSQL[j]!=',') { i=k+1; n=0; return false; } // continue after closing paranthesis
1646 // extract name (without possible array index)
1647 h = aSQL.find(",",j);
1648 if (h==string::npos) { h=k; } // no second comma, only field name, use default dbfieldtype
1650 fldname.assign(aSQL,j,h-j);
1651 j=h+1; // after , or )
1652 // get fieldtype (default if none specified)
1653 TDBFieldType dbfty=dbft_string; // default to string
1654 uInt32 colmaxsize=0;
1656 // more params specified (database field type and possibly column size)
1657 h = aSQL.find(",",j);
1658 if (h==string::npos) { h=k; } // no third comma, only field type, use default column size)
1659 // get database field type
1661 tyname.assign(aSQL,j,h-j);
1663 if (StrToEnum(DBFieldTypeNames,numDBfieldTypes,ty,tyname.c_str()))
1664 dbfty=(TDBFieldType)ty;
1665 j=h+1; // after , or )
1666 // get column max size (default if none specified)
1668 // column size specified
1669 StrToULong(aSQL.c_str()+j,colmaxsize,k-j);
1672 // find field or var
1673 TItemField *fldP=NULL;
1674 #ifdef SCRIPT_SUPPORT
1675 if (aScriptContextP) {
1676 sInt16 idx=aScriptContextP->getIdentifierIndex(OBJ_AUTO, aItemP ? aItemP->getFieldDefinitions() : NULL ,fldname.c_str());
1677 fldP=aScriptContextP->getFieldOrVar(aItemP,idx,0);
1681 if (aItemP) fldP = aItemP->getArrayField(fldname.c_str(),0,true);
1682 if (!fldP) { i=k+1; n=0; return false; } // no suitable mapping
1683 // now map as parameter
1686 map.inparam=paramin;
1687 map.outparam=paramout;
1688 map.parammode=param_field;
1690 map.ParameterValuePtr=NULL;
1692 map.StrLen_or_Ind=SQL_NULL_DATA; // note that this is not zero (but -1)
1695 map.maxSize=colmaxsize;
1696 map.dbFieldType=dbfty;
1698 aParameterMaps.push_back(map);
1699 // set substitution parameters
1703 } // TODBCApiAgent::ParseParamSubst
1706 // do generic substitutions
1707 void TODBCApiAgent::DoSQLSubstitutions(string &aSQL)
1709 #ifndef BINFILE_ALWAYS_ACTIVE
1710 if (!binfilesActive()) {
1711 // substitute: %u = userkey
1712 StringSubst(aSQL,"%u",fUserKey,2,fConfigP->fDataCharSet,fConfigP->fDataLineEndMode,fConfigP->fQuotingMode);
1713 // substitute: %d = devicekey
1714 StringSubst(aSQL,"%d",fDeviceKey,2,fConfigP->fDataCharSet,fConfigP->fDataLineEndMode,fConfigP->fQuotingMode);
1715 #ifdef SCRIPT_SUPPORT
1716 // substitute: %C = domain name (such as company selector)
1717 StringSubst(aSQL,"%C",fDomainName,2,fConfigP->fDataCharSet,fConfigP->fDataLineEndMode,fConfigP->fQuotingMode);
1720 #endif // not BINFILE_ALWAYS_ACTIVE
1721 #ifdef SCRIPT_SUPPORT
1722 // substitute %sv(sessionvarname) = session variable by name
1723 string::size_type i=0;
1724 while((i=aSQL.find("%sv(",i))!=string::npos) {
1727 string::size_type j=i+4;
1728 // find closing paranthesis
1729 string::size_type k = aSQL.find(")",j);
1730 if (k==string::npos) { i=j; continue; } // no closing paranthesis
1731 sInt32 m = k; // assume end of name is here
1733 s.assign(aSQL,j,m-j);
1734 // find session var with this name
1735 if (!fSessionScriptContextP) { i=j; continue; }
1736 TItemField *fldP = fSessionScriptContextP->getFieldOrVar(
1738 fSessionScriptContextP->getIdentifierIndex(OBJ_LOCAL,NULL,s.c_str(),s.size())
1740 if (!fldP) { i=j; continue; } // field not found, no action
1741 // get field contents
1742 fldP->getAsString(s);
1743 // subsititute (starting with "%v(", ending with ")" )
1744 aSQL.replace(i,k+1-i,s); i+=s.size();
1746 // substitute %p(mode,var_or_field[,dbfieldtype]) = map field to parameter
1747 // - as the only source of params in session-level SQL is the %p() sequence, we
1748 // can clear the list here (to make sure no param from another call remains active)
1749 // - if this is called from Datastore context, %p() sequences are already substituted, and
1750 // therefore no parameter list mixing will occur
1751 resetSQLParameterMaps();
1753 while((i=aSQL.find("%p(",i))!=string::npos) {
1754 string::size_type n=3; // size of base sequence %p(
1755 if (!ParseParamSubst(
1759 #ifdef SCRIPT_SUPPORT
1763 // subsititute param spec with single question mark
1764 aSQL.replace(i,n,"?"); i+=1;
1767 } // TODBCApiAgent::DoSQLSubstitutions
1770 // reset all mapped parameters
1771 void TODBCApiAgent::resetSQLParameterMaps(void)
1773 resetSQLParameterMaps(fParameterMaps);
1774 } // TODBCApiAgent::resetSQLParameterMaps
1777 // add parameter definition to the session level parameter list
1778 void TODBCApiAgent::addSQLParameterMap(
1781 TParamMode aParamMode,
1782 TItemField *aFieldP,
1783 TDBFieldType aDbFieldType
1789 map.inparam=aInParam;
1790 map.outparam=aOutParam;
1791 map.parammode=aParamMode;
1793 map.ParameterValuePtr=NULL;
1795 map.StrLen_or_Ind=SQL_NULL_DATA; // note that this is not zero (but -1)
1796 map.itemP=NULL; // no item
1798 map.maxSize=std_paramsize;
1799 map.dbFieldType=aDbFieldType;
1802 fParameterMaps.push_back(map);
1803 } // TODBCApiAgent::addSQLParameterMap
1809 #ifdef ODBCAPI_SUPPORT
1811 // get existing or create new ODBC environment handle
1812 SQLHENV TODBCApiAgent::getODBCEnvironmentHandle(void)
1814 if (fODBCEnvironmentHandle==SQL_NULL_HANDLE) {
1815 // create one environment handle for the session
1816 if (SafeSQLAllocHandle(
1817 SQL_HANDLE_ENV, SQL_NULL_HANDLE, &fODBCEnvironmentHandle
1820 throw TSyncException("Cannot allocated ODBC environment handle");
1822 // Set ODBC 3.0 (needed, else function sequence error will occur)
1823 if (SafeSQLSetEnvAttr(
1824 fODBCEnvironmentHandle, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0
1827 throw TSyncException("Cannot set environment to ODBC 3.0");
1830 // return the handle
1831 return fODBCEnvironmentHandle;
1832 } // TODBCApiAgent::getODBCEnvironmentHandle
1835 // check for connection-level error
1836 void TODBCApiAgent::checkConnectionError(SQLRETURN aResult)
1838 checkODBCError(aResult,SQL_HANDLE_DBC,fODBCConnectionHandle);
1839 } // TODBCApiAgent::checkConnectionError
1843 // get handle to open connection
1844 SQLHDBC TODBCApiAgent::getODBCConnectionHandle(void)
1848 if (fODBCConnectionHandle==SQL_NULL_HANDLE) {
1849 // no connection exists, allocate new Connection Handle
1850 PDEBUGPRINTFX(DBG_DBAPI,("Trying to open new ODBC connection with ConnStr = '%s'",fSessionDBConnStr.c_str()));
1851 // - get environment handle (session global)
1852 SQLHENV envhandle=getODBCEnvironmentHandle();
1853 if (envhandle==SQL_NULL_HANDLE)
1854 throw TSyncException("No environment handle available");
1855 // - allocate connection handle
1856 res=SafeSQLAllocHandle(
1859 &fODBCConnectionHandle
1861 checkODBCError(res,SQL_HANDLE_ENV,envhandle);
1863 // Some ODBC drivers (apparently MyODBC 3.51 on Mac OS X 10.5.2) crash when using SQLSetConnectAttr
1864 if (!fConfigP->fNoConnectAttrs) {
1865 // Set Connection attributes
1866 // - Make sure no dialog boxes are ever shown
1867 res=SafeSQLSetConnectAttr(fODBCConnectionHandle,SQL_ATTR_QUIET_MODE,NULL,0);
1868 checkConnectionError(res);
1869 // - Cursor library usage (default is NO)
1870 res=SafeSQLSetConnectAttr(fODBCConnectionHandle,SQL_ATTR_ODBC_CURSORS,(void*)(fConfigP->fUseCursorLib ? SQL_CUR_USE_ODBC : SQL_CUR_USE_DRIVER),0);
1871 checkConnectionError(res);
1872 // - Commit mode manually
1873 res=SafeSQLSetConnectAttr(fODBCConnectionHandle,SQL_ATTR_AUTOCOMMIT,(void*)SQL_AUTOCOMMIT_OFF,0);
1874 checkConnectionError(res);
1876 res=SafeSQLSetConnectAttr(fODBCConnectionHandle,SQL_ATTR_CONNECTION_TIMEOUT,(void*)fConfigP->fODBCTimeout,0);
1877 checkConnectionError(res);
1879 // Now connect, before setting transaction stuff
1880 // - append password to configured connection string
1882 connstr=fSessionDBConnStr.c_str();
1883 if (!fSessionDBPassword.empty()) {
1885 connstr+=fSessionDBPassword;
1888 const SQLSMALLINT outStrMax=1024;
1889 SQLCHAR outStr[outStrMax];
1890 SQLSMALLINT outStrSiz;
1891 res=SafeSQLDriverConnect(
1892 fODBCConnectionHandle, // connection handle
1893 NULL, // no windows handle
1894 (SQLCHAR *)connstr.c_str(), // input string
1896 outStr, // output string
1901 checkConnectionError(res);
1902 // Note: the following may show the password, so it MUST NOT be a PDEBUGxxx, but a DEBUGxxx !
1903 DEBUGPRINTFX(DBG_DBAPI+DBG_EXOTIC,("SQLDriverConnect returns connection string = '%s'",outStr));
1904 // Now configure transactions
1908 // - check what DB can offer for isolation levels
1909 SQLUINTEGER txnmask;
1910 res=SafeSQLGetInfo(fODBCConnectionHandle,SQL_TXN_ISOLATION_OPTION,&txnmask,0,NULL);
1911 if (getODBCError(res,msg,state,SQL_HANDLE_DBC,fODBCConnectionHandle)) {
1912 PDEBUGPRINTFX(DBG_ERROR,("SQLGetInfo for SQL_TXN_ISOLATION_OPTION failed: %s",msg.c_str()));
1914 PDEBUGPRINTFX(DBG_DBAPI,("ODBC source's Transaction support mask = 0x%04lX",txnmask));
1916 // - check standard isolation level
1918 res=SafeSQLGetInfo(fODBCConnectionHandle,SQL_DEFAULT_TXN_ISOLATION,&txn,0,NULL);
1919 if (getODBCError(res,msg,state,SQL_HANDLE_DBC,fODBCConnectionHandle)) {
1920 DEBUGPRINTFX(DBG_ERROR,("SQLGetInfo for SQL_DEFAULT_TXN_ISOLATION failed: %s",msg.c_str()));
1923 DEBUGPRINTF(("ODBC source's standard isolation mask = 0x%04lX",txn));
1927 // - set isolation level
1928 if (fConfigP->fODBCTxnMode!=txni_default) {
1929 SQLUINTEGER txnmode = 0; // none by default
1930 if (fConfigP->fODBCTxnMode<txni_none) {
1931 txnmode = 1 << (fConfigP->fODBCTxnMode);
1933 PDEBUGPRINTFX(DBG_DBAPI,("Setting SQL_ATTR_TXN_ISOLATION to = 0x%04lX",txnmode));
1934 res=SafeSQLSetConnectAttr(fODBCConnectionHandle,SQL_ATTR_TXN_ISOLATION,(void*)txnmode,0);
1935 checkConnectionError(res);
1938 PDEBUGPRINTFX(DBG_DBAPI,("Created and opened new ODBC connection (timeout=%ld sec)",fConfigP->fODBCTimeout));
1939 #ifdef SCRIPT_SUPPORT
1940 // save datastore context (as this script can be executed implicitly from another scripts SQLEXECUTE())
1941 TCustomImplDS *currScriptDS = fScriptContextDatastore;
1942 // now execute afterconnect script, if any
1943 fScriptContextDatastore=NULL; // connecting is not datastore related
1944 TScriptContext::execute(
1945 fAfterConnectContext,
1946 fConfigP->fAfterConnectScript,
1947 fConfigP->getAgentFuncTableP(), // context function table
1948 (void *)this // context data (myself)
1950 // restore datastore context as it was before
1951 fScriptContextDatastore = currScriptDS;
1955 // connection is not usable, dispose handle again
1956 SafeSQLFreeHandle(SQL_HANDLE_DBC,fODBCConnectionHandle);
1957 fODBCConnectionHandle=SQL_NULL_HANDLE;
1961 PDEBUGPRINTFX(DBG_DBAPI+DBG_EXOTIC,("Session: using connection handle 0x%lX",(uIntArch)fODBCConnectionHandle));
1962 return fODBCConnectionHandle;
1963 } // TODBCApiAgent::getODBCConnectionHandle
1966 // pull connection handle out of session into another object (datastore)
1967 SQLHDBC TODBCApiAgent::pullODBCConnectionHandle(void)
1969 SQLHDBC connhandle = getODBCConnectionHandle();
1970 fODBCConnectionHandle = SQL_NULL_HANDLE; // owner is caller, must do closing and disposing
1971 #ifdef SCRIPT_SUPPORT
1972 // make sure possible script statement gets disposed, as connection is now owned by datastore
1973 commitAndCloseScriptStatement();
1976 } // TODBCApiAgent::pullODBCConnectionHandle
1980 void TODBCApiAgent::closeODBCConnection(SQLHDBC &aConnHandle)
1985 if (aConnHandle!=SQL_NULL_HANDLE) {
1986 // Roll back to make sure we don't leave a unfinished transaction
1987 res=SafeSQLEndTran(SQL_HANDLE_DBC,aConnHandle,SQL_ROLLBACK);
1988 if (getODBCError(res,msg,state,SQL_HANDLE_DBC,aConnHandle)) {
1989 PDEBUGPRINTFX(DBG_ERROR,("closeODBCConnection: SQLEndTran failed: %s",msg.c_str()));
1991 // Actually disconnect
1992 res=SQLDisconnect(aConnHandle);
1993 if (getODBCError(res,msg,state,SQL_HANDLE_DBC,aConnHandle)) {
1994 PDEBUGPRINTFX(DBG_ERROR,("closeODBCConnection: SQLDisconnect failed: %s",msg.c_str()));
1996 // free the connection handle
1997 res=SafeSQLFreeHandle(SQL_HANDLE_DBC,aConnHandle);
1998 if (getODBCError(res,msg,state,SQL_HANDLE_DBC,aConnHandle)) {
1999 PDEBUGPRINTFX(DBG_ERROR,("closeODBCConnection: SQLFreeHandle(DBC) failed: %s",msg.c_str()));
2001 aConnHandle=SQL_NULL_HANDLE;
2003 } // TODBCApiAgent::closeODBCConnection
2006 // check if aResult signals error and throw exception if so
2007 void TODBCApiAgent::checkODBCError(SQLRETURN aResult,SQLSMALLINT aHandleType,SQLHANDLE aHandle)
2011 if (getODBCError(aResult,msg,state,aHandleType,aHandle)) {
2013 throw TSyncException(msg.c_str());
2015 } // TODBCApiAgent::checkODBCError
2018 // - check if aResult signals error and throw exception if so
2019 void TODBCApiAgent::checkStatementError(SQLRETURN aResult,SQLHSTMT aHandle)
2021 checkODBCError(aResult,SQL_HANDLE_STMT,aHandle);
2022 } // TODBCApiAgent::checkStatementError
2025 // - check if aResult signals error and throw exception if so
2026 // does not report NO_DATA error, but returns false if NO_DATA condition exists
2027 bool TODBCApiAgent::checkStatementHasData(SQLRETURN aResult,SQLHSTMT aHandle)
2029 if (aResult==SQL_NO_DATA)
2030 return false; // signal NO DATA
2032 checkStatementError(aResult,aHandle);
2033 return true; // signal ok
2034 } // TODBCApiAgent::checkStatementHasData
2037 // get ODBC error message for given result code
2038 // - returns false if no error
2039 bool TODBCApiAgent::getODBCError(SQLRETURN aResult,string &aMessage,string &aSQLState, SQLSMALLINT aHandleType,SQLHANDLE aHandle)
2042 if(aResult==SQL_SUCCESS)
2043 return false; // no error
2045 StringObjPrintf(aMessage,"ODBC SQL return code = %ld\n",(sInt32)aResult);
2047 SQLCHAR sqlstate[6]; // buffer for state message
2048 sqlstate[5]=0; // terminate
2050 SQLINTEGER nativeerror;
2051 SQLSMALLINT msgsize,recno;
2055 sInt16 maxmsgsize = 200;
2056 SQLCHAR *messageP = (SQLCHAR *) malloc(maxmsgsize);
2058 if (messageP==NULL) {
2059 StringObjAppendPrintf(aMessage,"- SQLGetDiagRec[%hd] failed because needed buffer of %ld bytes cannot be allocated",(sInt16)recno,(sInt32)maxmsgsize);
2060 break; // don't continue
2062 msgsize=0; // just to make sure
2064 aHandleType, // handle Type
2065 aHandle, // statement handle
2066 recno, // record number
2067 sqlstate, // state buffer
2068 &nativeerror, // native error code
2069 messageP, // message buffer, gets message
2070 maxmsgsize, // message buffer size
2071 &msgsize // gets size of message buffer
2073 if (res==SQL_NO_DATA) break; // seen all diagnostic info
2074 if (res==SQL_SUCCESS_WITH_INFO) {
2075 if (msgsize>maxmsgsize) {
2076 // buffer is too small, allocate a bigger one and try again
2078 maxmsgsize=msgsize+1; // make buffer large enough for message
2079 messageP = (SQLCHAR *) malloc(maxmsgsize);
2083 // SQL_SUCCESS_WITH_INFO, but buffer is large enough - strange...
2084 StringObjAppendPrintf(aMessage,"- SQLGetDiagRec[%hd] said SQL_SUCCESS_WITH_INFO, but buffer is large enough\n",(sInt16)recno);
2086 // info found, append it to error text message
2087 else if (res==SQL_SUCCESS) {
2088 StringObjAppendPrintf(aMessage,"- SQLState = '%s', NativeError=%ld, Message = %s\n",sqlstate,(sInt32)nativeerror,messageP);
2089 // save latest SQLState
2090 aSQLState.assign((const char *)sqlstate,5);
2093 StringObjAppendPrintf(aMessage,"- SQLGetDiagRec[%hd] failed, Result=%ld\n",(sInt16)recno,(sInt32)res);
2094 break; // abort on error, too
2098 // get rid of message buffer
2100 // if it's success with Info, this is not considered an error, but do show info in log
2101 if (aResult==SQL_SUCCESS_WITH_INFO) {
2102 // show info as exotics in debug logs, but treat as success
2103 // (note: MS-SQL is verbose here...)
2104 PDEBUGPRINTFX(DBG_DBAPI+DBG_EXOTIC,("SQL_SUCCESS_WITH_INFO: %s",aMessage.c_str()));
2105 return false; // this is no error
2108 return true; // treat as error error
2111 } // TODBCApiAgent::getODBCError
2115 // get new statement handle
2116 SQLHSTMT TODBCApiAgent::newStatementHandle(SQLHDBC aConnection)
2121 // Allocate Statement Handle
2122 res=SafeSQLAllocHandle(SQL_HANDLE_STMT,aConnection,&statement);
2123 checkODBCError(res,SQL_HANDLE_DBC,aConnection);
2124 // Statement Attributes
2125 // - row array size (ODBC 3.0, not really needed as default is 1 anyway)
2128 SQL_ATTR_ROW_ARRAY_SIZE,
2132 checkStatementError(res,statement);
2134 } // TODBCApiAgent::newStatementHandle
2140 /* About SQLGetData from WIN32_SDK:
2142 If the driver does not support extensions to SQLGetData, the function can only
2143 return data for unbound columns with a number greater than that of the last bound
2144 column. Furthermore, within a row of data, the value of the ColumnNumber argument
2145 in each call to SQLGetData must be greater than or equal to the value of
2146 ColumnNumber in the previous call; that is, data must be retrieved in increasing
2147 column number order. Finally, if no extensions are supported, SQLGetData cannot
2148 be called if the rowset size is greater than 1.
2152 // get value, returns false if no Data or Null
2153 bool TODBCApiAgent::getColumnValueAsULong(
2154 SQLHSTMT aStatement,
2161 SQLSMALLINT numcols;
2163 // check if there aColNumber is in range
2164 res = SafeSQLNumResultCols(aStatement,&numcols);
2165 checkStatementError(res,aStatement);
2166 if (aColNumber<1 || aColNumber>numcols)
2167 throw TSyncException(DEBUGTEXT("getColumnValueAsULong with bad col index","odds1"));
2169 res = SafeSQLGetData(
2170 aStatement, // statement handle
2171 aColNumber, // column number
2172 SQL_C_ULONG, // target type: unsigned long
2173 &aLongValue, // where to store the long
2174 4, // max size of value (not used here)
2175 (SQLLEN*)&ind // indicator
2177 checkStatementError(res,aStatement);
2178 // return true if real data returned
2179 return (ind!=SQL_NULL_DATA && ind!=SQL_NO_TOTAL);
2180 } // TODBCApiAgent::getColumnValueAsULong
2183 // get value, returns false if no Data or Null
2184 bool TODBCApiAgent::getColumnValueAsLong(
2185 SQLHSTMT aStatement,
2192 SQLSMALLINT numcols;
2194 // check if there aColNumber is in range
2195 res = SafeSQLNumResultCols(aStatement,&numcols);
2196 checkStatementError(res,aStatement);
2197 if (aColNumber<1 || aColNumber>numcols)
2198 throw TSyncException(DEBUGTEXT("getColumnValueAsLong with bad col index","odds2"));
2200 res = SafeSQLGetData(
2201 aStatement, // statement handle
2202 aColNumber, // column number
2203 SQL_C_LONG, // target type: signed long
2204 &aLongValue, // where to store the long
2205 4, // max size of value (not used here)
2206 (SQLLEN*)&ind // indicator
2208 checkStatementError(res,aStatement);
2209 // return true if real data returned
2210 return (ind!=SQL_NULL_DATA && ind!=SQL_NO_TOTAL);
2211 } // TODBCApiAgent::getColumnValueAsLong
2214 // get value, returns false if no Data or Null
2215 bool TODBCApiAgent::getColumnValueAsDouble(
2216 SQLHSTMT aStatement,
2218 double &aDoubleValue
2223 SQLSMALLINT numcols;
2225 // check if there aColNumber is in range
2226 res = SafeSQLNumResultCols(aStatement,&numcols);
2227 checkStatementError(res,aStatement);
2228 if (aColNumber<1 || aColNumber>numcols)
2229 throw TSyncException(DEBUGTEXT("getColumnValueAsDouble with bad col index","odds3"));
2231 res = SafeSQLGetData(
2232 aStatement, // statement handle
2233 aColNumber, // column number
2234 SQL_C_DOUBLE, // target type: signed long
2235 &aDoubleValue, // where to store the double
2236 8, // max size of value (not used here)
2237 (SQLLEN*)&ind // indicator
2239 checkStatementError(res,aStatement);
2240 // return true if real data returned
2241 return (ind!=SQL_NULL_DATA && ind!=SQL_NO_TOTAL);
2242 } // TODBCApiAgent::getColumnValueAsDouble
2245 // get value, returns false if no Data or Null
2246 bool TODBCApiAgent::getColumnValueAsString(
2247 SQLHSTMT aStatement,
2249 string &aStringValue,
2250 TCharSets aCharSet, // real charset, including UTF16!
2255 sInt32 maxstringlen=512; // enough to start with for most fields
2257 uInt8 *strbufP=NULL;
2259 SQLSMALLINT numcols;
2265 // check if there aColNumber is in range
2266 res = SafeSQLNumResultCols(aStatement,&numcols);
2267 checkStatementError(res,aStatement);
2268 if (aColNumber<1 || aColNumber>numcols)
2269 throw TSyncException(DEBUGTEXT("getColumnValueAsString with bad col index","odds4"));
2271 // - start with empty string
2272 aStringValue.erase();
2273 strbufP = new uInt8[maxstringlen+1];
2275 bool gotAllData=false;
2277 // make sure we have the buffer
2279 throw TSyncException(DEBUGTEXT("getColumnValueAsString can't allocate enough buffer memory","odds4a"));
2280 strbufP[maxstringlen]=0; // make sure we have ALWAYS a terminator (for appendStringAsUTF8)
2281 nextbuflen=maxstringlen; // default to same size we already have
2283 res = SafeSQLGetData(
2284 aStatement, // statement handle
2285 aColNumber, // column number
2286 aAsBlob ? SQL_C_BINARY :
2288 (aCharSet==chs_utf16 ? SQL_C_WCHAR : SQL_C_CHAR), // target type: Binary, 8-bit or 16-bit string
2290 SQL_C_CHAR, // no 16-bit chars
2292 strbufP, // where to store the data
2293 maxstringlen, // max size of string
2294 (SQLLEN*)&siz // returns real remaining size of data (=what we got in this call + what still remains to be fetched)
2296 if (res==SQL_NO_DATA) {
2301 if (res!=SQL_SUCCESS && res!=SQL_SUCCESS_WITH_INFO) {
2302 checkStatementError(res,aStatement);
2306 if (siz==SQL_NULL_DATA) {
2311 else if (siz==SQL_NO_TOTAL) {
2312 // we do not know how much is remaining, so it's certainly at least a full buffer
2313 PDEBUGPRINTFX(DBG_DBAPI+DBG_EXOTIC,("SQLGetData returned SQL_NO_TOTAL and %ld bytes of data",maxstringlen));
2315 nextbuflen=maxstringlen*2; // suggest next buffer twice as big as current one
2318 // what we get is either the rest or a full buffer
2319 if (siz>maxstringlen) {
2320 PDEBUGPRINTFX(DBG_DBAPI+DBG_EXOTIC,("SQLGetData returned %ld bytes of %ld total remaining",maxstringlen,siz));
2321 // that's how much we need for the remaining data. Plus one to avoid extra loop
2322 // at end for drivers that do not return state '01004'
2323 nextbuflen=siz-maxstringlen+1;
2324 // that's how much we got this time: one buffer full
2328 gotData=true; // not NULL
2330 if (res==SQL_SUCCESS_WITH_INFO) {
2331 // probably data truncated
2332 string msg,sqlstate;
2333 getODBCError(res,msg,sqlstate,SQL_HANDLE_STMT,aStatement);
2334 if (sqlstate=="01004") {
2336 PDEBUGPRINTFX(DBG_DBAPI+DBG_EXOTIC,("SQLGetData returns state '01004' (truncated) -> more data to be fetched"));
2337 // - do not yet exit loop
2338 gotAllData=false; // not all...
2339 gotData=true; // ...but some
2342 // otherwise treat as success
2343 PDEBUGPRINTFX(DBG_DBAPI+DBG_EXOTIC,("SQLGetData returns state '%s' -> ignore",sqlstate.c_str()));
2347 if (res==SQL_SUCCESS) {
2348 // it seems that not all drivers return SQL_SUCCESS_WITH_INFO when there is more data to read
2349 // so only stop reading here already if we haven't got one buffer full. Otherwise,
2350 // loop one more time to try getting more. We'll get SQL_NULL_DATA or SQL_NO_DATA then and exit the loop.
2351 if (siz<maxstringlen) {
2352 // received all we can receive
2356 // received exactly one buffer full - maybe there is more (MyODBC on Linux does not issue SQL_SUCCESS_WITH_INFO "01004")
2357 PDEBUGPRINTFX(DBG_DBAPI+DBG_EXOTIC,("SQLGetData returns SQL_SUCCESS but buffer full of data (%ld bytes) -> try to get more",(uInt32)siz));
2360 // copy what we already have
2362 aStringValue.append((const char *)strbufP,siz); // assign all data 1:1 to string
2364 else if (aCharSet==chs_utf16)
2365 wStr.append((const char *)strbufP,siz); // assign all data 1:1 to wide string buffer, will be converted later
2368 appendStringAsUTF8((const char *)strbufP, aStringValue, aCharSet, lem_cstr); // Convert to app-charset (UTF8) and C-type lineends
2369 // get a bigger buffer in case there's more to fetch and we haven't got 64k already
2371 if (maxstringlen<65536 && nextbuflen>maxstringlen) {
2372 // we could need a larger buffer
2373 maxstringlen = nextbuflen>65536 ? 65536 : nextbuflen;
2375 strbufP = new uInt8[maxstringlen+1];
2376 PDEBUGPRINTFX(DBG_DBAPI+DBG_EXOTIC,("Allocating bigger buffer for next call to SQLGetData: %ld bytes",(uInt32)maxstringlen));
2379 } while(!gotAllData);
2380 // done, we don't need the buffer any more
2381 if (strbufP) delete strbufP;
2385 if (strbufP) delete strbufP;
2388 // convert from Unicode to UTF-8 if we got unicode here
2390 if (aCharSet==chs_utf16) {
2391 appendUTF16AsUTF8((const uInt16 *)wStr.c_str(), wStr.size()/2, ODBC_BIGENDIAN, aStringValue, true, false);
2396 } // TODBCApiAgent::getColumnValueAsString
2399 // returns true if successfully and filled aODBCTimestamp
2400 bool TODBCApiAgent::getColumnAsODBCTimestamp(
2401 SQLHSTMT aStatement,
2403 SQL_TIMESTAMP_STRUCT &aODBCTimestamp
2408 SQLSMALLINT numcols;
2410 // check if there aColNumber is in range
2411 res = SafeSQLNumResultCols(aStatement,&numcols);
2412 checkStatementError(res,aStatement);
2413 if (aColNumber<1 || aColNumber>numcols)
2414 throw TSyncException(DEBUGTEXT("getColumnAsODBCTimestamp with bad col index","odds5"));
2416 res = SafeSQLGetData(
2417 aStatement, // statement handle
2418 aColNumber, // column number
2419 SQL_C_TYPE_TIMESTAMP, // target type: timestamp
2420 &aODBCTimestamp, // where to store the timestamp
2422 (SQLLEN*)&ind // returns indication if NULL
2424 checkStatementError(res,aStatement);
2425 // return true if data filled in
2426 return (ind!=SQL_NULL_DATA && ind!=SQL_NO_TOTAL);
2427 } // TODBCApiAgent::getColumnAsODBCTimestamp
2430 // get value (UTC timestamp), returns false if no Data or Null
2431 bool TODBCApiAgent::getColumnValueAsTimestamp(
2432 SQLHSTMT aStatement,
2434 lineartime_t &aTimestamp
2437 SQL_TIMESTAMP_STRUCT odbctimestamp;
2439 if (getColumnAsODBCTimestamp(aStatement,aColNumber,odbctimestamp)) {
2440 // there is a timestamp
2441 DEBUGPRINTFX(DBG_DBAPI+DBG_EXOTIC,(
2442 "ODBCTimestamp: %04hd-%02hd-%02hd %02hd:%02hd:%02hd.%03ld",
2443 odbctimestamp.year,odbctimestamp.month,odbctimestamp.day,
2444 odbctimestamp.hour,odbctimestamp.minute,odbctimestamp.second,
2445 odbctimestamp.fraction / 1000000
2448 date2lineartime(odbctimestamp.year,odbctimestamp.month,odbctimestamp.day) +
2449 time2lineartime(odbctimestamp.hour,odbctimestamp.minute,odbctimestamp.second, odbctimestamp.fraction / 1000000);
2455 } // TODBCApiAgent::getColumnValueAsTimestamp
2458 // get value, returns false if no Data or Null
2459 bool TODBCApiAgent::getColumnValueAsTime(
2460 SQLHSTMT aStatement,
2465 SQL_TIMESTAMP_STRUCT odbctimestamp;
2467 if (getColumnAsODBCTimestamp(aStatement,aColNumber,odbctimestamp)) {
2468 // there is a timestamp
2469 aTime = time2lineartime(
2471 odbctimestamp.minute,
2472 odbctimestamp.second,
2473 odbctimestamp.fraction / 1000000
2480 } // TODBCApiAgent::getColumnValueAsTime
2483 // get value, returns false if no Data or Null
2484 bool TODBCApiAgent::getColumnValueAsDate(
2485 SQLHSTMT aStatement,
2490 SQL_TIMESTAMP_STRUCT odbctimestamp;
2492 if (getColumnAsODBCTimestamp(aStatement,aColNumber,odbctimestamp)) {
2493 // there is a timestamp
2494 aDate = date2lineartime(
2496 odbctimestamp.month,
2504 } // TODBCApiAgent::getColumnValueAsDate
2508 // get column value as database field
2509 bool TODBCApiAgent::getColumnValueAsField(
2510 SQLHSTMT aStatement, sInt16 aColIndex, TDBFieldType aDbfty, TItemField *aFieldP,
2511 TCharSets aDataCharSet, timecontext_t aTimecontext, bool aMoveToUserContext
2515 lineartime_t ts, basedate=0; // no base date yet
2517 timecontext_t tctx = TCTX_UNKNOWN;
2521 bool notnull=false; // default to empty
2522 // get pointer if assigning into timestamp field
2523 TTimestampField *tsfP = NULL;
2524 if (aFieldP->isBasedOn(fty_timestamp)) {
2525 tsfP = static_cast<TTimestampField *>(aFieldP);
2527 // field available in multifielditem
2529 case dbft_uctoffsfortime_hours:
2530 notnull=getColumnValueAsDouble(aStatement,aColIndex,hrs);
2531 if (!notnull) goto assignzone; // assign TCTX_UNKNOWN
2532 moffs=(sInt16)(hrs*MinsPerHour); // convert to minutes
2534 case dbft_uctoffsfortime_mins:
2535 notnull=getColumnValueAsLong(aStatement,aColIndex,i);
2536 if (!notnull) goto assignzone; // assign TCTX_UNKNOWN
2537 moffs=i; // these are minutes
2539 case dbft_uctoffsfortime_secs:
2540 notnull=getColumnValueAsLong(aStatement,aColIndex,i);
2541 if (!notnull) goto assignzone; // assign TCTX_UNKNOWN
2542 moffs = i / SecsPerMin;
2545 // get zone name as string
2546 notnull=getColumnValueAsString(aStatement,aColIndex,val,aDataCharSet, lem_cstr);
2547 if (!notnull) goto assignzone; // assign TCTX_UNKNOWN
2548 // convert to context
2549 TimeZoneNameToContext(val.c_str(), tctx, getSessionZones());
2552 tctx = TCTX_MINOFFSET(moffs);
2554 // zone works only for timestamps
2555 // - move to new zone or assign zone if timestamp is still empty or floating
2557 // first move to original context (to compensate for possible move to
2558 // fUserTimeContext done when reading timestamp with aMoveToUserContext)
2559 // Note: this is important for cases where the new zone is floating or dateonly
2560 // Note: if timestamp field had the "f" flag, it is still floating here, and will not be
2561 // moved to non-floating aTimecontext(=DB context) here.
2562 tsfP->moveToContext(aTimecontext,false);
2563 // now move to specified zone, or assign zone if timestamp is still floating here
2564 tsfP->moveToContext(tctx,true);
2568 case dbft_lineardate:
2569 case dbft_unixdate_s:
2570 case dbft_unixdate_ms:
2571 case dbft_unixdate_us:
2572 notnull=getColumnValueAsLong(aStatement,aColIndex,dbts);
2574 ts=dbIntToLineartime(dbts,aDbfty);
2578 notnull=getColumnValueAsDate(aStatement,aColIndex,ts);
2580 tctx = TCTX_UNKNOWN | TCTX_DATEONLY; // dates are always floating
2582 case dbft_timefordate:
2583 // get base date used to build up datetime
2585 basedate = tsfP->getTimestampAs(TCTX_UNKNOWN); // unconverted, as-is
2587 // otherwise handle like time
2589 notnull=getColumnValueAsTime(aStatement,aColIndex,ts);
2590 // combine with date
2593 case dbft_timestamp:
2594 notnull=getColumnValueAsTimestamp(aStatement,aColIndex,ts);
2596 case dbft_lineartime:
2597 case dbft_unixtime_s:
2599 case dbft_unixtime_ms:
2600 case dbft_unixtime_us:
2601 notnull=getColumnValueAsLong(aStatement,aColIndex,dbts);
2603 ts=dbIntToLineartime(dbts,aDbfty);
2605 // time values will be put into aTimecontext (usually <datatimezone>, but can
2606 // be TCTX_UNKNOWN for field explicitly marked floating in DB with <map mode="f"...>
2607 tctx = aTimecontext;
2609 // something convertible to lineartime_t
2611 // first, if output in user zone is selected, move timestamp to user zone
2612 // Note: before moving to a field-specific zone, this will be reverted such that
2613 // field-specific TZ=DATE conversion will be done in the original (DB) TZ context
2614 if (aMoveToUserContext) {
2615 if (TzConvertTimestamp(ts,tctx,fUserTimeContext,getSessionZones()))
2616 tctx = fUserTimeContext; // moved to user zone
2618 // now store in item field
2620 // assign timestamp and context passed (TCTX_UNKNOWN for floating)
2621 tsfP->setTimestampAndContext(ts,tctx);
2624 // destination is NOT timestamp, assign ISO date/time string,
2625 // either UTC or qualified with local zone offset
2626 bool dateonly = aDbfty==dbft_date || aDbfty==dbft_dateonly;
2627 tctx = dateonly ? TCTX_UNKNOWN | TCTX_DATEONLY : aTimecontext;
2628 // - aWithTime, NOT aAsUTC, aWithOffset, offset, NOT aShowOffset
2629 TimestampToISO8601Str(val, ts, tctx, false, false);
2630 aFieldP->setAsString(val.c_str());
2634 aFieldP->assignEmpty(); // NULL value: not assigned
2637 // Database field is BLOB, assign it to item as binary string
2638 if ((notnull=getColumnValueAsString(aStatement,aColIndex,val,chs_unknown,true)))
2639 aFieldP->setAsString(val.c_str(),val.size());
2641 aFieldP->assignEmpty(); // NULL value: not assigned
2646 // Database field is string (or unknown), assign to item as string
2647 if ((notnull=getColumnValueAsString(aStatement,aColIndex,val,aDataCharSet)))
2648 aFieldP->setAsString(val.c_str());
2650 aFieldP->assignEmpty(); // NULL value: not assigned
2654 } // TODBCApiAgent::getColumnValueAsField
2657 // bind parameters (and values for IN-Params) to the statement
2658 void TODBCApiAgent::bindSQLParameters(
2659 TSyncSession *aSessionP,
2660 SQLHSTMT aStatement,
2661 TParameterMapList &aParamMapList, // the list of mapped parameters
2662 TCharSets aDataCharSet,
2663 TLineEndModes aDataLineEndMode
2666 SQLSMALLINT valueType,paramType;
2667 SQLUSMALLINT paramNo=1;
2670 TParameterMapList::iterator pos;
2672 for (pos=aParamMapList.begin();pos!=aParamMapList.end();++pos) {
2673 // bind parameter to statement
2675 colSiz=std_paramsize;
2676 switch (pos->parammode) {
2677 case param_localid_str:
2678 case param_localid_int:
2679 case param_remoteid_str:
2680 case param_remoteid_int:
2681 pos->StrLen_or_Ind=0; // no size indication known yet
2682 // a item id, always as string
2683 valueType=SQL_C_CHAR;
2684 // but column could also be integer
2685 paramType=pos->parammode==param_localid_int || pos->parammode==param_remoteid_int ? SQL_INTEGER : SQL_VARCHAR;
2686 // get input value (if any)
2690 pos->ParameterValuePtr=(void *)(
2691 pos->parammode==param_localid_str || pos->parammode==param_localid_int ?
2692 pos->itemP->getLocalID() :
2693 pos->itemP->getRemoteID()
2698 pos->ParameterValuePtr=(void *)""; // empty value
2700 pos->StrLen_or_Ind = strlen((const char *)pos->ParameterValuePtr); // actual length
2702 // maximum value size
2703 pos->BufferLength=std_paramsize+1; // %%% fixed value for ids, should be enough for all cases
2704 colSiz=std_paramsize;
2707 pos->StrLen_or_Ind=0; // no size indication known for field yet (for buffer we use predefined values)
2709 // determine value type and pointer
2710 switch (pos->dbFieldType) {
2712 valueType=SQL_C_CHAR;
2713 paramType=SQL_INTEGER;
2716 valueType=SQL_C_BINARY;
2717 paramType=SQL_LONGVARBINARY;
2722 if (aDataCharSet==chs_utf16) {
2724 valueType=SQL_C_WCHAR;
2725 paramType=SQL_WVARCHAR;
2731 valueType=SQL_C_CHAR;
2732 paramType=SQL_VARCHAR;
2736 // that's all for param with already prepared buffer
2737 if (pos->parammode==param_buffer)
2739 // get maximum value size
2740 colSiz=pos->maxSize;
2741 if (pos->maxSize==0) {
2742 // no max size specified
2743 colSiz=std_paramsize; // default to standard size
2745 // %%%no longer, we calc it below now%%% pos->BufferLength=colSiz+1;
2748 // get actual value to pass as param into string s2
2749 switch (pos->dbFieldType) {
2753 // get as app string
2754 pos->fieldP->getAsString(s1);
2755 // convert to database string
2758 if (aDataCharSet==chs_utf16) {
2760 appendUTF8ToUTF16ByteString(
2765 pos->maxSize // max size (0 = unlimited)
2769 #endif // ODBC_UNICODE
2777 qm_none, // no quoting needed
2778 pos->maxSize // max size (0 = unlimited)
2783 #ifndef _MSC_VER // does not understand warnings
2784 #warning "maybe we should store blobs using SQLPutData and SQL_DATA_AT_EXEC mode"
2787 // get as unconverted binary chunk
2788 if (pos->fieldP->isBasedOn(fty_blob))
2789 static_cast<TBlobField *>(pos->fieldP)->getBlobAsString(s2);
2791 pos->fieldP->getAsString(s2);
2794 // s2 now contains the value
2795 pos->ParameterValuePtr=(void *)s2.c_str(); // value
2796 // default to full length of input string
2797 pos->StrLen_or_Ind=s2.size(); // size
2798 pos->BufferLength=s2.size(); // default to as long as string is
2799 // limit to max size
2800 if (pos->maxSize!=0 && pos->maxSize<s2.size()) {
2801 pos->BufferLength=pos->maxSize;
2802 pos->StrLen_or_Ind=pos->maxSize;
2804 // expand buffer to colsiz if input is larger than default column size
2805 if (colSiz<pos->BufferLength)
2806 colSiz=pos->BufferLength;
2807 // buffer for output must be at least colsiz (which is maxsize, or std_paramsize if no maxsize specified)
2808 if (pos->outparam) {
2809 // input/output param, make buffer minimally as large as indicated by colsiz
2810 if (pos->BufferLength<colSiz)
2811 pos->BufferLength=colSiz;
2813 // plus one for terminator
2814 pos->BufferLength+=1;
2815 copyvalue=true; // we need to copy it
2819 } // switch parammode
2820 // if this is an out param, create a buffer of BufferLength
2821 if (pos->parammode!=param_buffer) {
2822 if (pos->outparam || copyvalue) {
2823 // determine needed size of buffer
2824 /* %%% no longer needed as we calculate buffer size above now
2825 if (!pos->outparam || pos->BufferLength<pos->StrLen_or_Ind+1) {
2827 colSiz=pos->StrLen_or_Ind;
2828 // adapt buffer size to actual length of input param
2829 pos->BufferLength=colSiz+1; // set buffer size to what we need for input parameter
2833 void *bP = sysync_malloc(pos->BufferLength);
2834 pos->mybuffer=true; // this is now a buffer allocated by myself
2836 // init buffer with input value
2837 memcpy(bP,pos->ParameterValuePtr,pos->StrLen_or_Ind+1);
2839 // pass buffer, not original value
2840 pos->ParameterValuePtr = bP;
2841 // make sure buffer contains a NUL terminator
2842 *((uInt8 *)bP+pos->StrLen_or_Ind)=0;
2844 if (pos->outparam && !pos->inparam)
2845 pos->StrLen_or_Ind=SQL_NULL_DATA; // out only has no indicator for input
2847 // now actually bind to parameter
2848 POBJDEBUGPRINTFX(aSessionP,DBG_DBAPI+DBG_EXOTIC,(
2849 "SQLBind: paramNo=%hd, in=%d, out=%d, parammode=%hd, valuetype=%hd, paramtype=%hd, lenorind=%ld, valptr=%lX, bufsiz=%ld, maxcol=%ld, colsiz=%ld",
2853 (uInt16) (pos->outparam ? (pos->inparam ? SQL_PARAM_INPUT_OUTPUT : SQL_PARAM_OUTPUT ) : SQL_PARAM_INPUT), // type of param
2857 pos->ParameterValuePtr,
2862 SQLRETURN res=SQLBindParameter(
2864 paramNo, // parameter number
2865 pos->outparam ? (pos->inparam ? SQL_PARAM_INPUT_OUTPUT : SQL_PARAM_OUTPUT ) : SQL_PARAM_INPUT, // type of param
2866 valueType, // value type: how we want it represented
2867 paramType, // parameter type (integer or char)
2868 (colSiz==0 ? 1 : colSiz), // column size, relevant for char parameter only (must never be 0, even for zero length input-only params)
2869 0, // decimal digits
2870 pos->ParameterValuePtr, // parameter value
2871 pos->BufferLength, // value buffer size (for output params)
2872 (SQLLEN*)&(pos->StrLen_or_Ind) // length or indicator
2874 checkStatementError(res,aStatement);
2877 } // for all parametermaps
2878 } // TODBCApiAgent::bindSQLParameters
2881 // save out parameter values and clean up
2882 void TODBCApiAgent::saveAndCleanupSQLParameters(
2883 TSyncSession *aSessionP,
2884 SQLHSTMT aStatement,
2885 TParameterMapList &aParamMapList, // the list of mapped parameters
2886 TCharSets aDataCharSet,
2887 TLineEndModes aDataLineEndMode
2890 SQLUSMALLINT paramNo=1;
2892 TParameterMapList::iterator pos;
2893 for (pos=aParamMapList.begin();pos!=aParamMapList.end();++pos) {
2894 // save output parameter value
2895 if (pos->outparam) {
2896 switch (pos->parammode) {
2897 case param_localid_str:
2898 case param_localid_int:
2899 case param_remoteid_str:
2900 case param_remoteid_int:
2901 // id output params are always strings
2902 POBJDEBUGPRINTFX(aSessionP,DBG_DBAPI+DBG_EXOTIC,(
2903 "Postprocessing Item ID param: in=%d, out=%d, lenorind=%ld, valptr=%lX, bufsiz=%ld",
2907 pos->ParameterValuePtr,
2910 /* Note: len or ind does not contain a useful value
2911 DEBUGPRINTFX(DBG_DBAPI,("%%%% saving remote or localid out param: len=%ld, '%s'",pos->StrLen_or_Ind, s.c_str()));
2912 if (pos->StrLen_or_Ind==SQL_NULL_DATA || pos->StrLen_or_Ind==SQL_NO_TOTAL)
2915 s.assign((const char *)pos->ParameterValuePtr,pos->StrLen_or_Ind);
2917 // SQL_C_CHAR is null terminated, so just assign
2918 s=(const char *)pos->ParameterValuePtr;
2921 if (pos->parammode==param_localid_str || pos->parammode==param_localid_int)
2922 pos->itemP->setLocalID(s.c_str());
2924 pos->itemP->setRemoteID(s.c_str());
2928 // buffer will be processed externally
2930 if (pos->StrLen_or_Ind==SQL_NULL_DATA || pos->StrLen_or_Ind==SQL_NO_TOTAL)
2931 *(pos->outSiz) = 0; // no output
2933 *(pos->outSiz) = pos->StrLen_or_Ind; // indicate how much is in buffer
2938 POBJDEBUGPRINTFX(aSessionP,DBG_DBAPI+DBG_EXOTIC,(
2939 "Postprocessing field param: in=%d, out=%d, lenorind=%ld, valptr=%lX, bufsiz=%ld",
2943 pos->ParameterValuePtr,
2947 if (pos->StrLen_or_Ind==SQL_NULL_DATA || pos->StrLen_or_Ind==SQL_NO_TOTAL)
2948 pos->fieldP->assignEmpty(); // NULL is empty
2950 // save value according to type
2951 switch (pos->dbFieldType) {
2956 /* Note: len or ind does not contain a useful value
2957 DEBUGPRINTFX(DBG_DBAPI,("%%%% saving remote or localid out param: len=%ld, '%s'",pos->StrLen_or_Ind, s.c_str()));
2958 if (pos->StrLen_or_Ind==SQL_NULL_DATA || pos->StrLen_or_Ind==SQL_NO_TOTAL)
2961 s.assign((const char *)pos->ParameterValuePtr,pos->StrLen_or_Ind);
2964 if (aDataCharSet==chs_utf16) {
2965 // SQL_C_WCHAR is null terminated, so just assign
2967 (const uInt16 *)pos->ParameterValuePtr,
2968 pos->StrLen_or_Ind, // num of 16-bit chars
2971 true, // convert line ends
2972 false // no filemaker CRs
2978 // SQL_C_CHAR is null terminated, so just assign
2979 s=(const char *)pos->ParameterValuePtr;
2980 // convert to app string
2983 (const char *)s.c_str(),
2989 pos->fieldP->setAsString(s2);
2992 // save as it comes from DB
2993 pos->fieldP->setAsString((const char *)pos->ParameterValuePtr,pos->StrLen_or_Ind);
2999 } // switch parammode
3001 // if there is a buffer, free it
3002 if (pos->mybuffer && pos->ParameterValuePtr) {
3003 // delete buffer if we have allocated one
3004 sysync_free(pos->ParameterValuePtr);
3005 pos->ParameterValuePtr=NULL;
3006 pos->mybuffer=false;
3010 } // for all parametermaps
3011 } // TODBCApiAgent::saveAndCleanupSQLParameters
3013 // Parameter handling for session level statements
3015 // bind parameters (and values for IN-Params) to the statement
3016 void TODBCApiAgent::bindSQLParameters(
3024 fConfigP->fDataCharSet,
3025 fConfigP->fDataLineEndMode
3027 } // TODBCApiAgent::bindSQLParameters
3030 // save out parameter values and clean up
3031 void TODBCApiAgent::saveAndCleanupSQLParameters(
3035 saveAndCleanupSQLParameters(
3039 fConfigP->fDataCharSet,
3040 fConfigP->fDataLineEndMode
3042 } // TODBCApiAgent::bindSQLParameters
3045 #endif // ODBCAPI_SUPPORT
3051 #ifdef SQLITE_SUPPORT
3053 // get SQLite error message for given result code
3054 // - returns false if no error
3055 bool TODBCApiAgent::getSQLiteError(int aRc,string &aMessage, sqlite3 *aDb)
3057 if (aRc == SQLITE_OK || aRc == SQLITE_ROW) return false; // no error
3058 StringObjPrintf(aMessage,"SQLite Error %d : %s",aRc,aDb==NULL ? "<cannot look up text>" : sqlite3_errmsg(aDb));
3063 // check if aResult signals error and throw exception if so
3064 void TODBCApiAgent::checkSQLiteError(int aRc, sqlite3 *aDb)
3068 if (getSQLiteError(aRc,msg,aDb)) {
3070 throw TSyncException(msg.c_str());
3072 } // TODBCApiAgent::checkSQLiteError
3075 // - check if aRc signals error and throw exception if so
3076 // returns true if data available, false if not
3077 bool TODBCApiAgent::checkSQLiteHasData(int aRc, sqlite3 *aDb)
3079 if (aRc==SQLITE_DONE)
3080 return false; // signal NO DATA
3081 else if (aRc==SQLITE_ROW)
3082 return true; // signal data
3084 checkSQLiteError(aRc,aDb);
3085 return true; // signal ok
3086 } // TODBCApiAgent::checkStatementHasData
3089 // get column value from SQLite result
3090 bool TODBCApiAgent::getSQLiteColValueAsField(
3091 sqlite3_stmt *aStatement, sInt16 aColIndex, TDBFieldType aDbfty, TItemField *aFieldP,
3092 TCharSets aDataCharSet, timecontext_t aTimecontext, bool aMoveToUserContext
3097 timecontext_t tctx = TCTX_UNKNOWN;
3101 // get pointer if assigning into timestamp field
3102 TTimestampField *tsfP = NULL;
3103 if (aFieldP->isBasedOn(fty_timestamp)) {
3104 tsfP = static_cast<TTimestampField *>(aFieldP);
3106 // determine if NULL
3107 bool notnull=sqlite3_column_type(aStatement,aColIndex)!=SQLITE_NULL; // if column is not null
3108 // field available in multifielditem
3110 case dbft_uctoffsfortime_hours:
3111 if (!notnull) goto assignzone; // assign TCTX_UNKNOWN
3112 hrs = sqlite3_column_double(aStatement,aColIndex);
3113 moffs=(sInt16)(hrs*MinsPerHour); // convert to minutes
3115 case dbft_uctoffsfortime_mins:
3116 if (!notnull) goto assignzone; // assign TCTX_UNKNOWN
3117 moffs = sqlite3_column_int(aStatement,aColIndex);
3119 case dbft_uctoffsfortime_secs:
3120 if (!notnull) goto assignzone; // assign TCTX_UNKNOWN
3121 moffs = sqlite3_column_int(aStatement,aColIndex);
3122 moffs /= SecsPerMin;
3125 if (!notnull) goto assignzone; // assign TCTX_UNKNOWN
3126 // get zone name as string
3127 appendStringAsUTF8((const char *)sqlite3_column_text(aStatement,aColIndex), val, aDataCharSet, lem_cstr);
3128 // convert to context
3129 TimeZoneNameToContext(val.c_str(), tctx, getSessionZones());
3132 tctx = TCTX_MINOFFSET(moffs);
3134 // zone works only for timestamps
3135 // - move to new zone or assign zone if timestamp is still empty or floating
3137 // first move to original context (to compensate for possible move to
3138 // fUserTimeContext done when reading timestamp with aMoveToUserContext)
3139 // Note: this is important for cases where the new zone is floating or dateonly
3140 // Note: if timestamp field had the "f" flag, it is still floating here, and will not be
3141 // moved to non-floating aTimecontext(=DB context) here.
3142 tsfP->moveToContext(aTimecontext,false);
3143 // now move to specified zone, or assign zone if timestamp is still floating here
3144 tsfP->moveToContext(tctx,true);
3148 case dbft_lineardate:
3149 case dbft_unixdate_s:
3150 case dbft_unixdate_ms:
3151 case dbft_unixdate_us:
3153 ts = dbIntToLineartime(sqlite3_column_int64(aStatement,aColIndex),aDbfty);
3154 tctx = TCTX_UNKNOWN | TCTX_DATEONLY; // dates are always floating
3156 case dbft_lineartime:
3157 case dbft_unixtime_s:
3159 case dbft_unixtime_ms:
3160 case dbft_unixtime_us:
3162 ts = dbIntToLineartime(sqlite3_column_int64(aStatement,aColIndex),aDbfty);
3163 // time values will be put into aTimecontext (usually <datatimezone>, but can
3164 // be TCTX_UNKNOWN for field explicitly marked floating in DB with <map mode="f"...>
3165 tctx = aTimecontext;
3167 // something convertible to lineartime_t
3169 // first, if output in user zone is selected, move timestamp to user zone
3170 // Note: before moving to a field-specific zone, this will be reverted such that
3171 // field-specific TZ=DATE conversion will be done in the original (DB) TZ context
3172 if (aMoveToUserContext) {
3173 if (TzConvertTimestamp(ts,tctx,fUserTimeContext,getSessionZones()))
3174 tctx = fUserTimeContext; // moved to user zone
3176 // now store in item field
3178 // assign timestamp and context (TCTX_UNKNOWN for floating)
3179 tsfP->setTimestampAndContext(ts,tctx);
3182 // destination is NOT timestamp, assign ISO date/time string,
3183 // either UTC or qualified with local zone offset
3184 bool dateonly = aDbfty==dbft_date || aDbfty==dbft_dateonly;
3185 tctx = dateonly ? TCTX_UNKNOWN | TCTX_DATEONLY : aTimecontext;
3186 // - aWithTime, NOT aAsUTC, aWithOffset, offset, NOT aShowOffset
3187 TimestampToISO8601Str(val, ts, tctx, false, false);
3188 aFieldP->setAsString(val.c_str());
3192 aFieldP->assignEmpty(); // NULL value: not assigned
3195 // Database field is BLOB, assign it to item as binary string
3198 siz = sqlite3_column_bytes(aStatement,aColIndex);
3201 aFieldP->setAsString((cAppCharP)sqlite3_column_blob(aStatement,aColIndex),siz);
3203 aFieldP->assignEmpty(); // NULL or empty value: not assigned
3208 // Database field is string (or unknown), assign to item as string
3210 appendStringAsUTF8((const char *)sqlite3_column_text(aStatement,aColIndex), val, aDataCharSet, lem_cstr); // Convert to app-charset (UTF8) and C-type lineends
3211 aFieldP->setAsString(val.c_str());
3214 aFieldP->assignEmpty(); // NULL value: not assigned
3218 } // TODBCApiAgent::getSQLiteColValueAsField
3221 // - prepare SQLite statement
3222 void TODBCApiAgent::prepareSQLiteStatement(
3225 sqlite3_stmt *&aStatement
3228 const char *sqltail;
3230 // discard possibly existing one
3232 sqlite3_finalize(aStatement);
3236 #if (SQLITE_VERSION_NUMBER>=3003009)
3237 // use new recommended v2 call with SQLite >=3.3.9
3238 int rc = sqlite3_prepare_v2(
3239 aDB, /* Database handle */
3240 aSQL, /* SQL statement, UTF-8 encoded */
3241 -1, /* null terminated string */
3242 &aStatement, /* OUT: Statement handle */
3243 &sqltail /* OUT: Pointer to unused portion of zSql */
3246 //Â use now deprecated call for older SQLite3 version compatibility
3247 int rc = sqlite3_prepare(
3248 aDB, /* Database handle */
3249 aSQL, /* SQL statement, UTF-8 encoded */
3250 -1, /* null terminated string */
3251 &aStatement, /* OUT: Statement handle */
3252 &sqltail /* OUT: Pointer to unused portion of zSql */
3255 checkSQLiteError(rc,aDB);
3256 } // TODBCApiAgent::prepareSQLiteStatement
3259 // bind parameter values to the statement (only IN-params for strings and BLOBs are supported at all)
3260 void TODBCApiAgent::bindSQLiteParameters(
3261 TSyncSession *aSessionP,
3262 sqlite3_stmt *aStatement,
3263 TParameterMapList &aParamMapList, // the list of mapped parameters
3264 TCharSets aDataCharSet,
3265 TLineEndModes aDataLineEndMode
3269 TParameterMapList::iterator pos;
3272 for (pos=aParamMapList.begin();pos!=aParamMapList.end();++pos) {
3273 // bind parameter to statement
3276 // SQLite only supports input params
3277 switch (pos->parammode) {
3280 // get actual value to pass as param into string s2
3281 switch (pos->dbFieldType) {
3285 // get as app string
3286 pos->fieldP->getAsString(s1);
3287 // convert to database string
3294 qm_none, // no quoting needed
3295 pos->maxSize // max size (0 = unlimited)
3297 // s2 is on the stack and will be deallocated on routine exit
3298 rc=sqlite3_bind_text(aStatement,paramno++,s2.c_str(),s2.size(),SQLITE_TRANSIENT);
3301 // get as unconverted binary chunk
3302 if (pos->fieldP->isBasedOn(fty_blob)) {
3305 static_cast<TBlobField *>(pos->fieldP)->getBlobDataPtrSz(ptr,sz);
3306 // BLOB buffer remains static as until statement is finalized
3307 rc=sqlite3_bind_blob(aStatement,paramno++,ptr,sz,SQLITE_STATIC);
3310 pos->fieldP->getAsString(s2);
3311 // s2 is on the stack and will be deallocated on routine exit
3312 rc=sqlite3_bind_blob(aStatement,paramno++,s2.c_str(),s2.size(),SQLITE_TRANSIENT);
3319 // we already have buffer pointers
3320 switch (pos->dbFieldType) {
3324 // external buffer remains stable until statement finalizes
3325 rc=sqlite3_bind_text(aStatement,paramno++,(const char *)pos->ParameterValuePtr,pos->StrLen_or_Ind,SQLITE_STATIC);
3328 // external buffer remains stable until statement finalizes
3329 rc=sqlite3_bind_blob(aStatement,paramno++,pos->ParameterValuePtr,pos->StrLen_or_Ind,SQLITE_STATIC);
3333 case param_localid_int:
3334 case param_localid_str:
3335 case param_remoteid_int:
3336 case param_remoteid_str:
3339 } // switch parammode
3340 if (rc==SQLITE_OK) {
3341 POBJDEBUGPRINTFX(aSessionP,DBG_DBAPI+DBG_EXOTIC,(
3342 "Bound Param #%d to SQLite statement",
3351 POBJDEBUGPRINTFX(aSessionP,DBG_ERROR,("SQLite only supports IN params"));
3353 } // for all parametermaps
3354 } // TODBCApiAgent::bindSQLiteParameters
3357 #endif // SQLITE_SUPPORT
3361 // Session level DB access
3362 // =======================
3365 #ifdef ODBCAPI_SUPPORT
3367 // get database time
3368 lineartime_t TODBCApiAgent::getDatabaseNowAs(timecontext_t aTimecontext)
3372 if (fConfigP->fGetCurrentDateTimeSQL.empty()) {
3373 return inherited::getDatabaseNowAs(aTimecontext); // just use base class' implementation
3376 // query database for current time
3378 SQLHSTMT statement=newStatementHandle(getODBCConnectionHandle());
3381 PDEBUGPUTSX(DBG_DBAPI,"SQL for getting current date/time:");
3382 PDEBUGPUTSX(DBG_DBAPI,fConfigP->fGetCurrentDateTimeSQL.c_str());
3384 TP_SWITCH(li,fTPInfo,TP_database);
3385 res = SafeSQLExecDirect(
3387 (SQLCHAR *)fConfigP->fGetCurrentDateTimeSQL.c_str(),
3390 TP_START(fTPInfo,li);
3391 checkStatementError(res,statement);
3392 // - fetch result row
3393 res=SafeSQLFetch(statement);
3394 if (!checkStatementHasData(res,statement))
3395 throw TSyncException("no data getting date/time from database");
3396 // get datetime (in database time context)
3397 if (!getColumnValueAsTimestamp(statement,1,now))
3398 throw TSyncException("bad data getting date/time from database");
3399 SafeSQLCloseCursor(statement);
3400 // dispose statement handle
3401 SafeSQLFreeHandle(SQL_HANDLE_STMT,statement);
3404 // dispose statement handle
3405 SafeSQLCloseCursor(statement);
3406 SafeSQLFreeHandle(SQL_HANDLE_STMT,statement);
3409 // convert to requested zone
3410 TzConvertTimestamp(now,fConfigP->fCurrentDateTimeZone,aTimecontext,getSessionZones(),TCTX_UNKNOWN);
3413 } // TODBCApiAgent::getDatabaseNowAs
3416 #ifdef SYSYNC_SERVER
3418 // info about requested auth type
3419 TAuthTypes TODBCApiAgent::requestedAuthType(void)
3421 TAuthTypes auth = TSyncAgent::requestedAuthType();
3423 // make sure that we don't request MD5 if we cannot check it:
3424 // either we need plain text passwords in the DB or SyncML V1.1 MD5 schema
3425 if (auth==auth_md5 && fSyncMLVersion<syncml_vers_1_1 && !fConfigP->fClearTextPw) {
3426 PDEBUGPRINTFX(DBG_ADMIN,("Switching down to Basic Auth because server cannot check pre-V1.1 MD5"));
3427 auth=auth_basic; // force basic auth, this can be checked
3430 } // TODBCApiAgent::requestedAuthType
3433 // %%% note: make the following available in clients as well if they support more than
3434 // pseudo-auth by local config (that is, real nonce is needed)
3436 // - get nonce string, which is expected to be used by remote party for MD5 auth.
3437 void TODBCApiAgent::getAuthNonce(const char *aDeviceID, string &aAuthNonce)
3439 if (!aDeviceID || fConfigP->fSaveNonceSQL.empty()) {
3440 // no device ID or no persistent nonce, use method of ancestor
3441 TStdLogicAgent::getAuthNonce(aDeviceID,aAuthNonce);
3444 // we have a persistent nonce
3445 DEBUGPRINTFX(DBG_ADMIN,("getAuthNonce: current auth nonce='%s'",fLastNonce.c_str()));
3446 aAuthNonce=fLastNonce.c_str();
3448 } // TODBCApiAgent::getAuthNonce
3451 // get next nonce (to be sent to remote party)
3452 void TODBCApiAgent::getNextNonce(const char *aDeviceID, string &aNextNonce)
3458 if (fConfigP->fSaveNonceSQL.empty()) {
3459 // no persistent nonce, use method of ancestor
3460 TStdLogicAgent::getNextNonce(aDeviceID,aNextNonce);
3463 // we have persistent nonce, create a new one and save it
3464 // - create one (pure 7bit ASCII)
3465 generateNonce(aNextNonce,aDeviceID,getSystemNowAs(TCTX_UTC)+rand());
3468 statement=newStatementHandle(getODBCConnectionHandle());
3471 sql = fConfigP->fSaveNonceSQL;
3472 // - substitute: %N = new nonce
3473 StringSubst(sql,"%N",aNextNonce,2,chs_ascii,lem_none,fConfigP->fQuotingMode);
3474 // - standard substitutions: %u=userkey, %d=devicekey
3475 resetSQLParameterMaps();
3476 DoSQLSubstitutions(sql);
3477 bindSQLParameters(statement);
3479 PDEBUGPUTSX(DBG_ADMIN+DBG_DBAPI,"SQL for saving nonce");
3480 PDEBUGPUTSX(DBG_ADMIN+DBG_DBAPI,sql.c_str());
3482 TP_SWITCH(li,fTPInfo,TP_database);
3483 res = SafeSQLExecDirect(
3485 (SQLCHAR *)sql.c_str(),
3488 TP_START(fTPInfo,li);
3489 checkStatementError(res,statement);
3490 saveAndCleanupSQLParameters(statement);
3491 // commit saved nonce
3492 SafeSQLFreeHandle(SQL_HANDLE_STMT,statement);
3493 SafeSQLEndTran(SQL_HANDLE_DBC,getODBCConnectionHandle(),SQL_COMMIT);
3495 catch (exception &e) {
3496 // release the statement handle
3497 SafeSQLFreeHandle(SQL_HANDLE_STMT,statement);
3498 SafeSQLEndTran(SQL_HANDLE_DBC,getODBCConnectionHandle(),SQL_ROLLBACK);
3502 catch (exception &e) {
3503 PDEBUGPRINTFX(DBG_ERROR,("Failed saving nonce: %s",e.what()));
3504 aNextNonce=fLastNonce; // return last nonce again
3507 } // TODBCApiAgent::getNextNonce
3509 #endif // SYSYNC_SERVER
3512 #ifdef HAS_SQL_ADMIN
3514 // cleanup what is needed after login
3515 void TODBCApiAgent::LoginCleanUp(void)
3520 // close the transaction as well
3521 if (fODBCConnectionHandle!=SQL_NULL_HANDLE) {
3522 res=SafeSQLEndTran(SQL_HANDLE_DBC,getODBCConnectionHandle(),SQL_ROLLBACK);
3523 checkODBCError(res,SQL_HANDLE_DBC,getODBCConnectionHandle());
3526 catch (exception &e) {
3528 PDEBUGPRINTFX(DBG_ERROR,("TODBCApiAgent::LoginCleanUp: Exception: %s",e.what()));
3530 } // TODBCApiAgent::LoginCleanUp
3533 // check device related stuff
3534 void TODBCApiAgent::CheckDevice(const char *aDeviceID)
3536 // first let ancestor
3537 if (!fConfigP->fGetDeviceSQL.empty()) {
3541 statement=newStatementHandle(getODBCConnectionHandle());
3543 bool creatednew=false;
3546 sql = fConfigP->fGetDeviceSQL;
3547 // substitute: %D = deviceID
3548 StringSubst(sql,"%D",aDeviceID,2,-1,fConfigP->fDataCharSet,fConfigP->fDataLineEndMode,fConfigP->fQuotingMode);
3550 PDEBUGPUTSX(DBG_ADMIN+DBG_DBAPI,"SQL for getting device key (and last nonce sent to device)");
3551 PDEBUGPUTSX(DBG_ADMIN+DBG_DBAPI,sql.c_str());
3553 TP_SWITCH(li,fTPInfo,TP_database);
3554 res = SafeSQLExecDirect(
3556 (SQLCHAR *)sql.c_str(),
3559 TP_START(fTPInfo,li);
3560 checkStatementHasData(res,statement);
3561 // - fetch result row
3562 res=SafeSQLFetch(statement);
3563 if (!checkStatementHasData(res,statement)) {
3565 throw TSyncException("Fatal: inserted device record cannot be found again");
3567 PDEBUGPRINTFX(DBG_ADMIN,("Unknown device '%.30s', creating new record",aDeviceID));
3569 #ifdef SYSYNC_SERVER
3570 // device does not exist yet
3574 // create new device
3575 SafeSQLCloseCursor(statement);
3577 sql = fConfigP->fNewDeviceSQL;
3578 string dk; // temp device key
3579 // - substitute: %D = deviceID
3580 StringSubst(sql,"%D",aDeviceID,2,-1,fConfigP->fDataCharSet,fConfigP->fDataLineEndMode,fConfigP->fQuotingMode);
3582 PDEBUGPUTSX(DBG_ADMIN+DBG_DBAPI,"SQL for inserting new device (and last nonce sent to device)");
3583 PDEBUGPUTSX(DBG_ADMIN+DBG_DBAPI,sql.c_str());
3585 TP_SWITCH(li,fTPInfo,TP_database);
3586 res = SafeSQLExecDirect(
3588 (SQLCHAR *)sql.c_str(),
3591 TP_START(fTPInfo,li);
3592 checkStatementError(res,statement);
3593 // commit new device
3594 SafeSQLEndTran(SQL_HANDLE_DBC,getODBCConnectionHandle(),SQL_COMMIT);
3595 // try again to get device key
3600 // device exists, read key and nonce
3601 #ifdef SCRIPT_SUPPORT
3602 fUnknowndevice=false; // we have seen it once before at least
3605 getColumnValueAsString(statement,1,fDeviceKey,chs_ascii);
3609 getColumnValueAsString(statement,2,dummy,chs_ascii);
3612 #ifdef SYSYNC_SERVER
3613 getColumnValueAsString(statement,2,fLastNonce,chs_ascii);
3617 SafeSQLCloseCursor(statement);
3618 PDEBUGPRINTFX(DBG_ADMIN,("Device '%.30s' found, fDeviceKey='%.30s'",aDeviceID,fDeviceKey.c_str()));
3620 #ifdef SYSYNC_SERVER
3621 DEBUGPRINTFX(DBG_ADMIN,("Last nonce saved for device='%.30s'",fLastNonce.c_str()));
3626 } while (true); // do until device found or created new
3629 // release the statement handle
3630 SafeSQLCloseCursor(statement);
3631 SafeSQLFreeHandle(SQL_HANDLE_STMT,statement);
3632 SafeSQLEndTran(SQL_HANDLE_DBC,getODBCConnectionHandle(),SQL_ROLLBACK);
3635 // release the statement handle
3636 SafeSQLCloseCursor(statement);
3637 SafeSQLFreeHandle(SQL_HANDLE_STMT,statement);
3638 } // if device table implemented
3639 } // TODBCApiAgent::CheckDevice
3642 // check credential string
3643 bool TODBCApiAgent::CheckLogin(const char *aOriginalUserName, const char *aModifiedUserName, const char *aAuthString, TAuthSecretTypes aAuthStringType, const char *aDeviceID)
3645 bool authok = false;
3650 // - if no userkey query, we cannot proceed and must fail auth now
3651 if (fConfigP->fUserKeySQL.empty()) return false;
3652 // - get nonce (if we have a device table, we should have read it by now)
3653 if (aAuthStringType==sectyp_md5_V10 || aAuthStringType==sectyp_md5_V11)
3654 getAuthNonce(aDeviceID,nonce);
3655 // - try to obtain an appropriate user key
3656 statement=newStatementHandle(getODBCConnectionHandle());
3659 string sql = fConfigP->fUserKeySQL;
3660 string uk; // temp user key
3661 // substitute: %U = original username as sent by remote,
3662 // %dU = username modified for DB search,
3663 // %M = authstring, %N = Nonce string
3664 StringSubst(sql,"%U",aOriginalUserName,2,-1,fConfigP->fDataCharSet,fConfigP->fDataLineEndMode,fConfigP->fQuotingMode);
3665 StringSubst(sql,"%dU",aModifiedUserName,3,-1,fConfigP->fDataCharSet,fConfigP->fDataLineEndMode,fConfigP->fQuotingMode);
3666 // %%% probably obsolete
3667 StringSubst(sql,"%M",aAuthString,2,-1,fConfigP->fDataCharSet,fConfigP->fDataLineEndMode,fConfigP->fQuotingMode);
3668 StringSubst(sql,"%N",nonce,2,fConfigP->fDataCharSet,fConfigP->fDataLineEndMode,fConfigP->fQuotingMode);
3669 // now do the standard stuff
3670 resetSQLParameterMaps();
3671 DoSQLSubstitutions(sql);
3672 bindSQLParameters(statement);
3674 PDEBUGPUTSX(DBG_ADMIN+DBG_DBAPI,"SQL for getting user key (and password/md5userpass if not checked in query):");
3675 PDEBUGPUTSX(DBG_ADMIN+DBG_DBAPI,sql.c_str());
3677 TP_SWITCH(li,fTPInfo,TP_database);
3678 res = SafeSQLExecDirect(
3680 (SQLCHAR *)sql.c_str(),
3683 TP_START(fTPInfo,li);
3684 checkStatementHasData(res,statement);
3685 saveAndCleanupSQLParameters(statement);
3686 // - fetch result row
3687 res=SafeSQLFetch(statement);
3688 if (fConfigP->fClearTextPw || fConfigP->fMD5UserPass) {
3690 // go though all returned rows until match (same username might be used with 2 different pws)
3691 while (checkStatementHasData(res,statement)) {
3692 // read in ascending index order!!
3696 getColumnValueAsString(statement,col,uk,chs_ascii);
3698 #ifdef SCRIPT_SUPPORT
3700 // also store it in first local context var
3701 if (fAgentContext) {
3702 hfP=fAgentContext->getLocalVar(0);
3703 if (hfP) hfP->setAsString(uk.c_str());
3706 // also store it in fUserKey already for USERKEY() function
3708 // - password or md5userpass
3709 getColumnValueAsString(statement,col,secret,chs_ascii);
3710 // - convert from hex to b64 if needed
3711 if (fConfigP->fMD5UPAsHex && secret.size()>=32) {
3713 cAppCharP p=secret.c_str();
3714 for (int k=0; k<16; k++) {
3716 if (HexStrToUShort(p+(k*2),h,2)<2) break;
3719 p = b64::encode(&md5[0],16);
3720 secret = p; // save converted string
3721 b64::free((void *)p);
3724 #ifdef SCRIPT_SUPPORT
3725 // also store it in second local context var
3726 if (fAgentContext) {
3727 hfP=fAgentContext->getLocalVar(1);
3728 if (hfP) hfP->setAsString(secret.c_str());
3729 // if result set has more columns, save them as well
3730 SQLSMALLINT numcols;
3731 // - get number of columns
3732 res = SafeSQLNumResultCols(statement,&numcols);
3733 checkStatementError(res,statement);
3734 // - save remaining columns into defined variables
3735 while (col<=numcols) {
3736 // check if enough variables
3737 if (col>fAgentContext->getNumLocals()) break;
3739 hfP=fAgentContext->getLocalVar(col-1);
3741 // timestamp is special
3742 if (hfP->getType()==fty_timestamp) {
3745 if (!getColumnValueAsTimestamp(statement,col,ts)) break;
3746 static_cast<TTimestampField *>(hfP)->setTimestampAndContext(ts,fConfigP->fCurrentDateTimeZone);
3749 // get and assign as string
3751 if (!getColumnValueAsString(statement,col,v,chs_ascii)) break;
3752 hfP->setAsString(v.c_str());
3758 // make standard auth check
3759 if (fConfigP->fClearTextPw) {
3760 // we have the clear text password, check against what was transmitted from remote
3761 authok=checkAuthPlain(aOriginalUserName,secret.c_str(),nonce.c_str(),aAuthString,aAuthStringType);
3764 // whe have the MD5 of user:password, check against what was transmitted from remote
3765 // Note: this can't work with non-V1.1 type creds
3766 authok=checkAuthMD5(aOriginalUserName,secret.c_str(),nonce.c_str(),aAuthString,aAuthStringType);
3768 #ifdef SCRIPT_SUPPORT
3769 // if there is a script, call it to perform decision
3770 // - refresh auth status
3771 fStandardAuthOK=authok; // for AUTHOK() func
3772 // - call script (if any)
3773 fScriptContextDatastore=NULL;
3774 authok=TScriptContext::executeTest(
3775 authok, // default for no script, no result or script error is current auth
3777 fConfigP->fLoginCheckScript,
3778 fConfigP->getAgentFuncTableP(), // context function table
3779 (void *)this // context data (myself)
3782 if (authok) break; // found, authorized
3784 res=SafeSQLFetch(statement);
3788 // AuthString was checked in SQL query
3789 // - no record found -> no auth
3790 if (res==SQL_NO_DATA) authok=false;
3793 // now assign user key
3795 PDEBUGPRINTFX(DBG_ADMIN,("Auth successful, fUserKey='%s'",fUserKey.c_str()));
3798 // we do not have a valid user key
3803 // release the statement handle
3804 SafeSQLCloseCursor(statement);
3805 SafeSQLFreeHandle(SQL_HANDLE_STMT,statement);
3806 SafeSQLEndTran(SQL_HANDLE_DBC,getODBCConnectionHandle(),SQL_ROLLBACK);
3809 // release the statement handle
3810 SafeSQLCloseCursor(statement);
3811 SafeSQLFreeHandle(SQL_HANDLE_STMT,statement);
3812 // return auth status
3814 } // TODBCApiAgent::CheckLogin
3817 // do something with the analyzed data
3818 void TODBCApiAgent::remoteAnalyzed(void)
3824 // call ancestor first
3825 inherited::remoteAnalyzed();
3826 if (!fConfigP->fSaveInfoSQL.empty()) {
3828 statement=newStatementHandle(getODBCConnectionHandle());
3831 sql = fConfigP->fSaveInfoSQL;
3832 // %nR Remote name: [Manufacturer ]Model")
3833 StringSubst(sql,"%nR",getRemoteDescName(),3,-1,fConfigP->fDataCharSet,fConfigP->fDataLineEndMode,fConfigP->fQuotingMode);
3834 // %vR Remote Device Version Info ("Type (HWV, FWV, SWV) Oem")
3835 StringSubst(sql,"%vR",getRemoteInfoString(),3,-1,fConfigP->fDataCharSet,fConfigP->fDataLineEndMode,fConfigP->fQuotingMode);
3836 // - standard substitutions: %u=userkey, %d=devicekey
3837 resetSQLParameterMaps();
3838 DoSQLSubstitutions(sql);
3839 bindSQLParameters(statement);
3841 PDEBUGPUTSX(DBG_ADMIN+DBG_DBAPI,"SQL for saving device info");
3842 PDEBUGPUTSX(DBG_ADMIN+DBG_DBAPI,sql.c_str());
3844 TP_SWITCH(li,fTPInfo,TP_database);
3845 res = SafeSQLExecDirect(
3847 (SQLCHAR *)sql.c_str(),
3850 TP_START(fTPInfo,li);
3851 checkStatementError(res,statement);
3852 saveAndCleanupSQLParameters(statement);
3853 // commit new device
3854 SafeSQLFreeHandle(SQL_HANDLE_STMT,statement);
3855 SafeSQLEndTran(SQL_HANDLE_DBC,getODBCConnectionHandle(),SQL_COMMIT);
3857 catch (exception &e) {
3858 // release the statement handle
3859 SafeSQLFreeHandle(SQL_HANDLE_STMT,statement);
3860 SafeSQLEndTran(SQL_HANDLE_DBC,getODBCConnectionHandle(),SQL_ROLLBACK);
3861 PDEBUGPRINTFX(DBG_ERROR,("Failed saving device info: %s",e.what()));
3864 } // TODBCApiAgent::remoteAnalyzed
3866 #endif // HAS_SQL_ADMIN
3868 #endif // ODBCAPI_SUPPORT
3872 #ifdef SYSYNC_SERVER
3874 void TODBCApiAgent::RequestEnded(bool &aHasData)
3876 // first let ancestors finish their stuff
3877 inherited::RequestEnded(aHasData);
3878 #ifdef ODBCAPI_SUPPORT
3879 // now close the ODBC connection if there is one left at the session level
3880 // and no script statement is still unfinished
3881 #ifdef SCRIPT_SUPPORT
3882 if (!fScriptStatement)
3885 closeODBCConnection(fODBCConnectionHandle);
3888 } // TODBCApiAgent::RequestEnded
3890 #endif // SYSYNC_SERVER
3893 // factory methods of Agent config
3894 // ===============================
3896 #ifdef SYSYNC_CLIENT
3898 TSyncAgent *TOdbcAgentConfig::CreateClientSession(const char *aSessionID)
3900 // return appropriate client session
3901 MP_RETURN_NEW(TODBCApiAgent,DBG_HOT,"TODBCApiAgent",TODBCApiAgent(getSyncAppBase(),NULL,aSessionID));
3902 } // TOdbcAgentConfig::CreateClientSession
3906 #ifdef SYSYNC_SERVER
3908 TSyncAgent *TOdbcAgentConfig::CreateServerSession(TSyncSessionHandle *aSessionHandle, const char *aSessionID)
3910 // return XML2GO or ODBC-server session
3911 MP_RETURN_NEW(TODBCApiAgent,DBG_HOT,"TODBCApiAgent",TODBCApiAgent(getSyncAppBase(),aSessionHandle,aSessionID));
3912 } // TOdbcAgentConfig::CreateServerSession
3917 } // namespace sysync
3919 /* end of TODBCApiAgent implementation */
3921 #endif // SQL_SUPPORT