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

asked 22 Nov, 06:14

PcrMember's gravatar image

PcrMember
1365715
accept rate: 0%

edited 22 Nov, 06:19


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.

permanent link

answered 22 Nov, 06:30

Volker%20Barth's gravatar image

Volker Barth
37.8k347515785
accept rate: 34%

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!

(22 Nov, 06:38) PcrMember
Replies hidden
1

[...]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.

(22 Nov, 07:16) Volker Barth
1

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.

(22 Nov, 07:22) Volker Barth

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.

(22 Nov, 07:29) PcrMember
Comment Text Removed
2

> 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).

(22 Nov, 08:03) Breck Carter

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!

(22 Nov, 08:06) PcrMember
showing 1 of 6 show all flat view

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

*/
permanent link

answered 22 Nov, 08:08

Breck%20Carter's gravatar image

Breck Carter
32.0k5207101037
accept rate: 20%

Thanks very much Breck <3

(22 Nov, 11:27) PcrMember
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×120
×76
×4

question asked: 22 Nov, 06:14

question was seen: 73 times

last updated: 22 Nov, 11:27