// set the auto-increment counter to the next highest unused index. otherwise it with keep making a longer and longer gap in the indexes.
String statement1 = "ALTER TABLE compass.program_profile_info AUTO_INCREMENT = 1";
PreparedStatement pstmt1 = dbconn.prepareStatement(statement1);
pstmt1.executeUpdate();
} catch (Exception e) {e.printStackTrace();}
CSVReader cSVReader = new CSVReader(new InputStreamReader(in), ',', '"', 1);
String [] nextLine;
while ((nextLine = cSVReader.readNext()) != null) {
System.out.println("read from CSV url: " + nextLine[0] + "," + nextLine[1] + "," + "etc...");
String statement = "insert into compass.program_profile_info " +
"(agency_name, program_name, program_type_code, program_type, " +
"target_pop_a_code, target_pop_a_name, units_total, units_occupied, " +
"units_available, contact_name, contact_phone, program_address_full, feed_source, update_time_stamp) " +
"values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
PreparedStatement pstmt = dbconn.prepareStatement(statement);
pstmt.setString(1, nextLine[0]);
//System.out.println("agency name is: " + nextLine[0]);
pstmt.setString(2, nextLine[1]);
//System.out.println("program name is: " + nextLine[1]);
String programTypeCode = translateProgramTypeCode(nextLine[2]);
//System.out.println("program type code is: " + programTypeCode);
pstmt.setString(3, programTypeCode);
String programType = translateProgramType(nextLine[2]);
//System.out.println("program type is: " + programType);
pstmt.setString(4, programType);
System.out.println("nextLine[3] is: " + nextLine[3]);
String targetPopulation = translateTargetPopulation(nextLine[3]);
System.out.println("target population is: " + targetPopulation);
pstmt.setString(5, targetPopulation);
pstmt.setString(6, translateTargetPopulationCode(targetPopulation));
// units total
pstmt.setString(7, nextLine[5]);
// units occupied
pstmt.setString(8, nextLine[6]);
// units available
pstmt.setString(9, nextLine[7]);
// contact/phone #/address
String[] contactInfo = splitContactInfo(nextLine[8]);
if (contactInfo.length == 1) {
// contact
pstmt.setString(10, contactInfo[0]);
// phone
pstmt.setString(11, null);
// address
pstmt.setString(12, null);
}
if (contactInfo.length == 2) {
// contact
pstmt.setString(10, null);
// phone
pstmt.setString(11, contactInfo[0]);
// address
pstmt.setString(12, contactInfo[1]);
}
if (contactInfo.length == 3) {
// contact
pstmt.setString(10, contactInfo[0]);
// phone
pstmt.setString(11, contactInfo[1]);
// address
pstmt.setString(12, contactInfo[2]);
}
else {
// contact
pstmt.setString(10, null);
// phone
pstmt.setString(11, null);
// address
pstmt.setString(12, null);
}
// data source code
pstmt.setString(13, "1");
pstmt.setTimestamp(14, new Timestamp(System.currentTimeMillis()));
pstmt.executeUpdate();
}
System.out.println("While loop ended");
httpconn.disconnect();
cSVReader.close();
} catch (Exception e) {e.printStackTrace();}
}
catch (SQLException e) {
e.printStackTrace();
}