• Post Reply Bookmark Topic Watch Topic
  • New Topic

Entity Bean problem - ejbStore

 
Michael Cleary
Ranch Hand
Posts: 93
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm working on an assignment for a J2EE class I'm taking, and I've run into a problem I don't know how to troubleshoot. This is a web application we've been working on all semester, and the latest assignment was to use an entity bean to do some simple DB manipulation (we are using MySQL). The first task is to retreive a list of all the uses in our database (grand total of 5 . Whenever I click on the button which is supposed to take me to a JSP page displaying a table of users, I get the following types of messages from Jboss and Tomcat:
From JBoss:
<snip>
21:32:35,345 ERROR [LogInterceptor] TransactionRolledbackException in method: pu
blic abstract bfpackage.ejbs.AccountTO bfpackage.ejbs.AccountBusinessMethods.get
Data() throws java.rmi.RemoteException, causedBy:
org.jboss.tm.JBossRollbackException: Unable to commit, tx=TransactionImpl:XidImp
l [FormatId=257, GlobalId=clearyxp//101, BranchQual=] status=STATUS_NO_TRANSACTI
ON; - nested throwable: (javax.ejb.EJBException: Account mdcleary failed to save
to database: bfpackage.bfExceptions.DAOException: Account update failed: java.s
ql.SQLException: Syntax error or access violation, message from server: "You ha
ve an error in your SQL syntax. Check the manual that corresponds to your MySQL
server version for the right syntax to use near 'role = 'General'WHERE name = '
mdcleary'' at line 1")
at org.jboss.tm.TransactionImpl.commit(TransactionImpl.java:413)
at org.jboss.ejb.plugins.TxInterceptorCMT.endTransaction....
......<more stuff>
at java.lang.Thread.run(Thread.java:534)
Caused by: javax.ejb.EJBException: Account mdcleary failed to save to database:
bfpackage.bfExceptions.DAOException: Account update failed: java.sql.SQLExceptio
n: Syntax error or access violation, message from server: "You have an error in
your SQL syntax. Check the manual that corresponds to your MySQL server versio
n for the right syntax to use near 'role = 'General'WHERE name = 'mdcleary'' at
line 1"
at bfpackage.ejbs.AccountBean.ejbStore(AccountBean.java:278)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:39)
</snip>
I get similar messages in the Tomcat window. I have checked my SQL syntax and I can't find anything wrong w/ it. ejbStore calls an "update" method which resides in one of my DAO classes. The SQL statement it seems to be complaining about is in the following snippet of code:
......
Connection dbConnection = MySqlDAOFactory.getConnection();

Statement statement = null;

try
{
String strSql = "UPDATE user SET " +
"name = " + DBUtil.createSqlField(account.getID()) +
"password = " + DBUtil.createSqlField(account.getPassword()) +
"role = " + DBUtil.createSqlField(account.getRole()) +
"WHERE name = " + DBUtil.createSqlField(account.getID());

statement = dbConnection.createStatement();
int result = statement.executeUpdate(strSql);
.....etc.
If anyone has any idea where things might be going wrong, I would really appreciate it.
TIA,
Mike
 
Avi Abrami
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Mike,

I have checked my SQL syntax and I can't find anything wrong w/ it.

It does appear wrong to me. String literals in SQL need to be enclosed in single quotes. I assume that columns "name", "password" and "role" are character columns (that is, they have a CHAR -- or VARCHAR -- data type). If they do, then the values you assign to them (in the UPDATE statement) need to be enclosed in single quotes -- which I didn't see in your post.
Personally, though, I prefer using a PreparedStatement, which relieves me of the need to add enclosing, single quotes.
Good Luck,
Avi.
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Michael Cleary:
near 'role = 'General'WHERE name = 'mdcleary''

As can be seen from the error message, the helper method DBUtil.createSqlField() does seem to add the enclosing quotes. The problem with your SQL is that you need a space between "'<role>'" and the following "WHERE ..." clause.
You can fix it by changing the line
"WHERE name = " + ...
to
" WHERE name = " + ...
Hope that helps!
 
Michael Cleary
Ranch Hand
Posts: 93
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks all!
The way it turned out, all I needed was two commas:
try
{
String strSql = "UPDATE user SET " +
"name = " + DBUtil.createSqlField(account.getID()) +
", password = " + DBUtil.createSqlField(account.getPassword()) +
", role = " + DBUtil.createSqlField(account.getRole()) +
"WHERE name = " + DBUtil.createSqlField(account.getID());
That took care of it. I had copied this code from a method in another of my DAO classes which, as it turns out, had not been called before - so I never realized there was a problem with it!
Thanks again,
Mike
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!