ODAC DataSet to TADODataSet

Matteo · Post by **Matteo** » Tue 30 May 2006 13:09

Hello everyone!

I'm evaluating ODAC and I've searched the forum for my problem's answer but I'v not found any:
I have a procedure in a big application that returns a TADODataSet but I'm on the way to change the TADOStoredProcedure with ODAC OraStoredProc to increase app performances and I hope to do that without changing the function prototype.

Well... I'm finding a way to exec a TOraStoredProc and copy data into a TADODataSet, like the TADOStoredProcedure Clone() function.
Let's try with an example:

now:

Code: Select all

procedure GetElencoIntercettazioni(ADODataSet: TAdoDataset; IdTarget: Integer; Modalita: string);
var
  ADOStoredProc1: TADOStoredProc;
begin
  ADOStoredProc1 := nil;
  ADOStoredProc1 := TADOStoredProc.Create(nil);
  ADOStoredProc1.Connection := ADOConnection1;
  ADOStoredProc1.ProcedureName := 'GetElencoIntercettazioni';
  ADOStoredProc1.Parameters.CreateParameter('p_id_target', ftInteger, pdInput, -1, IdTarget);
  ADOStoredProc1.Parameters.CreateParameter('p_modalita', ftString, pdInput, 1, Modalita);
  ADOStoredProc1.Open;
  ADODataSet.Clone(ADOStoredProc1);
  CleanTempStoredProc(ADOStoredProc1);

after:(?)

Code: Select all

procedure GetElencoIntercettazioni(ADODataSet: TAdoDataset; IdTarget: Integer; Modalita: string);
Var OraStoredProc1 : TOraStoredProc;
begin
  OraStoredProc1  := TOraStoredProc.create(nil);
  OraStoredProc1.StoredProcName := 'GetElencoIntercettazioni';
  OraStoredProc1.Params.CreateParam(ftInteger, 'p_id_target', ptInput).AsInteger := target;
  OraStoredProc1.Params.CreateParam(ftString, 'p_modalita', ptInput).AsString    := 'F';
  OraStoredProc1.Params.CreateParam(ftCursor, 'ref_rc', ptOutput);
  OraStoredProc1.Prepare;
  OraStoredProc1.ExecProc;
// and now???

Is there something possible?

Thanks in advance,

Matteo

Challenger · Post by **Challenger** » Tue 30 May 2006 13:57

TOraStoredProc component is derived form TDataSet and it will automatically open and fetch data from the cursor parameter that will be met first. If you have several parameters of type cursor you can use the following code:

Code: Select all

OraQuery.Cursor := OraStoredProc1.ParamByName('Cursor2').AsCursor;
OraQuery.Open;

Matteo · Post by **Matteo** » Tue 30 May 2006 15:52

Ok, so I've changed the function:

Code: Select all

procedure TForm1.ExecStored(NewDataset : TADODataSet; target : integer; modalita : string);
Var
  oraquery : TOraQuery;
  orastoredproc1 : TOraStoredProc;
begin
  OraStoredProc1 := TOraStoredProc.Create(nil);
  OraStoredProc1.Connection := OraSession1;
  OraStoredProc1.StoredProcName := 'GetElencoIntercettazioni';
  OraStoredProc1.Params.CreateParam(ftInteger, 'p_id_target', ptInput).AsInteger := target;
  OraStoredProc1.Params.CreateParam(ftString, 'p_modalita', ptInput).AsString    := modalita;
  OraStoredProc1.Params.CreateParam(ftCursor, 'ref_rc', ptOutput);
  OraStoredProc1.Prepare;
  OraStoredProc1.ExecProc;
  OraQuery := TOraQuery.Create(nil);
  OraQuery.Connection := OraSession1;
  OraQuery.Cursor     := OraStoredProc1.ParamByName('ref_rc').AsCursor;
  OraQuery.Open;

  OraStoredProc1.Close;
  OraStoredProc1.Destroy;
end;

But I still couldnt see the dataset outside the function, that, in fact, is what I need to avoid the changing of the unit interface.
Is there a way to copy the data from a TOraQuery object to a TADODataset object?

I've tried to clone:

Code: Select all

 NewdataSet.Clone(TCustomADODataSet(OraQuery));

but it explodes...

Matteo

Challenger · Post by **Challenger** » Wed 31 May 2006 11:37

TADODataset doesn't support copying data from TOraQuery. It's better in this situation to use TADOStoredProc component.

Matteo · Post by **Matteo** » Wed 31 May 2006 12:11

challenger wrote:TADODataset doesn't support copying data from TOraQuery. It's better in this situation to use TADOStoredProc component.

ODAC's performances are clearly the best way (on a stored that returns 8000 rows TADOstored requires about 1minute against 2/3 seconds using the ODAC stored), so I think we will change all the interfaces from TADO to TORA.

But I've a new problem, explained in the next thread...

Matteo

Matteo · Post by **Matteo** » Wed 07 Jun 2006 10:29

Ok, so I'm using Borland Developer Studio and the latest ODAC (trial) to perform the operation:

Code: Select all

OraStoredProc := TOraStoredProc.Create(nil);
OraStoredProc.Connection := OraSession;
OraStoredProc.StoredProcname := 'GetDettaglioIntercettazione';
OraStoredProc.Params.CreateParam(ftInteger, 'p_id_intercettazione', ptInput).AsInteger := IdIntercettazione;
OraStoredProc.Params.CreateParam(ftString, 'p_modalita', ptInput).AsString := Modalita;
OraStoredProc.Params.CreateParam(ftCursor, 'ref_cur', ptOutput);
OraStoredProc.Prepare;
OraStoredProc.ExecProc;
OraDataSet.Connection := OraSession;
OraDataSet.Cursor := OraStoredProc.ParamByName('ref_cur').AsCursor;
OraDataSet.Open;
...

But I've OraStoredProc.RecordCount=1 and OraDataSet.RecordCount=0 ... ds someone know how it's possible?!?

Challenger · Post by **Challenger** » Wed 07 Jun 2006 14:51

As I wrote before TOraStored will automatically open the first cursor and fetch block of data. So you can use it to work with cursor data. You should use the specified example if your procedure has several cursors and you want to fetch data from the second one. Another way is to execute stored procedure using TOraSQL component. Then you can use the specified code for all cursors including the first one.

Matteo · Post by **Matteo** » Wed 07 Jun 2006 15:16

challenger wrote:As I wrote before TOraStored will automatically open the first cursor and fetch block of data. So you can use it to work with cursor data. You should use the specified example if your procedure has several cursors and you want to fetch data from the second one. Another way is to execute stored procedure using TOraSQL component. Then you can use the specified code for all cursors including the first one.

The stored is defined as

Code: Select all

GetElencoIntercettazioni(p_id_target NUMBER, p_modalita VARCHAR2, p_data OUT ref cursor)

Ok, so the code I wrote before was incorrect, but I dont understand where is the error

I've learned that I need

Code: Select all

OraStoredProc.FetchAll := true;

to get all the rows in the stored but I didnt understand how to fetch the rows in the DataSet, I'm sorry...
The following code leaves the dataset empty. I've tried using OraStoredProc.Cursor and get the same result.
Could someone help me, showing me the error?? How could I fetch from the first cursor???

Code: Select all

      OraStoredProc.StoredProcName := 'GetElencoIntercettazioni';
      OraStoredProc.Params.CreateParam(ftInteger, 'p_id_target', ptInput).AsInteger := ID;
      OraStoredProc.Params.CreateParam(ftString, 'p_modalita', ptInput).AsString := 'F';
      OraStoredProc.Params.CreateParam(ftCursor, 'p_data', ptOutput);
      OraStoredProc.FetchAll := true;
      OraStoredProc.Execute;
      OraDataSet.Cursor := OraStoredProc.ParamByName('p_data').AsCursor;
      OraDataSet.Open;

Challenger · Post by **Challenger** » Thu 08 Jun 2006 07:11

Please try to use the following code:

Code: Select all

OraSQL.SQL.Add('begin');
OraSQL.SQL.Add('GetElencoIntercettazioni(:p_id_target, :p_modalita, :p_data);');
OraSQL.SQL.Add('end;');
OraSQL.ParamByName('p_id_target').AsInteger := ID;
OraSQL.ParamByName('p_modalita').AsString := 'F';
OraSQL.ParamByName('p_data').DataType := ftCursor;
OraSQL.Execute;

OraQuery.Cursor := OraSQL.ParamByName('p_data').AsCursor;
OraQuery.Open;

Matteo · Post by **Matteo** » Thu 08 Jun 2006 07:59

OK!
It works!

I've added OraQuery.FetchAll := true; to get all the rows.

But do you know the reason why this doesnt work with TOraStored in place of TOraSQL?

Challenger · Post by **Challenger** » Thu 08 Jun 2006 15:02

The reason of such behavior is that when TStoredProc executes the stored procedure it automatically opens the first cursor. Then it either fetches FetchRows number of rows or all rows if FetchAll property is set to True. Therefore when you assign a cursor to a query and open it, the cursor fetches data from the actual position. So if all rows were fetched by TStoredProc component there would be nothing to fetch for TOraQuery. As result TOraQuery component will be empty.