CREATE PUBLICATION statement [MobiLink] [SQL Remote]

Creates a publication. In MobiLink, a publication identifies synchronized data in a SQL Anywhere remote database. In SQL Remote, publications identify replicated data in both consolidated and remote databases.

Syntax 1 (MobiLink general use)

CREATE PUBLICATION [ IF NOT EXISTS ] [ owner. ]  publication-name
( article-definition, ... )

article-definition :
  TABLE table-name [ ( column-name, ... ) ]
[ WHERE search-condition ]

Syntax 2 (MobiLink scripted upload)

CREATE PUBLICATION  [ IF NOT EXISTS ] [ owner.]
WITH SCRIPTED UPLOAD 
( article-definition, ... )

article-definition :
  TABLE table-name [ ( column-name, ... ) ] 
[ USING ( [ PROCEDURE ] [ owner.][procedure-name ] 
   FOR UPLOAD { INSERT | DELETE | UPDATE }, ... ) ]

Syntax 3 (MobiLink download-only publications)

CREATE PUBLICATION [ IF NOT EXISTS ] [ owner.] publication-name
FOR DOWNLOAD ONLY
( article-definition, ... )

article-definition :  TABLE table-name [ ( column-name, ... ) ]

Syntax 4 (SQL Remote)

CREATE PUBLICATION [ IF NOT EXISTS ] [ owner.] publication-name
( article-definition, ... )

article-definition :
  TABLE table-name [ ( column-name, ... ) ]
[ WHERE search-condition ]
[ SUBSCRIBE BY expression ]

Parameters

IF NOT EXISTS clause When the IF NOT EXISTS clause is specified and the named publication already exists, no changes are made and an error is not returned.
article-definition Publications are built from articles. Each article identifies the rows and columns of a single table that are included in the publication. A publication may not contain two articles that refer to the same table.
If a list of column-names is included in an article, only those columns are included in the publication. If no column-names are listed, all columns in the table are include in the publication. For MobiLink synchronization, if column-names are listed then all columns in the primary key of the table must be included in the list.

In Syntax 2, which is used for publications that perform scripted uploads, the article description also registers the scripts that are used to define the upload.

In Syntax 3, which is used for download-only publications, the article specifies only the tables and columns to be downloaded.
WHERE clause The WHERE clause lets you define the subset of rows in a table to be included in an article.
In MobiLink applications, the WHERE clause affects the rows included in the upload. (The download is defined by the download_cursor script.) In MobiLink SQL Anywhere remote databases, the WHERE clause can only refer to columns included in the article, and cannot contain subqueries, variables, or non-deterministic functions.
SUBSCRIBE BY clause In SQL Remote, one way of defining a subset of rows of a table to be included in an article is to use a SUBSCRIBE BY clause. This clause allows many different subscribers to receive different rows from a table in a single publication definition.

Remarks

The CREATE PUBLICATION statement creates a publication in the database. A publication can be created for another user by specifying an owner name.

In MobiLink, publications are required in SQL Anywhere remote databases, and are optional in UltraLite databases. These publications and the subscriptions to them determine which data is uploaded to the MobiLink server.

You set options for a MobiLink publication with the ADD OPTION clause in the CREATE SYNCHRONIZATION SUBSCRIPTION statement or ALTER SYNCHRONIZATION SUBSCRIPTION statement.

Syntax 2 creates a publication for scripted uploads. Use the USING clause to register the stored procedures that you want to use to define the upload. For each table, you can use up to three stored procedures: one each for inserts, deletes, and updates.

Syntax 3 creates a download-only publication that can be synchronized with no transaction log file. When download-only publications are synchronized, downloaded rows may overwrite changes that were made to those rows in the remote database.

In SQL Remote, publishing is a two-way operation, as data can be entered at both consolidated and remote databases. In a SQL Remote installation, any consolidated database and all remote databases must have the same publication defined. Running the SQL Remote Extraction utility from a consolidated database automatically executes the correct CREATE PUBLICATION statement in the remote database.

Permissions

Side effects

Standards and compatibility

Example

The following statement publishes all columns and rows of two tables.

CREATE PUBLICATION pub_contact (
   TABLE Contacts,
   TABLE Company
);

The following statement publishes only some columns of one table.

CREATE PUBLICATION pub_customer (
   TABLE Customers ( ID, CompanyName, City )
);

The following statement publishes only the active customer rows by including a WHERE clause that tests the Status column of the Customers table.

CREATE PUBLICATION pub_customer (
   TABLE Customers ( ID, CompanyName, City, State, Status )
   WHERE Status = 'active'
);

The following statement publishes only some rows by providing a subscribe-by value. This method can be used only with SQL Remote.

CREATE PUBLICATION pub_customer (
   TABLE Customers ( ID, CompanyName, City, State )
   SUBSCRIBE BY State
);

The subscribe-by value is used as follows when you create a SQL Remote subscription.

CREATE SUBSCRIPTION TO pub_customer ( 'NY' )
   FOR jsmith;

The following example creates a MobiLink publication that uses scripted uploads:

CREATE PUBLICATION pub WITH SCRIPTED UPLOAD (
      TABLE t1 (a, b, c) USING (
         PROCEDURE my.t1_ui FOR UPLOAD INSERT,
         PROCEDURE my.t1_ud FOR UPLOAD DELETE,
         PROCEDURE my.t1_uu FOR UPLOAD UPDATE
      ),
      TABLE t2 AS my_t2 USING (
         PROCEDURE my.t2_ui FOR UPLOAD INSERT
      )
   );

The following example creates a download-only publication:

CREATE PUBLICATION p1 FOR DOWNLOAD ONLY (
   TABLE t1
);

Discuss this page in DocCommentXchange.