Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

ordering type other then UTF8 in a UTF8 DataBase [CORE3054] #3434

Closed
firebird-automations opened this issue Jun 17, 2010 · 11 comments
Closed

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Sascha Michel (datiscum1)

create collation unicode_num for utf8
from unicode 'NUMERIC-SORT=1';

CREATE DOMAIN "FileName" AS
VARCHAR(80) CHARACTER SET UTF8
COLLATE UNICODE_NUM;

CREATE TABLE "DruckJobs" (
"DruckJob_ID" INTEGER,
"DrJob_AuftragDokID" INTEGER,
"DrJob_AusgabeArt" INTEGER NOT NULL,
"DrJob_AusgabeFileName" "FileName" COLLATE UNICODE_NUM
);

The following insert will work correctly with Firebird 2.5 Beta2
FIBDS_DruckJob->UpdateTransaction->ExecSQLImmediate( "insert into \"DruckJobs\" ( \"DrJob_AuftragDokID\" 2, \"DrJob_AusgabeArt\" , \"DrJob_AusgabeFileName\" ) values ( 73 , 1 , 'DV_Auftragsbestätigung_73.pdf' )");

But any prepared Statement insert will fail with the following error message:

Error Message:
----------------------------------------
Incompatible column/host variable data type.
Dynamic SQL Error.
SQL error code = -303.
Malformed string.

When i change the column "DrJob_AusgabeFileName" ordering type to "UTF8" all will work fine !!

Reagrs,
Sascha Michel

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

I need reproducable test case, preferable using ISQL.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pmakowski

I can't reproduce

SQL> create collation unicode_num for utf8
CON> from unicode 'NUMERIC-SORT=1';
SQL> CREATE DOMAIN "FileName" AS
CON> VARCHAR(80) CHARACTER SET UTF8
CON> COLLATE UNICODE_NUM;
SQL> CREATE TABLE "DruckJobs" (
CON> "DruckJob_ID" INTEGER,
CON> "DrJob_AuftragDokID" INTEGER,
CON> "DrJob_AusgabeArt" INTEGER NOT NULL,
CON> "DrJob_AusgabeFileName" "FileName" COLLATE UNICODE_NUM
CON> );
SQL> show table "DruckJobs";
DruckJob_ID INTEGER Nullable
DrJob_AuftragDokID INTEGER Nullable
DrJob_AusgabeArt INTEGER Not Null
DrJob_AusgabeFileName (FileName) VARCHAR(80) CHARACTER SET UTF8 Nullable
COLLATE UNICODE_NUM

SQL> insert into "DruckJobs" ( "DrJob_AuftragDokID" , "DrJob_AusgabeArt" , "DrJob_AusgabeFileName" ) values ( 73 , 1 , 'DV_Auftragsbesttigung_73.pdf' );
SQL> commit;
SQL> set planonly;
SQL> insert into "DruckJobs" ( "DrJob_AuftragDokID" , "DrJob_AusgabeArt" , "DrJob_AusgabeFileName" ) values ( 73 , 1 , ? );
SQL> insert into "DruckJobs" ( "DrJob_AuftragDokID" , "DrJob_AusgabeArt" , "DrJob_AusgabeFileName" ) values ( ? , ? , ? );
SQL> show version;
ISQL Version: LI-V2.5.0.26024 Firebird 2.5 Release Candidate 3
Server version:
Firebird/linux Intel (access method), version "LI-V2.5.0.26024 Firebird 2.5 Release Candidate 3"
Firebird/linux Intel (remote server), version "LI-V2.5.0.26024 Firebird 2.5 Release Candidate 3/tcp (debian32)/P12"
Firebird/linux Intel (remote interface), version "LI-V2.5.0.26024 Firebird 2.5 Release Candidate 3/tcp (debian32)/P12"
on disk structure version 11.2

@firebird-automations
Copy link
Collaborator Author

Commented by: Sascha Michel (datiscum1)

O.K.
This happens only if the string used in the insert statement contains a non-ascii character like the "ä".

In your example it is 'DV_Auftragsbesttigung_73.pdf' in my it is 'DV_Auftragsbestätigung_73.pdf'
ä This is the real problem !!

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

What is client/connection charset?

@firebird-automations
Copy link
Collaborator Author

Commented by: Sascha Michel (datiscum1)

The charset is UTF8 !

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

The string is probably not encoded in UTF-8, so it's really "malformed". If you don't think so, you should create a test case.

@firebird-automations
Copy link
Collaborator Author

Commented by: Sascha Michel (datiscum1)

[0] L'D' 68U (0x0044)
[1] L'V' 86U (0x0056)
[2] L'_' 95U (0x005F)
[3] L'A' 65U (0x0041)
[4] L'u' 117U (0x0075)
[5] L'f' 102U (0x0066)
[6] L't' 116U (0x0074)
[7] L'r' 114U (0x0072)
[8] L'a' 97U (0x0061)
[9] L'g' 103U (0x0067)
[10] L's' 115U (0x0073)
[11] L'b' 98U (0x0062)
[12] L'e' 101U (0x0065)
[13] L's' 115U (0x0073)
[14] L't' 116U (0x0074)
[15] L'ä' 228U (0x00E4) <-- this is O.K. for me

And when i change the Sort order not the Charset for the field all works fin :-)

[16] L't' 116U (0x0074)
[17] L'i' 105U (0x0069)
[18] L'g' 103U (0x0067)
[19] L'u' 117U (0x0075)
[20] L'n' 110U (0x006E)
[21] L'g' 103U (0x0067)
[22] L'\0' 0U (0x0000)

I use C++ Builder 2009 and have no problem with UTF8 !
Is there anything wrong for you ?

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

This is not UTF-8 string. UTF-8 string has each non-ascii character represented by at least two bytes.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Sascha Michel (datiscum1)

UTF8String DokumentFileName ="DV_Auftragsbestätigung";

[0] 'D' 68 (0x44)
[1] 'V' 86 (0x56)
[2] '_' 95 (0x5F)
[3] 'A' 65 (0x41)
[4] 'u' 117 (0x75)
[5] 'f' 102 (0x66)
[6] 't' 116 (0x74)
[7] 'r' 114 (0x72)
[8] 'a' 97 (0x61)
[9] 'g' 103 (0x67)
[10] 's' 115 (0x73)
[11] 'b' 98 (0x62)
[12] 'e' 101 (0x65)
[13] 's' 115 (0x73)
[14] 't' 116 (0x74)
[15] 'ä' -28 (0xE4)
[16] 't' 116 (0x74)
[17] 'i' 105 (0x69)
[18] 'g' 103 (0x67)
[19] 'u' 117 (0x75)
[20] 'n' 110 (0x6E)
[21] 'g' 103 (0x67)
[22] '\0' 0 (0x00)

O.K. this works ! :-(

:-) thanks

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants