Recent

Author Topic: Compare queries results  (Read 10580 times)

Dane

  • New Member
  • *
  • Posts: 15
Compare queries results
« on: October 13, 2010, 07:16:38 pm »
I've two databases. I'll need to compare the same tables in both databases for differences, base on one (or two) field (fields). Could anybody advise me how?

JuhaManninen

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 4459
  • I like bugs.
Re: Compare queries results
« Reply #1 on: October 13, 2010, 10:12:21 pm »
I've two databases. I'll need to compare the same tables in both databases for differences, base on one (or two) field (fields). Could anybody advise me how?

I would read the needed data into memory using a proper data structure. TStringList may by just good enough.
Data can be read using the LCL DB connection components, or data-dump text files can be generated from those tables in advance.

Juha
Mostly Lazarus trunk and FPC 3.2 on Manjaro Linux 64-bit.

Dane

  • New Member
  • *
  • Posts: 15
Re: Compare queries results
« Reply #2 on: October 15, 2010, 06:08:16 am »
Ok. I have two queries results MSDBQuery and MyDBQuery. Can I search one of them for value of another like MSDBQuery.Locate('inc', MyDBQuery.FieldByName('inc').Value, []) and, if it's exists, delete record from query result?

JuhaManninen

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 4459
  • I like bugs.
Re: Compare queries results
« Reply #3 on: October 15, 2010, 08:12:41 am »
Ok. I have two queries results MSDBQuery and MyDBQuery. Can I search one of them for value of another like MSDBQuery.Locate('inc', MyDBQuery.FieldByName('inc').Value, []) and, if it's exists, delete record from query result?

I guess you can. How fast it is depends on the DB size, amount of data.
If your DB is rather small then it makes no difference.
If it has lots of data then it may be better read some data into memory and analyze it there. Current computers have lots of memory and it makes sense to use it.

On the other hand, if you have indexed the field you "Locate" then it is fast even with lots of data.

Juha
Mostly Lazarus trunk and FPC 3.2 on Manjaro Linux 64-bit.

Dane

  • New Member
  • *
  • Posts: 15
Re: Compare queries results
« Reply #4 on: October 15, 2010, 08:21:29 am »
Thanks for your answer. I've write a code:

Code: [Select]
      MSDBQuery.ReadOnly := False;
      MSDBQuery.Open;

      MyDBQuery.SQL.Clear;
      MyDBQuery.SQL.Add('SELECT * FROM table');
      MyDBQuery.Open;

      while not MSDBQuery.EOF do
      begin
         if MyDBQuery.Locate('inc', MSDBQuery.FieldByName('inc').Value, []) then
         begin
            MSDBQuery.FieldDefs.Dataset.Delete;
         end;
         MSDBQuery.Next;
      end;
      MyDBQuery.Close;
      exit;

When I've try to execute this code Lazarus display an error: Dataset is read only. But I've already set it False. Where I'm wrong?

Mando

  • Full Member
  • ***
  • Posts: 181
Re: Compare queries results
« Reply #5 on: October 15, 2010, 09:29:42 am »
Hello:

the code doesn't work for several reasons:

First eror:
Code: [Select]
MSDBQuery.FieldDefs.Dataset.Delete;
only do this
Code: [Select]
MSDBQuery.Delete;
but when you delete a record, the next record became the active record, then you pass to the next record (MSDBQuery.next), and jump one record.
Supose this dataSet
A<-Active record
B
C
D
if record A must be deleted, the dataSet
B<-Active record
C
D
Now, do next
B
C<-Active record
D
is not checked if record B must be deleted!!!....

You must modify your code like this:

Code: [Select]
if MyDBQuery.Locate('inc', MSDBQuery.FieldByName('inc').Value, []) then
   begin
     MSDBQuery.FieldDefs.Dataset.Delete;
   end else  MSDBQuery.Next;


Dane

  • New Member
  • *
  • Posts: 15
Re: Compare queries results
« Reply #6 on: October 15, 2010, 10:11:36 am »
Yes, you're right. I've found this errors. Thank you. But the error "Dataset is read-only" still here.
Code: [Select]
      MSDBQuery.ReadOnly := False;
      MSDBQuery.Open;

      MyDBQuery.SQL.Clear;
      MyDBQuery.SQL.Add('SELECT * FROM samo_claim');
      MyDBQuery.Open;
      while not MSDBQuery.EOF do
      begin
         if MyDBQuery.Locate('inc', MSDBQuery.FieldByName('inc').Value, []) then
            MSDBQuery.Delete <- This line got an error
         else
            MSDBQuery.Next;
      end;

If I've deleted record from MyDBQuery - it's ok. I've examined a value of ReadOnly property of MSDBQuery - it's false. May be this error is associated with MS SQL driver?

Mando

  • Full Member
  • ***
  • Posts: 181
Re: Compare queries results
« Reply #7 on: October 19, 2010, 01:55:20 am »
Hi, Dane:

What controls are you using?
 
if you use SQLdb, you may need to set the properties DeleteSQL and UpdateSQL to be able to delete/modify records.

Thats an idea. I hope it will be useful.

Regards.

Dane

  • New Member
  • *
  • Posts: 15
Re: Compare queries results
« Reply #8 on: October 19, 2010, 02:12:34 pm »
Hi, Mando
I'm using SQLdb. Can you write an example how to set the properties DeleteSQL and UpdateSQL. I've searched internet for something usefull, but there's no result.

xenblaise

  • Sr. Member
  • ****
  • Posts: 358
Re: Compare queries results
« Reply #9 on: October 19, 2010, 02:27:46 pm »
Quote
MyDBQuery.SQL.Clear;
MyDBQuery.SQL.Add('SELECT * FROM samo_claim');
MyDBQuery.Open;

Change it to;
MyDBQuery.SQL.Text := 'SELECT * FROM samo_claim';
MyDBQuery.Open;
 :D

xenblaise

  • Sr. Member
  • ****
  • Posts: 358
Re: Compare queries results
« Reply #10 on: October 19, 2010, 02:32:13 pm »
Quote
Hi, Mando
I'm using SQLdb. Can you write an example how to set the properties DeleteSQL and UpdateSQL. I've searched internet for something usefull, but there's no result.

you should try that post;
http://forum.lazarus.freepascal.org/index.php/topic,10811.0.html
 :D

Dane

  • New Member
  • *
  • Posts: 15
Re: Compare queries results
« Reply #11 on: October 19, 2010, 03:00:12 pm »
Ok. I've read. Its looks very simple: just .Delete. But I've done like this and always got an error "dataset is read-only". If I'll use TSQLQuery from MySQL database - it's works fine: I can delete records from the result. But if I'll work with MSSQL query - error.

Mando

  • Full Member
  • ***
  • Posts: 181
Re: Compare queries results
« Reply #12 on: October 19, 2010, 03:01:02 pm »
Hi Dane:
Quote
Hi, Mando
I'm using SQLdb. Can you write an example how to set the properties DeleteSQL and UpdateSQL. I've searched internet for something usefull, but there's no result.

There are two solutions.
The firts, build yourself the DeleteSQL in that way:
Code: [Select]
MSDBQuery.SQL:='DELETE FROM "YOUR_TABLE_NAME"
WHERE YourKeyField=:YourKeyField';

The other solution is:
Set  UpdateMode property of TSQLQuery to upWhereAll and UsePrimaryKeyAsKey to True. Then the control will gerenates the UpdateSQL and DeleterSQL strings for you.
Code: [Select]
MSDBQuery.UpdateMode            := upWhereAll;
MSDBQuery.UsePrimaruyKeyAsKey:= True;

regards

xenblaise

  • Sr. Member
  • ****
  • Posts: 358
Re: Compare queries results
« Reply #13 on: October 19, 2010, 03:13:53 pm »
Thats right Mando

Dane;
Have you tried to download the SQLITE connection sample?
http://wiki.lazarus.freepascal.org/User:Rocarobin

at the bottom you can download the source code project.
Update oct 20 2010
 :D

The source is fly anywhere.

xenblaise

  • Sr. Member
  • ****
  • Posts: 358
Re: Compare queries results
« Reply #14 on: October 19, 2010, 06:40:04 pm »
Ms_Access Updated oct 21 2010 with add_delete_update_print
http://wiki.lazarus.freepascal.org/MS_Access
 :D

 

TinyPortal © 2005-2018