Thanks Celeste. My background in programming is not even MS, it's Lotus Notes.
(I know I know.. it's not a real programming language but knowing lotus script
helped me get into real programming.)
Creating and dropping tables was what some colleagues (non java) of mine
suggested to avoid having too many joints. I think I explained wrong what I
meant to say. They said to create these tables to store the resultsets of
queries that have a lot of joins. I wanted to call them temporary tables but
didn't want to get people confused with SQL temporary tables. But the problem
I see in their suggested solution, is that everytime the data is updated, then
these tables would have to be dropped and recreated. Since they are not the
ones being updated but the normalized tables.
I was told that if you have to make more than 3 joints, then you will have
performance issues. I thought of using views and then storing them as appl
variables.
This specific application is small (around 5000 records)
As for the reports, I first started rewriting them in SQL and JSP. But then I
got access to Crystal Report and I saved all the SQLs as Stored Procedures. I
would love to have used XML/XSLT but there were all these different components I
had to learn to implement, that I decided to take the easy way for reporting and
use Crystal Report or just JSP/SQL.
But I'll definitely check out your suggestion about using Jrun stored procedure
tag for the updates.
Thanks!
ps We are using MS SQL Server 7.
"Haseltine,
Celeste" To: Jrun-Talk
<[EMAIL PROTECTED]>
<CHaseltine@magt cc:
icket.com> Subject: RE: Using Views
03/07/2002 03:01
PM
Please respond
to jrun-talk
Zeina,
Congradulations on your first JSP/JRUN product. I also made the switch from
MS to Java/JSP about 2 years ago, and I haven't regretted it yet.
I'm afraid I don't have any advice to offer regarding database views, as I
prefer to use SQL inner or outer joins to return the exact data I need in a
result set. But your statement regarding "creating and dropping tables
everytime I need to update the database" caught my attention. When I need
to update my database, I don't create any temporary tables, and I'm not sure
why you would want to do it that way. I prefer to use stored procedures to
update my database, and use the JRUN stored procedure tag. You can return
the @@error value from the execution of a stored procedure to determine
whether the stored procedure executed correctly, or if not, if you need to
do a roll back on the table(s) in question and send an error message to
either your user or to the webmaster/network admin. I don't have Drew's
book, but if you get a good book on SQLServer 2000, you can read up on using
stored procuedures to update your tables and to return result sets to you.
Again, you can use the JRUN stored proceudre tag (if you using 3.1), to
execute your stored procedures.
By the way, how did you duplicate your Access reports in JSP? Did you use
XML/XSLT to create the reports for viewing on the browser, or did you find
another means of creating reports?
Celeste
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 07, 2002 1:30 PM
To: JRun-Talk
Subject: Using Views
First, want to thank you all for telling me about, pooling, jrun tags, using
QueryTable, cashing resultset and application variables. Oh and avoiding
having
many resultsets. That last part was more difficult to implement not from
the
SQL part, but in the jsp, to avoid duplicate rows. I used conditions and
looping but the way the data was presented, did not make it easy. Oh and
Drew,
great book. Found it extremely helpful and useful. I actually refer to it
all
the time.
My assignment was to take an access database that was designed in 1994 and
redesign it in JSP and Jrun. As you can imagine, I had to go through
thousands
of queries, tables and macros to really identify all the elements being
used.
Worse part, the reports done by the application were all wrong because they
were
pulling data from wrong queries and tables.
The first stage of my redesign, I tried to replicate the design and
functionalities of the existing application in JRUN and MS SQL Server. (Not
to
mention that's how I learned JSP ;). Anyway, I was able to do that, and
was
able to produce all the reports correctly, all the functionalities etc.. Now
that I understand all the parts of the application and business side, I'm
remodeling the data, and simplifying the processes and making them more
generic
and reusable modules. .
Sorry for the lengthy background... anyway, my question is the following:
I have normalized the tables, and we ended up needing 1/4 of the fields and
tables originally created. I was planning on using Views to avoid having
too
many joints in each SQL. I am also planning to catch the resultsets.
Do any of you know the disadvantages of using views? I like them because
they
are dynamic so it's better than having to create and drop tables every time
we
need to update the database.
Thanks,
Zeina
______________________________________________________________________
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
