At 04:36 PM 13/03/2003 +0000, you wrote:

>Hi,
>I'm trying to port our applications from IBX (6.03) to IBObjects (I
>use Firebird 1.0.2.908-Win32, Delphi 6 Enterprise).
>One of these is an applications that copies data from different
>versions of our database (we cannot use datapump directly).
>After the porting toward IBObjects, this program works but has became
>slower than the original.
>In particular, there seems to be a slower execution of the following
>procedure, that we use to transfer one record from the source database
>to the destination database.
>
>// ORIGINAL VERSION (IBX)
>procedure TDMInit.CopyParameters(Source: TIBQuery; Destination:
>TIBStoredProc);
>var
> I : Integer;
>begin
> with Destination do
> for I := 0 to ParamCount - 1 do
> if Params[I].ParamType = ptInput then
> begin
> Params[I].Clear;
> if (Source.FindField(Params[I].Name) <> nil) then // If the
>destination table has some new field we must ignore it
> Params[I].Assign(Source.FieldByName(Params[I].Name));
> end;
>end;
>
>
>// NEW VERSION (IBObjects)
>procedure TDMInit.CopyParameters(Source: TIB_Cursor; Destination:
>TIB_StoredProc);
>var
> I : Integer;
>begin
> with Destination do
> begin
> Params.BeginUpdate;
> try
> Params.ClearBuffers(rsNone);
> for I := 0 to ParamCount - 1 do
> begin
> if Source.FindField(Params[I].FieldName) <> nil then
>
>Destination.Params[I].Assign(Source.FieldByName(Params[I].FieldName));
> end;
> finally
> Params.EndUpdate(True);
> end;
> end;
>end;
>
>Can someone please tell me if there is a faster way to pass fields
>values from a source query to a destination stored procedure?

There are a few wrong assumptions in your new procedure, but it doesn't
matter - because your CopyParameters procedure is quite unnecessary in IBO
(and the wrong thing to do for SQL, because the SP's input parameters are
compiled, not dynamic!).

Preparing the ib_storedproc fetches the parameter definitions from the
server and places them into the Params[] array. IBO then "knows" their
names, types and input order. You only have to prepare once.

All you need to do for your pump is something similar to the following;

procedure TDMInit.PumpData(Source: TIB_Cursor; Destination:
TIB_StoredProc);
var
I : Integer;
DestFieldName: string;
begin
Source.First; // opens the ib_cursor at the first row, preparing it if
necessary
with Destination do
begin
if not Prepared then Prepare; // gets the SP's Params the first time
while not Source.EOF do
begin
// IBO will take care of clearing the Params buffer if it needs to;

for I := 0 to ParamCount - 1 do
begin
// assuming you are sure that your Source and your destination
Field Names
// and parameter names have matching data types - otherwise you are in
// trouble
DestFieldName := Params[I].FieldName;
if Source.FindField(DestFieldName) <> nil then
begin
if Source.Fields[DestFieldName].IsNull then
begin
Params[I].Clear; // set Null
Continue; // loops back to the next param
end;
if Params[I] is TIB_ColumnBlob then
TIB_ColumnBlob(Params[I]).Assign(Source.Fields[DestFieldName])
else
Params[I].Value := Source.Fields[DestFieldName].Value;
end
else
Params[I].Clear; // sets the value to Null if no matching
Source fieldname
end;
Execute;
Source.Next;
end;
end;
end;

Helen