Parent key not found

[jjeffman]

Hello,
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) 
/

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 :

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;

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.

Code: Select all

map< String, int > mdlOK, ptoOK; 
map< String, int >::iterator itMdl, itPto;

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".

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

Both methods of adding "Modulo" and "Pontosup" have explicit transaction blocks:

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();
}

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

[jjeffman]

Never mind. I have found the problem.
There was a trigger changing the primary key value.
Thank you very much.
Best regards.
Jayme Jeffman

[MaximG]

We are glad that you found a necessary solution. Please don't hesitate to contact us with questions concerning ODAC usage.