How to pass unicode literal in parameter?

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
brace
Posts: 225
Joined: Wed 14 Feb 2007 08:26

How to pass unicode literal in parameter?

Post by brace » Thu 18 Jul 2019 10:24

How to tell SDAC to use a unicode literal?

I would like that when i do

Code: Select all

  MSQuery1.SQL.Text := 'UPDATE MYTABLE SET FIELD= :NEWVALUE';
  MSQuery1.ParamByName('NEWVALUE').AsString := '゚ワノラルユナ';
  MSQuery1.Execute;
SDAC passes N'゚ワノラルユナ' instead of 'ワノラルユナ', how is doable?

WIthout this nvarchar strings are casted to varchar.

Thanks.

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

Re: How to pass unicode literal in parameter?

Post by Stellar » Thu 18 Jul 2019 14:25

When you set the value of the string parameter using the AsString property, SDAC will set the data type for the parameter to ftWideString by default in Delphi 2009 or newer. In Delphi versions older than 2009, the data type for the string parameter will be set to ftString. To solve the issue, you can explicitly set the parameter type to ftString\ftWideString, or set the parameter value using the AsAnsiString\AsWideString property.

brace
Posts: 225
Joined: Wed 14 Feb 2007 08:26

Re: How to pass unicode literal in parameter?

Post by brace » Thu 18 Jul 2019 14:58

By using asstring i got unicode chars inserted as "?????" in SQL Server, i had to do a workaround avoiding using parameters bu by creating the update statement like this:

MSQuery1.SQL.Text := 'UPDATE MYTABLE SET FIELD = N''' + unicodeText+ '''';

I use DElphi 10, so it seems what you are telling me is not right.

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

Re: How to pass unicode literal in parameter?

Post by Stellar » Fri 19 Jul 2019 07:28

Please check the data type of the "NEWVALUE" parameter after assigning a value to it. For example:

Code: Select all

var
  FieldType: TFieldType;
begin
  MSQuery1.SQL.Text := 'UPDATE MYTABLE SET FIELD = :NEWVALUE';
  MSQuery1.ParamByName('NEWVALUE').AsString := '゚ワノラルユナ';
  FieldType :=  MSQuery1.ParamByName('NEWVALUE').DataType; //ftWideString or ftString
  MSQuery1.Execute;
end;

brace
Posts: 225
Joined: Wed 14 Feb 2007 08:26

Re: How to pass unicode literal in parameter?

Post by brace » Fri 19 Jul 2019 07:34

It is ftString, since the query SQL is created at runtime as in the code snippet, how can i force the parameter Datatype to Thanks.

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

Re: How to pass unicode literal in parameter?

Post by Stellar » Fri 19 Jul 2019 08:44

Please try to explicitly set the data type for the parameter after assinging a value to it, e.g.:

Code: Select all

MSQuery1.SQL.Text := 'UPDATE MYTABLE SET FIELD = :NEWVALUE';
MSQuery1.ParamByName('NEWVALUE').AsString := '゚ワノラルユナ';
MSQuery1.ParamByName('NEWVALUE').DataType := ftWideString;
MSQuery1.Execute;
Additionally, check the value of the global variable ParamStringAsAnsiString, which defaults to False. If you set it to True, all String parameters that are set using the AsString method will be of type ftString.
To execute the statement, please try to set it to False, e.g.:

Code: Select all

ParamStringAsAnsiString := False;
Note that the ParamStringAsAnsiString variable is located in unit DBAccess.

brace
Posts: 225
Joined: Wed 14 Feb 2007 08:26

Re: How to pass unicode literal in parameter?

Post by brace » Fri 19 Jul 2019 09:02

Thanks a lot.

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

Re: How to pass unicode literal in parameter?

Post by Stellar » Mon 22 Jul 2019 08:26

Glad to see that the issue was resolved.
Feel free to contact us if you have any further questions about our products.

Post Reply