Parent key not found
Posted: Tue 24 Oct 2017 13:49
Hello,
I am not being able of detecting what am I doing wrong.
I have two tables :
I have placed two TOraSQL components on a data module : addModulo and addPontoSup.
As I have two Oracle schemas which have some differences between their table structures and I would like to use the same component to hold the SQL command, so I am adding the SQL command on the Form Create event :
After connecting to the correct Oracle schema I am clicking on the button which executes the TForm1::TxtCadastroToHST2() method.
The first action of this method is to fill up two stl maps which contains the primary keys of existents records in the tables.
The second action is to load the text from a CSV file which contains values to fill up both tables.
For each CSV line the method fills up a vector with the line values. Some of these vector elements are concatenated to create the keys to search for the existence of the primary keys in the stl maps already having the old records.
The code logic is : if a "Pontosup" record does not exits it must be created, but its parent "Modulo" might not exist, so "Modulo" has to be created before adding "Pontosup".
Both methods of adding "Modulo" and "Pontosup" have explicit transaction blocks:
I am not getting any SQL error so the Modulo key must exist, but something is happening, even after passing the commit point of the "Modulo" insertion the record does not is available to other applications, meaning that the commit operation was not actualy executed.
Could you please point me any mistake I have made?
Thank you very much.
Best regards.
Jayme Jeffman
I am not being able of detecting what am I doing wrong.
I have two tables :
Code: Select all
-- first table
Create table HST2_MODULO (
MODULO_ID Number NOT NULL ,
SE Varchar2 (4) NOT NULL ,
MODULO_TIPO Char (2) NOT NULL ,
MODULO_NOME Varchar2 (6) NOT NULL ,
MODULO_VOPER Number(10,1) NOT NULL ,
Constraint HST2_MODULO_pk primary key (MODULO_ID)
USING INDEX TABLESPACE SAM_IND
)
TABLESPACE SAM_DAT
/
-- second table
Create table HST2_PONTOSUP (
PONTOSUP_ID Number NOT NULL ,
Descricao Varchar2 (80) NOT NULL ,
TAG_SCADA Varchar2 (40) NOT NULL ,
TAG_HOL Varchar2 (40) NOT NULL ,
GRANDEZA Varchar2 (5) NOT NULL ,
UNIDADE Varchar2 (4) NOT NULL ,
MODULO_ID Number NOT NULL ,
Constraint HST2_PONTOSUP_pk primary key (PONTOSUP_ID)
USING INDEX TABLESPACE SAM_IND
)
TABLESPACE SAM_DAT
/
Alter table HST2_PONTOSUP add Constraint PontosSup_Modulo
foreign key (MODULO_ID)
references HST2_MODULO (MODULO_ID)
/
As I have two Oracle schemas which have some differences between their table structures and I would like to use the same component to hold the SQL command, so I am adding the SQL command on the Form Create event :
Code: Select all
DBPROVIDER->addModulo->SQL->BeginUpdate();
DBPROVIDER->addModulo->SQL->Clear();
DBPROVIDER->addModulo->SQL->Add("INSERT INTO HST2_MODULO( MODULO_ID, SE, MODULO_TIPO, MODULO_NOME, MODULO_VOPER ) \
VALUES( :MODULO_ID, :SE, :TIPO_MODULO, :NOME_MODULO, :MODULO_VOPER )");
DBPROVIDER->addModulo->SQL->EndUpdate();
DBPROVIDER->addModulo->ParamByName("MODULO_ID" )->DataType = ftInteger;
DBPROVIDER->addModulo->ParamByName("MODULO_ID" )->ParamType = ptInput;
DBPROVIDER->addModulo->ParamByName("SE" )->DataType = ftString;
DBPROVIDER->addModulo->ParamByName("SE" )->ParamType = ptInput;
DBPROVIDER->addModulo->ParamByName("TIPO_MODULO" )->DataType = ftString;
DBPROVIDER->addModulo->ParamByName("TIPO_MODULO" )->ParamType = ptInput;
DBPROVIDER->addModulo->ParamByName("NOME_MODULO" )->DataType = ftString;
DBPROVIDER->addModulo->ParamByName("NOME_MODULO" )->ParamType = ptInput;
DBPROVIDER->addModulo->ParamByName("MODULO_VOPER")->DataType = ftFloat;
DBPROVIDER->addModulo->ParamByName("MODULO_VOPER")->ParamType = ptInput;
DBPROVIDER->addPontoSup->SQL->BeginUpdate();
DBPROVIDER->addPontoSup->SQL->Clear();
DBPROVIDER->addPontoSup->SQL->Add("INSERT INTO HST2_PONTOSUP( PONTOSUP_ID, DESCRICAO, TAG_SCADA, TAG_HOL, GRANDEZA, UNIDADE, MODULO_ID ) \
VALUES( :PONTOSUP_ID, :DESCRICAO, :TAG_SCADA, :TAG_HOL, :GRANDEZA, :UNIDADE, :MODULO_ID )");
DBPROVIDER->addPontoSup->SQL->EndUpdate();
DBPROVIDER->addPontoSup->ParamByName("PONTOSUP_ID")->DataType = ftInteger;
DBPROVIDER->addPontoSup->ParamByName("PONTOSUP_ID")->ParamType = ptInput;
DBPROVIDER->addPontoSup->ParamByName("DESCRICAO" )->DataType = ftString;
DBPROVIDER->addPontoSup->ParamByName("DESCRICAO" )->ParamType = ptInput;
DBPROVIDER->addPontoSup->ParamByName("TAG_SCADA" )->DataType = ftString;
DBPROVIDER->addPontoSup->ParamByName("TAG_SCADA" )->ParamType = ptInput;
DBPROVIDER->addPontoSup->ParamByName("TAG_HOL" )->DataType = ftString;
DBPROVIDER->addPontoSup->ParamByName("TAG_HOL" )->ParamType = ptInput;
DBPROVIDER->addPontoSup->ParamByName("GRANDEZA" )->DataType = ftString;
DBPROVIDER->addPontoSup->ParamByName("GRANDEZA" )->ParamType = ptInput;
DBPROVIDER->addPontoSup->ParamByName("UNIDADE" )->DataType = ftString;
DBPROVIDER->addPontoSup->ParamByName("UNIDADE" )->ParamType = ptInput;
DBPROVIDER->addPontoSup->ParamByName("MODULO_ID" )->DataType = ftInteger;
DBPROVIDER->addPontoSup->ParamByName("MODULO_ID" )->ParamType = ptInput;
The first action of this method is to fill up two stl maps which contains the primary keys of existents records in the tables.
Code: Select all
map< String, int > mdlOK, ptoOK;
map< String, int >::iterator itMdl, itPto;
For each CSV line the method fills up a vector with the line values. Some of these vector elements are concatenated to create the keys to search for the existence of the primary keys in the stl maps already having the old records.
The code logic is : if a "Pontosup" record does not exits it must be created, but its parent "Modulo" might not exist, so "Modulo" has to be created before adding "Pontosup".
Code: Select all
String keyMdl = cValores[2].Trim()+String("_")+cValores[3].Trim(),
keyPto = keyMdl + String("_")+cValores[6].Trim();
itPto = ptoOK.find(keyPto);
if( itPto == ptoOK.end() ){ // Pontosup not found, Modulo might not exist too
itMdl = mdlOK.find( keyMdl );
if( itMdl == mdlOK.end() ){ //Módulo also not found
// Create módulo
try{ // Adding new Modulo
fVal = 0.0;
TryStrToFloat( cValores[5].Trim(), fVal);
ModuloPk = AdicionarModulo(cValores[2].Trim(),
cValores[4].Trim(),
cValores[3].Trim(),
fVal);
if( ModuloPk > 0 ) mdlOK[ keyMdl ] = ModuloPk;
itMdl = mdlOK.find( keyMdl );
if( itMdl != mdlOK.end() ) ModuloPk = itMdl->second; // Make sure the map record has added
else ModuloPk = 0;
}
catch(Exception *E) {}
}
else{ // Modulo was found
ModuloPk = itMdl->second;
}
if( ModuloPk > 0 ){// Módulo should exists
try{ // Adding new Pontosup
PontoPk = AdicionarPonto(cValores[0].Trim(),
keyPto,
keyPto,
cValores[7].Trim(),
cValores[9].Trim(),
ModuloPk);
if( PontoPk > 0 ) ptoOK[ keyPto ] = PontoPk;
}
catch(Exception *E){}
}
} // End of Pontosup not found
Code: Select all
// Modulo
if(!DBPROVIDER->ORA_DB->InTransaction) DBPROVIDER->ORA_DB->StartTransaction();
try{
DBPROVIDER->addModulo->Execute();
if(DBPROVIDER->ORA_DB->InTransaction) DBPROVIDER->ORA_DB->Commit();
pkMdl = ModuloPK ;
}
catch(EOraError *E){
if(DBPROVIDER->ORA_DB->InTransaction) DBPROVIDER->ORA_DB->Rollback();
}
//Pontosup
if(!DBPROVIDER->ORA_DB->InTransaction) DBPROVIDER->ORA_DB->StartTransaction();
try{
DBPROVIDER->addPontoSup->Execute();
if(DBPROVIDER->ORA_DB->InTransaction) DBPROVIDER->ORA_DB->Commit();
pkPto = PtoPK ;
}
catch(EOraError *E){
if(DBPROVIDER->ORA_DB->InTransaction) DBPROVIDER->ORA_DB->Rollback();
}
Could you please point me any mistake I have made?
Thank you very much.
Best regards.
Jayme Jeffman