Integrating vCO: Access a Database (Overview)

By
Joerg Lew
– August 24, 2011Posted in: Best-Practices, Database, Integrate vCO, vCO Use-Cases, Workflow Design

VMware Orchestrator allows you to access SQL-Databases from within workflows. This post shows you common Use-Cases for that, explain technical background and introduces three-and-a-half ways how to implement the database-access.

Use-Cases

Accessing external databases within workflows is used to…

  • …integrate Your Workflows with external systems: You can retrieve or update information for an external system’s database (Ticket-System, Asset Management, CMDB, Documentation, Your VM-Deployment-System, ….
  • …build Your very own database-driven solution: If you create a larger “Suite” of workflows (like the VM-Deployment-System), expect that you have to deal with more and more metadata around the objects your workflows handle. A database is a good place to store this metadata, and it allows external systems to integrate easily with your Workflows in future.

Technical Background

The ability to integrate with external databases is presented by…. 😀


…the bundled  Database-Plugin. It provides a couple of Scripting-Objects for JDBC-Connections. So when you look for further reference information you can google for JDBC-Examples, they will fit (with some small limitations: Check the API Explorer of the vCO-Client to figure out the actual properties and methods of the JDBC-Objects).
In the bundled Workflow-Library you also find a Folder “JDBC Examples”, which provides a complete typical “lifecycle” of a database. Here you can also find a very useful Workflow called “JDBC Url Generator” which creates the correct complete JDBC-Connection-String you need to connect to the database.

Out-of-the-box, vCO supports Microsoft SQL-Server and Oracle-Databases, because for them the JDBC-Driver are pre-installed. However, it’s possible to add any other vendor’s JDBC-Driver; to get an idea, how to do this, see http://www.vmware.com/files/pdf/techpaper/vco-experimental-dbs-openldap-support.pdf.

Implementation Options

No matter, WHY you want to access external databases from your workflows, there are 3 (and a half) options to HOW do this:

1. Plain Database Access in Scripting Element

Just add a scripting element to your Workflow, and place the database operation in its JavaScript. This is useful, if you only call the database very rarely in defined workflows, and for single operations (For example: You want to update a documentation-database after deploying a new Virtual Machine).

2. Encapsulate Database Access to “DAO”-Actions

When you need database-calls more often in your workflows (Acutally, you should use this if you need it more than once :-)), you should encaplusate the database-calls into Actions. Typically you create pretty generic Actions for each CRUD-Operation, sometimes additional some “higher-level” Actions which do exactly the call you need often in the workflows.

(Side note: I call them DAO-Actions, compared to “Data-Access-Objects” in Software Engineering, when it comes to persistence and object-relational mapping in object-oriented software design.
Of course Actions are not “Objects”, but the idea is the same:)

So you create a single point/module, where all the database-related stuff is implemented. This makes it easier to do DRY-style developing, and rises the readability, re-useability, and maintain-ability of your works.
Consider the table-schema of the database you access is changing, so with DAO-Actions you only have to change the code in a single place.

Another big advantage using Actions to encasulate the database-access: You can use it in the Input Presentation of a Workflow! So you can get database-information even before the workflow starts. That can be used for example to make it easier for the end-user to start workflow which needs a VM as input, by providing a list of the current user’s VMs (and this information of course comes from the database):

.5 Call Stored Procedures

This is the “and-a-half” option, because it can be used in addition to the options above: JDBC allows you to call Stored Procedures of the database. This can be used e.g. if the vendor of an external system provide a bunch of Stored Procedures as an “API” for its database (or if you just want to pass all the SQL-work to the database-team :mrgreen: ).

See an example how to call a Stored Procedure in vCO.

3. Build Your own vCO-Plugin

The final option is the holy grail: Build Your own Plugin for the Orchestrator, which handles the integration to the external database. This of course is some effort, but it’s worth it if you integrate your workflows very frequently with this external database (or of course, if you build your own database-driven workflow-suite, see the second use-case above).

Building Your own custom Plugin allows you

  • to make database-entities accessible as JavaScript-Objects, and move all the database-specific stuff (table-layout, database connection details) to the Plugin. So the usage of the entities inside the Workflows and Actions is object-oriented, and all the SQL-stuff is hidden from the Workflow-Developer (see picture below).
  • to present database-entities as Inventory-Objects to the Orchestrator, which allows you to use these entities as Attributes and Input/Output-Parameters in Workflows. (Example: When integrating an Ticket-System, you can let the workflow-user select the actual Ticket for a certain workflow)

There are to flavors building such a Plugin:

1. Create the Plugin just with a vso.xml and some Hibernate-Mapping files (one for each entity you need in vCO). This allows you to present entites to the inventory, but you still have to wirte some generic JavaScript-Code in your Workflows & Actions.
On the other hand: You don’t need to develop a single line of Java-Code for the plugin 8-), and let the automatic Hibernate-Mapping-Feature of the Database-plugin do the work for you.

2. Create real Java-Objects in the Plugin and use the full vCO-Plugin API. This allows you to present your database-entities as real objects to the Workflows and Actions, and adds the full functionality of the API (like Triggers, …).
On the other hand: You have to develop Java-Code (a bunch of it), and you have to learn how the vCO-Plugin-API works. See the Solar-System-Plugin in the vco-examples-package an this presentation (Plugin-Development starts at ~28:45min) to get an idea of it. Good luck! 😈

Summary

The JDBC-Database-Plugin  provides very powerful integration-abilities for Your Workflows. You can start with a couple of JavaScript-lines for a simple integration, refactor them to DAO-Actions to have a clean Design when the need for integration grows, and unleash the full power of vCO by creating your own custom Plugin.