Solved

Compare rows from a db table using ADO

Posted on 2002-06-23
8
253 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

691 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