EAureliusOdbcException

Given this Code:

var
  Criteria: TCriteria<TTBLXLSImport>;
begin
  Criteria := mgrManager.Find<TTBLXLSImport>;
  Criteria.Add((Linq['LebensmittelCD'] = '') or Linq.IsNull('LebensmittelCD') );
  Data:=Criteria.List;

results in 

Error -1: [Microsoft][ODBC SQL Server Driver]Ung?ltiger Genauigkeitswert.

If I Change the blank
  Criteria.Add((Linq['LebensmittelCD'] = '5') or Linq.IsNull('LebensmittelCD') );

no error occurs.


The def of the column:
    [Column('LebensmittelCD', [], 250)]
    FLebensmittelCD: Nullable<string>;

The SQL Statement is ok, so Maybe there is a problem with Setting the params?

Additionally: In both cases the list is empty.

Hello Bernd,

Unfortunately I could not reproduce the problem. What component are you using to access the database?
Are you able to reproduce the issue in a smaller database and in a smaller Delphi project?
DriverName=MSSQL

SQLSRV32.DLL Driver Version 10.00.18362.01

This is part of the SQL.LOG Trace Info:
...
RezeptSchnittst c44-254    EXIT  SQLDriversW  with return code 0 (SQL_SUCCESS)
        HENV                0x048CD5A0
        UWORD                        1 <SQL_FETCH_NEXT>
        WCHAR *             0x03A4D89C [      10] "SQL Server"
        SWORD                      255
        SWORD *             0x0014FBDE (10)
        WCHAR *             0x0711276C [     101] "APILevel=2\ 0ConnectFunctions=YYY\ 0CPTimeout=60\ 0DriverODBCVer=03.50\ 0FileUsage=0\ 0SQLLevel=1\ 0UsageCount=1\ 0"
        SWORD                     1000
        SWORD *             0x0014FBDC (101)

RezeptSchnittst c44-254    ENTER SQLDriversW
        HENV                0x048CD5A0
        UWORD                        1 <SQL_FETCH_NEXT>
        WCHAR *             0x03A4D89C
        SWORD                      255
        SWORD *             0x0014FBDE
        WCHAR *             0x0711276C
        SWORD                     1000
        SWORD *             0x0014FBDC

RezeptSchnittst c44-254    EXIT  SQLDriversW  with return code 100 (SQL_NO_DATA_FOUND)
        HENV                0x048CD5A0
        UWORD                        1 <SQL_FETCH_NEXT>
        WCHAR *             0x03A4D89C
        SWORD                      255
        SWORD *             0x0014FBDE
        WCHAR *             0x0711276C
        SWORD                     1000
        SWORD *             0x0014FBDC

RezeptSchnittst c44-254    ENTER SQLDriverConnectW
        HDBC                0x048CAF90
        HWND                0x00000000
        WCHAR *             0x571E2440 [      -3] "******\ 0"
        SWORD                       -3
        WCHAR *             0x571E2440
        SWORD                       -3
        SWORD *             0x00000000
        UWORD                        0 <SQL_DRIVER_NOPROMPT>

RezeptSchnittst c44-254    EXIT  SQLDriverConnectW  with return code 1 (SQL_SUCCESS_WITH_INFO)
        HDBC                0x048CAF90
        HWND                0x00000000
        WCHAR *             0x571E2440 [      -3] "******\ 0"
        SWORD                       -3
        WCHAR *             0x571E2440 <Invalid buffer length!> [-3]
        SWORD                       -3
        SWORD *             0x00000000
        UWORD                        0 <SQL_DRIVER_NOPROMPT>

        DIAG [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'nuts'. (5701)

        DIAG [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english. (5703)

        DIAG [01S00] [Microsoft][ODBC SQL Server Driver]Ungültiges Attribut für die Verbindungszeichenfolge (0)

RezeptSchnittst c44-254    ENTER SQLAllocHandle
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x048CAF90
        SQLHANDLE *         0x06D430A8

RezeptSchnittst c44-254    EXIT  SQLAllocHandle  with return code 0 (SQL_SUCCESS)
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x048CAF90
        SQLHANDLE *         0x06D430A8 ( 0x048D7B80)

RezeptSchnittst c44-254    ENTER SQLPrepareW
        HSTMT               0x048D7B80
        WCHAR *             0x03ABFBBC [     142] "SELECT t.name as TABLE_NAME, SCHEMA_NAME(t.schema_id) as TABLE_SCHEMA FROM sys.tables t WHERE t.is_ms_shipped = 0 ORDER BY t.schema_id, t.name"
        SDWORD                   142

...
This is where the error occurs:
RezeptSchnittst c44-254    ENTER SQLPrepareW
        HSTMT               0x049004D0
        WCHAR *             0x070AB46C [     308] "SELECT A.ID AS A_ID, A.IDExtern AS A_IDExtern, A.BezeichnungExtern AS A_BezeichnungExtern, A.HerkunftCD AS A_HerkunftCD, A.LebensmittelCD AS A_LebensmittelCD, A.Bezeichnung AS A_Bezeichnung\ d\ aFROM TBLXLSImport A\ d\ aWHERE  A.BezeichnungExtern LIKE ? AND \ d\ a(A.LebensmittelCD Is Not Null And A.LebensmittelCD <> ?)"
        SDWORD                   308

RezeptSchnittst c44-254    EXIT  SQLPrepareW  with return code 0 (SQL_SUCCESS)
        HSTMT               0x049004D0
        WCHAR *             0x070AB46C [     308] "SELECT A.ID AS A_ID, A.IDExtern AS A_IDExtern, A.BezeichnungExtern AS A_BezeichnungExtern, A.HerkunftCD AS A_HerkunftCD, A.LebensmittelCD AS A_LebensmittelCD, A.Bezeichnung AS A_Bezeichnung\ d\ aFROM TBLXLSImport A\ d\ aWHERE  A.BezeichnungExtern LIKE ? AND \ d\ a(A.LebensmittelCD Is Not Null And A.LebensmittelCD <> ?)"
        SDWORD                   308

RezeptSchnittst c44-254    ENTER SQLBindParameter
        HSTMT               0x049004D0
        UWORD                        1
        SWORD                        1 <SQL_PARAM_INPUT>
        SWORD                       -8 <SQL_C_WCHAR>
        SWORD                       -9 <SQL_WVARCHAR>
        SQLULEN                    2
        SWORD                        0
        PTR                0x03348E10
        SQLLEN                     0
        SQLLEN *            0x072D3040

RezeptSchnittst c44-254    EXIT  SQLBindParameter  with return code 0 (SQL_SUCCESS)
        HSTMT               0x049004D0
        UWORD                        1
        SWORD                        1 <SQL_PARAM_INPUT>
        SWORD                       -8 <SQL_C_WCHAR>
        SWORD                       -9 <SQL_WVARCHAR>
        SQLULEN                    2
        SWORD                        0
        PTR                0x03348E10
        SQLLEN                     0
        SQLLEN *            0x072D3040 (4)

RezeptSchnittst c44-254    ENTER SQLBindParameter
        HSTMT               0x049004D0
        UWORD                        2
        SWORD                        1 <SQL_PARAM_INPUT>
        SWORD                       -8 <SQL_C_WCHAR>
        SWORD                       -9 <SQL_WVARCHAR>
        SQLULEN                    0
        SWORD                        0
        PTR                0x00000000
        SQLLEN                     0
        SQLLEN *            0x072D2ED8

RezeptSchnittst c44-254    EXIT  SQLBindParameter  with return code -1 (SQL_ERROR)
        HSTMT               0x049004D0
        UWORD                        2
        SWORD                        1 <SQL_PARAM_INPUT>
        SWORD                       -8 <SQL_C_WCHAR>
        SWORD                       -9 <SQL_WVARCHAR>
        SQLULEN                    0
        SWORD                        0
        PTR                0x00000000
        SQLLEN                     0
        SQLLEN *            0x072D2ED8

        DIAG [HY104] [Microsoft][ODBC SQL Server Driver]Ungültiger Genauigkeitswert (0)

RezeptSchnittst c44-254    ENTER SQLGetDiagRecW
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x049004D0
        SQLSMALLINT                  1
        SQLWCHAR *          0x06C886C0
        SQLINTEGER *        0x0014F96C
        SQLWCHAR *          0x0014F568
        SQLSMALLINT                512
        SQLSMALLINT *       0x0014F96A

RezeptSchnittst c44-254    EXIT  SQLGetDiagRecW  with return code 0 (SQL_SUCCESS)
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x049004D0
        SQLSMALLINT                  1
        SQLWCHAR *          0x06C886C0 [       5] "HY104"
        SQLINTEGER *        0x0014F96C (0)
        SQLWCHAR *          0x0014F568 [      62] "[Microsoft][ODBC SQL Server Driver]Ung\ffltiger Genauigkeitswert"
        SQLSMALLINT                512
        SQLSMALLINT *       0x0014F96A (62)

RezeptSchnittst c44-254    ENTER SQLFreeHandle
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x049004D0

RezeptSchnittst c44-254    EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)
        SQLSMALLINT                  3 <SQL_HANDLE_STMT>
        SQLHANDLE           0x049004D0

RezeptSchnittst c44-254    ENTER SQLDisconnect
        HDBC                0x048CAF90

RezeptSchnittst c44-254    EXIT  SQLDisconnect  with return code 0 (SQL_SUCCESS)
        HDBC                0x048CAF90

RezeptSchnittst c44-254    ENTER SQLFreeHandle
        SQLSMALLINT                  2 <SQL_HANDLE_DBC>
        SQLHANDLE           0x048CAF90

RezeptSchnittst c44-254    EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)
        SQLSMALLINT                  2 <SQL_HANDLE_DBC>
        SQLHANDLE           0x048CAF90

RezeptSchnittst c44-254    ENTER SQLFreeHandle
        SQLSMALLINT                  1 <SQL_HANDLE_ENV>
        SQLHANDLE           0x048CD5A0

RezeptSchnittst c44-254    EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)
        SQLSMALLINT                  1 <SQL_HANDLE_ENV>
        SQLHANDLE           0x048CD5A0

I then installed the MSSQL 2012 Native Driver and the problem did not occur. So it seems as if the ODBC driver has a problem with empty parameters. I recall having theis problem already when I inserted data in the DB.

So the solution is: Do not use the default ODBC driver, but install sqlncli.msi.

It's not clear, are you using the native driver connection in TAureliusConnection, or are you adapting a 3rd party component, like FireDac?

Native driver.

Ok, thank you. Indeed you were using a very old ODBC driver. You don't necessarily need to use native driver, you can still use ODBC, but you need a more recent version.

I see. As this is a fresh Win10 machine - do you have a version number of a more recent ODBC driver?

There are versions 11, 13, 17: https://docs.microsoft.com/en-us/sql/connect/odbc/windows/microsoft-odbc-driver-for-sql-server-on-windows?view=sql-server-ver15 which are newer than that one.