Needs help with UTF8 and swedish characters

[stab]

Hi all,

When trying to make an insert into a PostgreSQL-database using dbExpress as:
Insert into KeysInfo(PK, CustomerId, KeyInfo, Location, Info, Timestamp) Values('115', 'MCO98008161', 'GUL 84', '', 'GEMENSAM BEHÖRIGHETSKOD 808182', '2009-04-07 09:30:57')
I get the error:
'SQL Server Error: invalid byte sequence for encoding "UTF8": 0xd652'

I checked the UTF8 character code table and found out that
swedish 'Ö' is 0x214 and when checking every character before SQLDataSet.ExecSQL I confirmed that 'Ö' is encoded the right way.

My database is created as
CREATE DATABASE "mydb"
WITH OWNER = myowner
ENCODING = 'UTF8';

and the SQLConnection is initiated as:
SQLConnection.Params.Values['Database'] := 'mydb';
SQLConnection.Params.Values['User_Name'] := 'myuser';
SQLConnection.Params.Values['Password'] := 'mypassword';
SQLConnection.Params.Values['ServerCharSet'] := 'UTF8';
SQLConnection.Params.Values['UseUnicode'] := 'True';
SQLConnection.Connected := true;

Any help appreciated

Regards
stab

[Plash]

If you encode your SQL statement to UTF-8 manually before passing to the ExecSQL method, this can cause the error.

When you set the UseUnicode option to True, the driver automatically encodes the SQL statement to UTF-8. You should pass the SQL as is.

The ServerCharSet option means actually 'client character set' (same as the CharSet option). This option is ignored when UseUnicode = True.

[stab]

Changed according to your suggestion:
SQLConnection.Params.Values['Database'] := 'mydb';
SQLConnection.Params.Values['User_Name'] := 'myuser';
SQLConnection.Params.Values['Password'] := 'mypassword';
SQLConnection.Params.Values['Database'] := FDbName;
SQLConnection.Params.Values['User_Name'] := FUserName;
SQLConnection.Params.Values['Password'] := FPassword;
SQLConnection.Params.Values['UseUnicode'] := 'True';
SQLConnection.Connected := true;

and sent the SQL-statement as is with no manual encoding,
but the problem persists

Could it be something with my table KeysInfo that is declared as:
CREATE TABLE keysinfo
(
pk integer NOT NULL,
customerid character varying(50) NOT NULL,
keyinfo character varying(15),
"location" character varying(30),
info character varying(500),
"timestamp" timestamp without time zone,
CONSTRAINT pk_keysinfo PRIMARY KEY (pk)
)
WITH (OIDS=FALSE);
ALTER TABLE keysinfo OWNER TO postgres;

Any help appreciated

Regards
stab

[stab]

Forgot to say that my dbExpress-driver is version 1.00.3

stab

[Plash]

Note: you can use the driver extended options in the Params property of TSQLConnection starting with Delphi 2007.

If you are using an older Delphi version, you should use TCRSQLConnection component to set UseUnicode option in Params.

[stab]

Sorry, but the error is still there.

Below is a very simple testprogram that results in the error.

The program(Delphi 7) has a TSQLDataSet, a TEdit and a TButton.
In design-mode the TEdit.Text is set to:
Insert into KeysInfo(PK, CustomerId, KeyInfo, Location, Info, Timestamp) Values('115', 'MCO98008161', 'GUL 84', '', 'GEMENSAM BEHÖRIGHETSKOD 808182', '2009-04-07 09:30:57')
Clicking the Button results in error:
SQL Server Error: invalid byte sequence for encoding "UTF8": 0xd652

Could you please check if I have misunderstood something that causes the error.

The strange thing is when copying the text from the TEdit and pasting it into a query in pgAdmin III it is inserted with no error at all. Very nicely you find the swedish characters just as they should be.

unit DevartPostgesSQLTestMain;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, CRSQLConnection, DBXpress, FMTBcd, StdCtrls, DB, SqlExpr;

type
TForm1 = class(TForm)
SQLDataSet1: TSQLDataSet;
Edit1: TEdit;
Button1: TButton;
procedure FormCreate(Sender: TObject);
procedure FormDestroy(Sender: TObject);
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
FError : string;

FCRSQLConnection : TCRSQLConnection;
public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
begin
FCRSQLConnection := TCRSQLConnection.Create(Self);
SQLDataSet1.SQLConnection := FCRSQLConnection;
FCRSQLConnection.Params.Values['HostName'] := 'localhost';
FCRSQLConnection.ConnectionName := 'Devart PostgreSQL';
FCRSQLConnection.DriverName := 'DevartPostgreSQL';
FCRSQLConnection.GetDriverFunc := 'getSQLDriverPostgreSQL';
FCRSQLConnection.LibraryName := 'dbexppgsql.dll';
FCRSQLConnection.VendorLib := 'dbexppgsql.dll';
FCRSQLConnection.Params.Values['Database'] := FDbName;
FCRSQLConnection.Params.Values['User_Name'] := FUserName;
FCRSQLConnection.Params.Values['Password'] := FPassword;
FCRSQLConnection.Params.Values['UseUnicode'] := 'True';
try
FCRSQLConnection.Connected := true;
except
on E:Exception do
FError := E.Message;
end;
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
if FCRSQLConnection.Connected then
FCRSQLConnection.Connected := false;
FreeAndNil(FCRSQLConnection);
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
SQLDataSet1.CommandText := Edit1.Text;
try
SQLDataSet1.ExecSQL(true);
except
on E:Exception do
FError := E.Message;
end;
end;

end.

Regards
stab

[Plash]

We have reproduced the problem. The UseUnicode option is actually supported for Delphi 2006 and higher.

You should use the CharSet option instead. Add the following parameter:
CharSet=WIN1252

You should set CharSet to the character set of your client application (not a database).

Unfortunatly, the CharSet option does not work in Delphi 7 due to a bug. We have fixed this bug in the new build which release is scheduled for the next week.

[stab]

Hi,

According to your latest message a new build should have been done a week ago.

How is it going?

Regards
stab

[stab]

It's working, thanks alot

stab