Bob Matthews

Thanks Paul

Bob M

I understand how to create a result set with a view to pulling out a subset of records from a table

I don't understand how you access the value for a particular column of this subset

Bob M

Hi Dave

Good spotting - sloppy coding

Many thanks

Bob M

Thanks Paul

I have rewritten the code as follows
// if we have a candidate_ID to be validated, update Prototype record with Occurances if (Candidate_ID_to_be_validated > 0) {        fw.writetoFile(("we get to here - 1"), File_Name);            // update Occurances field in the similar Prototypes record by 1        psUpdate = conn.prepareStatement("UPDATE PROTOTYPES SET Occurances = Occurances + 1 WHERE Prototype_ID = ?");        fw.writetoFile(("we get to here - 2"), File_Name);            ps.setInt(1,Candidate_ID_to_be_validated);        psUpdate.executeUpdate();        // commit the above transactions        conn.commit();        fw.writetoFile(("Updated Prototypes record with Occurances"), File_Name);        fw.writetoFile(("and Occurances is : - " + (Occurances + 1)), File_Name);
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

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

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

Hi

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

ps = conn.prepareStatement ("SELECT * FROM PROTOTYPES WHERE Prototype_ID = ?"); ps.setInt(1,Prototype_ID); rs = ps.executeQuery(); rs.next(); // update Occurances field in the similar Prototypes record by 1 s.setCursorName("MYCURSOR"); psUpdate = conn.prepareStatement("UPDATE PROTOTYPES SET Occurances = ? WHERE CURRENT OF MYCURSOR"); statements.add(psUpdate); psUpdate.setInt(1, Occurances + 1); psUpdate.executeUpdate(); conn.commit();

Error: Cursor 'MY CURSOR' not found

Am I still on the required record ?

Bob M

thanks Paul

Bob M

I have a table with a first column as ID (integer) and this column is the primary key

I do some calculations to decide which record I wish to retrieve
and end up with the following integer value variable called Candidate_ID

The table also has a column called Occurances (integer) which I wish to update by adding 1

rs = s.executeQuery("SELECT * FROM PROTOTYPES WHERE Prototype_ID = Candidate_ID"); rs.next(); s.setCursorName("MYCURSOR"); rs = s.executeQuery("SELECT Occurances FROM PROTOTYPES WHERE Prototype_ID = Candidate_ID_to_be_validated FOR UPDATE"); rs.next(); psUpdate = conn.prepareStatement("UPDATE PROTOTYPES SET Occurances = ? WHERE CURRENT OF MYCURSOR"); statements.add(psUpdate); psUpdate.setInt(1, Occurances + 1); psUpdate.executeUpdate();
I am getting an error on the first line of code
SQL State: 42X04
Error Code: 20000
Message: Column 'Candidate_ID' is either not in any table or................

What should I change in the first line ?

Bob M

Hi Tim

Only me here

I reopened Netbeans as administrator and can now delete records

Cheers

Bob M

Hi
I have run my java program and it has added a couple of records to a table

when I try to delete those 2 records from within Netbeans I get the following message:-

Error code 20000, SQL state 25502

Delete command failed: An SQL data change is not permitted for a read-only connection, user or database

What do I need to do to be able to alter data in a table ?

Bob M

Thanks Paul..that did it

Bob M

Sorry..........missed  quote marks
ps = conn.prepareStatement("SELECT COUNT(*) from table_name where ID = Search_ID");

I have a table with a particular column named ID

The records in the table have an integer in the field and there may be several records with the same integer value

I am using the following code where Search_ID has just been calculated...........
ps = conn.prepareStatement(SELECT COUNT(*) from table_name where ID = Search_ID");
The program falters on this line and I am unclear why ?

Bob M

Knute - thanks for that

Carey - my new lone reads as follows................

rs = s.executeQuery("SELECT p0 FROM HOURLY_HISTORY WHERE Trading_Date = '"       + Date_temp + "' AND Trading_Time = " + Time_temp       + " FOR UPDATE");

Is there a better way to code this ?

Bob M