on 10-29-2014 12:06 PM
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?
ALTER PUBICATION <your_pub_name>
ALTER TABLE <your_table_name> WHERE get_sync_user(c_hotel_id) = 'fattal'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
. 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.
User | Count |
---|---|
90 | |
10 | |
10 | |
10 | |
7 | |
7 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.