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:
DROP TABLE Tank;
DROP TABLE TankChanges;
DROP PROCEDURE TankChangedColumns;
CREATE TABLE Tank (
CREATE TABLE TankChanges (
INSERT Tank VALUES ( 1, 1, 1 );
I tried to achieve this with the following procedure:
create procedure TankChangedColumns(in in_TankId integer)
But the procedure stops after checking the first column.
Can anyone help?
asked 15 Mar '10, 09:20
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.
will show identical rows, whereas
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.
answered 15 Mar '10, 11:08
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
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:
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.