Conflit of FieldsAsString between Unicode and Blob

[stlcours]

Mysql 5.0.24
Delphi XE
Unidac 5.1.4

CREATE TABLE `pub_page` (
`ex_id` INT(11) NOT NULL AUTO_INCREMENT,
`ex_name` VARCHAR(255) NULL DEFAULT NULL,
`ex_blob` LONGBLOB NULL,
`ex_desc` VARCHAR(5000) NULL DEFAULT '',
PRIMARY KEY (`ex_id`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
AUTO_INCREMENT=1;


with FConnection do
begin
ProviderName:='MySQL';
SpecificOptions.Values['UseUnicode'] := 'True';
end;


QueryResult := TUniQuery.Create(Owner);
QueryResult.Connection:=FConnection;
QueryResult.ReadOnly:=false;
with QueryResult do
begin
SpecificOptions.Values['MySQL.FetchAll'] := 'True';
// SpecificOptions.Values['BinaryAsString'] := 'True'; // if I just write this instruction, then Error：malformed trail byte or out of range char
SpecificOptions.Values['FieldsAsString'] := 'True'; // if I just write this instruction, then BLOB is good, but String is not Unicode now!!!
// I find the reference:
// 1. If the FieldsAsString option is True, all fields except BLOB and TEXT fields are mapped to ftString
// 2. All non-BLOB fields are stored as string (native MySQL format???).
// SpecificOptions.Values['RawAsString'] := 'True'; // none of use ??? What exactly for RawAsString
// I don't understand: If True, all RAW fields are treated as being of string datatype, e.g. represented as hexadecimal string.
SQL.Clear;
SQL.Add('SELECT * FROM pub_page');
Execute;
end;

How I can show the unicode characters correctly but BLOB is not mapped to string? FieldsAsString is a good parameter, but I mapped the other fields to mysql native string? It is not correct! Thanks for you help.

On the other hand, please answer me another question:
http://forums.devart.com/viewtopic.php?f=28&t=28620
Thanks again.

[AlexP]

Hello,

1) MySQLUniProvider does not support RawAsString setting. This setting is supported by OracleUniProvider.
2) You can use the DataTypeMapping feature to convert types of particular fields. Here is an example of using DataTypeMapping.

Code: Select all

var
  UniConnection: TUniConnection;
  UniQuery: TUniQuery;
  i : integer;
begin
  UniConnection := TUniConnection.Create(nil);
  try
    UniConnection.ConnectString := 'ProviderName=MySQL;User Id=root;Pwd=root;Host=db;Port=3310;Database=andrz';
    UniConnection.SpecificOptions.Values['UseUnicode'] := 'True';
    UniConnection.Connect;
    UniQuery := TUniQuery.Create(nil);
    try
      UniQuery.Connection:= UniConnection;
      UniQuery.ReadOnly:=false;

      UniQuery.SpecificOptions.Values['FetchAll'] := 'True';
      UniQuery.DataTypeMap.AddFieldNameRule('ex_name', ftWideString, true);
      UniQuery.DataTypeMap.AddFieldNameRule('ex_blob', ftWideString, true);
      UniQuery.DataTypeMap.AddFieldNameRule('ex_desc', ftWideString, true);
      UniQuery.SQL.Clear;
      UniQuery.SQL.Add('SELECT * FROM pub_page');
      UniQuery.Open;
      for I := 0 to UniQuery.FieldCount - 1 do begin
        ShowMessage(UniQuery.Fields[i].AsString);
      end;
    finally
      UniQuery.Free;
    end;
  finally
    UniConnection.Free;
  end;
end;

If you encounter any error when using such code, contact us again.

[stlcours]

Thank for your reply, especially in Sunday.

I continue to report the results of my tests.

1. SpecificOptions.Values['FieldsAsString'] := 'True'; // this is always required, even you use whatever DataTypeMap

2. QueryResult.DataTypeMap.AddFieldNameRule('ex_blob', ftWideString, true); // is not correct. then Error：malformed trail byte or out of range char

3. QueryResult.DataTypeMap.AddFieldNameRule('ex_blob', ftBlob, true); // is not correct, then error: unsupported field type: 15, but why???

4. only with these instrutions:
UniConnection.SpecificOptions.Values['UseUnicode'] := 'True';
UniQuery.DataTypeMap.AddFieldNameRule('ex_name', ftWideString, true);
// UniQuery.DataTypeMap.AddFieldNameRule('ex_blob', ftWideString, true); // don't use it. UniQuery.DataTypeMap.AddFieldNameRule('ex_desc', ftWideString, true);
UniQuery.SpecificOptions.Values['FieldsAsString'] := 'True';

is correct. But it is not a good idea to always use the DataTypeMap. Because sometime we cannot know how many fields or which fields of the SQL query will show(I have really deleted many other fields to show you this question). I want the other fields will be changed to String(FieldsAsString), but Blob should remain it's data without transform.

I think it is a normal request, you and the other clients haven't encounter this problem? There are two slutions:
1. When we use FieldsAsString=True, the blob/ftOraBlob/ftOraClob/ftGraphic(maybe not just blob), also maybe ftBytes/ftVariant, always contain its original data. But all non-BLOB fields should be stored as WideString if SpecificOptions.Values['UseUnicode'] = 'True'. You can add a additional option to UniDAC in next version to let the developer decide if he wants FieldsAsString as AnsiString or as WideString.

2. Don't need use the option FieldsAsString , but please resolve the error "malformed trail byte or out of range char" when FieldsAsString=false. Maybe you transformer the blob to Something others. It is wrong. The Blob should always contain its original data whatever happens.

[AlexP]

Hello,

1) No, when using DataTypeMapping, there is no need to enable this option.
2) This error occurs in the case when a BLOB field contain data, that cannot be mapped to WideString.
3) Conversion to BLOB is not supported in DataTypeMapping.
4) you can set DataTypeMapping not only by field names, but by their types in the DB as well. You can find an example in the MyDAC documentation: http://www.devart.com/mydac/docs/data_type_mapping.htm

[stlcours]

OK. DataTypeMapping works. But I still think it is not a good solution, because it just work to fix the "bug". When you are free, please think about my two solutions:

1. All non-BLOB fields should be stored as WideString if SpecificOptions.Values['UseUnicode'] = 'True'.
(Now I just use DataTypeMapping to solve this problem)
2. Please resolve the error "malformed trail byte or out of range char" when FieldsAsString=false.
(Maybe you transformer the blob to Something others. It is wrong)

Thanks again.

[stlcours]

Problem again!

When I use:
SpecificOptions.Values['FieldsAsString'] := 'True';
Then the query result of "date" is error. Exemple: 2014-01-15 only show as '2'. If I don't use FieldsAsString=true, then the date is correct!

[AlexP]

Hello,


1) We cannot modify the behavior when using the FieldsAsString option for all the fields to be mapped as WideString, since this will lead to errors in already existing projects of our users. If you have the UniDAC sources, we can tell the modifications required for such behavior.
2) As I wrote earlier, when attempting to map Blob data to String, if the data contains unsupported characters, you will get this error. To solve the problem, you needn't to map Blob (which can contain such data) to String
3) We cannot reproduce the problem. When using FieldsAsString, Date is displayed correctly. Please provide the script for creating the table.

[stlcours]

1. Yes. You cann't change the behavior of 'FieldsAsString' because of history. But maybe this is acceptable:
if FFieldsAsString and
not (pField.MyType in
[FIELD_TYPE_GEOMETRY,
FIELD_TYPE_TINY_BLOB, // TINYBLOB, TINYTEXT (may be BLOB or TEXT!)
FIELD_TYPE_BLOB, // BLOB, TEXT
FIELD_TYPE_MEDIUM_BLOB, // MEDIUMBLOB, MEDIUMTEXT
FIELD_TYPE_LONG_BLOB] // LONGBLOB, LONGTEXT
) then
if UseUnicode then InternalType := dtWideString
else InternalType := dtString;
I modified the souce code but it doesn't work. Please give me your modifications required for such behavior if you cann't modified the souce code of official version.


2. OK. I understand now that Blob cann't map to String(So BinaryAsString is nothing of use? Probably Blob has so many unknown characters). But why if
SpecificOptions.Values['BinaryAsString'] := 'False';
then nothing was got with my instructions, but without runtime error. Why? Code like this:
var
QueryResult: TUniQuery;
begin
QueryResult := TUniQuery.Create(Owner);
QueryResult.Connection:=UniConnection1;
with QueryResult do
begin
SpecificOptions.Values['MySQL.FetchAll'] := 'True';
SpecificOptions.Values['BinaryAsString'] := 'False';
SQL.Clear;
SQL.Add('Select * from pub_page');
Execute;
end;
end;


3. I reproduce the problem in a test project, simplely with instructions:
procedure TForm3.Button1Click(Sender: TObject);
var
QueryResult: TUniQuery;
begin
QueryResult := TUniQuery.Create(Owner);
QueryResult.Connection:=UniConnection1;
UniDataSource1.DataSet:=QueryResult; // UniDataSource1 link to a simple dbgrid

with QueryResult do
begin
SpecificOptions.Values['MySQL.FetchAll'] := 'True';
SpecificOptions.Values['FieldsAsString'] := 'True';
SQL.Clear;
SQL.Add('SELECT * FROM pub_page');
Execute;
end
end;

without runtime error, but the date '2014-01-15' was shown only as '2'.

This is my script for all of my questions above:
CREATE TABLE `pub_page` (
`ex_id` INT(11) NOT NULL AUTO_INCREMENT,
`ex_date` DATE NOT NULL DEFAULT '0000-00-00',
`ex_time` TIME NOT NULL DEFAULT '00:00:00',
`ex_public` DATE NOT NULL DEFAULT '0000-00-00',
`ex_journal` VARCHAR(30) NOT NULL DEFAULT '',
`ex_page` VARCHAR(30) NOT NULL DEFAULT '',
`ex_operateur` VARCHAR(30) NULL DEFAULT '',
`ex_blob` LONGBLOB NULL,
`ex_name` VARCHAR(255) NULL DEFAULT NULL,
`ex_desc` VARCHAR(5000) NULL DEFAULT '',
PRIMARY KEY (`ex_id`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
AUTO_INCREMENT=49;

if you cannot reproduce this case, I will send you my project and the script with data.
Waiting for you reply. Thousand of thanks!

[AlexP]

Hello,

1) You have added correct changes, however, these changes will work in run-time only. In order for the changes to be applied in design-time as well, you should delete UniDAC, rebuild packages, and install UniDAC from the built packages.
2) No error occurs, because there is no attempt to convert BLOB to string.
3) The provided sample displays correct values in the grid. Please send the project to alexp*devart*com

[stlcours]

1. I will continue to test.
2. You have not understood this question. It is not the problem of runtime error. it is:
1). BinaryAsString is nothing of use? Probably Blob has so many unknown characters if we use ['BinaryAsString'] := 'True';
2). When ['BinaryAsString'] := 'False'; there are nothing shown in dbgrid. This is a error. Use the database that I have sent to you to test, please. Otherwise, I will send you a case.
3. Already Sent to you.

[AlexP]

Hello,

I have received your letters - I will inform you as soon as we have any results.

[stlcours]

any news? Thanks...

[AlexP]

I have got received your sample, and there are no errors on the latest UniDAC version, Delphi XE and MySQL 5.7.2, independently on the BinaryAsString option value. Data in the ex_date and ex_public fields are displayed correctly (no cut) as well.

[stlcours]

Very good. I have test four machines. This bug is only in Unidac 5.1.4, not in Unidac 5.2.5.

But I have still problem with Blob, I will test again and come back. Thanks.