import java.sql.*;
public class TestARS {
  public static void main (String args[]) throws Exception {
    
  Class.forName("org.apache.derby.jdbc.ClientDriver");
  Connection conn = DriverManager.getConnection("jdbc:derby://localhost:1527/ARS;create=true;user=guest;password=password");
  //
  // change the following from "false" to "true" to automatically commit each update:
  conn.setAutoCommit(false);
  //
  String querySQL = "SELECT flightNo, carrier FROM flights WHERE flightNo = ?";
  String insertSQL = "INSERT INTO flights (flightNo, carrier, originCity, destinationCity, totalSeats, fare) values (?, ?, ?, ?, ?, ?)"; 
  String deleteSQL = "DELETE FROM flights WHERE flightNo = ?";
  String carrier="***not found***";
  int searchFlightNo=500;
  int flightNo=0;
  if (args.length>0) {
    searchFlightNo=Integer.parseInt(args[0]);
  }
  try {
    PreparedStatement query = conn.prepareStatement(querySQL);
    query.setInt(1, searchFlightNo);
    ResultSet rs=query.executeQuery();
    while (rs.next()) {
      carrier=rs.getString("carrier");
      flightNo=rs.getInt("flightNo");
    }  
    System.out.println("Carrier for Flight No : " + searchFlightNo + " is " + carrier);
    query.close();
    
    } 
  catch (SQLException e)
        {System.out.println(e.getMessage());
    }
  // 
  // uncomment delete if the flight was inserted and committed:
  //
  /*try {
    PreparedStatement delete = conn.prepareStatement(deleteSQL);
    delete.setInt(1, searchFlightNo);
    int result = delete.executeUpdate();
    if (result == 1) {
      System.out.println("Flight No : " + searchFlightNo + " deleted");
    }
    else {
      System.out.println("Flight No : " + searchFlightNo + " not found on delete");
    }
    delete.close();
    
    } 
  catch (SQLException e)
        {System.out.println(e.getMessage());
    }*/
  
  try {
    PreparedStatement insert = conn.prepareStatement(insertSQL);
    insert.setInt(1, searchFlightNo);
    insert.setString(2, "DL");
    insert.setString(3, "Las Vegas");
    insert.setString(4, "Orlando");
    insert.setInt(5, 220);
    insert.setDouble(6, 199.00);
    int result = insert.executeUpdate();
    System.out.println("Flight No : " + searchFlightNo + " inserted");
    insert.close();
    
    } 
  catch (SQLException e) {
        {System.out.println(e.getMessage());}
    }
  //
  // when autocommit is set to false, either commit the transaction or rollback:
  conn.rollback();
  //conn.commit();
  conn.close();
  }
}