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!