CODEWITHSUNDEEP
javaresultset
user3610984
user3610984

Reputation: 11

how to manipulate resultset

i am using java and postgres in my application. i am fetching data from the data base and use my logic against the data that is stored in the result set . after that i want to manipulate the second field of the result set through the statement 

 rs.updateString(2,s);

is my approch of manipulating the result set is correct. as i am not able to change the data that is stored in the result set through the above statement and while executing the above statement i am getting the exception. please help ...

after using the advice i have modified the program and my code is 

    Connection con = getNonTConnection();
    ResultSet rs = null;
    try {
    String strSQL = "Query for fetching two fields from database";
    pstmt=con.prepareStatement(strSQL,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
    pstmt.setString(1, locationID);
    log.debug(pstmt);
    rs = pstmt.executeQuery();
    while (rs.next())
    {
        piscode = rs.getString("pis_code").trim();
        password = rs.getString("pis_passwd").trim(); 
        passwdMatched = checkPassword(piscode,password); // return true or false 
        if(passwdMatched)
            s="true";
        else 
            s="false";
        rs.updateString(2,s);// this statement still result in error 
     }

the list of errors i got 

org.postgresql.util.PSQLException: The column name location not found.
13602ms [AWT-EventQueue-0] DEBUG: 2014-May-09 @ 12:35:58,327 (JRXmlDigesterFactory.java:createParser:1333)
Using SAX parser factory class net.sf.jasperreports.engine.xml.JRReportSaxParserFactory
at org.postgresql.jdbc1.AbstractJdbc1ResultSet.findColumn(AbstractJdbc1ResultSet.java:673)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.isUpdateable(AbstractJdbc2ResultSet.java:1351)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateValue(AbstractJdbc2ResultSet.java:1517)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateString(AbstractJdbc2ResultSet.java:1122)

in the code after the modification of 

 rs.updateRow();

i still got the error 

     org.postgresql.util.PSQLException: The column name location not found.
at org.postgresql.jdbc1.AbstractJdbc1ResultSet.findColumn(AbstractJdbc1ResultSet.java:673)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.isUpdateable(AbstractJdbc2ResultSet.java:1351)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateValue(AbstractJdbc2ResultSet.java:1517)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateString(AbstractJdbc2ResultSet.java:1122)
at org.postgresql.jdbc2.AbstractJdbc2ResultSet.updateString(AbstractJdbc2ResultSet.java:1211)

Upvotes: 1

Views: 1800

Answers (1)

Luiggi Mendoza
Luiggi Mendoza

Reputation: 85779

You make sure to create the PreparedStatement allowing updatable ResultSets. This will work if you call Connection#prepareStatement(String sql, int resultSetType, int resultSetConcurrency) and passing:

  • ResultSet.TYPE_SCROLL_SENSITIVE as resultSetType parameter.
  • ResultSet.CONCUR_UPDATABLE as resultSetConcurrency parameter.

Code example:

Connection con = ...
String sql = "SELECT ...";
PreparedStatement pstmt = con.prepareStatement(
    sql,
    ResultSet.TYPE_SCROLL_SENSITIVE,
    ResultSet.CONCUR_UPDATABLE);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
    //...
    String s = ...;
    //...
    rs.updateString(2,s);
    //important!
    rs.updateRow();
}

More info:

Note that this may fail if your JDBC driver doesn't support these values for the ResultSet, though.

Upvotes: 3

Related Questions