Page 1 of 1
Update with UNION querys
Posted: Mon 07 Jan 2013 16:25
by CristianP
Hi,
I have a TUniQuery with:
SELECT A, B, 'Table2012' AS TableName FROM Table2012
UNION
SELECT A, B, 'Table2013' AS TableName FROM Table2013
Theoretically TUniQuery can do updates using TableName field (TUniQuery can insert automatically this field for internal use).
Is there a possibility to do this with TUniQuery?
Best Regards,
Cristian Peta
Re: Update with UNION querys
Posted: Wed 09 Jan 2013 10:59
by AlexP
Hello,
Please describe the required behavior in more details: the table fields you want to edit (provide table DDL), the data and the place where it should be inserted when using such field in a query, and the exact DB name you are working with.
Re: Update with UNION querys
Posted: Wed 09 Jan 2013 14:06
by CristianP
Hi,
Because there are many records I separate them per year. Keeping records together in same table and archiving is more complicate and this design is from more than ten years now and is not easy to change.
Now the user for about 1 month every January must change year back to see December.
I want now to show to the user last 1-3 months from today.
There are 47 fields of INTEGER, VARCHAR(), DATETIME and DECIMAL(10,2).
I need only VARCHAR to be editable.
I work with SQLite, MySQL, SQL Server and PostgreSQL.
The grid is read only and the user can edit fields in a panel for each record.
If I edit in a query with only selected record I need to do RefreshRecord for grid (UNION query). But I have tested and RefreshRecord do not work for UNION querys maybe because UniQuery do not know the source table of the record.
Best Regards,
Cristian Peta
Re: Update with UNION querys
Posted: Wed 09 Jan 2013 17:42
by CristianP
Hi,
I looked at UniDac sources and TCustomDADataSet have one TDADataSetService with one TDASQLGenerator with one TCRTableInfo with
one TableName.
And for TUniQuery with UNION to be editable there must be
more TableName....
In this case I tested this code and I think for me is OK regarding updates.
Refresh and RefreshRecord I will do by myself in same way.
Code: Select all
procedure TForm1.FormCreate(Sender: TObject);
begin
//Here I will filter only last months from 2012 and first from 2013
UniQuery1.SQL.Text :=
'SELECT A, B, ''Table2012'' as TableName from Table2012 ' +
'UNION ' +
'SELECT A, B, ''Table2013'' as TableName from Table2013';
UniQuery1.Open;
VirtualTable1.AfterPost := nil;
VirtualTable1.Assign(UniQuery1);
VirtualTable1.AfterPost := VirtualTable1AfterPost;
VirtualTable1.Open;
UniQuery1.Close;
VirtualTable1.FieldByName('TableName').Visible := False;
VirtualTable1.FieldByName('B').ReadOnly := False;
end;
procedure TForm1.VirtualTable1AfterPost(DataSet: TDataSet);
begin
//Here I will need to check and update only modified fields
UniConnection1.ExecSQLEx(
'UPDATE ' + DataSet.FieldByName('TableName').AsString +
' SET B=:B WHERE A=:A',
['B', DataSet.FieldByName('B').AsString, 'A', DataSet.FieldByName('A').AsInteger]);
end;
Best Regards,
Cristian Peta
Re: Update with UNION querys
Posted: Thu 10 Jan 2013 12:16
by CristianP
Hi,
I changed to this code and now is working like a dream.
Code: Select all
procedure TForm1.FormCreate(Sender: TObject);
begin
//Here I will filter only last months from 2012 and first from 2013
UniQuery1.SQL.Text :=
'SELECT A, B, ''Table2012'' as TableName from Table2012 ' +
'UNION ALL ' +
'SELECT A, B, ''Table2013'' as TableName from Table2013';
UniQuery1.SQLUpdate.Text := 'some text so I can turn ReadOnly to false';
UniQuery1.Open;
UniQuery1.FieldByName('TableName').Visible := False;
UniQuery1.FieldByName('B').ReadOnly := False;
end;
procedure TForm1.UniQuery1BeforePost(DataSet: TDataSet);
begin
//Here I will need to check and update only modified fields
UniQuery1.SQLUpdate.Text := 'UPDATE ' + DataSet.FieldByName('TableName').AsString + ' SET B=:B where A=:A';
end;
Best Regards,
Cristian Peta
Re: Update with UNION querys
Posted: Thu 10 Jan 2013 13:23
by AlexP
Hello,
Glad to see that you solved the problem. If you have any other questions, feel free to contact us