Populate dropdown list from MySQL DB

Submitted by stijn on Fri, 03/21/2014 - 18:13
1
0
-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?

1 answer

Submitted by rafael.vianna on Mon, 03/24/2014 - 19:53
1
0
-1

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

def list = []

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

sql.eachRow "SELECT type_id from types",{list += it.type_id}

sql.close()

return list

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

Notifications