ADODataSet Delphi Edit

2

I have ADODataSet with the following query

DECLARE @ID_CONTRATO_EMPRESARIAL INTEGER = :ID_CONTRATO_EMPRESARIAL
BEGIN
WITH V1 AS (
select C.ID_CONTRATO, 
    COUNT(CASE AD.FUNCIONARIO WHEN 'TRUE' THEN 1 ELSE NULL END) FUNCIONARIOS,
    COUNT(*) MEMBROS
FROM CONTRATO C, ADESAO AD
WHERE C.ID_CONTRATO_EMPRESARIAL = @ID_CONTRATO_EMPRESARIAL  
    AND C.ID_CONTRATO = AD.ID_CONTRATO
GROUP BY C.ID_CONTRATO, C.NUMERO, C.VALOR_CLIENTE)
SELECT 
    V1.FUNCIONARIOS, V1.MEMBROS,
    PT.ID_PESSOA AS ID_PESSOA_TITULAR, PT.NOME AS NOME_TITULAR,
    C.*
FROM V1, CONTRATO C, PESSOA PT
WHERE V1.ID_CONTRATO = C.ID_CONTRATO
    AND C.ID_PESSOA = PT.ID_PESSOA
END

I would like to know if I can give a Edit in this DataSet and save the changed column in the database.

Basically what I'm trying to do is the following

    dmContratoEmpresarial.cdsContratoEmpresarialContratos.Edit;
    dmContratoEmpresarial.cdsContratoEmpresarialContratosVALOR_TI.Value := 1000;
    dmFramework.Post(dmContratoEmpresarial.cdsContratoEmpresarialContratos);

Where the VALOR_TI column is in the CONTRATO table. But when checking the bank the column has not changed.

    
asked by anonymous 24.05.2016 / 15:06

1 answer

1

Standard Approach

The general practice is: when you want to edit the result of a query involving multiple tables and update the database, the only records in the table to edit are retrieved in a separate query.

That is, the simplest would be you get in this complex query the record id that you need to edit, and then get this record to edit in another simple query and edit it in another dataset, or even do a SQL command update directly on the registry.

An option

On the other hand, an ADO query offers the property Unique Table , where you can specify the table on which insert , delete , and update operations will apply when the result of the query was obtained from more than one table. More or less like this:

ADOQuery.Properties['Unique Table'].Value := 'CONTRATO';

However, since your query is quite complex, it is possible that ADO will not be able to resolve it even with this set property; hence the standard approach.

    
24.05.2016 / 15:53