How to use Sql query results in OptionSourceProvider.groovy class for Option sources

rmartha · July 12, 2023, 9:11pm

HI all

i want to use SQL query out put in OptionSourceProvider.groovy, so the query results will be optionsources lists items for my custom report.

ReportProvider.groovy
2)OptionSourceProvider.groovy
3)ReportsPlugin.groovy

in OptionSourceProvider.groovy class i want to get a SQL query out put and use that Output in List<Map<String, Object>>,

soo this List can be used as a optionSource in ReportProvider.groovy

Anyone have smaple code? so i can use it?

Neil_Van_Rensburg · June 6, 2024, 3:09pm

Here are examlpes for Datasets (which replace OptionSources):

To add SQL into the mix, this report provider has an examlpe:

github.com

gomorpheus/morpheus-plugin-samples/blob/main/morpheus-reports-plugin/src/main/groovy/com/morpheusdata/reports/CustomReportProvider.groovy#L90


      
          	ContentSecurityPolicy getContentSecurityPolicy() {
          		def csp = new ContentSecurityPolicy()
          		csp.scriptSrc = '*.jsdelivr.net'
          		csp.frameSrc = '*.digitalocean.com'
          		csp.imgSrc = '*.wikimedia.org'
          		csp.styleSrc = 'https: *.bootstrapcdn.com'
          		csp
          	}
          
          
          	void process(ReportResult reportResult) {
          		morpheus.report.updateReportResultStatus(reportResult,ReportResult.Status.generating).blockingGet();
          		Long displayOrder = 0
          		List<GroovyRowResult> results = []
          		withDbConnection { Connection dbConnection ->
          			if(reportResult.configMap?.phrase) {
          				String phraseMatch = "${reportResult.configMap?.phrase}%"
          				results = new Sql(dbConnection).rows("SELECT id,name,status from instance WHERE name LIKE ${phraseMatch} order by name asc;")
          			} else {
          				results = new Sql(dbConnection).rows("SELECT id,name,status from instance order by name asc;")
          			}

Topic		Replies	Views
Issue when using DataSetProvider in a Morpheus Option Source Plugin Plugins options , option-lists , plugins , groovy	0	368	November 30, 2023
Example Morpheus DataSet Providers - How can the DataSet be used in an Option List and Input? Plugins options , option-lists	2	265	May 14, 2024
Option List data from task/script Ideas automation , open	9	637	July 5, 2024
How to create the custom report using API instead of SQL query? Plugins api	2	382	June 29, 2022
Example Morpheus Option Source Plugin Plugins automation , options , option-lists , api	8	803	April 26, 2024

How to use Sql query results in OptionSourceProvider.groovy class for Option sources

Related topics