Link to home
Start Free TrialLog in
Avatar of snazzyrags
snazzyrags

asked on

Statement.executeUpdate() not working

I am executing the following code snippet:
...
System.out.println("SQL statement=>"+sqlStatement);
            // execute sql update and close database connection
            s.executeUpdate(sqlStatement);
            
            s.close();
            con.close();
...

The println() function results in:
SQL statement=>INSERT INTO item (sku, description, dept, price, qoh, deal) VALUES ('3010002699', 'test item', '9999', '4.99', '15', '3/9.99');

I receive the following error:
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 version for the right syntax to use near '="3010002699"' at line 1"

Any ideas? Need more info? - please ask.
Doug

Avatar of Mick Barry
Mick Barry
Flag of Australia image

does the update work if u enter it directly into mysql?
what are the types of the columns?
Avatar of snazzyrags
snazzyrags

ASKER

The update does work if entered directly into mySql.
Also, the update does work! The record is added to the DB correctly. But the exception blows me out of the water.
The field types vary - character, int, decimal.
ok, dont use quotes for int and decimal...
use quotes only for char and then try it will work....

INSERT INTO item (sku, description, dept, price, qoh, deal) VALUES ('3010002699', 'test item', 9999, 4.99, 15, '3/9.99');
is sku a String field?

you may need to lose the quotes from around that value too...
ASKER CERTIFIED SOLUTION
Avatar of smueller
smueller

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>> ... for the right syntax to use near '="3010002699"' at line 1"

This error is talking about the use of

         ="3010002699"

I don't see that in
>> SQL statement=>INSERT INTO item (sku, description, dept, price, qoh, deal) VALUES ('3010002699', 'test item', '9999', '4.99', '15', '3/9.99');

Are you sure the error is about this?

If the output is exactly:

SQL statement=>INSERT INTO item (sku, description, dept, price, qoh, deal) VALUES ('3010002699', 'test item', '9999', '4.99', '15', '3/9.99');

Then the problem might also be the ';' at the end.  If its really in sqlStatement,  remove it and see if that changes thinigs.
> Then the problem might also be the ';' at the end.

I doubt it...  I think kiranhk is right, and it is the quotes round numeric values...
A lot better way to use statements is through prepared statements. Where you declare your input params with '?' and the later you replace them with appropriate argument - so you don't have to worry about right string representation of sql query statement. Since you don't want to code everything always from scratch, make yourself some sql templates.

Here is an example from Spring framework (which has all this things already done :-):

      protected int update(final PreparedStatementCreator psc, final PreparedStatementSetter pss)
                  throws DataAccessException {
            if (logger.isDebugEnabled()) {
                  String sql = getSql(psc);
                  logger.debug("Executing SQL update" + (sql != null ? " [" + sql  + "]" : ""));
            }
            Integer result = (Integer) execute(psc, new PreparedStatementCallback() {
                  public Object doInPreparedStatement(PreparedStatement ps) throws SQLException {
                        try {
                              if (pss != null) {
                                    pss.setValues(ps);
                              }
                              int rows = ps.executeUpdate();
                              if (logger.isDebugEnabled()) {
                                    logger.debug("SQL update affected " + rows + " rows");
                              }
                              return new Integer(rows);
                        }
                        finally {
                              if (pss instanceof ParameterDisposer) {
                                    ((ParameterDisposer) pss).cleanupParameters();
                              }
                        }
                  }
            });
            return result.intValue();
      }

public interface PreparedStatementCreator {
      PreparedStatement createPreparedStatement(Connection con) throws SQLException;
}

public interface PreparedStatementSetter {
      void setValues(PreparedStatement ps) throws SQLException;
}

public interface PreparedStatementCallback {
      Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException;
}

public interface ParameterDisposer {
      public void cleanupParameters();
}
what was the problem then?
Don't you guys ever sleep?!?!

smueller had the solution(?) when he suggested I revisit the stack trace. Sure enough, I wasn't looking at the problem statement. This statement is working perfectly, which I knew, except for the exception, but a bug further down in my code was throwing me into another method that was doing a query without initializing the db field to be queried.

Thanks smueller!

>  Don't you guys ever sleep?!?!

Mostly at night...  Mostly...

;-)

Cool...glad you got it fixed :-)

Good luck!

Tim