SAP SQL Anywhere

Question

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:

Create a copy of the current table called 'Table'Changes
Set proxy to this 'Table'Changes table
Create an insert trigger that compares the values for every column in the 'Table'Changes record with the same column in the 'Table' record.
If the column(s) that differ appear in the list the customer has defined I have to remove the record from 'Table' and add it again. If the column(s) that differ not appear in the list then I can update the 'Table' reocrd.

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

Answer 1

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.

Answer 2

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.

SAP SQL Anywhere

How to retrieve the names of all changed columns between to identical tables

Follow this question

Related questions