Unable to update the value to table in database

Question

I am using ajax to pass in some values to a function to update a table in MySQL. Function is very simple and short like below:

public void insert_update(String nodeid,String ts,Date startdt,Date enddt,int enable) {
    try {
             String sql = "UPDATE urllink SET STARTDT=" + startdt
                        + ",ENDDT=" + enddt
                        + ",ENABLE=" + enable
                        + " WHERE URL='f0=" + nodeid
                        + "&ts=" + ts + "'";

             em.createNativeQuery(sql).executeUpdate();
         } catch (Exception e) {
             e.printStackTrace();
         }
}

When I execute the code,it works fine but I checked my db, and it's not updating the values in the table. So what I tried is to debug and get the SQL statement since there seems to be a lot of parameters going on there.

This is the query being passed:

UPDATE urllink SET STARTDT=Tue Mar 26 00:00:00 SGT 2019,ENDDT=Sun Mar 31 00:00:00 SGT 2019,ENABLE=1 WHERE URL='f0=27484&ts=458a903bba97b49a485901165bb'

From the looks of it, I don't see anything wrong,everything seems okay.

I can't figure out why it didn't update into the table.

Edit

From further checking, it seems to tell me that :

Error:No value specified for parameter 1

although I did pass in nodeid value so how could this be happening?

Use [prepared statement to avoid SQL injection](https://stackoverflow.com/a/1812920/10195153) and you won't have this problem. — danblack, Mar 26 '19 at 04:57
Can you check if `&` sign is causing any issue.I think you want to use key word `AND` not the sign `&`. — RAHUL ROY, Mar 26 '19 at 05:00
MySQL has a [specific date format](https://dev.mysql.com/doc/refman/5.7/en/date-and-time-types.html) (in addition to `use prepared statements`) — danblack, Mar 26 '19 at 05:13
@danblack I might have misunderstood it. You can insert a String value in format `yyyy-mm-dd` to Date column in the table as it only accepts this format — Daredevil, Mar 26 '19 at 05:14
Take a peek at `String.format` string interpolation will help you will a lot of these types of problems. — Sam Orozco, Apr 23 '19 at 21:51

Unable to update the value to table in database

Edit

0 Answers0