package ch04.section08;

import java.sql.*;

public class baseclass {
  String sDBDriver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
  String sConnStr = "jdbc:microsoft:sqlserver://localhost;dataBaseName=hjtjc";
  String sUserName = "sa";
  String sPassword = "sa";
  Connection conn = null;
  public ResultSet rs = null;

  public baseclass() {
  }

  public int ConnectDB() {

    try {
      Class.forName(sDBDriver);
    }
    catch (java.lang.ClassNotFoundException e) {
      System.err.println("connDB():" + e.getMessage());
      return -1;
    }
    try {
      conn = DriverManager.getConnection(sConnStr, sUserName, sPassword);
    }
    catch (SQLException ex) {
      System.err.println("connDB.executeQuery:" + ex.getMessage());
      return -1;
    }
    return 0;
  }

  public int CloseDB() {
    try {
      conn.close();
    }
    catch (SQLException ex) {
      System.err.println("connDB.executeQuery:" + ex.getMessage());
      return -1;
    }
    return 0;

  }

  public int executeQuery(String sql) {
    rs = null;
    ResultSetMetaData rsmd = null;
    String value;
    java.sql.Date dt;
    try {
      Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                            ResultSet.CONCUR_READ_ONLY);
      rs = stmt.executeQuery(sql);
      rs.last();
      int row = rs.getRow();

      rsmd = rs.getMetaData();
      int col = rsmd.getColumnCount();
      rs.beforeFirst();
      for (int i = 0; i < row; i++) {
        if (!rs.next()) {
          break;
        }
        for (int j = 1; j <= col; j++) {
          int type = rsmd.getColumnType(j);
          if (type == 93) {
            dt = rs.getDate(j);
            if (dt == null) {
              value = "";
            }
            else {
              value = dt.toString();
            }
          }
          else {
            value = rs.getString(j);
          }

          if (value == null) {
            value = "";
          }
//          else {
//            value = rs.getString(j);
//          }
          System.out.println(rsmd.getColumnName(j).toLowerCase().trim() + ":" +
                             value);
        }

      }

    }
    catch (SQLException ex) {
      System.err.println("connDB.executeQuery:" + ex.getMessage());
      return -1;
    }
    return 0;
  }

}