TOraQuery and FOR UPDATE NOWAIT problem

[[email protected]]

Hi!

SELECT * FROM Table WHERE id=17 FOR UPDATE NOWAIT

in TOraQuery and then put Active=True

I get all the time "ORA-00907: missing right parenthesis"

I know the statement is OK...

How to execute it? Do I have to modify Open to Exec or what!?

P.S.
TOraQuery.IsQuery is true so I have automated procedures for executing queries...what lead me in problem?

[funky_disco_freak]

Forgot to say:
ODAC 5.1, Oracle .2.0.4, Win XP SP2, Delphi 7

[Alex]

Please try to check your SQL statement with "SQL Plus" Oracle tool.

[funky_disco_freak]

Dear Alex!
Ihave and no error i shown!! I use TOraQuery through my custom function for executing SQL sequnces... Here is code of my RunSQLOra:

function RunSqlOra(SQLString:string; OraS:TOraSession; ErrorFileName:String=cs_MY_LOG_FILE): Longint;
begin
Result:=-1;
with UserSession do begin
try
OraQ.Close;
OraQ.Session:=OraS;
OraQ.SQL.Clear;
OraQ.SQL.Add(SQLString);
OraQ.Prepare;
if OraQ.IsQuery then begin
OraQ.Options.QueryRecCount:=True; // obvezno zbog SELECT Querija koji vraća broj slogova
OraQ.FetchAll:=True;
OraQ.Open;
Result:= OraQ.RecordCount;
end
else begin
OraQ.StrictUpdate := True;
OraQ.ExecSQL;
Result := OraQ.RowsAffected;
end;
except
on E:Exception do begin
try
usOraError:=E.Message;
LogSQLError(E.Message, OraQ);
finally
OraQ.Close;
end;
end;
end;
end;
end;

and
object OraQ: TOraQuery
Session = OraSessionUser
SQL.Strings = (
'SELECT * FROM KANDIDAT WHERE SIFRA=17 FOR UPDATE NOWAIT')
Options.QueryRecCount = True
AfterExecute = SmartQKandidatAfterExecute
OnUpdateError = SmartQKandidatUpdateError
AfterOpen = SmartQKandidatAfterOpen
OnDeleteError = SmartQKandidatEditError
OnEditError = SmartQKandidatEditError
OnPostError = SmartQKandidatEditError
Left = 400
Top = 216
end

where all After events are poor logging made through:
LogSQL(TOraQuery(sender));

Please what is wrong?

[funky_disco_freak]

Code: Select all

function TIWUserSession.RunSqlOra(SQLString:string; OraS:TOraSession; ErrorFileName:String=cs_MY_LOG_FILE): Longint;
begin
  Result:=-1;
  with UserSession do begin
    try
      OraQ.Close;
      OraQ.Session:=OraS;
      OraQ.SQL.Clear;
      OraQ.SQL.Add(SQLString);
      OraQ.Prepare;
      if OraQ.IsQuery then begin
        OraQ.Options.QueryRecCount:=True; // obvezno zbog SELECT Querija koji vraća broj slogova
        OraQ.FetchAll:=True;
        OraQ.Open;
        Result:= OraQ.RecordCount;
      end
      else begin
        OraQ.StrictUpdate := True;
        OraQ.ExecSQL;
        Result := OraQ.RowsAffected;
      end;
    except
      on E:Exception do begin
        try
          usOraError:=E.Message;
          LogSQLError(E.Message, OraQ);
        finally
          OraQ.Close;
        end;
      end;
    end;
  end;
end;

[funky_disco_freak]

Dear Alex,

here are some facts:
SQL is definatelly OK:

Code: Select all

SELECT * FROM KANDIDAT WHERE SIFRA=17 FOR UPDATE NOWAIT

Here are some facts:

• When I put Active=True in Delphi IDE error is raised (same error message)
  When I open Query Editor, and press Execute (on SQL Page) looks OK-no exception!!
  When I choose "Data Editor" from Delphi IDE, and then Open, exception is raised (same error message)

All other properties in TOraQuery are default (if they are not text that is here once again-read from dfm file):

Code: Select all

  object OraQ: TOraQuery
    Session = OraSessionUser
    SQL.Strings = (
      'SELECT * FROM KANDIDAT WHERE SIFRA=17 FOR UPDATE NOWAIT')
    Options.QueryRecCount = True
    AfterExecute = SmartQKandidatAfterExecute
    AfterOpen = SmartQKandidatAfterOpen
    Left = 400
    Top = 216
  end

[funky_disco_freak]

Hi1

If you set

Code: Select all

QueryRecCount:=False; 

Query is executed normally!!!

Hope this is nice example to put that on bug "fix list" for future release!

You're still great guys!

[Alex]

TOraQuery sends SQL query to the server to get record count and cannot parse SELECT queries with
FOR UPDATE NOWAIT. You can see all statements that are executed on server if you set TOraQuery.Debug:=true and TOraSession.Debug:=True and include OdacVCL unit to the "uses" list.
If you want to use TOraQuery.Options.QueryRecordCount=true with locks please set

Code: Select all

OraQuery.SQL.Text := 'SELECT * FROM Table WHERE id=17';
OraQuery.SQLLock.Text := 'SELECT * FROM Table WHERE id=17 FOR UPDATE NOWAIT';
OraQuery.Open;
OraQuery.Lock;

[funky_disco_freak]

THX nice to read that....maybe someone other would come in the same troubles...

THX once more to clear up the issue!

[funky_disco_freak]

Hi!

If I use your code (i.e. OraQ.lock) and use OraQ for some other queries in the meantime, table locked with OraQ stay locked forever, regaldless TOraSession.Rollback or not!?

Is that normal and if is how to unlock table locked in that way?

THX

[Alex]

The locked record is automatically unlocked at the end of transaction, e.g. OraSession.Commit; or OraSession.Rollback.

[funky_disco_freak]

Hi!

I thought also but not true (at least in my case). I'll show you some Intraweb demo to demonstarte the problem (based on OE schema from Oracle).

OK?

[Alex]

Please send your sample code and serverside scripts to ODAC support address