Hi,
I'm trying to use live bindings and TAureliusDataset to populate a third party listview. I have two SQLite tables (Pupil and Address) and which, together, will form the data which I need to populate the listview.
I believe that I need a view for this to work, so I manually created a class mapping representing the combined data items :
unit PupilAddressViewMapping;
interface
uses
SysUtils,
Generics.Collections,
Aurelius.Mapping.Attributes,
Aurelius.Types.Blob,
Aurelius.Types.DynamicProperties,
Aurelius.Types.Nullable,
Aurelius.Types.Proxy;
type
TPupilAddress = class;
[Entity]
[Table('PupilAddress')]
[Id('Ffullnameref', TIdGenerator.None)]
TPupilAddress = class
private
[Column('fullnameref', [], 10)]
Ffullnameref: string;
[Column('fullname', [], 50)]
Ffullname: string;
[Column('licencetype', [], 10)]
Flicencetype: string;
[Column('datetheorypassed', [])]
Fdatetheorypassed: Nullable<TDateTime>;
[Column('street', [], 40)]
Fstreet: Nullable<string>;
[Column('town', [], 40)]
Ftown: Nullable<string>;
[Column('county', [], 40)]
Fcounty: Nullable<string>;
[Column('postcode', [], 10)]
Fpostcode: Nullable<string>;
[Column('type', [], 1)]
Ftype_: Nullable<string>;
[Column('email', [], 100)]
Femail: Nullable<string>;
[Column('mobilenumber', [], 15)]
Fmobilenumber: Nullable<string>;
[Column('homenumber', [], 15)]
Fhomenumber: Nullable<string>;
[Column('photo', [TColumnProp.Lazy])]
Fphoto: TBlob;
public
property fullnameref: string read Ffullnameref write Ffullnameref;
property fullname: string read Ffullname write Ffullname;
property licencetype: string read Flicencetype write Flicencetype;
property datetheorypassed: Nullable<TDateTime> read Fdatetheorypassed write Fdatetheorypassed;
property street: Nullable<string> read Fstreet write Fstreet;
property town: Nullable<string> read Ftown write Ftown;
property county: Nullable<string> read Fcounty write Fcounty;
property postcode: Nullable<string> read Fpostcode write Fpostcode;
property type_: Nullable<string> read Ftype_ write Ftype_;
property email: Nullable<string> read Femail write Femail;
property mobilenumber: Nullable<string> read Fmobilenumber write Fmobilenumber;
property homenumber: Nullable<string> read Fhomenumber write Fhomenumber;
property photo: TBlob read Fphoto write Fphoto;
end;
implementation
initialization
RegisterEntity(TPupilAddress);
finalization
end.
I also created the view (using SQLExpert) and saved it in the database:
CREATE VIEW [PupilAddress]
AS
SELECT
[main].[Pupil].[fullname],
[main].[Pupil].[licencetype],
[main].[Pupil].[datetheorypassed],
[main].[Address].[street],
[main].[Address].[town],
[main].[Address].[county],
[main].[Address].[postcode],
[main].[Address].[email],
[main].[Address].[mobilenumber],
[main].[Address].[homenumber]
FROM [main].[Pupil]
INNER JOIN [main].[Address] ON [main].[Address].[fullnameref] = [main].[Pupil].[fullnameref]
If I execute this as a query against the database (using SQLiteExpert) it returns the data I'm expecting.
I coded the following to execute the view from my app
procedure TPupils.Enter( FromView: TFormView );
var
AllAddresses :TList<TPupilAddress>;
AllPupilsCriteria : TCriteria<TPupilAddress>;
DatabaseFile : String;
begin
// Show the current list of pupils
DatabaseFile := ViewHost.AppData.MainDatabaseFile;
DB_Conn := TSQLiteNativeConnectionAdapter.Create(DatabaseFile);
DB_Manager := TObjectManager.Create(DB_Conn);
AllAddresses := TList<TPupilAddress>.Create;
AllPupilsCriteria := DB_Manager.Find<TPupilAddress>;
AllAddresses := AllPupilsCriteria.List;
PupilsDataset.SetSourceList(AllAddresses);
end;
However when I run it I get the following error:
'Error: ambiguous column name: main.Address.street
SELECT A.fullnameref AS A_fullnameref, A.fullname AS A_fullname, A.licencetype AS A_licencetype, A.datetheorypassed AS A_datetheorypassed, A.street AS A_street, A.town AS A_town, A.county AS A_county, A.postcode AS A_postcode, A.type AS A_type, A.email AS A_email, A.mobilenumber AS A_mobilenumber, A.homenumber AS A_homenumber
FROM PupilAddress A'.
I confess databases are not my strong points so any pointers would be very welcome
Thanks
Anthony