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