|
Hi, We have a remote server: CREATE SERVER "main_database" CLASS 'SAODBC' USING '...' READ ONLY;Is there any way to set remote server connection name individually for each connection? We have only one database user (for that kind of action) with external login to that server and all connections are using that DB user. When looking in the remote database (main_database) active connection names, I see something like ASACIS_653bf763d8e84707a90a6d6bba494e34_385. Of course, I can put CON parameter into remote server connection string but then all connections will have the same name. It would be nice to be able to set remote connection name individually. |
|
Try using ALTER SERVER before executing the first SQL statement that needs the remote server. Here is an end-to-end example; the ALTER SERVER ... CLOSE CONNECTION CURRENT statements may or may not be necessary in your setup to force the connection to be re-established with the new name. As you can see, the ALTER SERVER statements don't disturb existing connections. "%SQLANY12%\Bin32\dbinit.exe" ddd1.db "%SQLANY12%\Bin32\dbinit.exe" ddd2.db "%SQLANY12%\Bin32\dbspawn.exe" -f "%SQLANY12%\Bin32\dbeng12.exe" ddd1.db "%SQLANY12%\Bin32\dbspawn.exe" -f "%SQLANY12%\Bin32\dbeng12.exe" ddd2.db "%SQLANY12%\Bin32\dbisql.com" -c "ENG=ddd1;DBN=ddd1;UID=dba;PWD=sql;CON=ddd1" "%SQLANY12%\Bin32\dbisql.com" -c "ENG=ddd2;DBN=ddd2;UID=dba;PWD=sql;CON=ddd2" "%SQLANY12%\Bin32\dbisql.com" -c "ENG=ddd1;DBN=ddd1;UID=dba;PWD=sql;CON=user1" "%SQLANY12%\Bin32\dbisql.com" -c "ENG=ddd1;DBN=ddd1;UID=dba;PWD=sql;CON=user2" --------------------------------------------------------------------- -- On ddd2 (remote) 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; --------------------------------------------------------------------- -- On ddd1 (local) CREATE EXISTING TABLE ... AT ... 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 SERVER ddd2_server CLASS 'SAODBC' USING 'DRIVER=SQL Anywhere 11;ENG=ddd2;DBN=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'; SELECT * FROM proxy_t ORDER BY proxy_t.pkey; --------------------------------------------------------------------- -- On user1 (local) connect and display table. ALTER SERVER ddd2_server CONNECTION CLOSE CURRENT; ALTER SERVER ddd2_server CLASS 'SAODBC' USING 'DRIVER=SQL Anywhere 11;ENG=ddd2;DBN=ddd2;CON=from_user1'; SELECT * FROM proxy_t ORDER BY proxy_t.pkey; --------------------------------------------------------------------- -- On user2 (local) connect and display table. ALTER SERVER ddd2_server CONNECTION CLOSE CURRENT; ALTER SERVER ddd2_server CLASS 'SAODBC' USING 'DRIVER=SQL Anywhere 11;ENG=ddd2;DBN=ddd2;CON=from_user2'; SELECT * FROM proxy_t ORDER BY proxy_t.pkey; --------------------------------------------------------------------- -- On ddd2 (remote) display connections SELECT * FROM sa_conn_info() ORDER BY Name; Number,Name,Userid,DBNumber,LastReqTime,ReqType,CommLink,NodeAddr,ClientPort,ServerPort,BlockedOn,LockRowID,LockIndexID,LockTable,UncommitOps,ParentConnection 3,'ASACIS_edb83ceebed24a799f578b3ab02874ba_4','DBA',0,'2012-11-23 09:59:17.434','FETCH','local','',0,0,0,0,,'',0, 2,'ddd2','DBA',0,'2012-11-23 09:59:48.946','FETCH','local','',0,0,0,0,,'',0, 4,'from_user1','DBA',0,'2012-11-23 09:59:27.839','FETCH','local','',0,0,0,0,,'',0, 5,'from_user2','DBA',0,'2012-11-23 09:59:35.327','FETCH','local','',0,0,0,0,,'',0, Thank you! This seems to be a good (and the only one?) approach.
(26 Nov '12, 04:55)
Arthoor
|
|
There is a second approach you can take here, but before I detail that method, I thought I would give some background as to why remote data access connections to a remote SA set the connection name at all. Each database running on a server has a unique internal identifier (which is basically a GUID plus a little bit of extra information) and this internal identifier changes every time the database is restarted. When a remote data access connection is made to a remote SA server, the connection is always named with ASACISxxxx where the xxxx represents the internal identifier of the local SA database. When the connection is established at the remote SA server, the first thing that happens is the remote SA server identifies the connection as an incoming remote data access connection and uses the connection name to compare the internal database identifier of the originating database with the internal database identifier of the remote database being connected to. If the two internal database identifiers match, then the incoming remote connection is rejected with an appropriate error code. This is how SA prevents circular remote data access connections since allowing such connections is not only "silly" but there are also serious issues where the entire server can hang if both the local and remote connection on the same database attempt to modify the catalog. So you have to be careful when explicitly naming remote data access connections because you are opening yourself up to accidentally creating circular remote data access connections. Having said all that, if you are okay with taking the above risk, then there is a second approach that you can employ. First, you could change the login procedure or use some other method to create a variable and set it to the connection name you eventually want on the remote connection. Then you could use variables in the USING clause to set the remote connection to the variable you created. Here is an example of what you could do. Note that this example does not actually change the login procedure but does give you an idea of what you can do to make the connections unique. create server rem class 'saodbc' using '...;con={remote_connection_name}'; grant connect to user1 identified by user1; create externlogin user1 to rem remote login dba identified by sql; grant connect to user2 identified by user2; create externlogin user2 to rem remote login dba identified by sql; Now, start up two dbisql sessions, one with user1 as the userid and the second with user2. In each of the dbisql sessions, execute: create variable remote_connection_name varchar(256); set remote_connection_name = 'this remote connection is for ' || user_name(); forward to rem {select 1}; If you now run: sa_conn_info() on the remote database, you will see that there will be two connections one named "this remote connection is for user1" and the other named "this remote connection is for user2". Please note that variables in the using clause are new to SA 12.0.1, so you must have a recent SA 12.0.1 install to use this approach. For more information you can look at: Also, the second example on this page gives a nice overview on how variables in the USING and AT clauses can be used: Variable In USING and AT clause example HTH, Karim Ah, that's surely for Breck, a How-To circumvent the nasty "you-cannot-forward-to-yourself" limitation... by explicitly naming the connection... @Karim: Yes, I have noticed your clear stop sign:)
(08 Feb '13, 10:34)
Volker Barth
|
