on 2010 Mar 15 9:20 AM
What I'm trying to do is the following:
I'm synchronizing data from 1 database to another database using proxy tables. Because sybase doesn't allow me to update proxy tables currently I'm deleting the record and then add the record again with the new info.
When the record is deleted also some other records are deleted in other tables because I don't know what columns exactly have been changed.
Now the customer only wants the records to be deleted when some columns are changed. I'm trying to achieve this like this:
I want the check to use the SysColumns table do be able to dynamicly check the data for every column in the 'Table' record.
To create the tables and data into the tables use:
BEGIN
DROP TABLE Tank;
EXCEPTION WHEN OTHERS THEN
END;
BEGIN
DROP TABLE TankChanges;
EXCEPTION WHEN OTHERS THEN
END;
BEGIN
DROP PROCEDURE TankChangedColumns;
EXCEPTION WHEN OTHERS THEN
END;
CREATE TABLE Tank (
Id INTEGER NOT NULL PRIMARY KEY,
data1 INTEGER NOT NULL,
data2 INTEGER NOT NULL );
CREATE TABLE TankChanges (
Id INTEGER NOT NULL PRIMARY KEY,
data1 INTEGER NOT NULL,
data2 INTEGER NOT NULL );
INSERT Tank VALUES ( 1, 1, 1 );
INSERT Tank VALUES ( 2, 2, 2 );
INSERT Tank VALUES ( 3, 3, 3 );
INSERT TankChanges SELECT * FROM Tank;
UPDATE TankChanges SET data1 = 92 WHERE Id = 2;
UPDATE TankChanges SET data2 = 93 WHERE Id = 2;
COMMIT;
I tried to achieve this with the following procedure:
create procedure TankChangedColumns(in in_TankId integer)
begin
declare local_ColumnName long varchar;
declare err_notfound exception for sqlstate value '02000';
//
declare cursor_ColumnNames dynamic scroll cursor for
select column_Name
from SysColumn SC
where exists(select Table_Id from systable where table_name = 'Tank'
and Table_Id = SC.Table_Id)
Order by column_id for read only;
//
open cursor_ColumnNames;
//
ColumnNamesLoop:
loop
fetch next cursor_ColumnNames into local_ColumnName;
if sqlstate = err_notfound then leave DepotCleaningLoop end if;
//
message local_ColumnName type info to client;
execute immediate
'select (if TC.' || local_columnname ||' <> T.' ||
local_Columnname || ' then 1 else 0 endif) as Differs
from Tank T join TankChanges TC on T.Id = TC.Id
where T.Id = '|| in_TankId;
end loop;
//
close cursor_ColumnNames;
end;
But the procedure stops after checking the first column.
Can anyone help?
Regards,
Frank Vestjens
Request clarification before answering.
Frank,
I think Breck already answered your question in the sqlanywhere.general newsgroup.
The issue is that you're executing the EXECUTE IMMEDIATE statement in the middle of a loop, but the EXECUTE IMMEDIATE is issuing a SELECT that returns a result set. One cannot establish a result set in the middle of a stored procedure like this; you should get the error -946 for attempting to issue a SELECT in this context.
The typical way one establishes a result set within a procedure is to construct a result using a temporary table, and then in the final step of the procedure execute SELECT * FROM "temporary table". Here is a procedure that has logic similar to yours that does exactly this. The procedure assumes the existence of a table "foo". I tested the procedure below with
CREATE TABLE FOO (X INT, Y INT, Z INT);
alter procedure TankChangedColumns()
begin
declare local_ColumnName long varchar;
declare query long varchar;
declare local temporary table foo_columns (colid int, tableid int, colname char(128) );
declare err_notfound exception for sqlstate value '02000';
//
declare cursor_ColumnNames dynamic scroll cursor for
select column_Name
from SysColumn SC
where exists(select Table_Id from systable where table_name = 'foo'
and Table_Id = SC.Table_Id)
Order by column_id for read only;
//
open cursor_ColumnNames;
//
ColumnNamesLoop:
loop
fetch next cursor_ColumnNames into local_ColumnName;
if sqlstate = err_notfound then leave ColumnNamesLoop end if;
//
message local_ColumnName type info to client;
set query =
'insert into foo_columns select column_id, table_id, column_name' ||
' from syscolumn where column_name = ''' || local_ColumnName || '''' ;
execute immediate query;
end loop;
//
close cursor_ColumnNames;
select * from foo_columns;
end
After creating the procedure, Call TankChangedColumns() returns a three-row result set listing columns X, Y, and Z.
I'm also puzzled by this statement:
I'm synchronizing data from 1 database to another database using proxy tables. Because sybase doesn't allow me to update proxy tables currently I'm deleting the record and then add the record again with the new info.
SQL Anywhere's support for proxy tables does not include updating a proxy table through a join with another table from a different server, but UPDATEs to proxy tables are supported.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I would suggest a solution without using a cursor.
You can use the set-operators EXCEPT and INTERSECT to find those rows that have (not) been changed between both tables, i.e.
SELECT * FROM TankChanges
INTERSECT
SELECT * FROM Tank
ORDER by id
will show identical rows, whereas
SELECT * FROM TankChanges
EXCEPT
SELECT * FROM Tank
ORDER by id
will list those rows that have been altered (or inserted) in TankChanges.
If you are only interested in changes of particular columns, you could generate the according SELECT-list based on this column list (maybe by means of EXECUTE IMMEDIATE).
If you want to have different actions based on whether the differing rows are in this list you might combine both actions with the help of a MERGE statement.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
74 | |
30 | |
8 | |
8 | |
7 | |
6 | |
6 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.