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-issue-importer opened this issue Jun 17, 2010 · 11 comments
Closed

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

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

Comments

@firebird-issue-importer

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 17, 2010

Commented by: @asfernandes

I need reproducable test case, preferable using ISQL.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 17, 2010

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 17, 2010

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 17, 2010

Commented by: @asfernandes

What is client/connection charset?

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 17, 2010

Commented by: Sascha Michel (datiscum1)

The charset is UTF8 !

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 17, 2010

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 17, 2010

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 17, 2010

Commented by: @asfernandes

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 17, 2010

Modified by: @asfernandes

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

resolution: Won't Fix [ 2 ]

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 17, 2010

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jun 21, 2010

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
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants