Updated failed. Found XXX records.

[eduardosic]

Dear Sirs

I think that I found an error in the last release of MyDAC (5;....).
I have a large system working perfectly (Delphi2007 - MySQL 5,0,...) and after I installed
the last release of MyDAC, in 2 different routines I become to receive an msg:

"Updated failed. Found XXX records."

After a long time over the problem, I found the following common points that appears to me
to be related with it:

1 - the error appears when the system tries to make a post over a TMyTable in edit mode;

2 - the table data has a primary key with 4 or 5 fields: the first 3-4 integer fields and
the last field of the key is an auto incremented field (this is the important fact);

What I found:

A - if instead of the fifth auto incremented field I put a normal integer field: it works without error;

B - with the 5th.field auto incremented, if I put the name of all fields in the "KeyFields" property of the table, it works without error;

C - with auto increment in the last key position, setting the debug property of the table and using MyDacVCL, I found that the edit string generated by the TMyTable shows only the auto incremented field in the where clause, as it was the unique component of the key.

But, if the last field of the key is not auto incremented, the where contains all the fields of the key;

You can find an example (code and data) the reproduces the error in the following link:

http://www.drdsistemas.com.br/util/myda ... 6-2008.zip

I would appreciate very much to receive some news from you soon, about this error, since I am not sure about other points in my systems that this error could happen.

cordially yours

[Dimon]

Thank you for information. We have reproduced this problem and fixed it. This fix will be included in the next MyDAC build.

[eduardosic]

Thank you for information. We have reproduced this problem and fixed it. This fix will be included in the next MyDAC build.

Thank's Dimon.

[oppofix]

I have a similar problem with Oracle Data Access;
when the same bug-fix for Oracle Data Access?
Thanks a lot,
Fabrizio (Italy)

[Plash]

Please describe in details what you are doing and what error you receive.

[oppofix]

I'll explain my problem with pleasure.

My routine is "Processa_Telegramma_401", where i make some
things to get a Dataset containing one or more records.
In these records i need to update only some fields,
but whet the record is only one it works,
if the records are more than one, i get
the error "Found XXX Records".

Note: if there is more than one record, the records
are different because they contain one field called "DATA_ORA",
type "TIMESTAMP"; there is no key in these records.

MANY THANKS!!!

Here is my function, with the error point bolded:

************************************************************
function Processa_Telegramma_401(odacSession: TOraSession; var iRecs: integer): integer;

var
sQuery: widestring;
sCommand: widestring;
sInf: string;
iRetVal: integer;
sBarcode: string;
sZona: string;
odacQuery: TOraQuery;
odacUpdate: TOraQuery;

begin

// Inizializzo i Valori di Ritorno
iRetVal:= STATUS_TELE_401_Funzione_non_Eseguita;
iRecs:= 0;

// Gestore degli Errori
try

// Inizializzo gli Oggetti Query
Init_OraQuery(odacSession,odacQuery);
Init_OraQuery(odacSession,odacUpdate);

// RICERCA IN TABELLA COLLI_DEVIATI
sQuery:= 'SELECT * FROM COLLI_DEVIATI ';
////sQuery:= sQuery + 'WHERE STATO' + IntToStr(STATUS_TELE_401_Ok) + ' ';
sQuery:= sQuery + 'WHERE STATO=0 ';
sQuery:= sQuery + 'AND ELABORAZIONI<10 ';
sQuery:= sQuery + 'ORDER BY DATA_ORA';
odacQuery.SQL.Clear;
odacQuery.SQL.Add(sQuery);
odacQuery.Open;
iRecs:= odacQuery.RecordCount;
if iRecs=0 then
begin
Result:= STATUS_TELE_401_Ok;
exit;
end;

// Iniziamo una bella Transazione, così stiamo tutti più Tranquilli...
odacSession.StartTransaction;

// Ciclo di Lettura dati da Processare
while not odacQuery.Eof do

begin

try

// Recupero campi Interessanti dalla Tabella "COLLI_DEVIATI"
sBarcode:= odacQuery.Fields.FieldByName('BARCODE').Value;
if Length(Trim(sBarcode))=36 then
sBarcode:= sBarcode + ' ';
sZona:= odacQuery.Fields.FieldByName('LINEA').Value;

// Update Campi di Stato in Tabella "COLLI_DEVIATI"
odacQuery.Edit;
odacQuery.Fields.FieldByName('STATO').Value:= STATUS_TELE_401_Cassa_Riversata_ad_AIA;
odacQuery.Fields.FieldByName('DESC_STATO').Value:= sERRORE_TELE[STATUS_TELE_401_Cassa_Riversata_ad_AIA];
odacQuery.Fields.FieldByName('ELABORAZIONI').Value:= odacQuery.Fields.FieldByName('ELABORAZIONI').Value + 1;
odacQuery.UpdateRecord;

// Insert Telegramma 401 in Tabella "TRASCAR_TR"
sInf:= '';
sInf:= sInf + 'CD';
sInf:= sInf + ' ';
sInf:= sInf + Format('%-40s',[sBarcode]);
sInf:= sInf + Format('%-3s',[sZona]);
sInf:= sInf + FormatdateTime('yyyymmddhhmmss',now);
sInf:= sInf + '#';
sCommand:= 'SELECT * FROM TRASCAR_TR WHERE 1=2';
odacUpdate.SQL.Clear;
odacUpdate.SQL.Add(sCommand);
odacUpdate.Open;
odacUpdate.Insert;
odacUpdate.Fields.FieldByName('TRSTAT').Value:= 0;
odacUpdate.Fields.FieldByName('TRTIME').Value:= FormatDateTime('yyyymmddhhmmsszzz',now);
odacUpdate.Fields.FieldByName('TRMITT').Value:= 'TRASCAR';
odacUpdate.Fields.FieldByName('TRHOST').Value:= 'AIA';
odacUpdate.Fields.FieldByName('TRCODA').Value:= 'TRASCAR';
odacUpdate.Fields.FieldByName('TRTELE').Value:= 401;
odacUpdate.Fields.FieldByName('TROPER').Value:= 1;
odacUpdate.Fields.FieldByName('TRINFO').Value:= sInf;
odacUpdate.Post;

except

// Annullo Insert del Telegramma in Tabella "TRASCAR_RC"
if not odacQuery.Eof then
odacUpdate.Delete;

// Segnalazione Errore nel Campo di Stato in Tabella "COLLI_DEVIATI"
odacQuery.Cancel;
odacQuery.Edit;
odacQuery.Fields.FieldByName('STATO').Value:= STATUS_TELE_401_Cassa_Non_Riversata_ad_AIA;
odacQuery.Fields.FieldByName('DESC_STATO').Value:= sERRORE_TELE[-STATUS_TELE_401_Cassa_Non_Riversata_ad_AIA];
odacQuery.Fields.FieldByName('ELABORAZIONI').Value:= odacQuery.Fields.FieldByName('ELABORAZIONI').Value + 1;
odacQuery.UpdateRecord;

end;

// Avanti coi Carri...
odacQuery.Next;

end;

// Conclusione Corretta della Transazione
odacSession.Commit;

iRetVal:= STATUS_TELE_401_Ok;

except

// Errore!
iRetVal:= STATUS_TELE_401_Application_Error;

// Conclusione con errore della Transazione
odacSession.Rollback;

end;

// Distruggo gli Oggetti Query
Destroy_OraQuery(odacQuery);
Destroy_OraQuery(odacUpdate);

// Valore di Ritorno
Result:= iRetVal;

end;





//******************************************************************************
//*** PROCEDURA per Istanziare ed Inizializzare un Oggetto TOraQuery
//******************************************************************************
procedure Init_OraQuery(var odacSession: TOraSession; var odacQuery: TOraQuery);

begin

// Creazione Oggetto
odacQuery:= TOraQuery.Create(nil);
odacQuery.FetchAll:= true;

// Link con la Sessione di Connessione al Database
odacQuery.Session:= odacSession;

end;



//******************************************************************************
//*** PROCEDURA per Chiudere e Distruggere un Oggetto TOraQuery
//******************************************************************************
procedure Destroy_OraQuery(var odacQuery: TOraQuery);

begin

// Creazione Oggetto
odacQuery.Close;
odacQuery.Destroy;

end;

[eduardosic]

I'll explain my problem with pleasure.

My routine is "Processa_Telegramma_401", where i make some
things to get a Dataset containing one or more records.
In these records i need to update only some fields,
but whet the record is only one it works,
if the records are more than one, i get
the error "Found XXX Records".

Note: if there is more than one record, the records
are different because they contain one field called "DATA_ORA",
type "TIMESTAMP"; there is no key in these records.

MANY THANKS!!!

Here is my function, with the error point bolded:

************************************************************
function Processa_Telegramma_401(odacSession: TOraSession; var iRecs: integer): integer;

var
sQuery: widestring;
sCommand: widestring;
sInf: string;
iRetVal: integer;
sBarcode: string;
sZona: string;
odacQuery: TOraQuery;
odacUpdate: TOraQuery;

begin

// Inizializzo i Valori di Ritorno
iRetVal:= STATUS_TELE_401_Funzione_non_Eseguita;
iRecs:= 0;

// Gestore degli Errori
try

// Inizializzo gli Oggetti Query
Init_OraQuery(odacSession,odacQuery);
Init_OraQuery(odacSession,odacUpdate);

// RICERCA IN TABELLA COLLI_DEVIATI
sQuery:= 'SELECT * FROM COLLI_DEVIATI ';
////sQuery:= sQuery + 'WHERE STATO' + IntToStr(STATUS_TELE_401_Ok) + ' ';
sQuery:= sQuery + 'WHERE STATO=0 ';
sQuery:= sQuery + 'AND ELABORAZIONI<10 ';
sQuery:= sQuery + 'ORDER BY DATA_ORA';
odacQuery.SQL.Clear;
odacQuery.SQL.Add(sQuery);
odacQuery.Open;
iRecs:= odacQuery.RecordCount;
if iRecs=0 then
begin
Result:= STATUS_TELE_401_Ok;
exit;
end;

// Iniziamo una bella Transazione, così stiamo tutti più Tranquilli...
odacSession.StartTransaction;

// Ciclo di Lettura dati da Processare
while not odacQuery.Eof do

begin

try

// Recupero campi Interessanti dalla Tabella "COLLI_DEVIATI"
sBarcode:= odacQuery.Fields.FieldByName('BARCODE').Value;
if Length(Trim(sBarcode))=36 then
sBarcode:= sBarcode + ' ';
sZona:= odacQuery.Fields.FieldByName('LINEA').Value;

// Update Campi di Stato in Tabella "COLLI_DEVIATI"
odacQuery.Edit;
odacQuery.Fields.FieldByName('STATO').Value:= STATUS_TELE_401_Cassa_Riversata_ad_AIA;
odacQuery.Fields.FieldByName('DESC_STATO').Value:= sERRORE_TELE[STATUS_TELE_401_Cassa_Riversata_ad_AIA];
odacQuery.Fields.FieldByName('ELABORAZIONI').Value:= odacQuery.Fields.FieldByName('ELABORAZIONI').Value + 1;
odacQuery.UpdateRecord;

// Insert Telegramma 401 in Tabella "TRASCAR_TR"
sInf:= '';
sInf:= sInf + 'CD';
sInf:= sInf + ' ';
sInf:= sInf + Format('%-40s',[sBarcode]);
sInf:= sInf + Format('%-3s',[sZona]);
sInf:= sInf + FormatdateTime('yyyymmddhhmmss',now);
sInf:= sInf + '#';
sCommand:= 'SELECT * FROM TRASCAR_TR WHERE 1=2';
odacUpdate.SQL.Clear;
odacUpdate.SQL.Add(sCommand);
odacUpdate.Open;
odacUpdate.Insert;
odacUpdate.Fields.FieldByName('TRSTAT').Value:= 0;
odacUpdate.Fields.FieldByName('TRTIME').Value:= FormatDateTime('yyyymmddhhmmsszzz',now);
odacUpdate.Fields.FieldByName('TRMITT').Value:= 'TRASCAR';
odacUpdate.Fields.FieldByName('TRHOST').Value:= 'AIA';
odacUpdate.Fields.FieldByName('TRCODA').Value:= 'TRASCAR';
odacUpdate.Fields.FieldByName('TRTELE').Value:= 401;
odacUpdate.Fields.FieldByName('TROPER').Value:= 1;
odacUpdate.Fields.FieldByName('TRINFO').Value:= sInf;
odacUpdate.Post;

except

// Annullo Insert del Telegramma in Tabella "TRASCAR_RC"
if not odacQuery.Eof then
odacUpdate.Delete;

// Segnalazione Errore nel Campo di Stato in Tabella "COLLI_DEVIATI"
odacQuery.Cancel;
odacQuery.Edit;
odacQuery.Fields.FieldByName('STATO').Value:= STATUS_TELE_401_Cassa_Non_Riversata_ad_AIA;
odacQuery.Fields.FieldByName('DESC_STATO').Value:= sERRORE_TELE[-STATUS_TELE_401_Cassa_Non_Riversata_ad_AIA];
odacQuery.Fields.FieldByName('ELABORAZIONI').Value:= odacQuery.Fields.FieldByName('ELABORAZIONI').Value + 1;
odacQuery.UpdateRecord;

end;

// Avanti coi Carri...
odacQuery.Next;

end;

// Conclusione Corretta della Transazione
odacSession.Commit;

iRetVal:= STATUS_TELE_401_Ok;

except

// Errore!
iRetVal:= STATUS_TELE_401_Application_Error;

// Conclusione con errore della Transazione
odacSession.Rollback;

end;

// Distruggo gli Oggetti Query
Destroy_OraQuery(odacQuery);
Destroy_OraQuery(odacUpdate);

// Valore di Ritorno
Result:= iRetVal;

end;





//******************************************************************************
//*** PROCEDURA per Istanziare ed Inizializzare un Oggetto TOraQuery
//******************************************************************************
procedure Init_OraQuery(var odacSession: TOraSession; var odacQuery: TOraQuery);

begin

// Creazione Oggetto
odacQuery:= TOraQuery.Create(nil);
odacQuery.FetchAll:= true;

// Link con la Sessione di Connessione al Database
odacQuery.Session:= odacSession;

end;



//******************************************************************************
//*** PROCEDURA per Chiudere e Distruggere un Oggetto TOraQuery
//******************************************************************************
procedure Destroy_OraQuery(var odacQuery: TOraQuery);

begin

// Creazione Oggetto
odacQuery.Close;
odacQuery.Destroy;

end;

Try to Set the property KeyFields for the odacQuery with the Field's of primary key of table COLLI_DEVIATI while the new build of mydac is not released.

[Plash]

If your table has no primary key, you should add ROWID to the SELECT statement:

Code: Select all

SELECT T.*, T.ROWID FROM COLLI_DEVIATI T