Empty and Distinct SQL query returned in a servlet...

[Gemma Cameron]

Here is my situation and problems. Any help will be much appreciated!

Firstly in my doGet method I have a drop down list the user can choose from. This list is generated by entries in a database. I have used SELECT DISTINCT to get unique values in this list - however it doesn't seem to return anything - leading me to believe there is a problem with the distinct key word - therefore any work around would also be appreciated... however I would like to avoid complex if and while statements that would only print out a value if it's not already there...

Secondly there is a normal input box in the doGet method. When a user enters a number, which then is recieved as a request in the doPost method, returns no entries from the database currently I just get the database headings displayed and an empty table. I would like to "catch" this error and return feedback to the user, asking them to try another number. Solutions so far have involved variations of the following:


while(rs.next())
{
if (rs.getString(1)==null)
{
empty=true;
}
if(empty)
{
out.println("<h2>Sorry there's nothing in the database to match</h2>");
}
else
{
//loop through resultset and print the data
}

Needless to say this doesn't work... Any ideas would be most appreciated.

Also do I have to use servlet mapping to used the Request Dispatcher if I want to include Javascript client-side validation? And has anyone any ideas how to validate whether the user has entered just numbers into a field?



This is all for my dissertation for university - many thanks in advance

Gemma

[Ben Souther]

Your first two issues could be handled a lot easier if you pull your database code out of the servlet and put it into a bean.

1.) This would allow you to add a main method for testing and edit/compile/run right from the command line (or IDE if you prefer) until you have your query returning the results that you expect.
BTW: Most RDBMs have query tools that allow you to type in ad-hoc queries and see the results.

2.) If you do all of your processing in the bean you can return a data structure that can be measured easily (collection or array). If the collection returned from the bean is empty, return a message to the user or forward them to a page that informs them that there is no data for the results they've entered.

Also do I have to use servlet mapping to used the Request Dispatcher if I want to include Javascript client-side validation?
I have no idea what that question means.
I'm guessing it was an error in typing.
Re-phrase it and I'll be happy to take a shot at it.

And has anyone any ideas how to validate whether the user has entered just numbers into a field?

 
int    someInt;
String userMessage;
try{
  someInt = Inteter.parseInt(request.getParameter("some_int"));
  userMessage = "This is a good int. Thank you.";
}catch(NumberFormatException e){
  // not an int
  userMessage = "You've entered a non-int in the Some Int field!!!";
}

Out of curiosity, what is the subject matter for you dissertation?
[ May 05, 2005: Message edited by: Ben Souther ]

[Gemma Cameron]

Thank you so much Ben!!!

The Request Dispatcher question was with regards javascript files. In other words do I need to map the servlet in order to locate the function.js file? Or can I just do everything relatively to save time.

My dissertation is due in on tuesday so I really am leaving it very late to be writing Beans...

The dissertation was actually a project given to me by a company I went on placement to. They have a database there called Change Tracker which is used to log all the defects and changes to their vehicle leasing system. All these changes require to go through a development cycle and the current system makes it very hard to analyse this data. My solution to this problem was to design several servlets which would let you search for a partuclar Change Request and view the progress in a graphical manner - this being shaded HTML tables to show whether the Analysis, Build, Test and Release stages were not started, in progress or not started. The user can then expand this stage to view details such as forecasted and actual completion dates, to whom that task it allocated and also any comments.

Thanks again for your help. Any further information would be much appreciated as I should like to develop this further after the dissertation due date to further my knowledge of servlets, JSPs and, of course, that wonderful language Java.

( :

[Ben Souther]

Originally posted by Gemma Cameron:
Thank you so much Ben!!!

The Request Dispatcher question was with regards javascript files. In other words do I need to map the servlet in order to locate the function.js file? Or can I just do everything relatively to save time.

I see.
No, you don't need a requestDispatcher for that. Just make sure the href is relative to the URL that you us to call the servlet.

<script language="javascript" src="js/functions.js"></script>


[edited to remove a foolish typo pointed out by D Rog]
[ May 08, 2005: Message edited by: Ben Souther ]

[D Rog]

Wow, I didn't know that JS files can be included using cascade style sheet syntax. I used something like:
<link rel="stylesheet" href="webappcontext/gui_styles.css" type="text/css">
for styles, and
<script src="webappcontext/errormessage.js" language="Javascript">
for JS.

[Gemma Cameron]

Thanks a lot for that both of you.

However has anyone got a "quick-fix" solution for my SELECT DISTINCT problem? I am getting pretty desperate now. Or is it just that Servlet's don't handle that SQL statement?

I tried SELECT DISTINCT Project FROM CCNs

which is used in the following as sqlProject variable:

try
{
Class.forName ( "com.mysql.jdbc.Driver" );
String url = "jdbc:mysql://localhost/changetracker";
conn = DriverManager.getConnection(url, "root", "t9JA3BTJ");
stmt = conn.createStatement();
rs = stmt.executeQuery(sqlProject);

//create drop down list of projects

out.println("<font color=\"#0000FF\">Please choose a Project<br>");
out.println("<select size=\"1\" name=\"project\">");
out.println("<option selected> </option>");

while(rs.next())
{
//modify so unique instances only
out.println("<option>"+ rs.getString(6) +"</option>");

}
out.println("</select>");
out.println("<p></p>");

} //try

catch (Exception e)
{
out.println("<font color=\"#0000FF\">Sorry there has been a problem.<br> Please report the following Error: <br>" + e + "</font>");
}



The results are that a drop down list is started, but not finished. Therefore breaking the rest of the code for that page.

I do not have time now to write a Bean, but any explanations would be most appreciated as I can explain the error and possible solutions in my dissertation.

Thanks once again!!!

Gemma

[Gemma Cameron]

It's OK

I have figured it out now.

Firstly a seperate SQL statement was required for each, and the rs.getString() had to be 1 each time as only distinct records for the specified column were being pulled - not all.

Thanks for your help guys!!!

Gemma