Page 1 of 1
TOraQuery and FOR UPDATE NOWAIT problem
Posted: Thu 24 Mar 2005 00:59
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?
Posted: Thu 24 Mar 2005 01:02
by funky_disco_freak
Forgot to say:
ODAC 5.1, Oracle .2.0.4, Win XP SP2, Delphi 7
Posted: Fri 25 Mar 2005 08:56
by Alex
Please try to check your SQL statement with "SQL Plus" Oracle tool.
I have checked and error is somewher in my using TOraQuery but dont know where!???
Posted: Sun 27 Mar 2005 13:15
by 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?
here is more nicelly...sorry for previous post!
Posted: Sun 27 Mar 2005 13:20
by 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;
Some notices...
Posted: Sun 27 Mar 2005 13:35
by 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
Solve the problem
Posted: Sun 27 Mar 2005 13:40
by funky_disco_freak
Hi1
If you set
Query is executed normally!!!
Hope this is nice example to put that on bug "fix list" for future release!
You're still great guys!
Posted: Mon 28 Mar 2005 06:31
by 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;
THX!
Posted: Mon 28 Mar 2005 20:36
by 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!
one other thing...
Posted: Mon 28 Mar 2005 21:42
by 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
Posted: Tue 29 Mar 2005 07:10
by Alex
The locked record is automatically unlocked at the end of transaction, e.g. OraSession.Commit; or OraSession.Rollback.
Posted: Tue 29 Mar 2005 11:11
by 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?
Posted: Tue 29 Mar 2005 12:03
by Alex
Please send your sample code and serverside scripts to ODAC support address