(Firebird, Unicode) Problem with €-Sign

[mscherr]

Hi!

I can read/write Unicode Text from/to the DB by using a grid.
But when I have a "€"-sign in my text, it fails. If I remove it, it works.

OS: XP; IDE: D2009; FB: 2.0
The callstack is:

[font=Courier New]exception class : EZDatabaseError
exception message : SQL Error: Malformed string. Error Code: -104. Invalid token The SQL: UPDATE WOERTER SET BEGRIFF=? WHERE BEGRIFF=? AND LANGTEXT=?;.

main thread ($730):
005d8179 +0bd ZEOSTest.exe ZAbstractDataset 383 +10 TZAbstractDataset.InternalUpdate
005d8463 +0ff ZEOSTest.exe ZAbstractDataset 470 +17 TZAbstractDataset.InternalPost[/font]

Can someone reproduce this error?

Michael

[mscherr]

Another CallStack on this issue:

[font=Courier New]exception class : EZDatabaseError
exception message : 0 record(s) updated. Only one record should have been updated.

main thread ($c58):
005d8179 +0bd ZEOSTest.exe ZAbstractDataset 383 +10 TZAbstractDataset.InternalUpdate
005d8463 +0ff ZEOSTest.exe ZAbstractDataset 470 +17 TZAbstractDataset.InternalPost[/font]

I'm new to this unicode stuff.
Maybe it has something to do with my table?

[hr]
Table: Collate UTF8

Unicode-chars in grid
>> Exception - Malformed string / Invalid token

SQL.Text:= 'update woerter set langtext=:text';
ParamByName('text').AsString:= 'ABC123€€€';
>> Exception - Malformed string / Invalid token

SQL.Text:= 'update woerter set langtext=''ABC123€€€''';
>> OK, but Wrong chars in Grid and wrong chars by .AsString

[hr]
Table: Collate Unicode_FSS

Unicode-chars in Grid
>> Ok

SQL.Text:= 'update woerter2 set text=:text';
ParamByName('Text').AsString:= 'ABC123€€€';
>> OK in Grid, OK by .AsString

SQL.Text:= 'update woerter2 set text=''ABC123€€€''';
>> OK, but Wrong chars in Grid and wrong chars by .AsString

[hr]
It seems that SQL.Text is not handled as WideString!?

[seawolf]

Try adding, on unit ZInterbaseToken.pas

constructor TZInterbaseWordState.Create;
begin
SetWordChars(#0, #255, False);
SetWordChars('a', 'z', True);
SetWordChars('A', 'Z', True);
SetWordChars('0', '9', True);
SetWordChars('_', '_', True);
SetWordChars('$', '$', True);
SetWordChars('€', '€', True);
end;

Does is solve the problem?

[mscherr]

No, it doesn't.

You can test it in D2009 by creating a FB table in Unicode_FSS which has a text field. Set the SQL.Text property of a ZQuery to something like...

insert into testtable (text) values ('ABC123');
ExecSQL

Works!

insert into testtable (text) values ('ABC123€€€');
ExecSQL

Invalid chars.


Maybe these special characters are converted to utf8 and not converted back?
I ask because it seems to me that every char of the €-signs is doubled when I read them back.

[seawolf]

The "fun" side is I test that code with Delphi 6 and it works as expected.
Anyway tomorrow I'm going to plan a test with D2009

[mscherr]

Thanks.

Here is an small testproject with a test-db to show this issue.

It shows the issue with UNICODE_FSS tables and SQL.Text / SQL Parameters.
It does not show the issue with UTF8 tables and entering chars in a grid / dbcontrol.

[seawolf]

Try this:

Unit ZdbcInterbase6Utils.pas

function PrepareStatement(PlainDriver: IZInterbasePlainDriver;
Handle: PISC_DB_HANDLE; TrHandle: PISC_TR_HANDLE; Dialect: Word;
SQL: string; var StmtHandle: TISC_STMT_HANDLE):
TZIbSqlStatementType;
var
StatusVector: TARRAY_ISC_STATUS;
begin
{ Allocate an sql statement }
PlainDriver.isc_dsql_alloc_statement2(@StatusVector, Handle, @StmtHandle);
CheckInterbase6Error(PlainDriver, StatusVector, lcExecute, Sql);

{ Prepare an sql statement }
{$IFDEF DELPHI12_UP}
PlainDriver.isc_dsql_prepare(@StatusVector, TrHandle, @StmtHandle,
0, PAnsiChar(UTF8String(SQL)), Dialect, nil);
{$ELSE}
PlainDriver.isc_dsql_prepare(@StatusVector, TrHandle, @StmtHandle,
0, PAnsiChar(SQL), Dialect, nil);
{$ENDIF}
CheckInterbase6Error(PlainDriver, StatusVector, lcExecute, SQL);

I tried with the test application (pushing test insert) and it works as expected to me

[SlavoF]

http://zeos.firmos.at/viewtopic.php?t=2295