Solved

Compare rows from a db table using ADO

Posted on 2002-06-23
8
226 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Best Firemonkey component pack 1 85
select query - oracle 16 91
Delphi: Connect to running MS Outlook 4 47
PHP preg_replace code convert to Delphi 14 30
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

932 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

9 Experts available now in Live!

Get 1:1 Help Now