Issue with retrieving id from a table.

[raghu tammina]

Hi,

i am trying to select id from a table

int id=0;
   id=DBHelper.getIntResult("select id from tab_sf_std_opportunity where name='"+opt.getName()+"' and closedate='"+simpleDateFormat.format(opt.getCloseDate().getTime())+"' and productcode='"+opt.getProduct_Code__c()+"'");

In jsp page i have one button when click on it, it will check the id if it is zero then insertion will be done otherwise updates the data.

First time when i pressed button works fine if i press second time then i'm getting one of id is zero but with that combination i have the data in database.

and it is behaving in a different manner.The above record is inserting again in table.

see the attached screen shot

in img2 AP003 item exists in database but i'm getting id is zero.and AP005 item inserted once again.

please suggest me.

[Tony Docherty]

I would start by printing out the SQL string you are generating to makes sure it is correct. In particular check the date/time format as the fields may not be in the order/format the DB is expecting.

[raghu tammina]

Hi Tony,

I tested date/time format also it is giving id when i done test in backend.

[Tony Docherty]

Have you tested the exact SQL statement you are generating to see if it works?
Are you definitely getting 0 back or are you just assuming that because the item is added again?

[raghu tammina]

Yes i tested exact statement.

select id  FROM tab_sf_std_opportunity where name='Rt_test' and closedate='2014-01-21' and productcode='AP003';

i got id=108.

[Jaikiran Pai]

What does the exact code in DBHelper.getIntResult look like?

[Tony Docherty]

raghu tammina wrote:
Yes i tested exact statement.

select id  FROM tab_sf_std_opportunity where name='Rt_test' and closedate='2014-01-21' and productcode='AP003';

i got id=108.

That isn't the SQL statement generated by your code, the spacing and capitalization is wrong. I suggest you print out the generated statement and then cut and paste the output into whatever application you use to directly execute the sql.
Also you didn't answer my second question - have you tried outputting the value of ID to see if it really is 0.
Finally (and probably most importantly) do as Jaikiran has suggested and show the code for the DBHelper.getIntResult(..) method and also the code immediately following the code you have shown so we can see what you do with the returned id.

[Martin Vashko]

What I believe is happening is that the locale of the client you're using to test the query differs from the locale that your application is using. This could be true for various reasons (and I'm not going into it here, except to say that it happens). The problem then would be that date formatted in Java doesn't match the format the database expects, which leads either to errors (probably just ignored in your code) or wrong records being matched (if the database expect the date in the YYYY-DD-MM format and the day of month is less then 13, for example).

I'd suggest doing the following:

1) Modify your code to use prepared statements - this page has three very compelling reasons to do so, and the second reason is what I'm talking about. This should resolve your current problems.

2) Make sure that any exceptions in your code are not ignored. Exceptions must cause the current action to be aborted, must be shown to the user (so that the user knows something went wrong) and must be properly logged (a full stacktrace at the very very least), so that someone can actually try to nail them down when reported. This should help you resolve your future problems - there always are problems - faster.

[raghu tammina]

Hi Tony,

Here is the code for getIntResult()

public static int getIntResult(String qry, HttpSession session) {
		int result = 0;
		java.sql.Connection con = null;
		java.sql.Statement stat = null;

		java.sql.ResultSet rs = null;

		try {

			con = ConnectionString.getConnection((String) session
					.getAttribute("datasource"));
			stat = con.createStatement();

			rs = stat.executeQuery(qry);
			if (rs.next()) {
				result = rs.getInt(1);
			} else {
				result = 0;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (stat != null) {
					stat.close();
					stat = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (java.sql.SQLException e) {

			}
		}
		return result;
	}

I got id is zero i'm printing the value in stack trace.

10:54:27,068 INFO [STDOUT] cus:Rt_test

10:54:27,068 INFO [STDOUT] date:2014-01-21

10:54:27,069 INFO [STDOUT] Item:AP003

10:54:27,069 INFO [STDOUT] id::::::::::: 0

[Martin Vashko]

The output you've posted seems to contain messages sent to stdout. However, e.printStackTrace() prints the stack trace to stderr. At this point I believe that you're getting an exception for reasons I've explained in my previous post.

Your getIntResult method will return zero even when an exception occurs, which you mistakenly interpret as a nonexistent record. You should propagate the exception higher up instead, since it is clearly a situation the application is not ready to deal with. I personally would throw an unchecked exception - say, a RuntimeException - as there is no way to recover from it and the sole purpose would be to abort current processing and log the exception at the topmost level. So, instead of

e.printStackTrace();

I'd use

throw new RuntimeException(e);