From d408593c1acbdde72b03e6a99816451224d7839d Mon Sep 17 00:00:00 2001 From: "Thiago A. Correa" Date: Fri, 13 Jul 2012 14:28:18 -0300 Subject: Fix error when inserting to tables with datetime fields with QODBC SQL Server 10 introduced stricter rules for TIMESTAMP validation, making it necessary to specify the decimal digits. Other databases might do the same as well, so this patch introduces a check for the TIMESTAMP column size and adjusts the decimal digits parameter as needed. Task-number: QTBUG-2192 Change-Id: If6d798c6c928ebda75bc474e49a07fbbfbe5816c Reviewed-by: Mark Brand (cherry picked from qtbase/b8b79a0f37ec74fd5b4ad829e522a384ba3622ae) --- src/sql/drivers/odbc/qsql_odbc.cpp | 46 +++++++++++++++++++++++++++++++--- tests/auto/qsqlquery/tst_qsqlquery.cpp | 28 ++++++++++++++++++++- 2 files changed, 69 insertions(+), 5 deletions(-) diff --git a/src/sql/drivers/odbc/qsql_odbc.cpp b/src/sql/drivers/odbc/qsql_odbc.cpp index 44c5f0c..62ab818 100644 --- a/src/sql/drivers/odbc/qsql_odbc.cpp +++ b/src/sql/drivers/odbc/qsql_odbc.cpp @@ -54,6 +54,7 @@ #include #include #include +#include #include #include @@ -115,7 +116,7 @@ class QODBCDriverPrivate public: enum DefaultCase{Lower, Mixed, Upper, Sensitive}; QODBCDriverPrivate() - : hEnv(0), hDbc(0), unicode(false), useSchema(false), disconnectCount(0), isMySqlServer(false), + : hEnv(0), hDbc(0), unicode(false), useSchema(false), disconnectCount(0), datetime_precision(19), isMySqlServer(false), isMSSqlServer(false), isFreeTDSDriver(false), hasSQLFetchScroll(true), hasMultiResultSets(false), isQuoteInitialized(false), quote(QLatin1Char('"')) { @@ -127,6 +128,7 @@ public: bool unicode; bool useSchema; int disconnectCount; + int datetime_precision; bool isMySqlServer; bool isMSSqlServer; bool isFreeTDSDriver; @@ -139,6 +141,7 @@ public: void checkHasSQLFetchScroll(); void checkHasMultiResults(); void checkSchemaUsage(); + void checkDateTimePrecision(); bool setConnectionOptions(const QString& connOpts); void splitTableQualifier(const QString &qualifier, QString &catalog, QString &schema, QString &table); @@ -1401,14 +1404,25 @@ bool QODBCResult::exec() dt->hour = qdt.time().hour(); dt->minute = qdt.time().minute(); dt->second = qdt.time().second(); - dt->fraction = qdt.time().msec() * 1000000; + + int precision = d->driverPrivate->datetime_precision - 20; // (20 includes a separating period) + if (precision <= 0) { + dt->fraction = 0; + } else { + dt->fraction = qdt.time().msec() * 1000000; + + // (How many leading digits do we want to keep? With SQL Server 2005, this should be 3: 123000000) + int keep = (int)qPow(10.0, 9 - qMin(9, precision)); + dt->fraction /= keep * keep; + } + r = SQLBindParameter(d->hStmt, i + 1, qParamType[(QFlag)(bindValueType(i)) & QSql::InOut], SQL_C_TIMESTAMP, SQL_TIMESTAMP, - 19, - 0, + d->driverPrivate->datetime_precision, + precision, (void *) dt, 0, *ind == SQL_NULL_DATA ? ind : NULL); @@ -1895,6 +1909,7 @@ bool QODBCDriver::open(const QString & db, d->checkSqlServer(); d->checkHasSQLFetchScroll(); d->checkHasMultiResults(); + d->checkDateTimePrecision(); setOpen(true); setOpenError(false); if(d->isMSSqlServer) { @@ -2130,6 +2145,29 @@ void QODBCDriverPrivate::checkHasMultiResults() #endif } +void QODBCDriverPrivate::checkDateTimePrecision() +{ + SQLINTEGER columnSize; + SQLHANDLE hStmt; + + SQLRETURN r = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt); + if (r != SQL_SUCCESS) { + return; + } + + r = SQLGetTypeInfo(hStmt, SQL_TIMESTAMP); + if (r == SQL_SUCCESS || r == SQL_SUCCESS_WITH_INFO) { + r = SQLFetch(hStmt); + if ( r == SQL_SUCCESS || r == SQL_SUCCESS_WITH_INFO ) + { + if (SQLGetData(hStmt, 3, SQL_INTEGER, &columnSize, sizeof(columnSize), 0) == SQL_SUCCESS) { + datetime_precision = (int)columnSize; + } + } + } + SQLFreeHandle(SQL_HANDLE_STMT, hStmt); +} + QSqlResult *QODBCDriver::createResult() const { return new QODBCResult(this, d); diff --git a/tests/auto/qsqlquery/tst_qsqlquery.cpp b/tests/auto/qsqlquery/tst_qsqlquery.cpp index 5960795..6923904 100644 --- a/tests/auto/qsqlquery/tst_qsqlquery.cpp +++ b/tests/auto/qsqlquery/tst_qsqlquery.cpp @@ -217,6 +217,8 @@ private slots: void QTBUG_21884(); void QTBUG_16967_data() { generic_data("QSQLITE"); } void QTBUG_16967(); //clean close + void QTBUG_2192_data() { generic_data(); } + void QTBUG_2192(); void sqlite_constraint_data() { generic_data("QSQLITE"); } void sqlite_constraint(); @@ -338,7 +340,8 @@ void tst_QSqlQuery::dropTestTables( QSqlDatabase db ) << qTableName( "task_250026", __FILE__ ) << qTableName( "task_234422", __FILE__ ) << qTableName("test141895", __FILE__) - << qTableName("qtest_oraOCINumber", __FILE__); + << qTableName("qtest_oraOCINumber", __FILE__) + << qTableName( "bug2192", __FILE__); if ( db.driverName().startsWith("QPSQL") ) tablenames << qTableName("task_233829", __FILE__); @@ -3205,6 +3208,29 @@ void tst_QSqlQuery::QTBUG_16967() } } +void tst_QSqlQuery::QTBUG_2192() +{ + QFETCH( QString, dbName ); + QSqlDatabase db = QSqlDatabase::database( dbName ); + CHECK_DATABASE( db ); + { + const QString tableName(qTableName("bug2192", __FILE__)); + tst_Databases::safeDropTable( db, tableName ); + + QSqlQuery q(db); + QVERIFY_SQL(q, exec("CREATE TABLE " + tableName + " (dt DATETIME)")); + + QVERIFY_SQL(q, prepare("INSERT INTO " + tableName + " (dt) VALUES (?)")); + q.bindValue(0, QVariant(QDateTime(QDate(2012, 7, 4), QTime(23, 59, 59, 999)))); + QVERIFY_SQL(q, exec()); + + // Check if value was stored with at least second precision. + QVERIFY_SQL(q, exec("SELECT dt FROM " + tableName)); + QVERIFY_SQL(q, next()); + QVERIFY(q.value(0).toDateTime().msecsTo(QDateTime(QDate(2012, 7, 4), QTime(23, 59, 59, 999))) < 1000 ); + } +} + void tst_QSqlQuery::oraOCINumber() { QFETCH( QString, dbName ); -- cgit v0.12