I've tried using ExecuteSQL to re-read a record prior to committing the new data but this returns the new data contrary to other blog posts that indicate that ExecuteSQL does not use the current found set to find records.
Those are not the same thing. The found set applies to the current client's found set. ExecuteSQL doesn't have any clue about that; it works at the table (occurrence) level. But it does use local, uncommitted data on whatever record(s) involved in the query have changes.
So what you read was correct. It just means something different than that you think it means.
This is the demo file from my 2014 devcon presentation. It does what you are after. Check out the flow-chart that is in the container field of the audit log demo file, it explains the concept and the flow.
It works only because just before you commit you have the new data and the server still has the old data. But you can't do anything in your own session to get the old data, you have to use PSoS to ask the server for the old data.
The rest of the file is about making it generic so that it works off any layout of any context without you having to hard code what fields to collect. It translates what is on the user's layout to an ExecuteSQL() query for the PSoS session.
Note: one of the things we found out in putting this together: you DO NOT want to use ExecuteSQL() in the user's session when you still have an open record in that user's session for the target table you want to touch through ExecuteSQL(). If you have a lot of records there, it will kill all performance since FMS will send you ALL the data of the whole table so that the client can do the sql query.
If your final goal is to build a changes log file, then there's plenty of material around - search the forum for "audit".
I would also consider the MBS plugin which has a section on that, including auditing the deletion of records.