Is there any possibility to check SQL syntax using doa?

[vladimir_ch]

Is there any possibility to check SQL syntax using doa?
Regards,
Vladimir

 

[Marco Kalter]

Not really. You can use the TOracleQuery.Describe and DescribeFull functions to get a field list and a function type, but these functions do perform any detailed checks about the validity of the SQL. Even if they return a function type, the SQL may still fail when executed.

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

 

[sergejh]

If describe() does not parse SQL query, how can I do it?
My problem is... when I call

TOracleQuery q;
q->describe();

It describe field definitions, but not check SQL syntax (for example, for SQL-query it work correctly, but for INSERT-query - always return true).

Why DOA do not has function like "oparse" in OCI? Or maybe, through DOA it is possible to call this OCI function? How do that?

 

[Marco Kalter]

Oparse is an old SQL*Net 2 function that is no longer used in Net 8 and later. The actual amount of syntax checking ius limited though. The full parse and execute are combined in one call to the server.

 

[sergejh]

The full parse and execute are combined in one call to the server.

And DOA function do that is Execute() ?

Now, I doing next:

MyCheckQuery()
{
 TOracleQuery *q = new TOracleQuery;
 q->Session = ...; //set session
 q->SQL->Text = "..."; //some query
 try
 {
  q->Execute();
  q->Rollback();
 }
 catch(...)
 {
  ; //some error
 }
}

Is this right? But this variant take server resources...

 

[al0]

Originally posted by sergejh:

The full parse and execute are combined in one call to the server.

And DOA function do that is Execute() ?

Now, I doing next:
[Skipped]
Is this right? But this variant take server resources...

I really don't understand what for you need to check the check your SQL-statement without executing.

You latest approach is generally correct but bad as bad. Moreover, in some cases it may cause some database changes, for example, if it fire some triggers with authoomous transaction iside. And it really can consume a lot of server resources, especially if statement is correct.

BTW you can despite the fact that oparse() is obsoleted it is till available for Oracle 8i and 9i (don't know about 10g). You can convert ORacle Server context to LDA with OCISvcCtxToLda() and then use old OCI7 functions.

OCI9 contains as well OCIStmtPrepare() and OCIStmtPrepare2() functions those are closest native counterpats of oparse(), but they performe only local check of statement, so much less rigorous then oparse().

And final touch - you can use OCI functions from DOA, they are defined in OracleCI unit. To obtain necessary contexts and handles you shall use ExternalAUT ... ExternalSVC properties of TOracleSession.

IF you wiuld explain purpose of the check that you wan't to do I may be able to make better suggestion.

Feel free to mail me directly if you wish (you may mail in Russian if you prefer).

 

[al0]

Originally posted by Marco Kalter:
Oparse is an old SQL*Net 2 function that is no longer used in Net 8 and later. The actual amount of syntax checking ius limited though. The full parse and execute are combined in one call to the server.

Ther is a possibility to separate parsing from execution. OCI_STMT_EXECUTE has mode parameter. If set it to OCI_PARSE_ONLY then only parsing will be performed.

16-6 Oracle Call Interface Programmer