I searched the forum with no success.

UniMainModule has a local Database connection, ServerModule has Global Database connection.

I need to Backup Database in code and cannot circumvent Lock Issues - Please advise - Thanks

 //////////////////////////////////////////////////////////////////////////////
 try
   UniMainModule.CloseTables(Self); // ALL Of My Tables Are Closed along with Local Database
                                                           //  (would rather Read Only Lock)
   LockCount:= 0;
   LockRequest:
   with SrvDatabase do begin

     //////////////////////////////////////////////////////////////////////////
     try
       StartTransaction(LockTables);

       ////////////////////////////////////////////////////////////////////////
       if SrvQry1.Active then SrvQry1.Close;
       SrvQry1.SQL.Clear;
       SrvQry1.Params.Clear;
       SrvQry1.SQL.Add('BACKUP DATABASE '+ProjectName+' '+
                       'AS "'+BackupName+'" '+
                       'TO STORE "'+ProjectName+'-BK" '+
                       'INCLUDE CATALOG ;');
       SrvQry1.ExecSQL;

       ////////////////////////////////////////////////////////////////////////
       Commit;
       Result:= True;
       LockFailedFlag:= False;

     except
       on E: Exception do begin
         Inc(LockCount);
         s:= E.Message;
         LockFailedFlag:= True;
         Rollback;
       end;
     end;

   end; // MyDatabase
   if LockFailedFlag = True then begin
     if LockCount < 5 then GoTo LockRequest;
   end;

 finally
   SetLength(LockTables, 0);
   UniMainModule.OpenTables(Self); // ReOpen Local Database and ALL of My Tables
 end;

Andrew


I can't test anything in Delphi with your database because I'm on D2007 - ansi not unicode

<<UniMainModule has a local Database connection, ServerModule has Global Database connection.>>

I have no idea what this means

<<with SrvDatabase do begin>>

How is this related to the database you want to back up?

<<StartTransaction(LockTables);>>

Why?

<<SrvQry1>>

Session, database, any other useful info

Roy Lambert

Andrew,

You don't need to put the database in a transaction as EDB will handle putting a read lock on the database for you.  

I'm assuming that SvrQry1 is already connected to the same session as SrvDatabase so you can simplify the process significantly.  Set your default return value to false and loop through up to 5 times and break if the backup is successful on any of the attempts.

Result := False;
try
  if SrvQry1.Active then SrvQry1.Close;
  UniMainModule.CloseTables(Self); // ALL Of My Tables Are Closed along with Local Database
                                                          
  SrvQry1.SQL.Clear;
  SrvQry1.Params.Clear;
  SrvQry1.SQL.Add('BACKUP DATABASE '+ProjectName+' '+
                  'AS "'+BackupName+'" '+
                  'TO STORE "'+ProjectName+'-BK" '+
                    'INCLUDE CATALOG ;');

   for TryCount := 1 to 5 do begin
   try
     SrvQry1.ExecSQL;
     Result:= True;
   except
     on E: Exception do begin
       s:= E.Message;
     end;
   end;

  if Result then break;
  end;

finally
  UniMainModule.OpenTables(Self); // ReOpen Local Database and ALL of My Tables
end;

Roy, We have a Server with it's own EDB Engine/Database/Session which I will refer to as GLOBAL (this is where the Backup and Restore are executed).

And we have a Local application with it's own Database/Session (connected to the Server) which I will refer to as LOCAL.

If I remove StartTransaction/Commit/Rollback on the BACKUP code then all is fine (no lock issue [it must operate in readonly mode]) - so far so good.

Now for RESTORE I close LOCAL Tables/Database, I have experimented with closing the SERVER Database as you can see below but still to no avail - LOCK ISSUE remains

Incidentally USER has BACKUP/RESTORE privileges.

Tim, please advise.

function TUniServerModule.RestoreDatabase(UserName: String): Boolean;
var
 s: String;
begin

 //////////////////////////////////////////////////////////////////////////////
 try
   Result:= False;
   s:= IntToStr(SrvDatabase.DataSetCount);
   SrvDatabase.CloseDataSets;
   SrvDatabase.Close;

   ////////////////////////////////////////////////////////////////////////////
   if SrvQry1.Active then SrvQry1.Close;
   SrvQry1.ReadOnly:= True;
   SrvQry1.SQL.Clear;
   SrvQry1.Params.Clear;
   SrvQry1.SQL.Add('RESTORE DATABASE '+ProjectName+' '+
                   'FROM "'+RestoreBackupName+'" '+
                   'IN STORE "'+ProjectName+'-BK" '+
                   'INCLUDE CATALOG ;');
   SrvQry1.ExecSQL;
   Result:= True;

 except
   on E: Exception do begin
     s:= E.Message;
   end;
 end;
 SrvDatabase.Open;

 ...

end;

Andrew


As Terry says - no need for the transaction, and as you've discovered that was the source of your lock problem. Looking at this and other examples I don't think you fully understand what a transaction is for. The purpose is to make ALTERATIONS (ie insert, delete or update) to multiple rows, possibly multiple tables act as atomic. If you're reading tables its not needed (eg NextDocNum), its not needed if you're only altering one row in one table either - by its nature that is already atomic.

Have you tried running your restore in EDBManager - if that works its your code. If it fails please post the error message.

Roy Lambert

Roy, I do understand transactions, I was being through.

Restore works in EDBMgr because there are no other sessions to contend with (incidentally if you move columns up and down in EDBMgr ALTER Table it is buggy), my problem is a LOCAL session asks the SERVER to do a Restore.

I have asked Tim to comment.   

Hi Andrew.

> incidentally if you move columns up and down in EDBMgr ALTER Table it is buggy

This is a known issue and Tim is planning on addressing this in the next EDB build.

> Restore works in EDBMgr because there are no other sessions to contend with (incidentally if you move columns up and down in EDBMgr ALTER Table it is buggy), my problem is a LOCAL session asks the SERVER to do a Restore.

A backup can be done while there are other sessions connected, but a restore cannot.  You will need to ensure that you have EXCLUSIVE access to the database and perform the restore from that one active session.

 

Thank you Terry for your comments, can you please give me an example on "EXCLUSIVE access to the database".

All my research only shows Exclusive Option on TEDBTable, as you can see I am using TEDBQuery, I see no examples on Database Exclusive code - please advise.

Andrew Hill wrote:

> All my research only shows Exclusive Option on TEDBTable, as you can see I am using TEDBQuery, I see no examples on Database Exclusive code - please advise.

If you try to do the restore from the TEDBQuery level, that query object is connected to the same database that you are trying to restore.  And because it's in use, it can't overwrite itself while it's open.

So, close EVERYTHING except the session and do the restore from the TEDBSession.Execute function.  Personally, I would actually close the session, reopen, and then do the restore from the session level to make sure that I didn't forget to close everything.

Terry, Thanks for the pointer