Reported by private email by Alessandro Russo <axela74@yahoo.it> In my company I use libreoffice for a small Base interface to a database on SQL Server Express. Two days ago I upgraded Libreoffice from an old 3.x version to the last version but I found a very strange error when I tried to update a datetime field in a table: HY104 Invalid precision or scale value The value specified for the argument /ColumnSize/ or /DecimalDigits/ was outside the range of values supported by the data source for a column of the SQL data type specified by the /ParameterType/ argument. After several test I found the the bug was introduced with the 4.x version (the 3.6.7.2 was ok). After some investigations I think that the bug was introduced with this commit: http://cgit.freedesktop.org/libreoffice/core/commit/connectivity/source/drivers/odbcbase/OPreparedStatement.cxx?h=libreoffice-4-0-2&id=2c54307713160295ad51244eee41ef959a7fd55a I did a debug with my odbc driver and I found that the old version (3.6.7.2) sent this calls to the odbs driver: Programma Contr 1f10-166c ENTER SQLPrepare HSTMT 0x0B08E668 UCHAR * 0x0B6028EC [ 81] "UPDATE "DistilleriaEtna"."dbo"."CaricoContrassegni" SET "data" = ? WHERE "ID" = ?" SDWORD 81 Programma Contr 1f10-166c EXIT SQLPrepare with return code 0 (SQL_SUCCESS) HSTMT 0x0B08E668 UCHAR * 0x0B6028EC [ 81] "UPDATE "DistilleriaEtna"."dbo"."CaricoContrassegni" SET "data" = ? WHERE "ID" = ?" SDWORD 81 Programma Contr 1f10-166c ENTER SQLNumParams HSTMT 0x0B08E668 SWORD * 0x0B8DB504 Programma Contr 1f10-166c EXIT SQLNumParams with return code 0 (SQL_SUCCESS) HSTMT 0x0B08E668 SWORD * 0x0B8DB504 (2) Programma Contr 1f10-166c ENTER SQLBindParameter HSTMT 0x0B08E668 UWORD 1 SWORD 1 <SQL_PARAM_INPUT> SWORD 93 <SQL_C_TYPE_TIMESTAMP> SWORD 93 <SQL_TYPE_TIMESTAMP> SQLULEN 19 SWORD 0 PTR 0x07C45560 SQLLEN 0 SQLLEN * 0x07C4FA48 Programma Contr 1f10-166c EXIT SQLBindParameter with return code 0 (SQL_SUCCESS) HSTMT 0x0B08E668 UWORD 1 SWORD 1 <SQL_PARAM_INPUT> SWORD 93 <SQL_C_TYPE_TIMESTAMP> SWORD 93 <SQL_TYPE_TIMESTAMP> SQLULEN 19 SWORD 0 PTR 0x07C45560 SQLLEN 0 SQLLEN * 0x07C4FA48 (16) The new version sent these calls: Programma Contr 1f94-2388 ENTER SQLPrepare HSTMT 0x09554948 UCHAR * 0x084DCF00 [ 81] "UPDATE "DistilleriaEtna"."dbo"."CaricoContrassegni" SET "data" = ? WHERE "ID" = ?" SDWORD 81 Programma Contr 1f94-2388 EXIT SQLPrepare with return code 0 (SQL_SUCCESS) HSTMT 0x09554948 UCHAR * 0x084DCF00 [ 81] "UPDATE "DistilleriaEtna"."dbo"."CaricoContrassegni" SET "data" = ? WHERE "ID" = ?" SDWORD 81 Programma Contr 1f94-2388 ENTER SQLNumParams HSTMT 0x09554948 SWORD * 0x0B32A9A8 Programma Contr 1f94-2388 EXIT SQLNumParams with return code 0 (SQL_SUCCESS) HSTMT 0x09554948 SWORD * 0x0B32A9A8 (2) Programma Contr 1f94-2388 ENTER SQLBindParameter HSTMT 0x09554948 UWORD 1 SWORD 1 <SQL_PARAM_INPUT> SWORD 93 <SQL_C_TYPE_TIMESTAMP> SWORD 93 <SQL_TYPE_TIMESTAMP> SQLULEN 16 SWORD -1 PTR 0x16661418 SQLLEN 16 SQLLEN * 0x0799DB70 Programma Contr 1f94-2388 EXIT SQLBindParameter with return code -1 (SQL_ERROR) HSTMT 0x09554948 UWORD 1 SWORD 1 <SQL_PARAM_INPUT> SWORD 93 <SQL_C_TYPE_TIMESTAMP> SWORD 93 <SQL_TYPE_TIMESTAMP> SQLULEN 16 SWORD -1 PTR 0x16661418 SQLLEN 16 SQLLEN * 0x0799DB70 DIAG [HY104] [Microsoft][ODBC SQL Server Driver]Valore di scala non valido. (0) As you can see the difference is here: Programma Contr 1f10-166c ENTER SQLBindParameter HSTMT 0x0B08E668 UWORD 1 SWORD 1 <SQL_PARAM_INPUT> SWORD 93 <SQL_C_TYPE_TIMESTAMP> SWORD 93 <SQL_TYPE_TIMESTAMP> SQLULEN 19 *16* SWORD 0 *-1* PTR 0x07C45560 SQLLEN 0 *16* SQLLEN * 0x07C4FA48 On this page from Microsoft https://msdn.microsoft.com/en-us/library/ms710963%28v=vs.85%29.aspx if found this sample code: SQL_DATE_STRUCT dsOrderDate; SQLINTEGER cbOrderDate = 0; retcode = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_TYPE_DATE, SQL_TIMESTAMP, sizeof(dsOrderDate), 0, &dsOrderDate, 0, +&cbOrderDate); I don't know which of the 3 different parameter cause the error. I think that the methods of OPreparedStatement that prepare the call are: setTimestamp that call setScalarParameter +<https://docs.libreoffice.org/connectivity/html/classconnectivity_1_1odbc_1_1OPreparedStatement.html#ac090c746a00808b097f3b2a0 +2d48f96b> that than call void OPreparedStatement::setParameter +<https://docs.libreoffice.org/connectivity/html/classconnectivity_1_1odbc_1_1OPreparedStatement.html#a065dc46064a6281fd8c9b5c4 +77285f21>(const sal_Int32 parameterIndex, const sal_Int32 _nType, const SQLULEN _nColumnSize, const sal_Int32 _nScale, const void* const _pData, const SQLULEN _nDataLen, const SQLLEN _nDataAllocLen); I can't compile libreoffice myself so I cannot check it But I can try any build you can give me and see if the error go away.
I don't have a Windows build either, but we can find another developer that has if need be. The ColumnSize change from 19 to 16 is correct according to https://msdn.microsoft.com/en-us/library/ms711786%28v=vs.85%29.aspx (entry for SQL_TYPE_TIMESTAMP) The DecimalDigits argument is indeed incorrect according to https://msdn.microsoft.com/en-us/library/ms709314(v=vs.85).aspx The BufferLength change from 0 to 16 is correct according to https://msdn.microsoft.com/en-us/library/ms713979%28v=vs.85%29.aspx
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=0df0ab1bc8530b5bc307e946f35e8f6be883c2d1 tdf#96461 ODBC SQLBindParameter: correct DecimalDigits for time(date) values It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "libreoffice-5-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=e95e07ef3b125688d4dce47f382533a1db50479f&h=libreoffice-5-1 tdf#96461 ODBC SQLBindParameter: correct DecimalDigits for time(date) values It will be available in 5.1.0.1. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "libreoffice-5-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=fabfd88b11bb6253e9631664d9adb47c119f9e7d&h=libreoffice-5-0 tdf#96461 ODBC SQLBindParameter: correct DecimalDigits for time(date) values It will be available in 5.0.5. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
I tested the last nightly and it worked! Thanks for the very fast bug fixing!