Hopefully I can explain this properly...
Currently we have a working Mobilink (v12.0.1) setup with Windows Mobile clients - and has been running happily for some time. Now though, I need to update one of the tables to add an additional column. I have no problems with creating a new sync script version, or with updating remote databases to use the new script version - this is all fine.
My question is this - how do I go about changing my 'new database script' (where I create new DB's for new clients) to point just that updated table at 'v2' instead of 'v1', whilst leaving all the other tables using 'v1'?
Currently that script creates all the tables, then the publication / ML user. Then last of all it creates the synchronization subscription, which sets the script version as 'v1'. But if I've got a single table that needs to use 'v2', how can I specify that?
I don't really want to have to put all of the table scripts into 'v2' if I don't have to, although I can see that this is one such solution.
asked 19 Feb '14, 06:52
I wouldn't suggest having a single remote database synchronize using two different script versions. Defining synchronization scripts for all tables in 'v2' in the consolidated database and having newly created remote databases (with the extra column) synchronize using the 'v2' script version is the correct way to handle this.
If a remote database is using the "old" schema (i.e. updated table does not have the extra column), it should synchronize using the "old" script version ('v1' in your example). If a remote database is using the "new" schema (i.e. updated table does have the extra column), it should synchronize using the "new" script version ('v2' in your example).
The START SYNCHRONIZATION SCHEMA CHANGE (and subsequant STOP SYNCHRONIZATION SCHEMA CHANGE) command is used when you alter the schema of the remote database and move to a different script version.
Aside : Copying a script version in a consolidated database is easy. I've attached a simple SQL Anywhere stored procedure that makes a copy of a script version in an SQL Anywhere consolidated database. After you've copied 'v1' to 'v2' modify the scripts for my_updated_table in 'v2', and you're ready to go.
create procedure CopyScriptVersion ( in @orig varchar(128), in @dest varchar(128) ) begin declare cur_table cursor for select event,table_name,script_language,script from ml_table_scripts where version = @orig; declare cur_conn cursor for select event,script_language,script from ml_connection_scripts where version = @orig; declare cur_col cursor for select mlt.name, mlc.name, mlc.type from ml_column mlc, ml_script_version mlsv, ml_table mlt where mlsv.version_id = mlc.version_id and mlt.table_id = mlc.table_id and mlsv.name = @orig order by mlt.name, mlc.idx; declare @event varchar(128); declare @table_name varchar(128); declare @script_lang varchar(128); declare @column_name varchar(128); declare @type varchar(128); declare @script long varchar; open cur_table; fetch first cur_table into @event,@table_name,@script_lang,@script; while ( sqlcode = 0 ) loop call ml_add_lang_table_script( @dest, @table_name, @event, @script_lang, @script ); fetch next cur_table into @event,@table_name,@script_lang,@script; end loop; close cur_table; open cur_conn; fetch first cur_conn into @event,@script_lang,@script; while ( sqlcode = 0 ) loop call ml_add_lang_connection_script( @dest, @event, @script_lang, @script ); fetch next cur_conn into @event,@script_lang,@script; end loop; close cur_conn; open cur_col; fetch first cur_col into @table_name, @column_name, @type; while ( sqlcode = 0 ) loop call ml_add_column( @dest, @table_name, @column_name, @type ); fetch next cur_col into @table_name, @column_name, @type; end loop; close cur_col; end;
answered 19 Feb '14, 08:47