Populate dropdown list from MySQL DB

Q&A
1

I know there are a lot of forum posts on this topic, but I'm stuck at this for a very long time and don't know what's wrong.

I'm trying to get a very simple list from a MySQL database (only 1 row, 5 items) and put this info into a dropdown list in a form. I Googled a lot and this code is supposed to be working, but unfortunately I get some errors.

I made form, dragged a list widget to the form and in the Data tab I click the edit button from the Available Values field. Next I select Script and enter this code:

 

 

import groovy.sql.Sql;

def host = "localhost";

def user = "root";

def pass = "";

def schema = "bonita";

def query = "SELECT type_id from types";

def list=[];

sql = providedscripts.BonitaSql.newInstance("jdbc:mysql://${host}/${schema}",user, pass, new com.mysql.jdbc.Driver()) sql.eachRow(query,{row-> list.add(row.type_id)});

println list[0];

list;

error on 1st line: sql cannot be resolved

error on 8: providedscripts cannot be resolved, and on 9 again the sql error.

What goes wrong here?

2

Hi,

I'd say to you import the providedscripts too or just remove them from you sentence. They are not needed anymore I guess.

I'd use like that:

import groovy.sql.Sql

<p>
	def list = []</p>

<p>
	sql = BonitaSql.newInstance("jdbc:mysql://host/schema","user", "pass", new com.mysql.jdbc.Driver())</p>

<p>
	sql.eachRow "SELECT type_id from types",{list += it.type_id}</p>

<p>
	sql.close()</p>

<p>
	return list</p>

Not quite sure if works just fine in MySQL but probably does.