Batch query processing with ColdFusion

 

Many a times we wonder if we can perform the action in database as a batch of multiple SQL statements. Though there are ways where in one execute multiple statements from but not all databases support that. Also that option risks the potential of SQL injection attacks if the queries are not written correctly. I personally would not prefer using for the purpose.

In one of my recent project I was required to perform multiple database inserts from the files. The records I am talking about can reach anywhere above 10K. I am sure not many of you would still want to use for this scenario.

To accomplish the task in hand I took advantage of the executeBatch() available in JDBC 2.0. the below script shows how we can do batch updates to a Oracle database.

<cfscript>
   class =  createObject("java","java.lang.Class").forName("oracle.jdbc.driver.OracleDriver");
   DriverManager= createObject("java","java.sql.DriverManager");
   connectStr = "jdbc:oracle:thin:@<serverName>:<portNumber>:<sid>";
   con = DriverManager.getConnection(connectStr, '<username>', '<password>');
</cfscript>

The above snippet is used to get the connection to the database using a connection string. If you have a datasouce setup and want to use it to get the connection use the following snippet :

<cfscript>
  ic = createObject("java","javax.naming.InitialContext");
  ds = ic.lookup("<datasource>");
  con = ds.getConnection();
</cfscript>

Note : The datasources set in the CFAdmin will not be available using the above method. Only datasources set in the J2EE server will be available.

After we have the connection[con], we can use the following script to add statements to a batch and execute it as a batch.

<cfscript>
 con.setAutoCommit(false);
 sql = "INSERT INTO test(id,name) VALUES(?,?)";
 pstmt = con.prepareStatement(sql);
 for (i=1; i<=1000; i++) {
   pstmt.setInt(1,i);
   pstmt.setString(2,"Sample String#i#");
   pstmt.addBatch();
 }
 updateCounts = pstmt.executeBatch();
 flag=true;
 for(i=1; i<=arrayLen(updateCounts);i++){
  if(updateCounts[i] == -3){ 
    flag=false; 
    con.rollback();
    break;
  }
 }
 if(flag){
   con.commit();
   writeoutput("Operation succeeded");
 }
 else
   writeoutput("Operation failed");
 </cfscript>

The JDBC recommendation is to set autocommit to false when using batch updates "for proper error handling." Doing so also allows all the benefits of transaction processing.

Update: To check if your database supports batch updates just check using this snippeton the connection object created above.

<cfscript>
writeoutput(con.getMetaData().supportsBatchUpdates());
</cfscript>