Jenkins – Active Choice: PostgreSQL – Return result of SELECT query

For a parameterized assembly with an image tag selection, you will need the Active Choices plugin

Go to "Manage Jenkins"

Section "Manage Plugins"

Go to the "Available" tab and select "Active Choices" in the search.

Install it. You also need plugins:

• Database
• PostgreSQL Database

Create a "New Item" – "Pipeline", indicate that it will be a parameterized assembly, and add the parameter "Active Choices Parameter"

We indicate that this is "Groovy Script" and paste the following into it:

import groovy.sql.Sql
import java.sql.Driver

credentialsId = 'artem-services-rds-credentials'
url = 'artem-services-rds.xxxxxxxxxxxx.eu-central-1.rds.amazonaws.com:5432/postgres'

def creds = com.cloudbees.plugins.credentials.CredentialsProvider.lookupCredentials(
  com.cloudbees.plugins.credentials.common.StandardUsernameCredentials.class, Jenkins.instance, null, null ).find{
    it.id == credentialsId
  }

def driver = Class.forName('org.postgresql.Driver').newInstance() as Driver 

def props = new Properties()
props.setProperty("user", "${creds.username}") 
props.setProperty("password", "${creds.password}")

def conn = driver.connect("jdbc:postgresql://${url}", props) 
def sql = new Sql(conn)

def artifact = []

artifact.add("Not selected")

def rows = sql.rows("select * from users").each { row ->
  artifact.add("$row.first_name")
}

return artifact

Where is the value of the variables, "credentialsId" – Jenkins Credentials ID with login and password to connect to the database;

"url" – database connection string (endpoint + port + db name);

This Active Choice makes a SELECT query on the "users" table and returns only the values of the "first_name" fields, as well as adding "Not selected" to the first position of the result.

Tagged: Tags Active Choice Jenkins Plugin Postgres PostgreSQL