Devart Forums

Posted: **Wed 05 Apr 2006 13:20**

Delphi 7
ODAC 5.70.0.29
Oracle 9.2.0.7

We have some problems when using NonBlocking-mode. We tried to expose the problems with the following method (see also comments in the code):

- after calling "BreakExec", "SQL.Executing" is still true, but in our opinion it should be false, right?

- access violation when freeing the session (see code below)

- after BreakExec the "drop table" doesn't work (it does nothing). See comment "//this doesn't drop table" in code below

- after calling "BreakExec" we call Commit and get the number of rows, which is always 0: is this correct? Shouldn't BreakExec just abort the process and if we call commit afterwards, all changes until the BreakExec should be stored? (see comment "//the following line returns always 0" in the code below)

- if we use "lSession.Options.Net := False" then we get a "ORA-24381: Error in Array-DML" exception in the line "Assert(SQL.Executing, 'executing');"

Code: Select all

procedure TOraSession_t.TestNonBlocking;
const
  CRecords = 1000000;
var
  lSession: TOraSession;
begin
  lSession := TOraSession.Create(nil);
  try
    lSession.Options.Net := False;
    lSession.Username := //user name;
    lSession.Password := //password;
    lSession.Server := //enter server address here;
    lSession.LoginPrompt := False;
    lSession.Connect;
    Assert(lSession.Connected, 'connected');

    try lSession.ExecSQL('drop table test_nb', []);
    except on EOraError do;
    end;

    lSession.ExecSQL('create table test_nb (i integer)', []);
    try
      lSession.ThreadSafety := True;
      lSession.SQL.NonBlocking := True;
      with lSession do begin
        SQL.Text := 'insert into test_nb (i) values (1)';
        SQL.Execute(CRecords);
        Assert(SQL.Executing, 'executing');
        Sleep(500);

        SQL.BreakExec;
        //SQL.Executing is true here, but should be false
        Assert(not SQL.Executing, 'executing after breakexec');

        //here committing < CRecords.
        lSession.Commit;

        lSession.SQL.NonBlocking := False;
        lSession.ThreadSafety := False;
        //the following line returns always 0
        lSession.ExecSQL('begin select into :C count(*) from test_nb; end;',
          [0]);
        OutputDebugString('count = ' + 
          lSession.ParamByName('C').AsString);
      end;
    finally
      lSession.SQL.NonBlocking := False;
      lSession.ThreadSafety := False;
      //this doesn't drop table
      lSession.ExecSQL('drop table test_nb', []);
    end;
  finally
    FreeAndNil(lSession); //Access violation
  end;
end;

Can you please give me some hints about the described problems? Thanks.[/code]

Posted: **Mon 10 Apr 2006 09:02**

The error "Error in Array-DML" is being raised under OCI because there is a restriction of max 65534 iterations on Oracle 9g.

BreakExec method just calls OCIBreak procedure and it doesn't set Executing property to False. So you have to add

Code: Select all

    while OraSQL.Executing do
      Application.ProcessMessages;

to wait until the execution thread ends and sets Executing property to False.

After commit you have 0 records because you set very small delay before performing BreakExec. Try to set for example 1000.

We couldn't reproduce other issues.

Posted: **Mon 10 Apr 2006 14:41**

Thanks for the information.

About the AV we have when freeing the session. I further investigated the problem: we get it every time we execute the method posted above. The AV occurs in the method "TOCIConnection.StopThread" in the "PeekMessage" line:

Code: Select all

  // PeekMessage is needed to avoid AV when TOCICommand or TOCIRecordSet is
  // destroying and thread is active.
  if APeekMessage then
    PeekMessage(AMsg, hWindow, WM_ENDTHREAD, WM_ENDTHREAD, PM_REMOVE);

The problem is not the code in "TOCIConnection.StopThread", but when this method is called Self is already nil! Why? Look at the following callstack (this is the call stack when we free the session in the method above):

TOCICommand.SetConnection(nil)
TCustomDASQL.SetConnection(nil)
TCustomDAConnection.ClearRefs
TCustomDAConnection.Destroy
TOraSession.Destroy

As you can see TOCICommand.FConnection is set to nil. This due to the line in "TCustomDAConnection.Destroy": ClearRefs; after this, "TCustomDAConnection.Destroy" calls inherited:

TOCIConnection.StopThread(nil,True)
TOCICommand.Destroy
TCustomDASQL.Destroy
TComponent.DestroyComponents
TComponent.Destroy
TCustomConnection.Destroy
TCustomDAConnection.Destroy
TOraSession.Destroy

And here "TOCIConnection.StopThread" is called, where Self is already nil:

Code: Select all

destructor TOCICommand.Destroy;
begin
{$IFDEF MSWINDOWS}
  if hExecThread  nil then begin
    FConnection.StopThread(hExecThread, True);
  [...]

In the code above FConnection is already nil! In the following code you can see where FConnection is set to nil (of course not directly but through some method calls -> see first call stack) and afterwards a method of FConnection is called (see second call stack).

Code: Select all

destructor TCustomDAConnection.Destroy;
begin
  try
    Disconnect;
  finally
    ClearRefs; <<< here TOCICommand.FConnection is set to nil

    inherited; <<< here TOCICommand.FConnection.StopThread() is called (where FConnection is already nil)
[...]

Can you confirm that this is a bug? Or do I miss something? If it is a bug, please let me know if and when it will be fixed.
Thanks.

Posted: **Tue 11 Apr 2006 11:17**

We reproduced and fixed this problem.

Posted: **Tue 11 Apr 2006 15:01**

Thanks. Can you just tell me, when the next release build will be available?

Posted: **Wed 12 Apr 2006 13:19**

We plan to release new build of ODAC this week.

Devart Forums

Several problems with NonBlocking-mode

Several problems with NonBlocking-mode