I'm using Aurelius 2.2 and are getting errors while trying to save data to a SQL Server 2008 R2.
I can read the data just fine, but are getting errors while modifying or adding objects.
Two datatypes are causing problems.
1. Boolean - when trying to save I get an error saying 'Conversion failed when converting the varchar value 'T' to data type bit'. Apparently, Aurelius is converting Boolean value 'True' to varchar 'T' instead of bit 1.
2. Blob - The SQL Server datatype nvarchar(max) was converted to TBlob by TMS Data Modeler, and I can read the string fine using the TBlob's AsString property. But when trying to modify the value I get an error saying 'Operand type clash: image is incompatible with nvarchar(max)'
1. By looking in the Aurelius source code, I see that there was some commented out code regarding ftBoolean type in the Aurelius.Sql.AnsiSQLGenerator unit. So I added the following code to the Aurelius.Sql.MSSQL unit:
In DefineColumnType():
...
ftBoolean:
Column.DataType := 'BIT'; // not supported by all databases
...
In GetSupportedFieldTypes():
...
Result := inherited GetSupportedFieldTypes + [ftGuid] + [ftBoolean];
...
And reading and writing Boolean values to BIT fields seems to be working fine.
2. In TMS Data Modeler, instead of using the actual SQL Server type nvarchar(max) I change it to varchar(max). This results in the generated code using type string for this property, which works find for reading and writing.
Is anyone from TMS reading this? Do you have a better fix for this issue?
The two reported issues are that by design. To keep compatibility and easy switch with other databases, boolean properties are saved to string[1] fields even in SQL Server. You can workaround it with a similar approach as you did. But instead changing Aurelius source code directly, you can create a class descending from TMSSQLSQLGenerator and override the mentioned methods adding the code you needed. Then you register this new class under a new sql dialect name and use that dialect in your Aurelius connections.