Your browser does not support script
NAVIGATION
Home Search Disclaimer Login/Register
Updated : 6/2/2017

GHowSA Database Mirror

UPDATE

Following a migration to a new host the one-wire system has been ported to a MySQL database and hence the hosting database can be updated from the local MySQL database using a simple scheduled script. However, I have retained this page in its current form to document the original approach.

Introduction

The GHowSA data capture systems (GHowSAW, GHowSAG and GHowSAE) operate on a local machine saving the data to a Microsoft Access database, whilst this personal web site is hosted outside of South Africa. To view the data on this web site requires access to the database hosted on the local machine but accessing a MS Access database over the internet is not possible, and even if it were the upload speed of the local machine is totally inadequate for the purpose. Consequently, a method is required to maintain a synchronised copy of the data on the web-site host. Fortunately, true synchronisation of the databases is not required since data is only added at the site running the GHowSA system ie the local machine and therefore what I actually needed was a mirroring of the local database to a database at the web host server for data analysis and viewing.

The full content management database is approximately 50 Mbytes and could theoreticaly, be uploaded to the web server at hourly intervals but this could place the whole system at risk and in any case would be overkill in that, at present, only about 100kb of new data is generated every hour. I therefore decided that a batch process would do the job.

The local internet upload speed precludes the presentation of live data so compromises have to be made and I decided that for relatively slowly changing data such as the weather data of GHowSA and and the soil temperature and moisture levels of GHowSAG for example that mirroring the data between the databases at hourly intervals would be sufficient. The frequency of mirroring may be increased after commisioning trials if the method proves to be sufficiently reliable, after all at higher frequencies the amount of data to be transferred will be proportionately smaller.

Even at this leasurely rate the synchronisation proved to be a little more complex than I initially thought and exacerbated by one or two unexpected problems not the least of which was the unavailablty of 64-bit drivers for MS Access. Nonetheless I managed to achieve the mirroring level I wanted and this page describes in some detail how it was achieved.

Method

The general approach adopted is to use the Windows Task scheduler to run a batch job every hour to transfer all the new data collected in the last hour to the web host database. The general methodology for mirroring the databases is as follows:-

Getting the data

Extract the data for mirroring using a VBScript and save the data to a CSV file. The script used is shown below.

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set fname=objFSO.CreateTextFile("D:\Temp\Update.csv",true)
Set dataRec = CreateObject("ADODB.Recordset")
Set Cn = CreateObject("ADODB.Connection")
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data"&_
          "Source = D:\dbase\dbase.mdb;Persist Security Info=False"
cn.Open(StrConn)
'select all data from the last 75 minutes and write to file Update.csv
sql = "SELECT * FROM Table WHERE Date_Time>=#"&DateAdd("n",-75,Now())&"#;"
dataRec.Open sql,cn
dataRec.MoveFirst
Do UNTIL datarec.eof
    strCSV=""
    For i = 1 to dataRec.Fields.Count-1
        strCSV = strCSV & dataRec(i).value &","
    Next
    strCSV=Left(strCSV,Len(strCSV)-1)
    fname.WriteLine(strCSV)
    dataRec.MoveNext
Loop
fname.Close
Set fname=nothing
Set objFSO=nothing
datarec.Close
Set datarec=nothing
cn.Close
Set cn=nothing
        

The important features to note regarding this script is the use of the DataAdd function to get the latest data.
DateAdd(""n"",-75,Now()) provides a date/time which is 75 minutes prior to the current time. I retrieve 75 minutes of data to provide a 15 minute overlap to avoid any issues of timing diffence between the GHowSA data collection system and the task scheduler.

The first major problem now arises when trying to run the script. An error code 800A0E7A 'Provider cannot be found. It may not be properly installed' is generated at line 13. This is because I was running the script on a 64-bit machine and there are no 64-bit version of the OLEDB for Access! The solution is to run the script in 32-bit mode using SySWoW64 and cscript. To do this the script must be run from the command line using:

c:\windows\SySWoW64\cscript d:\script\update.vbs
        

We now have a script that can be scheduled to provide a CSV file of the data to be mirrored. In Task Scheduler set the Action to c:\windows\SySWoW64\cscript with parameters d:\script\update.vbs (the full path and name of the VBScript file)

Data transfer

The update data must now be copied to the data to the web server using FTP. There a numerous scripts available on the internet to do this but not all are equal. The scipt I use is derived from one from 'Today Guess What'which is a VBScript FTP Download Sample and is also available here

An FTP upload script uses the windows FTP client to upload the files using parameters defined in a batch file. The progress of the tranfer plus any error messages are presented in a message box. These messages can be inhibited by setting the constant 'silent' to true. However, even if messaging is inhibited if there are any problems encountered with the FTP upload then the output of the ftp process will be saved in a file Update_?????.txt in the system default temp folder.

A very simple ftp upload script can be seen here

The file tranfer scripts is then appended to the data capture script.

Data Update

Now that the update data is on the web host server we can process the data to add it to the database. This is done in the same VBScript by calling a page on the server to process the data. This paqe parses the csv file to create sql insert statements to add the data to the database. It is important to note that the table being updated must have constraints to prevent duplicate data being added. In my case the constraint requires the combination of date/time and the device identity to be unique.

Setting constraints in MS access across multiple fields is fairly easy, once you know how its done, since the method is not exactly intuitive. First of all make sure that all the fields that you intend to use in the constraint have the required property set to 'Yes'. In the design view of the table go to 'View' and select 'Indexes' and add a name for the new index and select the first field to be used in the constraint. Then move down one row and select the next field for the constraint (note the Index Name on this row must be left blank). Continue adding fields as required up to a maximum of ten fields. When all the fields have been added highlight the row containing the Index Name you entered at the beginning and In the Index Properties section set 'Unique' to yes. Finally save the table design.

Note you will not be able to save the table design if the table already contains data which does not meet the constraint requirements. If this is the case with a table containing a large amount of data then create a design only copy of the table, add the contraints to the copy and then append data from the original table to the copy table. This will add all data except that which does not meet the new constraint. Delete the original table and then rename the copy to the original name.

The final task of the update page is to check if the update data spans midnight and if so it (a) updates the summary table with the summary data for the the previous day and (b) deletes data from the detail table that is older than seven days.

Task Scheduler

The task is added to the Task Scheduler and set to run every hour. Unfortunately, every time the task runs a Command Prompt windows pops-up which, whilst not a major issue, it can be irritating. To avoid this window pop up you can either change the user account to the system account which will not open a window or you can from Run command line or Task Scheduler action use:

cmd.exe /c start /min c:\windows\SySWoW64\cscript d:\update\update.vbs ^& exit

to run the task minimised and close on completion. This latter option is preferred since the first method involves some security issue regarding the running of scripts that access the internet under the system account.

Evaluation

The above mirroring method was added to the Task Scheduler and scheduled to run hourly. From the schedule history it appears that the update process (creates data file, ftp file to server, update the database) takes approximately 8 seconds. The evaluation of the process was run for three weeks with no recorded errors so the system has been left running. The method obviously requires internet access and I am waiting for a new 3G modem for use as a fall over for my ADSL internet connection. Once I receive the new modem I will re-evaluate the process with that modem.

In the interim the Update.VBS file has been modified to log the number of updates missed as a result of ftp errors and to extend the range of the sql statement to include missed updates up to a maximum of 24 i.e the update procedure will recover for the internet access being unavailable for a period of up to 24 hours.

Guest
288 Page Hits