MSSQL server: Invalid type used for "Bit"

BIZ TMS Aurelius
1

I have a MSSQL database with a table containing a "f_content_present" field of type "bit" (not null).


I have created a XData server application and auto-genrated the entities from the IDE after selecting the "driver" mode for the Aurelius connection object.

I'm trying to use a Linq statement to count the number of records that have the field set to 1 (true) from a service and where the 'f_status' field (integer) is less than 2. My code is the following:

  CriteriaResult := TXDataOperationContext.Current.GetManager.Find<Tt_files>
    .Select(TProjections.Count('f_ID').As_('Count'))
    .Where(Linq.LessThan('f_status', 2) and Linq.Eq('f_content_present', true))
    .UniqueValue;

The code above compiles but fails with an exception:

EAureliusOdbcException with message 'Error -1: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Conversion failed when converting the nvarchar value 'T' to data type bit.'

When debugging the query, I see that Aurelius generates the following SQL code (correct):

SELECT count(A.f_ID) As f0_
FROM t_files A
WHERE  (A.f_status < :p_0 And A.f_content_present = :p_1)

When examining the call parameters, however, the type of the 'p_1' is of type "ftFixedChar" with value 'T' (which is obviously wrong).

On a more general note, this seems to be a quite constant issue with Aurelius and MSSQL server: it seems to have been written with the assumption that the boolean type is, in fact, a string. This, in turns, causes all manners of trouble. I would really like this long-standing issue to be fixed because it makes working with existing database a real pain.

Thanks
2

BTW, if anyone finds this thread looking for a workaround for this issue, you can replace the use of "Linq.Eq('field', true)" by the equivalent 'linq.SQL('Field=1')


e.g:
  CriteriaResult := TXDataOperationContext.Current.GetManager.Find<Tt_files>
    .Select(TProjections.Count('f_ID').As_('Count'))
    .Where(Linq.LessThan('f_status', 2) and Linq.Sql('[f_content_present] = 1'))
    .UniqueValue;
3

Hello Stephane,

Changing that behavior from using numeric 1/0 instead of char T/F in SQL Server has always been possible, and since version 3.5 (Jan-2017) it's documented as a simple option. Check "UseBoolean" in the following documentation topic:

http://www.tmssoftware.biz/business/aurelius/doc/web/configuring_sql_dialects.html

4

Thank you Wagner, it works.


May I suggest that this be set by default for the MSSQL dialect? It makes no sense at all to use strings to store bit-type variables in MSSQL (performance, clarity and danger).


5

Unfortunately, we cannot break existing code. Benefits are minimum, considering that just that line of code is enough to change the behavior.