Comparing data from a table in different Databases

Post your Gambas programming questions here.
Post Reply
AndyGable
Posts: 359
Joined: Wednesday 2nd December 2020 12:11am
Location: Northampton, England
Contact:

Comparing data from a table in different Databases

Post by AndyGable »

Hi Everyone,

Happy New Year to you all.

I would like some advice on the following matter (not sure If I have asked this before but I am asking it again)

I have 2 databases Database A is running on my Local PC and Database B is running on my Main network server

I am using a Small Program to keep the two databases in sync

It is possible to get from the Main Database ONLY records that have changes / does not exist in the Local Database?

I am NOT using replication as that looks way to hard to set up and maintain

So My Application is written in Gambas3 and it connects to both my Network Database and local Database and moves data between them

is there a SQL Command I can use to only return changed data (example Item 10101 price was Changed on the Server database is there way i can use SQL to compare what is in the local database to what is stored on the server)
User avatar
grayghost4
Posts: 174
Joined: Wednesday 5th December 2018 5:00am
Location: Marengo, Illinois usa

Re: Comparing data from a table in different Databases

Post by grayghost4 »

I am not a SQL user ... but a simple google found this :

https://www.google.com/search?channel=f ... +of+update

hope this will help you
User avatar
Quincunxian
Posts: 171
Joined: Sunday 25th June 2017 12:14am
Location: Western Australia

Re: Comparing data from a table in different Databases

Post by Quincunxian »

There is an database ATTACH function in SQLite but it does not seem to be implemented in Gambas.
If the changes are made on your server DB then you would have a Date changed or a Boolean flag to say that a value is changed and a unique identifier that is consistent between the server and local tables.

You have three conditions.
The data is new on the server and needs to be added to the local instance
The data has been deleted on the server and needs to be removed on the local instance.
The data has changed and needs to be updated on the local instance.

If you delete a record on the server, you should flag it as deleted but preserve the record until the alignment process has been run.

DS = Server connection. DL = Local connection.

$RecS = DS.Exec("SELECT * FROM DS.Table where {condition}")
If Not IsNull($RecS) and $RecS.Available then
For Each $RecS
'Deleted Flag.
If $RecS!Deleted then
DL.Exec("DELETE FROM DL.Table WHERE ($RecS!Id = Id)) ' Clean local instance
DS.Exec("DELETE FROM DS.Table WHERE (Id = $RecS!Id)) ' Clean from the server
endif

'New/Changed
$RecL = DL.Exec("SELECT Id FROM DL.Table WHERE(Id = $RecS!Id))
If $RecL.Count = 0 then ' it is a new record.
{Enter INSERT statement for new record}
Else ' It is an existing record that needs to be changed.
{Enter UPDATE statement for required fields to change}
EndIf

'If you are using a boolean flag to identify changed records on the server then update it as 'processed'
DS.Exec("UPDATE DS.table set {Flag} = 0 WHERE (Id = $RecS!Id))

Next

## WARNING This is untested and written from memory so use as an example only.
It should give you enough to get a working instance running. PLEASE backup your data before testing.
The SQL should be generic enough for MySQL ect...
Cheers - Quin.
I code therefore I am
AndyGable
Posts: 359
Joined: Wednesday 2nd December 2020 12:11am
Location: Northampton, England
Contact:

Re: Comparing data from a table in different Databases

Post by AndyGable »

Quincunxian wrote: Sunday 2nd January 2022 3:50am There is an database ATTACH function in SQLite but it does not seem to be implemented in Gambas.
If the changes are made on your server DB then you would have a Date changed or a Boolean flag to say that a value is changed and a unique identifier that is consistent between the server and local tables.

You have three conditions.
The data is new on the server and needs to be added to the local instance
The data has been deleted on the server and needs to be removed on the local instance.
The data has changed and needs to be updated on the local instance.

If you delete a record on the server, you should flag it as deleted but preserve the record until the alignment process has been run.

DS = Server connection. DL = Local connection.

$RecS = DS.Exec("SELECT * FROM DS.Table where {condition}")
If Not IsNull($RecS) and $RecS.Available then
For Each $RecS
'Deleted Flag.
If $RecS!Deleted then
DL.Exec("DELETE FROM DL.Table WHERE ($RecS!Id = Id)) ' Clean local instance
DS.Exec("DELETE FROM DS.Table WHERE (Id = $RecS!Id)) ' Clean from the server
endif

'New/Changed
$RecL = DL.Exec("SELECT Id FROM DL.Table WHERE(Id = $RecS!Id))
If $RecL.Count = 0 then ' it is a new record.
{Enter INSERT statement for new record}
Else ' It is an existing record that needs to be changed.
{Enter UPDATE statement for required fields to change}
EndIf

'If you are using a boolean flag to identify changed records on the server then update it as 'processed'
DS.Exec("UPDATE DS.table set {Flag} = 0 WHERE (Id = $RecS!Id))

Next

## WARNING This is untested and written from memory so use as an example only.
It should give you enough to get a working instance running. PLEASE backup your data before testing.
The SQL should be generic enough for MySQL ect...

Thanks Quincunxian that will give me something to work from
Post Reply