Error in MyQuery

[thiagoalx]

Hi !

I have a error in MyQuery.
In MyQuery, i have the following SQL:

Code: Select all

select sf.id, sf.funcao, sf.solicitante, sf.motivo, sf.autorizacao, c.cargo, f.nome, m.motivo as motivo_nome, sf.situacao, sf.data_solicitacao, sf.prioridade, sf.anotacoes, sf.data_autorizacao, fs.nome as autorizacao_nome
 from solicitacao_funcionario sf
  left join cargo c on c.codigo=sf.funcao
  left join usuario u on u.funcionario=sf.solicitante
  left join funcionario f on f.id=u.funcionario
  left join motivo m on m.codigo=sf.motivo
  left join usuario us on us.funcionario=sf.autorizacao
  left join funcionario fs on fs.id=us.funcionario
   where sf.funcao like :funcao
   order by sf.id asc

And i generate SQL.
After post the record, i need to refresh only the current record.

MyQuery RefreshOptions:
roAfterInsert: True
roAfterUpdate: True

And my Application says: "Refresh failed. 0 Records Found";

I need help.
Thanks!

[DemetrionQ]

This error can occur due to several reasons:
1) an editing record was deleted by another user.
2) if you have set the SQLUpdate and SQLRefresh properties so that they access different tables, and you have tried to modify fields not included in the SQLUpdate query, then the error will occur when refreshing the record, since there occurs accessing to the table that isn't modified on the server and doesn't contain the record with the new field values.

To solve the problem, you should correctly set the SQLUpdate and SQLRefresh properties. For example, set the TMyQuery.SQLRefresh to this:

Code: Select all

    select sf.id, sf.funcao, sf.solicitante, sf.motivo, sf.autorizacao, c.cargo, f.nome, m.motivo as motivo_nome, sf.situacao, sf.data_solicitacao, sf.prioridade, sf.anotacoes, sf.data_autorizacao, fs.nome as autorizacao_nome
    from solicitacao_funcionario sf
      left join cargo c on c.codigo=sf.funcao
      left join usuario u on u.funcionario=sf.solicitante
      left join funcionario f on f.id=u.funcionario
      left join motivo m on m.codigo=sf.motivo
      left join usuario us on us.funcionario=sf.autorizacao
      left join funcionario fs on fs.id=us.funcionario
       where 
         sf.id = :id

If you don't set the SQLUpdate and SQLRefresh properties, you can specify the editing table in the TMyQuery.UpdatingTable property.

[thiagoalx]

I solved this problemn creating VIEW with this SQL

Code: Select all

CREATE VIEW vw_solicitacao_funcionario AS
(
select sf.id, sf.funcao, sf.solicitante, sf.motivo, sf.autorizacao, c.cargo, f.nome, m.motivo as motivo_nome, sf.situacao, sf.data_solicitacao, sf.prioridade, sf.anotacoes, sf.data_autorizacao, fs.nome as autorizacao_nome
from solicitacao_funcionario sf
  left join cargo c on c.codigo=sf.funcao
  left join usuario u on u.funcionario=sf.solicitante
  left join funcionario f on f.id=u.funcionario
  left join motivo m on m.codigo=sf.motivo
  left join usuario us on us.funcionario=sf.autorizacao
  left join funcionario fs on fs.id=us.funcionario
   order by sf.id asc
)

First, i use Generate SQL, but in the TMyQuery.SQLRefresh, I insert this view. It's works.

But i use this in other table, and now, show the same error:
Steps:

1) I insert the SQL

Code: Select all

select fs.*, m.motivo as motivo_nome
 from funcionario_salario fs
 left join motivo m on m.codigo = fs.motivo
 order by fs.data_salario asc

2) Execute and Generate SQL
3) Create VIEW

Code: Select all

CREATE VIEW vw_funcionario_salario as
(
select fs.*, m.motivo as motivo_nome
 from funcionario_salario fs
 left join motivo m on m.codigo = fs.motivo
 order by fs.data_salario asc
)

4) Put this VIEW in the TMyQuery.SQLRefresh

Code: Select all

select * from vw_funcionario_salario
where
id = :id

I have others tableas with the same steps, and it works.

The error show when i use Insert(TMyQuery.Append and after TMyQuery.Post).
If i use Update(TMyQuery.Edit and after TMyQuery.Post), don't show the error.

*I Use Auto Increment in column, in MySQL DB.

If in event AfterPost, i insert:

Code: Select all

procedure TdmFuncionarios.qrSalarioAfterPost(DataSet: TDataSet);
begin

  DataSet.Refresh;

end;

It works, but i don't want refresh Dataset, I want refresh the Record.

[AndreyZ]

The problem may occur because the value of the auto-increment field is not returned to the client. Try setting the ReturnParams option of the TMyQuery component to True. If it does not solve the problem, please try creating a small sample to demonstrate the problem and send it to andreyz*devart*com , including a script to create all needed server objects (tables, stored procedures, etc.).

[thiagoalx]

I change my database MySQL to PostgreSQL for other reasons.

And i have a same problem, and i create a topic, because now, i use UniDAC.

http://forums.devart.com/viewtopic.php?f=28&t=28024

Thanks !

[AndreyZ]

It is good to see that you have found the solution. If any other questions come up, please contact us.