Connect from DB1's procedure to DB2 and insert row(s) of data

[vlad1]

Hi, SQL Anywhere v11 (but will migrate to v17 latest in a couple of months)

I can't seem to find easily in the documentation is there a quick way to connect from one database after insert of one row (from a function or a procedure) to another database to insert (copy) an identical row to the other database as well?

Just to underline, these databases are NOT synchronised through Mobilink or SQL Remote, that's not what I'm after.

All I need is that when 1 row is inserted in DB1, that a trigger on the new row calls a stored procedure, and that stored procedure makes a connection to DB2, so that I can execute an INSERT statement to DB2.

Could help with procedure syntax on how to connect like this or point me to useful docs pls? Hopefully connect via ODBC DSN name / connection string would be ideal. I suppose I can always use Remote Server to keep a permanent connection to DB2, but I'd like to see is there an easier way for quick connect / insert / commit / disconnect from DB2 from a procedure or a function?

Thanks

[VolkerBarth]

You are basically asking for Remote data access (aka "proxy tables").

For a similar question, see here.

If you do not want to have a permanent connection to the other database, you can disconnect it explicitly (in v11 via ALTER SERVER ... CONNECTION CLOSE..). However, I can't tell whether this is reasonable within a trigger, particularly if the trigger gets called frequently. A different approach might be to use a trigger to call an event that updates the second database on an independent connection.

---

Of course you could also use SQL Anywhere's builtin web server and web client facilities to transport data between both systems.

[vlad1]

Hi Volker, thanks very much for that, useful. I knew that using Remote Server is a safe plan B, but I wanted to see if there's a more... elegant solution.

No, the trigger would not get called very often, and the trigger would actually call a stored procedure, I would definitely not call all this sort of behaviour from a trigger due to all the locks.

Thanks!

[VolkerBarth]

[...]and the trigger would actually call a stored procedure

Well, that doesn't matter. Unless you use an event, the trigger itself and the stored procedure will be called within the same transaction, so the locking problem is the same as if the according statements from the procedure would be a part of the trigger's code itself.

[VolkerBarth]

FWIW, you can also create remote servers dynamically or supply variables to their definition, so the remote connection itself can vary without changing the remote server's definition. And instead of proxy tables you can also use FORWARD to apply random statements at the remote server.

[vlad1]

This could be interesting, I was just reading up on how to create (and drop) servers dynamically from the stored procedure, all I need to do is insert the identical row just inserted in DB1, row with about 80 columns, so if I could just use FORWARD to send that last INSERT statement which went into DB1 to DB2 that could be neat.

But to start with, I'm probably just going to keep the Remote Server running, unless I notice a noticeable drop in performance, which I doubt, same server, same table structure, only 1 row.

[Breck_Carter]

> keep the Remote Server running

The term "remote server" is a euphemism for a connection; it's not an actual server (athough the target of the connection is an actual server but that's another topic).

There is no performance penalty whatsoever for keeping an idle remote server connection open.

The biggest performance issues come from things like cross-server joins between proxy and real tables, because there is very little (i.e., no) query optimization done.

These observations come from ten years of experience on a commercial product based on proxy tables and procedures (Foxhound).

[vlad1]

Ah well, I can save myself a lot of trouble then, and not worry about resources if it's only one extra connection.

Thanks Breck!

[Breck_Carter]

FWIW here is a template for a V11 proxy table; a lot of this code is optional depending on your needs:

---------------------------------------------------------------------
-- On ddd2

BEGIN
   DROP TABLE t
   EXCEPTION WHEN OTHERS THEN
END;

CREATE TABLE t ( 
   pkey INTEGER NOT NULL PRIMARY KEY,
   data INTEGER NOT NULL );

INSERT t VALUES ( 1, 2 );
INSERT t VALUES ( 2, 2 );
COMMIT;

SELECT * FROM t ORDER BY t.pkey;

/*
pkey,data
1,2
2,2
*/

---------------------------------------------------------------------
-- On ddd1

BEGIN
   DROP TABLE t
   EXCEPTION WHEN OTHERS THEN
END;

BEGIN
   DROP TABLE proxy_t
   EXCEPTION WHEN OTHERS THEN
END;

BEGIN -- optional for SQL Anywhere
   DROP EXTERNLOGIN DBA TO ddd2_server;
   EXCEPTION WHEN OTHERS THEN
END;

BEGIN
   DROP SERVER ddd2_server;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE TABLE t ( 
   pkey INTEGER NOT NULL PRIMARY KEY,
   data INTEGER NOT NULL );

INSERT t VALUES ( 1, 1 );
INSERT t VALUES ( 3, 1 );
COMMIT;

CREATE SERVER ddd2_server CLASS 'SAODBC' 
   USING 'DRIVER=SQL Anywhere 11;ENG=ddd2;DBN=ddd2';

/* or...

CREATE SERVER ddd2_server CLASS 'SAODBC' 
   USING 'DSN=ddd2';

*/

CREATE EXTERNLOGIN DBA -- optional for SQL Anywhere
   TO ddd2_server 
   REMOTE LOGIN "DBA" IDENTIFIED BY 'sql';

CREATE EXISTING TABLE proxy_t 
   AT 'ddd2_server...t';

/* or...

CREATE EXISTING TABLE proxy_t (
   pkey   INTEGER NOT NULL,
   data   INTEGER NOT NULL,
   PRIMARY KEY ( pkey )
 ) AT 'ddd2_server...t';

*/

BEGIN 
SELECT * FROM t ORDER BY t.pkey;
SELECT * FROM proxy_t ORDER BY proxy_t.pkey;
END;

/*
pkey,data
1,1
3,1
pkey,data
1,2
2,2
*/


-- Note: ON EXISTING UPDATE does not work (yet?)...

INSERT t
    ON EXISTING SKIP
SELECT * 
  FROM proxy_t
 WHERE proxy_t.pkey NOT IN (
          SELECT t.pkey
            FROM t );
COMMIT;

SELECT * FROM t ORDER BY t.pkey;

/* Note that row 1,2 was not inserted...

pkey,data
1,1
2,2
3,1

*/

[vlad1]

Thanks very much Breck ❤️