Working with SQL (SQL plugin)
by Daniel Langenhan
VMware vRealize Orchestrator Cookbook - Second Edition
- VMware vRealize Orchestrator Cookbook Second Edition
- VMware vRealize Orchestrator Cookbook Second Edition
- Credits
- About the Author
- About the Reviewers
- www.PacktPub.com
- Preface
- 1. Installing and Configuring Orchestrator
- 2. Optimizing Orchestrator Configuration
- Introduction
- Tuning the appliance
- Tuning Java
- Configuring the Kerberos authentication
- Configuring access to the local filesystem
- Configuring the Orchestrator service SSL certificate
- Orchestrator log files
- Redirecting Orchestrator logs to an external server
- Backup and recovery
- Control Center titbits
- 3. Distributed Design
- Introduction
- Building an Orchestrator cluster
- Load-balancing Orchestrator
- Upgrading a cluster
- Managing remote Orchestrators
- Synchronizing Orchestrator elements between Orchestrator servers
- 4. Programming Skills
- Introduction
- Version control
- Changing elements in a workflow
- Importing and exporting Orchestrator elements
- Working with packages
- Workflow auto documentation
- Resuming failed workflows
- Using the workflow debugging function
- Undelete workflows and actions
- Scheduling workflows
- Sync presentation settings
- Locking elements
- 5. Visual Programming
- 6. Advanced Programming
- Introduction
- JavaScript complex variables
- Working with JSON
- JavaScript special statements
- Turning strings into objects
- Working with the API
- Creating actions
- Waiting tasks
- Sending and waiting for custom events
- Using asynchronous workflows
- Scripting with workflow tokens
- Working with user interactions
- 7. Interacting with Orchestrator
- Introduction
- User management
- User preferences
- Using Orchestrator though the vSphere Web Client
- Accessing Orchestrator REST API
- Accessing the Control Center via the REST plugin
- Running Orchestrator workflows using PowerShell
- Using PHP to access the REST API
- 8. Better Workflows and Optimized Working
- 9. Essential Plugins
- 10. Built-in Plugins
- Introduction
- Working with XML
- Working with SQL (JDBC)
- Working with SQL (SQL plugin)
- Working with PowerShell
- Working with SOAP
- Working with Active Directory
- Working with SNMP
- Working with AMQP
- 11. Additional Plugins
- 12. Working with vSphere
- 13. Working with vRealize Automation
- Introduction
- Working with the vRA-integrated Orchestrator
- Automating a vRA instance in Orchestrator
- Configuring an external Orchestrator in vRA
- Adding Orchestrator as an infrastructure endpoint
- Adding an Orchestrator endpoint
- Integrating Orchestrator workflows as XaaS Blueprints
- Managing AD users with vRA
- Using the Event Manager to start workflows
In this recipe, we will explore the SQL plugin to work with SQL. This is a bit different from the JDBC recipe.
Obviously, we need a database. This database can be PostgreSQL, MS SQL, Oracle, or MySQL. For testing, you can use the PostgreSQL database that is implemented in the appliance (refer to the Tuning the appliance recipe in Chapter 2, Optimizing Orchestrator Configuration). Also, take a look at the There's more... section of this recipe.
We will use a Microsoft SQL 2008 R2 database in this example; however, the steps are the same for all databases. The database we will be using is called testDB.
You will need an existing database and a user who is able to create/drop tables as well as insert/delete information, for example, the DBO role.
See the following sections.
In this section, we will connect an SQL server to Orchestrator:
- Start the Library | SQL | Configuration | Add a database workflow.
- Enter a name for the connection.
- Select the kind of database you like.
- Enter the JDBC connection string to your database. You can use the Library | JDBC | JDBC URL generator workflow to create and test the string:
- When the workflow has finished, go and check the inventory:
We will now run a SQL statement:
- Start the Library | SQL | Execute a custom query on a database workflow.
- Select the database.
- Enter the following as a statement:
INSERT INTO testtbl VALUES (2,'some lastname','some firstname');. - Run the workflow and check the database table.
We will now run an SQL query:
- Create a new workflow.
- Add the workflow and create the inputs as in-parameters. Assign the output variable (array of
SQL:ActiveRecord) as an attribute. - Add a scriptable task and assign the attribute as in-parameter.
- Enter the following script:
for each (result in resultRecords){ columns=(result.getFieldNames()); for each (column in columns){ System.log("Column :"+column+" content:"+result.getProperty(column)); } } - Save and run the script with a SQL query such as
select * from testtbl.
The SQL plugin is a bit different than the JDBC connector as it uses Orchestrator objects. There are the following Orchestrator scripting classes:
SQLActiveRecordSQLColumnSQLDatabaseSQLDatabaseManagerSQLTable
Also extremely valuable are the Generate CRUD workflows for a table. CRUD stands for Create, Read, Update and Delete and will create these four workflows a given table.
-
No Comment