parambyname filtro varchar - nvarchar

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
wilton_radinfo
Posts: 11
Joined: Mon 29 Apr 2013 18:59

parambyname filtro varchar - nvarchar

Post by wilton_radinfo » Fri 01 Jun 2018 12:46

when defining a parameter of type .asstring, the tmsquery component creates a parameter of type nvarchar (4000), this generates performance problems in the sql server that needs to do the conversion from nvarchar to varchar,
there is some mechanism for it to use the parameter of the correct type, without having to use the autoprepare option, because this option triggers another command in the database to identify the type of the field. I only use varchar fields in my bank, if this was a global configuration it would help me a lot.

sdac 8.0.5
delphi tokyo 10.2.3

Stellar
Devart Team
Posts: 149
Joined: Tue 03 Oct 2017 11:00

Re: parambyname filtro varchar - nvarchar

Post by Stellar » Mon 04 Jun 2018 09:27

When setting a parameter value using the AsString method, the parameter type is set automatically. For older versions of Delphi, the type is set as ftWideString, and for younger ones, it is set as ftString. When the Prepare method is executed, the request parameters description is requested from the server, and an additional request is executed to the server.
If you do not want an additional request to be executed to the server, and the default parameter type is not suit, then you can specify the required parameter type manually, for example:

MSQuery1.ParamByName('Param_Name').DataType := ftString;
MSQuery1.ParamByName('Param_Name').AsString := 'Value';

wilton_radinfo
Posts: 11
Joined: Mon 29 Apr 2013 18:59

Re: parambyname filtro varchar - nvarchar

Post by wilton_radinfo » Wed 06 Jun 2018 12:51

this will give me a huge job, I have over 5000 string parameter references on the system, I will try to find this in the sdac sources, and change this internally in the component. if it were possible to change this internal pattern, it always takes string parameters like varchar, it would be very good, it would save me days of work.

I do not want to use autoprepare because this will have a very big cost for my server.

Stellar
Devart Team
Posts: 149
Joined: Tue 03 Oct 2017 11:00

Re: parambyname filtro varchar - nvarchar

Post by Stellar » Thu 07 Jun 2018 08:22

In order for all string parameters set using the AsString method to have the ftString type, you can set the global variable ParamStringAsAnsiString to True when initializing your application, for example:

ParamStringAsAnsiString := True;

Note that the ParamStringAsAnsiString variable is located in unit DBAccess.

wilton_radinfo
Posts: 11
Joined: Mon 29 Apr 2013 18:59

Re: parambyname filtro varchar - nvarchar

Post by wilton_radinfo » Thu 07 Jun 2018 16:51

perfect

Stellar
Devart Team
Posts: 149
Joined: Tue 03 Oct 2017 11:00

Re: parambyname filtro varchar - nvarchar

Post by Stellar » Fri 08 Jun 2018 11:29

It is good to see that the problem has been solved.
Feel free to contact us if you have any further questions about our products.

Post Reply