Batch query processing with ColdFusion
Bookmark and Share
 

Posted by Rahul in ,

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>

6 comments

Thanks! Can something similar be done with the JDBC drivers for SQL Server 2005?

Govindaram   says 6/12/2008 4:33 PM

Is it possible to get Connection object created by coldfusion by providing the datasource name?

(or)

Is it possible to get datasource configuration (url, user, password) from coldfusion to create a connection object.


This will help in configuring to database instead of hardcoding.

@govindaram - You can get a connection from the CF connection pool as follows:

Connection con = ServiceFactory.getDataSourceService().getDatasource(getDatasource()).getConnection();

Be sure to close it when you are done so it is returned to the connection pool in a timely manner ;)

Thanks Eric for answering this. I am in CFUnited currently & didnt get time to reply on this

Thanks Eric,

I have written a custom tag to do the same. Please have a look at it in my blog and provide your comment.

http://govindreloaded.blogspot.com/2008/07/coldfusion-customtag-for-query-batch.html

Anonymous   says 1/20/2012 10:26 PM

Hi Rahul,
Do you have any blog about how to programmatically import tab delimited text file into Oracle table?
I used to use Sybase bcp but we're navigating away from Sybase to Oracle 11g
Thanks,
Alec

Post a Comment