Page 1 of 1
ODAC DataSet to TADODataSet
Posted: Tue 30 May 2006 13:09
by Matteo
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);
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
Posted: Tue 30 May 2006 13:57
by Challenger
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;
Posted: Tue 30 May 2006 15:52
by Matteo
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
Posted: Wed 31 May 2006 11:37
by Challenger
TADODataset doesn't support copying data from TOraQuery. It's better in this situation to use TADOStoredProc component.
Posted: Wed 31 May 2006 12:11
by Matteo
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
Posted: Wed 07 Jun 2006 10:29
by Matteo
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?!?
Posted: Wed 07 Jun 2006 14:51
by Challenger
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.
Posted: Wed 07 Jun 2006 15:16
by Matteo
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
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;
Posted: Thu 08 Jun 2006 07:11
by Challenger
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;
Posted: Thu 08 Jun 2006 07:59
by Matteo
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?
Posted: Thu 08 Jun 2006 15:02
by Challenger
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.