Thanks Martijn. I had a quick look at DBWorkbench and it looks like a
good tool for getting a diff between a dev and prod database and I will
investigate it further, however it doesn't address the issue of
distributing (or applying/executing) that diff when I don't have access
to the server to be updated.
FYI, the IDE tool we are using is Clarion 5 - antiquated and not
mainstream - but it is our legacy (and we will change it eventually) but
our first concern is to change the database (from Topspeed) to Firebird.
As mentioned, I can pump SQL statements through the application. An
example of the syntax that Clarion uses for SQL is:
SQLFile{PROP:SQL} = 'SELECT field1,field2 FROM table1' |
& ' WHERE field1 > (SELECT max(field1)'
|
& ' FROM table2'
!Returns a result set that you get one
! row at a time using NEXT(SQLFile)
I can even do this (which is great for minor schema changes):
SQLFile{PROP:SQL} = 'ALTER TABLE Table1 ADD ColumnX CHAR(10);'
Not the most elegant approach but we can call SP and pass parameters and
generally get by for the time being.
The problem occurs as soon as it try any DDL(?) 'SET TERM ^; ALTER
PROCEDURE PROC1 ....' for example, it errors on SET TERM^. Also,
formatting a large number of sizeable ALTER PROCEDURE statements to fit
into the {PROP:SQL} statement (while paying close attention to the
apostrophes) will likely be a huge PITA.
Surely others have servers they have no access to, or have
stand-alone/off-the-shelf/shrinkwrap applications that must update these
objects. How do they do it?
Or must I ship ISQL.exe with my application and INPUT scripts?
Thanks again for your input.
Andrew
________________________________
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]
Sent: Thursday, 12 March 2015 6:06 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Distributing/deploying Stored Procedures
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?
