need cursor info

[gk]

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

 

[RedGrittyBrick]

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
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 ?

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_(databases)
http://java.sun.com/docs/books/tutorial/jdbc/basics/index.html


i'll appreciate

Will you? I'll wait ...

 

[gk]

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........"

No it doesn't.


A cursor isn't really a place where records are held.



You can't because it doesn't.



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 .

Only if the set is of size 1.



Because it has great utility.

great utility ?

I wonder what you mean? I suspect the answer is "yes", but the question
is unfathomable.



Your Google-fu is too weak! Exercise it more!



http://en.wikipedia.org/wiki/Cursor_(databases)

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



Will you? I'll wait ...

thank you for your response

 

[Patricia Shanahan]

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

....

The sample code uses ResultSet, so I suggest reading its API documentation.

Patricia

 

[Ian Wilson]

ok. but these are as simple as extracting values from a ResultSet
Yes.

.....here is no cursor involved .
Wrong.


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,
Wrong.

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 ?

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 ..."

 

[Robert Klemme]

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 ?

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

 

[gk]

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

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 ?

 

[rao]

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......([email protected])

 

[Martin Gregorie]

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();

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.

 

[gk]

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.

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.

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.

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.

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 ?

 

[Robert Klemme]

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.

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

 

[Martin Gregorie]

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...."

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.

 

[Martin Gregorie]

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:

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

 

[Robert Klemme]

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.

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.

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

 

[Martin Gregorie]

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

 

[Robert Klemme]

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"

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.

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.

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

 

[Martin Gregorie]

On 27.11.2006 13:01, Martin Gregorie wrote:
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

 

[Robert Klemme]

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

 

[Martin Gregorie]

On 28.11.2006 02:02, Martin Gregorie wrote:
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

 

[Robert Klemme]

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.

I would guess not in that particular case because ResultSetMetaData worked.

Thanks for the heads-up and checking.

You're welcome!

Regards

robert