• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

JSP and Oracle

I have a jsp application running on Tomcat with an Oracle backend.
The app creates many items called Cards that each display a unique collection of data culled from various different attributes of different tables in the Oracle database via a simple select sql string.
Some of these strings will be VERY long.
There is also the option of creating a new card with a new string that must be created and stored with the others. Each string will be identified by it's relevant card.
Does anyone have any idea where or how I should store and manage these strings?
Text files?
Stored procedures?
CallableStatements?
HELP!
0
morleycallaghan
Asked:
morleycallaghan
  • 2
  • 2
1 Solution
 
Binary1Commented:
Is there a problem with using a database table to store and maintain these strings? I don't know whether these strings are used as-is or if there are different parameters, etc.

If you store them in a table and design the application around it you can make the number of your cards dynamic, based on the strings stored in the table. Just add a new string to the DB table to add a new card. From the description the table would only have to be two columns, a card ID and the string.

0
 
morleycallaghanAuthor Commented:
I think that the strings would be too long to store in a database: I know they are too long for Access. But I could store the address or path of the text file. I wonder, though, if anyone has had success using some other method?
0
 
Binary1Commented:
I've previously used a system that pulled the SQL string from a text file. It allowed me to change the SQL string without having to change the programming.

In Oracle a varchar(2) column can hold up to 4,000 characters. For larger strings you can use LONG or CLOB which will easily hold up to 2 gigabytes.
0
 
morleycallaghanAuthor Commented:
Ahh I wasn't sure how big a varchar2 could be. I am doing the prelminary work with Access and Access sure can't manage it.
That makes more sense that trying to play with things like stored procedures which give me the heebie jeebies.
0
 
jwentingCommented:
As an alternative you could try putting the SQL statements into an XML file and loading that into memory (indexed on something, maybe an index number or other identifier).

That's probably cheaper in CPU and memory load, at least if the set of queries is quite static.

I've implemented the above for disconnecting all database specific code from an application I did for a customer.
All SQL is in the XML and can be changed to for example take changes in syntax for another database engine into account.
That version does rely on column names remaining the same in the resultsets, but I have ideas to get that separated out as well.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now