Populate dropdown list from MySQL DB
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?
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.