Subject Re: [firebird-support] Distributing/deploying Stored Procedures
Author Martijn Tonies (Upscene Productions)
Post date 2015-03-12T07:05:48Z
Hello Andrew,
 
Welcome to the Firebird community.
 
>I can pump SQL statements
through the application to make certain changes the first time a new release is run (alter table etc) but am concerned at how I am supposed to update SPs (potentially a large number) without access to the FB server.  I considered extracting the metadata(FlameRobin), deleting superfluous statements and pumping what is left through the app but that seems like A LOT of work.
 
 
When modifying metadata, make sure everyone is logged out of your application. Although it’s possible to “do metadata stuff” with people connected, it’s not the preferred way.
 
When you want to change a large number of procedures and triggers, it’s certainly possible to simply re-create them, certainly the easiest.
 
However, when dropping, there will be dependencies from one procedure to another, so they have to be dropped in a specific order.
 
There’s an alternative, however:
 
1) drop all triggers (that call procedures)
2) alter all procedures to an empty body, this keeps the parameter signature in place
3) drop all procedure
4) create all procedures with an empty body to establish parameter signatures
5) alter all procedures with complete body
6) create all triggers
 
If you use a tool like our Database Workbench tool, you can alternatively compare your development to your production database and see what meta data has changed and extract a script, or automatically record a change script while doing all changes.
 
Or extract a script for all procedures that does these create-with-empty-body-and-then-alter-cycle.
 
Hope this helps.
 
With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!
 
 
 
 

I’m a bit of a FB noob and am after some advice about Stored Procedures and the best way to distribute them to client sites.

 

We are in the process of redeveloping our software using FB as the back end (it was previously ISAM).  We have a reasonably large client base and our application is installed on many sites which run independently.

 

We intend to make use of Stored Procedures, Triggers etc and between releases (during development) these objects will be created, dropped, modified etc.  I have been tasked with finding a solution to distribute the update/upgrade SP.

 

At the client end they will access the data via our application (via ODBC).  They will have no direct access to the FB server and no ISQL command.   

 

I can pump SQL statements through the application to make certain changes the first time a new release is run (alter table etc) but am concerned at how I am supposed to update SPs (potentially a large number) without access to the FB server.  I considered extracting the metadata(FlameRobin), deleting superfluous statements and pumping what is left through the app but that seems like A LOT of work.

 

Also, do I bother with ‘alter procedure …..’ or do I simply drop them and (re)create them (after all, at release time, the SP is the SP) 

 

I am trying to automate the task as much as possible, I don’t want our support staff to have to intervene in an upgrade.

 

Surely I’m not the only one that has needed to do such a thing.  Does such a mechanism exist? Perhaps some suitable suggestions?