Database publishing - two db resources (Production and UAT)

In our new project we are starting to use dB publishing and I’ve encountered a conundrum that I thought someone in the community might have an idea on.

We have a UAT state in our workflow which serves as an approval state (once item is published to UAT web server, end-users approve it) (we do not manage approvals through CMS, but use other process). Edition to this UAT web server also includes all already Public items, so that the complete website is present for review.

For our Production site we will publish a regular page (first template) and also a dB entry (second template) to our Production database.

Is it possible to switch the db.resource on a db publish template to redirect the publish to UAT database based on the item’s state?

Since Edition to UAT web server also includes Public items it seems that there is no other way to this but create additional templates and content list to handle UAT publishing.

Any ideas and suggestions are welcome,
Thank you

I’ve implemented this myself as a JEXL Extension – i have 4 seperate databases that i have to mux between.

  1. Download and install ATCToolkit.
  2. Define a site/context variable on your Publishing Design tab for each of your Prod and UAT sites (or your prod and UAT contexts) that contains the JNDI Connection Name of the database to which you will be publishing. (This hinges on the tables in both databases being identical) For the sake of argument, I’m going to assume you use the name “pub_db
  3. Remove your old bindings for $db.origin, $db.datasource, $db.drivertype and $db.database
  4. Add the following bindings to replace the ones you’ve removed:

$db_conn_name = $sys.variables.pub_db
$db_conn = $user.datasourceTools.getDatasourceConfig( $db_conn_name )
$datasource = $user.datasourceTools.getJndiDatasource( $db_conn.dataSource )
$db.database = $datasource.server.substring( $tools.math.add( $datasource.server.lastIndexOf(":"),1 ) )
$db.origin = $db_conn.origin
$db.resource = $
$db.drivertype = $datasource.driverName

Thanks Sam,
I’ve ended up creating a new context variable for each of the two sites and then used $sys.variables bindings to access the variable values …