Resultset update not wokring even after using update statement

Question

I saw many answers related to this , and I used one given on stackoverflow of using update prepared statement, but issue still persists.

Getting below error

com.mysql.cj.jdbc.exceptions.NotUpdatable: Result Set not updatable (references computed values or doesn't reference any columns or tables).This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, can not use functions and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details.

This is my code

for(int i =1 ; i <= 2; i++){
             firstResult = (i - 1) * batchSize;
            if (i == totalPages){
                batchSize = dataSize.intValue() - firstResult;
            }

            String query = "select ID,AADHAR_CARD_NUMBER,ADDRESS_LINE_1,ADDRESS_LINE_2,ADDRESS_LINE_3," +
                    "ADDRESS_LINE_4,ANNIVERSARY_DATE,BANK_BRANCH_CODE,BLACKLIST,CARD_TYPE,CASHIER_NAME,CATEGORY,CHANNEL," +
                    "CITY,COMMUNICATION_ADDRESS_LINE_1,COMMUNICATION_ADDRESS_LINE_2,COMMUNICATION_ADDRESS_LINE_3," +
                    "COMMUNICATION_CITY,COMMUNICATION_COUNTRY,COMMUNICATION_STATE,COMMUNICATION_ZIP,COMPANY,COUNTRY," +
                    "CREATED_BY,CREATION_DATE,CUSTOMER_ID_GENERATION_DATE,DATE_OF_BIRTH,DATE_TURNED_NPA,DEREGISTRATION_DATE," +
                    "DELINQUENCY_DATE,DESIGNATION,EDUCATION,EMAIL_1,EMAIL_2,EMPLOYEE_CODE,ENROLMENT_DATE,FILE_ID,FILENAME," +
                    "FIRST_NAME,GENDER,GHOST,HEADER,HOME_PHONE,IB_ACTIVATIONDATE,IB_ACTIVATIONSTATUS,IB_DEACTIVATIONDATE," +
                    "IS_DELINQUENT,IS_DORMANT,IS_KYC_VERIFIED,IS_MINIOR,IS_NPA,IS_SENIOR_CITIZEN,LAST_MODIFIED_BY," +
                    "LAST_MODIFIED_DATE,LAST_NAME,LEVEL_1,LEVEL_2,LEVEL_3,MARITAL_STATUS,MB_ACTIVATIONDATE,MB_ACTIVATIONSTATUS," +
                    "MB_DEACTIVATIONDATE,MIDDLE_NAME,MOBILE,MODE_OF_ACTIVATION,MOTHER_MAIDEN_NAME,NATIONALITY,PAN_CARD_NUMBER," +
                    "PROFILE_REGISTRATION_DATE,REFERENCE_NUMBER,REFERRAL_CODE,REFERRER_CODE,REFERER_ID,REGION,REGISTERED_SOURCE," +
                    "REGISTRATION_DATE,REGISTRATION_STATUS,SECONDARY_CONTACT_NUMBER,SOURCE,SPOUSE_OR_FATHER_DATE_OF_BIRTH," +
                    "SPOUSE_OR_FATHER_NAME,STAFF_FLAG,STATE,STORE_CODE,STORE_ID,TIER,TIER_CHANGE_DATE,TITLE," +
                    "CUSTOMERLEDGER_UNIQUE_CUSTOMER_ID,UPDATE_DATE,UPDATE_IDENTIFIER,VERSION,WORK_PHONE,YES_FIRST_FLAG,ZIP," +
                    "\"MOBILE_CHECKSUM\",\"MOBILE_SHA\",\"EMAIL_CHECKSUM\",\"EMAIL_SHA\" from " + table + " limit " + firstResult + "," + batchSize;


            PreparedStatement prepStmt = connect.prepareStatement(
                    query,
                    ResultSet.TYPE_FORWARD_ONLY,
                    ResultSet.CONCUR_UPDATABLE);

            ResultSet rs = prepStmt.executeQuery();

            while (rs.next()) {
                //rs.getString("mobile");
                rs.updateString("FIRST_NAME", convertToDatabaseColumn(rs.getString("FIRST_NAME"),key));
                rs.updateString("MIDDLE_NAME", convertToDatabaseColumn(rs.getString("MIDDLE_NAME"),key));
                rs.updateString("LAST_NAME", convertToDatabaseColumn(rs.getString("LAST_NAME"),key));
                rs.updateString("ADDRESS_LINE_1", convertToDatabaseColumn(rs.getString("ADDRESS_LINE_1"),key));
                rs.updateString("ADDRESS_LINE_2", convertToDatabaseColumn(rs.getString("ADDRESS_LINE_2"),key));
                rs.updateString("ADDRESS_LINE_3", convertToDatabaseColumn(rs.getString("ADDRESS_LINE_3"),key));
                rs.updateString("ADDRESS_LINE_4", convertToDatabaseColumn(rs.getString("ADDRESS_LINE_4"),key));
                rs.updateString("HOME_PHONE", convertToDatabaseColumn(rs.getString("HOME_PHONE"),key));
                rs.updateString("WORK_PHONE", convertToDatabaseColumn(rs.getString("WORK_PHONE"),key));
                rs.updateString("MOBILE", convertToDatabaseColumn(rs.getString("MOBILE"),key));
                rs.updateString("EMAIL_1", convertToDatabaseColumn(rs.getString("EMAIL_1"),key));
                rs.updateString("MOBILE_SHA", sha256Hash(rs.getString("MOBILE")));
                rs.updateString("EMAIL_SHA", sha256Hash(rs.getString("EMAIL_1")));
                rs.updateLong("MOBILE_CHECKSUM", getchecksum(rs.getString("MOBILE")));
                rs.updateLong("EMAIL_CHECKSUM", getchecksum(rs.getString("EMAIL_1")));
            }
                System.out.println("Batch Read ... " + i + " started from "+ firstResult);
            csvWriter.writeAll(rs, true);
            System.out.println("Batch written ..." + i);
        }

what does your table structure look like? Also you might want to use sql formatter to format query in case of syntax issues — Milan Desai, Dec 13 '19 at 10:17
The examples I've seen use ResultSet.TYPE_SCROLL_INSENSITIVE instead of ResultSet.TYPE_FORWARD_ONLY, have you tried that? — mwarren, Dec 13 '19 at 10:32
Does this answer your question? [ResultSet update row is not working](https://stackoverflow.com/questions/28124427/resultset-update-row-is-not-working) — Onkar Musale, Dec 13 '19 at 10:32
From the error: _"Result Set not updatable (references computed values or doesn't reference any columns or tables)."_ and _"must select all primary keys from that table"_. Does your query reference computed values, or does it not include some of the primary key columns? You may want to post the DDL of the table you're querying. — Mark Rotteveel, Dec 13 '19 at 11:47

Resultset update not wokring even after using update statement

0 Answers0