How to add a JDBC driver to Jenkins’s jobs & load the parameter values from SQL Database

Jenkins + SQL JDBC

Agenda:

• How to setup SQL JDBC driver to Jenkins.
• To load the data from SQL to Jenkins parameterized build.
• Example with a dependent Choices Parameter with help of the groovy script.

Steps to Setup JDBC driver in Jenkins:

1. We need to download the MS JDBC driver (i.e Microsoft JDBC Driver 7.4 for SQL Server).
2. Based on our Java installed version, we need to choose a JDBC plug in version after Setp:1
3. Copy the corresponding JDBC version and paste into jenkins installed location like C:\Program Files (x86)\Jenkins\jre\lib\ext\
4. Restart the Jenkins Server.

For example: Load the Jenkins parameters data from SQL

In most of the cases, we need to bind the dynamic values to choice’s plug in.So, we need to pull the data from SQL to bind the Jenkins parameters. Sometimes we get the requirement dependent choice’s parameters.For this first we need to download the Active Choices Plugin from Manage Plugin’s option in Jenkins.

To Load the dynamic values to choice’s parameters with help of a groovy script like below:

Parent:

import groovy.sql.Sql
def dbConfig = [url:'jdbc:SQL database server address:1433;databaseName=DBname', user:'userid', password:'pwd', driver:'com.microsoft.sqlserver.jdbc.SQLServerDriver']
List countryList = []
try{
Sql.withInstance(dbConfig.url, dbConfig.user, dbConfig.password,dbConfig.driver) { sql ->
  sql.eachRow('SELECT DISTINCT Country FROM country'){ row ->
    countryList.add(row.Country)
  }
}
}
catch (Exception e){
   countryList.add(e)
 }
if(countryList.size()==0){
  countryList.add("Resluts Not found")
}
 return countryList

Parent Active Choices Parameter

Child:

import groovy.sql.Sql
def dbConfig = [url:'jdbc:SQL database server address:1433;databaseName=DBname', user:'userid', password:'pwd', driver:'com.microsoft.sqlserver.jdbc.SQLServerDriver']
List cityList = []
try{
Sql.withInstance(dbConfig.url, dbConfig.user, dbConfig.password,dbConfig.driver) { sql ->
  sql.eachRow("SELECT DISTINCT City FROM City WHERE Country ='${Parent}'"){ row ->
    cityList .add(row.City )
  }
}
}
catch (Exception e){
 cityList.add(e)
 }
if(cityList.size()==0){
  cityList.add("Resluts Not found")
}
return cityList

Child Active Choices Reactive Parameter

For more detailed setup, please visit below link:

Note:
Similarly, we can load the list of the server’s name from different sources.

==If you find any thing wrong please let me know i will update my self==