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 <[EMAIL PROTECTED]> 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,
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
