MsSql 2008 nvarchar(max) problem

[spin]

MsSql 2008 nvarchar(max) problem :
I am asking for help with the following problem:
Zeos version: 7.2.14-release
Lazarus version: 2.2.4 FPC: 3.2.2
Operating system: win10

ZConnection properties:
AutoEncodeStrings=ON
codepage=CP_UTF8
controls_cp=CP_UTF16

ZConnection protocol: ado

After connecting, I can see nvarchar(800), but I can't see the nvarchar(max) data.:
names(nvarchar(max),nul)

ZQuery1.FieldByName('names').AsString;
ZQuery1.FieldByName('names').AsWideString;
ZQuery1.FieldByName('names').AsUTF8String;
ZQuery1.FieldByName('names').AsUnicodeString

I try any of them, my variable is empty!

It is interesting that:
i:=length(ZQuery1.FieldByName('names').AsUnicodeString);
'i' always returns a good value.

I can modify it: 'UPDATE tbl_data set names = '''+ 'New name: ÁáÖöÜüÓóÉéŐőÚú'+',' +''' where id = '''+tbl_id+'''';

This works fine, I can see the modified data correctly in SQL Server Management Studio.

[marsupilami]

Could you please test what happens if you try the current Zeos 8.0? Could you also maybe try to provide a small test application (SQL Scripts + delphi applilcation) that generate the problem?

[aehimself]

If even special characters can be read back correctly after updating I'm wondering if it's an initial encoding issue.
What is the result of Field.AsBytes (I hope FPC has it...?)

[spin]

Thank you for your quick response.
In response to the questions: I tried in CodeTyphon Studio, there is Zeos 8.0, but it was not good here either.
This was an old MySql database, it was converted to MsSql at the request of the administrator. (I used MSSQLConnection). Now you have to return to MySql, that's why I thought of ZConnection. The administrator made a copy of the MsSql database on my machine. And with this zeos, the nvarchar(max) value no longer appeared. I have since realized that I can only access the copy and not the original. The IP address points to the original and the copy still comes in. With MSSQLConnection I can achieve both.
Unfortunately, I can only deal with this problem a little, because my job at work is to repair machines (CNC Robot, etc.) and I don't program.

[marsupilami]

Hello Spin,

what connection string do you use for ADO? And which Version of MS SQL Server do you use?

We discourage the use of CodeTyphonn because they usually don't use current versions of Zeos. What happens if you use Lazarus and then use the current Zeos 8.0 from SVN? With Zeos 8.0 you could even switch to the OLEDB protocol which should be faster than ADO.

With best regards,

Jan

[spin]

Hello Jan,

I'll try it tomorrow, I hope I'll have time for it, I won't get another job.
I hope this is the latest: zeoslib-code-0-r7974-branches-8.0-patches.zip

With best regards,
Spin

[spin]

Hello Jan,

MsSql server: Microsoft SQL Server Express Edition with Advanced Services
version: 10.50.4000.0

It gave the following error with the OLEDB protocol: Error Message: SQL Error: OLEDB Error
SQLState: IM002 Native error: 0
Error message: [Microsoft][ODBC Driver Manager] Data source name not found and no base driver specified.
Source: Microsoft OLE DB Provider for ODBC Drivers
Code: 0 Message: IID_IDBInitialize.Initialize

This connection string may be the problem. I'll look into that connection string tomorrow.


With best regards,

Spin

[spin]

Hello Jan,

Everything works fine.
This is a good connection string:
Provider=MSOLEDBSQL;DataTypeCompatibility=80;Server=myServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;

Both OLEDB and ADO work fine.
Thank you all for your help!

With best regards,

Spin

[spin]

Now I have another problem using OLEDB and I am asking for help.
If there is no "öüóőúáűé" character in the first query, it works fine, in the next query there can already be "öüóőúáűé" characters. After restarting the application, if there is "öüóőúáűé" character in the first query, it gives an error message, there is a problem with character typing, but I don't know where and why. I am attaching the SQLMonitor file. ControlsCodePage and ClientCodepage Utf8 is Form Charset Unicode.

Zeos: 8.0.0 – Beta

Lazarus: 2.2.5
FPC: 3.2.2
Win32/64

With best regards,

Spin

[marsupilami]

Hello Spin,

we really need a sample application that demonstrates the problem. Could you please try to crete a small sample application and a database create script that demonstrates the problem?

With best regards,

Jan

[spin]

Hello Jan,

I have attached the requested files.

With best regards,
Spin

[marsupilami]

Hello Spin,

a patch has been added to fix this problem. Please try the latest revisions from SVN.

With best regards,

Jan

[spin]

Hello Jan,

It works fine now. Thanks for the help.

With best regards,

Spin