Why do I get a problem if I set a batch size for prepared statement to 1?

[Former Member]

Hi,

Is it a bug?

I use the following pseudo code to send an array of data records to one database:

...  
var myDataRecords = [...];                  // array with data records  
var myInsertQuery = 'INSET INTO ...';  
prepSt = conn.prepareStatement(myInsertQuery);  
if (myDataRecords.length > 0){  
     prepSt.setBatchSize(myDataRecords.length);  
     for(var i = 0; i < myDataRecords.length; i++){  
          prepSt.setInt(1, myDataRecords[i].dataField_1);  
          prepSt.setString(2, myDataRecords[i].dataField_2);  
          ...  
          prepSt.setString(n, myDataRecords[i].dataField_n);  
          prepSt.addBatch();  
     }  
     prepSt.executeBatch();       
}  
prepSt.close();  
...  

It works fine but if the data record array has only one record this code throws exception at addBatch():

"PreparedStatement.addBatch: bulk insert not enabled".

Is a "batching" with a batch size = 1 a special case?  The special cases make a code ugly. .

[Former Member]

Have the same problem here...

[thomas_jung]

Not a bug. Simply the way its designed. If you only have one record you can't use addBatch. You have to test for this have a logic branch for this situation.

[SAPSeeker]

Dear Thomas,

I too face the same issue. Is there any workaround for this ? Or, any suggested way to overcome/handle this ?

Thanks,

Gaurav.

[Former Member]

Hi Gaurav,

You can have a look at section "Create XSJS to crawl data", searchTweets.xsjs, line 27 - 67. I implemented the logic to check the length first. Here is the pseudo code.

var data = [...]; //the array you want to insert into SAP HANA

if (data.length === 1) {

     //do execute

} else if (data.length > 1) {

     //do setBatchSize

     for (var i in data) {

          //do addBatch

     }

     //do executeBatch

}

Best regards,

Wenjun