At 11:48 PM 14-02-02 +0000, Jose Manuel wrote:

>Hi list...
>
>I'm still converting from BDE... getting to know IB.
>
>I need to update an entire table from a query's result:
>
>I used to have this query (In QRecalc's SQL):
>
>SELECT MOV_ALM.Producto , MOV_ALM.Almacen , MOV_ALM.Presentacion,
> SUM( MOV_ALM.Cantidad * CONCEPTS.Multiplicador ) AS SUM_OF_Cantidad
>FROM MOV_ALM , CONCEPTS, PRODUCTS
>WHERE
> ( ( MOV_ALM.Tipo_de_Movimiento = CONCEPTS.Numero ) AND
> ( MOV_ALM.Producto = PRODUCTS.Producto ) AND
> ( CONCEPTS.Tipo = 'Almacén' ) AND
> ( MOV_ALM.Aplicada = 'T' ) AND
> ( PRODUCTS.Inventariable = 'T' ) )
>GROUP BY
> MOV_ALM.Producto , MOV_ALM.Almacen, MOV_ALM.Presentacion;
>
>And the procedure:
>
>TRecalc1 : TIBOTable.
>
> QRecalc1.First;
> while not QRecalc1.EOF do
> begin
> if not TRecalc1.FindKey([QRecalc1Producto.AsString,
> QRecalc1Almacn.AsString, QRecalc1Presentacin.AsString])
> then
> begin
> TRecalc1.Insert;
> TRecalc1Producto.AsString := QRecalc1Producto.AsString;
> TRecalc1Almacn.AsString := QRecalc1Almacn.AsString;
> TRecalc1Presentacin.AsString :=
> QRecalc1Presentacin.AsString;
> end
> else
> TRecalc1.Edit;
> TRecalc1Existencia.AsFloat := QRecalc1SUMOFCantidad.AsFloat;
> TRecalc1.Post;
>
> QRecalc1.Next;
> end;
>
>which does work, but takes a lot of time...
>
>How can I make this work faster and only in the server side? (I don't
>need to take any decisions nor parameters)

You could (and probably will, eventually) re-architect your client interface so that the SQL from your client application can be simplified. But, considering your immediate question, the following procedure will accept parameters from your datasets in their current state and process one set of input data:

create procedure SomeProc (newProducto varchar(n),
newAlmacn varchar(n), newPresentacin varchar(n),
SUMOFCantidad numeric (m,n))
as
begin
if (not (exists (select Producto from aTable
where Producto = :newProducto
and Almacn = :newAlmacn
and Presentacin = :newPresentacin))) then
insert into aTable(Producto, Almacn, Presentacin)
values ( :newProducto, :newAlmacn, :newPresentacin);
else
update aTable
set Existencia = :SUMOFCantidad
where Producto = :newProducto
and Almacn = :newAlmacn
and Presentacin = :newPresentacin

end

With your current architecture, your client program would walk through the dataset in a "while not EOF" loop and call the procedure with the latest set of input parameters each time it passes to another row. The whole operation would be done inside a single transaction, with your client-side procedure calling Commit on the transaction once the dataset loop was complete.

The refined "server-centric" architecture would not require a dataset on the client side at all. You would use a FOR SELECT...DO... loop in a stored procedure to select the dataset on the server side only and to walk through it, passing the columns of interest into procedure variables, performing the calculations and doing the condition insert/update logic.

regards,
Helen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org
_______________________________________________________