Field Renaming on Joins using TSQLQuery
lazarus / 16-Databases

----------------------------------------------------------------------
jsonnabend · 2015-11-12 21:08

I'm brand new to Lazarus after years (and years) of Delphi
programming. I'm porting some database code using TSQLQuery in
Lazarus.

I am doing a join where the joined tables have fields with the same
name.  In Delphi, in the join, conflicting fields would be named
TABLENAME.FIELDNAME.  In Lazarus, the first field is FIELDNAME, then
next FIELDNAME_1, etc.

Is there any way to tell TSQLQuery to use the TABLENAME.FIELDNAME
convention?

TIA

----------------------------------------------------------------------
rvk · 2015-11-12 21:21

In Delphi the conflicting fields where also named field, field1 etc.
It's not Delphi which names the fields but you underlying database.

I know Firebird returns field, field1 etc. Maybe you used another
database in Delphi which does return table-names in front of the
fields?

You can name the fields in your query with the AS-keyword.
And within the join itself you can use TABLENAME.FIELD in the WHERE
and SELECT parts.

If there is a way to use other naming-convention you should tell the
database if it has that option. Once the fields are returned to
FPC/Lazarus/Delphi it can't know what they are. (For example
FPC/Lazarus/Delphi doesn't even know to which table field or field1
belongs.)

----------------------------------------------------------------------
jsonnabend · 2015-11-12 21:28

rvk -

Thanks for the info.  The backend is MS Access in both instances.  Not
sure what changed between the two dialects.  I was hoping not to have
to use "AS" clauses, but it looks like that's the solution.

- Jeff

----------------------------------------------------------------------
rvk · 2015-11-12 21:42

Quote from: jsonnabend on November 12, 2015, 10:28:12 pmThe backend is
MS Access in both instances.
Maybe there is an option in the database-connector which you can set
to receive full table-names. The connector would issue the request to
the database which will in turn return the full name.

(not sure what that option would be though)

What database-component are you using for the connect in Lazarus? And
what were you using in Delphi?

----------------------------------------------------------------------
jsonnabend · 2015-11-12 21:44

I'm using TSQLQuery,  TSQLTransaction and TODBCConnection

----------------------------------------------------------------------
LacaK · 2015-11-13 08:09

Quote from: jsonnabend on November 12, 2015, 10:08:35 pmIs there any
way to tell TSQLQuery to use the TABLENAME.FIELDNAME convention?
No. At "Lazarus/FreePascal side" it is not possible.

----------------------------------------------------------------------
rvk · 2015-11-13 13:02

I was wrong

With the ODBC-connection and MS Access, FPC does get the duplicate
fieldnames from the connection.
In Delphi there is more done to determine what table this field
belongs to (which is possible with ODBC).
But FPC just adds _1. You can see this in the following snippets:

in fpc\packages\fcl-db\src\sqldb\odbc\odbcconn.pas
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i     // add
FieldDef    with FieldDefs.Add(FieldDefs.MakeNameUnique(ColName),
FieldType, FieldSize, (Nullable=SQL_NO_NULLS) and
(AutoIncAttr=SQL_FALSE), i) do    begin      if Updatable =
SQL_ATTR_READONLY then Attributes := Attributes + [faReadonly];   
end;and in fpc\packages\fcl-db\src\base\fields.inc
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i function
TFieldDefs.MakeNameUnique(const AName: String): string;var
DblFieldCount : integer;begin  DblFieldCount := 0;  Result := AName; 
while assigned(inherited Find(Result)) do    begin   
inc(DblFieldCount);    Result := AName + '_' +
IntToStr(DblFieldCount);    end;end;
So if you would like the complete table.field names you would need to
adjust the source at that point, retrieving the info about a
field/column and setting the name accordingly.

Other databases, like Firebird, never give duplicate fieldnames in a
select and already add 1, 2 etc to the returned fields (which you can
override with AS-keyword).