0

At the execution of a small t-sql loop (while - begin - end) in native MSSQL connection and JDBC getting a strange results. In MSSQL SMS all works as expected where via JDBC getting just the first loop.

declare @i int = 1
while @i <= 10
  begin
    select @i
    set @i=(@i+1)
  end

Result in SMS is: 1 2 3 ... 10

Where in via jdbc just 1

Highly appreciate any help !

Thank you

Alex K
  • 22,315
  • 19
  • 108
  • 236
Daniel Nik
  • 3
  • 3
  • You could try putting `SET NOCOUNT ON;` at the beginning of the batch and see if that helps. (Details [here](https://msdn.microsoft.com/en-CA/library/ms189837.aspx).) – Gord Thompson Nov 06 '16 at 20:20
  • Thank you but ... didn't help – Daniel Nik Nov 06 '16 at 20:29
  • 1
    Are you checking for multiple result sets, as described [here](http://stackoverflow.com/q/9696572/2144390)? – Gord Thompson Nov 06 '16 at 20:39
  • Hi Gord, I'm creating a temp table, then need to update several rows regarding the values in the temp table i.e. – Daniel Nik Nov 06 '16 at 20:55
  • {select rank() over (order by table.date) as id ,id ,entrance_date as to_date ,entrance_date as from_date ,last_id as ll_id into #tmp from table where (some filter) declare @i int, @n int select @i = min(id), @n = max(id) from #tmp while @i <= @n begin update #tmp set from_date = (select top 1 ...where ... order) ,ll_id = (select top 1 ...where ... order) set @i = @i+1 end select * from #tmp} – Daniel Nik Nov 06 '16 at 21:01
  • 1
    Please show the Java code you use. – Mark Rotteveel Nov 07 '16 at 14:24
  • Hi Mark, there is not java code. It's a pure SQL via jdbc connectivity used by Jasper reports. – Daniel Nik Nov 07 '16 at 15:50

1 Answers1

0

you can try

int size = 1
while(rs.next()){ 
     if(rs != null){

        rs.beforeFirst();  
        rs.last();  
        size = rs.getRow();  

     }

     System.out.println("cols = " + size);
}
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Raghu Ariga
  • 1,059
  • 1
  • 19
  • 28
  • 2
    Hi Raghu, that will require a code change where I'm not allowed. The Q is executed in <![CDATA[ ... ]]> as a regular tsql script – Daniel Nik Nov 06 '16 at 20:31