Page 1 of 1
Insert BlobField problem (was TMyConnection Charset problem)
Posted: Wed 25 Feb 2009 04:56
by 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?
Posted: Wed 25 Feb 2009 06:12
by 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
);
Posted: Wed 25 Feb 2009 06:38
by 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?
Posted: Wed 25 Feb 2009 10:21
by swierzbicki
Hi,
IMO, create a sample project + sample MySQL DB and send it to DevArt support. You should also send your MySQL ini file.
Posted: Thu 26 Feb 2009 05:45
by 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??
Posted: Thu 26 Feb 2009 06:15
by 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();
Posted: Thu 26 Feb 2009 08:03
by 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
Posted: Thu 26 Feb 2009 13:08
by Dimon
We are investigating this problem. As soon as we solve the problem we will let you know.
Posted: Fri 27 Feb 2009 10:07
by Dimon
We have investigate this problem and fixed it. This fix will be included in the next MyDAC build.
Posted: Tue 31 Mar 2009 05:55
by geekman1024
When will be the release date of the next build? I'm waiting.
Posted: Tue 31 Mar 2009 10:21
by Dimon
The next MyDAC build will be released by the end of this week.
Posted: Wed 01 Apr 2009 08:33
by geekman1024
that's good to know, thanks for the reply.
