Question about returned the data by the query

[warcan]

Hi,
I use Delphi7+Zeos 7.
I have some query including construction - concat(f1," ",f2) as F , where f1 or f2 field by date or time. After query opening in ZQuery, field F have type - bytes. Version late 7.0 return type - string. When f1 and f2 not date or time return type - string. It is correct operation of ZQuery?

[EgonHugeist]

Which Database are you using?
Something to reproduce would be nice too!

[warcan]

I am use MySQL 4.1.16

Query - select concat(f1," ",f2) as A, f1,f2 from t1
Table have 1 row
f1 - 0000-00-00 - type DATE
f2 - 0000-00-00 - type VARCHAR

In manual by concat "...If the arguments include any binary strings, the result is a binary string...."

[warcan]

nil

[EgonHugeist]

Shouldn't you convert the DateTime-Value to a string and than do your concatations?

something like

Code: Select all

 select concat(Cast(f1 as varchar(10))," ",f2) as A, f1,f2 from t1

[warcan]

I'm not looking for a solution, I wonder, which Zeos version works correctly v.6.6.6 or v.7?
The thing is that in User Manual by mysql it is stated:

• Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string form;

After the returned query data is modified in Zeos 7, I will have to fix all my projects. It is not a problem, but if zeos v.6.6.6 works correctly I'd prefer using it and avoid the necessity to fix my projects. Thus, in that case it will be better for me to use v.6.6.6 without upgrade to v. 7

[EgonHugeist]

You are running into a known old MySQL issue..
Just see http://jondowdle.com/mysql-concat-and-mixed-types/
There are more threads to find if you simply google the issue.

I did check your exmple with MySQL5.6 and i got the results you expect:

Code: Select all

//http://zeoslib.sourceforge.net/viewtopic.php?f=38&t=18791
procedure TZTestCompMySQLBugReport.Test38_18791;
var
  Query: TZQuery;
begin
  Query := CreateQuery;
  Query.SQL.Text := 'select concat(p_begin_work," ",p_name) as A, p_begin_work,p_name from people';
  try
    Query.Open;
    CheckStringFieldType(Query.Fields[0].DataType, Query.Connection.DbcConnection.GetConSettings);
    CheckEquals('09:00:00 Vasia Pupkin', Query.Fields[0].AsString);
  finally
    Query.Free;
  end;
end;

I remember a MySQL side which did describe the fix.. But i don't know the URL any more

So 6.6.6 was wrong and 7up is fixed for binary fields (which simply was missing on 6.6.6 AFAIK)

[warcan]

Thank you very much for your reply and your time. I'll have to recheck all query for correctness display by using zeos v7.

[EgonHugeist]

Or you update MySQL and the bug is fixed..

[warcan]

A lot of thanks for the advice.