Bug #61269 Implement statement batch for Connector C++

• Submitted: 23 May 2011 19:14
• Reporter: Daryl H
• Status: Open
• Category: Connector / C++
• Severity: S4 (Feature request)
• Version: 1.1.0
• OS: Any
• CPU Architecture: Any

[23 May 2011 19:14] Daryl H

Description:
Specifically the addBatch() and executeBatch() (and their variations) for  statements and prepared statements, that are implemented in Connector J++ should be implemented in C++.

I am generating a large quantity of updates on a remote server, and individual prepared procedure calls take an inordinate amount of time.  I would assume that the batch processing will mitigate the communications latency.

How to repeat:
Here is the example code in Java:

/**
 * SqlServerPreparedStatementBatch.java
 * Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.
 */
import java.sql.*;
public class SqlServerPreparedStatementBatch {
  public static void main(String [] args) {
    Connection con = null;
    try {
      com.microsoft.sqlserver.jdbc.SQLServerDataSource ds 
        = new com.microsoft.sqlserver.jdbc.SQLServerDataSource();
      ds.setServerName("localhost");
      ds.setPortNumber(1269);
      ds.setDatabaseName("AdventureWorksLT");
      ds.setUser("Herong");
      ds.setPassword("TopSecret");
      con = ds.getConnection();

// PreparedStatement
      PreparedStatement ps = con.prepareStatement(
	"INSERT INTO Profile (FirstName, LastName) VALUES (?, ?)");

// Provide values to parameters for copy 1
      ps.setString(1,"John");
      ps.setString(2,"First");

// Create copy 1
      ps.addBatch();

// Provide values to parameters for copy 2
      ps.setString(1,"Bill");
      ps.setString(2,"Second");

// Create copy 2
      ps.addBatch();

// Provide values to parameters for copy 3
      ps.setString(1,"Mark");
      ps.setString(2,"Third");

// Create copy 3
      ps.addBatch();

// Provide values to parameters for copy 4
      ps.setString(1,"Jack");
      ps.setString(2,"Last");

// Create copy 4
      ps.addBatch();

// Execute all 4 copies
      int[] counts = ps.executeBatch();
      
      int count = 0;
      for (int i=0; i<counts.length; i++) {
      	count += counts[i];
      }
      System.out.println("Total effected rows: "+count);

// Close the PreparedStatement object
      ps.close();

      con.close();        
    } catch (Exception e) {
      System.err.println("Exception: "+e.getMessage());
      e.printStackTrace();
    }
  }
}