CREATE PUBLICATION statement [MobiLink] [SQL Remote]

Use this statement to create 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 [ owner.]publication-name
( article-definition, ... )

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

Syntax 2 (MobiLink scripted upload)

CREATE PUBLICATION [ owner.]publication-name
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 [ owner.]publication-name
FOR DOWNLOAD ONLY
( article-definition, ... )

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

Syntax 4 (SQL Remote)

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

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

Parameters

article-definition Publications are built from articles. To include more than one article, separate article definitions with commas. Each article is a table or part of a table. An article can be a vertical partition of a table (a subset of the table's columns), a horizontal partition (a subset of the table's rows based on a WHERE clause) or a vertical and horizontal partition.
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. See Creating publications for scripted 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 is a way of defining the subset of rows of 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 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

Must have DBA authority. Requires exclusive access to all tables referred to in the statement.

Side effects

Automatic commit.

Standards and compatibility

SQL/2003 Vendor extension.

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
);

Send feedback about this page via email or DocCommentXchange