“ta” eConnect stored procedures & upgrades a thought…

eConnect presents a “pre” and “post” procedure for the various document transactions. This allows custom SQL scripts to be ran before and after eConnect transactions.

Beware, as anyone who works with eConnect will tell you, these procedures are DROPPED during version upgrades. This understandably makes developers wary of using them, for fear of problems after upgrades.

My current practice with these procedures is to have a single line call to another stored procedure that acts as a proxy. The proxy contains all the script I need to run. This means if someone ever forgets to take a copy before upgrade, it is very simple to restore without having lost any IP from the body of the script as it is only the pointer to the proxy that is lost.

I understand why for simplicity of upgrade scripts this is done this way. It also forces the developer to consider that the calling parameters may have changed if the table definition changes. However in every one of the scripts I have like this, they only consume the primary keys or other primary fields that are unlikely to grow or change data type.

It would be great to have a convention where anything between tags named something like this,

<!—User Script start –>

and

<!—User Script end –>

is stripped out by the upgrade script and injected into the new version of the stored procedures when they are created. I’m tempted to put something together like this for ourselves to automate the upgrade process. I could also script a search for stored procedures containing theses tags to iterate though them all making the changes pre and post upgrade.

Perhaps the pre upgrade run of this could also store the parameter names and types to compare after upgrade and this raise a warning where they have changed.