Insert BlobField problem (was TMyConnection Charset problem)

[geekman1024]

Hi all,
I'm developing an application for member management, which will require using Chinese characters for member name. I used to use GB2312 charset, which works fine, but I have to switch to GBK charset, since GBK could display more characters that Gb2312 could not display.

I have changed the MySQL database and tables to work fine with GBK charset, but when I switch the TMyConnection->Options->Charset setting to GBK, it gives error #42000. The member name field has been converted to some garbage that my application could not handle.

See error screenshot:


What I'm trying to do is copy the selected member data to another table (both tables have identical field design, the destination table serves as a temporary holder for selected members data) for information printout.

Here is the code snippet for the copy operation, I'm using MyDAC 5.55.0.39, MySQL 5.0.24 and Borland C++ Builder 6 Enterprise and WinXP Professional SP3 (Chinese Traditional edition, but configure to work with Chinese Simplified applications).

Code: Select all

TDataSet *ds = MemSrcGrid->DataSource->DataSet;
TBlobField *dstblob;
TStream *srcstream;

for(int i=0; iSelectedRows->Count; i++)
{
    ds->GotoBookmark((void *)MemSrcGrid->SelectedRows->Items[i].c_str());
    CardT->Append();
    CardT->FieldByName("mem_id")->AsString = "*" + ds->FieldByName("mem_id")->AsString + "*";
    CardT->FieldByName("mem_name1")->AsString = ds->FieldByName("mem_name1")->AsString;
    CardT->FieldByName("mem_name2")->AsString = ds->FieldByName("mem_name2")->AsString;
    CardT->FieldByName("mem_year")->AsString = ds->FieldByName("mem_year")->AsString;
    CardT->FieldByName("mem_class")->AsString = ds->FieldByName("mem_class")->AsString;
    CardT->FieldByName("mem_sex")->AsString = ds->FieldByName("mem_sex")->AsString;

    srcstream = ds->CreateBlobStream(ds->FieldByName("mem_photo"), bmRead);
    dstblob = (TBlobField *)CardT->FieldByName("mem_photo");
    dstblob->LoadFromStream(srcstream);
    CardT->FieldByName("mem_kodsek")->AsString = ds->FieldByName("mem_kodsek")->AsString;
    CardT->Post();
}

CardT is the destination table and MemSrcGrid is the TCRDBGrid containing the source data.

Can anyone tell me how to make GBK charset work (GB2312 works fine with the exact same code), please?

[geekman1024]

after experimenting and tracing each parameters, I figured out that the "garbage data" was not caused by the Chinese characters in member names, but rather, by the Blob (photo) data. So, why would the Blob data being treated as text data, and how do I make it not being treated as text data?

below is another version (Using Query->Parameters method, which I figure out after reading other posts about Blobs in the forum):

Code: Select all

TMyQuery *QTransfer;

srcstream = ds->CreateBlobStream(ds->FieldByName("mem_photo"), bmRead);

QTransfer->Active = false;
QTransfer->ParamByName("sid")->AsString = "*" + ds->FieldByName("mem_id")->AsString + "*";
QTransfer->ParamByName("name1")->AsString = ds->FieldByName("mem_name1")->AsString;
QTransfer->ParamByName("name2")->AsString = ds->FieldByName("mem_name2")->AsString;
QTransfer->ParamByName("year")->AsString = ds->FieldByName("mem_year")->AsString;
QTransfer->ParamByName("class")->AsString = ds->FieldByName("mem_class")->AsString;
QTransfer->ParamByName("sex")->AsString = ds->FieldByName("mem_sex")->AsString;
QTransfer->ParamByName("photo")->LoadFromStream(srcstream, ftBlob);
QTransfer->ParamByName("code")->AsString = ds->FieldByName("mem_kodsek")->AsString;
QTransfer->Execute();

and this is the SQL statement:

Code: Select all

INSERT INTO cards
(mem_id, mem_name1, mem_name2, mem_year, mem_class, mem_sex, mem_photo, mem_kodsek)
VALUES
(
:sid,
:name1,
:name2,
:year,
:class,
:sex,
:photo,
:code
);

[geekman1024]

hmm... very odd situation here.

the Blob field is actually photo of the members, being desperate, I tried to transfer members data one-by-one, and found out some of the data may transfer without problem, while some will not transfer at all (The 1st through 4th records would not transfer, but the 5th and 6th transferred without any problem, then the rest would not transfer... ).

Any idea why some Blob data could be inserted in to the destination table while some could not?

[swierzbicki]

Hi,

IMO, create a sample project + sample MySQL DB and send it to DevArt support. You should also send your MySQL ini file.

[geekman1024]

thanks for the suggestion, swierzbicki, but I don't know what I should send to the support, I have to be discrete about the data I send out, since these data contains personal information of my clients.

After much deeper tracing and trial and error approach, these are the clues I gathered:

1) The application works fine if I used GB2312 charset, but will not work if I used GBK encoding. The problem is, GB2312 is not sufficient to display some less common Chinese characters.

2) The reason for the Error #42000, which I guessed, was that while the Binary data for the Blob field was handled correctly in GB2312 encoding, has been converted to some illegal character when using GBK encoding, or maybe the other way round -- Since GBK covers more characters, it has codes that exceeds a certain range, which could have became a certain illegal characters for the SQL statement (which was generated automatically by MyDAC components). I would call it a bug in the MyDAC components, since I could insert the pictures to the table without problem using phpMyAdmin.

So far, nothing from the officials? Or must I file an official request to get help??

[vga]

you can use anydac(you can find it in ramobject suite), it works fine.


ADCommand1.CommandText.Text := 'update test set pic_name=:pic_name, Image=:Image';
ADCommand1.ParamByName('pic_name').AsString := '錦';
ADCommand1.ParamByName('Image').LoadFromFile('boy.jpg', ftBlob);
ADCommand1.Execute();

[swierzbicki]

Geekman1024,

Devart Team is usually monitoring these forum. I've observed that they are really busy on each end of month (they are updating all their component during this short time).

Yoiu can submit a ticket here :
http://www.devart.com/company/contact.html

They have a pretty good support and they can give you a solution to your problem pretty quickly (from 2 days to 1 week, it depends how hard is your bug to reproduce / fixe).

You don't have to send them your whole database / table. Just recreate a test case with non personal data. I know it takes some times to create a sample project and a sample database but it really worth. Devart will narrow your bug down much faster with this reproducible test case.

Best regard,

Stephan

[Dimon]

We are investigating this problem. As soon as we solve the problem we will let you know.

[Dimon]

We have investigate this problem and fixed it. This fix will be included in the next MyDAC build.

[geekman1024]

When will be the release date of the next build? I'm waiting.

[Dimon]

The next MyDAC build will be released by the end of this week.

[geekman1024]

that's good to know, thanks for the reply.