Thursday, August 03, 2006

Accessing MSSQL Server Database Transaction Log via JDBC

Wanting to access the transaction log, I found that dbcc is a god way to do it via the SQL Query Analyzer. However, programatically via jdbc, I was curious whether the same command will work returning a result set. It turned out to be nothing to access the transaction log using dbcc and the MSSQLServer's jdbc driver. I added three jar files in the classpath: msbase.jar, mssqlserver.jar, msutil.jar and started writing the program :

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class TestMSQLServer {

  public static void testDBCC() {
    String DRIVER =
        "com.microsoft.jdbc."
            "sqlserver.SQLServerDriver";
    String DBURL =
        "jdbc:microsoft:sqlserver: "
            "//localhost:1433;";

    Connection connection = null;
    Statement stmt = null;
    ResultSet rs = null;
    ResultSetMetaData metadata = null;
    try {
      Class.forName(DRIVER);
      connection =
          DriverManager.getConnection(DBURL,
              "sa""sa");
      stmt = connection.createStatement();
      rs =
          stmt
              .executeQuery("dbcc log (master,2)");
      metadata = rs.getMetaData();
      int noOfColumns =
          metadata.getColumnCount();
      int noOfDashes = 0;
      for (int i = 1; i <= noOfColumns; i++) {
        String columnName =
            metadata.getColumnName(i);
        System.out.print(columnName + "\t");
        noOfDashes +=
            (columnName.length() 4);
      }
      System.out.println();
      for (int i = 0; i < noOfDashes; i++) {
        System.out.print("-");
      }
      System.out.println();
      while (rs.next()) {
        for (int i = 1; i <= noOfColumns; i++) {
          System.out.print(rs
              .getObject(metadata
                  .getColumnName(i))
              "\t");
        }

        System.out.println();

      }
    catch (Exception e) {
      e.printStackTrace();
    finally {
      try {
        if (rs != null) {
          rs.close();
        }
        if (stmt != null) {
          stmt.close();
        }
        if (connection != null) {
          connection.close();
        }
      catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }

  public static void main(String[] args) {
    testDBCC();
  }

}

No comments: