Zeoslib Portal

Posted: **22.06.2013, 07:33**

Hi, I'm having trouble inserting Unicode into an SQL Server 2008 database using ZeosLib 7.0.3 and D7. I've created a simple test program that first inserts and then queries the inserted value to see if it has been inserted properly.

The test table schema:

Code: Select all

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

I've upload the simple test program source (ZeosLib source included) - link here http://www.2shared.com/file/jwyx79eE/testdb.html 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 - http://www.axolot.com/TNT/

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 in MSSQL.

Will appreciate any pointers.

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

ps: I've also tried "FZQuery.SQL.add(sqlstring)" instead of "FZQuery.SQL.Text := sqlstring".

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.

Posted: **22.06.2013, 18:21**

touchring,

before testing you apps:

D7 doesn't support Wide-String/Memo fields. Thought TNT needs WideFields, so i'm starting from the premisse you made your own UTF8 hack?

Suggestions for using these Components (i've never used before):

TZConnection.ClientCodePage := '';<- Choose your Windows encoding
TZConnection.ControlsCodePager := zCP_UTF8;
TZConnection.AutoEncodeStrings := True;

Please test my suggestion first. Othewise reply here.

Posted: **23.06.2013, 15:34**

Hi EgonHugeist , thanks for replying.

D7 doesn't support Wide-String/Memo fields. Thought TNT needs WideFields, so i'm starting from the premisse you made your own UTF8 hack?

I've not made any changes to zeoslib. From what I know, D7 supports UTF-8 string manipulation and conversion from codepages to utf8 and vice versa. As for displaying unicode on the form, we can use TNT controls.

TZConnection.ClientCodePage := '';<- Choose your Windows encoding
TZConnection.ControlsCodePager := zCP_UTF8;
TZConnection.AutoEncodeStrings := True;

Based on my tests, if AutoEncodeStrings := True, then if the data is UTF8 encoded, it will be converted by Zeoslib into the server collation codepage.

ClientCodePage and ControlsCodePage don't appear to affect the data encoding. Do you know what are they for?

By the way, as mentioned, I am able to enter UTF8 encoded data into MSSQL and also read UTF8 data from it. However, I can't use UTF8 as characters are lost when stored as UTF8 in MSSQL - due to MSSQL not fully supporting UTF8.

In my test program, I entered the literal unicode string into the database, but I'm not able to retrieve the literal string on retrieval, they appear as question marks. And in SQL management studio also.

Code: Select all

...
var
sqlstring, data:widestring;
...
  data:= tntedit1.Text;
  sqlstring:= 'INSERT INTO INCOMINGLOG ([MESSAGE]) VALUES(N''' + data + ''')';

Posted: **23.06.2013, 18:29**

touchring,

you corrupt your own data!

How it works:

Code: Select all

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 := 'zeoslib';
  FZConnection.HostName := 'localhost';
  FZConnection.User := 'sa';
  FZConnection.Password := 'masterkey';
  FZConnection.Connect;
  FZQuery.SQL.Text := 'SELECT * from incominglog';
  FZQuery.ExecSQL;
  FZQuery.Open;
  FZQuery.First;
  while not FZQuery.EOF do
  begin
    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 := 'zeoslib';
  FZConnection.HostName := 'localhost';
  FZConnection.User := 'sa';
  FZConnection.Password := 'masterkey';
  FZConnection.Connect;
  data:= tntedit1.Text;
  sqlstring:= 'INSERT INTO INCOMINGLOG ([MESSAGE]) VALUES(N''' + data + ''')';
  FZQuery.SQL.Text := utf8encode(sqlstring);
  FZQuery.ExecSQL;
end;

end.

TZQuery.SQL is a TStringList and is NOT able to pickup WideString values. So D7 cast the WideString down to '?'

But you opened my eyes for D2005up for a nasty thing (instable codepages! for the Strings). Not related to you but a problem for other users. Can you check my suggestion and verify it with you SQLStudio?

Posted: **24.06.2013, 05:30**

Hi Michael, thanks.

I'm able to get it work partially by encoding the data as UTF-8 (your code to encode the entire SQL string works also) however, SQL Server doesn't support UTF8 fully, so some characters are lost - http://stackoverflow.com/questions/1251 ... utf8-howto

I've verified that in my tests, this was the reason why I had to try to use unicode.

Is there an alternative to using FZQuery.SQL?

Posted: **25.06.2013, 18:55**

touchring,

Is there an alternative to using FZQuery.SQL?

courrently i've to say nope it isn't. We internally can read the fields but we do not process this special 'NCHAR' case, sorry. Unfortunally only on TDataSet-layer. We miss Real-PreparedStatments which (i hope so) can tell us if the Fields/Paramters either Ansi or UTF8 expect. Don't hesitate to check our current codebase (:, Write a patch and i'll commit it. Or raise a ticket on SF.NET.

Using D7, you can assamle your queries like this:

update 'blabla' values (1, '??????','+UTF8Encode(YourWideString)+', ???)'

So you can avoid data loss and each expected field gets it's expected value.
Again: Take care you don't run into the same issues..

Posted: **25.06.2013, 18:56**

touchring,

Double post..

Hint you can also use:

TZQuery.FieldByName('xx').AsString := UTF8Encode(AWideString);
TZQuery.Fields[1].AsString := UTF8Encode(AWideString);
TZQuery.ParamByName('xx').AsString := UTF8Encode(AWideString);
TZQuery.Params('xx').AsString := UTF8Encode(AWideString);

Don't use TZConnection.AutoEncodeStrings = True; for this case;

This case should work with all Ansi-Compilers. It dosn't work with the UnicodeIDE's since they automaltically change the Field/Param-Types to ftWideString/ftWideMemo. That's the case where we miss the expected string-type determination..

Posted: **28.06.2013, 04:21**

Hi Michael, I've decided to encode in specific page code instead of UTF8 which appears to work for MSSQL and Zeoslib.

Code: Select all

function WideStringToString(const ws: WideString; codePage: Word): AnsiString;
var
  l: integer;
begin
  if ws = '' then Result := ''
  else
  begin
    l := WideCharToMultiByte(codePage,
      WC_COMPOSITECHECK or WC_DISCARDNS or WC_SEPCHARS or WC_DEFAULTCHAR,
      @ws[1], - 1, nil, 0, nil, nil);
    SetLength(Result, l - 1);
    if l > 1 then
      WideCharToMultiByte(codePage,
        WC_COMPOSITECHECK or WC_DISCARDNS or WC_SEPCHARS or WC_DEFAULTCHAR,
        @ws[1], - 1, @Result[1], l - 1, nil, nil);
  end;
end; { WideStringToString }

Code: Select all

  data:= tntedit1.Text; 

 if sqlcharset <> 65001 then
    data :=WideStringToString(data,sqlcharset)
else data := UTF8Encode(data);

  if data = '' then
    data := tntedit1.Text;

Posted: **30.06.2013, 17:11**

touchring,

great! Hint: replace dwflags with 0 for function WideCharToMultiByte. It's so much faster!

Posted: **03.07.2013, 15:07**

EgonHugeist wrote:touchring,

great! Hint: replace dwflags with 0 for function WideCharToMultiByte. It's so much faster!

Thanks for the hint.

Zeoslib Portal

[solved] Can't insert Unicode into MSSQL 2008 using v7.0.3

[solved] Can't insert Unicode into MSSQL 2008 using v7.0.3