Search code examples
delphiadodelphi-5parameterized-query

How to parameterize widestrings using TADOCommand parameterized query?

i am trying to use a parameterized query with 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

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

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̝

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

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

Bonus Chatter

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

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¯ '

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

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

ParameterObject.Value := NewValue;

where

  • NewValue is a variant of type VT_BSTR (aka varOleStr) with the proper value
  • ParameterObject is a native ADO _Parameter object, with a .Type of 129 (adChar)

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)

Solution

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

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

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