Unable to insert Unicode into SQL Server 2008 using Delphi ZeosLib and Delphi 7

Question

I'm having trouble inserting Unicode into a SQL Server database using Delphi ZeosLib and Delphi 7, and then reading the inserted value. I've created a simple test program that first inserts and then queries the inserted value.

The test table schema:

CREATE TABLE [dbo].[incominglog](
    [message] [nvarchar](500) NULL
) ON [PRIMARY]

I've upload the simple test program source (ZeosLib source included) - click here to download. I've also included ntwdblib.dll but you can use your own.

The test program also requires TNT component which can be downloaded from here

Using the test program, the Unicode characters that I have inserted appear as question marks on retrieval - I'm not certain whether the problem lies with the insert code or the query code.

I've also tried encoding the data into utf-8 before inserting and then decoding the data after retrieving from utf-8 - please search "//inserted as utf8" in the test program source. I'm able to view the Unicode after it has been decoded, so this method works. However, for my actual application, I can't encode as UTF-8 as SQL Server doesn't support UTF-8 completely - some characters can't be stored. See my previous question here.

Will appreciate any pointers. :)

Meanwhile, here's the source for the Test program:

unit Unit1;

interface

uses
  ZConnection, Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ZAbstractRODataset, ZAbstractDataset, ZAbstractTable, ZDataset,
  StdCtrls, TntStdCtrls;

type
  TForm1 = class(TForm)
    Button1: TButton;
    TntMemo1: TTntMemo;
    Button2: TButton;
    TntEdit1: TTntEdit;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;
  FZConnection: TZConnection;
  FZQuery: TZQuery;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
  tntmemo1.Lines.Clear;
  FZConnection := TZConnection.Create(Owner);
  FZConnection.LoginPrompt := False;
  FZQuery := TZQuery.Create(Owner);
  FZQuery.Connection := FZConnection;
  FZConnection.Protocol := 'mssql';
  FZConnection.Database := 'replace-with-your-db';
  FZConnection.HostName := 'localhost';
  FZConnection.User := 'sa';
  FZConnection.Password := 'replace-with-your-password';
  FZConnection.Connect;
  FZQuery.SQL.Text := 'SELECT * from incominglog';
  FZQuery.ExecSQL;
  FZQuery.Open;
  FZQuery.First;
  while not FZQuery.EOF do
  begin
   tntmemo1.Lines.add(FZQuery.FieldByName('message').AsString);
  // tntmemo1.Lines.add(utf8decode(FZQuery.FieldByName('message').AsString));  //inserted as utf8
    FZQuery.Next;
  end;

end;

procedure TForm1.Button2Click(Sender: TObject);
var
sqlstring, data:widestring;
begin
  FZConnection := TZConnection.Create(Owner);
  FZConnection.LoginPrompt := False;
  FZQuery := TZQuery.Create(Owner);
  FZQuery.Connection := FZConnection;
  FZConnection.Protocol := 'mssql';
  FZConnection.Database := 'replace-with-your-db';
  FZConnection.HostName := 'localhost';
  FZConnection.User := 'sa';
  FZConnection.Password := 'replace-with-your-password';
  FZConnection.Connect;
  data:= tntedit1.Text;
 // data:= utf8encode(tntedit1.Text);  //inserted as utf8
  sqlstring:= 'INSERT INTO INCOMINGLOG ([MESSAGE]) VALUES(N''' + data + ''')';
  FZQuery.SQL.Text := sqlstring;
  FZQuery.ExecSQL;
end;

end.

Answer 1

I have not tested your example, but I'm able to save and retrieve from database Unicode characters without problem on SQL Server with Delphi 7 VCL/CLX and zeoslib.

I think in your case it will be enough changing your save procedure like this :

procedure TForm1.Button2Click(Sender: TObject);
var
sqlstring : widestring;
data : UTF8String;
begin
  FZConnection := TZConnection.Create(Owner);
  FZConnection.LoginPrompt := False;
  FZQuery := TZQuery.Create(Owner);
  FZQuery.Connection := FZConnection;
  FZConnection.Protocol := 'mssql';
  FZConnection.Database := 'replace-with-your-db';
  FZConnection.HostName := 'localhost';
  FZConnection.User := 'sa';
  FZConnection.Password := 'replace-with-your-password';
  FZConnection.Connect;
  data:= UTF8String( utf8encode(tntedit1.Text) );
  sqlstring:= 'INSERT INTO INCOMINGLOG ([MESSAGE]) VALUES(:DATA)';
  FZQuery.SQL.Text := sqlstring;
  FZQuery.ParamByName('DATA').AsString := data;
  FZQuery.ExecSQL;
end;

The point is changing your data string variable in UTF8String type, and use parameters pass the data string to the query ... To be honest I use it with a ZTable and Post command, but it should be the same with a ZQuery like yours ...