<%@ page import="java.sql.*,
                 java.util.Properties" %>
<%
  String question = request.getParameter("question");
  String category = request.getParameter("category");
  String answerHint = request.getParameter("answerHint");
  int answerId =
    Integer.parseInt(request.getParameter("answerId"));
  int hasImage = Integer.parseInt(request.getParameter("hasImage"));
  int hasMusic = Integer.parseInt(request.getParameter("hasMusic"));
  int sendAnswer = Integer.parseInt(request.getParameter("sendAnswer"));

  String choices[] = new String [6];
  choices[0] = request.getParameter("choice0");
  choices[1] = request.getParameter("choice1");
  choices[2] = request.getParameter("choice2");
  choices[3] = request.getParameter("choice3");
  choices[4] = request.getParameter("choice4");
  choices[5] = request.getParameter("choice5");

  // Get the path to the database
  Properties conf = new Properties();
  conf.load(this.getClass().getResourceAsStream("/config.properties"));
  String fileroot = conf.getProperty("DBFileRoot");

  // Retrieve the database connection
  Class.forName("org.hsqldb.jdbcDriver");
  Connection c = 
    DriverManager.getConnection ("jdbc:hsqldb:" + fileroot + "TriviaDB", "sa", "");
  c.setAutoCommit(false);

  PreparedStatement insert1 = 
    c.prepareStatement("insert into Questions " +
      "(question, category, answerId, hasImage, hasMusic, answerHint, sendAnswer) " +
      "values (?, ?, ?, ?, ?, ?, ?)");
  insert1.setString(1, question);
  insert1.setString(2, category);
  insert1.setInt(3, answerId);
  insert1.setInt(4, hasImage);
  insert1.setInt(5, hasMusic);
  insert1.setString(6, answerHint);
  insert1.setInt(7, sendAnswer);
  int status = insert1.executeUpdate();

  Statement query = c.createStatement();
  ResultSet rs = query.executeQuery ("call identity()");
  rs.next();
  int questionId = rs.getInt(1);

  PreparedStatement insert2 = 
    c.prepareStatement("insert into Choices " +
                       "(questionId, choiceId, choice) " +
                       "values (?, ?, ?)");
  for (int i = 0; i < choices.length; i++) {
    if ( choices[i] != null && !"".equals(choices[i]) ) {
      insert2.setInt(1, questionId);
      insert2.setInt(2, i+1);
      insert2.setString(3, choices[i]);
      status = insert2.executeUpdate();
    }
  }
  c.commit();

  insert1.close();
  insert2.close();
%>

<html>
<body>
<p>Now, please rename the multimedia files to <%=questionId%> .gif/.png/.mid

<p>The current database content:

<pre>
<%
  rs = query.executeQuery ("Select * from Questions");
  while (rs.next()) {
%>
<%=rs.getInt(1)%> <%=rs.getString(2)%> <%=rs.getString(3)%> <%=rs.getInt(4)%> <%=rs.getInt(5)%> <%=rs.getInt(6)%>
<%
  }
  rs = query.executeQuery ("Select * from Choices");
  while (rs.next()) {
%>
<%=rs.getInt(1)%> <%=rs.getInt(2)%> <%=rs.getInt(3)%> <%=rs.getString(4)%>
<%
  }
  rs.close();
  c.close();
%>
</pre>
</body>
</html>
