BLOB size different. With FireDAC ok but not with MyDAC

[verhijden]

I have the following issue. When I load a JPG file of 25 kB into a LARGEBLOB ( MySQL 5.5.54 ) with .LoadFromFile() and 'immediatly' do a .SaveToFile() the file is 39 kB in size. And not readable. But I tried this on exactly the same place in code with FireDAC then the storage of the BLOB is okay. But not with MyDAC. Just dropped a TMyQuery on the form and the TMyConnection is also standard, just set the character set to LATIN1 ( this is the standard of MYSQL ), but i tried also different character sets. I tried options like compression on or off ( now is as I told you ) default. No data mappings used. But FireDAC is okay and MyDAC not....

I am using XE6, and I am not a newbie, 15 years Delphi experience.

The code ( and there is no code between!!!). The in.jpg is 25Kb and the out.jpg is 39kB


FDQuery1.Insert;
FDQuery1LogoBedrijf.LoadFromFile('C:\Users\emile\Desktop\perifact\Compiled FULL\in.jpg');
FDQuery1.Post;

FDQuery1.Close;
FDQuery1.Open;
FDQuery1LogoBedrijf.SaveToFile('C:\Users\emile\Desktop\perifact\Compiled FULL\out.jpg');


The table definition is:

CREATE TABLE `reportsettings` (
`PrintLayout` INT(11) NOT NULL,
`LogoBedrijfStretch` TINYINT(1) NOT NULL,
`LogoExtraStretch` TINYINT(1) NOT NULL,
`LogoFooterStretch` TINYINT(1) NOT NULL,
`LogoHeigthBedrijf` INT(11) NOT NULL,
`LogoHeigthExtra` INT(11) NOT NULL,
`LogoHeigthFooter` INT(11) NOT NULL,
`LogoLeftBedrijf` INT(11) NOT NULL,
`LogoLeftExtra` INT(11) NOT NULL,
`LogoLeftFooter` INT(11) NOT NULL,
`LogoTopBedrijf` INT(11) NOT NULL,
`LogoTopExtra` INT(11) NOT NULL,
`LogoTopFooter` INT(11) NOT NULL,
`LogoWidthBedrijf` INT(11) NOT NULL,
`LogoWidthExtra` INT(11) NOT NULL,
`LogoWidthFooter` INT(11) NOT NULL,
`LayoutPresentation` INT(11) NOT NULL,
`LabelSumTekstTotaalVanFactuurIncBTW` VARCHAR(250) NOT NULL,
`LabelSumTekstFactuurBtwBedrag` VARCHAR(250) NOT NULL,
`LabelSumTekstTotaalVanFactuurExcBTW` VARCHAR(250) NOT NULL,
`LabelSumTekstTotaalVanOfferteIncBTW` VARCHAR(250) NOT NULL,
`LabelSumTekstOfferteBtwBedrag` VARCHAR(250) NOT NULL,
`LabelSumTekstTotaalVanOfferteExcBTW` VARCHAR(250) NOT NULL,
`TekstFactuurStatusOpen` VARCHAR(4096) NOT NULL,
`TekstFactuurStatusBetaald` VARCHAR(4096) NOT NULL,
`PrintAreaBedrijf1` BLOB NULL,
`PrintAreaBedrijf2` BLOB NULL,
`PrintAreaBedrijf3` BLOB NULL,
`PrintAreaBedrijf4` BLOB NULL,
`TekstFactuurStatusCreditFactuur` VARCHAR(4096) NOT NULL,
`DefaultFactuurVoetregel` VARCHAR(4096) NOT NULL,
`DefaultOfferteVoetregel` VARCHAR(4096) NOT NULL,
`LogoBedrijf` LONGBLOB NOT NULL,
`LogoExtra` LONGBLOB NOT NULL,
`LogoFooter` LONGBLOB NOT NULL
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

[ViktorV]

Unfortunately, we could not reproduce the issue.
In order to get a detailed answer, please compose a small sample demonstrating the described behavior and send it using the contact form https://www.devart.com/company/contactform.html including scripts for creating database objects.

[verhijden]

Hi!

I could not reproduce it in a small test application, I tried that right away in the beginning. But in my large app with approx. 40 datamodules and lots of forms there this issue occurs. So it's not easy to send you a ZIP with the 'problem program'. My work around is to use FireDAC again.....

[ViktorV]

As we wrote earlier, unfortunately, we could not reproduce the issue. To understand the issue cause and find a solution to fix it, we need a test sample in which the issue is stably reproduced. As soon as we receive this sample and if the cause is in our product code, we will try to fix it as soon as possible.
Note, if the sample has a large size, please place it on free filehosting and send us the link using the contact form https://www.devart.com/company/contactform.html

[verhijden]

Hi! Do you also support TeamViewer sessions? I refactored the whole project back to FireDac. And to be honest I like your product ( and I paid for it), so I gave it again a try. And started with the blobs, reading and writing was okay. I am not sure but I use approx. 100+ TDatasets. And it was working fine, until at almost the end stage there was the issue back again. And as I told you.... just manually replacing the FireDac components to MyDac, not with a tool like GExperts or someting. What I also did is do some cleanup of the FireDac 'uses' in every datamodule and form. Not more not less. Is there a dependency with a FireDac class?

I found on the internet in one of your forums a 'simular' issue, viewtopic.php?f=7&t=30794 , well not exactly, but the input in that case was also different than the output. And yes, I also tried to set the property 'SaveBlobsAsHex' or 'BinaryAsString' and combinations of it. My database is now UTF8. Is there a other (property) dependency for storing blobs? Remote support would be fine to show you the problem.

[ViktorV]

Using TeamViewer we will be able only to see that the problem might exist, but cannot understand its cause.
We still cannot confirm that random error cause in your app is MyDAC code. You are the only user who contacted us with this issue. To understand the issue cause we need test sample where the issue is stably reproduced.

[verhijden]

4 months later...an update.... I am using now MyDac 9.02, and a bit(!) happier.

Today I refactored again all FireDac datasets to the latest MyDAC, it took me 6 hours due to a lot of datasets. And yes, a different behaviour. Storing and reading back a .JPG in a blob was now working fine

So far so good. The character set is UTF8 by the way. But I noticed the some special characters in the database where not correctly stored, like the euro sign €. Hmmm, solution; turn on the property 'UseUnicode' of the TMyConnection component. And all special characters are also stored well. As a side affect al datasets will need to change the TStringField to a TWideStringField, but thats fine, I changed them all.

But...... now is the old bug ( or behaviour ) back again. Storing a JPG into a blob will corrupt me that JPG file. The outputsize is different then I put in. The info is altered. And the info stored in the blob is not usable anymore. All other properties are untouched of the TMyConnection component. Except for username/password/login prompt.

What can this be?!?!?!?! What is UseUniCode doing with a blob?!?!

Greetz,
Emile

[verhijden]

I debugged a bit more. When I excecute this code and then go afterwards to the filesystem via the Windows Explorer. Then 'out1.jpg' is readable, and 'out2.jpg' not..... ( By the way, there is always 1 record, so I am not querying a wrong record )

Correction on the reply above, file size is the same. But there is an issue with turning on 'UseUnicode'


with qryLogoTop do
begin
Close;
Open;
if EOF then
begin
Insert;
end else begin
Edit;
end;
TBlobField(FieldByName(fieldname)).LoadFromFile(fullfilename);
Post;
TBlobField(FieldByName(fieldname)).SaveToFile('out1.jpg');
Close;
Open;
TBlobField(FieldByName(fieldname)).SaveToFile('out2.jpg');
Close;
end;

[ViktorV]

Unfortunately, we cannot reproduce this issue even with the new data provided by you. Therefore, to investigate MyDAC behavior, please create and send us a small sample demonstrating the issue and provide a test access to your server.

[verhijden]

I have a working solution, I did set the charset of the TMyConnection to 'utf8mb4' and now it works.

Happy? Yes! But, I do not understand a thing. BLOB means binary large object. What has a character set to do with a binary thing?

[verhijden]

I am still curious for the question above

In your documentation is a wrong description:
https://www.devart.com/mydac/docs/?deva ... harset.htm
it says: If the UseUnicode property is set, the Charset property will be ignored. thats not true in my case.

[ViktorV]

We are glad to see you have found a solution.
As we wrote earlier, we could not reproduce the issue. It occurs as a result of some specific conditions that we do not know, and, unfortunately, the sent log does not provide us with the information about these specific conditions. Therefore, we are waiting for the sample, reproducing the issue, for its investigation.

[verhijden]

I wish I could send you a sample. But the project is very large and uses 3th party libraries (that you probably don't have).

By the way. I am not criticizing your product. I love it. And just wanted to have a solution, which I have now, and help to make your product better.

Thanks!
Emile

[ViktorV]

Thank you for interest to our product and your contribution in our product development.
We are constantly working on our products performance improvement to meet our users' needs.
We will be very grateful if you could compose and send us a sample reproducing the issue without using third party components and if the issue cause is in our product code, we will fix it in the shortest possible time.