Ndtyjky

I'm trying to get data from SQLSERVER using TADOQUERY linked to my database , using this request

declare @num_p varchar(9)

declare cur1 cursor for select do_piece from F_DOCENTETE where (DO_Piece like ('____00')or DO_Piece like ('____A0')or DO_Piece like ('____B0') 

or DO_Piece like ('____B0')or DO_Piece like ('____C0') OR DO_Piece like ('____D0')OR DO_Piece like ('____E0')OR DO_Piece like ('____F0')OR DO_Piece like ('____G0')

OR DO_Piece like ('____H0')OR DO_Piece like ('____I0')OR DO_Piece like ('____J0')OR DO_Piece like ('____K0')OR DO_Piece like ('____L0')OR DO_Piece like ('____M0')

OR DO_Piece like ('____N0')OR DO_Piece like ('____O0')OR DO_Piece like ('____P0')OR DO_Piece like ('____Q0')OR DO_Piece like ('____R0')OR DO_Piece like ('____S0')

OR DO_Piece like ('____T0')OR DO_Piece like ('____U0')OR DO_Piece like ('____V0')OR DO_Piece like ('____W0')OR DO_Piece like ('____X0')OR DO_Piece like ('____Y0')

OR DO_Piece like ('____Z0')OR DO_Piece like ('____D0'))and DO_Date between ('01/01/2018') and('31/12/2018') and do_type in ('16','17')

open cur1

fetch cur1 into @num_p 

while @@FETCH_STATUS=0

begin

select  f_docligne.do_piece,f_docligne.ct_num,F_COMPTET.CT_Intitule,AR_Ref, DL_Design,DL_Qte, DL_PrixUnitaire,DL_PUDevise,DL_Frais,DL_PUTTC, DL_MontantHT,

 DL_MontantTTC,DO_Ventile,DO_Cours,f_docligne.do_date from F_DOCLIGNE,F_DOCENTETE,F_COMPTET

 WHERE  f_docligne.DO_Piece=F_DOCENTETE.DO_Piece and F_DOCLIGNE.DO_Type=F_DOCENTETE.DO_Type and f_docligne.CT_Num=F_DOCENTETE.DO_Tiers 

 and F_DOCENTETE.DO_Tiers=F_COMPTET.CT_Num and f_docligne.DO_Piece like((select LEFT(@num_p,5))+'_') order by f_docligne.DO_Piece

fetch cur1 into @num_p 

end

close cur1

deallocate cur1

the problem is that in sqlserver i see all records while fetching
but in delphi app it returns only last fetch , so how can i get it as SQLSERVER doest , shows data as it is displayed

Your SQL statement returns multiple resultsets, so you need to get each resultset.
I used next approach in old Delphi7 application and it worked for me. All you need is to call NextRecordset() method.

Delphi part:

procedure TForm1.btnOpenClick(Sender: TObject);

var

   s: string;

   rs: _RecordSet;

   n: Integer;

begin

   // Here, I assume that you have TADOQuery component already created,

   // with correct Connection or ConnectionString property.

   s := 'Your SQL statement';

   try

      if qry.Active then qry.Close;

      qry.SQL.Clear;

      qry.SQL.Add(s);

      qry.Open;

   except

      on E: Exception do begin

         ShowMessage('Error ' + E.Message);

         Exit;

      end{on};

   end{try};



   // Consume multiple resultsets

   rs := qry.Recordset;

   repeat

      while not rs.Eof do begin

         // Do what you want with fields values.

         // ShowMessage(rs.Fields['FieldName'].Value);

         rs.MoveNext;

      end{while};

      rs := qry.NextRecordset(n);

   until (rs = nil);



   //

   qry.Close;

   ShowMessage('OK');

end;

This does not answers the question how to get your query as SQL Server does , but you could retrieve your results by rewriting your query so you dont need a cursor.

Its hard to alter a query when little is known about the schema, but I tried anyway.

You should really use the newer join style, it is around for more than 20 years now and is much more readable.

Also use date formats that are region independant, like 'yyyyMMdd' for example.

This should get you started, but you need to fill in a lot of ??? off course

select  L.do_piece, L.ct_num, E.CT_Intitule, ???.AR_Ref, ???.DL_Design, ???.DL_Qte, ???.DL_PrixUnitaire, ???.DL_PUDevise, 

        ???.DL_Frais, ???.DL_PUTTC, ???DL_MontantHT, ???.DL_MontantTTC, ???.DO_Ventile, ???.DO_Cours, L.do_date 

from    F_DOCLIGNE L

  inner join F_DOCENTETE E on L.DO_Piece = E.DO_Piece

                          and L.DO_Type = E.DO_Type

                          and L.CT_Num = E.DO_Tiers

  inner join F_COMPTET P on E.DO_Tiers = P.CT_Num



where (E.DO_Piece like ('____00') or E.DO_Piece like ('____A0') or E.DO_Piece like ('____B0') 

       or E.DO_Piece like ('____B0') or E.DO_Piece like ('____C0') OR E.DO_Piece     like ('____D0') OR E.DO_Piece like ('____E0') OR E.DO_Piece like ('____F0') OR     E.DO_Piece like ('____G0')

       OR E.DO_Piece like ('____H0') OR E.DO_Piece like ('____I0') OR E.DO_Piece like ('____J0') OR E.DO_Piece like ('____K0') OR E.DO_Piece like ('____L0') OR E.DO_Piece like ('____M0')

       OR E.DO_Piece like ('____N0') OR E.DO_Piece like ('____O0') OR E.DO_Piece like ('____P0') OR E.DO_Piece like ('____Q0') OR E.DO_Piece like ('____R0') OR E.DO_Piece like ('____S0')

       OR E.DO_Piece like ('____T0') OR E.DO_Piece like ('____U0') OR E.DO_Piece like ('____V0') OR E.DO_Piece like ('____W0') OR E.DO_Piece like ('____X0') OR E.DO_Piece like ('____Y0')

       OR E.DO_Piece like ('____Z0') OR E.DO_Piece like ('____D0')

      )

and   E.DO_Date between ('20180101') and('20181231') 

and   E.do_type in ('16','17')



order by L.DO_Piece

or as suggested by @IvanStarostin you could do

select  L.do_piece, L.ct_num, E.CT_Intitule, ???.AR_Ref, ???.DL_Design, ???.DL_Qte, ???.DL_PrixUnitaire, ???.DL_PUDevise, 

        ???.DL_Frais, ???.DL_PUTTC, ???DL_MontantHT, ???.DL_MontantTTC, ???.DO_Ventile, ???.DO_Cours, L.do_date 

from    F_DOCLIGNE L

  inner join F_DOCENTETE E on L.DO_Piece = E.DO_Piece

                          and L.DO_Type = E.DO_Type

                          and L.CT_Num = E.DO_Tiers

  inner join F_COMPTET P on E.DO_Tiers = P.CT_Num



where E.DO_Piece like '___[0A-Z]0'

and   E.DO_Date between ('20180101') and('20181231') 

and   E.do_type in ('16','17')



order by L.DO_Piece