UCAExc:::4.0.4 attempt to assign to non-updatable column

Question

This is a link to a sample database I would like this code to connect to my database and encrypt the rows within the table "WellTable" unfortunately I am getting an error listed below. I have followed the guide here: Manipulating an Access database from Java without ODBC ... I am using Atom IDE.

import java.security.Key;
import javax.crypto.Cipher;
import javax.crypto.spec.SecretKeySpec;
import java.util.Scanner;
import java.sql.*;
/*import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
*/


public class hippa {
  public static void main(String[] args) {
        //String databaseURL = "jdbc:ucanaccess://C:/Users/al3cz/OneDrive/Desktop/hippaProject/WellnessMerge.accdb";
        String passkey = ""; // field for passkey
        int dctnum = 0; //dct loop number
        final String key = "Pass1234Pass1234"; // 128 bit key
        String ID = "";
        int x = 0;

        try {

          //connect to the database and get ID
            Connection dbConnection = DriverManager.getConnection("jdbc:ucanaccess://C:/Users/al3cz/OneDrive/Desktop/hippaProject/WellnessMerge.accdb");
            Statement readID = dbConnection.createStatement();
            String sql = "SELECT [CID] FROM [WellTable]";
            ResultSet rs = readID.executeQuery(sql);
            while (rs.next()) {
                 ID = rs.getString("CID");

                //ID = "863784593";

                // Create key and cipher
                Key aesKey = new SecretKeySpec(key.getBytes(), "AES");
                Cipher cipher = Cipher.getInstance("AES");

                //TODO create GUI button that Encrypts all IDs
                // encrypts the ID
                cipher.init(Cipher.ENCRYPT_MODE, aesKey);
                byte[] encryptedID = cipher.doFinal(ID.getBytes());
                StringBuilder sb = new StringBuilder();
                for (byte b: encryptedID) {
                    sb.append((char)b);
                }
                // print the encrypted ID
                String encID = sb.toString();
              //  System.out.println("encrypted ID:" + encID);
                rs.updateString("CID", encID);
                x++;


                // convert the ID to byte array for decryption
                byte[] bb = new byte[encID.length()];
                for (int i=0; i<encID.length(); i++) {
                    bb[i] = (byte) encID.charAt(i);
                }
            }
            System.out.println("This plugin has encrypted " + x +" ID(s)");

            //decrypts the ID
            //TODO create GUI button that Decrypts current IDs or all IDs
            /*
            Scanner keyboard = new Scanner(System.in);
            do {
                System.out.println("Enter the password to decrypt database");
                passkey = keyboard.nextLine();
                if (passkey.equals(key)){
                    dctnum = 7153;
                    cipher.init(Cipher.DECRYPT_MODE, aesKey);
                    String decrypted = new String(cipher.doFinal(bb));
                    System.out.println("decrypted ID:" + decrypted); //TODO insert way to reverse updateID
                }else {System.err.println("incorrect passkey, encrypted ID remains");}
             } while (dctnum != 7153);
             */
        }

        catch(Exception e){
            e.printStackTrace();
        }
  }
}

This code gives an error :  4.0.4 attempt to assign to non-updatable column
        at net.ucanaccess.jdbc.UcanaccessResultSet.updateString(UcanaccessResultSet.java:1924)

"will this code successfully connect to my database and do what I would like it to do" Is not a question which will get any answer. Specify your problem: 1. "code abc" 2. should "do def" 3. but "does ghi" 4. Why/how/wtf - IF YOU WANT A ANSWER TO THAT QUESTION: LET THE CODE RUN AND TEST IT - and for " how can I clean up my code using methods" -> read ANY book about object orientattion / look at basic java tutorials — Marvin Emil Brach, May 27 '19 at 20:24
I don't have ms-access and I am unable to run the code to its full extent. It will help me if someone with more experience is willing to run it and, or look at it and be able to spot the reasons why it wouldn't do what I would like it to, or how it could be written more efficiently. From whatever they say I will be able to learn more. From what, you say I haven't learned much. Thank you for your input though. — Oneil Alec Mahoney, May 28 '19 at 21:12
I've edited my question further hope it is up to site standards: @Mar — Oneil Alec Mahoney, Jun 02 '19 at 01:15
I answered your question. But your code will now throw " java.sql.SQLDataException: data exception: invalid character value for cast" -> please do not change this question again. Look for the same problem in stackoverflow and if that problem is not solved here, then create a new question. — Marvin Emil Brach, Jun 02 '19 at 17:31
@MarvinEmilBrach This is due to my encryption algorithm only encrypting to a string value while the datatype within the column of the database is a number, I wonder if there is a java statement able to change the type of the database from number field to text field. Thanks for your help. — Oneil Alec Mahoney, Jun 03 '19 at 15:21
This is possible since ucanacces 4.0.0 see https://stackoverflow.com/questions/35260429/how-to-alter-table-using-ucanaccess — Marvin Emil Brach, Jun 03 '19 at 15:54

score 1 · Accepted Answer · answered Jun 02 '19 at 17:29

Now you have a problem which is answerable:

If you read the documentation for java.sql.Connection.createStatement() you see:

Result sets created using the returned Statementobject will by default be type TYPE_FORWARD_ONLY and have a concurrency level of CONCUR_READ_ONLY.

So to make it updateable you have to use another constructor which enables you to change this:

Statement readID = dbConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

duplicate of net.ucanaccess.jdbc.UcanaccessSQLException: attempt to assign to non-updatable column

UCAExc:::4.0.4 attempt to assign to non-updatable column

1 Answers1