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.
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.
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).
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.
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?
After Data Modeler Aurelius export ORM-mapping for that column is:
[Column('Data', [TColumnProp.Lazy])]
[DBTypeMemo]
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 >
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.