EUpdateError in ApplyUpdates method
lazarus / 16-Databases

----------------------------------------------------------------------
nightrider · 2012-01-31 23:19

Sometime ago I avoided to use sequences Insert/Post and Edit/Post
'cause i always receive errors in the subseqente AppyUpdates method
execution.

Suddenly(at least it seems) these sequences began to run ok and I
forgot the trouble.

Today the story repeat. I have a sample program that uses Edit/Post
and I receive error EUpdateError in the ApplyUpdates method. The
mentioned sequence occurs twice and the error occurs only in one
these.

I don't know what's happen. I'll show my code below and I hope someone
of you can tellme what's the trouble. The error:

An unhandled exception occurred at $0045378A :
EUpdateError : An error occured while applying the updates in a
record:  : No up
date query specified and failed to generate one. (No fields for
inclusion in whe
re statement found)
  $0045378A
  $00453531

occurs in the line # 81

cdsCon.ApplyUpdates;

Greetings from Sao Paulo - Brazil

Ricardo

The code:
----------------------------------------------------------------------
--------------------------------------

// Le fisico selecionando filiais 1275 e 1504 e trocando para 1401.
Troca filial
// do fisico na imagem do contabil se o fisico estiver conciliado
// Coloca "(filial 1275)"(sem aspas) ou "(filial 1504)"(sem aspas) na
descricao do
// fisico e da imagem do contabil tb se o fisico estiver conciliado
program fisico_acerta_cod_filial_e_acerta_imagem_contabil;

{$mode objfpc}{$H+}

uses
  {$IFDEF UNIX}{$IFDEF UseCThreads}
  cthreads,
  {$ENDIF}{$ENDIF}
  Classes,Sqlite3DS,
  { you can add units after this }
  StrUtils,SysUtils,sqldb,sqlite3conn;

const lidos : longint = 0;
      fisalt : longint = 0;
      conalt : longint = 0;
      NomeDB =
'C:\dados_de_clientes\recebidos_nadson\casas_bahia\casasbahia.db';

var VConn: TSQLite3Connection;
    VTrans: TSQLTransaction;
    cdsCon,cdsFis : TSQLQuery;
begin
  VConn := TSQLite3Connection.Create(VConn);
  VConn.DatabaseName := NomeDB;
  VTrans := TSQLTransaction.Create(VTrans);
  VTrans.DataBase := VConn;
  cdsCon := TSQLQuery.Create(cdsCon);
  cdsCon.DataBase := VConn;
  cdsCon.Transaction := VTrans;
  cdsFis := TSQLQuery.Create(cdsFis);
  cdsFis.DataBase := VConn;
  cdsFis.Transaction := VTrans;
  VTrans.StartTransaction;
  cdsFis.SQL.Text := 'select filial,sequencia,ativo,descricao from
fisico where filial in (''1275'',''1504'')';
  try
    cdsFis.Open;
  except
    On e:Exception do
       begin;
         Writeln('Erro OPEN fisico = ',e.Message);
         VTrans.Rollback;
         Halt;
       end;
  end;
 While not cdsFis.EOF do
   begin;
     Inc(lidos);
     if (lidos < 101) or (lidos mod 500 = 0) then
        Writeln(lidos);
     cdsCon.SQL.Text := 'select filial,sequencia,ativo,descricao from
contabil where sequencia = ' +
QuotedStr(cdsFis.FieldByName('sequencia').AsString) +
                        ' and ativo = ' +
QuotedStr(cdsFis.FieldByName('ativo').AsString);
     try
       cdsCon.Open;
     except
       On e:Exception do
          begin;
            Writeln('Erro OPEN contábil = ',e.Message);
            VTrans.Rollback;
            Halt;
          end;
     end;
     While not cdsCon.EOF do
       begin;
         try
           cdsCon.Edit;
           cdsCon.FieldByName('descricao').AsString :=
cdsCon.FieldByName('descricao').AsString +
                                              '(filial ' +
cdsCon.FieldByName('filial').AsString + ')';
           cdsCon.FieldByName('filial').AsString := '1401';
           cdsCon.Post;
         except
           On e:Exception do
              begin;
                Writeln('Erro Edit contábil = ',e.Message);
                VTrans.Rollback;
                Halt;
              end;
         end;
         cdsCon.ApplyUpdates;
         Inc(conalt);
         cdsCon.Next;
       end;
     cdsCon.Close;
     cdsFis.Edit;
     cdsFis.FieldByName('descricao').AsString :=
cdsFis.FieldByName('descricao').AsString +
                                        '(filial ' +
cdsFis.FieldByName('filial').AsString  + ')';
     cdsFis.FieldByName('filial').AsString := '1401';
     cdsFis.Post;
     cdsFis.ApplyUpdates;
     Inc(fisalt);
     cdsFis.Next;
   end; // While not cdsFis.EOF do
  VTrans.Commit;
  Writeln('Número de sequencias processadas na entrada = ',lidos);
  Writeln('Fisicos alterados = ',fisalt);
  Writeln('Contabeis alterados = ',conalt);
end.

----------------------------------------------------------------------
ludob · 2012-02-01 09:40

The error message you are getting can be raised when using
upWhereKeyOnly as TSqlQuery.Upda]"]>Blockedde and no keys are found.
If using upWhereKeyOnly make sure your table has a (primary) key. 
Are you using persistent fields? These are the fields underneath the
query in object inspector or listed in the fields editor when double
clicking on the query. When using persistent fields and using
upWhereKeyOnly as TSqlQuery.Upda]"]>Blockedde you need to specify the
keyfield(s) yourself. When no persistent fields are defined, sqldb
finds the keys for you. Add pfInKey to the TField.ProviderFlags for
the fields that are key fields.

----------------------------------------------------------------------
nightrider · 2012-02-01 13:30

I'm using the edit/post in the same way I use with the same SQLite
database and the same two tables in 28 other programs. I never need to
change or Upda]"]>Blockedde since the defaults were solving my
troubles.

I can't understand your comments about persistent fields, since all
columns of my tables are persistent(at least, in the way I learn about
persistent fields).

I decide that I won't use insert/post and edit/post ever more. Due to
my experiences this combinations aren't reliable and are unstable too.
No offense at. It's my opinion based in the real experiences I had.
I'll define a second dataset and will do the updates via ExecSQL
method. This one ever fails or create troubles of instability for me.

I confess I would like to understand what's wrong here, but I haven't
the expertise needed and I can't ask for someone to waste time
analyzing and explaining the real trouble.

BTW, do you know where I can learn about ProviderFlags, pfInKey and
similar threads?

Thank you very much for your help.

[ ]

Ricardo

----------------------------------------------------------------------
ludob · 2012-02-01 13:45

Quote from: nightrider on February 01, 2012, 02:30:16 pmI can't
understand your comments about persistent fields, since all columns of
my tables are persistent(at least, in the way I learn about persistent
fields).
See http://docwiki.embarcadero.com/RADStudio/en/Persistent_Field_Compo
nents for an explanation of persistent in this context.
Quote from: nightrider on February 01, 2012, 02:30:16 pmBTW, do you
know where I can learn about ProviderFlags, pfInKey and similar
threads?
http://wiki.lazarus.freepascal.org/Working_With_TSQLQuery#Primary_key_
fields