[Solved] SQL Help

[AndyGable]

Hi all

I need someone who understandas SQL better then me

what I am trying to do is compare tables from my local database to my remote database

The Remote database has tables that have lots of feilds in but for the local database do not need all the feilds

so I am using this

Code: Select all

    $Query = Null
    $Query &= "Select "
    $Query &= "idnumber, "
    $Query &= "refusaldescription "
    $Query &= "from " & Global.$DBCon & ".agerefusallist "
    $Query &= "Where refusaldescription NOT IN "
    $Query &= "(Select "
    $Query &= "idnumber, "
    $Query &= "refusaldescription "
    $Query &= "from " & Global.$DBConLocal & ".agerefusallist);"

But I get a error on the Global.$DBCon (this is the remote database called BackOfficeData)

Am i even on the right tack or am I completely loosing the plot?

[Quincunxian]

Hi Andy - I dabble a little in DB stuff.
From your code, my best guess is that you are trying to use the same connection for both databases and that won't work.
You will need to instantiate two connection objects then make a comparison of both.
I need to do this for a current project where I need to compare a local MySQL to a remote one.
My need is to simply ensure that both databases have the same table structure, and that the record counts are the same,
but it should be easy for you to make the distinction between those that have more fields than the local DB.
I want to make the code reusable so you should be able to make the changes to suit your need without too many problems.
Give me a day or so and I'll post something up.

[AndyGable]

Hi Andy - I dabble a little in DB stuff.
From your code, my best guess is that you are trying to use the same connection for both databases and that won't work.
You will need to instantiate two connection objects then make a comparison of both.
I need to do this for a current project where I need to compare a local MySQL to a remote one.
My need is to simply ensure that both databases have the same table structure, and that the record counts are the same,
but it should be easy for you to make the distinction between those that have more fields than the local DB.
I want to make the code reusable so you should be able to make the changes to suit your need without too many problems.
Give me a day or so and I'll post something up.

Thanks Quincunxian

I look forward to seeing your code just to give you a heads up

Global.$DBCon Is the connection to the remote database

Global.$DBConLocal is the connection to the local database

[Quincunxian]

Hi Andy,
I've tested this with a local & remote MySQL database and it worked ok.
I could compare two tables and highlight similar fields in the remote table that were in the local one.
I added the ability to extract data from either table records in CSV, XML or JSON. (Not sure about the formatting of the JSON though)

I use xdg-open to open documents which 'should' be on most linux distros.
Documents are saved in your documents folder or in the application run path if it can't find it for some reason.

To use:
Enter your credentials and database detail in the local - then - the remote connection.
Click the Local button.
The tables combo should populate.
Select a table to review - you can do the same for the Remote connection. (depending on the location of the remote connection, this may take a few seconds)
If you have two valid tables then you can compare them both.

Note# I have not included any capability to save the credentials so will only work for the current session.

Hope this gives you a good start to your solution. - Any problems, drop me a note.

Edit: the sqlite and postgres connection types are untested so not sure how well they will work.

[AndyGable]

Hi Andy,
I've tested this with a local & remote MySQL database and it worked ok.
I could compare two tables and highlight similar fields in the remote table that were in the local one.
I added the ability to extract data from either table records in CSV, XML or JSON. (Not sure about the formatting of the JSON though)

I use xdg-open to open documents which 'should' be on most linux distros.
Documents are saved in your documents folder or in the application run path if it can't find it for some reason.

To use:
Enter your credentials and database detail in the local - then - the remote connection.
Click the Local button.
The tables combo should populate.
Select a table to review - you can do the same for the Remote connection. (depending on the location of the remote connection, this may take a few seconds)
If you have two valid tables then you can compare them both.

Note# I have not included any capability to save the credentials so will only work for the current session.

Hope this gives you a good start to your solution. - Any problems, drop me a note.

Edit: the sqlite and postgres connection types are untested so not sure how well they will work.

Thanks so much for that i have been having a look and it works great on my first table but when I change to another it throws a error

"out of Bounds in Cls_SQL_Unilities Line 29 "

This is line 29 $Rec = $Con.Exec("SELECT Count(" & TmpTable.PrimaryKey[0] & ") As TotalRecords FROM " & InTable)

Sorry to be a pain but any ideas what this means?

[Quincunxian]

Hi Andy,
That line is just getting the record count of the table.

Normally, you would use SELECT COUNT(*) AS TotalRecords FROM TableName
MySQL reference HERE
This failed for me when I did some initial testing and I simply put in a count of the Primary Key as a substitute for the field to be counted. - Should of looked into this further but it was getting late.

The primary Key, (Table.PrimaryKey) is a string array, so needs to be suffixed with a an array element [n] as you may have more than one key to a table.
You 'can' have tables without a primary key set and this would produce the error that you have encountered.?

Change the line to:
$Con.Exec("SELECT Count(*) As TotalRecords FROM " & InTable) and see if that makes a difference.

[thatbruce]

Try

Code: Select all

SELECT Count(1) FROM <tablename>;

This is a more generic and usually way faster than Count(*) as the latter queries all the fields in the table whereas the above just looks at the first column regardless of what it is.
Also just to add to the original reply, cross database queries like the one you were trying to write are very rdbms specific and non-standard SQL is needed. Avoid at any cost
And just finally, have you looked at the SQLRequest (gb.db) class. It's a bit hard to get your head around but it does guarantee correct SQL as an outcome. I use it all the time now as an alternative to trying to build SQL using a lot of string commands (and have built some pretty serious SQL using it).
regards
bruce

[AndyGable]

Thanks guys that compare works great but how do I see if any of the data between the two tables is the same or different?

Example I have a agerefusallist table and I need to see if the data in the the remote database is the same as the local and if not update or add what is missing to the table.

[thatbruce]

Just do an update where they differ. Hehe, easy to say but not easy to do without understanding how to do "foreign table references" in mysql. (Which I do not even have the slightest intention of ever knowing about!)
To be serious, if you can work out how to do the FT references, then issuing an update that "doesn't" update anything should be just as expensive as finding that values differ, if you get my drift. In theory, and very much pseudo code
UPDATE 'local'.sales SET total=(select total FROM 'foreign'.sales WHERE 'local'.total <> 'foreign'.total) WHERE blahblah
If the sub-query is a null set then nothing is updated. As I said t'other day the SQL you need is v. much db dependent and I don't know mysql. (Read elsewhere for my true opinion of that dbms, or guess!)
regards
b

[AndyGable]

Just to let you all know I have managed to slove my issues with checking tables has the same data in it


Basically what i have done is

Read in the remote table
Loop though it each record and find it in the local time
Do a where on each field) if it returns 0 then I know something has changed so it gets updated of it returns a 1 then I skip it and go to the next record