Updating publication through ISQL

[former_member329524]

Hello, all

I have a question:

Is there a way to update publication articles using ISQL.

Specifically, I need to update the "WHERE" part of all articles in the publication to be something else.

Obviously, I cannot just run a command like this:

//-------------------------

update sys.SYSARTICLE

set where_expr = '"get_sync_user"("c_hotel_id") = ''fattal'''

where publication_id = 24

//-----------------------

Is there a way to export a publication into a text file without unloading the whole DB?

[chris_keating · Accepted solution]

ALTER PUBICATION <your_pub_name>

     ALTER TABLE <your_table_name> WHERE get_sync_user(c_hotel_id) = 'fattal'

[former_member329524 · Accepted solution]

Yes, I know of that syntax.

I was more interested in how to affect all articles in the publication without specifying them explicitly.

The "WHERE" clause is the same everywhere, so, I hoped, it would be possible to update all the articles at once.

As a workaround, I was hoping I could get a "CREATE PUBLICATION" command syntax from the DB without unloading the whole DB. Then, I could do a quick replace of the where in the file and then run the "ALTER PUBLICATION" command with the new syntax.

[chris_keating · Accepted solution]

. If this is in a SQL Anywhere database, here is a rough example of SQL to do this:

begin

declare alterpubstmt long varchar;

declare publications_crsr cursor for select publication_id, publication_name from syspublication;  

declare pub_id integer;

declare pub_name char(128);

open publications_crsr;

lp: loop

    fetch next publications_crsr into pub_id, pub_name;

    set alterpubstmt = string( 'alter publication ', pub_name );

    if sqlcode <> 0 then leave lp end if;

    begin

        declare tablepubs_crsr cursor for select table_name, where_expr from sysarticle sa join systable st on sa.table_id = st.table_id where publication_id = pub_id;

        declare table_name char(128);

        declare where_expr long varchar;

        open tablepubs_crsr;

        lp1: loop

            fetch next tablepubs_crsr into table_name, where_expr;

            if sqlcode <> 0 then leave lp1 end if;

            set alterpubstmt = string( alterpubstmt, ' alter table ', table_name );

            set alterpubstmt = string( alterpubstmt, ' where ', 'get_sync_user(c_hotel_id) = ''fattal''', ',' )

        end loop lp1;

        set alterpubstmt = left( alterpubstmt, length(alterpubstmt) - 1 );

    end;

    execute immediate alterpubstmt;

end loop lp;

end

Currently, this assumes that all tables in the pub will get the where clause that you posted.

[former_member329524 · Accepted solution]

Thank you, Chris

I shall try that