Thrown java.sql.SQLSyntaxErrorException: ORA-00911: invalid character and unable to find the reason in Netbeans

Question

I have to create a Javafx application that connects to a database. we made in Oracle and is able to view entries via putting in an already inserted ID, insert a new row to the table, update a row, or clear.

My application connects to the database just fine. Rather, when trying to insert a new row into the empty table, I'm thrown ORA-00911: invalid character. I can't find the problem anywhere in my code, so I'm hoping to see if any of you can spot what's wrong:

private void Insert() {
        String queryString = "INSERT INTO Staff (id, lastName, firstName, mi, address, city, state, telephone, email) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);";
        try {
            preparedStatement = connection.prepareStatement(queryString);
            preparedStatement.setString(1, tfId.getText());
            preparedStatement.setString(2, tfLastName.getText());
            preparedStatement.setString(3, tfFirstName.getText());
            preparedStatement.setString(4, tfMi.getText());
            preparedStatement.setString(5, tfAddress.getText());
            preparedStatement.setString(6, tfCity.getText());
            preparedStatement.setString(7, tfState.getText());
            preparedStatement.setString(8, tfTelephone.getText());
            preparedStatement.setString(9, tfEmail.getText());
            preparedStatement.executeUpdate();
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
    }

I already tried removing the above the SQL, having heard that sometimes that resolves it and it didn't work. Also, in case there is any confusion, I already established PreparedStatement preparedStatement earlier among my other variables. Also, here is my error log:

java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1046)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3694)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1354)
    at week13.Week13.Insert(Week13.java:151)
    at week13.Week13.lambda$start$2(Week13.java:90)
    at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:86)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
    at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
    at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:49)
    at javafx.event.Event.fireEvent(Event.java:198)
    at javafx.scene.Node.fireEvent(Node.java:8411)
    at javafx.scene.control.Button.fire(Button.java:185)
    at com.sun.javafx.scene.control.behavior.ButtonBehavior.mouseReleased(ButtonBehavior.java:182)
    at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:96)
    at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:89)
    at com.sun.javafx.event.CompositeEventHandler$NormalEventHandlerRecord.handleBubblingEvent(CompositeEventHandler.java:218)
    at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:80)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
    at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
    at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:54)
    at javafx.event.Event.fireEvent(Event.java:198)
    at javafx.scene.Scene$MouseHandler.process(Scene.java:3757)
    at javafx.scene.Scene$MouseHandler.access$1500(Scene.java:3485)
    at javafx.scene.Scene.impl_processMouseEvent(Scene.java:1762)
    at javafx.scene.Scene$ScenePeerListener.mouseEvent(Scene.java:2494)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:394)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:295)
    at java.security.AccessController.doPrivileged(Native Method)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler.lambda$handleMouseEvent$353(GlassViewEventHandler.java:432)
    at com.sun.javafx.tk.quantum.QuantumToolkit.runWithoutRenderLock(QuantumToolkit.java:389)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(GlassViewEventHandler.java:431)
    at com.sun.glass.ui.View.handleMouseEvent(View.java:555)
    at com.sun.glass.ui.View.notifyMouse(View.java:937)
    at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at com.sun.glass.ui.win.WinApplication.lambda$null$147(WinApplication.java:177)
    at java.lang.Thread.run(Thread.java:748)

Statements terminators are not part of the statement itself. The JDBC API is designed to execute individual statements, so a statement should not be terminated. Can you remove the semicolon from `queryString` and try again? — x80486, Nov 26 '19 at 18:22
Check if you are inserting string while the field only accepts the number — Zafar, Nov 26 '19 at 18:26
@inson that's likely the case, specifically for the ID field. — JoshGivens, Nov 26 '19 at 18:30
@x80486 is right and this is not the first question on this topic here... — Marmite Bomber, Nov 26 '19 at 18:43
@x80486 I tried that and it said the table and view does not exist when I removed it — Cunga Bunga, Nov 26 '19 at 18:44
@MikeNakis nothing at the moment. If insert was working, it'd allow me to view inserted rows into the database — Cunga Bunga, Nov 26 '19 at 18:54
try `System.out.println(preparedStatement);` from: [https://stackoverflow.com/questions/2683214/get-query-from-java-sql-preparedstatement](https://stackoverflow.com/questions/2683214/get-query-from-java-sql-preparedstatement) If it prints out the statement give it a look over and see if anything looks wrong — Matt, Nov 26 '19 at 19:08
Your stack trace yields an `ORA-00942`, not an `ORA-00911`...I think that's a different problem. You need to get to the same state you were before to be able to solve one problem, then the next one (if any). — x80486, Nov 26 '19 at 19:26
@x80486 Just now fixed it. Didn't realize I had the wrong error message of all things. — Cunga Bunga, Nov 26 '19 at 19:30

score 2 · Answer 1 · answered Nov 26 '19 at 20:58

2

remove ';' at the end of your insert statement

answered Nov 26 '19 at 20:58

Anton Straka

139
2

Thrown java.sql.SQLSyntaxErrorException: ORA-00911: invalid character and unable to find the reason in Netbeans

1 Answers1