Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - SQL Reference » SQL statements » Alphabetical list of SQL statements

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
  • MobiLink general use
    CREATE PUBLICATION [ IF NOT EXISTS ] [ owner. ]  publication-name
    ( article-definition, ... )
    article-definition :
      TABLE table-name [ ( column-name, ... ) ]
    [ WHERE search-condition ]
  • MobiLink scripted upload
    CREATE PUBLICATION [ IF NOT EXISTS ] [ 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 }, ... ) ]
  • MobiLink download-only publications
    CREATE PUBLICATION [ IF NOT EXISTS ] [ owner.] publication-name
    FOR DOWNLOAD ONLY
    ( article-definition, ... )
    article-definition :  TABLE table-name [ ( column-name, ... ) ]
  • 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 the MobiLink scripted upload syntax, which is used for publications that perform scripted uploads, the article description also registers the scripts that are used to define the upload.

    In the MobiLink download-only publications, 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.

The MobiLink scripted upload syntax 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.

The MobiLink download-only publications syntax 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.

For all syntaxes, you must have exclusive access to all tables referred to in the statement to execute the statement.

Privileges

You must have the SYS_REPLICATION_ADMIN_ROLE system role.

Side effects

Automatic commit.

Standards
  • ANSI/ISO SQL Standard

    Not in the standard.

Example

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

CREATE PUBLICATION pub_contact (
   TABLE GROUPO.Contacts,
   TABLE GROUPO.Customers
);

The following statement publishes only some columns of one table.

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

The following statement publishes only the rows for customer located in New York (NY) by including a WHERE clause that tests the State column of the Customers table.

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

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 GROUPO.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 (
      GROUPO.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
      ),
      GROUPO.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 (
   GROUPO.TABLE t1
);