how to batch insert

1
0
-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.

Comments

Submitted by Sean McP on Wed, 02/11/2015 - 12:00

Don't understand, need more info re the process...

5 answers

1
0
-1

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<String> 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

1
0
-1

can you please ellobarate.Thanks

Comments

Submitted by yannick.lombardi on Wed, 02/11/2015 - 14:03

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})

Submitted by cvelu on Thu, 02/12/2015 - 10:12

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???

Submitted by yannick.lombardi on Thu, 02/12/2015 - 10:47

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<String> jobs = new ArrayList<String>();
for (ArrayList<String> j : field_Table1) {
        jobs.add(j.get(0));
}
return jobs;

Task + String variable

4) Add the iteration : Iteration

5) Add the connector : Connector

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

Submitted by cvelu on Thu, 02/12/2015 - 13:53

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.

Submitted by yannick.lombardi on Thu, 02/12/2015 - 14:08

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

Submitted by cvelu on Thu, 02/12/2015 - 14:09

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

Submitted by yannick.lombardi on Thu, 02/12/2015 - 14:12

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.

Submitted by cvelu on Fri, 02/13/2015 - 15:43

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.

Submitted by yannick.lombardi on Fri, 02/13/2015 - 15:55

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

Submitted by cvelu on Fri, 02/13/2015 - 16:05

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.

Submitted by cvelu on Fri, 02/13/2015 - 16:06

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

Submitted by yannick.lombardi on Fri, 02/13/2015 - 16:12

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.

Submitted by cvelu on Fri, 02/13/2015 - 16:15

But the input data is a List how Can I use that in the query?

Submitted by yannick.lombardi on Fri, 02/13/2015 - 16:16

The "Collection" field is a List. The "Input Data" is a String. As you can see on the picture : Picture

Submitted by cvelu on Fri, 02/13/2015 - 16:26

Oh I dont have these fields .I just have onle one field which accepts input lists

Submitted by Sean McP on Sat, 02/14/2015 - 09:50

Apologies I had a bad day, but to fix your problem you have to be clear on what you want, so far we have given you everything but we still cannot seem to make it work. Yannick has done a wonderful job and he cannot see what is happening for your question to fail.

You do not even say what version on Bonita you are using, which would help, especially if you do not see the same things as Yannick has shown in pictures.

So far you have said.

I have a list and I want to insert the data in the list into a database...there are two ways to do this.

Via Connector which Yannick has shown you, or in code similar to (note you will have to change the code to suit your environment, this code is based on postgres, and is not tested...):

StringBuffer sb = new StringBuffer();
sb.append("a,b,c,d,e"); //create temp string
 
List<String> jobs = sb.toString().split(",");

//set basics for db
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        String sql2 = null;
        String dbTable = null;
        String user = null;
        String password = null;

String url = "jdbc:postgresql://"+dbHost+":"+dbPort+"/"+dbDatabase);
                        try {
                                Class.forName("org.postgresql.Driver");
try {
                        con = DriverManager.getConnection(url, dbUser, dbPassword);

for (String job : jobs){

String templateSQL = "INSERT into dbTable (colName) values('"+job+"')";

                        pst = con.prepareStatement(templateSQL);
                        rs = pst.executeQuery();
}
                } catch (SQLException ex1) {
                        logger.severe(module+": Error (ex1): "+ex1.getMessage());

                } finally {
                        try {
                                if (rs != null) {
                                        rs.close();
                                }
                                if (pst != null) {
                                        pst.close();
                                }
                                if (con != null) {
                                        con.close();
                                }
                        } catch (ClassNotFoundException e) {
                                if(debug){logger.info("After Load Class: ClassNotFoundException");}
                        }

1
0
-1

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.

1
0
-1

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.

1
0
-1

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

Comments

Submitted by yannick.lombardi on Wed, 02/11/2015 - 13:55

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.

Notifications