• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Bear Bibeault
  • Knute Snortum
  • Liutauras Vilda
Sheriffs:
  • Tim Cooke
  • Devaka Cooray
  • Paul Clapham
Saloon Keepers:
  • Tim Moores
  • Frits Walraven
  • Ron McLeod
  • Ganesh Patekar
  • salvin francis
Bartenders:
  • Tim Holloway
  • Carey Brown
  • Stephan van Hulst

Updating a column in a table  RSS feed

 
Ranch Hand
Posts: 181
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi

I am attempting to update the Occurances column in the PROTOTYPES table



Error: Cursor 'MY CURSOR' not found

Am I still on the required record ?

Bob M
 
Bartender
Posts: 20125
103
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SQL is not a procedural language. When you say "update table_x set column_y = ?", it's doing a vertical operation on the column. There is no cursor in SQL, and in functional terms, the action can be considered to be happening simultaneously (that is, in parallel) on all rows of the table at once (actual internal implementation might not be exactly that, but the net effect muct be).

To process a table procedurally row by row, you'd have to use a stored procedure and the stored procedure would then establish a cursor row, advance the cursor row by row in a loop and assign to/from the cursor row.

The exact coding of such a stored procedure is totally dependent on the vendor of the database. Unlike SQL, there is no standard for stored procedures, and in fact, on many platforms stored procedures can be written in a variety of languages. For example, Oracle has their own language (PL/SQL), but you can also code stored procedures in Java. The PostgreSQL DBMS supports maybe half a dozen different languages that stored procedures can be written in.
 
Bob Matthews
Ranch Hand
Posts: 181
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Tim

I am totally confused

I am trying to select one particular record from a table, based on an integer variable, which happens to be the primary key for the table namely Prototype_ID
I then want to update a column in that record namely Occurances by one.

I know how to select say the first row of a table using s.ExecuteQuery and then I use 'MYCURSOR' and update the column Occurances

But when I try to select a record using the predetermined integer variable with a PreparedStatement 'MYCURSOR' is not found

I am not understanding the differences here

Bob M
 
Tim Holloway
Bartender
Posts: 20125
103
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
PreparedStatements are not procedures. And, to repeat, there is no such thing as a cursor - or anything named "cursor" - in SQL. Cursors are only possible in procedures, not in SQL.

However, while SQL will apply itself vertically to a column update, you can restrict which rows, get their columns updated. Which isn't the same thing as doing it with a cursor, but it gets the job done.

For example:


This takes the value of "occurances" for the row(s) being updated, add 1s to the value of "occurances" on that row, and updates the column value. You can code that as a PreparedStatement ("WHERE id = ?"), and generally should, but the action is the same either way.
 
Bob Matthews
Ranch Hand
Posts: 181
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tim

Cursors:- embedded, named, scrollable, scrolling etc.
from Index to Book - The Complete Reference - SQL - 3rd edition

How can you say that there is no such thing as 'cursor' in SQL ?

Bob M
 
Tim Holloway
Bartender
Posts: 20125
103
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Bob Matthews wrote:Tim

Cursors:- embedded, named, scrollable, scrolling etc.
from Index to Book - The Complete Reference - SQL - 3rd edition

How can you say that there is no such thing as 'cursor' in SQL ?

Bob M



Because your "SQL" book covers more than just SQL.

An SQL RDBMS typically accepts not only SQL, but also DDL and not uncommonly some sort of proprietary procedural language such as Oracle's PL/SQL or Microsoft's Transact/SQL.

But SQL itself, as defined by Date and Codd is a mathematical language designed to operate on sets of data and has no procedural elements or constructs in it. No "if" statements, no loops, and no cursors.

There  are times when it is convenient to operate in a procedural manner on a set of data, which is why we have JDBC for client-side logic and PL/SQL for server-side logic and thus some sort of cursor mechanism to allow row-by-row operations. But that's not as efficient or as portable as SQL and therefore should be avoided where possible.
 
Sheriff
Posts: 23968
50
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It may be true that your version of SQL supports cursors. But the code you're attempting to write there is a horribly long and convoluted way to write the simple statement



You only need 5 lines of code to do that, instead of the more than 11 lines that might make your code work. And it only runs 1 SQL statement and not 2.
 
Bob Matthews
Ranch Hand
Posts: 181
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Paul

I have rewritten the code as follows

Candidate_ID_to_be_validated = 3
The last entry in the log file is "we get to here - 2"

and I get a NullPointerException error - not exactly sure which line is
creating this error

Bob M
 
Rancher
Posts: 3797
40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The stacj trace of the exception will tell you exactly which line it is that threw that exception.

However, if you look at the code you just posted, line 6 creates a PreparedStatement called psUpdate, but line 9 is setting the statement variable on something called ps.
 
Bob Matthews
Ranch Hand
Posts: 181
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Dave

Good spotting - sloppy coding

Many thanks

Bob M
 
Bob Matthews
Ranch Hand
Posts: 181
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello again

The above code is not updating the Occurances column - why I am unsure ?
Bob M
 
Paul Clapham
Sheriff
Posts: 23968
50
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you are unsure how many records are updated by that code, it happens that the executeUpdate() method returns the number of records that it updated. So assign the result of executeUpdate() to a variable and see what you get.
 
Bob Matthews
Ranch Hand
Posts: 181
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Paul

Only 1 Prototype record is being updated each time

Bob M
 
Paul Clapham
Sheriff
Posts: 23968
50
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That seems reasonable enough to me. It looks to me like Prototype_ID might be the primary key of the PROTOTYPES table, in which case that UPDATE statement would update (at most) one row.

So is there some reason you think that UPDATE statement should update more than one row?

Also, all of your comments in the code seem to envision only one row at a time being updated.
 
Bob Matthews
Ranch Hand
Posts: 181
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul
I am attempting to update just one record by adding 1 to the Occurances column field
However, on testing the program, no update occurs
Bob M
 
Paul Clapham
Sheriff
Posts: 23968
50
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Bob Matthews wrote:Only 1 Prototype record is being updated each time



Bob Matthews wrote:However, on testing the program, no update occurs



I don't see how both of these statements can be true.
 
Bob Matthews
Ranch Hand
Posts: 181
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am attempting (hoping) to update 1 record but alas the chosen record is NOT updated with the current code
 
Marshal
Posts: 5634
147
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
PC said earlier to capture the return value of the update.  This will give us another piece of the puzzle.  So...
 
Bob Matthews
Ranch Hand
Posts: 181
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi

Just the one record is updated oorrectly.......
I was mistaken in thinking the record wasn't updated

apologies
Bob M
 
The only cure for that is hours of television radiation. And this tiny ad:
RavenDB is an Open Source NoSQL Database that’s fully transactional (ACID) across your database
https://coderanch.com/t/704633/RavenDB-Open-Source-NoSQL-Database
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!