MySQL Select + different Fields

Q&A
1

Hey there,

i have an understanding problem.

My MySQL Connector in the first Automated-Process has the following Code:

Select id, name, orderdate from customers where date Like ‘2014__04%’;

The result of query went to a variable “query_result” which is Data type “Text” and multiple.

The Form in the process below has actually only one Select-Field. Available values is selected the variable “query_result”. On running the Process everything is ok.
The Form in the Select-Field shows all results of the SQL-Query (n x n) , BUT the three resultfields are comma separated like “0001, test, 20140304000000” or “0002, test2, 20140404000000”

How can in now split the three resultfields into three variables in one query??? I mean, variable1 = 0001, variable2 = test, variable3 = 20140304000000 or better how can i read the variable “query_result” which is filled by the SQL-Query? (Textfield1 = 0001, Textfield2 = test, Textfield3 = 20140304000000)

Available value in Textfield1 like $query_result[value1]
Available value in Textfield2 like $query_result[value2]
Available value in Textfield3 like $query_result[value3]

I hope I have explained my question well enough?

2

Hi.

I see 2 solutions to do this :

1) You use the “scripting mode” of the connector to get the result like you want. You will need to manipulate the resultset in the output of the connector.

For example, to get all the id in a list :

import java.sql.ResultSetMetaData;

List list = new ArrayList();

resultset.beforeFirst();

while (resultset.next()) {
list.add(resultset.getString(“id”));
}

return list;

Or you can add each row of the query’s result in a list of hashmap :

import java.sql.ResultSetMetaData;

List<HashMap<String,String>> list = new ArrayList<HashMap<String,String>>();

ResultSetMetaData metadata = resultset.getMetaData();
int numberOfColumns = metadata.getColumnCount();

resultset.beforeFirst();

while (resultset.next()) {
HashMap<String,String> map = new HashMap<String,String>();
int i = 1;
while (i <= numberOfColumns) {
map.put(metadata.getColumnLabel(i), resultset.getString(i++));
}
list.add(map);
}

return list;

2) Split your list in a script task with a script like :

ArrayList listOfId = new ArrayList();
for (ArrayList list : query_result) {
listOfId.add(list.get(0));
}
return listOfId;

The script you need depend of what you want to do.

3

Greetings, this solution worked for me Yannick for the select , how to i proceed if i wanted to INSERT data.