ORA-12571 when inserting rows in a threaded procedure

[JeanR]

Hello,

i try to test ODAC 6.0 with my delphi 7 and oracle 10g database for adopt this components in our dev. team (i haven't licence version yet, can it be a reason for my problemd ?), but i except a big blocking bug :

when i use the Direct connection mode in a thread to insert rows, i expect lot of error 12571, if made the insert with a TOraQuery linked to a TOraSession created inside the thread.

with 100 records to insert, i can have 10 to 20 errors !
destination table include varchars2, blobs, clobs and numbers fields.

Little exemple :

Code: Select all

type
  TMythread= class(TThread)
  private
    OraSessionThread: TOraSession;
    OraQueryThread: TOraQuery;

...
constructor TMythread.Create;
begin
  inherited Create(False);

  OraSessionThread := TOraSession.Create(nil);
  with OraSessionThread do begin
    if FrmMain.cb_direct.Checked then begin // DIRECT
      Options.Direct := True;
      ConnectString := DCSTRING;
      Schema := STR_SCHEMA;
      Server :=  STR_DSERVER;
      Options.ConnectionTimeout := 2000;
      ThreadSafety := True;
  end else begin // OCI
      Options.Direct := False;
      ConnectString := CSTRING;
      Schema := STR_SCHEMA;
      Server := STR_SERVER;
      ThreadSafety := True;
  end;

    Options.UseUnicode := True;
    LoginPrompt := False;
    Connect;

  end;

  OraQueryThread := TOraQuery.Create(nil);
  OraQueryThread.Session := OraSessionThread;

end;

procedure TMythread.Execute;
(...) // loop to generate list of sql insertions
    OraQueryThread.SQL.Text := 'INSERT INTO ' + TABLENAME + ' (' + StrChamps + ') VALUES (' + StrParams + ')';
OraQueryThread.Params[i].AsX := XYZ; (...)
OraQueryThread.Execute;
(...)
// end loop
OrasessionThread.Commit;
end;

destructor TMyThread.Destroy;
begin
  OraSessionThread.Commit;
  OraSessionThread.Disconnect;
  FreeAndNil(OraQueryThread);
  FreeAndNil(OraSessionThread);
  inherited;
end;

Note : if i use the not direct mode (by OCI), all inserts is ok, without any errors, but in my case OCI connection is really too long, thats why i prefer to use Direct mode ...

any idea ?

(Odac 6, Delphi 7, Oracle 10g, Client wXP, Serveur W2k3)

Many thanks and sorry for my poor english !

[JeanR]

FAQ # Are the ODAC connection components thread-safe?
In Client mode, ODAC is thread-safe. In Direct mode, we do not guarantee complete thread safety and recommend setting up a separate Connection set for each thread that uses ODAC.

Argh :'(

i'v set different connection for each thread, but it doesn't solve my problem i'm in the "In Direct mode, we do not guarantee complete thread safety" case ?

thx

Jean.R

[Plash]

We could not reproduce the problem. Please send to odac*crlab*com a complete small sample that demonstrates the problem, including script to create server objects.