SQL query doesnt finish in delphi

Question

Im using SQL Server 2005 with Delphi 2010

I have a table ventas with id_venta (PK)= id_sale, total(of the sale), (more values), estado=state(active/inactive) and cumulative.

The table registers all the sales of articles of a store, and i need to register the cumulative sum of the total row by row. Im using the state field to specify the sales made in the shift if there were more than one shift in the day. Im using this query:

Declare @id integer; 
set @id=(Select min(id_venta) from ventas where estado='activo');

while(select @id)<=(Select max(id_venta) from ventas) 
  begin 
    update ventas set acumulado=(select sum(total) from ventas 
    where id_venta<=@id and estado='activo') where id_venta=@id
    select @id=@id+1 
  end

This query does exacly what i want when i run it on SQL but when i do it on delphi it only gives me the comulative of about 151 rows of 431, not finishing.

This is my delphi code:

conect.Q_equivalencias.Active:=false;
conect.Q_equivalencias.SQL.Clear;
conect.Q_equivalencias.SQL.Add('Declare @id integer; set @id=(Select min(id_venta) from ventas where estado='+char(39)+'activo'+char(39)+' );');
conect.Q_equivalencias.SQL.Add('while(select @id)<=(Select max(id_venta) from ventas) begin');
conect.Q_equivalencias.SQL.Add('update ventas set acumulado=(select sum(total) from ventas  ');
conect.Q_equivalencias.SQL.Add('where id_venta<=@id and estado='+char(39)+'activo'+char(39)+') where id_venta=@id');
conect.Q_equivalencias.SQL.Add('select @id=@id+1 end');
conect.Q_equivalencias.ExecSQL;

What do i have to do so my query in Delphi finishes?

Edit:

Strange thing just happened, i tried with conect.Q_equivalencias.Open; insted of conect.Q_equivalencias.ExecSQL; and ofcourse it throw me an error creating cursor handler but it finished the query, all rows were updated, why is that?

Tried many things and it seems that my query on delphi only updates 152 rows...

TQuery, TDataSource, conected by ODBC, i have many more insert, update, select querys, but this is the only one gifing me a problem, the query itself does what is has to do in SQL Management Studio Express but in Delphi 2010 doesnt finish. — Edgar Holguin
– Edgar Holguin, Commented Sep 6, 2012 at 19:26
I've seen a similar problem that was to do with the length of time the query took, where if it took over a certain amount it would just hang indefinately. It was a couple of years ago and I can't remember how we solved it. — James
– James, Commented Sep 7, 2012 at 8:08

Lucero · Accepted Answer · 2012-09-09 09:54:44Z

2

Maybe adding a SET NOCOUNT ON; at the beginning of your query may solve the issue.

That said, I'd use a single UPDATE statement (or even better a VIEW or computed column) instead of such an odd query. I'm not sure if the query actually does what you really want it to do, but the following single statement should be doing the equivalent:

UPDATE ventas
    SET acumulado=(
        SELECT SUM(v.total)
        FROM ventas v
        WHERE v.id_venta<=ventas.id_venta AND estado='activo'
    ) WHERE id_venta>=(
        SELECT MIN(id_venta)
        FROM ventas
        WHERE estado='activo'
    );

answered Sep 9, 2012 at 9:54

Lucero

60.4k9 gold badges127 silver badges154 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Edgar Holguin Over a year ago

the query works, at least in SQL and not in Delphi, but im not really good at SQL so compared to this query it is an odd query... yours works perfect, thanks.

Collectives™ on Stack Overflow

SQL query doesnt finish in delphi

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related