Sending a Result Set To another java class via http

Turn the rs into a DefaultTableModel and then serialize its dataVector

You can use this method to get the TableModel

    public TableModel resultSetToTableModel(ResultSet rs) {
      try {
        // The below just for searches
        ResultSetMetaData metaData = rs.getMetaData();
        int numberOfColumns = metaData.getColumnCount();
        Vector columnNames = new Vector();
        // Get the column names
        for (int column = 0; column < numberOfColumns; column++) {
          columnNames.addElement(metaData.getColumnLabel(column + 1));
        }
        // Get all rows.
        Vector rows = new Vector();
        while (rs.next()) {
          Vector newRow = new Vector();
          for (int i = 1; i <= numberOfColumns; i++) {
            newRow.addElement(rs.getObject(i));
          }
          rows.addElement(newRow);
        }
        return new DefaultTableModel(rows, columnNames);
      }
      catch (Exception e) {
        e.printStackTrace();
        return null;
      }
    }

ok now I have a TableModel :)
now what?
thanks
Ghost

that looks really easy :)
wonderful
we will try this soon and get back to you
thanks
Ghost

I brought this solution up to my team yet they said that it might be slow seeing as how there may be thousands of results...
Is there a way to speed it up?
Ghost

You could try sending  each row as a String and use a zip stream

that's what I was thinking.. can we some how convert the result set to a string and then build it back into a result set on the other side?
Ghost

Yes, you'll need to take the String form of each column value, zip it, then do the reverse on the other side

is there a way to convert a result set into an xml string? with just one call
one of my co-workers says that there is such a method in c++. I know nothing about c++. So I firgure I'll ask you?
got anything for me?
thanks
Ghost

Not readily. There are libraries you can get, such as XMLDB

XMLDB??? I never heard of it. I looked it up and I got some stuff
but it looks to me like a linux thing...
I'm developing on windows does that matter?
thanks
Ghost

These apis are confusing. This could help:

http://www.rpbourret.com/xml/XMLAndDatabases.htm

:S are you sure you gave me the right link?
that's just xml information?
Ghost

I think it's going to be simpler to send a String stream though. You'll have to experiment with this.

- more time required to compress
+ lesser amount of data to send

>>:S are you sure you gave me the right link?

It's meant to be xml + db (hopefully some of them Java)

I argree with you
but some here think that having to go through all the rows and then convert them to string and then send them one at a time is too long cause we might be dealing with millions of rows...
can we convert the whole thing and then send that?
thanks
Ghost

we found this..
http://www.developer.com/db/article.php/3329001
but we believe that built in functions would do better than methods we code ourselves..
what do you think?
Ghost

>>but some here think that having to go through all the rows and then convert them to string ...

Any other software is likely to have the same sort of overhead. An xml one would be even worse.

You could try a home-brewed binary stream, but that would probably not be as easily compressible

btw, if you're sending an RS over the wire from A to B, why can't B get the RS directly from the same place A's getting it?

let's say A is a database..

we have to go from the database to B

that's sort of what we are doing
I found a couple more examples...

http://www.jeckle.de/freeStuff/JDBC2XML/#example

I'll take a look at it later, out to lunch! :)
thanks for your help
I'll let you know when I test it.
Ghost

I'd take a guess at http://www.jeckle.de/freeStuff/JDBC2XML/#example being quite a bit slower than what i was suggesting

actually I want to try something else..

I think I'm going to convert the resultSet to a vector and then transmit that over the wire and then convert it back to a result set on the other side.

I need to know the fastest way to do this.
please provide the methods to go from
ResultSet to Vector and
Vector to ResultSet

I"m going to increase the points for this question since it's getting more complicated then I first thought.
thanks for yout help
Ghost

I just noticed that the first thing you posted does resultset to vector..
do you know if that's the fastest way to do it?
thanks
Ghost

>>
I just noticed that the first thing you posted does resultset to vector..
>>

Yes - that was my point ;-)

>>
do you know if that's the fastest way to do it?
>>

As a String should be pretty fast

no I was meaning the conversion from resultset to vector is that the best way to do it? I don't know I'm just asking for your opinion
 :)
cheers,
Ghost

Yes

ResultSet -> Vector -> String(s)

no kidding huh?
but then you will be doing that extra step on both sides...don't you think it would be better just to go from
resultset --> vector --> http -->vector --> resultset

Ghost

ok here is what I have so far..

/**
    * Class that will make the connection and send the request and receive the
    * response.
    */
   public void makeRequest(String strPath, String strXMLParams) {
      try {
         URL url = new URL(strPath);
         HttpURLConnection conn = (HttpURLConnection)url.openConnection();
         conn.setDoOutput(true);
         conn.setRequestMethod("POST");
         OutputStream os = conn.getOutputStream();
         ObjectOutputStream out = new ObjectOutputStream(os);
         conn.setRequestProperty("Content-type", "application/octet-stream");
     
         out.writeObject(strXMLParams);
         out.flush();
         out.close();

         Object inObject = null;
         ObjectInputStream in = new ObjectInputStream (conn.getInputStream());
         inObject = in.readObject();
         in.close();
         rs = (ResultSet)inObject;
      }
      catch(MalformedURLException error) {
         System.out.println("Bad URL");
         error.printStackTrace();
      }
      catch(IOException error) {
         System.out.println("IOException error");
         error.printStackTrace();
      }
      catch(ClassNotFoundException error) {
         System.out.println("ClassNotFoundException error");
         error.printStackTrace();
      }
   }


what method do I add to the jsp
I just want to test to see if it worked.
so a basic read and sending back a resultset (contents not important)
would be great
thanks in advance,
Ghost

You should use a servlet. If you're doing it that way, take the result set in doPost and send it back in doGet. In the client you showed above, better set the request method to GET in the second case

Can I use a jsp page?
What would be the advantages of using a servlet?
>>In the client you showed above, better set the request method to GET in the second case

can you explain this a little futher please?
thanks
Ghost

>>
Can I use a jsp page?
What would be the advantages of using a servlet?
>>

Not easily. A servlet it better as it methods are exposed. I assume the above is just a test case - ( or why would you be sending an RS just to send it right back again?)

what is supposed to happen is we send a query String to our jsp...
the jsp processes the information and then returns the vector containing the resultset to our java
what are your views of this?
Ghost

>>what are your views of this?

You can just post it off and read the output stream sent back. You could use a jsp if you want

I would perfer if we can get the jsp working and then after we will explore other methods
can you help me ou with the jsp?
thanks
Ghost

ok what we decided to do was use a CachedRowSet instead of converting to a vector and then back to a resultset we will just store it in a CachedRowSet.
how can you send a CachedRowSet to a java porgram from a jsp page..
that's all I need..
thanks,
Ghost

ResultSet-->vector is fine
but
Vector-->Resultset is not gonna happen..
as far as I know Resultset is a interface connected to a statement
and hence you will not be able to create it stand alone! ( unless ofcourse you create a class implementing Resulset!!)

Whatever you're sending between to different JVM's must implement java.io.Serializable or it will not be reliably reconstructed.

CachedRowSet does not implement (extend) Serializable... Vector does.  Vector is the better choice...

>>instead of converting to a vector and then back to a resultset

I think you're getting confused. What i was originally suggesting is to convert between Vector and TableModel. The first TableModel can *come* from a ResultSet though.

Even if a CachedRowSet were usable, it would have more (and probably redundant) overhead in comparison

>>searlas

http://java.sun.com/j2se/1.5.0/docs/api/javax/sql/rowset/CachedRowSet.html

6.0 Passing Data to Thin Clients
One of the main reasons to use a CachedRowSet object is to pass data between different components of an application. Because it is serializable, a CachedRowSet object can be used, for example, to send the result of a query executed by an enterprise JavaBeans component running in a server environment over a network to a client running in a web browser.

While a CachedRowSet object is disconnected, it can be much leaner than a ResultSet object with the same data. As a result, it can be especially suitable for sending data to a thin client such as a PDA, where it would be inappropriate to use a JDBC driver due to resource limitations or security considerations. Thus, a CachedRowSet object provides a means to "get rows in" without the need to implement the full JDBC API.

That is why I wanted to use a CachedRowSet.


>>Cehj
This I realise but the time will be saved as we would no longer require to convert from resultset to vector and then back again...
So if we just convert all of our ResultSets into CachedRowSets then we should not have a problem. Right?

All that I need is now the code for the jsp side where we are sending the CachedRowSet to the Java..
what I'm doing is sending a xml string to the jsp then jsp will query a database with the xmlstring and then return a CachedRowSet
now I must send this CachedRowSet back to the java class...

I hope you all understand and can help cause I'm running out of time over here :)
thanks for your help
Ghost

Okay then your code should look like this in jsp..


//Get the XML
//connect to the Database
//get the resultSet and convert it to cachedResultSet.

ObjectOutputStream oop = new ObjectOutputStream(response.getOutputStream());
//Make sure you are not using the outputstream before this particular line, not even by having a space at the start of JSP //because then this line will throw the exception saying illegalStateException..

oop.writeObject(yourobject);
oop.flush();
oop.close();

//End of jsp

in your Java class
//get the URL
//Connect to the URL
//Send XML
ObjectInputStream oip = new ObjectInputStream(con.getInputStream());
ChachedResultSet crs= (ChachedResultSet)oip.readObject();

//use this Chached ResultSet

yes rrz mentioned something to that effect..
I'll try it and get back to you
Ghost

ok I'm getting this error now..

javax.servlet.ServletException: Invalid Cursor position
      org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:862)
      org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:795)
      org.apache.jsp.test_jsp._jspService(test_jsp.java:129)
      org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:133)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
      org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:311)
      org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:301)
      org.apache.jasper.servlet.JspServlet.service(JspServlet.java:248)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:856)


root cause

java.sql.SQLException: Invalid Cursor position
      com.sun.rowset.CachedRowSetImpl.next(CachedRowSetImpl.java:1201)
      com.sun.rowset.CachedRowSetImpl.populate(CachedRowSetImpl.java:474)
      org.apache.jsp.test_jsp._jspService(test_jsp.java:86)
      org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:133)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
      org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:311)
      org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:301)
      org.apache.jasper.servlet.JspServlet.service(JspServlet.java:248)
      javax.servlet.http.HttpServlet.service(HttpServlet.java:856)

Wonderful huh?

this is my code..
It's in my jsp

CachedRowSetImpl rowset = new CachedRowSetImpl();
rowset.populate(rsR);
rowset.beforeFirst();

if (rowset != null ){

  RowSetMetaData  md = (RowSetMetaData)rowset.getMetaData();

    if(rowset.next()) {
      for(int i=1; i <= md.getColumnCount(); i++) {
         out.print(md.getColumnName(i) + " \t| ");
      }
      out.println("<br />");
      do {
         for(int i=1; i<= md.getColumnCount(); i++) {
            out.print(rowset.getString(i) + " \t| ");
         }
         out.println("<br />");
      }while(rowset.next() && rowset != null);  
    }
    else {
      out.println("ResultSet Contained no records");
    }

}  
else {
   out.println("ResultSet Contained null");
}

I'm just trying to print the CachedRowSetImpl to the screen to see if that part is working before I send it off to my java..
Anyideas?
the result set is being fill before. and it does contain records I have tested that part and that works.
thanks,
Ghost

:D never mind that last post I forget to set the resultset back to the begining before populating...
I foresee I long day...
thanks
Ghost

Interesting reference about CachedRowSet being serializable Ghost...

http://java.sun.com/j2se/1.5.0/docs/api/javax/sql/rowset/CachedRowSet.html

I guess this is one of the aspects of using a beta.  That javadoc is wrong... CachedRowSet does not extend Serializable, neither does it's superclass RowSet/ResultSet, neighter does Joinable.  So.... it's just plain wrong.

I don't suppose you've tried:
CachedRowSetImpl rowset = new CachedRowSetImpl();
if (rowset instanceof Serializable) {
  out.println("I am lucky... my app server has a serializable CachedRowSet implementation...");
}

Some are not so lucky...
http://www.jguru.com/forums/view.jsp?EID=1149475

LOL :)
I guess I'm lucky
Tomcat 5.0.14
I get that print out..
weird huh?
Ghost