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:

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

Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments