Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In with Facebook Sign In with Google Sign In with OpenID

Categories

We have migrated to a new platform! Please note that you will need to reset your password to log in (your credentials are still in-tact though). Please contact lee@programmersheaven.com if you have questions.
Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Rerieving a specific row, please help...

MordienMordien Posts: 34Member
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
«1

Comments

  • raymcdraymcd Posts: 284Member
    : 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
  • MordienMordien Posts: 34Member
    : : 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

  • raymcdraymcd Posts: 284Member
    : : : 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
  • infidelinfidel Posts: 2,900Member
    : 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.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

  • infidelinfidel Posts: 2,900Member
    : : 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?


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

  • MordienMordien Posts: 34Member
    : : 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 FROM WHERE " is useless in this case.
    :
    : [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
    :
    :

  • infidelinfidel Posts: 2,900Member
    : : : 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 FROM WHERE " is useless in this case.

    forward/backward buttons mean display the next/previous record [italic]in a cursor[/italic], 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.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

  • raymcdraymcd Posts: 284Member
    : : : : 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 FROM WHERE " is useless in this case.
    :
    : forward/backward buttons mean display the next/previous record [italic]in a cursor[/italic], 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.
    :
    :
    : [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
    :
    :

    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
  • MordienMordien Posts: 34Member
    [b][red]This message was edited by Mordien at 2003-5-23 4:48:33[/red][/b][hr]
    : : : : : 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 FROM WHERE " is useless in this case.
    : :
    : : forward/backward buttons mean display the next/previous record [italic]in a cursor[/italic], 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.
    : :
    : :
    : : [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]
    : :
    : :
    :
    : 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


  • infidelinfidel Posts: 2,900Member
    : Alright, here is is how I solved it:
    :
    [code]
    : 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;
    : }
    [/code]

    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.


    [size=5][italic][blue][RED]i[/RED]nfidel[/blue][/italic][/size]

«1
Sign In or Register to comment.