Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Why is my uprs.UpdateRow not working?

Posted on 2008-10-22
20
570 Views
Last Modified: 2012-05-05
These three lines is not working well, database table "salestable" is not adding records.There are no errors.

uprsSales.moveToInsertRow();
uprsSales.updateFloat(columnnumber, columnsold);
uprsSales.updateRow();

import java.sql.*;
public class InsertRows {
public static void main(String args[]) {
    
            try { 
            String url = "jdbc:mysql://localhost:3306/test2"; 
            Connection conn = DriverManager.getConnection(url,"root","root"); 
            
            PreparedStatement stmtInventory = conn.prepareStatement("SELECT * FROM inventorytable", java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
            PreparedStatement stmtSales = conn.prepareStatement("SELECT * FROM salestable", java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
            ResultSet uprsInventory; 
            ResultSet uprsSales;
            uprsInventory=stmtInventory.executeQuery();
            uprsSales=stmtSales.executeQuery();
            ResultSetMetaData uprsInventoryMetaData = uprsInventory.getMetaData();
            ResultSetMetaData uprsSalesMetaData = uprsSales.getMetaData();
 
 
    int numberOfColumns = uprsInventoryMetaData.getColumnCount();
    System.out.println("resultSet MetaData column Count=" + numberOfColumns);
 
 
            
 
 int columnnumber = 2;
float columnsold;
int rownumber;
while(columnnumber < numberOfColumns) {
  uprsInventory.absolute(2);
  
   int idvar1 = uprsInventory.getInt("id");
  System.out.print("id " + idvar1 + " ");
  
rownumber = uprsInventory.getRow();
float columnpresent = uprsInventory.getInt(columnnumber);
System.out.println("PRESENT. row=" + rownumber + " column=" + columnnumber + " value=" + columnpresent);
 
uprsInventory.previous();
rownumber = uprsInventory.getRow();
float columnprevious = uprsInventory.getInt(columnnumber);
System.out.println("PREVIOUS. row=" + rownumber + " column=" + columnnumber + " value=" + columnprevious);
 
 
columnsold = 0;
columnsold = columnprevious - columnpresent;
System.out.println("column sold " + columnsold);
 
uprsSales.moveToInsertRow();
uprsSales.updateFloat(columnnumber, columnsold);
uprsSales.updateRow();
 
 
columnnumber = columnnumber + 1;
 
}
uprsInventory.close();
stmtInventory.close();
uprsSales.close();
stmtSales.close();
conn.close();
 
} catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
}
}

Open in new window

0
Comment
Question by:glynco
  • 13
  • 7
20 Comments
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22774315
What is the error you are getting
0
 

Author Comment

by:glynco
ID: 22774322
There are no errors. Build was successful.
0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22774327
i mean when you run this example are you getting any errors.
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:glynco
ID: 22774339
There are no errors running the program.
Compiling 1 source file to C:\Documents and Settings\ALCO\My Documents\NetBeansProjects\JavaApplication60\build\classes
compile:
run:
resultSet MetaData column Count=4
id 2 PRESENT. row=2 column=2 value=90.0
PREVIOUS. row=1 column=2 value=100.0
column sold 10.0
column number to write salestable: 2
id 2 PRESENT. row=2 column=3 value=45.0
PREVIOUS. row=1 column=3 value=50.0
column sold 5.0
column number to write salestable: 3
BUILD SUCCESSFUL (total time: 1 second)

Open in new window

0
 

Author Comment

by:glynco
ID: 22774350
I just tried putting a value directly to
uprsSales.updateFloat(2,10);
but the record not added to the database.

System.out.println("column number to write salestable: " + columnnumber);
uprsSales.moveToInsertRow();
uprsSales.updateFloat(2, 10);
uprsSales.updateRow();
0
 

Author Comment

by:glynco
ID: 22774376
I tried
uprsSales.insertRow();
and the records added but got this error because I have to loop 3 times to write 3 column records.

SQLException: Field 'id' doesn't have a default value
0
 

Author Comment

by:glynco
ID: 22774423
I put autoincrement on key column ID and the uprsSales.insertRow(); works but the records are on 3 different rows instead it should be on the same row.
So uprsSales.updateRow(); should do the trick but I cant make it work.
0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22774437
use this method moveToCurrentRow.
0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22774444
0
 

Author Comment

by:glynco
ID: 22774452
I've changed this line:
            PreparedStatement stmtSales = conn.prepareStatement("INSERT INTO VALUES(?,?,?); salestable", java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);

got this error:
SQLException: Can not issue data manipulation statements with executeQuery().
0
 

Author Comment

by:glynco
ID: 22774507
Attached a copy of the salestable I use insertRow() instead of updateRow()
salestable.JPG
0
 

Author Comment

by:glynco
ID: 22774518
I tried putting uprsSales.previous(); so that it goes back 1 row after each while loop

uprsSales.moveToInsertRow();
uprsSales.previous();
uprsSales.updateFloat(columnnumber, columnsold);
uprsSales.insertRow();

but I got this error:
SQLException: Not on insert row.
0
 

Author Comment

by:glynco
ID: 22774538
tried this:
uprsSales.last();
uprsSales.updateFloat(columnnumber, columnsold);
uprsSales.updateRow();

got this error:
Exception in thread "main" java.lang.NullPointerException
        at com.mysql.jdbc.UpdatableResultSet.syncUpdate(UpdatableResultSet.java:1499)
        at com.mysql.jdbc.UpdatableResultSet.updateFloat(UpdatableResultSet.java:2065)
0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22774548
in which line number  NullPointerException came
0
 

Author Comment

by:glynco
ID: 22774568
I dont know. I've been trying changing the 3 lines  only.

Tried:
uprsSales.absolute(1);
uprsSales.updateFloat(columnnumber, columnsold);
uprsSales.updateRow();

error:
Exception in thread "main" java.lang.NullPointerException
        at com.mysql.jdbc.UpdatableResultSet.syncUpdate(UpdatableResultSet.java:1499)
        at com.mysql.jdbc.UpdatableResultSet.updateFloat(UpdatableResultSet.java:2065)
        at InsertRows.main(InsertRows.java:53)
Java Result: 1
0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22774588
0
 

Author Comment

by:glynco
ID: 22774745
I am using mySQL not Oracle so I dont have problem with that bug you referring.
0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22774766
wait for sometime so that other experts can look into ur problem cos i hav n't worked in the advanced JDBC.
0
 

Author Comment

by:glynco
ID: 22775135
When I insert a row and enter a value 1 of column "id" using HeidiSql then it works.

So I try this to put this to insert a new row and put id value 1
uprsSales.moveToInsertRow(  );
uprsSales.updateInt(1,1);
uprsSales.insertRow();

then try to update
uprsSales.absolute(1);
uprsSales.updateFloat(columnnumber, columnsold);
uprsSales.updateRow();

the result is
one column gets the value but the other 2 column did not and got this error
SQLException: Duplicate entry '1' for key 1
I think this is because of the while loop for the 2nd and 3rd loop trying to overwrite "id"
0
 

Accepted Solution

by:
glynco earned 0 total points
ID: 22775153
I got it. I put the insertRow above the while loop.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question