如何使用TADOCommand参数化查询参数化widestrings ?

i am trying to use a parameterized query with Delphi TADOCommand:

我正在尝试使用Delphi TADOCommand的参数化查询:

var 
   s: WideString;
   cmd: TADOCommand;  
   recordsAffected: OleVariant;
begin
   cmd := TADOCommand.Create(nil);
   cmd.Connection := Connection;
   cmd.CommandText := 'INSERT INTO Sqm(Filename) VALUES(:filename)';

   s := AFilename;
   cmd.Parameters.ParamByName('filename').Value := s;
   cmd.Execute();

The resulting data in the database is complete mangled:

数据库中的结果数据被完全破坏:

C?:\U?s?er?s?\i??n?.A?V`A?T?O?P?I?A?\A?p?p?D??t??\L?o???l?\A?v?at??r? S?o?f?t?w?är¨? C?r??t?i??n?s?\S?o°f?t?w?r?? Q?u??li?t?y? M??t?r?i?cs?\C??S?-s?q?m?00.x?m?l

C:\ U s ?呃? s ? \我? ? n ?。? V ? T ? O P ? ? ? ? \ ? P P ? D ? ? ? ? \ L O ? ? ?我? \ ? V ? ? ? r ?年代? o f ? t w ? ar¨吗?t r C ? ? ? ?我? ? n ? s ? \ s ? o°f ? t w ? r ? ?李问?你? ? ? t ? y ?r t M ? ? ? ?我? cs \ C ? ? S ? - S ?问? M ? 00. x ? M ? l

i can use a native parameterized ADO Command object. It saves the data correctly:

我可以使用本地参数化ADO命令对象。它正确地保存数据:

C̬:\Ȗŝḙr͇s̶\i̜ẵn̥.ÀV̹AͧT̶O̠P̩I̿Ȁ\A͜p̥p̔D͑ẫt̒ā\L̫o͋ɕălͭ\A̼v̼ẵt͈ấr̄ S̫o̖f͎t̻w̵ạr͂ẽ C̾r̮ḛẵt͘iͩo̳n̬s̨\S̪ōf̒t͘w̚âr̿ɇ Qͬüẳlͮi̫tͥy̽ M͘ȇt̨r̟i̻çš\C͍MͥS̚-s̞q̕m͜00.xͤm̧l̝

C̬:\Ȗŝḙr̶͇\我̜ẵn̥。AV̹ͧT̶O̠我̩̿Ȁ\͜P̥P̔D͑ẫt̒āO \ L̫͋ɕălͭ\̼v̼ẵt͈ấr̄年代̫O̖f͎T̻w̵ạr͂ẽC̾r̮ḛẵt͘我ͩO̳n̨̬\年代̪ōf̒T͘w̚ar̿ɇ问ͬuẳlͮ我̫Tͥy̽M͘ȇt̨r̟我̻cš\ C͍MͥS̚Q - S̞̕米͜00. xͤM̧L̝

but it's very fragile and not suitable for production use.

但它非常脆弱，不适合生产使用。

How can i use unicode/WideStrings with TADOCommand in Delphi?

在Delphi中，我如何使用unicode/ widestring和TADOCommand ?

Bonus Chatter

奖金喋喋不休

In SQL Server Profiler you can see the SQL being executed:

在SQL Server Profiler中，您可以看到正在执行的SQL:

exec sp_executesql N'INSERT INTO Sqm(Filename) VALUES(@P1)', N'@P1 char(300),@P2 text', 'C?:\Us?er?s?\i?än?.A?V?A?T?O?P?I?À\A?p?p?D?ât?a\L?o?çal¯\A?v?at??r? So?f?t?w?ar?? C?r??á?i?o?n?s?\So¸f"t?w?ar?? Q?u??l?i?ty? M??t?r?i¸?s`\C?M°S?-s?q?m?00.?m¨´l¯ '

exec sp_executesql N 'INSERT成平方米(文件名)值(@P1)' N ' @P1 char(300),@P2文本”、“C:\我们?呃? s ? \我?一?。? V ? ? T ? O P ? ?我?一个? \ P ? P ? D ? ? \ L ? O ?卡尔¯\ ? V ? ? ? r ?所以f ? t w ?基于“增大化现实”技术? ?r C ? ? ? ?我? o ? n ? s ? \”所以¸f t w ?基于“增大化现实”技术? ?l Q ? u ? ? ?我?泰?r t M ? ? ? ?我¸? s \ C ? M°s - s ?问? M ? 00。l m¨´¯”

Which points out the problem - it's building the WideString parameter as a char(300) value. Make it not broke.

这就指出了问题所在——它将WideString参数构建为char(300)值。使它不坏了。

The last i see of my WideString before it goes down the parameter hole is:

我最后看到我的WideString在它进入参数孔之前是:

ParameterObject.Value := NewValue;

where

在哪里

NewValue is a variant of type VT_BSTR (aka varOleStr) with the proper value
NewValue是具有适当值的VT_BSTR(又称varOleStr)类型的变体。
ParameterObject is a native ADO _Parameter object, with a .Type of 129 (adChar)
参数对象是一个本地的ADO _Parameter对象，带有。

Even trying to force the parameter type:

甚至试图强制参数类型:

cmd.Parameters.ParamByName('filename').DataType := ftWideString;
cmd.Parameters.ParamByName('filename').Value := s;

doesn't help.

没有帮助。

Note: This question is part of a series on how to paramaterize INSERT INTO foo (value) VALUES (%s)

注意:这个问题是关于如何将INSERT插入foo (value)值的系列文章的一部分(%s)

https://*.com/questions/10726212/using-wrong-type-with-parameterized-query-causes-error

https://*.com/questions/10726212/using-wrong-type-with-parameterized-query-causes-error

How big is an nvarchar(max) as far as ADO is concerned?

对于ADO来说，nvarchar(max)到底有多大?

How to parameterize widestrings using TADOCommand parameterized query?

如何使用TADOCommand参数化查询参数化widestrings ?

"Must declare the variable @myvariable" error with ADO parameterized query

“必须用ADO参数化查询声明变量@myvariable”错误。

2 个解决方案

#1

How about using

如何使用

cmd.Parameters.ParamByName('filename').Value := WideStringToUCS4String(s);

By the way, s is declared as widestring. is it necessary to have s as a widestring? How about just

顺便说一下，s被声明为widestring。有必要把s作为一个widestring吗?只是如何

var
  s : String;

in System.pas, UCS4String (UCS-4 byte or UTF-32 bits) is declared as:

在系统。pas, UCS4String (UCS-4字节或UTF-32位)被声明为:

...
...
UCS4Char = type LongWord;
...
UCS4String = array of UCS4Char;
...
function WideStringToUCS4String(const S: WideString): UCS4String;
...
function UCS4StringToWidestring(const S: UCS4String): WideString;

What data type you stored the filename column as ? Can sql server 2000 handle UTF-32 string?

您将文件名列存储为什么数据类型?sql server 2000可以处理UTF-32字符串吗?

#2

The answer is that it cannot be done in Delphi (5).

答案是它不能在Delphi(5)中完成。

It might be fixed in newer versions of Delphi; but without anyone to test it we won't know.

它可能被固定在新版本的Delphi中;但是如果没有人来测试，我们就不知道了。

Q.: How to parameterize widestrings using TADOCommand parameterized query?
A.: You can't. Sorry for the inconvience.

Q。:如何使用TADOCommand参数化查询来参数化widestrings ?一个。:你不能。对不起inconvience。

#1