Welcome to my site. I started this site as an online archive for me and all things virtual. I kept getting requests for help around some of the more standard issues we all hit in dealing with Cloud automation, particularly around the VMware vCloud Stack so I decided to make my forum public...and here we are.

My name is Lee Friend and I'm based in London, UK. I am a Cloud orchestration specialist in the end to end management, automation and integration of virtualisation stacks covering all pieces of the puzzle from IPAM (InfoBlox / QIP / IP Control), DNS, AD, resiliency & DR (SRM automation), management system integration (including ServiceNow, BMC Remedy, ticketing systems) and on and on.

Having worked since the very early days of virtualisation, I specialise in the vRealize suite (vRO,vRA,vCD,vR OPs,vCloudClient,SRM,and on) with the aim of orchestrating wherever it's needed. I have helped out large financials, global exchanges and cloud service providers at an architecture level all the way through to workflow engineering.

Call a Microsoft SQL stored procedure fron vRO

All topics related to VMware vCenter Orchestrator / vRealize Orchestrator
leefriend
Site Admin
Posts: 30
Joined: Sat Dec 20, 2014 4:45 pm

Call a Microsoft SQL stored procedure fron vRO

Postby leefriend » Wed Aug 17, 2016 10:05 am

SQL SP used for example:

Code: Select all

CREATE PROCEDURE sp_GetAuthorDetails
   @AuthorName varchar(250)
AS
BEGIN
   SET NOCOUNT ON;
   SELECT name [AuthorName], age [Age], city [City], books [Books] FROM Authors WHERE name = @AuthorName
END
GO


And the workflow to call the SP:
// input parameter(s)
Input = name (string)

Code: Select all

var sql = "EXEC sp_GetAuthorDetails @AuthorName = '" + name + "'";
 
// get following db account details from configuration items ideally so not to hard code
var db_url = <db_url>;
var db_username = <db_username>;
var db_password = <db_pwd>;
 
var jdbcConnectionFactory = new JDBCConnection();
try {
        var jdbcConnection = jdbcConnectionFactory.getConnection(db_url, db_username, db_password);
        var statement = jdbcConnection.prepareCall(sql);
        var resultSet = statement.executeQuery();
        while (resultSet.next()){
                System.log("Author: " + resultSet.getString("AuthorName"));
                System.log(" - Age: " + resultSet.getString("Age"));
                System.log(" - City: " + resultSet.getString("City"));
                System.log(" - Books: " + resultSet.getString("Books"));
        }
} catch (exception){
        errorMessage = " - sql stored proc failed with error:" + exception;
        System.warn(errorMessage);
} finally {
        if (resultSet) resultSet.close();
        if (statement) statement.close();
        if (jdbcConnection) jdbcConnection.close();
}


Happy days ;)

Return to “Everything vCO / vRO”



cron