Compare rows from a db table using ADO

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.
phil1_curtisAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Lee_NoverConnect With a Mentor Commented:
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
 
Lee_NoverCommented:
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
 
aikimarkCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
kretzschmarConnect With a Mentor Commented:
you could load the values into a
vararray of variant and compare
then against the array
0
 
aikimarkCommented:
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
 
CleanupPingCommented:
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
 
kretzschmarCommented:
>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
 
Lukasz LachCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.