SQL Anywhere Mobile - HOwto: upgrade the schema of a remote

Author

HOwto: upgrade the schema of a remote

Ignacio Mateos

2005-07-25, 7:34 am

Hi:

I wonder what is the correct way to alter a table that is part of a
publication in a remote database.

This is how I do it: delete the table from the publication, alter the
schema, add the table to the publication.

Now it does not sync, then I drop the remote user, create it again and do a
full sync.

But I won't be able to still this in production anymore.

How can I then alter a table and still keep the sync process working?

Regards

David Fishburn

2005-07-25, 9:30 am

"Ignacio Mateos" <imateos@telefonica.net> wrote in
news:42e4e168$1@foru
ms-2-dub of sybase.public.sqlanywhere.mobilink:

IM> I wonder what is the correct way to alter a table that is part of a
IM> publication in a remote database.
IM>
IM> This is how I do it: delete the table from the publication, alter the
IM> schema, add the table to the publication.
IM>
IM> Now it does not sync, then I drop the remote user, create it again
and
IM> do a full sync.
IM>
IM> But I won't be able to still this in production anymore.
IM>
IM> How can I then alter a table and still keep the sync process working?

You need to write a stored procedure in the remote database.

Probably the easiest way is to INSERT all of your schema changes into a
table and have them synced down to the device.

OR

Put your schema changes in a file.
Get that file to the device (somehow).

Then write one of these:
MobiLink Clients
Dbmlsync Client Event Hooks
sp_hook_dbmlsync_sch
ema_upgrade

In this procedure, you would get your schema changes (either from your
own table, or using the ASA xp_read_file() function).

Then use the EXECUTE IMMEDIATE statement to execute it.

By doing this in this stored procedure, you know it is safe to be done.

You do not have to remove the table from the publication and you do not
need to drop the table, simply alter both the table and the publication
(if necessary).

--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).

EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm

Developer Community / Whitepapers
http://www.ianywhere.com/developer

CaseXpress - to report bugs
http://casexpress.sybase.com

CodeXchange - Free samples
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]

Ignacio Mateos

2005-07-25, 11:25 am

Thank you for your answer, sounds kind of difficult, I will try it.

Is this procedure described in the manual?

Regards

"David Fishburn" < fishburn_spam_off@ia
nywhere.com> escribió en el mensaje
news:Xns969E70631943
2fishburnsybasecom@1
0.22.241.106...
> "Ignacio Mateos" <imateos@telefonica.net> wrote in
> news:42e4e168$1@foru
ms-2-dub of sybase.public.sqlanywhere.mobilink:
>
> IM> I wonder what is the correct way to alter a table that is part of a
> IM> publication in a remote database.
> IM>
> IM> This is how I do it: delete the table from the publication, alter the
> IM> schema, add the table to the publication.
> IM>
> IM> Now it does not sync, then I drop the remote user, create it again
> and
> IM> do a full sync.
> IM>
> IM> But I won't be able to still this in production anymore.
> IM>
> IM> How can I then alter a table and still keep the sync process working?
>
> You need to write a stored procedure in the remote database.
>
> Probably the easiest way is to INSERT all of your schema changes into a
> table and have them synced down to the device.
>
> OR
>
> Put your schema changes in a file.
> Get that file to the device (somehow).
>
> Then write one of these:
> MobiLink Clients
> Dbmlsync Client Event Hooks
> sp_hook_dbmlsync_sch
ema_upgrade
>
> In this procedure, you would get your schema changes (either from your
> own table, or using the ASA xp_read_file() function).
>
> Then use the EXECUTE IMMEDIATE statement to execute it.
>
>
> By doing this in this stored procedure, you know it is safe to be done.
>
> You do not have to remove the table from the publication and you do not
> need to drop the table, simply alter both the table and the publication
> (if necessary).
>
>
> --
> David Fishburn
> Certified ASA Developer Version 8
> iAnywhere Solutions - Sybase
> Professional Services
> Please only post to the newsgroup
> Please ALWAYS include version and MORE importantly BUILD number with
> EACH post (dbeng9 -v).
>
> EBFs and Maintenance Releases
> http://downloads.sybase.com/swx/sdmain.stm
>
> Developer Community / Whitepapers
> http://www.ianywhere.com/developer
>
> CaseXpress - to report bugs
> http://casexpress.sybase.com
>
> CodeXchange - Free samples
> [url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]
>

Breck Carter [TeamSybase]

2005-07-25, 1:41 pm

On 25 Jul 2005 08:02:57 -0700, David Fishburn
< fishburn_spam_off@ia
nywhere.com> wrote:

>You do not have to remove the table from the publication and you do not
>need to drop the table, simply alter both the table and the publication
>(if necessary).

Are you saying that ALTER TABLE changes made by an
sp_hook_dbmlsync_sch
ema_upgrade procedure are exempt from the usual
SQLCODE -819 error "Table 'xxx' is part of a synchronization
definition"?

I've never actually tried *leaving out* the ALTER PUBLICATION DROP and
ADD statements from the downloaded stream :)

Breck

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

David Fishburn

2005-07-26, 1:24 pm

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
news:41aae1l1vt40u0f
56drhk1cpcsn21dgf48@
4ax.com of
sybase.public.sqlanywhere.mobilink:

BC> On 25 Jul 2005 08:02:57 -0700, David Fishburn
BC> < fishburn_spam_off@ia
nywhere.com> wrote:
BC>
BC>>You do not have to remove the table from the publication and you do
BC>>not need to drop the table, simply alter both the table and the
BC>>publication (if necessary).
BC>
BC> Are you saying that ALTER TABLE changes made by an
BC> sp_hook_dbmlsync_sch
ema_upgrade procedure are exempt from the usual
BC> SQLCODE -819 error "Table 'xxx' is part of a synchronization
BC> definition"?

Yes, since it is on the dbmlsync connection.
That is *why* you want to use this routine.

BC> I've never actually tried *leaving out* the ALTER PUBLICATION DROP
and
BC> ADD statements from the downloaded stream :)

If you are just altering the table article to add new columns to the
list, you would just modify it. Just like you don't drop a table to add
a new column, you just modify it to add a new column.

--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).

EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm

Developer Community / Whitepapers
http://www.ianywhere.com/developer

CaseXpress - to report bugs
http://casexpress.sybase.com

CodeXchange - Free samples
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]

Sponsored Links