Page 1 of 1
Firebird problem with VarChar fields
Posted: Mon 19 Sep 2011 22:59
by BruceEglington
I have a program which connects to a Firebird version 2.5 database to read a VarChar field. This field happens to be defined as ASCII but that is not the issue here. When using the Embarcadero version of the DbExpress driver, I have no problem connecting to and reading the contents of the VarChar field but when I use the Devart driver (latest version 3.0, dbexpida40.dll), I get an error -
Type mismatch for field 'IDFIELD', expecting: String actual: WideString.
I have not done anything unusual, just connected to the database with a TSQLConnection, a TSQLQuery and a Tclientdataset.
I use Rad Studio 2010.
Thanks
Bruce
Posted: Tue 20 Sep 2011 09:09
by AndreyZ
Hello,
To solve the problem, you should set the UseUnicode parameter of TSQLConnection to False. You can set it in design-time, or in run-time using the following code:
Code: Select all
SQLConnection.Params.Values['UseUnicode'] := 'False';
What about situations with both ASCII and Unicode
Posted: Tue 20 Sep 2011 14:26
by BruceEglington
Thanks, that works for this case but what about situations where I want Unicode. All I am doing is opening a query and linking to a TDBGrid or TDBEdit field. These should be able to handle either Unicode or non-Unicode and both could come through the same SQLconnection.
Posted: Tue 20 Sep 2011 15:44
by AndreyZ
This problem is caused by persistent fields that you have in your datasets. When UseUnicode is set to True, all character data is stored as WideStrings, and TStringField is replaced with TWideStringFiled. To be able to work both with Unicode data and non-Unicode data, you should remove persistent fields from your datasets.
Persistent fields
Posted: Tue 20 Sep 2011 16:00
by BruceEglington
Hi
thanks for the suggestion but that just does not work. When using TDB.. items in both GUI and Intraweb applications one needs to have persistent fields. That seems fundamental to most database applications. I don't understand how one can ever seriously suggest removing persistent fields for database connections, etc.
Bruce
Posted: Wed 21 Sep 2011 11:45
by AndreyZ
If you have persistent fields that you created with UseUnicode=False, all varchar fields will be mapped to TStringField. If you change UseUnicode to True and don't remove persistent fields, the following error will occur on dataset opening: "Type mismatch for field 'FIELDNAME', expecting: String actual: WideString".
And in another case, if you have persistent fields that you created with UseUnicode=True, all varchar fields will be mapped to TWideStringField. If you change UseUnicode to False and don't remove persistent fields, the following error will occur on dataset opening: "Type mismatch for field 'FIELDNAME', expecting: WideString actual: String". As you can see, if you want to change the UseUnicode property, you have to remove persistent fields.
persistent fields
Posted: Wed 21 Sep 2011 12:55
by BruceEglington
Okay, I think I understand. It is not that I need to not have persistent fields but rather that I need to recreate them with the Unicode setting set to True. In this case, all fields, including ASCII ones wil be treated as widestrings.
Presumably I could edit the Delphi .dfm files as text and change all string fields (or whatever they are) to widestring (or whatever). Would this work to speed up the process as I have hundreds of tables/queries with multiple string fields.
Thanks
Bruce
Posted: Wed 21 Sep 2011 13:53
by AndreyZ
Yes, you can replace all TStringField with TWideStringField in your dfm files.
Posted: Thu 22 Sep 2011 04:25
by BruceEglington
Thanks
Bruce
Re: Firebird problem with VarChar fields
Posted: Tue 17 Jul 2012 08:33
by ralle1
Hello Andrey!
I‘m just working on a conversion of a large project from Delphi 2007 to XE2.
In this context, I’ve seen this error when I connect to a unicode batabase with UseUnicode=true.
So I switched every TStringField to TWideStringField and it works fine. But:
I need to be backward compatible. There will be some customers working with Unicode-Charsets and others will continue to work with win1252 databases. Because of my called change, I can’t work with a win1252 database.
Do you know an elegant solution for this probelm?
Thanks, Ralle
Re: Firebird problem with VarChar fields
Posted: Tue 24 Jul 2012 12:56
by ZEuS
To let the application connect to both Unicode and non-Unicode databases without errors, you can switch persistent fields from TWideStringField to TStringField, and set UseUnicode to False and Charset to UTF8 in TIBCConnection.Options.
Re: Firebird problem with VarChar fields
Posted: Fri 27 Jul 2012 06:52
by ralle1
If I set UseUnicode to False, The strings are displayed incorrectly.
Special characters like german umlauts German look like this: Aöü
what am I doing wrong??
Re: Firebird problem with VarChar fields
Posted: Mon 30 Jul 2012 08:37
by ZEuS
To display and set fields data correctly you should convert field values in the TStringField.OnGetText and TStringField.OnSetText events like this:
Code: Select all
procedure TForm1.Field1GetText(Sender: TField; var Text: string; DisplayText: Boolean);
begin
Text := Utf8ToAnsi(Sender.AsString);
end;
procedure TForm1.Field1SetText(Sender: TField; const Text: string);
begin
Sender.AsString := AnsiToUtf8(Text);
end;
Re: Firebird problem with VarChar fields
Posted: Mon 30 Jul 2012 09:50
by ralle1
Thank you for your answer.
I put the events as described. All characters are displayed correctly, but when I edit a record and post it appears an error:
Code: Select all
Dynamic SQL Error
SQL error code = -303
Malformed string.
The error only occurs when special characters are included.
Do you have also a solution to this?
Thanks!
Ralle
Re: Firebird problem with VarChar fields
Posted: Mon 06 Aug 2012 07:33
by ZEuS
Such error may occur when a dataset field does not convert it's value to UTF8 when applying changes to the database. Please make sure that all the persistent fields have the OnSetText event handler. If the problem persists, please try to create a small sample that demonstrates the problem, including a script to create the test database and the test table, and send it to eugeniyz*devart*com.