Problems in the field editor with fields of the same name in two different tables

[Fr0sT]

Do it! I've noticed you did a nice simplification on my ugly code

Okay, I just didn't want to break your commit combo

Will it? Needs to be tested.

IDK, this quoting is used in ASA and DbLib only which I do not use...

And this job can't be made in atomic commits. Either all or nothing..

I'll remove the clone methods but complete omitting, AFAIU, is more serious change. I doubt UncachedGetColumns could ever be called with quoted identifiers but it is just my impression.

[EgonHugeist]

I doubt UncachedGetColumns could ever be called with quoted identifiers but it is just my impression.

No idea. JDBC may help but i don't think they expect quoted identifiers oslt. I propose to allow case sensitive patterns only.

EDIT

on other Drivers this is named like "NormalizePatternCase"

don't hassitate to start. I've a full server battery running for all supported protocols except SyBase+DBLib(but Jan can do this job AFAIK).

Let's wait for Jan..

[tkszeos]

Thanks so much, you are the best!!!

[EgonHugeist]

Thanks for the applause, tkszeos. :

Note, i've added a generic test for all supported protocols to get rid about behavior changes. If other issues arrive raise a ticket with a reproducable testcase. I'm not able to say how long this simple issue did persist or when it was(re?)introduced, but i'm sorry for this inconsitency.

[tkszeos]

Hi HegonHugeist,


after doing some tests unfortunately your changes did not solve the problem.
The strange thing is that if you invert the position of the fields in the SELECT the field type is correctly assigned (review my first post).

The problem occurs both in Delphi 7 and in CodeTyphon with all versions of Firebird.

I used zeoslib-code-0-4127-branches-testing-7.2

Thanks again

[EgonHugeist]

i added overrides of LoadColumns() for firebird, sqlite, mysql. These Drivers are able to return the fieldname and it's tablename so dermining metainformations should be easy for them.

Where is the issue comming from:
Note current code ist pretty abstract and try's to find the column the by it's fieldname or better fieldlabel. While playing with the stuff i was running into several traps:
1. FireBird automatically returns unique FieldLabels. They do not fit to the determined SelectSchema.

Code: Select all

SELECT
    LINGUE_PC."Arrivo",
    PREVHDR."Arrivo"
  FROM
    PREVHDR INNER JOIN
    LINGUE_PC ON (PREVHDR."Lingua" = LINGUE_PC."Codice")

returns all columnlabels as "Arrivo, Arrivo_1" in the native columninfos. Looking to your select which runs though a sectional splitted composer of zeos zeos can't know that second column will not match to the columninfo which was retrieved by firebird is named as Arrivo_1.
using:

Code: Select all

SELECT
    LINGUE_PC."Arrivo",
    PREVHDR."Arrivo" as "Arrivo_1" 
  FROM
    PREVHDR INNER JOIN
    LINGUE_PC ON (PREVHDR."Lingua" = LINGUE_PC."Codice")

would resolve your issue. That's what you did with your view.

2. Trap: Column Identifiers and it's labes are handled differently by supported RDBMS.
As stated in point 1 firebirds automatically returns unique columnlabels.
All other protocols have it's own behavior as well. So some of them are returning the programmers exact given columnlabel, which doesn't match the rules of (Quoted)Identifer quoting or dequoting rules.

3. MySQL and it's table-identifiers...

Finally it has nothing todo with quoted or unquoted identifiers.
could yout test my suggestion with the unique labels?

I would like to know how all do think about? In the past Jan did build in a OIDTablecache because of same reasons i think.
Everything is possible.

[tkszeos]

I have done some test:

In the PREVHDR table, the "Arrivo" field is defined as DATE.
In the LINGUE_PC table, the "Arrivo" field is defined as VARCHAR(20).

This select:

Code: Select all

SELECT
    PREVHDR."Arrivo",
    LINGUE_PC."Arrivo" AS "Arrivo_1" 
  FROM
    PREVHDR INNER JOIN
    LINGUE_PC ON (PREVHDR."Lingua" = LINGUE_PC."Codice")

in the field editor produces:
TZReadOnlyQueryArrivo: TDateField CORRECT
TZReadOnlyQueryArrivo_1: TDateField WRONG

This other select:

Code: Select all

SELECT
  PREVHDR."Arrivo" AS ARRIVO_P,
  LINGUE_PC."Arrivo" AS ARRIVO_L 
FROM
  PREVHDR INNER JOIN
  LINGUE_PC ON (PREVHDR."Lingua" = LINGUE_PC."Codice")

in the field editor produces:
TZReadOnlyQueryARRIVO_P: TDateField CORRECT
TZReadOnlyQueryARRIVO_L: TDateField WRONG

But this select

Code: Select all

SELECT
  LINGUE_PC."Arrivo",
  PREVHDR."Arrivo"
FROM
  PREVHDR INNER JOIN
  LINGUE_PC ON (PREVHDR."Lingua" = LINGUE_PC."Codice")

in the field editor produce:
TZReadOnlyQueryArrivo: TStringField CORRECT
TZReadOnlyQueryArrivo_1: TDateField CORRECT

Why?

[EgonHugeist]

Hi, could you please update and tell me if your issue continues? From my POV the problem should be resolved for FB/IB, MySQL, PostgreSQL, SQLite.
Currently i'm implenting a generic testcase to catch the little bugga for all protocols.

[tkszeos]

Yes yes yes!!!
I updated to zeoslib-code-0-4171-branches-testing-7.2 and confirm that the issue has been solved!
All cases in which the problem was present now behave correctly.

you very much

[EgonHugeist]

Hi, nice to here you're happy now. Current solution kills many loops and is faster than before.

But i'm still not happy because the issue isn't resolved in a generic way for drivers who do not return the true tablename and ColumnName.

Inbetween i know why the old code did fail:

Code: Select all

SELECT
    LINGUE_PC."Arrivo",
    PREVHDR."Arrivo"
  FROM
    PREVHDR INNER JOIN
    LINGUE_PC ON (PREVHDR."Lingua" = LINGUE_PC."Codice")

Firebird returns unique ColumnLabels which means second "Arrivo" field will be returned as "Arrivo_1". But our IZSelectSchema doesn't know anything about it.

going deeper to the code there is a Zeos homemade issue:

Code: Select all

function TZAbstractResultSetMetadata.GetColumnLabel(ColumnIndex: Integer): string;
var
  I, J, N: Integer;
  ColumnName: string;
  ColumnsInfo: TObjectList;
begin
  { Prepare unique column labels. }
  if FColumnsLabels = nil then
  begin
    ColumnsInfo := FResultSet.ColumnsInfo;
    FColumnsLabels := TStringList.Create;
    for I := 0 to ColumnsInfo.Count - 1 do
    begin
      N := 0;
      ColumnName := TZColumnInfo(ColumnsInfo[I]).ColumnLabel;
      for J := 0 to I - 1 do
        if TZColumnInfo(ColumnsInfo[J]).ColumnLabel = ColumnName then
          Inc(N);
      if ColumnName = '' then
        ColumnName := 'Column';
      if N > 0 then
        ColumnName := ColumnName + '_' + ZFastCode.IntToStr(N);
      FColumnsLabels.Add(ColumnName);
    end;
  end;

  Result := ColumnsLabels[ColumnIndex{$IFNDEF GENERIC_INDEX} - 1{$ENDIF}];
end;

Here Zeos makes the same for all columns which are not unique. Note only FB has this unique col_label behavior. Thinking about the ISelectSchema which try's to find the field by alias first, but does not know anything about then homemade/FB produced issue ... It must fail do determine the TZTableRef for the currentfield randomly.

EDIT:
And PostgresSQL does not follow the the identifier rules for Alias labels.

So the IZSelectSchema needs simply add this behavior. Then the Column will allways be found for all drivers. A simple but evil missing point in current code...

[tkszeos]