List:General Discussion« Previous MessageNext Message »
From:murthy gandikota Date:January 3 2007 11:18pm
Subject:Re: JSP/JSTL problem
View as plain text  
Many thanks for your kind response. I was using a form that was submitted twice for any
given new ssn. I have also benefited from your comments on how to write a better java
code. Turns out my html and javascript skills need improvement. Many thanks again.
  Regards
  

David Griffiths <dgriffiths@stripped> wrote:
  
This is a little dangerous as well; if an exception gets thrown, the 
statement doesn't get closed. My sample code is below.

That said, your query looks fine. Add logging to your code to figure out 
what's going on. Run your query by hand against your database; I suspect 
you have an issue with your data.

One final note before the source code - don't use values that have 
real-world meaning as primary keys. If the format of the SSN changes, 
and you create a foreign key back to the "cust" table, you've added a 
headache. Create auto-increment primary keys that are just value-less 
numbers, and create a unique index on the ssn column.



PreparedStatement stmt = null;
ResultSet rs = null;
try
{
try
{
// Check the SSN to make sure it's a number before you start 
preparing statements
Integer iSSN = new Integer(ssn);
}
catch (NumberFormatException e)
{
out.println("The ssn is not a valid number: " + ssn);
return;
}
stmt = conn.prepareStatement("SELECT ssn, first, last FROM cust 
WHERE ssn = ?");
stmt.setInt(1, iSSN.intValue());
rs = stmt.executeQuery();
if (rs.next())
{
out.println("Customer exists: " + rs.getString(1));
}
else
{
out.println("The SSN could not be found in the database: " + id);
}
}
catch (Exception e)
{
// Log it, or whatever
}
finally
{
// The statements here will always be called, exception or no. By 
wrapping the ".close()" statements in a try-catch, you guarantee that
// each resource will have "close" called on it before returning
try
{
rs.close();
}
catch (Exception e) {}

try
{
stmt.close();
}
catch (Exception e) {}

try
{
conn.close();
}
catch (Exception e) {}
}

David

murthy gandikota wrote:
> I tried everything you suggested. 'Think it is the way I have set up 
> the table in MYSQL. This is the table 
> 
> +------------+---------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +------------+---------------+------+-----+---------+-------+
> | ssn | int(9) | | PRI | | |
> | submitdate | date | YES | | NULL | |
> | submitto | int(3) | YES | | NULL | |
> | first | varchar(30) | YES | | NULL | |
> | last | varchar(30) | YES | | NULL | |
> | loanAmt | decimal(10,2) | YES | | NULL | |
> | company | int(3) | YES | | NULL | |
> | fee | decimal(10,2) | YES | | NULL | |
> | appType | int(3) | YES | | NULL | |
> | appSource | int(3) | YES | | NULL | |
> | appStatus | int(3) | YES | | NULL | |
> | dateStatus | date | YES | | NULL | |
> | fundedAmt | decimal(10,2) | YES | | NULL | |
> +------------+---------------+------+-----+---------+-------+
>
>
> Hassan Schroeder wrote: On 1/2/07, murthy
> gandikota wrote:
>
> 
>> ps = con.prepareStatement("select first, last from cust where ssn=?");
>> int ssnint = Integer.parseInt(ssn.trim());
>> ps.setInt(1, ssnint);
>> ResultSet rs=ps.executeQuery();
>> if ( rs.next()) {
>> rs.close();
>> out.println("Customer already exists " + Integer.parseInt(ssn));
>> return;
>> }
>> 
>
> 
>> I get the message "customer already exists" for EVERY ssn that I
>> 
> tried.
>
> Not sure how you're actually running this, but it looks dangerous -- if
> rs.next() is false, you're not closing that ResultSet object. And the
> `return`
> seems pointless here. What happens if you change that 'if' to 'while',
> and
> print out the first, last, ssn results? (and for good measure change
> that
> SELECT statement to 'SELECT ssn, first, last').
>
> For comparison, here's some simple code similar to yours, which works
> exactly as expected: if "messageId" doesn't exist in the DB, it prints
> out
> the "not a valid id" message.
>
> stmt = conn.prepareStatement("SELECT messageText FROM messages WHERE
> messageId = ?");
> stmt.setInt(1, id); 
>
> rs = stmt.executeQuery();
> if ( rs == null )
> {
> out.println("null ResultSet
> ");
> // never happens :-)
> }
> if (rs.next())
> {
> out.println(rs.getString(1) + "
> ");
> }
> else
> {
> out.println("not a valid id");
> }
> rs.close();
> stmt.close();
> conn.close();
>
> HTH,
> 


 __________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
Thread
JSP/JSTL problemmurthy gandikota29 Dec
Re: JSP/JSTL problemmurthy gandikota30 Dec
Re: JSP/JSTL problemmurthy gandikota2 Jan
Re: JSP/JSTL problemmurthy gandikota3 Jan
  • Re: JSP/JSTL problemDavid Griffiths3 Jan
    • Re: JSP/JSTL problemmurthy gandikota4 Jan