Solved

Compare rows from a db table using ADO

Posted on 2002-06-23
8
220 Views
Last Modified: 2010-04-04
I want to read a complete row from a table and store it in a variable, lets say ROW1. Later I will read the same row again and store this as ROW2. I will then test if ROW1=ROW2 and if not I then know if the row has been changed by another user.

Does anyone have some code that will allow me to do this.

Or is there a better way ? (More points if yes)

Thanks Phil.

BTW I am using MySQL or MS Access as the DB.
0
Comment
Question by:phil1_curtis
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 12

Expert Comment

by:Lee_Nover
ID: 7101761
storing the whole row would be inefficient
I suggest you add a new field like a datestamp
whenever you change anything in the row update that field with the current date
so you need only compare that field

another way would be to create a hash of the fields in the row
but that's time consuming (it's actually very fast, but compared to the first method it's really slow :)

if you still want all of the items compared you can write them to a buffer like:

procedure TForm1.Button1Click(Sender: TObject);
var I: Cardinal;
    memStr: TMemoryStream;
begin
     memStr:=TMemoryStream.Create;
     try
        for I:=0 to tblMain.FieldCount-1 do
            memStr.Write(tblMain.Fields[I].Value, tblMain.Fields[I].DataSize);
        // oldStr is a global memory stream which holds a previously read row
        // read the same way as above
        //if AnsiStrComp(memStr.Memory, oldStr.Memory) = 0 then
        // they're same - no change
     finally
        FreeAndNil(memStr);
     end;
end;


tblMain is the table you're using
hope it helps
0
 
LVL 12

Accepted Solution

by:
Lee_Nover earned 125 total points
ID: 7101767
ahh a correction .. I forgot that .Fields[x].Value is a setter property
so you need to store the field in a variant

-

procedure TForm1.Button1Click(Sender: TObject);
var I: Cardinal;
    memStr: TMemoryStream;
    varVar: Variant;
begin
     memStr:=TMemoryStream.Create;
     try
        for I:=0 to tblMain.FieldCount-1 do
        begin
          varVar:=tblMain.Fields[I].Value;
          memStr.Write(varVar, tblMain.Fields[I].DataSize);
        end;
        // oldStr is a global memory stream which holds a previously read row
        // read the same way as above
        //if AnsiStrComp(memStr.Memory, oldStr.Memory) = 0 then
        // they're same - no change
     finally
        FreeAndNil(memStr);
     end;
end;
0
 
LVL 45

Expert Comment

by:aikimark
ID: 7102212
1.  I think you can set some options with your ADO Command object or Connection object that will retrieve the records with a pessimistic read.  This tells the ADO that you expect to update the record.  The downside to this is that it would effectively lock the record from other users' updates.

2. You can construct your update query to include the field comparison.
Example:
UPDATE tblname
SET fld1 = newval1, fld2 = newval2, ..., fldn = newvaln
WHERE fld1 = oldval1, fld2 = oldval2, ..., fldn = oldvaln

3. If mySQL has a timestamp field, you can use that value to determine if the record has changed since you read it.
0
 
LVL 27

Assisted Solution

by:kretzschmar
kretzschmar earned 125 total points
ID: 7102775
you could load the values into a
vararray of variant and compare
then against the array
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 45

Expert Comment

by:aikimark
ID: 7103682
If you use an ADO disconnected recordset, you can compare the new record with the old record without having to store them in a variant array.

For example:
For i=0 To (oldrs.Fields.Count-1) Do
  Begin
  If oldrs.Fields(i).value <> newrs.Fields(i).value
   etc.

  End;
0
 

Expert Comment

by:CleanupPing
ID: 9343174
phil1_curtis:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 9343197
>Post your closing recommendations!  No comment means you don't care.

i am definitivly not able to post recommendations for 200+ cleanup-posts per day.

that means not, that i didn't care of it, if i post no recommendation there!!
0
 
LVL 5

Expert Comment

by:Lukasz Lach
ID: 9453494
phil1_curtis,
No comment has been added lately (17 days), so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area for this question:

RECOMMENDATION: split points between Lee_Nover http:#7101761 and kretzschmar http:#7102775

Please leave any comments here within 7 days.

-- Please DO NOT accept this comment as an answer ! --

Thanks,

anAKiN
EE Cleanup Volunteer
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now