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

snazzyragsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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žinHead of SW DevelopmentCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.