Subject RE: [firebird-support] Distributing/deploying Stored Procedures
Author Svein Erling Tysvær
>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.

Basically, there are two good, competing Firebird tools: Database Workbench and IB Expert. Most of us probably just started using one of them and are satisfied with the one we chose. I think that once upon a time, DB Workbench was a bit easier to use, whereas IB Expert had a few more features, but DB Workbench has at least added new features since then (I've never tried IB Expert, so I don't know how that has changed).

>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^.

Yes, this is DDL (data definition language) as opposed to DML (data manipulation language, typically INSERT/SELECT/DELETE). However, I don't think your problem is related to this difference, it is more likely that you're using some kind of DSQL, cursor or query component (which typically accept one statement) where you should use a script component that can accept multiple statements (since SET TERM is one statement in itself, it's only usable in scripts).

>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.

I do remember having written SELECT 'EXECUTE STATEMENT ... with '''''''' inside the statement, though I've no knowledge about {PROP:SQL}.