Update with UNION querys

[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

[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.

[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

[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

[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

[AlexP]

Hello,

Glad to see that you solved the problem. If you have any other questions, feel free to contact us