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
parambyname filtro varchar - nvarchar
Re: parambyname filtro varchar - nvarchar
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';
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';
-
- Posts: 11
- Joined: Mon 29 Apr 2013 18:59
Re: parambyname filtro varchar - nvarchar
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.
I do not want to use autoprepare because this will have a very big cost for my server.
Re: parambyname filtro varchar - nvarchar
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.
ParamStringAsAnsiString := True;
Note that the ParamStringAsAnsiString variable is located in unit DBAccess.
-
- Posts: 11
- Joined: Mon 29 Apr 2013 18:59
Re: parambyname filtro varchar - nvarchar
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.
Feel free to contact us if you have any further questions about our products.