Hello Andrew,

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

A diff script, either by checking and collecting the routines yourself or
another method, is required.

Next, you have a small config table of sorts in which you keep at least 1
record with a version number for your database schema.

In your application, you can check this number and execute the appropriate
update scripts if required.

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

SET TERM really is a non-SQL statement, but rather an instruction used by
the script parser. Firebird doesn't accept multiple statements at once, so
the script parser cuts a script into single statements. If you have a ALTER
TABLE, you end it with ";". In a stored procedure, being one big compound
statement, the ";" character is used as a terminator for individual
statements as well, this confuses script parsers. By using SET TERM, the
parser knows that it will ignore ";" as the statement separator for a while
and use a replacement separator instead.



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!