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