Solved

Compare rows from a db table using ADO

Posted on 2002-06-23
8
241 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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
 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
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…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

829 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