Plugin SQL intercept

[neil.interactit]

Hey guys,

James just hit me with a question that I thought best to raise with you. When he upgrades a client Jiwa 6 to Jiwa 7, he ends up - on a case by case basis - with an extensive list of changes to be made, each time he runs through the upgrade. This is essentially a OneNote list he reads through and repeats in Jiwa. Inevitably, at 3am when doing the actual upgrade, a silly mistake snowballs into huge issues.

So his question "can you sniff the SQL updates as I make changes in Jiwa so that I can compile these into a script to re-run each test run".

While I can do this SQL-side, independently of Jiwa, the SQL is pretty noisy, and complicated by multiuser access. Better, if possible to do this Jiwa-side.

So my question - would it be possible, from a plugin, to intercept ALL database calls in order to log these? If so, then I can filter out INSERT/UPDATE/DELETE/EXECs and probably even annotate with the calling form. I would probably also work out some way of providing a logging on/off control, so that only specific UI-generated changes of interest are logged.

Is this at all possible, or am I barking up the wrong tree?

Cheers,
Neil

[SBarnes]

You can get reads from the readerexecuted event on the database property the manager its signature is below

Code: Select all

 public delegate void ReaderExecutedEventHandler(SqlCommand SQLCommand, CommandBehavior CommandBehaviour);



Given all business objects inherit from the same maintenance object that implements IJiwaSaveable which is below, you can latch onto the save events and use the lcient property on maintenace to get you the form but that won't actually get you the SQLs, but you could serailise out the objects and work from there.

Code: Select all

namespace JiwaFinancials.Jiwa.JiwaApplication
{
    [CLSCompliant(true)]
    public interface IJiwaSaveable
    {
        bool ChangeFlag
        {
            get;
            set;
        }

        void iSave();

        void Save();

        event IJiwaCommonInterfaces.SaveEndDelegate SaveEnd;

        event IJiwaSaveable.SaveEndingEventHandler SaveEnding;

        event IJiwaSaveable.SaveStartEventHandler SaveStart;

        public delegate void SaveEndingEventHandler(object sender, EventArgs e);

        public delegate void SaveStartEventHandler(object sender, EventArgs e);
    }
}


If you can get the SQLs you need to run one other option would be to add these to the scripts database and have Jiwa run them for you as part of the upgrade process.

[Mike.Sheen]

While I can do this SQL-side, independently of Jiwa, the SQL is pretty noisy, and complicated by multiuser access. Better, if possible to do this Jiwa-side.

Nonsense - I do this all the time on very busy live customer environments using SQL Profiler and using the ClienProcessID filter to limit the sql captured to be ONLY what is run in my Jiwa session.

You get the client process ID from the Jiwa About dialog accessible from any form in the application, and then when you launch a profile trace add a filter to only include SQL commands for that specific ClientProcessID.

[neil.interactit]

Thanks Stuart.
Thanks Mike.

I'll sort something for James on this basis.

Cheers,
Neil