JDBC - How do I 'getString' from a ResultSet if the column's name is a keyword?

Question

Whenever I try to retrieve the value for a column named "Comment" in an Access db (.mdb), It gives me the following error.

Offence: copying data
ERROR - Offence TABLE
java.sql.SQLSyntaxErrorException: ORA-00904: "Comment": invalid identifier

This is my code:

public static void copyOffenceTable()
    {
        PreparedStatement updateOffenceTypeTable  = null;

        //Set up "insert" string
        String insertString = "INSERT INTO Offence "
                + "(id, OffenceName, status, OffenceSuburb, OffenceDate, FirstName, LastName, \"Comment\", OffenderAddress, OffenderSuburb, OffenderPostcode, OffenderState, OffenderEmail, LicenceNumber, DOB, StaffIssuerID, OffenderPhone) "                                          
                + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

        String MSAccessQuery = "SELECT * FROM Offence";

        try
        {
            //create a query object for MSAccess
            dbmsMSAccess.DBMSStatement = dbmsMSAccess.DBMSConnection.createStatement();
            //Query the MSAccess database for extraction
            dbmsMSAccess.DBMSResultSet = dbmsMSAccess.DBMSStatement.executeQuery(MSAccessQuery);
            //make query object for Oracle
            dbmsOracle.DBMSOracleStatement = dbmsOracle.DBMSOracleConnection.createStatement();

            dbmsOracle.DBMSOracleStatement.execute("TRUNCATE TABLE Offence");

            dbmsOracle.DBMSOracleConnection.setAutoCommit(false);

            updateOffenceTypeTable = dbmsOracle.DBMSOracleConnection.prepareStatement(insertString);

            System.out.println("Offence: copying data");

            while(dbmsMSAccess.DBMSResultSet.next())
            {
                updateOffenceTypeTable.setInt(1, dbmsMSAccess.DBMSResultSet.getInt("id") );
                updateOffenceTypeTable.setString(2, dbmsMSAccess.DBMSResultSet.getString("OffenceName") );
                updateOffenceTypeTable.setString(3, dbmsMSAccess.DBMSResultSet.getString("Status") );
                updateOffenceTypeTable.setString(4, dbmsMSAccess.DBMSResultSet.getString("OffenceSuburb") );
                updateOffenceTypeTable.setDate(5, dbmsMSAccess.DBMSResultSet.getDate("OffenceDate") );
                updateOffenceTypeTable.setString(6, dbmsMSAccess.DBMSResultSet.getString("FirstName") );
                updateOffenceTypeTable.setString(7, dbmsMSAccess.DBMSResultSet.getString("LastName") );
                updateOffenceTypeTable.setString(8, dbmsMSAccess.DBMSResultSet.getString("COMMENT") );
                updateOffenceTypeTable.setString(9, dbmsMSAccess.DBMSResultSet.getString("OffenderAddress") );
                updateOffenceTypeTable.setString(10, dbmsMSAccess.DBMSResultSet.getString("OffenderSuburb") );
                updateOffenceTypeTable.setInt(11, dbmsMSAccess.DBMSResultSet.getInt("OffenderPostcode") );
                updateOffenceTypeTable.setString(12, dbmsMSAccess.DBMSResultSet.getString("OffenderState") );
                updateOffenceTypeTable.setString(13, dbmsMSAccess.DBMSResultSet.getString("OffenderEmail") );
                updateOffenceTypeTable.setInt(14, dbmsMSAccess.DBMSResultSet.getInt("LicenceNumber") );
                updateOffenceTypeTable.setDate(15, dbmsMSAccess.DBMSResultSet.getDate("DOB") );
                updateOffenceTypeTable.setInt(16, dbmsMSAccess.DBMSResultSet.getInt("StaffIssuerID") );
                updateOffenceTypeTable.setInt(17, dbmsMSAccess.DBMSResultSet.getInt("OffenderPhone") );

                updateOffenceTypeTable.executeUpdate();
            }

            System.out.println("Offence: ACCESS DATA COPIED TO ORACLE\n");
        }
        catch(Exception X)
        {
            System.out.println("ERROR - Offence TABLE");
            X.printStackTrace();
        } 
    }

Is there a way to refer to a column that is also a keyword through jdbc?

I've tried putting 'comment' between apostrophes and also quote marks.

Any suggestions?

EDIT: The values are being placed into an oracle database with supposedly identical tables.

if you dont use any quotes '," does it also fails with same error? — harsh, May 20 '13 at 11:59
How are you connecting to Access .mdb and getting ORA error codes? — Ravi K Thapliyal, May 20 '13 at 13:02
@RaviThapliyal I forgot to mention that i was putting them into an Oracle database. Sorry about that =[ — , May 20 '13 at 13:08

score 0 · Answer 1 · edited May 23 '17 at 10:09

Try with some brackets:

 String insertString = "INSERT INTO Offence "
                + "(id, OffenceName, status, OffenceSuburb, OffenceDate, FirstName, LastName, [Comment], OffenderAddress, OffenderSuburb, OffenderPostcode, OffenderState, OffenderEmail, LicenceNumber, DOB, StaffIssuerID, OffenderPhone) "                                          
                + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

EDIT: For oracle, if we trust this topic : https://stackoverflow.com/a/1162391/291541

The solution is the double quote but you have to pay attention to the case. Please check if your column is comment instead of Comment by example.

Unfortunately not. =[ It gives me another exception about the same thing — , May 20 '13 at 12:10

score 0 · Answer 2 · answered May 20 '13 at 12:02

0

Please remove Double Quotes inside the Query

   String insertString = "INSERT INTO Offence "
                + "(id, OffenceName, status, OffenceSuburb, OffenceDate, FirstName, LastName, Comment, OffenderAddress, OffenderSuburb, OffenderPostcode, OffenderState, OffenderEmail, LicenceNumber, DOB, StaffIssuerID, OffenderPhone) "                                          
                + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

answered May 20 '13 at 12:02

Akshay Joy

1,765
1
14
23

unfortunately it just gave me another exception: "java.sql.SQLSyntaxErrorException: ORA-01747: invalid user.table.column, table.column, or column specification" – May 20 '13 at 12:13
1

In the Question you specified the MDB file(Access). but Code looks like you connect to ORACLE Database, So may be the Table or column anme Correct. I couldn't see any OLEDB Statement here – Akshay Joy May 20 '13 at 12:17
Yup, I'm retrieving column data from an Access database, and inserting them into an established oracle database. This is just a static method in my main class All the connections happen in the main method, and i have similar methods for other tables. – May 20 '13 at 12:26
Either Table column name or Table not Exist in Oracle Db. by seeing th e Log this what I know – Akshay Joy May 20 '13 at 12:33

score 0 · Answer 3 · answered May 20 '13 at 12:03

0

Won't be possible to rename your column at query level? something like select comment as my_comment from blabla

You could also reach that field using its index

answered May 20 '13 at 12:03

Aitor

195
1
11

Do you mean in the actual database itself, add a subquery into the insertString String? (I'm sorry, i'm actually kinda new to jdbc, it's for university) – May 20 '13 at 12:12

score 0 · Answer 4 · answered May 20 '13 at 12:38

0

Use a back quote (or back tick) to escape the offending column's name like `\"Comment\"`

String insertString = "INSERT INTO Offence "
+ "(id, OffenceName, status, OffenceSuburb, OffenceDate, FirstName, LastName, `\"Comment\"`, OffenderAddress, OffenderSuburb, OffenderPostcode, OffenderState, OffenderEmail, LicenceNumber, DOB, StaffIssuerID, OffenderPhone) "                                          
+ "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

answered May 20 '13 at 12:38

Ravi K Thapliyal

51,095
9
76
89

nope, it just gives me this error "java.sql.SQLSyntaxErrorException: ORA-01747: invalid user.table.column, table.column, or column specification" =/ – May 20 '13 at 12:46
You said your database is .mdb but your error codes suggest it's Oracle (ORA). I just realised but why is so? – Ravi K Thapliyal May 20 '13 at 12:48
I'm retrieving access columns and inserting them into an Oracle database. Oh god i forgot to mention it. Sorry about that =S. – May 20 '13 at 13:05

score 0 · Accepted Answer · answered May 20 '13 at 13:20

In the end, None of these methods suggested worked for me, however they were worth a shot!

I ended up having to go into the database and get permission to change the field from comment to comments.

Thanks everyone who posted i really appreciate it!

JDBC - How do I 'getString' from a ResultSet if the column's name is a keyword?

5 Answers5