Celeste,
I am using MS SQL Server and am using inner/outer joins. The reason why I am
looping through two resultsets and not joining them, is:
While I need one row for each projectid from the first resultset, I need to get
all the rows with the same project id from the second resultset. When I do
inner join, the resultset includes the duplicate data.
Then instead of having multiple duplicate rows in the HTML table, with the same
info except for few columns for each project x, having two resulset allows me to
populate the first row with the first resulset row and then I am adding columns
for the additional info for each projectid x from the second resultset.
Thanks,
Zeina
"Haseltine,
Celeste" To: Jrun-Talk
<[EMAIL PROTECTED]>
<CHaseltine@magt cc:
icket.com> Subject: RE: Searching Resultset - my
solution
02/26/2002 02:19
PM
Please respond
to jrun-talk
Zafif,
I have not really been following this discussion, so the suggestion I am
about to make may have already been made. But it sounds to me like you are
returning two result sets, and are looping through the second result set to
find the records that relate back to a particular record in the first result
set. Why are you doing this? Besides taking up a LOT of memory on your
server, it's an inefficient way to code. A couple of suggestions here.
1. If you need the first result set to display a list for user selection,
then do so. After the user selects the item he is interested in via your
list, store the primary key value for the record your user selects in a
session object. On the next "page", re-query your database using either an
inner or outer join to return ONLY the primary record and its related
records back to you, and then proceed on.
2. If you are NOT using a relational database on the back end that allows
you to perform inner or outer join query's, consider using an XML file to
initially store your data. You can query your database to obtain your
records, write them out to an XML file onto your server, and then use that
XML file as a "read only" version of your data for the duration of the
session, making sure that you destroy it when your user logs out. Then you
can then traverse the XML "tree" to find your primary record, and all it's
related "child" records, IF you define your XML "tree" in this manner.
In the event you are not familiar with inner/outer SQL joins, I have
provided an example for you.
In the below example I have a parent table called Orders and a child table
called OrderDetails. The relationship between the two tables is one Order
record to one or many OrderDetails records. In order to return a particular
order record which contains ALL the related detail records for an Order with
the primary key value of 2, I can query the db as follows:
SELECT OrderDetails.OrderID, Orders.DateOrderPlaced,
Orders.ConfirmationNumber, OrderDetails.TicketID,
OrderDetails.Quantity, Orders.OrderID
FROM OrderDetails INNER JOIN
Orders ON OrderDetails.OrderID = Orders.OrderID
WHERE (OrderDetails.OrderID = 2)
I don't know if this helps you any, but please re-evaluate your need to
maintain two large result sets in memory. That's generally not a good way
to code JSP's.
Celeste
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 26, 2002 12:45 PM
To: JRun-Talk
Subject: RE: Searching Resultset - my solution
Nikhil, tried to do what U suggested, but then for each entry in the
Hashtable,
I would have a vector, and since there are over 100 records...I will end up
with
over 100 vectors. I wasn't sure if that was efficient.
What I am really trying to do is loop through one resultset A and in it,
loop
through another resultset B, and get the matching rows. So I tried
searching
through the resultset B by using the while(rsB.next()) and then finding the
rows
that I need, but the problem I had, jrun would not accept rsB.beforeFirst(),
so
I got data for the first row and not the rest of resultset A.
Finally, I created 4 vectors, that each stored one of the columns of the
resultset B, and if the column had a null value, I replaced it with '-'.
Then
searched through one of the vectors for the parameter and
Vector vProj = new Vector();
Vector vODS_TYPE = new Vector();
Vector vPrevious_ODS = new Vector();
Vector vODS_Phased_Out = new Vector();
while (rsB.next()){
vProj.addElement(rsB.getString("SUB_PROD_ID") ==
null?"-":rsB.getString
("SUB_PROD_ID"));
vPrevious_ODS.addElement(rsB.getString("Previous_ODS_to_be_Phased_Out")
==null?"-":rsB.getString("Previous_ODS_to_be_Phased_Out"));
vODS_TYPE.addElement(rsB.getString("ODS_TYPE") ==
null?"-":sB.getString
("ODS_TYPE"));
vPrevious_ODS.addElement(rsB.getString("Previous_ODS_to_be_Phased_Out")
==null?"-":rsB.getString("Previous_ODS_to_be_Phased_Out")) ;
vODS_Phased_Out.addElement(rsB.getString("ODS_Phased_Out") ==null?"
-":rsB.getString("ODS_Phased_Out"));}
// I loop through the Resultset A, and populate a row of the table, then
search
for the parameter in one of the vectors (vProj).
------------------
-------------------
//
for(int x = 0; x < vProj.size(); x++){
if((vProj.elementAt(x)).equals(Sub_Project_ID)){
counto++;
%>
<td class="ReportTableCellLabel">
<%=vODS_TYPE.elementAt(x)%>
</td>
<td class="ReportTableCellLabel">
<%=vPrevious_ODS.elementAt(x)%>
</td>
<td class="ReportTableCellLabel">
<%=vODS_Phased_Out.elementAt(x)%>
</td>
<%
}
}
I don't know if this is the best solution, but that's the only way I could
get
it to work. When I first learned about the QueryTable Object, I thought it
would include a method to search through the QueryTable obj.
Thanks,
Zeina
Nikhil Silsarma
<Nikhil.Silsarma@digit To: Jrun-Talk
<[EMAIL PROTECTED]>
alrum.com> cc:
Subject: RE: Searching
Resultset
02/26/2002 10:58 AM
Please respond to
jrun-talk
Why don't you use Hashtable( key=parameter, value=another hashtable/or
Vector). In that case, based on the key it will return the data).
Nikhil R Silsarma
Developer
DigitalRUM UK Ltd.
Tel: 020 7604 2049
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: 26 February 2002 15:20
To: JRun-Talk
Subject: Searching Resultset
Drew, Great book! Went through it over the weekend! Wish I got it 6 months
ago, when I started to develop in JSP! Could have saved me lot of time!!
Anyway, my q for the day. I need to be able to search a resultset. The
parameter that I need to use to search, is not unique in the Resultset and I
need to be able to get all the entries with that key.
I thought of using hashatable, but since the key is not unique, it won't
work, I
thought of using vectors of arrays. I looked into QueryTable and didn't
find
any methods to get by key.
Is there an easy way to search the resultset based on a value?
Thanks,
Zeina
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
