The USE Statement In SQL Anywhere

Question: How do I code "USE database" in SQL Anywhere? I'm trying to write a batch file to update several databases on the same server.

Answer: Use (pun intended) the CONNECT and SET CONNECTION statements in your SQL file, then run that SQL file in batch mode via dbisql.

Here is a simple example of how CONNECT can establish named connections to three different databases, and then switch back and forth between those connections via SET CONNECTION:

CONNECT TO ddd1 DATABASE ddd1 AS ddd1_con USER dba IDENTIFIED BY sql;
CONNECT TO ddd2 DATABASE ddd2 AS ddd2_con USER dba IDENTIFIED BY sql;
CONNECT TO ddd3 DATABASE ddd3 AS ddd3_con USER dba IDENTIFIED BY sql;

SET CONNECTION ddd1_con;
SET CONNECTION ddd2_con;
SET CONNECTION ddd3_con;

Caution: This stuff doesn't fully work in dbisql's GUI mode. I don't know why, but there you go... in batch mode the CONNECT statement can be used to create and maintain a named connection that can be reused via SET CONNECTION. However, in GUI mode the second CONNECT closes the first connection even if you gave that one a name. Yup, that's a bummer... but you can use CONNECT to switch back and forth between databases in GUI mode if you don't mind only having one connection open at a time, so maybe it doesn't matter to you.

There are two different forms of CONNECT; the format shown above is for shared memory connections, and CONNECT USING can be used with TCPIP connections:

CONNECT USING 'SERVER=ddd1;DBN=ddd1;UID=dba;PWD=sql;LINKS=TCPIP;CON=ddd1_con';
CONNECT USING 'SERVER=ddd2;DBN=ddd2;UID=dba;PWD=sql;LINKS=TCPIP;CON=ddd2_con';
CONNECT USING 'SERVER=ddd3;DBN=ddd3;UID=dba;PWD=sql;LINKS=TCPIP;CON=ddd3_con';

SET CONNECTION ddd1_con;
SET CONNECTION ddd2_con;
SET CONNECTION ddd3_con;

Caution: CONNECT USING is a SQL statement so don't use "double quotes" around the connection string, use 'single quotes' ...and don't forget the CON= connection parameter; you have to give the connection a name if you want to use the SET CONNECTION statement.

For the skeptical among you, here's proof that it works. First, here a Windows command file to create and start three separate databases, then run dbisql in batch mode to run a SQL script:

"%SQLANY12%\bin32\dbinit.exe" ddd1.db
"%SQLANY12%\bin32\dbinit.exe" ddd2.db
"%SQLANY12%\bin32\dbinit.exe" ddd3.db

"%SQLANY12%\bin32\dbspawn.exe" -f "%SQLANY12%\bin32\dbeng12.exe" -x none ddd1.db
"%SQLANY12%\bin32\dbspawn.exe" -f "%SQLANY12%\bin32\dbeng12.exe" -x none ddd2.db
"%SQLANY12%\bin32\dbspawn.exe" -f "%SQLANY12%\bin32\dbeng12.exe" -x none ddd3.db

"%SQLANY12%\bin32\dbisql.com" READ ENCODING Cp1252 "06s script demonstrate_set_connection.sql"

Here's what the SQL script "06s script demonstrate_set_connection.sql" contains:

----------------------------------------------------------------------------
-- Establish 3 connections from one dbisql session.

CONNECT TO ddd1 DATABASE ddd1 AS ddd1_con USER dba IDENTIFIED BY sql;
MESSAGE STRING ( CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Name' ), 
   ' CONNECT TO ', PROPERTY ( 'Name' ),
   ' DATABASE ', DB_PROPERTY ( 'Name' ), 
   ' AS ', CONNECTION_PROPERTY ( 'Name' ) ) TO CONSOLE;

CONNECT TO ddd2 DATABASE ddd2 AS ddd2_con USER dba IDENTIFIED BY sql;
MESSAGE STRING ( CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Name' ), 
   ' CONNECT TO ', PROPERTY ( 'Name' ),
   ' DATABASE ', DB_PROPERTY ( 'Name' ), 
   ' AS ', CONNECTION_PROPERTY ( 'Name' ) ) TO CONSOLE;

CONNECT TO ddd3 DATABASE ddd3 AS ddd3_con USER dba IDENTIFIED BY sql;
MESSAGE STRING ( CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Name' ), 
   ' CONNECT TO ', PROPERTY ( 'Name' ),
   ' DATABASE ', DB_PROPERTY ( 'Name' ), 
   ' AS ', CONNECTION_PROPERTY ( 'Name' ) ) TO CONSOLE;

----------------------------------------------------------------------------
-- INSERT a row on each connection.

SET CONNECTION ddd1_con;
MESSAGE STRING ( CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Name' ), ' SET CONNECTION ddd1_con;' ) TO CONSOLE;
BEGIN
   DROP TABLE t1;
   EXCEPTION WHEN OTHERS THEN
END;
CREATE TABLE t1 ( pkey INTEGER NOT NULL PRIMARY KEY );
INSERT t1 VALUES ( 111 );
MESSAGE STRING ( CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Name' ), ' INSERT t1 VALUES ( 111 );' ) TO CONSOLE;

WAITFOR DELAY '00:00:02';

SET CONNECTION ddd2_con;
MESSAGE STRING ( CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Name' ), ' SET CONNECTION ddd2_con;' ) TO CONSOLE;
BEGIN
   DROP TABLE t2;
   EXCEPTION WHEN OTHERS THEN
END;
CREATE TABLE t2 ( pkey INTEGER NOT NULL PRIMARY KEY );
INSERT t2 VALUES ( 222 );
MESSAGE STRING ( CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Name' ), ' INSERT t2 VALUES ( 222 );' ) TO CONSOLE;

WAITFOR DELAY '00:00:02';

SET CONNECTION ddd3_con;
MESSAGE STRING ( CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Name' ), ' SET CONNECTION ddd3_con;' ) TO CONSOLE;
BEGIN
   DROP TABLE t3;
   EXCEPTION WHEN OTHERS THEN
END;
CREATE TABLE t3 ( pkey INTEGER NOT NULL PRIMARY KEY );
INSERT t3 VALUES ( 333 );
MESSAGE STRING ( CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Name' ), ' INSERT t3 VALUES ( 333 );' ) TO CONSOLE;

WAITFOR DELAY '00:00:02';

----------------------------------------------------------------------------
-- ROLLBACK, then INSERT a different row.

SET CONNECTION ddd1_con;
MESSAGE STRING ( CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Name' ), ' SET CONNECTION ddd1_con;' ) TO CONSOLE;
ROLLBACK;
MESSAGE STRING ( CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Name' ), ' ROLLBACK;' ) TO CONSOLE;
INSERT t1 VALUES ( 1 );
MESSAGE STRING ( CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Name' ), ' INSERT t1 VALUES ( 1 );' ) TO CONSOLE;

WAITFOR DELAY '00:00:02';

SET CONNECTION ddd2_con;
MESSAGE STRING ( CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Name' ), ' SET CONNECTION ddd2_con;' ) TO CONSOLE;
ROLLBACK;
MESSAGE STRING ( CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Name' ), ' ROLLBACK;' ) TO CONSOLE;
INSERT t2 VALUES ( 2 );
MESSAGE STRING ( CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Name' ), ' INSERT t2 VALUES ( 2 );' ) TO CONSOLE;

WAITFOR DELAY '00:00:02';

SET CONNECTION ddd3_con;
MESSAGE STRING ( CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Name' ), ' SET CONNECTION ddd3_con;' ) TO CONSOLE;
ROLLBACK;
MESSAGE STRING ( CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Name' ), ' ROLLBACK;' ) TO CONSOLE;
INSERT t3 VALUES ( 3 );
MESSAGE STRING ( CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Name' ), ' INSERT t3 VALUES ( 3 );' ) TO CONSOLE;

WAITFOR DELAY '00:00:02';

----------------------------------------------------------------------------
-- COMMIT, then show final row.

SET CONNECTION ddd1_con;
MESSAGE STRING ( CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Name' ), ' SET CONNECTION ddd1_con;' ) TO CONSOLE;
COMMIT;
MESSAGE STRING ( CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Name' ), ' COMMIT;' ) TO CONSOLE;
BEGIN
   DECLARE @pkey INTEGER;
   SELECT t1.pkey INTO @pkey FROM t1;
   MESSAGE STRING ( CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Name' ), ' t1.pkey = ', @pkey ) TO CONSOLE;
END;

WAITFOR DELAY '00:00:02';

SET CONNECTION ddd2_con;
MESSAGE STRING ( CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Name' ), ' SET CONNECTION ddd2_con;' ) TO CONSOLE;
COMMIT;
MESSAGE STRING ( CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Name' ), ' COMMIT;' ) TO CONSOLE;
BEGIN
   DECLARE @pkey INTEGER;
   SELECT t2.pkey INTO @pkey FROM t2;
   MESSAGE STRING ( CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Name' ), ' t2.pkey = ', @pkey ) TO CONSOLE;
END;

WAITFOR DELAY '00:00:02';

SET CONNECTION ddd3_con;
MESSAGE STRING ( CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Name' ), ' SET CONNECTION ddd3_con;' ) TO CONSOLE;
COMMIT;
MESSAGE STRING ( CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Name' ), ' COMMIT;' ) TO CONSOLE;
BEGIN
   DECLARE @pkey INTEGER;
   SELECT t3.pkey INTO @pkey FROM t3;
   MESSAGE STRING ( CURRENT TIMESTAMP, ' ', CONNECTION_PROPERTY ( 'Name' ), ' t3.pkey = ', @pkey ) TO CONSOLE;
END;

Here's what the dbisql batch session displays as it runs:

Connected to "ddd1" on "ddd1".
Connected to "ddd2" on "ddd2".
Connected to "ddd3" on "ddd3".
1 row(s) inserted
1 row(s) inserted
1 row(s) inserted
1 row(s) inserted
1 row(s) inserted
1 row(s) inserted
1 row(s) affected
1 row(s) affected
1 row(s) affected
Execution time: 18.14 seconds

Here's the MESSAGE output as it appeared in the three separate server console windows for ddd1, ddd2 and ddd3:

2011-06-02 12:04:46.978 ddd1_con CONNECT TO ddd1 DATABASE ddd1 AS ddd1_con
2011-06-02 12:04:47.110 ddd1_con SET CONNECTION ddd1_con;
2011-06-02 12:04:47.602 ddd1_con INSERT t1 VALUES ( 111 );
2011-06-02 12:04:54.776 ddd1_con SET CONNECTION ddd1_con;
2011-06-02 12:04:54.807 ddd1_con ROLLBACK;
2011-06-02 12:04:54.824 ddd1_con INSERT t1 VALUES ( 1 );
2011-06-02 12:05:01.114 ddd1_con SET CONNECTION ddd1_con;
2011-06-02 12:05:01.144 ddd1_con COMMIT;
2011-06-02 12:05:01.168 ddd1_con t1.pkey = 1

2011-06-02 12:04:47.052 ddd2_con CONNECT TO ddd2 DATABASE ddd2 AS ddd2_con
2011-06-02 12:04:49.739 ddd2_con SET CONNECTION ddd2_con;
2011-06-02 12:04:50.171 ddd2_con INSERT t2 VALUES ( 222 );
2011-06-02 12:04:56.946 ddd2_con SET CONNECTION ddd2_con;
2011-06-02 12:04:56.958 ddd2_con ROLLBACK;
2011-06-02 12:04:56.971 ddd2_con INSERT t2 VALUES ( 2 );
2011-06-02 12:05:03.281 ddd2_con SET CONNECTION ddd2_con;
2011-06-02 12:05:03.294 ddd2_con COMMIT;
2011-06-02 12:05:03.304 ddd2_con t2.pkey = 2

2011-06-02 12:04:47.105 ddd3_con CONNECT TO ddd3 DATABASE ddd3 AS ddd3_con
2011-06-02 12:04:52.286 ddd3_con SET CONNECTION ddd3_con;
2011-06-02 12:04:52.743 ddd3_con INSERT t3 VALUES ( 333 );
2011-06-02 12:04:59.059 ddd3_con SET CONNECTION ddd3_con;
2011-06-02 12:04:59.071 ddd3_con ROLLBACK;
2011-06-02 12:04:59.083 ddd3_con INSERT t3 VALUES ( 3 );
2011-06-02 12:05:05.386 ddd3_con SET CONNECTION ddd3_con;
2011-06-02 12:05:05.398 ddd3_con COMMIT;
2011-06-02 12:05:05.408 ddd3_con t3.pkey = 3

When you take the MESSAGE output and sort it by timestamp, you can see how one dbisql session was able to switch back and forth between three separate connections; not only that, but it could do that without disturbing the transactions that were running on each of those connections:

2011-06-02 12:04:46.978 ddd1_con CONNECT TO ddd1 DATABASE ddd1 AS ddd1_con

2011-06-02 12:04:47.052 ddd2_con CONNECT TO ddd2 DATABASE ddd2 AS ddd2_con

2011-06-02 12:04:47.105 ddd3_con CONNECT TO ddd3 DATABASE ddd3 AS ddd3_con

2011-06-02 12:04:47.110 ddd1_con SET CONNECTION ddd1_con;
2011-06-02 12:04:47.602 ddd1_con INSERT t1 VALUES ( 111 );

2011-06-02 12:04:49.739 ddd2_con SET CONNECTION ddd2_con;
2011-06-02 12:04:50.171 ddd2_con INSERT t2 VALUES ( 222 );

2011-06-02 12:04:52.286 ddd3_con SET CONNECTION ddd3_con;
2011-06-02 12:04:52.743 ddd3_con INSERT t3 VALUES ( 333 );

2011-06-02 12:04:54.776 ddd1_con SET CONNECTION ddd1_con;
2011-06-02 12:04:54.807 ddd1_con ROLLBACK;
2011-06-02 12:04:54.824 ddd1_con INSERT t1 VALUES ( 1 );

2011-06-02 12:04:56.946 ddd2_con SET CONNECTION ddd2_con;
2011-06-02 12:04:56.958 ddd2_con ROLLBACK;
2011-06-02 12:04:56.971 ddd2_con INSERT t2 VALUES ( 2 );

2011-06-02 12:04:59.059 ddd3_con SET CONNECTION ddd3_con;
2011-06-02 12:04:59.071 ddd3_con ROLLBACK;
2011-06-02 12:04:59.083 ddd3_con INSERT t3 VALUES ( 3 );

2011-06-02 12:05:01.114 ddd1_con SET CONNECTION ddd1_con;
2011-06-02 12:05:01.144 ddd1_con COMMIT;
2011-06-02 12:05:01.168 ddd1_con t1.pkey = 1

2011-06-02 12:05:03.281 ddd2_con SET CONNECTION ddd2_con;
2011-06-02 12:05:03.294 ddd2_con COMMIT;
2011-06-02 12:05:03.304 ddd2_con t2.pkey = 2

2011-06-02 12:05:05.386 ddd3_con SET CONNECTION ddd3_con;
2011-06-02 12:05:05.398 ddd3_con COMMIT;
2011-06-02 12:05:05.408 ddd3_con t3.pkey = 3

To tell you the truth, I don't know if SET CONNECTION is the same as, different from, better than or worse than the USE statement in Transact/SQL. If the SQL Server docs didn't suck so badly I would know the answer without testing... just a minute... OK, there, it's on my to do list :)

---