Board index » delphi » SQL Server response

SQL Server response

Hi Guys,

I recently posed a Q to another newsgroup and on reflection and some more
experimentation I think the problem lies with the SQL Server (IB6.0)

I'm using a standard way of copy a selection query dataset from one database
to another (replica tables in each).
I did however want to multi select from a dbgrid and just select a certain
number of records to pump.
My code I think is correct and whats very strange is one out of five times
this code works perfectly.
The code is:

form1.DbAltGrid1.DataSource.DataSet.DisableControls;
with form1.DbAltGrid1.SelectedRows do
if count > -1 then
begin
   tempbookmark := form1.DbAltGrid1.DataSource.DataSet.GetBookmark;
   for i := 0 to count -1 do
   begin
      if indexof(items[i]) > -1 then
      begin
           form1.DbAltGrid1.DataSource.DataSet.bookmark:=items[i];

reffuarn:=trim(form1.dbaltgrid1.DataSource.DataSet.Fields[0].asstring);
           with ib_cursor1 do begin
               with sql do begin
                  clear;
                  add('select * from integratedlist where uarn = :refuarn');
              end;
          parambyname('refuarn').asstring:=trim(reffuarn);
    end;
    ib_datapump1.Execute;
    ib_transaction1.Commit;
 end;
 end;
 form1.DbAltGrid1.DataSource.DataSet.GotoBookmark(tempbookmark);
 form1.DbAltGrid1.DataSource.DataSet.freeBookmark(tempbookmark);
 end;
 form1.DbAltGrid1.DataSource.DataSet.EnableControls;

What its effectively doing is going from one bookmarked record to the next
on the way it puts a field value into var reffuarn then does the standard
datapump using that field as the select statement.

What I think is happening (and my reason for posting this Q here) is that
when it works its because the records it selects are close to eachother and
the SQL server has time to respond. Usually it pumps the first record in
perfectly and pumps the rest blank! I think its because the loop in delphi
is whizzing around too fast for the select statement to respond. I'm pulling
my hair out over this because it is essential it works but I cannot suss out
why it works sometimes and not other times.

Any thoughts whatsoever will be welcome, I've run out of ideas.

Thanks,

 

Re:SQL Server response


I fixed this, just in case anyone might be interested (or know a better way
or a reason for this)

If after ever datapump execution you do:

ib_connection2.connected:=false;
ib_connection2.connected:=true;

it seems to reset the ib_cursor2 of the destination database and its all ok
after that. Go Figure??

Thanks

Quote
"Andy Murphy" <a...@homedesk.demon.co.uk> wrote in message

news:39d9bee6_1@dnews...
Quote
> Hi Guys,

> I recently posed a Q to another newsgroup and on reflection and some more
> experimentation I think the problem lies with the SQL Server (IB6.0)

> I'm using a standard way of copy a selection query dataset from one
database
> to another (replica tables in each).
> I did however want to multi select from a dbgrid and just select a certain
> number of records to pump.
> My code I think is correct and whats very strange is one out of five times
> this code works perfectly.
> The code is:

> form1.DbAltGrid1.DataSource.DataSet.DisableControls;
> with form1.DbAltGrid1.SelectedRows do
> if count > -1 then
> begin
>    tempbookmark := form1.DbAltGrid1.DataSource.DataSet.GetBookmark;
>    for i := 0 to count -1 do
>    begin
>       if indexof(items[i]) > -1 then
>       begin
>            form1.DbAltGrid1.DataSource.DataSet.bookmark:=items[i];

> reffuarn:=trim(form1.dbaltgrid1.DataSource.DataSet.Fields[0].asstring);
>            with ib_cursor1 do begin
>                with sql do begin
>                   clear;
>                   add('select * from integratedlist where uarn =
:refuarn');
>               end;
>           parambyname('refuarn').asstring:=trim(reffuarn);
>     end;
>     ib_datapump1.Execute;
>     ib_transaction1.Commit;
>  end;
>  end;
>  form1.DbAltGrid1.DataSource.DataSet.GotoBookmark(tempbookmark);
>  form1.DbAltGrid1.DataSource.DataSet.freeBookmark(tempbookmark);
>  end;
>  form1.DbAltGrid1.DataSource.DataSet.EnableControls;

> What its effectively doing is going from one bookmarked record to the next
> on the way it puts a field value into var reffuarn then does the standard
> datapump using that field as the select statement.

> What I think is happening (and my reason for posting this Q here) is that
> when it works its because the records it selects are close to eachother
and
> the SQL server has time to respond. Usually it pumps the first record in
> perfectly and pumps the rest blank! I think its because the loop in delphi
> is whizzing around too fast for the select statement to respond. I'm
pulling
> my hair out over this because it is essential it works but I cannot suss
out
> why it works sometimes and not other times.

> Any thoughts whatsoever will be welcome, I've run out of ideas.

> Thanks,

Other Threads