I wonder if there is an efficient way to retrieve a specific row in a database (SQL), say row nr 3. The search should not be based on what information is stored in that row, I simply mean to retrieve row nr 3 regardless of what data is stored there.

/Thanks

: I wonder if there is an efficient way to retrieve a specific row in a database (SQL), say row nr 3. The search should not be based on what information is stored in that row, I simply mean to retrieve row nr 3 regardless of what data is stored there.
:
: /Thanks
:

What are you trying to get? How will you know its the 3rd row? How is it identified as such? Or will you not know (other than it was returned from the sql)?

-ray

: : I wonder if there is an efficient way to retrieve a specific row in a database (SQL), say row nr 3. The search should not be based on what information is stored in that row, I simply mean to retrieve row nr 3 regardless of what data is stored there.
: :
: : /Thanks
: :
:
: What are you trying to get? How will you know its the 3rd row? How is it identified as such? Or will you not know (other than it was returned from the sql)?
:
: -ray


: Well, the thing is in the program I am using a "cursor" to display the records in the database sequentially. After I display the first row in the database, I want to display the second row and so on. And even if the primary key numerically matches cursor, the whole thing fails of course if I delete a row in the database. Anyway, I have solved it now in a kind of stupid way, but if you know of any sophisticated way to do this, please tell me.
Sorry for not being clear in the first post :).

/
Mordien

: : : I wonder if there is an efficient way to retrieve a specific row in a database (SQL), say row nr 3. The search should not be based on what information is stored in that row, I simply mean to retrieve row nr 3 regardless of what data is stored there.
: : :
: : : /Thanks
: : :
: :
: : What are you trying to get? How will you know its the 3rd row? How is it identified as such? Or will you not know (other than it was returned from the sql)?
: :
: : -ray
:
:
: : Well, the thing is in the program I am using a "cursor" to display the records in the database sequentially. After I display the first row in the database, I want to display the second row and so on. And even if the primary key numerically matches cursor, the whole thing fails of course if I delete a row in the database. Anyway, I have solved it now in a kind of stupid way, but if you know of any sophisticated way to do this, please tell me.
: Sorry for not being clear in the first post :).
:
: /
: Mordien

It's very bad for performance to hit the database again and again and again. What you need to do is get all the data in one trip and cache it. I assume there is a reason you are doing it this way. Maybe you are using ASP and don't have a stateful connection? If this is it, you can cache a recordset object in the user's session collection. This is undesirable most of the time, but its better than opening a Connection and hitting the database over and over again. Just make sure you set the recordset to nothing (set oRS = Nothing) in the Session_OnEnd event in Global.asa.


-Ray

: : Well, the thing is in the program I am using a "cursor" to display the records in the database sequentially. After I display the first row in the database, I want to display the second row and so on. And even if the primary key numerically matches cursor, the whole thing fails of course if I delete a row in the database. Anyway, I have solved it now in a kind of stupid way, but if you know of any sophisticated way to do this, please tell me.

What program(s) are you using?


infidel

: I wonder if there is an efficient way to retrieve a specific row in a database (SQL), say row nr 3. The search should not be based on what information is stored in that row, I simply mean to retrieve row nr 3 regardless of what data is stored there.

This isn't really a valid request. Unless you specifically ORDER BY, or have a primary key that orders your data, SQL makes no guarantees that records will be returned in any particular order. "3rd row in the table" doesn't really make sense, unless you get down to the actual data files, and even then I'm not so sure.


infidel

: : I wonder if there is an efficient way to retrieve a specific row in a database (SQL), say row nr 3. The search should not be based on what information is stored in that row, I simply mean to retrieve row nr 3 regardless of what data is stored there.
:
: This isn't really a valid request. Unless you specifically ORDER BY, or have a primary key that orders your data, SQL makes no guarantees that records will be returned in any particular order. "3rd row in the table" doesn't really make sense, unless you get down to the actual data files, and even then I'm not so sure.
:

Well, in that case I am surprised that it is not a valid request. I mean this is something that has to be used quite often. I am writing the program in java and in this application there are, among other things, a forward button and a backwards button. The forward/backwards button simply means display the next/previous customer in the database, REGARDLESS of what primary key, first name, last name etc is stored there. So using a statement like: "SELECT <something> FROM <theTable> WHERE <condition>" is useless in this case.
:
: infidel
:
:

: : : I wonder if there is an efficient way to retrieve a specific row in a database (SQL), say row nr 3. The search should not be based on what information is stored in that row, I simply mean to retrieve row nr 3 regardless of what data is stored there.
: :
: : This isn't really a valid request. Unless you specifically ORDER BY, or have a primary key that orders your data, SQL makes no guarantees that records will be returned in any particular order. "3rd row in the table" doesn't really make sense, unless you get down to the actual data files, and even then I'm not so sure.
: :
:
: Well, in that case I am surprised that it is not a valid request. I mean this is something that has to be used quite often. I am writing the program in java and in this application there are, among other things, a forward button and a backwards button. The forward/backwards button simply means display the next/previous customer in the database, REGARDLESS of what primary key, first name, last name etc is stored there. So using a statement like: "SELECT <something> FROM <theTable> WHERE <condition>" is useless in this case.

forward/backward buttons mean display the next/previous record in a cursor, not "in the database". You must first retrieve data from a database before you can move through it. A simple select is not guaranteed to provide the results in a specific order unless you "order by" or have sorted indexes. If you think about it, it must be this way. Before you retreive data via a cursor, it's just raw bytes in a file/memory block. It has to be organized by the DBMS before you can move forward or backward through it.


infidel

: : : : I wonder if there is an efficient way to retrieve a specific row in a database (SQL), say row nr 3. The search should not be based on what information is stored in that row, I simply mean to retrieve row nr 3 regardless of what data is stored there.
: : :
: : : This isn't really a valid request. Unless you specifically ORDER BY, or have a primary key that orders your data, SQL makes no guarantees that records will be returned in any particular order. "3rd row in the table" doesn't really make sense, unless you get down to the actual data files, and even then I'm not so sure.
: : :
: :
: : Well, in that case I am surprised that it is not a valid request. I mean this is something that has to be used quite often. I am writing the program in java and in this application there are, among other things, a forward button and a backwards button. The forward/backwards button simply means display the next/previous customer in the database, REGARDLESS of what primary key, first name, last name etc is stored there. So using a statement like: "SELECT <something> FROM <theTable> WHERE <condition>" is useless in this case.
:
: forward/backward buttons mean display the next/previous record in a cursor, not "in the database". You must first retrieve data from a database before you can move through it. A simple select is not guaranteed to provide the results in a specific order unless you "order by" or have sorted indexes. If you think about it, it must be this way. Before you retreive data via a cursor, it's just raw bytes in a file/memory block. It has to be organized by the DBMS before you can move forward or backward through it.
:
:
: infidel
:
:

That's mostly correct. You need a cursor into set of records. This is called a recordset in dao/rdo/ado. I have to say yes, this IS VERY unusual. Navigating through a recordset is very common, but "rolling your own" cursor is NOT.

You should NEVER ask for a row of data without being able to 100% identify that row. Arbitrary requests are exactly that, no guarantees. If this is an acceptable way to operate, good luck to you.

If you can't use a cursor (directly or indirectly with a recordset), cache the data somewhere. Session state, text file, HTML hidden input tag, somewhere, anywhere.

Why can't you use some kind of data object to hold a cursor for you? This is a standard operation. It IS done all the time and almost noone is trying avoid the basic data mechanisms.

-ray

This message was edited by Mordien at 2003-5-23 4:48:33

---

: : : : : I wonder if there is an efficient way to retrieve a specific row in a database (SQL), say row nr 3. The search should not be based on what information is stored in that row, I simply mean to retrieve row nr 3 regardless of what data is stored there.
: : : :
: : : : This isn't really a valid request. Unless you specifically ORDER BY, or have a primary key that orders your data, SQL makes no guarantees that records will be returned in any particular order. "3rd row in the table" doesn't really make sense, unless you get down to the actual data files, and even then I'm not so sure.
: : : :
: : :
: : : Well, in that case I am surprised that it is not a valid request. I mean this is something that has to be used quite often. I am writing the program in java and in this application there are, among other things, a forward button and a backwards button. The forward/backwards button simply means display the next/previous customer in the database, REGARDLESS of what primary key, first name, last name etc is stored there. So using a statement like: "SELECT <something> FROM <theTable> WHERE <condition>" is useless in this case.
: :
: : forward/backward buttons mean display the next/previous record in a cursor, not "in the database". You must first retrieve data from a database before you can move through it. A simple select is not guaranteed to provide the results in a specific order unless you "order by" or have sorted indexes. If you think about it, it must be this way. Before you retreive data via a cursor, it's just raw bytes in a file/memory block. It has to be organized by the DBMS before you can move forward or backward through it.
: :
: :
: : infidel
: :
: :
:
: That's mostly correct. You need a cursor into set of records. This is called a recordset in dao/rdo/ado. I have to say yes, this IS VERY unusual. Navigating through a recordset is very common, but "rolling your own" cursor is NOT.
:
: You should NEVER ask for a row of data without being able to 100% identify that row. Arbitrary requests are exactly that, no guarantees. If this is an acceptable way to operate, good luck to you.
:
: If you can't use a cursor (directly or indirectly with a recordset), cache the data somewhere. Session state, text file, HTML hidden input tag, somewhere, anywhere.
:
: Why can't you use some kind of data object to hold a cursor for you? This is a standard operation. It IS done all the time and almost noone is trying avoid the basic data mechanisms.
:
: -ray

Alright, here is is how I solved it:

private int findPK(int position)
{
int stop = 0;
String sqlCommand = "SELECT Kundnr FROM Kundtabell";
try {
Statement s = connection.createStatement();
ResultSet rs = s.executeQuery(sqlCommand);
while(stop < position) {
stop++;
rs.next();
}
return rs.getInt(1);
}
catch(SQLException err) {
System.out.println("Fel: " + err.getMessage());
}

return 1;
}
:

/Mordien

: Alright, here is is how I solved it:
:

: private int findPK(int position)
: 	{
: 		int stop = 0;
: 		String sqlCommand = "SELECT Kundnr FROM Kundtabell";
: 		try {
: 			Statement s = connection.createStatement();
: 			ResultSet rs = s.executeQuery(sqlCommand);
: 			while(stop < position) {
: 				stop++;
: 				rs.next();
: 			}
: 			return rs.getInt(1);
: 		}
: 		catch(SQLException err) {    
: 			System.out.println("Fel: " + err.getMessage());	
: 		}
: 
: 		return 1;
: 	}

Looks like a perfectly reasonable solution, but I still must reiterate that unless your DBMS implicitly order your records via an index or some other mechanism, this is not guaranteed to return the same record every time it runs.


infidel

: : Alright, here is is how I solved it:
: :
:

: : private int findPK(int position)
: : 	{
: : 		int stop = 0;
: : 		String sqlCommand = "SELECT Kundnr FROM Kundtabell";
: : 		try {
: : 			Statement s = connection.createStatement();
: : 			ResultSet rs = s.executeQuery(sqlCommand);
: : 			while(stop < position) {
: : 				stop++;
: : 				rs.next();
: : 			}
: : 			return rs.getInt(1);
: : 		}
: : 		catch(SQLException err) {    
: : 			System.out.println("Fel: " + err.getMessage());	
: : 		}
: : 
: : 		return 1;
: : 	}
: 

:
: Looks like a perfectly reasonable solution, but I still must reiterate that unless your DBMS implicitly order your records via an index or some other mechanism, this is not guaranteed to return the same record every time it runs.
:
:
: infidel
:
:

Holy crud... This is way over the top unneccessary.

You'd be better off creating a temporary table and putting a sequential ID in a column and using that as a key to get your record.

You MUST do one of two different things. Keep an active cursor or keep a cache of the data. If you cache the data, keep a timestamp so you know if the data has been "touched." Really you could probably just cache the PK column(s) for the data in your query.

What's going to happen when your user is on rec3 for a few minutes and rec2 was deleted? When he clicks next will he get the original rec4 or the new rec4? And when the user starts giving you wacky descriptions about inconsistencies in the data what will you do? As if it wasn't hard enough to get useful info from a user, let's give them problems that are NOT REPRODUCABLE. OMG.

Of course, you can completely disregard all of this if you don't mind your app working like crap.

Sorry, I'm a little cranky that the matrix hasn't been released over here yet... I'll feel better tonight :)

-ray

: : : Alright, here is is how I solved it:
: : :
: :

: : : private int findPK(int position)
: : : 	{
: : : 		int stop = 0;
: : : 		String sqlCommand = "SELECT Kundnr FROM Kundtabell";
: : : 		try {
: : : 			Statement s = connection.createStatement();
: : : 			ResultSet rs = s.executeQuery(sqlCommand);
: : : 			while(stop < position) {
: : : 				stop++;
: : : 				rs.next();
: : : 			}
: : : 			return rs.getInt(1);
: : : 		}
: : : 		catch(SQLException err) {    
: : : 			System.out.println("Fel: " + err.getMessage());	
: : : 		}
: : : 
: : : 		return 1;
: : : 	}
: : 

: :
: : Looks like a perfectly reasonable solution, but I still must reiterate that unless your DBMS implicitly order your records via an index or some other mechanism, this is not guaranteed to return the same record every time it runs.
: :
: :
: : infidel
: :
: :
:
: Holy crud... This is way over the top unneccessary.
:
: You'd be better off creating a temporary table and putting a sequential ID in a column and using that as a key to get your record.
:
: You MUST do one of two different things. Keep an active cursor or keep a cache of the data. If you cache the data, keep a timestamp so you know if the data has been "touched." Really you could probably just cache the PK column(s) for the data in your query.
:
: What's going to happen when your user is on rec3 for a few minutes and rec2 was deleted? When he clicks next will he get the original rec4 or the new rec4? And when the user starts giving you wacky descriptions about inconsistencies in the data what will you do? As if it wasn't hard enough to get useful info from a user, let's give them problems that are NOT REPRODUCABLE. OMG.
:
: Of course, you can completely disregard all of this if you don't mind your app working like crap.
:
: Sorry, I'm a little cranky that the matrix hasn't been released over here yet... I'll feel better tonight :)
:
: -ray

Well, the database is simply a local one. Data inconsistencies has to do with simultaneous users trying to update the database. And I believe I could temporary store the PKs in a vector or something, but would it improve the performance significantly or did you suggest that only because you think elegancy == good performance? I would still have to update the buffer every time I delete or add a record.

Btw, it sucks (Matrix) :)!

/Mordien

: : : : Alright, here is is how I solved it:
: : : :
: : :

: : : : private int findPK(int position)
: : : : 	{
: : : : 		int stop = 0;
: : : : 		String sqlCommand = "SELECT Kundnr FROM Kundtabell";
: : : : 		try {
: : : : 			Statement s = connection.createStatement();
: : : : 			ResultSet rs = s.executeQuery(sqlCommand);
: : : : 			while(stop < position) {
: : : : 				stop++;
: : : : 				rs.next();
: : : : 			}
: : : : 			return rs.getInt(1);
: : : : 		}
: : : : 		catch(SQLException err) {    
: : : : 			System.out.println("Fel: " + err.getMessage());	
: : : : 		}
: : : : 
: : : : 		return 1;
: : : : 	}
: : : 

: : :
: : : Looks like a perfectly reasonable solution, but I still must reiterate that unless your DBMS implicitly order your records via an index or some other mechanism, this is not guaranteed to return the same record every time it runs.
: : :
: : :
: : : infidel
: : :
: : :
: :
: : Holy crud... This is way over the top unneccessary.
: :
: : You'd be better off creating a temporary table and putting a sequential ID in a column and using that as a key to get your record.
: :
: : You MUST do one of two different things. Keep an active cursor or keep a cache of the data. If you cache the data, keep a timestamp so you know if the data has been "touched." Really you could probably just cache the PK column(s) for the data in your query.
: :
: : What's going to happen when your user is on rec3 for a few minutes and rec2 was deleted? When he clicks next will he get the original rec4 or the new rec4? And when the user starts giving you wacky descriptions about inconsistencies in the data what will you do? As if it wasn't hard enough to get useful info from a user, let's give them problems that are NOT REPRODUCABLE. OMG.
: :
: : Of course, you can completely disregard all of this if you don't mind your app working like crap.
: :
: : Sorry, I'm a little cranky that the matrix hasn't been released over here yet... I'll feel better tonight :)
: :
: : -ray
:
: Well, the database is simply a local one. Data inconsistencies has to do with simultaneous users trying to update the database. And I believe I could temporary store the PKs in a vector or something, but would it improve the performance significantly or did you suggest that only because you think elegancy == good performance? I would still have to update the buffer every time I delete or add a record.
:
: Btw, it sucks (Matrix) :)!
:
: /Mordien
:

First let me say that my suggestions weren't an effort to inject elegance, its an effort to inject accuracy. It isn't elegant anyway. I would call this a last ditch effort to preserve data integrity which your code violates in a number of different ways. There are reasons why locks exist but I'm pretty much out of gas on this debate so I'm not going to go into it.

Second, yes, it'll perform better if you can ID the row and request it. Rather than scanning the entire set yourself. This'll be more evident at the end of the recordset. You're effectively doing a manual tablescan. This is the slowest thing that can be done by the dbms and I'm positive that it'll be slower on the client side and you're moving ALL the records to the client (even if the data is on the same machine, it needs to be moved process to process). As a bonus, you are only moving one record and you have less traffic.

I don't understand what you are doing. Anytime a programmer justifies violating data integrity because "my situation doesn't need it" is completely beyond me. All code should work properly. If you expect that your code will be so worthless that it'll never inspire others to use it... what can I say. You're probably right because that's always be a self-fulfilling prophecy. When even the programmer doesn't respect the software, noone will. Even if you are right, I'd take the 5 minutes and guarantee data integrity. I think of data integrity as being sacred and my coding efforts show it. If you don't think the same way... your's will show that as well, believe me.

I saw the matrix and "sucks?" It wasn't the greatest I've ever seen, special effects aside, but it was up there on the list. Special effects were definitely top 3. The SE were innovative and stunning but they lost points for SE looking fake/contrived. The whole idea is for SE to make you believe you are watching real events, not thinly disguised Comp Anim. Neo looked really fake quite a few times.

-Ray

: : : : : Alright, here is is how I solved it:
: : : : :
: : : :

: : : : : private int findPK(int position)
: : : : : 	{
: : : : : 		int stop = 0;
: : : : : 		String sqlCommand = "SELECT Kundnr FROM Kundtabell";
: : : : : 		try {
: : : : : 			Statement s = connection.createStatement();
: : : : : 			ResultSet rs = s.executeQuery(sqlCommand);
: : : : : 			while(stop < position) {
: : : : : 				stop++;
: : : : : 				rs.next();
: : : : : 			}
: : : : : 			return rs.getInt(1);
: : : : : 		}
: : : : : 		catch(SQLException err) {    
: : : : : 			System.out.println("Fel: " + err.getMessage());	
: : : : : 		}
: : : : : 
: : : : : 		return 1;
: : : : : 	}
: : : : 

: : : :
: : : : Looks like a perfectly reasonable solution, but I still must reiterate that unless your DBMS implicitly order your records via an index or some other mechanism, this is not guaranteed to return the same record every time it runs.
: : : :
: : : :
: : : : infidel
: : : :
: : : :
: : :
: : : Holy crud... This is way over the top unneccessary.
: : :
: : : You'd be better off creating a temporary table and putting a sequential ID in a column and using that as a key to get your record.
: : :
: : : You MUST do one of two different things. Keep an active cursor or keep a cache of the data. If you cache the data, keep a timestamp so you know if the data has been "touched." Really you could probably just cache the PK column(s) for the data in your query.
: : :
: : : What's going to happen when your user is on rec3 for a few minutes and rec2 was deleted? When he clicks next will he get the original rec4 or the new rec4? And when the user starts giving you wacky descriptions about inconsistencies in the data what will you do? As if it wasn't hard enough to get useful info from a user, let's give them problems that are NOT REPRODUCABLE. OMG.
: : :
: : : Of course, you can completely disregard all of this if you don't mind your app working like crap.
: : :
: : : Sorry, I'm a little cranky that the matrix hasn't been released over here yet... I'll feel better tonight :)
: : :
: : : -ray
: :
: : Well, the database is simply a local one. Data inconsistencies has to do with simultaneous users trying to update the database. And I believe I could temporary store the PKs in a vector or something, but would it improve the performance significantly or did you suggest that only because you think elegancy == good performance? I would still have to update the buffer every time I delete or add a record.
: :
: : Btw, it sucks (Matrix) :)!
: :
: : /Mordien
: :
:
: First let me say that my suggestions weren't an effort to inject elegance, its an effort to inject accuracy. It isn't elegant anyway. I would call this a last ditch effort to preserve data integrity which your code violates in a number of different ways. There are reasons why locks exist but I'm pretty much out of gas on this debate so I'm not going to go into it.
:
: Second, yes, it'll perform better if you can ID the row and request it. Rather than scanning the entire set yourself. This'll be more evident at the end of the recordset. You're effectively doing a manual tablescan. This is the slowest thing that can be done by the dbms and I'm positive that it'll be slower on the client side and you're moving ALL the records to the client (even if the data is on the same machine, it needs to be moved process to process). As a bonus, you are only moving one record and you have less traffic.
:
: I don't understand what you are doing. Anytime a programmer justifies violating data integrity because "my situation doesn't need it" is completely beyond me. All code should work properly. If you expect that your code will be so worthless that it'll never inspire others to use it... what can I say. You're probably right because that's always be a self-fulfilling prophecy. When even the programmer doesn't respect the software, noone will. Even if you are right, I'd take the 5 minutes and guarantee data integrity. I think of data integrity as being sacred and my coding efforts show it. If you don't think the same way... your's will show that as well, believe me.
:
: I saw the matrix and "sucks?" It wasn't the greatest I've ever seen, special effects aside, but it was up there on the list. Special effects were definitely top 3. The SE were innovative and stunning but they lost points for SE looking fake/contrived. The whole idea is for SE to make you believe you are watching real events, not thinly disguised Comp Anim. Neo looked really fake quite a few times.
:
: -Ray

Hi!
I cant believe this. This is quite some words for someone who has only a vague concept of what data integrity is. I am not violating the principle about data integrity in anyway. Data integrity "protects" the data from having null values, unreasonable values, no duplicate tuples etc.
Somehow I also get the feeling you didnt quite understand what the initial topic was all about. Anyway, the program works nicley, thank you. Im not angry or anything, but other people who responded at least tried to help me whereas you are stuck with your "coding principles" BS.

Anyway, sure Matrix has awesome special effects, but too ridiculous to be taken seriously. But K1 in las Vegas tonight, thats something!

/Mordien
:
:
:
:
:
:
:

It's not really about coding principles. If you know doing something a certain way is error prone, why not avoid it?

And you are 100% right, I am stuck on it. I have a very disturbing accuracy fetish.

Inconsistent analysis is the principle associated with the sample I gave, about reading rec3, rec deleted, where does move next go. I looked it up real quick and its not categorized under data integrity, so you're right on that one. I'll look up the others later.

But, it remains the same. Anything worth doing is worth doing right.

-Ray

: It's not really about coding principles. If you know doing something a certain way is error prone, why not avoid it?
:
: And you are 100% right, I am stuck on it. I have a very disturbing accuracy fetish.
:
: Inconsistent analysis is the principle associated with the sample I gave, about reading rec3, rec deleted, where does move next go. I looked it up real quick and its not categorized under data integrity, so you're right on that one. I'll look up the others later.
:
: But, it remains the same. Anything worth doing is worth doing right.
:
: -Ray

Fair enough! I know my solution is "home-made" and probably very ill-suitable for large databases. But you are wrong about losing track when deleting a record. If I delete row nr 3 in the database while reading it (displaying it on a small frame), the immediate row following it will "become" row nr 3 and that will be displayed instead. The "cursor" remains the same.

And besides, I dont need rec(X) to get rec(X+1) as I would in a linked list.

/Mordien


: