aspose file tools*
The moose likes JDBC and the fly likes Issue with retrieving id from a table.  Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Issue with retrieving id from a table.  " Watch "Issue with retrieving id from a table.  " New topic
Author

Issue with retrieving id from a table.

raghu tammina
Ranch Hand

Joined: Jul 11, 2012
Posts: 60
Hi,

i am trying to select id from a table



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.


[Thumbnail for img3.png]

Tony Docherty
Bartender

Joined: Aug 07, 2007
Posts: 2316
    
  49
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
Ranch Hand

Joined: Jul 11, 2012
Posts: 60
Hi Tony,

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

Joined: Aug 07, 2007
Posts: 2316
    
  49
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
Ranch Hand

Joined: Jul 11, 2012
Posts: 60

Yes i tested exact statement.


i got id=108.
Jaikiran Pai
Marshal

Joined: Jul 20, 2005
Posts: 10210
    
166

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


[My Blog] [JavaRanch Journal]
Tony Docherty
Bartender

Joined: Aug 07, 2007
Posts: 2316
    
  49
raghu tammina wrote:
Yes i tested exact statement.


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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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
Ranch Hand

Joined: Jul 11, 2012
Posts: 60

Hi Tony,



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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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 I'd use
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Issue with retrieving id from a table.