This recipe focuses on the interaction between Orchestrator and an SQL database using the Java database connector (JDBC). You will learn how to send SQL queries as well as commands to a database.
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.
This recipe has multiple parts that will cover all aspects of working with a database.
To connect to an SQL database, Orchestrator uses JDBC. Therefore, first, we need to create a JDBC URL:
- Log in to the Orchestrator Client and start the workflow by navigating to Library | JDBC | JDBC URL generator.
- Select the type of database you would like to connect to.
- Enter the database's IP or FQDN, the database name, as well as the authentication details.
- For a Microsoft SQL server, you may need to provide additional information, such as the SQL instance and the DB's domain name:
- After the workflow has finished successfully, it's easy to copy the connection string from the logs. The string for my SQL server looks like the following:
jdbc:jtds:sqlserver://192.168.220.4:1433/vcoapp;domain=mylab.local
This workflow not only creates the URL, it also tests it, which is quite handy. Keep the URL, as we will need it for all the other parts of the recipe. A good idea is to store the URL in a configuration.
We are now going to open and close the JDBC connection to a database:
- Create a new workflow and the following variables:
Name
Type
Place
Usage
jdbcURLStringAttribute
The JDBC URL from the first part of this recipe.
userStringAttribute
The username for the DB connection.
passwordSecureStringAttribute
The password for DB connection.
- Search for or browse the SDK module,
SQL. This module contains all the methods we will use in this recipe. - Drag a scriptable task onto the schema and enter the following script:
// constructors for JDBC DB and connection var myDB = new JDBCConnection(); var myConnect; // connect to DB myConnect = myDB.getConnection(jdbcURL, user , password); //further scripting // if the connection is open, close it. if (myConnect) { // disconnect from DB myConnect.close(); }
Next, we pass an SQL statement to the SQL server to be executed. Note that this executes an SQL statement, not an SQL query; we will address SQL queries in the next section of this recipe. The difference is that queries return values, whereas the execution of an SQL statement is either successful or unsuccessful:
- Duplicate (or add to) the workflow from the first part of this recipe.
- Create the following variables and bind them to the scriptable task:
Name
Type
Place
Usage
sqlStatementStringIN
The string with the complete SQL command.
resultNumberOUT
The result of the SQL command. 0 = OK.
- Enter the following script after
//further scripting:// Open SQL statement var mySQL = myConnect.createStatement(); // Open SQL results var result = mySQL.executeUpdate(sqlStatement); //close SQL statement mySQL.close(); - Run the workflow. The following SQL statement will create a table called
testtblthat contains theID,LastName, andFirstNamecolumns:CREATE TABLE testtbl (ID int, LastName varchar(255),FirstName varchar(255)); - Run the workflow again and use the following statement. It will create an entry in the table:
INSERT INTO testtbl VALUES (1,'Langenhan','Daniel');
In the How it works... section of this recipe, we will discuss the difference between the createStatement and prepareStatement methods.
In this part, we will look at how to deal with the results from a query. We will create a CSV of the results of the query:
- Duplicate the workflow from the first part of this recipe.
- Create the following variables and bind them to the scriptable task:
Name
Type
Place
Usage
sqlQueryStringIN
The string with the SQL query.
outputStringOUT
The output in a CSV format.
- Enter the following script after
//further scripting:// constructors for JDBC DB and connection var myDB = new JDBCConnection(); var myConnect; // connect to DB myConnect = myDB.getConnection(jdbcURL, user , password); //initialize output output=""; //open SQL statement var mySQL = myConnect.createStatement(); // open query var results = mySQL.executeQuery(sqlQuery); // get number of columns in a table from results metadata var resultMetaDate = results.getMetaData(); var colCount = resultMetaDate.getColumnCount(); //walk thought all rows while ( results.next() ) { //Walk thought all columns for (i = 1; i < colCount+1; i++) { // Past row together output = output+","+results.getStringAt(i); } //new line after end of row output = output+" "; } // close query results.close(); // close SQL statement mySQL.close(); - Try the workflow with a SQL query such as
select * from testtbl.
Also, take a closer look at the ResultSet and ResultSetMetaData objects for more possibilities on how to deal with the output of an SQL query.
Orchestrator's ability to use an external database and queries and execute statements on them makes it possible for Orchestrator not only to integrate with other systems, but also to store and process data. A typical system that Orchestrator will integrate with is a configuration management database (CMDB).
Looking into the methods of the Connection object, we find the createStatement() method that we used earlier as well as the prepareStatement() method. The difference between these is that you can use variables in the prepareStatement method; these are defined during runtime, whereas in createStatement, we can use only fixed queries. Let's work through an example. We want to delete an entry from the database. The SQL delete statement is the following:
DELETE FROM testtbl where (FirstName = "Daniel" and LastName = "Langenhan")
If we wanted to delete something else, we would have to rewrite the whole statement every time. Using prepareStatement, we don't have to do that. We use the following SQL statement:
DELETE FROM testtbl where (FirstName = ? and LastName = ?)
To make this work, we have to not only change the code of the script, but also add two new in-parameters (lastName and firstName). The new code looks like this:
var stat = mySQL.prepareStatement( sqlStatement ); // exchange the first ? for the content from the in-parameters stat.setString( 1, firstName ); // exchange the second ? for the content from the in-parameters stat.setString( 2, lastName ); //run the altered statement var result = stat.executeUpdate(); //close the statement stat.close ;
Basically, we just substituted ? with the values of in-parameters while the workflow is running.
The appliance comes with a preinstalled PostgreSQL database that can (but should not) be used. The appliance is configured to allow local access, so you just need to create a new database. To do this, follows these steps:
- Log into the appliance with root access.
- Run the following commands one after another:
su postgres psql CREATE USER testuser with PASSWORD 'testpass'; CREATE DATABASE testdb; GRANT ALL PRIVILEGES on DATABASE testdb to testuser; q exit
This will create a database called testdb and give testuser all rights using the password testpass.
You can learn more about SQL at http://www.w3schools.com/sql/default.asp .
The example workflows are as follows:
10.02.1 Connecting to a DB10.02.2 Execute SQL statement10.02.3 Execute SQL Query10.02.4 Execute SQL statement (with prepareStatement)
And the configuration item is 10.02 DB Config.