Switch between test and production database in ArcMap?

Question

I have two Oracle 11g databases, one for testing (tst.world) and one for production (prd.world). Except when I am trying out new things, they have exactly the same table structure.

In ArcCatalog I have created a connection - let's call it My Connection - to the test database. I use it in a .mdx file to display data from multiple tables in the test base. When the file is opened I get a prompt asking me to enter username and password:

Database connection dialog.

I would like the user to be able to choose between the test and the production database in that prompt, or in some other way choose which database to get the data from. Right now this is not possible since the field "Instance:" is greyed out. So far the only way in which I have been able to accomplish this is to use two separate .mdx files whit exactly the same content except that they use different connections. Clearly that is not a good solution.

Another solution would be to edit the connection settings in ArcCatalog every time I want to switch database. That works for me, but I am afraid it would be considered too complicated and tedious by many of the end users.

Is there a better way to accomplish this task?

• Farid Cheraghi: Why not creating two separate connection files? Furthermore you can store the username and password with the connection files
• Branco: I don't know enough about the connection protocols, but is there a chance you could use two distinct logins? One login gives them access to the test environment, the other login gives me access to the production? Like branco for production and brancoT for test? Not sure if that is an option for standard ArcMap, or if you would need to use a custom process via ArcObjects or ArcPy?
• Adam: just keep your end users in the production environment - if you're doing the testing then they won't need to access the test environment. They can have a single mxd with data pointing to production and never have to worry about changing connections/data sources.
• Anders: They might want to test running update querys on the DB, importing data from external sources etc and view the result graphicaly somewhere before daring to try it on the production base. So this feature is needed.
• Adam: that seems like even more reason to have them operating in separate MXDs to avoid inadvertently modifying the production database due

Answer 1

An easier option than creating python addin would be to create a python script tool that uses various arcpy.GetParameterAsText(0) statements that relate to the Create Database Connection arguments, and then using conditional logic (if, else), is directed to open one of two map documents sourced to either test or production (already saved somewhere on your network and to open you may use os.startfile(mxdPath)). The script tool dialog can have plain text text boxes, text boxes with hidden text (used for passwords), or combo boxes pre-populated with values to select from (this could be where the users selects between test or production db).

Here is a simple example of what the script tool code may look like:

import arcpy,os
user = arcpy.GetParameterAsText(0) # script tool text box
pwd = arcpy.GetParameterAsText(1) # script tool hidden text box
db = arcpy.GetParameterAsText(2) # script tool populated combo box
arcpy.CreateDatabaseConnection_management("Database Connections",
 db,
 "ORACLE",
 "myServerName",
 "DATABASE_AUTH",
 user,
 pwd)
if db == 'tst.world': # combo value from GetParameterAsText(2)
 os.startfile('C:/Temp/test.mxd') # open mxd with test layers sourced
else:
 os.startfile('C:/Temp/production.mxd') # open mxd with production layers sourced

Answer 2

Non-Arcpy

If you don't want to go for arcpy, one solution is to create two separate Mxds.

Right Click your original mxd in ArcCatalog and then click "Set Data Source...". In this way you can instantly create a new mxd file which points to your new connection file (e.g. ProductionDatabaseConnectionFile.sde). It takes about 10 seconds.

If your original Mxd is changed then this process must be repeated to reflect the changes to the other Mxd.

Arcpy

If the above approach doesn't fit your needs, I recommend to create a python Addin application extension(see here) and listen for "openDocument (self)" event. Create a dialog in that event to let the user choose between different databases!