If you iterate through all the rows in your result, of course the last row will show up on your form, having been the last one read.

Really what you say you want to do is read only the first result, that is:

And what you actually want to do might be a little different if you ever want to display more than the first result.

Everything is theoretically impossible, until it is done. ~Robert A. Heinlein

Nick de Waal
Ranch Hand

Joined: Feb 19, 2012
Posts: 42

posted Feb 21, 2012 17:29:08

0

And what you actually want to do might be a little different if you ever want to display more than the first result.

I tried changing it to if I also tried using the beforeFirst() both seem to work thank you,

I need this because when my application starts the fields need to be populated with the first record, subsequently I need to add buttons that will eg:
update, add, delete, and retrieve. So is it still a good idea to use this method or should I be trying something different.

Sounds like you want to read the data in your database into objects and then manipulate those objects.

Example you'd have an Employee object that you could create by reading a row from your sql query. You could have a list of them in memory so that if the user tries to go to another record you don't have to communicate with the database. And each employee could know how to update/add itself to the database, or you could have a utility class that knows how to update Employees to the database.

Nick de Waal
Ranch Hand

Joined: Feb 19, 2012
Posts: 42

posted Feb 21, 2012 17:47:49

0

Thank you Tina,

This works:

I also tried this :

But you're right it throws this exception "java.sql.SQLException: Result set type is TYPE_FORWARD_ONLY"
it does still populate the fields but the if(rs.next()) seems to be the proper way.

@Sonny: SQL is absolutely awful about standardization; and while Manoj's technique is what I'd try myself, different databases may well have different ways of doing it (I seem to remember that the equivalent on SQL Server is ROW_NUMBER; in Progress it's 'SELECT FIRST' (or possibly 'FIRST(*)', I forget...)).

However, that said, his suggestion will certainly involve the least amount of traffic over the network.

Winston

Isn't it funny how there's always time and money enough to do it WRONG?
Articles by Winston can be found here

Thanks Winston for the correction, I forgot to mention that this is database specific.
However if I need to get the first row I will always use this query as databases are supposed to handle the data, so let them handle and get only what you required.
This will also reduce the traffic and processing of data..

That's not supported by Access, MS SQL Server or MySQL. Unfortunately, there is no uniform way of doing this. Access and MS SQL Server use TOP X, whereas MySQL uses LIMIT X:
java.sql.Statement does have method setMaxRows but I don't know if this translates to a similar request to the database itself, or if the driver itself cuts of any results it doesn't want.

Rob Spoor wrote:That's not supported by Access, MS SQL Server or MySQL. Unfortunately, there is no uniform way of doing this. Access and MS SQL Server use TOP X, whereas MySQL uses LIMIT X:
java.sql.Statement does have method setMaxRows but I don't know if this translates to a similar request to the database itself, or if the driver itself cuts of any results it doesn't want.

Thanks Rob, TOP 1 seems to work. Would there be an equivalent statement for if I wanted the last record? because my next step is to clear the text fields by pressing a (new) button and populating the EmpNo textfield with the next new record,
so if the last record in the table's Emp ID field is 103 I need 104 to show up.
sorry but we have not covered sql in detail we are just being introduced to JDBC.

That would require the field to be integer / numeric already, or 22 would come after 111. You can then also use rs.getInt, but keep in mind that you still need to call next():
If there are no results yet the MAX will return NULL, which will cause getInt to return 0.

Also, about getting the first - unless you sort the results manually no order is guaranteed. You need to add an ORDER BY Emp_ID clause.

Nick de Waal
Ranch Hand

Joined: Feb 19, 2012
Posts: 42

posted Feb 22, 2012 15:59:16

0

Thank you very much.

this is the code (see below)I ended up with and it seems to working fine so thank you all.

Most databases have some sort of mechanism to provide default identity values for columns in new records: in Oracle and few others, there are sequences, in other databases, there are identity columns which get automatically populated. Though your solution generally works, it might take more resources than needed and (more importantly) might fail (generate the same value for new record) if two users try to add a record at the same time -- at least in some databases.

Unfortunately, this mechanism is not standardized across databases and I don't know about a mechanism in JDBC you could use that would work in all databases. Some ORM frameworks (eg. Hibernate) solve these differences internally and you don'T have to care about the exact mechanism of generating IDs for new records. (I, personally, have always coded for a specific database so far, so I'm free of this kind of issues, but not everyone is so lucky.)

If you can limit yourself to a family of databases which provide identity columns, such as MS SQL Server, MS Access or MySQL (I hope), it might be best to declare the column as such and use this feature. This wouldn't work on Oracle and some others (PostgreSQL and DB2 among others, though I don't know for sure).

Also, pay close attention to what Rob Spoor said about the order by clause regarding your first question here. Database is free to return you rows in any order, unless you specify the order you want using ORDER BY clause. So the first returned row is not necessarily the one with least Emp_ID, but furthermore, separate executions of the same query might return different "first" records! (Depending on your database and other circumstances.)

Nick de Waal
Ranch Hand

Joined: Feb 19, 2012
Posts: 42

posted Feb 23, 2012 00:09:42

0

Thank you for the interesting facts very helpful information indeed for a beginner like myself.
yes, regarding my first question I took Rob's advice

here is what I am using (it works so I hope you guys don't pick it apart ) just joking, I find this forum very helpful because I believe the course I am doing is very general and really just glancing over the things I need to learn.
But thankfully I can come here in my own time and learn from the masters.

Martin Vajsar wrote:Most databases have some sort of mechanism to provide default identity values for columns in new records: in Oracle and few others, there are sequences, in other databases, there are identity columns which get automatically populated. Though your solution generally works, it might take more resources than needed and (more importantly) might fail (generate the same value for new record) if two users try to add a record at the same time -- at least in some databases.

While that is true, it does have one drawback - the ID of the new record will not be available until the record is actually inserted into the database. If you need it before that time you have to resort to other techniques like this.

Two frameworks I've worked with both use the same following technique for this. They have a table with two columns, one for the table name and one for the next / last ID (that's where the two differ). A stored procedure is used to retrieve the next ID for a table, incrementing the value in the database for a next call to the stored procedure. This does lead to IDs being discarded if a record is not committed in the end, but that's better than possibly using the same ID twice.

Rob Spoor wrote:While that is true, it does have one drawback - the ID of the new record will not be available until the record is actually inserted into the database. If you need it before that time you have to resort to other techniques like this.

In my opinion, you seldom need the ID before creating the record. If you need the ID for a foreign key, you're going to insert parent record first anyway. And you generally cannot publish the ID to the "outside world" before you commit the new record in the database, because otherwise you risk the transaction will fail, rendering the ID you've published invalid. If you allow that to happen, you've exposed yourself to a whole new class of inconsistencies the database is generally expected to prevent from happening.

Two frameworks I've worked with both use the same following technique for this. They have a table with two columns, one for the table name and one for the next / last ID (that's where the two differ). A stored procedure is used to retrieve the next ID for a table, incrementing the value in the database for a next call to the stored procedure. This does lead to IDs being discarded if a record is not committed in the end, but that's better than possibly using the same ID twice.

This solution does not work in databases where writes do not block reads (eg. Oracle and other multiversioning databases). In the end you'd have to use database specific solution involving locking or autonomous transactions anyway, using good knowledge of the database locking and concurrency mechanisms to have it really right. In other words, the implementation has to be different in different databases, and therefore it might be easier to just use the proper mechanism provided by the database to generate IDs.

That said, it is really a pity that JDBC does not abstract from these differences between databases to hide them from the programmer somehow. But since additional objects may be required in the database for such a solution (eg. a sequence or a reserved table with identity column), it might well be outright impossible to do that.

this populates the text-fields but with the last record of the table, I need it to populate with the first but I am unsure as to how.

Any help would be appreciated even its just a hint.

Thank you

and this isn't important now but doing a select * then DOB = rs.getString(1) style of select isn't very safe, you should either list the column in order in your select statement or use the ResultTet.get(String columnName) method .

Tim Moores wrote:Also be aware that SQL has no concept of a "first record" in a table - records are intrinsically unordered. The same SELECT query may return results in different order each time it is called.

Agreed, but I've never understood why SQL doesn't intrinsically support the first row (or n rows) of a result. It seems to me a perfectly reasonable data-directed request.

Mind you, I've never liked it. Probably why I found being a DBA so frustrating.

Tim Moores wrote:Also be aware that SQL has no concept of a "first record" in a table - records are intrinsically unordered. The same SELECT query may return results in different order each time it is called.

Agreed, but I've never understood why SQL doesn't intrinsically support the first row (or n rows) of a result. It seems to me a perfectly reasonable data-directed request.

Mind you, I've never liked it. Probably why I found being a DBA so frustrating.

Winston

I was always told it would be fine, unless the dba had to play with the database in the night, recreating tables, that was why you should never rely on column or row order.

Wendy Gibbons wrote:I was always told it would be fine, unless the dba had to play with the database in the night, recreating tables, that was why you should never rely on column or row order.

True, but I was speaking specifically of the "SELECT FIRST" or "SELECT...TOP" construct. As far as I know, it isn't part of the standard SQL syntax (which is probably why it has so many different implementations), when as far as I'm concerned it's perfectly valid data request:
"give me the first row that matches these criteria, and I don't much care which one it is" Furthermore, it's the sort of request that can only be optimized by the database.

Mind you, as I recall, "SELECT DISTINCT" wasn't part of the original spec either, until (I suspect) a few programmers suggested that it might be worth making the language usable as well as theoretically sound.

Hi,
I hope I'm not hijacking the original thread by posting this question but I have a couple of questions regarding the order of records retrieved from DB.
If i insert 5 rows in a table in a particular alphabetical order (A,B,C,D,E) and after some days query it with a select * , wouldn't it return in the same order i Inserted them ?
Atleast thats what I thought it would. But this statement suggests otherwise

The same SELECT query may return results in different order each time it is called.

From this line

I was always told it would be fine, unless the dba had to play with the database in the night, recreating tables, that was why you should never rely on column or row order.

I understand that unless there is some change the rows will be retrieved in the order they were inserted, am i correct ?

The reason I'm surprised is- we have a set of questions displayed to the user and the questions are stored in the DB. In our code we retrieve the questions and just display it directly without any order by and the order of the questions are the same on the screen and DB. Its been like that for all users always.

Anand Athinarayanan wrote:Hi,
I hope I'm not hijacking the original thread by posting this question but I have a couple of questions regarding the order of records retrieved from DB.
If i insert 5 rows in a table in a particular alphabetical order (A,B,C,D,E) and after some days query it with a select * , wouldn't it return in the same order i Inserted them ?
Atleast thats what I thought it would. But this statement suggests otherwise

The same SELECT query may return results in different order each time it is called.

From this line

I was always told it would be fine, unless the dba had to play with the database in the night, recreating tables, that was why you should never rely on column or row order.

I understand that unless there is some change the rows will be retrieved in the order they were inserted, am i correct ?

The reason I'm surprised is- we have a set of questions displayed to the user and the questions are stored in the DB. In our code we retrieve the questions and just display it directly without any order by and the order of the questions are the same on the screen and DB. Its been like that for all users always.

No, there is no guarantee that the data will be returned in a specific order in future, even if it seems to do so right now, because things can change in the background (DBA actions, export/import, indexing, moving tablespaces etc). If you want the data to bre returned in a specific order for your application, the way to do this is to use the "ORDER BY..." clause in your SQL. That's what it's there for.