Which database am I connected to?

Different database management systems encourage different behaviors, yielding different answers to the question "How many databases do I have access to?"

With products like Oracle, Microsoft SQL Server, Sybase ASE and MySQL the answer tends to be "Just one"... there is a certain administrative difficulty involved in creating multiple databases and starting multiple database engines. For example, when a developer sits down to write and test some code, the development tools connect to one single test database, and often the same database is shared with other developers. It is fairly rare for a developer to create a local copy of the test (or production) database, and certainly not multiple copies running on multiple local engines.

SQL Anywhere encourages a different behavior by making it insanely easy to copy databases and start engines. The data is stored in operating system files, not raw "devices", that's one big difference from some other products. Copying a database is as simple as stopping the engine and doing a drag-and-drop on the *.db file.

Another difference is that the physical database files are binary compatible across otherwise totally incompatible operating systems. For example, you can create a database on Vista, copy it to Red Hat, and then to Solaris... or Windows Mobile. Along the way, the machines can change between little endian and big endian. Endian differences are bigger than driving on the right or left, they're like narrow gauge railways versus wide gauge, leaded versus unleaded, completely incompatible... but not for SQL Anywhere files.

It is not unusual for SQL Anywhere's answer to the question "How many databases do I have access to?" to be "Dozens... hundreds... I'm starting to lose track."

OK, I'm exaggerating... please note the careful use of "tends to be" and "not unusual". But it's true, with SQL Anywhere you can switch back and forth between multiple databases, multiple versions many times a day. For example, when testing an idea, say for a blog post like this, I create and start a completely new SQL Anywhere database (one doubleclick, ten seconds) so that my SQL will run on a "clean slate"... no leftover tables from other work, and so on. I will save the script but delete the database when I'm done.

There's a dark side to this: it's sometimes just too easy to connect to the wrong database. Especially when the connections are indirect, through ODBC DSNs, program code, connection pools and so on. I sometimes fire up Foxhound just to find out which database I'm connected to; which server? what file? what's the IP address?



Here's how Foxhound answers those questions:

The server and database name lines tell you something you probably already know, but in this case something more; i.e., it's a High Availability setup, the arbiter is ok but one of mirror servers is down, and the Primary is server2:

     Server name: ENG=fares  (Primary is server2; Arbiter is connected; Secondary is disconnected) 
   Database name: DBN=fares

Then there's the name of the machine where the SQL Anywhere server is running, plus its IP address and port:

    Machine name: TSUNAMI 
   dbping result: Connected to server over TCPIP at address 192.168.1.51:2637

Finally, here's the physical database file that's being used for this connection:

                  File  Free Space  Free Space 
    DBSPACE Name  Size     In File    On Drive  Fragments   File Specification 
   system         3.1M        12k       93.9G          2   C:\data\mirror\fares.db

Foxhound works for ODBC DSNs, but it's only telling you about its own connection to the database. If you're using the same DSN from the same computer, fine... but what about an application program? Which database is it connected to?

That's what this little procedure is for. Plop it in your database, add the following query to your code, and display the long or short string in your program's "About box" (or even the window title bar):

   SELECT concise, verbose FROM which_database();

It doesn't do exactly what Foxhound does (it doesn't work with SQL Anywhere 5.5, for example) but it probably shows you what you need to know about your current connection. Here's what that SELECT returns for a dbisql connection to the same database shown above:

   Server name fares, Database fares on TSUNAMI at 192.168.1.51:2637 using C:\data\mirror\fares.db

     Server name: ENG=fares  (Primary is server2; Arbiter is connected; Secondary is disconnected)
   Database name: DBN=fares
    Machine name: TSUNAMI
      Connection: Network TCPIP to 192.168.1.51:2637
   Database File: C:\data\mirror\fares.db

Here's what it returns for a connection to a local database via SharedMemory:

   Server name ddd11, Database ddd11 on PAVILION2 at local SharedMemory using C:\projects\$SA_templates\ddd11.db

     Server name: ENG=ddd11
   Database name: DBN=ddd11
    Machine name: PAVILION2
      Connection: local SharedMemory
   Database File: C:\projects\$SA_templates\ddd11.db

Here's a TCP/IP connection to the same local database:

   Server name ddd11, Database ddd11 on PAVILION2 at local 192.168.1.105:2638 using C:\projects\$SA_templates\ddd11.db

     Server name: ENG=ddd11
   Database name: DBN=ddd11
    Machine name: PAVILION2
      Connection: local TCPIP to 192.168.1.105:2638
   Database File: C:\projects\$SA_templates\ddd11.db

Here's the code (Caveat: If you're using an earlier version of SQL Anywhere, you'll have to change this code. For example, PROPERTY ( 'TcpIpAddresses' ) was added in Version 11, and that's one of the reasons Foxhound uses dbping instead):

CREATE OR REPLACE PROCEDURE which_database()
RESULT (
   concise LONG VARCHAR,
   verbose LONG VARCHAR )
BEGIN
SELECT STRING (
          'Server name ', 
          PROPERTY ( 'Name' ), 
          ', Database ', 
          DB_PROPERTY ( 'Name' ),
          ' on ', 
          PROPERTY ( 'MachineName' ),
          ' at ',  
          IF CONNECTION_PROPERTY ( 'CommLink' ) = 'TCPIP'
             THEN ''
             ELSE STRING ( CONNECTION_PROPERTY ( 'CommLink' ), ' ' )
          ENDIF,
          IF CONNECTION_PROPERTY ( 'CommNetworkLink' ) = 'TCPIP'
             THEN PROPERTY ( 'TcpIpAddresses' ) 
             ELSE CONNECTION_PROPERTY ( 'CommNetworkLink' )
          ENDIF,
          ' using ', 
          DB_PROPERTY ( 'File' ) ) AS concise, 
       STRING (
          '     Server name: ENG=',  
          PROPERTY ( 'Name' ), 
          IF PROPERTY ( 'Name' ) <> PROPERTY ( 'ServerName' )
             THEN STRING ( 
                '  (Primary is ',             
                PROPERTY ( 'ServerName' ),
                '; Arbiter is ',
                DB_PROPERTY ( 'ArbiterState' ),
                '; Secondary is ',
                DB_PROPERTY ( 'PartnerState' ),
                IF DB_PROPERTY ( 'PartnerState' ) = 'connected'
                   THEN STRING ( ', ', DB_PROPERTY ( 'MirrorState' ), ')' )
                   ELSE ')'
                ENDIF )
             ELSE ''
          ENDIF,             
          '\x0d\x0a   Database name: DBN=',  
          DB_PROPERTY ( 'Name' ),
          '\x0d\x0a    Machine name: ',  
          PROPERTY ( 'MachineName' ),
          '\x0d\x0a      Connection: ',  
          IF CONNECTION_PROPERTY ( 'CommLink' ) = 'TCPIP'
             THEN 'Network '
             ELSE STRING ( CONNECTION_PROPERTY ( 'CommLink' ), ' ' )
          ENDIF,
          CONNECTION_PROPERTY ( 'CommNetworkLink' ),
          IF CONNECTION_PROPERTY ( 'CommNetworkLink' ) = 'TCPIP'
             THEN STRING ( ' to ', PROPERTY ( 'TcpIpAddresses' ) )
             ELSE ''
          ENDIF,
          '\x0d\x0a   Database File: ',
          DB_PROPERTY ( 'File' ) ) AS verbose;
END;

You can find more information about SQL Anywhere run-time properties here:

Server-wide PROPERTY() calls

Database-specific DB_PROPERTY() calls

Connection-specific CONNECTION_PROPERTY() calls

---

Download "Upgrade of 11.0.0 to 11.0.1"
What's new in version 11.0.1
New Editions in SQL Anywhere 11.0.1
SQL Anywhere 11.0.1 FAQs
SQL Anywhere 11.0.1 Editions