Ajnother parsing question

[ltd]

Hi,

i have the following problem.

When i use a TOracleQuery to execute the following code i get parses on the database for every call.

"begin
Return:=Mypackage.MyfuncPar1,ar2);
end;"

When i call the following code i get no extra parses:

"begin
MyPackage.MyprocedurePar1,ar2,ar3);
end;"

All variables are declared and are of standard types ( otString, otInteger). I am not closing the query. So my question is why is an PL/SQL function( or the return variable) forcing a reparse on every call?

I use DOA 3.4.5 on BCB5 with different Oracle versions. Test was done against Oracle 9.2.0.3.0.

Best regards,

Lars

 

[Marco Kalter]

The first call is not correct (Return is not declared) and should raise an exception. This would cause a reparse the next time, but I assume this is not what really happened?

------------------
Marco Kalter
Allround Automations

 

[ltd]

Hi, there was one ":" missing in the example.

Here is the real code:

pqPeerState->SQL->Add( "BEGIN" );
pqPeerState->SQL->Add( ":nRet := "+asPackageName+"PEERSTATE( :nIDInstance, :nIDOperator, :tBitPos,"
" :tBitValue, :nSize, :vcEtc, "
" :nIDErrorText, :vcErrorText, :iDoCommit ); "
);
pqPeerState->SQL->Add( "END;" );
pqPeerState->DeclareVariable( "nRet", otInteger );
pqPeerState->DeclareVariable( "nIDInstance", otInteger );
pqPeerState->DeclareVariable( "nIDOperator", otInteger );
pqPeerState->DeclareVariable( "tBitPos", otInteger );
pqPeerState->DeclareVariable( "tBitValue", otInteger );
pqPeerState->DeclareVariable( "nSize", otInteger );
pqPeerState->DeclareVariable( "vcEtc", otString );
pqPeerState->DeclareVariable( "nIDErrorText", otInteger );
pqPeerState->DeclareVariable( "vcErrorText", otString );
pqPeerState->DeclareVariable( "iDoCommit", otInteger );

After that there are only calls to set or get variable values and calls to execute the procedure

Lars

[This message has been edited by ltd (edited 24 July 2003).]

 

[Marco Kalter]

If you declare variables before each execute call, the query will be reparsed. If the variable types don't change, do not redeclare them to prevent extra parse operations.

------------------
Marco Kalter
Allround Automations

 

[ltd]

Hi Marco,
another missunderstanding due to my example not beeing clear enough.

The code in my previous mail is in the constructor. After that you only find something like this ( code comes from another query) :

pqDestReq->SetVariable( "nIDOperator", GlobalOperator.GetUserId());
pqDestReq->SetVariable( "tLocation", tData.Scanner.setLocVariant());
pqDestReq->SetVariable( "nPIC", tData.PIC);
pqDestReq->SetVariable( "vcBarcode", tData.Barcode);
pqDestReq->SetVariable( "vcEtc", tData.OtherData);
pqDestReq->SetVariable( "iDoCommit", 1);

pqDestReq->Execute();

if( pqDestReq->GetVariable( "nRet" ) < 0 )
{
uiIDErrText = pqDestReq->GetVariable( "nIDErrorText" );
asErrText = pqDestReq->GetVariable( "vcErrorText" );

There are no redeclarations and as i said when i use a procedure instead of a function i get no reparses. The only difference i can see is the ":nRet" Variable and as i said i never redeclare anything after creating the query in the constructor.