• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Why select statement always return the last inserted values?

 
John Francis Ochotorina
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When I try to Select a record using Prepared Statement it always giving me a last inserted values that I recently add.

First what did I do is to search a record in my first table. If the record is exist the foreign key table will populate the values. My Primary and Foreign Key tables works well. The values populating appropriately to their corresponding components but it's not giving me the right values. Any help?

1stTable

2ndTable

Select Query:



So what did I do here is join the SECTION_NAME column to Foreign Key table using Right Join. If the record exist it will join the two tables.

Code:



As you can see here. In my first ResultSet myFirstRs when I search a existing SECTION_NAME the foreign key values will populate. If something something in my loop correct me. Thanks in advanced!
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Without an ORDER BY clause, the database will just return what it wants, which may just be what's currently stored in the cache.

As an aside, the query uses SELECT *, which really is best for EXISTS() and ad hoc queries. In normal code, spelling out the column names is self-documenting, and good protection against column changes.
 
John Francis Ochotorina
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:Without an ORDER BY clause, the database will just return what it wants, which may just be what's currently stored in the cache.

As an aside, the query uses SELECT *, which really is best for EXISTS() and ad hoc queries. In normal code, spelling out the column names is self-documenting, and good protection against column changes.


Hi! Thanks for responding. I tried to add in my query the ORDER BY.


When I run this to the query it's giving me the correct ascending value. But when I run the program still giving me the last inserted
Here is the screenshot.
Screenshot_1.png
[Thumbnail for Screenshot_1.png]
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
John Francis Ochotorina wrote:Hi! Thanks for responding. I tried to add in my query the ORDER BY.
When I run this to the query it's giving me the correct ascending value. But when I run the program still giving me the last inserted

searchSECTIONSETTINGS has no parameters, though, i am confused by the ticks (`SECTION_ID`). I am not sure what those are for.

Are you saying that mySecondPs.executeQuery() only returns one record, and mySecondRs.next() returns false the after it?
 
John Francis Ochotorina
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:
John Francis Ochotorina wrote:Hi! Thanks for responding. I tried to add in my query the ORDER BY.
When I run this to the query it's giving me the correct ascending value. But when I run the program still giving me the last inserted

searchSECTIONSETTINGS has no parameters, though, i am confused by the ticks (`SECTION_ID`). I am not sure what those are for.

Are you saying that mySecondPs.executeQuery() only returns one record, and mySecondRs.next() returns false the after it?


The ticks that your saying is when you hit (CTRL + Space) the help in the NetBeans compiler gives you already the value you want to get.

My ResultSet returning false and giving me a last values in my database. Yes only returns one record. Is there something wrong in my condition? Thanks for responding
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
John Francis Ochotorina wrote:My ResultSet returning false and giving me a last values in my database. Yes only returns one record. Thanks for responding

So, the same exact query returns a different number of records depending on it being run from your code or from the database itself? That seems strange.

One idea is that there are uncommitted records in the current database session, but not viewable from the java code. To remedy that, issue a
Another idea is the code is using a different database. Can you verify the code-added record is viewable in the database session?
 
John Francis Ochotorina
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:
John Francis Ochotorina wrote:My ResultSet returning false and giving me a last values in my database. Yes only returns one record. Thanks for responding

So, the same exact query returns a different number of records depending on it being run from your code or from the database itself? That seems strange.

One idea is that there are uncommitted records in the current database session, but not viewable from the java code. To remedy that, issue a
Another idea is the code is using a different database. Can you verify the code-added record is viewable in the database session?


I tried to add in my code. First I set to
before I will retrieve using ResultSet I put the before try block. Correct me If I'm doing it wrong. Thanks bro.

 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Autocommit is fine, i guess. This isn't a comment on the code inasmuch as it is a comment on the database sessions. We need to verify the same data is visible in both sessions. (The database connection where you run the query and see all the data, and the java code session, created when running the code.) And while we're at it, that both are connected to the same database.
 
John Francis Ochotorina
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:Autocommit is fine, i guess. This isn't a comment on the code inasmuch as it is a comment on the database sessions. We need to verify the same data is visible in both sessions. (The database connection where you run the query and see all the data, and the java code session, created when running the code.) And while we're at it, that both are connected to the same database.


I post the full code here. Btw I'm using mysql connector 5.1.38

Running Query in NetBeans for checking:



After running this. Output below.

Output of Query:
OUTPUT

INSERT QUERY:


SELECT Query:



OUTPUT BELOW:

Screenshot_2.png
[Thumbnail for Screenshot_2.png]
Screenshot_3.png
[Thumbnail for Screenshot_3.png]
This is the values that I last inserted.
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I do not know what is wrong, and my Java knowledge is limited. I can only suggest debugging.

If you do not do the insert, does a record come up?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I guess that the problem lies here:

John Francis Ochotorina wrote:


This loop goes through all records in the resultset, and calls setSelectedItem on individual combo boxes. Note that setSelectedItem doesn't add new item into the combo box's list, it just sets the text in the combo box.

Let's say that there are N records returned by your query. On the first execution of the body of the while loop, the combo boxes are set up with the texts contained in the first record. On the second execution, the cmbo boxes are set up with the texts contained in the second record - setSelectedItem overwrites what was there before. And so on for the third, fourth, ... Nth record.

The loop terminates when all records are processed, leaving your combo boxes set to the contents of the last record. For simple SELECT queries, databases sometimes (not always) return rows in the order in which they were inserted to the database - therefore you always end up with values from the last record in your combo boxes. Note: if you want to obtain rows in a specific order, always use the ORDER BY clause to specify that order.

If you want your combo boxes to contain all of the records returned by the query, use the addItem or insertItem method - see Javadoc.
 
John Francis Ochotorina
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Martin Vajsar wrote:I guess that the problem lies here:

John Francis Ochotorina wrote:


This loop goes through all records in the resultset, and calls setSelectedItem on individual combo boxes. Note that setSelectedItem doesn't add new item into the combo box's list, it just sets the text in the combo box.

Let's say that there are N records returned by your query. On the first execution of the body of the while loop, the combo boxes are set up with the texts contained in the first record. On the second execution, the cmbo boxes are set up with the texts contained in the second record - setSelectedItem overwrites what was there before. And so on for the third, fourth, ... Nth record.

The loop terminates when all records are processed, leaving your combo boxes set to the contents of the last record. For simple SELECT queries, databases sometimes (not always) return rows in the order in which they were inserted to the database - therefore you always end up with values from the last record in your combo boxes. Note: if you want to obtain rows in a specific order, always use the ORDER BY clause to specify that order.

If you want your combo boxes to contain all of the records returned by the query, use the addItem or insertItem method - see Javadoc.


Thanks for responding! I put already the ORDER BY clause in my select query when I run this to the NetBeans compiler. It showing a ASC value which is correct. I tried the method that you prefer the when I used the addItem the only changes is the textfield and comboboxes become enabled. 2nd Option is the when I run the project when I clicked the combobox it's retrieving the value that I inserted in the database.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
John Francis Ochotorina wrote:When I try to Select a record using Prepared Statement it always giving me a last inserted values that I recently add.

First what did I do is to search a record in my first table. If the record is exist the foreign key table will populate the values. My Primary and Foreign Key tables works well. The values populating appropriately to their corresponding components but it's not giving me the right values. Any help?

You should always explain first what you are trying to achieve. And then you can share code snippets about how you have implemented it. Currently I am and have no clue about what you are trying to achieve. The code snippets you have posted and the screenshots you have shared seem to contradict each other.

So you have a query searchSECTIONNAME which seems to be a prepared statement (which is great ) and searches a table based on the section name. If I look at your code, you search for a section using the value provided in the Section_SearchSection_Textfield. Then you get the section name from this result set to fill up the Section_SectionName_TextField in your GUI. And then you execute your second query searchSECTIONSETTINGS. But this query is not parametrized at all! So you will always return all records from table allsections_settings and not only those from the section you have just searched for (and what seems to be expected, why would you otherwise let the user search first for a section ).

Then it seems you are expecting more than one record as you are using a while loop to iterate through the result set, but you are displaying the results in a bunch of seperate combo boxes. And in my opinion that makes no sense at all! And updating a record will be very, very, very hard for a user. If you want to display multiple records from a search, you would expect to see a JTable with different columns. If you want to update a record, you could double click on that record (or click on an Edit button) and then you could have a seperate update dialog (or if you are really brave an editable JTable).

Finally I have two remarks about your code quality:
1/ don't use indexes when retrieving results from your result set. If you are re-arranging your columns in the SELECT clause of your query, you'll introduce hard to find bugs
2/ try to follow Oracle's code conventions and don't use underscores in variable names (only use them for constants)

John Francis Ochotorina wrote:As you can see here. In my first ResultSet myFirstRs when I search a existing SECTION_NAME the foreign key values will populate.

Honestly I don't see at all where the foreign key values of your first query are populated. If you completely remove everything related to the first query and result set, your second query and result set will produce exactly the same outcome!

Hope it helps!
Kind regards,
Roel
 
John Francis Ochotorina
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
John Francis Ochotorina wrote:When I try to Select a record using Prepared Statement it always giving me a last inserted values that I recently add.

First what did I do is to search a record in my first table. If the record is exist the foreign key table will populate the values. My Primary and Foreign Key tables works well. The values populating appropriately to their corresponding components but it's not giving me the right values. Any help?

You should always explain first what you are trying to achieve. And then you can share code snippets about how you have implemented it. Currently I am and have no clue about what you are trying to achieve. The code snippets you have posted and the screenshots you have shared seem to contradict each other.

So you have a query searchSECTIONNAME which seems to be a prepared statement (which is great ) and searches a table based on the section name. If I look at your code, you search for a section using the value provided in the Section_SearchSection_Textfield. Then you get the section name from this result set to fill up the Section_SectionName_TextField in your GUI. And then you execute your second query searchSECTIONSETTINGS. But this query is not parametrized at all! So you will always return all records from table allsections_settings and not only those from the section you have just searched for (and what seems to be expected, why would you otherwise let the user search first for a section ).

Then it seems you are expecting more than one record as you are using a while loop to iterate through the result set, but you are displaying the results in a bunch of seperate combo boxes. And in my opinion that makes no sense at all! And updating a record will be very, very, very hard for a user. If you want to display multiple records from a search, you would expect to see a JTable with different columns. If you want to update a record, you could double click on that record (or click on an Edit button) and then you could have a seperate update dialog (or if you are really brave an editable JTable).

Finally I have two remarks about your code quality:
1/ don't use indexes when retrieving results from your result set. If you are re-arranging your columns in the SELECT clause of your query, you'll introduce hard to find bugs
2/ try to follow Oracle's code conventions and don't use underscores in variable names (only use them for constants)

John Francis Ochotorina wrote:As you can see here. In my first ResultSet myFirstRs when I search a existing SECTION_NAME the foreign key values will populate.

Honestly I don't see at all where the foreign key values of your first query are populated. If you completely remove everything related to the first query and result set, your second query and result set will produce exactly the same outcome!

Hope it helps!
Kind regards,
Roel


Thanks for responding and tips! So I follow what you told to me. I create a JTable where the records go after searching. I used rsxml.jar in this table which I found on youtube. My question is when I search a existing record in my Primary Key table, I want the referencing table which is Foreign Key table will populate the values of the `Section_Name` that I select. How can I determine if I'm getting the right values? If there's something wrong with my code or condition? Screenshot below.



Screenshot_4.png
[Thumbnail for Screenshot_4.png]
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
John Francis Ochotorina wrote:My question is when I search a existing record in my Primary Key table, I want the referencing table which is Foreign Key table will populate the values of the `Section_Name` that I select. How can I determine if I'm getting the right values? If there's something wrong with my code or condition?

Could you explain in detail what you are trying to achieve (preferably with some sample data)? Because I'm a bit unclear about your database schema and what you are exactly trying to do. That's why you need to share your database schema (together with some sample data and the desired result of your query).

There is no such thing as a primary key table, because in a RDBMS any table should have a primary key. Because a primary key is the unique identifier for each row.

If you have two tables A and B, and table B is referring to table A. And you want to select records from B for a given column value of A, you can do this using a single query. Something like

Hope it helps!
Kind regards,
Roel
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:There is no such thing as a primary key table, because in a RDBMS any table should have a primary key.

A couple side points. (I love those. )

While it is normal to have a primary key, it is most certainly not required. Staging and log tables do not require them (and probably shouldn't have them) as well as external tables.

Roel De Nijs wrote:Because a primary key is the unique identifier for each row.

You ought to remove the word "the" from that statement. Databases tend to have row ids that uniquely identify each row. Unique Keys force data to be unique, and can therefore also be used. Well, unless the RDBMS allows more than one NULL in a unique key. (Because keys are enforced via indexes, it depends if the rdbms stores nulls on the index. Oracle does not, SQL Server does. Therefore, Oracle has no issues with multiple nulls in a unique key, where SQL Server does have an issue with it. However, if the column is also not null, it isn't a issue.)

A primary key is just a not null unique key that is used as a default key in some areas. In any case, it is most certainly not required (though strongly recommended).
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:A couple side points.

Have a cow for those lovely side points!

Brian Tkatch wrote:While it is normal to have a primary key, it is most certainly not required.

That's why I used "should" and not "must"

Brian Tkatch wrote:You ought to remove the word "the" from that statement.

True! I should have used "a" instead of "the" in that statement and I would have been spot-on. That's a rookie mistake!
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
Brian Tkatch wrote:A couple side points.

Have a cow for those lovely side points!

Moo!

Roel De Nijs wrote:
Brian Tkatch wrote:While it is normal to have a primary key, it is most certainly not required.

That's why I used "should" and not "must"

Though, i would further change "should" to "should probably" or "usually should." Should on its own seems likely always, which is not the case.
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:or "usually should." Should on its own seems likely always, which is not the case.


Well, in Java it would be "likely always", especially with ORM frameworks.
They get a bit grumpy without them.
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:
Brian Tkatch wrote:or "usually should." Should on its own seems likely always, which is not the case.


Well, in Java it would be "likely always", especially with ORM frameworks.
They get a bit grumpy without them.

I hate frameworks. TopLink is also stupid and evil.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:Though, i would further change "should" to "should probably" or "usually should." Should on its own seems likely always, which is not the case.

In my vocabulary (and understanding) of the English language (as a non-native English speaker who scored poorly on English during secondary school ), "should" doesn't imply "always". If I want to express "always", I would use "must" or "is required" or explicitly mention "always" in the sentence. But according to Google Translate, my understanding is wrong indeed and therefore I have to be careful when using the word "should" Probably "may" or "might" would have been a better choice of words in that sentence. Regarding a mock question in one of the study guides, there was a similar (fairly lengthy) discussion about the difference between "allowed" and "required".
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
Brian Tkatch wrote:Though, i would further change "should" to "should probably" or "usually should." Should on its own seems likely always, which is not the case.

In my vocabulary (and understanding) of the English language (as a non-native English speaker who scored poorly on English during secondary school ), "should" doesn't imply "always". If I want to express "always", I would use "must" or "is required" or explicitly mention "always" in the sentence. But according to Google Translate, my understanding is wrong indeed and therefore I have to be careful when using the word "should" Probably "may" or "might" would have been a better choice of words in that sentence. Regarding a mock question in one of the study guides, there was a similar (fairly lengthy) discussion about the difference between "allowed" and "required".

Heh. In standard speech, "should" is a strong recommendation. "should probably" lessens that a bit, and "usually should" says it does not always apply. In technical documentation, at least in RFCs, they have specific definitions.

In any case, i meant my comment with a bit of humor. Rules are rarely absolute, and when one is said, i often try to find the exception. In this case it was easy.

"Every rule has an exception, except this one."
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Brian Tkatch wrote:
Dave Tolls wrote:
Well, in Java it would be "likely always", especially with ORM frameworks.
They get a bit grumpy without them.

I hate frameworks. TopLink is also stupid and evil.


The likes of Hibernate have, by and large (and with the odd caveat) made my life a lot easier when it comes to your standard web app.
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:
Brian Tkatch wrote:
Dave Tolls wrote:
Well, in Java it would be "likely always", especially with ORM frameworks.
They get a bit grumpy without them.

I hate frameworks. TopLink is also stupid and evil.


The likes of Hibernate have, by and large (and with the odd caveat) made my life a lot easier when it comes to your standard web app.

There's certainly a place for them. Then again, some people actually like driving automatic transmissions.

When the framework removes functionality or demands this or that be done, i often question its usefulness.
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Possibly, but then I don't remember producing Oracle dbs that didn't have PKs on all their tables, so maybe it was less of a stretch for me.
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:Possibly, but then I don't remember producing Oracle dbs that didn't have PKs on all their tables, so maybe it was less of a stretch for me.

Heh, not complaining. In most cases, the tables do have PKs. I just wanted to point out that there are a common, legitimate cases where a PK is not a good idea. It's good to keep that in the back of your mind so you don't blindly add things that are not needed. And, when a mistake is made, a valuable lesson is learned.
 
Tim Holloway
Saloon Keeper
Posts: 18367
56
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm late to the party, but here are some observations.

No, as others have stated, a SELECT doesn't always return the last inserted values. In FoxPro, for example, it would have returned the FIRST inserted values, lacking an explicit ORDER BY. The actual order is not guaranteed unless you define it. At one time, I strongly suspected that the PostgreSQL DBMS was either returning totally random selections that differed on each SELECT or at least were in the order of the last ordering operation that had been done. In other words, entirely unpredictable in a multi-user environment.

ORMs benefit by primary keys, but what they need even more than a primary key is uniqueness. If two Entity records have the same hashCode and compare the same with equals() - which implies having the same hash, they are considered to be the same record. A primary key is just a much easier way of assuring uniqueness.

And finally, be careful with ROWIDS. Not all DBMS's support such things and not all ROWIDs are simple integers. Specifically, the Oracle ROWID is a 3-part construct which describes where, physically, within the database the record is.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic