 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Java Forum / General / November 2006need cursor info
Hi, i need some info on cursor. cursor == current set of records ....right ? so, it holds set of records ....how many records it can hold ? i have seen these examples http://javaalmanac.com/egs/java.sql/MoveCursor.html http://javaalmanac.com/egs/java.sql/GetCursorPosition.html But, i have not got the answers of my questions. here are the questions . How i can set , how many record a cursor can hold ? How do i retrive those records ? suppose, i have for loop and i want to get the next set of records , should i move the cursor by 1 place ? why i should use the cursor ? does it improve in any case ? i have searched the net ....not found any answers / references where i could find the answers of my questions. If anybody can answer these questions or provide me some resources/links where i could get the answers of these questions...i'll appreciate thank you > Hi, > i need some info on cursor. > cursor == current set of records ....right ? No. Wrong. > so, it holds set of records .... No it doesn't. > how many records it can hold ? A cursor isn't really a place where records are held. > i have seen these examples > http://javaalmanac.com/egs/java.sql/MoveCursor.html [quoted text clipped - 5 lines] > > How i can set , how many record a cursor can hold ? You can't because it doesn't. > How do i retrive those records ? By using ResultSet.next() and ResultSet.getXXX() on your ResultSet instance; > suppose, i have for loop and i want to get the next set of records , > should i move the cursor by 1 place ? Only if the set is of size 1. > why i should use the cursor ? Because it has great utility. > does it improve in any case ? I wonder what you mean? I suspect the answer is "yes", but the question is unfathomable. > i have searched the net ....not found any answers / references where i > could find the answers of my questions. Your Google-fu is too weak! Exercise it more! > If anybody can answer these questions or provide me some > resources/links where i could get the answers of these questions... http://en.wikipedia.org/wiki/Cursor_%28databases%29 http://java.sun.com/docs/books/tutorial/jdbc/basics/index.html > i'll appreciate Will you? I'll wait ... > > Hi, > > i need some info on cursor. > > cursor == current set of records ....right ? > > No. Wrong. ok , i found this text here http://webopedia.com/TERM/c/cursor.html it says ,cursor definition "......In some database languages, short for current set of records, the currently selected set of records........" > > so, it holds set of records .... > [quoted text clipped - 19 lines] > > By using ResultSet.next() and ResultSet.getXXX() on your ResultSet instance; ok. but these are as simple as extracting values from a ResultSet .....here is no cursor involved . > > suppose, i have for loop and i want to get the next set of records , > > should i move the cursor by 1 place ? [quoted text clipped - 4 lines] > > Because it has great utility. great utility ? > > does it improve in any case ? > [quoted text clipped - 10 lines] > > http://en.wikipedia.org/wiki/Cursor_%28databases%29 oh...ok, i see the wikipedia says , "..a cursor can be thought of as an iterator over the collection of rows in the result set...." well, but what will i get out of it ? i can do the same thing like this (without a cursor) while (rs.next()) { // Get the data from the row using the column index String s = rs.getString(1); // Get the data from the row using the column name s = rs.getString("col_string"); } this is also iterating the resultset without a cursor ...right ? so, why do i use cursor then ? what benefit cursor will give ? Yea, do you want to tell , cursor can go backwards in the resultset ? i am not sure ....or this is a benefit i'll will get from a cursor . please explain , why and when one should use cursor ? > http://java.sun.com/docs/books/tutorial/jdbc/basics/index.html > > > i'll appreciate > > Will you? I'll wait ... thank you for your response >>>How do i retrive those records ? >> >>By using ResultSet.next() and ResultSet.getXXX() on your ResultSet instance; > > ok. but these are as simple as extracting values from a ResultSet Yes. > .....here is no cursor involved . Wrong. >>>If anybody can answer these questions or provide me some >>>resources/links where i could get the answers of these questions... [quoted text clipped - 18 lines] > > this is also iterating the resultset without a cursor ...right ? so, Wrong. > why do i use cursor then ? > what benefit cursor will give ? [quoted text clipped - 3 lines] > > please explain , why and when one should use cursor ? As Patricia says, read the documentation for the API! http://javadocs.org/ResultSet The answer you seek is in the second paragraph: "A ResultSet object maintains a ... The next method moves the ..." > oh...ok, i see the wikipedia says , > [quoted text clipped - 14 lines] > this is also iterating the resultset without a cursor ...right ? so, > why do i use cursor then ? Actually this is not correct. Typically a ResultSet will use a DB cursor under the hoods to do the iteration. When using JDBC you typically do not see DB cursors. When you write stored procedures you will often use them. Kind regards robert > > oh...ok, i see the wikipedia says , > > [quoted text clipped - 23 lines] > > robert i found these , "............. A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row. It is possible to produce ResultSet objects that are scrollable and/or updatable. The following code fragment, in which con is a valid Connection object, illustrates how to make a result set that is scrollable and insensitive to updates by others, and that is updatable............ " BUT , what is the below says ? "...... to insert column values into the insert row. An updatable ResultSet object has a special row associated with it that serves as a staging area for building a row to be inserted. The following code fragment moves the cursor to the insert row, builds a three-column row, and inserts it into rs and into the data source table using the method insertRow. rs.moveToInsertRow(); // moves cursor to the insert row rs.updateString(1, "AINSWORTH"); // updates the // first column of the insert row to be AINSWORTH rs.updateInt(2,35); // updates the second column to be 35 rs.updateBoolean(3, true); // updates the third column to true rs.insertRow(); rs.moveToCurrentRow(); .......... " see, rs.moveToInsertRow() , which row ? row 1 ? row 2 ? or which row ? and also rs.moveToCurrentRow() , again whats the different between moveToInsertRow() and moveToCurrentRow(). so , this means JDBC can play with DB cursor and jump to any location in result to do transaction.......is this all about cursor ? or there are some more things a cursor can do ? hi, u will get the all the records if we have dynamic tables here.........adn ? is u sed for updating record from gui inter face. k if u have any doubts mail me......(cool4rao@gmail.com) > > > oh...ok, i see the wikipedia says , > > > [quoted text clipped - 68 lines] > > or there are some more things a cursor can do ? > rs.moveToInsertRow(); // moves cursor to the insert row > rs.updateString(1, "AINSWORTH"); // updates the [quoted text clipped - 3 lines] > rs.insertRow(); > rs.moveToCurrentRow(); This is bad practice. You should never hard-code column numbers into a program because, if you do, your program will mysteriously fail if the columns in the table are re-ordered or additional columns are added. Always explicitly name columns or use the findColumn() method. Same objection goes for the common short-hand "select * from ..." form of query. > .......... > " > > see, rs.moveToInsertRow() , which row ? row 1 ? row 2 ? or which row > ? The insert row doesn't exist before its built and written to the database so it doesn't have any position. Even when its contents have been stored the "position" of the newly created row's position in the result set is undefined but it is probably added to the end of the result set. IMHO it would unwise to write code that depends on reading it and most unwise to rely on it being in any particular position in the result set. > and also rs.moveToCurrentRow() , again whats the different between > moveToInsertRow() and > moveToCurrentRow(). The Insert row is an imaginary row which you can use to assemble a new row before you insert it into the table and result set. It is not the same as the current row. The documentation is clear on this: the current row is remembered when you use the insert row and you can return to it from the insert row. > so , this means JDBC can play with DB cursor and jump to any location > in result to do transaction.......is this all about cursor ? Correct. You can move the cursor forward, backward, to start, to end, to the 'nth' row. This sets the current row. Once a row is selected you can access or update columns in that row or you can delete the row. NOTE: that assumes you have set the result set to TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE. The default is that the cursor only moves forwards.  Signaturemartin@ | Martin Gregorie gregorie. | Essex, UK org | > > rs.moveToInsertRow(); // moves cursor to the insert row > > rs.updateString(1, "AINSWORTH"); // updates the [quoted text clipped - 53 lines] > gregorie. | Essex, UK > org | will you please explain , whats the meaning of the below.. static int TYPE_SCROLL_INSENSITIVE The constant indicating the type for a ResultSet object that is scrollable but generally not sensitive to changes made by others. i dont understand , what does they mean by "..generally not sensitive to changes made by others...." does it want to say READ ONLY resultset ? what does they want to say by made by others ? do they want to say, the resultset can not insert, update ? who are these "others" ? say, i have got a ResultSet in my program after executeQuery() , now tell me , what does the other will mean in this context ? > will you please explain , whats the meaning of the below.. > [quoted text clipped - 4 lines] > i dont understand , what does they mean by "..generally not sensitive > to changes made by others...." It means that your result set isn't updated if other users make changes to the database while you're processing your result set. > does it want to say READ ONLY resultset ? No. READ_ONLY means that you can't insert, delete, or update rows in your result set. > who are these "others" ? > say, i have got a ResultSet in my program after executeQuery() , now > tell me , what does the other will mean in this context ? It means any other database transaction (aka commit unit) run against the database while you're processing your result set. NOTE: the stuff you're asking is elementary database knowledge and not really relevant to this newsgroup. I'd strongly suggest that you read a good text on SQL databases (anything by Chris Date is good despite its age) and/or the manuals for your database. When you've done that, install an RDBMS and experiment with it by using its interactive SQL utility to get a practical feel for what you've learnt. Then you'll be in a better position to understand how JDBC works and why it works the way it does. Signaturemartin@ | Martin Gregorie gregorie. | Essex, UK org | >> rs.moveToInsertRow(); // moves cursor to the insert row >> rs.updateString(1, "AINSWORTH"); // updates the [quoted text clipped - 10 lines] > re-ordered or additional columns are added. Always explicitly name > columns or use the findColumn() method. I am sorry, but you are wrong here. Nothing will break if the table layout changes as long as you enumerate columns explicitly. Accessing columns by index is as safe as using the name and usually faster than by name. Doing so is perfectly sane since you create the query. When doing ResultSet rs = st.executeQuery("select foo, bar from tab") you always know that column 1 is "foo" and column 2 is "bar". The bad practice is this: > Same objection goes for the > common short-hand "select * from ..." form of query. No, this is a different case. "select *" is unstable, i.e. it will change layout if the schema of the queried table is changed. Btw, indexing by name and findColumn() also break if columns are renamed. Kind regards robert > I am sorry, but you are wrong here. Nothing will break if the table > layout changes as long as you enumerate columns explicitly. Accessing [quoted text clipped - 3 lines] > always know that column 1 is "foo" and column 2 is "bar". The bad > practice is this: Not entirely. Your example is, of course, correct. However, its also legal to write various equivalents to "select 1,3,5,6 from foo..." which is just as unstable as "select * from ..." and, unfortunately, equally legal SQL. The OP's code fragment is equivalent to "insert 1,2,3 values("AINSWORTH", 35, true) into foo". That may map onto a properly enumerated column list but we just can't tell from the quoted code fragment. Hence my warning. > Btw, indexing by name and findColumn() also break if columns are renamed. Of course, but at least using column names should give a more meaningful error message AND scanning the source code will at least flag up all the statements that need to be changed - which won't happen if column numbers are used. Adding and deleting columns are much more likely changes than renaming an existing column, simply because we *know* the latter causes problems. If all the column numbers still fall within the table the query using them will at best produce data conversion errors, which can waste a lot of time if the bug chaser makes wrong assumptions about the cause of the bug, and at worst will silently do incorrect value conversions and produce wrong results. Cheers, Martin Signaturemartin@ | Martin Gregorie gregorie. | Essex, UK org | >> I am sorry, but you are wrong here. Nothing will break if the table >> layout changes as long as you enumerate columns explicitly. Accessing [quoted text clipped - 8 lines] > is just as unstable as "select * from ..." and, unfortunately, equally > legal SQL. I completely forgot about *that* option. Thanks for pointing it out. That seems even less good practice to me - and I cannot remember the last time I have actually seen that. > The OP's code fragment is equivalent to "insert 1,2,3 > values("AINSWORTH", 35, true) into foo". That may map onto a properly > enumerated column list but we just can't tell from the quoted code > fragment. Hence my warning. Since we did not see the SELECT you're formally correct. But given the rareness of actually using SELECT 1,2,3 you can assume that the SELECT actually enumerated column names. And in that case it's perfectly safe to use indexes. If the select statement came from some other piece of code (i.e. not within the same method) then it might actually be better to use column names. But in that case a more robust approach should be taken anyway. Typically you would check that the columns you expect are there and throw some kind of exception if they are not because that would be a violation of the method's contract. Or you go completely meta like generic SQL tools do and just print / present / output all columns that are in the ResultSet. >> Btw, indexing by name and findColumn() also break if columns are renamed. >> > Of course, but at least using column names should give a more meaningful > error message AND scanning the source code will at least flag up all the > statements that need to be changed - which won't happen if column > numbers are used. You can still search for the table name. This seems the safer debugging technique to me since it will catch all occurrences of that table and not find places in code that access similarly named columns in other tables. > Adding and deleting columns are much more likely changes than renaming > an existing column, simply because we *know* the latter causes problems. Right. > If all the column numbers still fall within the table the query using > them will at best produce data conversion errors, which can waste a lot > of time if the bug chaser makes wrong assumptions about the cause of the > bug, and at worst will silently do incorrect value conversions and > produce wrong results. Maybe. This certainly depends on the size of the code base and the team. Thanks for the fruitful discussion! Kind regards robert > I completely forgot about *that* option. Thanks for pointing it out. > That seems even less good practice to me - and I cannot remember the > last time I have actually seen that. This is slightly OT, because is non-Java. However, as knowing about this gotcha may be useful to somebody, here goes: There are some really evil uses for these constructs (SELECT * and using column numbers) and some of these can cause really terrible performance. Some time back I was asked to find the cause of astoundingly poor performance (tens of seconds to retrieve one row from a properly indexed Sybase table with a several thousand rows in it). The application with the problem was written in MS Visual C++ and used the Microsoft Foundation Classes (MFC) to access the database via ODBC. There was nothing obviously wrong with source code apart from a "select * from...." SQL statement, but this had sensible WHERE and ORDER BY clauses which were both supported by indexes. Although the "*" was bad practice I didn't see how it could affect performance. So, I turned on ODBC tracing to see what was going on. It turned out that the MFC library code had an, ahem, idiosyncratic way of getting the column names so it could bind field buffers to them. Instead of iterating through the metadata for the table with the ODBC "get column name" function, which is well documented and fast, it did the following: 1) edited the SELECT statement, removing the WHERE and ORDER BY clauses and replacing them with "WHERE 1 = 1" 2) Ran the edited statement against the database. This returned a line containing the column names. No rows were returned. 3) The column names list was parsed and field buffers were bound to each column. 4) The original query was run and the result set fetched and processed through a cursor. Step (2) was causing the performance hit. The "WHERE 1=1" clause caused Sybase to sequentially scan the entire table before returning the column headings. Needless to say, this took 99% of the total time: when the query was run as written it was very fast. So, I rewrote the SELECT to list the columns it needed. Performance was in line with expectations and ODBC tracing showed that steps (1) to (3) were now omitted. This sort of thing is pernicious because its performance impact won't be apparent when run against the test database with its typically small data set, but *will* bite the developers as data builds up to expected volumes in the production database. Now, in Java we don't have a real equivalent for MFC, but there *is* a strong family resemblance between JDBC and ODBC, so its possible that similar gotchas could be lurking in corners of JDBC. HTH Signaturemartin@ | Martin Gregorie gregorie. | Essex, UK org | >> I completely forgot about *that* option. Thanks for pointing it out. >> That seems even less good practice to me - and I cannot remember the [quoted text clipped - 25 lines] > 1) edited the SELECT statement, removing the WHERE and ORDER BY clauses > and replacing them with "WHERE 1 = 1" I believe this must be a typo. You probably found "WHERE 1 = 0" - if not, then it's completely logical for the DB to do a FTS - actually that's the fastest way to get *all* the records. And that's what you do when writing "WHEN 1 = 1". :-) > 2) Ran the edited statement against the database. This returned a line > containing the column names. No rows were returned. [quoted text clipped - 20 lines] > strong family resemblance between JDBC and ODBC, so its possible that > similar gotchas could be lurking in corners of JDBC. Actually there are drivers with which you cannot get the column names from a table via meta data, so once I actually had to resort to SELECT * FROM table WHERE 1 = 0 in order to get the column names and types. Fortunately I did not see the effects you mention. :-) Kind regards robert > I believe this must be a typo. You probably found "WHERE 1 = 0" - if > not, then it's completely logical for the DB to do a FTS - actually > that's the fastest way to get *all* the records. And that's what you do > when writing "WHEN 1 = 1". :-) You may be right - its been a long time since I chased that one down. > Actually there are drivers with which you cannot get the column names > from a table via meta data, so once I actually had to resort to SELECT * > FROM table WHERE 1 = 0 in order to get the column names and types. > Fortunately I did not see the effects you mention. :-) Sounds like a nasty driver to me. You're lucky you didn't have any large tables to play with. Somewhat later I was working with the RedBrick data warehouse, which only supported ODBC. Our fact table contained 3 BILLION rows... Do you recall what happened when you tried using meta data? I mean, was there a recognizable error code that could have been used, e.g. by middleware developers, to decide whether the column metadata was usable or if it was necessary to do the WHERE 1 = 0 trick? Cheers, Martin Signaturemartin@ | Martin Gregorie gregorie. | Essex, UK org | >> Actually there are drivers with which you cannot get the column names >> from a table via meta data, so once I actually had to resort to SELECT [quoted text clipped - 3 lines] > Sounds like a nasty driver to me. You're lucky you didn't have any large > tables to play with. It /was/ a large table but the DB was smart enough to not do a FTS. > Somewhat later I was working with the RedBrick data > warehouse, which only supported ODBC. Our fact table contained 3 BILLION > rows... Some of our DWH's are in the same region. > Do you recall what happened when you tried using meta data? I mean, was > there a recognizable error code that could have been used, e.g. by > middleware developers, to decide whether the column metadata was usable > or if it was necessary to do the WHERE 1 = 0 trick? I checked, but I did not find any error messages documented and I can't remember. Would have to test it out again. The DB and driver were Oracle - could be that it was with Oracle 8 and a newer driver. Kind regards robert > I checked, but I did not find any error messages documented and I can't > remember. Would have to test it out again. The DB and driver were > Oracle - could be that it was with Oracle 8 and a newer driver. Noted. I wonder if this could also bite the ResultSet.findColumn() method? The reasons given for this method to throw an SQLException imply that it might. Thanks for the heads-up and checking. Martin Signaturemartin@ | Martin Gregorie gregorie. | Essex, UK org | >> I checked, but I did not find any error messages documented and I >> can't remember. Would have to test it out again. The DB and driver [quoted text clipped - 4 lines] > I wonder if this could also bite the ResultSet.findColumn() method? The > reasons given for this method to throw an SQLException imply that it might. I would guess not in that particular case because ResultSetMetaData worked. > Thanks for the heads-up and checking. You're welcome! Regards robert > Hi, > i need some info on cursor. [quoted text clipped - 5 lines] > http://javaalmanac.com/egs/java.sql/MoveCursor.html > http://javaalmanac.com/egs/java.sql/GetCursorPosition.html ... The sample code uses ResultSet, so I suggest reading its API documentation. Patricia Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.