nvarchar(max) type in SQL Server


How should I use nvarchar(max) or text field types in SQL Server? I have exorting ORM from Data Modeler to Aurelius and these types will be TBlob in ORM.

Trying to set String to that blob does not work:
It will generate error:
[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 11.0][SQL Server]Operand type clash: image is incompatible with nvarchar(max)

Field type  nvarchar(1000) in SQL Server is working directly (without asString).

Hannu Hilanne

Declare your property as string instead of TBlob and set column size (using column attribute) greater than 65535.

Yes; I was thinking also that, but the point is Data Modeler export to Aurelius (maybe this issue should be there). If I change manually exported property then I need to remember to change these after every export.

Just found the same issue here:
I suppose it's still waiting for fix.

Yes, but we have to investigate it more. This seems to be a SQL Server limitation and/or SQL Server + specifi component limitation. Ideally, TBlob should be used, and I believe it works for other database servers. It shouldn't matter if the field is TBlob or varchar(max), because the data itself is string. 

For example, what are you using to connect to database? dbExpress? Can you verify if it works using a different component (dbExpress, ADO, FireDac comes to mind as native Delphi components you can use to connect to SQL Server).

I use FireDac as I thought it would be best choice. Exception message is:

[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 11.0][SQL Server]Operand type clash: image is incompatible with nvarchar(max)

Looks like a SQL client error. Have you tried using OleDB instead of Native Client?

I tried dbGo, but it does not change the issue; Exception will come:
EOleException error raised, with message : Operand type clash: image is incompatible with nvarchar(max)

Just a note, for the record, that this has been fixed and improved in both Aurelius and Data Modeler. Aurelius now supports a [DBTypeMemo] attribute to indicate a TBLob property is text, not binary.  And Data Modeler exports that attribute accordingly when the database type is text blob (CLOB, TEXT, MEMO, etc.)

Since the last update, I get for fields which are defined as "text" in the Data Modeler in the database a varchar(max), right. 

Somehow there was converted to varbinary(max). I don't know when this happened. Maybe that was done by a SQLExpress Update, Anyway...

But a problem is in that the in Delphi the resulting TBlob shows only the first character in the DBMemo fields. I can only cure that by manually change them to a string (65535) to get the whole text displayed.

How does it come?

Hello, can you please provide more details, it's not clear to me what's happening. The field changed from varchar to varbinary in data modeler or database itself? You have already existing data there? How is the Aurelius mapped class generated by Data Modeler? What is the exact code you use to retrieve data from the class and convert it to a string?

I have seen almost same issue. More details

MSSQL 2012 table field is  
    [Data] nvarchar NULL
After Data Modeler Aurelius export ORM-mapping for that column is:
    [Column('Data', [TColumnProp.Lazy])]
    FData: TBlob;

And accessing Data.AsString gives text with additional space with every char so in thml it looks only having one character( Aurelius 2.7.1 there were no additional spaces) Sample:
< p > & n b s p ; < / p >  
 < p > < s p a n   s t y l e = " f o n t - s i z e :   m e d i u m ; " > < s t r o n g > E P L L   o t t a a   s y k s y l l & a u m l ;   k & a u m l ; y t t & o u m l ; & o u m l ; n   u u d e n   S a f i r - s i i r t o k u l j e t u s t e n   t i l a u s j & a u m l ; r j e s t e l m & a u m l ; n . < / s t r o n g > < / s p a n > < / p >  
 < p > < s p a n   s t y l e = " f o n t - s i z e :   s m a l l ; " > K i r j a u t u k a a   o i k e a a n   h o i t o p a i k k a a n   j o t t a   t i l a u s   v o i d a a n   m u o d o s t a a   a u t o m a a t t i s e s t i   o i k e a a n   n o u t o - o s o i t t e e s e e n . < / s p a n > < / p >  
 < p > & n b s p ; < / p >
I tried to change "undocumented" global setting TGlobalConfigs.GetInstance.MapStringToNationalChar but it didn't make any difference. 
But by acessing Data.AsUnicodeString gives correct result. It should be like that from now on?

Yes. Aurelius can't tell the encoding of the string stored in a binary blob. If the field is marked as memo, then it will always store the string in the TBlob object encoded using TEncoding.Unicode (the default for Delphi strings).

So TBlob.AsString returns a string considering blob data is Default encoded (which is Ansi or UTF8 depending on platform). It's actually kept there for backward compatibility only.
TBlob.AsUnicodeString returns a string considering blob data is Unicode encoded.