/*
 * Prospero Software Products http://www.prosperosoftware.co.uk
 * October 2004. Added new parameter MS_Port.
 * November 2004. Added new parameter CSV_EOR_CH.
 * Generic routine to import file from a MS SQL database to AS/400.
 * This program uses JDBC driver called jtds from sourceforge.net
 * Parameters for this program (all strings) are:
 *  1) Return code 7 alpha. Set to blank if program runs OK.
 *  2) IP Address of PC hosting MSSQL database.
 *  3) Name of MSSQL database.
 *  4) Port MSSQL database service is listening on.
 *  5) User to log on to MSSQL database.
 *  6) Password to log on to MSSQL database.
 *  7) Application (appears in MSSQL database logs).
 *  8) Name of MSSQL table to read (records to be retrieved).
 *  9) Fully qualified name of file to hold extracted CSV records.
 * 10) Character(s) to be used to delimit alphanumeric fields e.g. a double-quote
 * 11) Character(s) to be used to separate fields e.g. a comma (,).
 * 12) Single character to replace CR (hex 0D) in input text fields
 * 13) Single character to replace LF (hex 0A) in input text fields
 * 14) Character(s) to be used to terminate CSV record e.g. a carriage-return and/or line-feed
 * 15) Single character to be used to terminate CSV record (if above parameter is *CHAR)
 * 16) Lengths of fields in CSV String e.g. 15,5,1,100
 */

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

class prtvmsf {
      String MS_IP;
      String MS_DB;
      String MS_Port;
      String MS_User;
      String MS_Password;
      String MS_App;
      String MS_Table;
      String CSV_File;
      String CR_REPLACE;
      String LF_REPLACE;
      String CSV_Alpha_Delimit;
      String CSV_Separator;
      String CSV_EOR;
      String CSV_EOR_CHAR;
      String FieldLengthsCSV;
      boolean importFailure = false;
      boolean filterFailure = false;
      int el = -1;
      /* Default static constructor with parameters. */
      public static void main(String[] argv) {
        new prtvmsf(argv);
      }
    /** External method (makes class runnable) */
    prtvmsf(String[] args) {
        // Test right number of parameters received
        if (args.length != 16) {
           System.err.println("prtvmsf.class received " + args.length + " parameters - should have received 16.");
           if (args.length > 0) args[0] = "ERR010";
           return;
      	}
        boolean prtvmsfFailure = do_import(args);
        if (prtvmsfFailure) { args[0] = "ERR010"; }
        else { args[0] = "       "; }
    }
   /** Mainline function */
   boolean do_import(String[] args) {
      // Initialise variables from parameters
      MS_IP = args[1];
      MS_DB = args[2];
      MS_Port = args[3];
      MS_User = args[4];
      MS_Password = args[5];
      MS_App = args[6];
      MS_Table = args[7];
      CSV_File = args[8];
      CR_REPLACE = args[9];
      LF_REPLACE = args[10];
      CSV_Alpha_Delimit = args[11];
      CSV_Separator = args[12];
      CSV_EOR = args[13];
      CSV_EOR_CHAR = args[14];
      FieldLengthsCSV = args[15];
      // Temporary audit of parameters
      /*
System.out.println("CSVFILE   " + CSV_File.length() + " " + CSV_File);
System.out.println("REPLACECR " + CR_REPLACE.length() + " " + CR_REPLACE);
//System.out.println("REPLACECR " + CR_REPLACE.length() + " " + CR_REPLACE + " " + (int)CR_REPLACE.charAt(0));
System.out.println("REPLACELF " + LF_REPLACE.length() + " " + LF_REPLACE);
//System.out.println("REPLACELF " + LF_REPLACE.length() + " " + LF_REPLACE + " " + (int)CR_REPLACE.charAt(0));
System.out.println("CSVDLM    " + CSV_Alpha_Delimit.length() + " " + CSV_Alpha_Delimit);
System.out.println("CSVSEP    " + CSV_Separator.length() + " " + CSV_Separator);
System.out.println("CSVEOR    " + CSV_EOR.length() + " " + CSV_EOR);
       */
      // Define SQL objects
      Connection conn = null;
      Statement stmt = null;
      ResultSet rs = null;
      try {
         // Load JDBC driver
         Class.forName("net.sourceforge.jtds.jdbc.Driver");
         // Concatenate parameter variables to get MS SQL connection
         // string (format determined by JDBC driver manager)
         String connectionString = "jdbc:jtds:sqlserver://" + MS_IP + ':' + MS_Port + '/' + MS_DB + ";user=" + MS_User + ";password=" + MS_Password + ";appname=" + MS_App;
          // Prepare SQL connection, statement and result set
          conn = DriverManager.getConnection(connectionString);
          stmt = conn.createStatement();
          rs = stmt.executeQuery("SELECT * FROM " + MS_Table);
          // Build array of column (field) types
          ResultSetMetaData rsmd = rs.getMetaData();
          int Columns = rsmd.getColumnCount();
          if (Columns <= 0) {
            System.err.println("MS SQL table has no fields.");
            return(true);
          }
          int c;
          int[] ctype = new int[Columns];
          for (c = 1; c <= Columns; c++) {
             ctype[c-1] = rsmd.getColumnType(c);
          }
          // Get field lengths into array of ints
          el = -1;
          int[] clen = new int[Columns];
          for (c = 0; c < Columns; c++) {
            clen[c] = nextIntToken(FieldLengthsCSV);
            if (clen[c] < 0) {
              System.err.println("ISeries file has fewer fields than MS SQL table.");
              return(true);
            }
          }
          int finalLength = nextIntToken(FieldLengthsCSV);
          if (finalLength >= 0) {
            System.err.println("ISeries file has more fields than MS SQL table.");
            return(true);
          }
          // Prepare output CSV file
          BufferedWriter CSVWriter = new BufferedWriter(new FileWriter(CSV_File));
          // Initialise string buffer to hold CSV values for each record
          StringBuffer CSVRecord = new StringBuffer(512);
          while (rs.next()) {
             // Clear string buffer from previous record
             CSVRecord.setLength(0);
             // Iterate through all fields
             for (c = 0; c < ctype.length; c++) {
                // Process each field according to Java type
                if (ctype[c] == java.sql.Types.BIGINT) {
                   CSVRecord.append(rs.getLong(c+1));
                }
                else if (ctype[c] == java.sql.Types.BIT) {
                   CSVRecord.append(rs.getBoolean(c+1));
                }
                else if (ctype[c] == java.sql.Types.CHAR) {
                   CSVRecord.append(CSV_Alpha_Delimit);
                   String t = filterChar(rs.getString(c+1));
                   if (t.length() > clen[c]) { CSVRecord.append(t.substring(0,clen[c])); }
                   else { CSVRecord.append(t); }
                   CSVRecord.append(CSV_Alpha_Delimit);
                }
                else if (ctype[c] == java.sql.Types.DATE) {
                   CSVRecord.append(rs.getDate(c+1));
                }
                else if (ctype[c] == java.sql.Types.DECIMAL) {
                   CSVRecord.append(rs.getBigDecimal(c+1));
                }
                else if (ctype[c] == java.sql.Types.DOUBLE) {
                   CSVRecord.append(rs.getDouble(c+1));
                }
                else if (ctype[c] == java.sql.Types.FLOAT) {
                   CSVRecord.append(rs.getDouble(c+1));
                }
                else if (ctype[c] == java.sql.Types.INTEGER) {
                   CSVRecord.append(rs.getInt(c+1));
                }
                else if (ctype[c] == java.sql.Types.LONGVARCHAR) {
                   CSVRecord.append(CSV_Alpha_Delimit);
                   String t = filterChar(rs.getString(c+1));
                   if (t.length() > clen[c]) { CSVRecord.append(t.substring(0,clen[c])); }
                   else { CSVRecord.append(t); }
                   CSVRecord.append(CSV_Alpha_Delimit);
                }
                else if (ctype[c] == java.sql.Types.NUMERIC) {
                   CSVRecord.append(rs.getBigDecimal(c+1));
                }
                else if (ctype[c] == java.sql.Types.REAL) {
                   CSVRecord.append(rs.getFloat(c+1));
                }
                else if (ctype[c] == java.sql.Types.SMALLINT) {
                   CSVRecord.append(rs.getShort(c+1));
                }
                else if (ctype[c] == java.sql.Types.TIME) {
                   CSVRecord.append(rs.getTime(c+1));
                }
                else if (ctype[c] == java.sql.Types.TIMESTAMP) {
                   CSVRecord.append(rs.getTimestamp(c+1));
                }
                else if (ctype[c] == java.sql.Types.TINYINT) {
                   CSVRecord.append(rs.getByte(c+1));
                }
                else if (ctype[c] == java.sql.Types.VARCHAR) {
                   CSVRecord.append(CSV_Alpha_Delimit);
                   String t = filterChar(rs.getString(c+1));
                   if (t.length() > clen[c]) { CSVRecord.append(t.substring(0,clen[c])); }
                   else { CSVRecord.append(t); }
                   CSVRecord.append(CSV_Alpha_Delimit);
                }
                else {
                   CSVRecord.append(CSV_Alpha_Delimit);
                   CSVRecord.append(CSV_Alpha_Delimit);
                }
                // Append field separator for all but final field
                if (c < ctype.length - 1) CSVRecord.append(CSV_Separator);
             }
             // Append end-of-record character(s) and write to output CSV file
             if (CSV_EOR.compareTo("*CR") == 0) { CSVRecord.append((char)0X0D); }
             else if (CSV_EOR.compareTo("*LF") == 0) { CSVRecord.append((char)0X0A); }
             else if (CSV_EOR.compareTo("*CRLF") == 0) { CSVRecord.append((char)0X0D); CSVRecord.append((char)0X0A); }
             else if (CSV_EOR.compareTo("*NULL") == 0) { CSVRecord.append((char)0X00); }
             else if (CSV_EOR.compareTo("*CHAR") == 0) {
                if (CSV_EOR_CHAR.length() == 0) { CSVRecord.append((char)0X00); }
                else { CSVRecord.append(CSV_EOR_CHAR.charAt(0)); }
              }
             CSVWriter.write(CSVRecord.toString());
             // Break after this output line if fatal error encountered during filterChar
             if (filterFailure) { break; }
          }
          // Close output CSV file when all records written
          CSVWriter.flush();
          CSVWriter.close();
          if (filterFailure) { importFailure = true; };
       }
       catch (Exception e) {
          importFailure = true;
          e.printStackTrace();
       }
       // Always close SQL connection, statement and result set
       finally {
          if (rs != null) try { rs.close(); } catch (Exception e) {;}
          if (stmt != null) try { stmt.close(); } catch (Exception e) {;}
          if (conn != null) try { conn.close(); } catch (Exception e) {;}
       }
       return(importFailure);
    }
    /** Ensure trimmed string is non-null and replace CR and LF characters */
    String filterChar(String data) {
      try {
       if (data == null || data.length() == 0) return("");
       StringBuffer buf = new StringBuffer(data);
       for (int p = 0; p < buf.length(); ) {
          char c = buf.charAt(p);
          if (c == (char)0X0D) {
            // Null value replacement
            if (CR_REPLACE.length() == 0) {
              buf.deleteCharAt(p);
            }
            else {
              buf.setCharAt(p,CR_REPLACE.charAt(0));
              p++;
            }
          }
          else if (c == (char)0X0A) {
            if (LF_REPLACE.length() == 0) {
              buf.deleteCharAt(p);
            }
            else {
              buf.setCharAt(p,LF_REPLACE.charAt(0));
              p++;
            }
          }
          else {
            p++;
          }
       }
       if (buf.length() == 0) return("");
       String newData = buf.toString().trim();
       if (newData == null || newData.length() == 0) return("");
       return(newData);
      }
       catch (Exception e) {
          filterFailure = true;
          e.printStackTrace();
       }
       return("");
    }
    /** Get next element (as int) from CSV String (numeric element only).
     Return -1 if reached end of String */
  int nextIntToken(String CSV) {
    String nextElement = null;
    if (CSV == null || el >= CSV.length() - 1) return(-1);
      int el2 = CSV.indexOf(',',el+1);
      if (el2 > el) {
        nextElement = CSV.substring(el+1,el2);
        el = el2;
      }
      else {
        nextElement = CSV.substring(el+1);
        el = CSV.length();
      }
    // Error return if no next element
    if (nextElement == null) return(-1);
    // Otherwise return integer value
    return(Integer.parseInt(nextElement));
  }
}