DBISAM ver. 4.25
I am trying to copy data from one table to another (both tables have the same structure).  The data transfer to the Archive table seems to work OK but when the process ends I get error
Error 9494 too many blob open on <table name>

Please note I am new to DBISMAS and the function I am providing is a work in progress but I do not seem to know why it is causing the error. Note:  the function is based on a thread in
these newsgroups.


function TForm1.f_ArchiveRecords3() : Integer;
var
 qryGetRowsToTransfer : TDBISAMQuery;
 qryTransfer : TDBISAMQuery;
 intNumBeforeCommit,intLoopCounter,intRecordCount : INTEGER;

 Start, Stop: Cardinal;
begin
 //initialze some vars
 Result := 0;
 intNumBeforeCommit := 1000;
 intLoopCounter := 0;
 intRecordCount := 0;

 qryGetRowsToTransfer := TDBISAMQuery.Create(NIL);

 //Get all unique rows that must be transfered
 with qryGetRowsToTransfer do begin
   DatabaseName := Form1.DBISAMDatabase1.DatabaseName;
   SQL.Clear;
   SQL.Add('SELECT * FROM Specs ');
   SQL.Add('WHERE SpecID NOT IN (SELECT SpecID FROM t_SpecArchive) ');
   SQL.Add('AND SpecID IN (SELECT SpecID FROM Specs WHERE state IN (11));');

   Prepare;
 end;

 //Run query alert if error
 try
   qryGetRowsToTransfer.Active := TRUE;
   except
     on e : exception do begin
       showmessage(e.message);
       exit;
     end;
 end;

 //For small record sets we will have only one commit at EOF
 if qryGetRowsToTransfer.RecordCount < intNumBeforeCommit then
   intNumBeforeCommit := qryGetRowsToTransfer.RecordCount;

 //Prepare query that will do the transfering
 qryTransfer := TDBISAMQuery.Create(NIL);
 try
   with qryTransfer do begin
     RequestLive := True;
     DatabaseName := Form1.DBISAMDatabase1.DatabaseName;
     SQL.Clear;
     SQL.Add('INSERT INTO t_SpecArchive ');
     SQL.Add('SELECT * FROM Specs ');
     SQL.Add('WHERE SpecID = :specid;');
     Prepare;
   end;

   Start := TimeGetTime;

   qryGetRowsToTransfer.First;
   qryTransfer.Database.StartTransaction(nil);
   while not qryGetRowsToTransfer.EOF do begin
     //Transaction loop
     for intLoopCounter := 1 to intNumBeforeCommit do begin
       try
         with qryTransfer do begin
           ParamByName('specid').AsString := qryGetRowsToTransfer.FieldByName('SpecID').AsString;
           Caption := Format('%d records processed ', [Result]);
           ExecSQL;
         end;
         Inc(Result);
         //Inc(intRecordCount);
       except
         on E: Exception do
           begin
           //check for duplicate key // based on query above I do
           //not need this code anymroe
           if (E is EDBISAMEngineError) and (EDBISAMEngineError(E).ErrorCode = 9729) then
             begin
               //Inc(intRecordCount);  //skip and the loop
             end
           else  //Not sure what other exceptions I should handle here
           begin
             //commit records in the transaction cache
             qryTransfer.Database.Commit;
             raise;
           end;
         end;
       end;
       qryGetRowsToTransfer.Next;
     end;

     qryTransfer.Database.Commit(True);
     qryTransfer.Database.StartTransaction(nil);
   end;
 finally

   if qryTransfer.Database.InTransaction then
     qryTransfer.Database.Commit(True);

    Stop := TimeGetTime;
    Caption := Format('%d records inserted in %s secs',
            [Result, FormatFloat('#0.000', (Stop-Start)/1000)]);

   if Assigned(qryTransfer) then qryTransfer.Free;
 end;

 qryGetRowsToTransfer.Free;
end;




Any help would be greatly appreciated.

Thanks Silven

silven


I don't know the answer to your question but I can't understand why you're doing it the way you are.

 qryGetRowsToTransfer seems to be able to do a select from Specs for all the info you want transfering. You then loop through that and transfer a row at a time using qryTransfer. Why not just do the whole lot in one bash?

Untested code just cut from different bits of your post

     SQL.Add('INSERT INTO t_SpecArchive ');
  SQL.Add('(SELECT * FROM Specs ');
   SQL.Add('WHERE SpecID NOT IN (SELECT SpecID FROM t_SpecArchive) ');
   SQL.Add('AND SpecID IN (SELECT SpecID FROM Specs WHERE state IN (11)));');


Roy Lambert

Why the loop -- The reason is because my client would like to have an indication of what record is being transfered.   

But I do agree it can be done very quickly with the query you provided.

I need to find away to indicate which record is being worked on.

Any ideas... my main problem is the blob issue


Thanks Silven

silven


If all you need is an indication use a progress bar and the OnQueryProgress event


Roy Lambert

Totally agree,  I am going to go back to my client and ask if he will accept a progress bar instead of scrolling SpecIDs.

I am still wondering where the blog errors are coming from and how to avoid them in the future.


Thanks

Silven,

<< DBISAM ver. 4.25 >>

Which build are you using ?

<< I am trying to copy data from one table to another (both tables have the
same structure).  The data transfer to the archive table seems to work OK
but when the process ends I get error Error 9494 too many blob open on
<table name >>

Where in the code that you posted are you getting the 9494 error ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim

I am using build 3.

I seem to be getting the error when the function has completed the 3500 enties and has returned to the calling procedure


Thanks,
Silven

I restructured my query to use the an INSERT INTO as mentionned above and I do not get the 9494 error.  

Is there away to get the number records inserted when using the INSERT INTO method.

I also implemented  a progress bar on the  OnQueryProgress is it normal for the below query to progress from 0 to 100 twice.

SQL.Add('INSERT INTO t_SpecArchive ');
SQL.Add('SELECT * FROM Specs ');
SQL.Add('WHERE SpecID NOT IN (SELECT SpecID FROM t_SpecArchive) ');
SQL.Add('AND SpecID IN (SELECT SpecID FROM Specs WHERE state IN (11));');

my simple progress function...
procedure TForm1.QueryProgress(Sender: TObject;PercentDone: Word; var AbortQuery: Boolean);
begin
 if ( ProgressBar1 <> nil ) then
   begin
        ProgressBar1.Position := PercentDone;
   end
end;


thanks,
silven

silven

>Is there away to get the number records inserted when using the INSERT INTO method.

Try RowsAffected after the query

>I also implemented a progress bar on the OnQueryProgress is it normal for the below query to progress from 0 to 100 twice.

Probably because there are two queries that actually run - the select and the insert

Roy Lambert

Thanks for you help Roy