• Welcome, Guest. Please login.
 

Automatically Backup Database Every Five Minutes Using slCopyDatabase

Started by Fredrick Ughimi, June 08, 2011, 02:38:22 am

Previous topic - Next topic

Fredrick Ughimi

Hello,

I' d like use slCopyDatabase to backup my database automatically, say every five 5 minutes. At the moment I only provided manual backup of the database.

Any ideas?
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fred Meier

I assume this is a server/remote database?  If yes then there are several ways:

1. Have a monitoring/admin type client computer that runs a process all the
   time and based on a timer awakes every 5 minutes and does the slCopyDatabase.

2. Have a process always running on the server that based on a timer awakes every 5
   minutes and does the slCopyDatabase.  May want this process to be a service.

3. Activate the Start Up Exit in SQLiteningServer and then write a separate thread that
   awakes every 5 minutes. 

There are probable other ways as well.  Numbers 1 and 2 are pretty
straight forward but require human administration (unless 2 is a service). 
Number 3 is probable the best, most fool-proof, but requires understanding
and activating SQLiteningServer Exits. 



Fredrick Ughimi

Hi Fred,

Thank you for your response.

>>Number 3 is probable the best, most fool-proof, but requires understanding and activating SQLiteningServer Exits. 

I 'd prefer option 3 for obvious reasons. Where can I read up SQLiteningServer Exits? Checked the help file. Wasn't much help.
Or better still can you give me a code sample of how it should look like?

Best regards,
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

Hello,

Maybe my prospective client is asking too much? Or is automatic backup not the usual practice?

I did provide manual backup, automatic backup when the program starts and automatic backup when the program exits. Thought these were okay for any application. No, they want backup done every 5 minutes or so.

Kind regards,


Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fred Meier

There are two ways to do this --- one is to use an Exit the other is to
use the Monitor.  I do not have a preference. 
QuoteSQLiteningServer includes the concept of "Exits".  The term 'Exit' comes
   from the main program (SQLiteningServer.Exe) 'Exiting' to a sub program
   (SQLiteningServerExits.Dll) which contains your custom code.  There currently are 5 exits
   available and are controlled by the following conditional compile equates:
      %CompileExit_1_Connect, %CompileExit_2_Disconnect, %CompileExit_3_Access
      %CompileExit_101_Start, %CompileExit_102_Stop
   The default is that they are all %False. Because the exits are activated only
   if you want them, there is no overhead for the ones who don't use them.

QuoteSQLiteningServer has ability to have a user coded monitor process running
   along with the server process.  SQLiteningServer will now shell to the
   SQLiteningServerMonitor.Exe process when it starts.  This process will run
   as long as the server is running, it will exit when server is stopped.  If
   this monitor process is not required then just delete
   SQLiteningServerMonitor.Exe. 
    Purpose: This is the place you can code things like writing back
             up copies or do any other database or file maintenance
             using all the power of SQLitening in local mode. 


The first step is to recompile SQLiteningServer to activate either the Exit or the Monitor.
   Change %CompileExit_101_Start = %False to %True to activate Exit
   Change %CompileStartServerMonitor = %False to %True to activate Monitor
Next is to compile either SQLiteningServerExits.Bas or SQLiteningServerMonitor.Bas.
Last is to Stop and Start the server.
Below is sample code for both ways --- use one or the other.


'  ==========================================================================
'  |                                                                        |
'  | SQLitening Server Exits                                                |
'  |                                                                        |
'  ==========================================================================
'   This is an example Dll which will receive calls from SQLiteningServer at
'   specific exit points.  The term 'Exit' comes from the main program
'   (SQLiteningServer) 'Exiting' to a sub program and then waiting for the
'   return code.  This sub program is a Dll than must be named
'   SQLiteningServerExits.Dll and has one exported entry named AllExits.  This
'   entry accepts all exit calls from the main process.  The sub program will
'   do whatever processing is required and then return a code to the main
'   process.  If the return code is zero then the main program will continue
'   else this code is returned to the calling client as an error or the main
'   program may end with an error.  This sub program must be thread safe. For
'   slightly better preferences it can access SQLite3.Dll directly using the
'   included sample macros or it can use SQLitening.Dll which is thread safe.
'  ==========================================================================

#Compile Dll "SQLiteningServerExits.Dll"
#Dim All
#Include "SQLitening.Inc"

' ==========>>> Using Ones
Declare Sub UsingLogIt(rsEntry as String)

'=========================<[ BackUp Thread ]>==========================
Thread Function BackUpThread(byval rhLogItCodePtr as Dword) as Long
' Will backup Sample.Db3 every 5 minutes and log the event.

   Local llRC as Long

   do
      llRC = slOpen("Sample.Db3", "E")
      if llRC = %SQLite_OK then llRC = slCopyDatabase("SampleBackUp.Db3", "E")
      slClose
      if llRC = %SQLite_OK then
         call dword rhLogItCodePtr using UsingLogIt("BackUp Completed OK")
      else
         call dword rhLogItCodePtr using UsingLogIt("BackUp Had Error " & format$(llRC))
      end if
      sleep 5 * 60 * 1000
   loop

End Function
'========================<[ Start Exit #101 ]>=========================
Function StartExit(byval rhLogItCodePtr as Dword) as Long
' Will start the BackUp thread and log the event.

   Local lhA as Dword

   thread create BackUpThread(rhLogItCodePtr) to lhA
   thread close lhA to lhA

   call dword rhLogItCodePtr using UsingLogIt("Exit #101 BackUp Thread Started")

End Function


'===========================<[ All Exits ]>============================
Function AllExits alias "AllExits" (byval rlExit as Long, _
                                    byval rhLogItCodePtr as Dword, _
                                    rsData as String)Export as Long

'   Exit values, when the exit occurs, what is passed in Data, and what
'   are the expected  return values.
'
'       1 = Connnect. Occurs when slConnect is issued.  Before the entry
'           is loged.  Data contains the following $BS delimited values.
'                 1 = Tcp File Number
'                 2 = Socket Number
'                 3 = User Name, if received from client
'                 4 = Computer Name, if received from client
'                 5 = IP Address, if received from client
'           Return zero if OK or non zero to reject the connect.
'
'       2 = Disconnect. Occurs when slDisconnect is issued or a connection is
'           dropped.  Data contains the following $BS delimited values.
'                 1 = Tcp File Number
'                 2 = Socket Number
'                 3 = One of the following result values
'                       57 or 0 = Dropped OK
'                       255     = slDisconnect issued
'                       else    = Dropped -- value is PB err code
'           Return is ignored.
'
'       3 = Access. Occurs when one of the following is issued but before
'           it is actually done:
'              slOpen, slAttach, slGetFile, slPutFile, slRunProc
'           Data contains the following $BS delimited values.
'                 1 = Tcp File Number
'                 2 = File or Proc Name
'                 3 = Pass Word, if received from client
'                 4 = Type as follows:
'                       1 = GetFile
'                       2 = PutFile
'                       4 = Open
'                       8 = Attach
'                      16 = Run Proc
'                 5 = Value and use depends on Type as follows:
'                       Ignore if Type 1 or 2.
'                       Contains the SQLite open flags (Create and ReadOnly) if Type 4.
'                       Contains the open database handle if Type 8.
'
'       4 = Open. Occurs after slOpen one of the following is issued but before
'           it is actually done:
'              slOpen, slAttach, slGetFile, slPutFile, slRunProc
'           Data contains the following $BS delimited values.
'                 1 = Open database handle
'                 2 = Database file Name
'                 3 = Pass Word, if received from client
'
'     101 = Start. Occurs once when SQLiteningServer is starting.  No
'           Data is passed.  Return zero if OK or non zero to not allow
'           SQLiteningServer to start.
'
'     102 = Stop. Occurs once when SQLiteningServer is stopping.  No
'           Data is passed.  Return is ignored.
'
'   LogCodePtr may be called passing a single string containg the data
'   to be added to the log.

   ' Crack the exit number
   select case long rlExit
   case 101    ' Start
      function = StartExit(rhLogItCodePtr)
   end select

End Function


'  ==========================================================================
'  |                                                                        |
'  | SQLitening Server Monitor                                              |
'  |                                                                        |
'  ==========================================================================
'  If SQLitening Server is compiled with %CompileStartServerMonitor = %True
'  then it will shell to this process when it starts. This process will run
'  as long as the server is running, it will exit when the server is  stopped. 
'  This is the place you can code things like back up or do any other database
'  maintenance work using all the power of SQLitening in local mode.  If this
'  monitor is not required then just compile SQLitening Server with
' %CompileStartServerMonitor = %False.
'  ==========================================================================

#Compile Exe "SQLiteningServerMonitor.Exe"
#Dim All
#Include "SQLitening.Inc"

'============================<[ Equates ]>=============================
' ==========>>> Misc
%True = -1
%False = 0
%kSynchonize = &H100000
%kWaitObject = 0
%kWaitTimeout = &H102
%kWaitFailed = -1
%kWaitTime = 20000           ' Number of miliseconds to sleep between each monitor -- 10000 = 10 seconds

'============================<[ Declares ]>============================
Declare Function OpenProcess LIB "KERNEL32.DLL" ALIAS "OpenProcess" (BYVAL dwDesiredAccess AS DWORD, BYVAL bInheritHandle AS LONG, BYVAL dwProcessId AS DWORD) AS LONG
Declare Function WaitForSingleObject LIB "KERNEL32.DLL" ALIAS "WaitForSingleObject" (BYVAL hHandle AS LONG, BYVAL dwMilliseconds AS LONG) AS LONG

'=============================<[ Global ]>=============================

'===========================<[ Shut Down ]>============================
Sub ShutDown
'  This is called one time when the server stops.  Do any clean up work here.
   
End Sub

'============================<[ Monitor ]>=============================
Sub Monitor
'  This is called every n seconds depending on %kWaitTime.  Do your work here.
' Will backup Sample.Db3.

   Local llRC as Long
DBV "Mon" & "  " & curdir$
   if slOpen("Sample.Db3", "E") = %SQLite_OK then
DBV "MonOpen
      slCopyDatabase "SampleBackUp.Db3", "E"
      slClose
   end if

End Sub

'==============================<[ Main ]>==============================
Function PbMain()

   Local lhProcessHand As Dword

   ' Set current folder
   chdir  exe.path$

DBV "StrarMon" & "  " & curdir$
   ' Wait for server to end
   lhProcessHand = OpenProcess(%kSynchonize, %False, val(command$))
   Do
      Select Case WaitForSingleObject(lhProcessHand, %kWaitTime)
      case %kWaitTimeout    'the timer expired on wait so call Monitor
         Monitor
      Case %kWaitObject, %kWaitFailed     'if lkWaitObject then it completed
                                          'if lkWaitFailed then it completed before we opened process
         ShutDown
         exit do
      end select
   Loop

End Function






Fredrick Ughimi

Hello Fred,

Thank you for the insight and codes.

I'd like to try both methods. SQLiteningServerExits.Dll compiled alright. But I could not compile SQLiteningServerMonitor.Exe. Its asking for the declaration of DBV. So, I declared  DBV AS STRING, then changed the statement somewhat like DBV = "Mon" & "  " & CURDIR$  before I could compile. Hope I am doing the right thing. I am using PBwin 9.04

Would try both methods in my production setup and see how it goes.

Best regards,





Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fred Meier

My error.  I left the DBV debug statements in the source.  Just delete all of the DBV statements.

Fredrick Ughimi

Hi Fred,

>>My error.  I left the DBV debug statements in the source.  Just delete all of the DBV statements.

Ok.

I see both SQLiteningServerExits.Dll  and SQLiteningServerMonitor.Exe exists on their own. No connection with the main application except with the DB. I just setup a two system connection to try them out.

Best regards,
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

Fred,

I noticed conflicting names for SQLiteningServerExits.Dll. The sample code product  for exits and the product of the source code to recompile are both SQLiteningServerExits.Dll.  They can't exists in the same directory. Are they supposed to be in different directories?

Do I change the name of the sample code?

Same goes for SQLiteningServerMonitor.

Kind regards,

Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fred Meier

There can be only one SQLiteningServerExits and only one SQLiteningServerMonitor so just replace the sample ones with your real ones.

Fredrick Ughimi

Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

Hello Fred, with the sample codes its clear that the backup its done in the directory where the real database resides. How can one direct the back up to an external drive?
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

Hello Fred,

I tried recompiling SQLiteningServer today and I got:

String constant expected line 37: #RESOURCE ICON 1, "SQLitening.Ico"

I am using PBWin 9.40. I guess you used version 10 to compile. I don't have version 10.

I am in a jam here...
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fred Meier

Replace the following:
#Resource Icon 1, "SQLitening.Ico"
#Resource VersionInfo
#RESOURCE FILEVERSION 1, 54, 0, 0
#RESOURCE STRINGINFO "0409", "0000"
#RESOURCE VERSION$ "FileDescription",      "SQLitening Server"
#RESOURCE VERSION$ "ProductName",      "SQLitening"
#RESOURCE VERSION$ "ProductVersion",   "1.5"
with
#Resource "SQLitening.Pbr"

Fred Meier

QuoteHello Fred, with the sample codes its clear that the backup its done in the directory where the real database resides. How can one direct the back up to an external drive?

You can set DestinationFileName parm in slCopyDatabase to be any drive and/or directory you wish.