Sybase JConnect : ENABLE_BULK_LOAD usage

Question

Can anyone out there provide an example of bulk inserts via JConnect (w/ENABLE_BULK_LOAD) to Sybase ASE? I've scoured the internets and found nothing. I'm running on a short timeline so if someone can provide a short java code example it would be much appreciated.

Thanks, Chris

Answer 1

Don't know how to do this in Java, but you can bulk-load text files with LOAD TABLE SQL statement. We did it with Sybase ASA over JConnect.

Answer 2

Support for Batch Updates

Batch updates allow a Statement object to submit multiple update commands as one unit (batch) to an underlying database for processing together.

Note: To use batch updates, you must refresh the SQL scripts in the sp directory under your jConnect installation directory. CHAPTER

See BatchUpdates.java in the sample (jConnect 4.x) and sample2 (jConnect 5.x) subdirectories for an example of using batch updates with Statement, PreparedStatement, and CallableStatement. jConnect also supports dynamic PreparedStatements in batch.

Reference:

http://download.sybase.com/pdfdocs/jcg0420e/prjdbc.pdf

http://manuals.sybase.com/onlinebooks/group-jcarc/jcg0520e/prjdbc/@ebt-link;hf=0;pt=7694?target=%25N%14_4440_START_RESTART_N%25#X

.

Other Batch Update Resources

http://java.sun.com/j2se/1.3/docs/guide/jdbc/spec2/jdbc2.1.frame6.html

http://www.jguru.com/faq/view.jsp?EID=5079

Answer 3

I got in touch with one of the engineers at Sybase and they provided me a code sample. So, I get to answer my own question.

Basically here is a rundown, as the code sample is pretty large... This assumes a lot of pre initialized variables, but otherwise it would be a few hundred lines. Anyone interested should get the idea. This can yield up to 22K insertions a second in a perfect world (as per Sybase anyway).

SybDriver sybDriver = (SybDriver) Class.forName("com.sybase.jdbc3.jdbc.SybDriver").newInstance();
sybDriver.setVersion(com.sybase.jdbcx.SybDriver.VERSION_6);
DriverManager.registerDriver(sybDriver);

//DBProps (after including normal login/password etc.
props.put("ENABLE_BULK_LOAD","true");

//open connection here for  sybDriver

String SQLString = "insert into batch_inserts (row_id, colname1, colname2)\n values (?,?,?) \n";

PreparedStatement   pstmt;
try
{
   pstmt = dbConn.prepareStatement(SQLString);      
}
catch (SQLException sqle)
{
   displaySQLEx("Couldn't prepare statement",sqle);
   return;
}

for (String[] val : valuesToInsert)
{
   pstmt.setString(1, val[0]);  //row_id    varchar(30)
   pstmt.setString(2, val[1]);//logical_server varchar(30)
   pstmt.setString(3, val[2]);  //client_host varchar(30)

   try
   {
      pstmt.addBatch();
   }
   catch (SQLException sqle)
   {
      displaySQLEx("Failed to build batch",sqle);
      break;
   }
}

try {
   dbConn.commit();
   pstmt.close();
} catch (SQLException sqle) {
   //handle
}

try {
   if (dbConn != null)
      dbConn.close();
} catch (Exception e) {
   //handle
}