how to batch insert

Q&A
1

Is there a option where I can o batch insert using for loop where I can specify how many times I want the insert to happen,or is there some other way to do bath insert.

2

For example ,I want to insert all the elements from a arrayList.How can I do that??

3

Or how can I insert the elements from a String buffer(which has comma seperated values) and I want a insert statement to run for each value.

4

Or how can I insert the elements from a String buffer(which has comma seperated values) and I want a insert statement to run for each value.

5

can you please ellobarate.Thanks

6

This is rather basic Java and can be found in a very simple JAVA search, not really related to Bonita…

lets start with

StringBuffer sb = new StringBuffer(); sb.append("a,b,c,d,e"); //create temp string

List l = sb.toString().split(“,”); //create the list
for(String li : l){ //for each list item
String insertStr = String.format(“Insert into db col values(‘%s’)”, li);
}

regards
Seán

7

Don’t understand, need more info re the process…

8

Hi.
You can create a task with a connector and add an Iterator (in the Iterator tab) that iterate on your list to insert your data for each value in the list.

9

Use “Add new comment” rather than “Answer” please. It is easier to follow the discussion.

First, create your ArrayList of thing to insert.
Create a script task where you add a database connector (MySQL for example).
In the data tab of your task, add a variable “thingToInsert”.
In the Iteration tab of your task, check “is Multi instantiated” and choose your list as the collection to iterate on. In the “Input data” chose your variable “thingToInsert”.
To finish, in the connector your can put the query "INSERT INTO table VALUES (${thingToInsert})

10

Hi Thank u so much for the etailed explantion.Now the Iteration workd…but the String variable that I create in that particular task is not getting values from the ArrayList so the Inser fails.But If just hardcode some value in insert …the it works.Can You help???

11

I cannot determine the issue without more informations.

What error do you have in the logs when the insert fails ?

Can you post the code you use to create and fill your ArrayList ?

Here is how I do it :

  1. Create a list in the “data tab” of the pool : (in this example I have a list of job)
    Creation of the list

  2. Fill the list : (I fill my list with data from a form but you can do it as you wish)

    ArrayList jobs = new ArrayList();
    for (ArrayList j : field_Table1) {
    jobs.add(j.get(0));
    }
    return jobs;

    Task + String variable

  3. Add the iteration :
    Iteration

  4. Add the connector :
    Connector

In your query you need to have something like “INSERT INTO jobTable(jobName) VALUES (${job})”.

12

What I mean is .In the Iteration tab,
1)I have choosen Parrellel multi instantiation
2)In the Input I have select the arrayList
3)In the Output(Result data from each instance), I have created a variable --output.But the output value is null.Its does not get any value.

So when I o an insert for that output variable the insert fails.

13

What kind of output did you have ?
And what say your error log ?

If you want to use the output of the Iteration tab, you need :

  • create a variable of type List in your Process
  • create a varable of type String (or other, it depends of your output) in the task
  • set the “output data” with the String variable
  • set the “List of output results” with the List variable

After that, you can use the list in the next Task.

[Like this]
[Like this]: http://img4.hostingpics.net/pics/981714iterationoutput.png

14

Ya I have don the same,but the Output list is getting null values.

15

Did you set your output in connector ?
And above all, did you have an output for your connector ? Insert queries don’t have any output by default.

16

But y do I need output for Insert query.

I just need to get the value from Iterating the list and storing it to a string variable and the insert that string variable using inser statement.

Its so confusing.

That output which I get from iterating is null.Both for the String variable and List of appended results is also a null list.

17

What is the thing you call “output” ?
Can you explain what you try to do, how you do it, what is the error ?
Maybe you can add a picture to explain the problem.

If you just want to " Iterating the list and storing it to a string variable and the insert that string variable using inser statement", the solution is here :
http://community.bonitasoft.com/answers/how-batch-insert#comment-38421

18

Bu Output , I mean the section in the Iteration Tab ,

(Result data from each instance) where you select a string variable.

I know to iterate values in a array list.

what I am trying to do is,

with each iteration of a arraylist(i:e) for each value in the arraylist ,I want an insert to happen for that value from arraylist.

19

I am not getting any error.Its just that Its does not insert the way I want it to.

20

You don’t need the “Output Data” field to do that.

It’s the “Input Data” that you need to use in your SQL query to insert it.

In my example, the “Input Data” is “job”. So, in my query I have “Insert into mytable(jobName) values(${job})”. This is “job” that is inserted in the column “jobName” of my table.