import java.io.*;
import java.sql.*;
import java.util.*;

public class JDBCDemo
{
  public static void main( String[] args)
  {
    JDBCDemo obj = new JDBCDemo();

    if (obj.initialize())
    {
      while (obj.connect())
      {
        while (obj.getSQL())
          obj.displayResult();
        obj.closeDatabase();
      }
    }
  }

  private Connection  m_conn;
  private Statement   m_stmt;
  private ResultSet   m_rs;
  private int         m_count;
  private String      m_header;
  private String      m_headerLine;
  private int         m_rowPerPage = 19;

  private void printString( String str)
  { System.out.println( str); }

  private void skipLine()
  { System.out.println( ""); }

  private String prompt( String str)
  {
    System.out.print( str);

    byte input[] = new byte[512];
    int count = 0;

    try { count = System.in.read( input); }
    catch (IOException err) { err.printStackTrace(); }

    if (count != 0)
    {
      // take out cr lf
      while ((count!= 0) &&
             (input[count-1] == (byte)10 || input[count-1] == (byte)13))
        count--;
    }

    return (count != 0) ? new String( input, 0, count) : null;
  }

  public boolean initialize()
  {
    printString( "Omnidex JDBC Demo program.");
    printString( "Dynamic Information Systems Corporation (c) 2013.");
    skipLine();
    skipLine();

    try { Class.forName( "omnidex.jdbc.OdxJDBCDriver"); }
    catch (ClassNotFoundException err)
    {
      printString( "Unable to find Omnidex JDBC driver.");
      return false;
    }
    return true;
  }

  public boolean connect()
  {
    String connString = null;
    String input;

    while (connString == null)
    {
      connString = prompt( "Enter connection string [exit]: ");
      if (connString == null || connString.equalsIgnoreCase( "exit"))
        return false;

      // connect
      try
      {
        String url = "jdbc:omnidex:" + connString;

        m_conn = DriverManager.getConnection( url);

        m_stmt = m_conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
          ResultSet.CONCUR_READ_ONLY);
      }
      catch (SQLException err)
      {
        printString( "Unable to connect to " + connString);
        err.printStackTrace();
        skipLine();
        connString = null;
      }
    }

    return true;
  }

  public boolean getSQL()
  {
    String sql = null;
    while (sql == null)
    {
      try
      {
        if (m_rs != null)
        {
          m_rs.close();
          m_rs = null;
        }
        m_count = -1;

        sql = prompt( "Enter SQL [exit]: ");
        if (sql == null || sql.equalsIgnoreCase( "exit"))
          return false;

        // execute SQL and get the result set.
        if (m_stmt.execute( sql))
          m_rs = m_stmt.getResultSet();
        else
          m_count = m_stmt.getUpdateCount();

        m_header = null;
        m_headerLine = null;
      }
      catch (SQLException err)
      {
        printString( "Unable to execute SQL statement.");
        skipLine();
        sql = null;
      }
    }

    return true;
  }

  private int getRowSize( ResultSetMetaData rsmd, int columnCount,
    int columnLgth[])
  {
    int rowSize = 0;

    try
    {
      for (int i=1; i <= columnCount; i++)
      {
        columnLgth[i] = rsmd.getColumnDisplaySize(i);
        rowSize += columnLgth[i] + 2;
      }
    }
    catch (SQLException err) { err.printStackTrace(); }

    return rowSize;
  }

  private void displayHeader( ResultSetMetaData rsmd, int columnCount,
    int rowSize, int columnLgth[])
  {
    if (m_header == null)
    {
      try
      {
        char header[];
        char headerLine[];
        int offset, length;
        String label;

        // allocate the header buffer and data buffer
        header = new char[rowSize];
        headerLine = new char[rowSize];

        // combine the column headers
        offset = 0;
        for (int i=1; i <= columnCount; i++)
        {
          label = rsmd.getColumnLabel(i);
          length = columnLgth[i];

          label.getChars( 0, (label.length() < length ? label.length() : length),
            header, offset);

          for (int j=0; j < length; j++)
            headerLine[offset+j] = '-';

          offset += length + 2;
        }

        // generate the string header
        m_header = new String( header);
        m_headerLine = new String( headerLine);
      }
      catch (SQLException err)
      {
        err.printStackTrace();
        m_header = "";
        m_headerLine = "";
      }
    }

    printString( m_header);
    printString( m_headerLine);
  }

  public void displayResult()
  {
    if (m_rs == null)
    {
      if (m_count != -1)
        printString( "Number of row(s) affected: " + m_count);
      else
        printString( "No result set.");
      skipLine();

      return;
    }

    try
    {

      ResultSetMetaData rsmd;
      int columnCount;
      int rowSize;
      char data[];
      int columnLgth[];
      int rowCount = 0;
      boolean display = true;

      rsmd = m_rs.getMetaData();
      columnCount = rsmd.getColumnCount();

      columnLgth = new int[columnCount+1];  // offset 0 is not used
      rowSize = getRowSize( rsmd, columnCount, columnLgth);
      data = new char[rowSize];

      while (true)
      {
        if (display)
        {
          while ((rowCount < m_rowPerPage) && m_rs.next())
          {
            if (rowCount == 0)
            {
              displayHeader( rsmd, columnCount, rowSize, columnLgth);
              rowCount++;
            }

            int offset = 0;
            for (int i=1; i <= columnCount; i++)
            {
              int length = columnLgth[i];
              for (int j=0; j < length; j++)
                data[offset+j] = ' ';

              String value = m_rs.getString(i);
              if (value != null)
                value.getChars( 0, value.length(), data, offset);

              offset += length + 2;
            }

            printString( new String( data));
            rowCount++;
          }
        }
        else
          display = true;

        skipLine();
        String cmd = prompt( "(N)ext, (P)revious, (F)irst, (L)ast, (E)xit [N]: ");
        if (cmd != null)
        {
          switch (cmd.charAt(0))
          {
            case 'N':
            case 'n':
              break;
            case 'P':
            case 'p':
              int row = m_rs.getRow() - (m_rowPerPage * 2) + 2;
              if (row <= 0)
                m_rs.beforeFirst();
              else
                m_rs.absolute( row);
              break;
            case 'F':
            case 'f':
              m_rs.beforeFirst();
              break;
            case 'L':
            case 'l':
              m_rs.absolute( -m_rowPerPage);
              break;
            case 'E':
            case 'e':
              return;
            default:
              display = false;
              break;
          }
        }
        rowCount = 0;
      }
    }
    catch (SQLException err) { err.printStackTrace(); }
  }

  public void closeDatabase()
  {
    if (m_conn != null)
    {
      try
      {
        if (m_stmt != null)
          m_stmt.close();

        m_conn.close();
      }
      catch (SQLException err) {}

      m_stmt = null;
      m_conn = null;
    }
  }
}







