Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Cannot Delete a record with an delete query though Java Eclipse connected to MS Access

 
Ryan Choong
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Problem with update record in MS Access. It show this error when print stack trace .

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.1 unexpected token: QUANTITY
at net.ucanaccess.jdbc.UcanaccessConnection.prepareStatement(UcanaccessConnection.java:509)
at thirdTier.GeneralDBAccess.update(GeneralDBAccess.java:272)
at secondTier.DataStorage.updateConfirmed(DataStorage.java:62)
at firstTier.UserInterface.begin(UserInterface.java:174)
at firstTier.ItemDriver.main(ItemDriver.java:16)
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: QUANTITY
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
at net.ucanaccess.jdbc.UcanaccessConnection.prepareStatement(UcanaccessConnection.java:505)
... 4 more
Caused by: org.hsqldb.HsqlException: unexpected token: QUANTITY
at org.hsqldb.error.Error.parseError(Unknown Source)
at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
at org.hsqldb.Session.compileStatement(Unknown Source)
at org.hsqldb.StatementManager.compile(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 7 more

user interface



Data storage




GeneralDBAccess




Kindly help to check what the problem.
 
Claudiu Chelemen
Ranch Hand
Posts: 75
Eclipse IDE Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I took a quick glance at the code and I noticed that the update sql in the GeneralDBAccess class is not well formed, it should have commas after each set.
Claudiu
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to the ranch!

In my opinion, the problem is the update statement itself:
Ryan Choong wrote:


You need to put a comma after the question marks. The SQL command, as it stands now, is

- it should be clear now where the additional commas need to be.
 
Ryan Choong
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
After I add the the comma,

now show this error.

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.1 user lacks privilege or object not found: WHERE
at net.ucanaccess.jdbc.UcanaccessConnection.prepareStatement(UcanaccessConnection.java:509)
at thirdTier.GeneralDBAccess.update(GeneralDBAccess.java:265)
at secondTier.DataStorage.updateConfirmed(DataStorage.java:62)
at firstTier.UserInterface.begin(UserInterface.java:174)
at firstTier.ItemDriver.main(ItemDriver.java:16)
Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: WHERE
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
at net.ucanaccess.jdbc.UcanaccessConnection.prepareStatement(UcanaccessConnection.java:505)
... 4 more
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: WHERE
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.ParserDQL.XreadTargetSpecification(Unknown Source)
at org.hsqldb.ParserDML.readSetClauseList(Unknown Source)
at org.hsqldb.ParserDML.compileUpdateStatement(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
at org.hsqldb.Session.compileStatement(Unknown Source)
at org.hsqldb.StatementManager.compile(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 7 more
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'd guess it's a syntax error again. Post your current code, please.
 
Ryan Choong
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think i solved the syntax error problem

here's the latest code.



Now the problem is when I select the update, it go backs to my menu.

user interface




Not sure why go back to show menu but not update the data
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ryan Choong wrote:Not sure why go back to show menu but not update the data

Then you should start debugging your own code so you can IsolateTheProblem. You'll more likely to get a valuable answer if you post just a code snippet of 10-15 lines of code instead of 350+ lines, because before someone can answer he/she has to evaluate all these lines of code and very few people will do that.
 
Ryan Choong
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry for 1st time posting in this forum.

I'm newbie in java programming.

So after debug the code for update

user interface





data storage




GeneralDBAccess




I try to update, but is not updating at all. show nothing error.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ryan Choong wrote:Sorry for 1st time posting in this forum.

I'm newbie in java programming.

So after debug the code for update

This is already much better, but still it's a lot of code to go through (almost 150 lines combined). I went through your code and it looks a little bit strange, weird and inconsistent.

Let's have a look at this lineFor me it seems you are retrieving a Phone from the database by invoking the update-method?!?

Then you do something likeI have no clue what's the purpose of this code. Maybe it's getting all information from a Phone out of the database. I really don't know! But again that should be 1 call to a get() method, not invoking several methods and definitely not an updateConfirmed() method.

Finally this code is executedAnd I quess the updatePhone() method is asking the user to provide the new details. And then the resulting Phone object is persisted to the database (but that doesn't seem to work). It also seems that your user has to enter the phone id twice. That's not really user-friendly. And what's the purpose of the find() method call inside the update() method And another very important remark: it seems you are not closing your resources (like Connection, Statement, ResultSet). This will result in resource leaks! You should use try-with-resources whenever it's possible, because resources will be automatically closed. If it's not possible to use it in your method, you must definitely close all used resources.

Besides the above, I also notice you are casting a lot in this code. Using casts throughout your code is mostly an indication your code lacks an object-oriented design. And using casts without instanceof can be dangerous and result in a ClassCastException. You should definitely introduce some generics in your code. It will get rid of all these casts and gives you compile-time safety! And you also get less cluttered code (and that's easier to read and maintain). The more lines of code you have, the more likely to have bugs

So based on what you are trying to do, I would expect this kind of control flow

Considering your class design, you could have a DataStorage class like thisAnd this class will contain all methods which are common to all items. If some items have a different type of key, you could add an additional generic type parameter and use that as well. Then for specific Phone method implementations, you'll create a subclass of this classUsing these classes you don't need to cast anymore, because when you need a Phone object, the PhoneStorage will provide one.

Now for your Dao (DbAccess) it's very similar:Then you have an abstract class with common code for all your dao classesAnd finally you have a Dao which focuses on Phone objectsAnd then the ItemStorage class will have an instance of the appropriate ItemDao class.

Try to incorporate some of these designs into your code. I don't know of course what your application requires, but you should get some ideas and apply them to your code. This will give you a better design and that results in better readibility, maintainability and reusability. It will also makes it easier to debug your code.

Hope it helps!
Kind regards,
Roel
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And you should probably add a service layer as well if you need several calls to the database which are executed as one transaction.

And your package names are violating the naming conventions and are not very descriptive. What do you think about?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic