unexpected token: * required: FROM

[Chaz]

Good Day All,

I am having an issue with a simple Delete query throwing the below error. Here is the SQL Statement.

conn.createStatement().execute("Delete * from [Personnel] WHERE((([ID]) = " + id + "));");

The stack trace is :
net.ucanaccess.jdbc.UcanaccessSQLException: unexpected token: * required: FROM
at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:164)
at connections.AccessConnection.removePerson(AccessConnection.java:661)
at manning.Person_RightClickMenu$3.actionPerformed(Person_RightClickMenu.java:68)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.AbstractButton.doClick(Unknown Source)
at javax.swing.plaf.basic.BasicMenuItemUI.doClick(Unknown Source)
at javax.swing.plaf.basic.BasicMenuItemUI$Handler.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$200(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: * required: FROM
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.executeUpdate(Unknown Source)
at net.ucanaccess.jdbc.ExecuteUpdate.executeWrapped(ExecuteUpdate.java:73)
at net.ucanaccess.jdbc.AbstractExecute.executeBase(AbstractExecute.java:129)
at net.ucanaccess.jdbc.ExecuteUpdate.execute(ExecuteUpdate.java:56)
at net.ucanaccess.jdbc.UcanaccessStatement.executeUpdate(UcanaccessStatement.java:162)
... 40 more
Caused by: org.hsqldb.HsqlException: unexpected token: * required: FROM
at org.hsqldb.error.Error.parseError(Unknown Source)
at org.hsqldb.ParserBase.unexpectedTokenRequire(Unknown Source)
at org.hsqldb.ParserBase.readThis(Unknown Source)
at org.hsqldb.ParserDML.compileDeleteStatement(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
at org.hsqldb.Session.executeDirectStatement(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 46 more

Any help with this is greatly appreciated.

[Chaz]

Interestingly I removed the astrik from the statement and it worked... Is this supposed to be like this?

[Marco Amadei]

Yes, it is, nothing strange. The correct SQL standard DELETE syntax is:
DELETE FROM TABLE WHERE ...
even if the Microsoft Jet Database Engine accepts
DELETE * FROM TABLE WHERE...(besides the standard syntax).
Hsqldb, as well as most of the dbms and according with the SQL standard syntax, only accepts DELETE FROM TABLE .
UCanAccess , on the other hand, in this case doesn't perform a sql conversion, because the use of DELETE * FROM TABLE is really uncommon.

[Marco Amadei]

In order to avoid some trouble, I decided to support DELETE * FROM TABLENAME in the 2.0.8.

[Chaz]

Thank you for your detailed response. Coming from a heavy Access development style by default Access uses the astrik in it's queries when designing them so I have just typically done this in the past.

Thank you again for your help and time! I really apprecaite your efforts and this project you are taking on.