Aurelius using NVARCHAR for BIT ?

Column is defined in entity as Boolean.


TXDataWebDataSet sees it as boolean. 

When inserting or updating a record an error is generated saying...

{
    "error": {
        "code": "AureliusOdbcException",
        "message": "Error -1: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Conversion failed when converting the nvarchar value 'T' to data type bit."
    }
}

SQL Trace shows the following...
declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@P1 nvarchar(24),@P2 nvarchar(5),@P3 nvarchar(1),@P4 nvarchar(1),@P5 nvarchar(1),@P6 nvarchar(1),@P7 int,@P8 nvarchar(3),@P9 int,@P10 nvarchar(5)',N'SET NOCOUNT ON; 
DECLARE @AureliusOutputTbl TABLE (ID INT); 
INSERT INTO gigDocs (
  docFile, docTitle, isPolicyDoc, isEndDoc, isQuote, isInvoice, policyTypeID, stateSpecific, insCompID, ifInclude)
OUTPUT INSERTED.docID INTO @AureliusOutputTbl
 VALUES (
  @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10);
SET NOCOUNT OFF;
SELECT ID From @AureliusOutputTbl;',N'Test_Dcoument.pdf',N'Test6',N'T',N'F',N'F',N'F',1,N'ALL',1,N'CYBER'
select @p1

@P3 - @P6 should all be "BIT" not nvarchar(1).

Entity column type is set to BOOLEAN...
[Column('isPolicyDoc', [])]
    FisPolicyDoc: Nullable<Boolean>; //example

Why is Aurelius trying to use nvarchar instead of bit? Any suggestions to correct?

Note: This is through an XDataServer via TXdataWebDataset, posted here since I wasn't sure where this should go and appears to be Aurelis related.

It's an old issue with MSSQL support: it's been tailored for ooooold version of the server and, apparently, never updated.


The solution (which I got from here) was to write a descendant to the TMSSQLSQLGenerator class that re-implements it as an int (it's still not a bit, but it's way better than a NVARCHAR[1] (which is an aberation no matter how you read it).

Here is a unit that does this. Tu use it, include it in your project and, when calling the IDBConnection factory, pass 'MSSQL2' as SQL dialect (e.g. DBConnection := TDbGoConnectionAdapter.Create(Connection, 'MSSQL2', OwnedConnection);)

unit Aurelius.SQL.MSSQL2;

interface
uses
  DB,
  Aurelius.SQL.Interfaces,
  Aurelius.SQL.MSSQL;

type
  TMSSQL2Generator = class(TMSSQLSQLGenerator)
  protected
    function GetEquivalentFieldTypes: TFieldTypeEquivArray; override;
    function ConvertValue(Value: Variant; FromType, ToType: TFieldType): Variant; override;
    function GetSqlDialect: string; override;
  end;

implementation
uses
  Aurelius.SQL.Register;

{ TMSSQL2Generator }

function TMSSQL2Generator.ConvertValue(Value: Variant; FromType,
  ToType: TFieldType): Variant;
begin
  if (FromType = ftBoolean) and (ToType in [ftShortInt, ftByte]) then
  begin
    if Value = True then
      Result := '1' // 1 = True
    else
      Result := '0'; // 0 = False
  end
  else
  if (FromType in [ftShortInt, ftByte]) and (ToType = ftBoolean) then
  begin
    Result := Value <> 0;
  end
  else
    Result := inherited ConvertValue(Value, FromType, ToType);
end;

function TMSSQL2Generator.GetEquivalentFieldTypes: TFieldTypeEquivArray;
begin
  SetLength(Result, 1);

  Result[0].NotSupportedType := ftBoolean;
  Result[0].EquivalentType := ftShortInt;
end;

function TMSSQL2Generator.GetSqlDialect: string;
begin
  Result := 'MSSQL2';
end;

initialization
  TSQLGeneratorRegister.GetInstance.RegisterGenerator(TMSSQL2Generator.Create);

end.

Well I think I found the solution. Not ssure if this is the proper place to put this in, but in the DispatcherStart event I used...


(TSQLGeneratorRegister.GetInstance.GetGenerator('MSSQL') as TMSSQLSQLGenerator).UseBoolean := True;

..which actually changed it to BIT. After discovering the opttion UseBoolean in the source, I found the following.

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

...which led me to the solution and resolved it for me this morning.

That's the correct solution. You can put that code anywhere as long it's executed before any code that need such information (like inserting record in such table or updating the database structure)