[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1293
  • Last Modified:

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

0
snazzyrags
Asked:
snazzyrags
1 Solution
 
objectsCommented:
does the update work if u enter it directly into mysql?
what are the types of the columns?
0
 
snazzyragsAuthor Commented:
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.
0
 
kiranhkCommented:
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');
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
TimYatesCommented:
is sku a String field?

you may need to lose the quotes from around that value too...
0
 
smuellerCommented:
Are you sure that the syntax error is given for that piece of code above?  When you see the stack trace, does it reference that exact line number for this statement:
s.executeUpdate(sqlStatement);
0
 
zzynxSoftware engineerCommented:
>> ... 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?

0
 
Dejan PažinCommented:
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.
0
 
TimYatesCommented:
> 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...
0
 
drjustinCommented:
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();
}
0
 
TimYatesCommented:
what was the problem then?
0
 
snazzyragsAuthor Commented:
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!

0
 
TimYatesCommented:
>  Don't you guys ever sleep?!?!

Mostly at night...  Mostly...

;-)

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

Good luck!

Tim
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now