HELP! Applet's trouble with Servlet's response

[Dan Fishman]

HELP! Will pay $ if you help me.
I have an applet that allows a user to enter a database server URL, username, password, and SQL string, then send them to a servlet that will respond by sending back the resultset in Micro$haft EXCEL, that is being launched from the browser. But it doesn't work. I know the servlet gets the input data, but I think the problem is I don't know how to force the applet to delegate the reponse to the browser that the applet is launched from. I don't expect the applet to try to "open" the response in EXCEL, but I expect THE BROWSER to open the response in EXCEL. That I believe is the main problem. My problem is I need to explicitly force the applet to delegate or shut itself off temporarily then let the browser parse the EXCEL output from the servlet. I don't know how to do this! I have a similar program that works but it is just simple HTML communicating with servlet. Perhaps it is impossible to do this with an applet??!! I'm pretty sure my problem isn't with my servlet code because it is simply outputting data in EXCEL MIME type, the problem lies with my applet code. Please help!

Here's the applet source code:

 
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.net.*;
import java.io.*;
 
 
public class SQLApplet extends JApplet {
private JComboBox cmboDBServer;
private JTextField txtUsername, txtPassword;
        private JTextArea qryTextArea;
        private String[] qryParameters = new String[4];
        private JButton btnExecute;
 
/**
 * Setup the GUI.
 */
public void init() {
Container contentPane = getContentPane();
                contentPane.setLayout(new FlowLayout());
 
String [] strDBServers =              {"DATASOURCE1","DATASOURCE2","DATASOURCE3"};
cmboDBServer = new JComboBox(strDBServers);
cmboDBServer.setEditable(true);
          contentPane.add(cmboDBServer);
 
txtUsername = new JTextField(10);
                contentPane.add(txtUsername);
 
txtPassword = new JPasswordField(10);
                contentPane.add(txtPassword);
 
qryTextArea = new JTextArea(30,70);
JScrollPane qryScrollPane = new JScrollPane(qryTextArea);
                contentPane.add(qryScrollPane);
 
        btnExecute = new JButton("Execute");
        contentPane.add(btnExecute);
 
btnExecute.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
onSendData();
}
});
 
 
}
 
private URLConnection getServletConnection()
throws MalformedURLException, IOException {
 
URL urlServlet = new URL(getCodeBase(), "AppletServlet");
URLConnection con = urlServlet.openConnection();
 
con.setDoInput(false);
con.setDoOutput(true);
con.setUseCaches(false);
con.setRequestProperty(
"Content-Type",
"application/x-java-serialized-object");
 
return con;
}
 
private void onSendData() {
try {
// get input data for sending
qryParameters[0] = cmboDBServer.getSelectedItem().toString();
            qryParameters[1] = txtUsername.getText();
            qryParameters[2] = txtPassword.getText();
            qryParameters[3] = qryTextArea.getText();
 
// send data to the servlet
URLConnection con = getServletConnection();
OutputStream outstream = con.getOutputStream();
ObjectOutputStream oos = new ObjectOutputStream(outstream);
oos.writeObject(qryParameters);
oos.flush();
oos.close();
 
} catch (Exception ex) {
ex.printStackTrace();
}
}
}

Now here's the servlet source code:

 
import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
 
public class AppletServlet extends HttpServlet {
 
  public void doGet(HttpServletRequest req, HttpServletResponse res)
                               throws ServletException, IOException {
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;
    ResultSetMetaData rsmd = null;
    String[] qryParameters = new String[4];
 
    res.setContentType("application/vnd.ms-excel");
    PrintWriter out = res.getWriter();
 
try {
// read a String-object from applet
// instead of a String-object, you can transmit any object, which
// is known to the servlet and to the applet
InputStream in = req.getInputStream();
ObjectInputStream inputFromApplet = new ObjectInputStream(in);
qryParameters = (String[]) inputFromApplet.readObject();
inputFromApplet.close();
in.close();
 
} catch (IOException ioe) {
out.println("I/O Exception caught: " + ioe.getMessage());
}
        catch(ClassNotFoundException e) {
                out.println("Couldn't load database driver: " + e.getMessage());
        }
 
    try {
      // Load (and therefore register) Sun's JDBC-ODBC Driver
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
 
      // Get a Connection to the database
      con = DriverManager.getConnection(
        "jdbc   :-o	   dbc:"+qryParameters[0], qryParameters[1], qryParameters[2]);
 
      // Create a Statement object
      stmt = con.createStatement();
 
      // Execute an SQL query, get a ResultSet
      rs = stmt.executeQuery(qryParameters[3]);
      rsmd = rs.getMetaData();
 
      int numberOfColumns = rsmd.getColumnCount();
      for (int i = 1; i <= numberOfColumns; i++) {
      if (i > 1) out.print("\t");
    String columnName = rsmd.getColumnName(i);
    out.print(columnName);
}
out.println("");
 
while (rs.next()) {
  for (int i=1; i <= numberOfColumns; i++) {
    if (i > 1) out.print("\t");
out.print(rs.getObject(i));
  }
  out.println("");
}
 
 
      stmt.close();
    }
    catch(ClassNotFoundException e) {
      out.println("Couldn't load database driver: " + e.getMessage());
    }
    catch(SQLException e) {
      out.println("SQLException caught: " + e.getMessage());
    }
    finally {
      // Always close the database connection.
      try {
        if (con != null) con.close();
      }
      catch (SQLException ignored) { }
    }
  }
 
  //Process the HTTP Post request
  public void doPost(HttpServletRequest req,
    HttpServletResponse res)
    throws ServletException, IOException {
    doPost(req, res);
  }
}

Thanks in advance.

-Dan
[ June 19, 2005: Message edited by: Dan Fishman ]

[Ernest Friedman-Hill]

Hi Dan,

Welcome to JavaRanch!

You can't do this with URLConnection. Instead of using serialization and all that fancy stuff, change your servlet to take just plain old CGI-type parameters encoded in the URL, and them use the AppletContext.showDocument() method to submit the URL containing those parameters to the servlet. The document that the servlet returns will be fielded by the browser. Use the optional second parameter to prevent the applet from being replaced. For this to work, of course, the servlet will have to use a Content-type that the browser recognizes as an Excel spreadsheet, and the browser has to be configured to launch Excel.

Don't want yer money, but you can clean out behind the pig pen if'n y'all want to help out.

[Dan Fishman]

Hi Ernest. Thanks! I was afraid of that. But, for security reasons, I prefer to POST my parameters rather than display them within the URL. I don't want my or other's password displayed in the URL. Furthermore, isn't there also a limit to how long my parameters can be? What if I have a long SQL string? Sorry for the dumb questions.

-Dan

[Dan Fishman]

Here's the code to make it work now just in case anyone's interested. I don't have much experience with the GET method. It sure was a learning experience. The problem I ran into was that it doesn't like carriage returns. I had to create a function to remove carriage returns in the SQL statement. That was the only stumbling block I ran into. The rest of was pretty easy.

 
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.net.*;
import java.io.*;
 
 
public class SQLApplet extends JApplet {
private JComboBox cmboDBServer;
private JTextField txtUsername, txtPassword;
        private JTextArea qryTextArea;
        private JButton btnExecute;
 
/**
 * Setup the GUI.
 */
public void init() {
Container contentPane = getContentPane();
                contentPane.setLayout(new FlowLayout());
 
String [] strDBServers = {"MMP-SQLP-WARRANTY","DSSOGW01","DSNOGW01"};
cmboDBServer = new JComboBox(strDBServers);
cmboDBServer.setEditable(true);
          contentPane.add(cmboDBServer);
 
txtUsername = new JTextField(10);
                contentPane.add(txtUsername);
 
txtPassword = new JPasswordField(10);
                contentPane.add(txtPassword);
 
qryTextArea = new JTextArea(30,70);
JScrollPane qryScrollPane = new JScrollPane(qryTextArea);
                contentPane.add(qryScrollPane);
 
                btnExecute = new JButton("Execute");
                contentPane.add(btnExecute);
 
btnExecute.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
sendData();
}
});
 
 
}
 
private void sendData() {
try {
// get input data for sending
String strServer = cmboDBServer.getSelectedItem().toString();
            String strUsername = txtUsername.getText();
            String strPassword = txtPassword.getText();
            String strQuery = qryTextArea.getText();
 
            String strSQL = formatString(strQuery);
 
            String targetString = "http://localhost/yourwebapp/SQLServlet?dbURL="+strServer+
                                  "&"+"Username="+strUsername+"&"+"Password="+strPassword+
                                  "&"+"sqlString="+strSQL;
 
            URL targetURL = new URL(targetString);
 
getAppletContext().showDocument(targetURL);
 
 
} catch (Exception ex) {
ex.printStackTrace();
}
}
        // Remove carriage returns
private String formatString (String str) {
str = str.trim();
String newString = "";
String[] stringParts = str.split("\n");
for (int i=0; i < stringParts.length; i++) {
newString = newString+" "+stringParts[i];
}
return newString;
}
}

[ June 19, 2005: Message edited by: Dan Fishman ]

[Ben Souther]

Dan,
One tip that will probably get you more help on this site.

Whenever posting more than a line or two of code, wrap it in the UBB code tags (there is a button on the edit screen that will create them for you). This preserves your indenting and makes your code a lot easier to read. This usually results in more people reading it and thus... more help.

[Dan Fishman]

So folks. Is there a way to send the request data using POST method? Anyone? Using the GET method as was suggested works fine, but I can't send long SQL statements and furthermore, I am not allowed to use certain characters that are commonly used in SQL statements. Along with these issues and also because the password is visible, I am hoping someone can suggest an alternative method. Thanks.

-Dan

[Dan Fishman]

Wow no response! But alas, I found a way to use POST method. I modified the original applet to communicate with the servlet that will handle my query request (execute SQL, create resultset, save resultset as text file). Then I have it invoke another servlet that I created whose sole purpose is to open the file and launch it within the browser as an EXCEL file.

Here's the applet code:

import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.net.*;
import java.io.*;
 
 
public class SQLApplet extends JApplet {
private JComboBox cmboDBServer;
private JTextField txtUsername, txtPassword, txtFilename;
        private JTextArea qryTextArea;
        private JButton btnExecute;
 
/**
 * Setup the GUI.
 */
public void init() {
Container contentPane = getContentPane();
                contentPane.setLayout(new FlowLayout());
 
String [] strDBServers = {"DATASOURCE1","DATASOURCE2"};
cmboDBServer = new JComboBox(strDBServers);
cmboDBServer.setEditable(true);
          contentPane.add(cmboDBServer);
 
txtUsername = new JTextField(10);
                contentPane.add(txtUsername);
 
txtPassword = new JPasswordField(10);
                contentPane.add(txtPassword);
 
                txtFilename = new JTextField(15);
                contentPane.add(txtFilename);
 
qryTextArea = new JTextArea(30,70);
JScrollPane qryScrollPane = new JScrollPane(qryTextArea);
                contentPane.add(qryScrollPane);
 
                btnExecute = new JButton("Execute");
                contentPane.add(btnExecute);
 
btnExecute.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
sendData();
}
});
 
 
}
 
private URLConnection getServletConnection()
throws MalformedURLException, IOException {
 
// Connect to the SQL Servlet
URL urlServlet = new URL(getCodeBase(), "SQLServletSerialized");
URLConnection con = urlServlet.openConnection();
 
// Set the applet to receive input and send output
con.setDoInput(true);
con.setDoOutput(true);
con.setUseCaches(false);
con.setRequestProperty(
"Content-Type",
"application/x-java-serialized-object");
return con;
}
 
private void sendData() {
try {
 
   String[] strParameters = new String[5];
   strParameters[0] = cmboDBServer.getSelectedItem().toString();
   strParameters[1] = txtUsername.getText();
                   strParameters[2] = txtPassword.getText();
                   strParameters[3] = qryTextArea.getText();
                   strParameters[4] = txtFilename.getText();
 
   // send data to the servlet
   URLConnection con = getServletConnection();
   OutputStream outstream = con.getOutputStream();
   ObjectOutputStream oos = new ObjectOutputStream(outstream);
   oos.writeObject(strParameters);
   oos.flush();
   oos.close();
 
// receive result from servlet
InputStream instr = con.getInputStream();
ObjectInputStream inputFromServlet = new ObjectInputStream(instr);
String result = (String) inputFromServlet.readObject();
inputFromServlet.close();
 
 
                  String targetString = "http://yourwebapp/ShowResultSetServlet?filename="+txtFilename.getText();
 
 
                  URL targetURL = new URL(targetString);
 
getAppletContext().showDocument(targetURL);
 
 
 
 
} catch (Exception ex) {
System.out.println("Exception: " + ex.getMessage());
}
}
}

Here's the SQLServletSerialized code that handles my query request:

import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
 
public class SQLServletSerialized extends HttpServlet {
 
  public void doGet(HttpServletRequest req, HttpServletResponse res)
                               throws ServletException, IOException {
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;
    ResultSetMetaData rsmd = null;
    PrintWriter out = null;
    res.setContentType("application/x-java-serialized-object");
 
    try {
      // Get query parameters from the SQL Applet and store them as a String array
      InputStream in = req.getInputStream();
      ObjectInputStream inputFromApplet = new ObjectInputStream(in);
      String[] qryParameters = (String[]) inputFromApplet.readObject();
 
      System.out.println(qryParameters[0]);
 
 
      File result = new File("c:/hostdirectory/"+qryParameters[4]);
      out = new PrintWriter(new BufferedWriter(new FileWriter(result)));
 
 
      // Load (and therefore register) Sun's JDBC-ODBC Driver
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
 
      // Get a Connection to the database
      con = DriverManager.getConnection(
        "jdbc     :-o	     dbc:"+qryParameters[0], qryParameters[1], qryParameters[2]);
 
      // Create a Statement object
      stmt = con.createStatement();
 
      // Execute an SQL query, get a ResultSet
      rs = stmt.executeQuery(qryParameters[3]);
      rsmd = rs.getMetaData();
 
      int numberOfColumns = rsmd.getColumnCount();
      for (int i = 1; i <= numberOfColumns; i++) {
      if (i > 1) out.print("\t");
    String columnName = rsmd.getColumnName(i);
    out.print(columnName);
}
out.println("");
 
while (rs.next()) {
  for (int i=1; i <= numberOfColumns; i++) {
    if (i > 1) out.print("\t");
out.print(rs.getObject(i));
  }
  out.println("");
}
 
 
                        stmt.close();
 
 
OutputStream outstr = res.getOutputStream();
ObjectOutputStream oos = new ObjectOutputStream(outstr);
                        String strMessage = "Query completed";
oos.writeObject(strMessage);
oos.flush();
oos.close();
 
 
                        out.close();
 
    }
    catch(ClassNotFoundException e) {
      out.println("Couldn't load database driver: " + e.getMessage());
    }
    catch(SQLException e) {
      out.println("SQLException caught: " + e.getMessage());
    }
    finally {
      // Always close the database connection.
      try {
        if (con != null) con.close();
      }
      catch (SQLException ignored) { }
    }
  }
 
  //Process the HTTP Post request
  public void doPost(HttpServletRequest request,
    HttpServletResponse response)
    throws ServletException, IOException {
    doGet(request, response);
  }
}

Finally, here's the code for the servlet that just opens the resultset textfile as an EXCEL file within your browser:

import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
 
public class ShowResultSetServlet extends HttpServlet {
 
  public void doGet(HttpServletRequest req, HttpServletResponse res)
                               throws ServletException, IOException {
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;
    ResultSetMetaData rsmd = null;
 
    res.setContentType("application/vnd.ms-excel");
    PrintWriter out = res.getWriter();
    String strFile = req.getParameter("filename");
 
    File resultSet = new File("c:/hostdirectory/"+strFile);
    if (resultSet.exists()) {
       BufferedReader in = new BufferedReader(new FileReader(resultSet));
       String line = in.readLine();
       while(line != null) {
          out.println(line);
          line = in.readLine();
       }
       in.close();
    }
  }
 
  //Process the HTTP Post request
  public void doPost(HttpServletRequest request,
    HttpServletResponse response)
    throws ServletException, IOException {
    doGet(request, response);
  }
}

Just FYI, the codes above are not thread safe and assumes Windows style directory system. I'm sure someone can do better...

Have a nice day,
Daniel

[ June 29, 2005: Message edited by: Dan Fishman ]

[ June 29, 2005: Message edited by: Dan Fishman ]

[ June 29, 2005: Message edited by: Dan Fishman ]
[ July 14, 2005: Message edited by: Dan Fishman ]