Insert values from array - XSJS

[gamergtx260]

for(var j=0; j<output2.length; j++)

  {

  var pstmt4 = conn.prepareStatement("INSERT INTO STUDENT01.HISTORY VALUES(?,?,?,?)");

  pstmt4.setString(1,date);

  pstmt4.setString(2,time);

  pstmt4.setString(3,output2[j]);  // at this line

  pstmt4.setString(4,output);

  pstmt4.executeQuery();

  }

The above code shows an error while inserting. but works without the loop. Am i missing something?

[Former Member]

For some reason, it worked with a while loop.

[Former Member]

Thanks Rajkumar,

Can you help with the sample syntax?

Nehal.

[Former Member]

Sure.

var conn,st,b = 4,c = 5,d = 6,j=0;

conn = $.db.getConnection();

var a = [11,12,13,14,15,16,17];

while ( j<a.length ) {

    st = conn.prepareStatement("INSERT INTO \"D062461\".\"warp.web.model::testInsert\" values(?,?,?,?)");

    st.setInteger(1,a[j]);

    st.setInteger(2,b);

    st.setInteger(3,c);

    st.setInteger(4,d);

    st.execute();

    j = j + 1;

}

conn.commit();

[Former Member]

Thanks Rajkumar,

But I need it with

1. The new XSJS SPS9 DB interface

2. Using Batch update

Nehal.

[thomas_jung]

Yes this can be done with the new $.hdb interface. There is no need for a dedicated batch function. If you look at the executeUpdate function in the API you will see that it accepts an Array for the arguments. There is even an example of this approach to do a batch insert in the documentation:

JSDoc: Class: Connection

[Former Member]

Spot on! Thomas, thank you.

[Former Member]

Sorry to come back Thomas (but  this is not for the lack of trying)

Im unable to condition the parsed JSON for the array argument.

After parsing the JSON into an array using the $.parseJSON(response_body)

I tried passing the json.results to the Execute Update, but that obvioulsy does not work.

I get an error to the tune of it "must be an array of parameters". I searched(and searched alot) for a JS script solution, but the answer is not that evident.

Care to shed some light?

Former Member

[thomas_jung]

Did you have a look at the example in the documentation?

//Batch Insert

var argsArray = [["MINT", 3.50, 34.5], ["VANILLA", 2.50, 23.6], ["CHERRY", 4.50, 67.9]];

connection.executeUpdate('INSERT INTO "DB_EXAMPLE"."ICECREAM" VALUES (?,?,?)', argsArray) connection.commit();

The argument array must look like the above. I doubt your parsed JSON matches this type of structure. The root in a JSON parse is a JavaScript object not an array.  Here is a rather good blog that discusses the differences between objects and arrays in JavaScript:

Javascript: Understanding Objects vs Arrays and When to Use Them. [Part 1] | Metal Toad

[Former Member]

Thanks Thomas,

I did see the example, but was hoping against hope that it would be compatible. The blog cleared that now.

Thanks.

[thomas_jung]

Might I also suggest that you use a batch operation.  This will send all the inserts into the database in one bundle (use executeBatch instead).  Here is a similar example that uses the Batch concept:

var st = conn.prepareStatement('INSERT INTO #local_roles_table_1 VALUES(?)');

  st.setBatchSize(userJson.roles.length);

  for ( var x = 0; x < userJson.roles.length; x++) {

  st.setString(1, userJson.roles[x].role_name);

  st.addBatch();

  }

  st.executeBatch();

  st.close();

[Former Member]

Hi Thomas,

Is the batch mechanism also supported with the new XSJS DB interface - SPS9?  Or do we have to revert to the older prepare statement.

Nehal

Former Member

[former_member182114]

Hi Rajkumar,

What is the error message?

Give a try moving the prepareStatement outside the for, after it's already prepared I belive it will work fine just binding and executing.

Regards, Fernando Da Rós