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 <[EMAIL PROTECTED]> 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
