JDBC SQL prepared statement calling function but getting expression error

Question

I'm trying to insert a TIMESTAMP into a table, but the timestamp inserted will always be X days away from now. Here is my code:

ps = c.prepareStatement("INSERT INTO tablename (uuid, pool, expires) VALUES (?, ?, DATE_ADD(NOW(), INTERVAL ? DAY)");
ps.setString(1, player.getUniqueId().toString());
ps.setInt(2, plugin.s.DEFAULT_POOL);
ps.setInt(3, plugin.s.PAYMENT_DAYS);

But when this executes a syntax error occurs. If there is a better method for doing this, or a fix for this, I'd like to know. Thanks!

what exactly is the syntax error, and what DB is this for? I'm guessing mysql... — Marc B, Jul 17 '15 at 19:36
You will have to replace **DATE_ADD(NOW(), INTERVAL ? DAY)** with java code. — Norbert, Jul 17 '15 at 19:37
please post all the relevant code, the stack trace and the table structure. — cнŝdk, Jul 17 '15 at 19:37

cнŝdk · Accepted Answer · 2015-07-17T20:24:58.890

3

First of all note that your query is missing a closing ) at the end, the following:

VALUES (?, ?, DATE_ADD(NOW(), INTERVAL ? DAY)

Should be:

VALUES (?, ?, DATE_ADD(NOW(), INTERVAL ? DAY))

Then may be you need to use a new Date() and add to it the PAYMENT_DAYS for the date type, try the following:

public static Date addDaysToCurrentDate(int days) {
     SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
     Calendar c = Calendar.getInstance();
     c.setTime(new Date()); 
     c.add(Calendar.DATE, days); 
     return sdf.parse(c.getTime());
}

ps = c.prepareStatement("INSERT INTO tablename (uuid, pool, expires) VALUES (?, ?, ?)");
ps.setString(1, player.getUniqueId().toString());
ps.setInt(2, plugin.s.DEFAULT_POOL);
ps.setInt(3, addDaysToCurrentDate(plugin.s.PAYMENT_DAYS));

edited Jul 17 '15 at 20:24

answered Jul 17 '15 at 19:43

cнŝdk

31,391
7
56
78

That PAYMENT_DAYS is an integer number of days, not a date object. – RealSkeptic Jul 17 '15 at 19:44
Ah Ok I see, in that case we just need to convert it to date, I am Editing my answer. – cнŝdk Jul 17 '15 at 19:47
3

I would just wait for the OP to fix the parenthesis. I'm not sure the problem goes beyond that. – RealSkeptic Jul 17 '15 at 19:49
Yes I think, the missing parentheses may be the cause of the problem. – cнŝdk Jul 17 '15 at 19:51
So I added the parenthesis, and the syntax error went away. However, the entry still shows the timestamp the entry was made, not X days in the future, like I wanted. – Pocketkid2 Jul 17 '15 at 20:40
Now the weirdest part so far is that that exact command, put in a raw sql script, with the prepare and execute statements, worked perfectly for me. I just need to figure out what's wrong with my JDBC code... – Pocketkid2 Jul 17 '15 at 20:52

score 0 · Answer 2 · answered Jul 17 '15 at 20:39

0

try using select instead of values

insert into ... select ?,?, DATE_ADD(NOW(), INTERVAL ? DAY))

or replace with java code as suggested:

Calendar c = Calendar.getInstance();
c.setTime(new Date()); 
c.add(Calendar.DATE, days); 
ps.setDate(3, new java.sql.Date(c.getTime().getTime()));

answered Jul 17 '15 at 20:39

Yurii K

162
1
5

Does java.sql.Date have both time and date parts? I want to have both. – Pocketkid2 Jul 17 '15 at 20:41
@Pocketkid2 See: [java.util.Date vs java.sql.Date](http://stackoverflow.com/q/2305973/642706) – Basil Bourque Jul 18 '15 at 04:11

score 0 · Answer 3 · edited Mar 17 '21 at 05:55

0

ps = c.prepareStatement("INSERT INTO tablename (uuid, pool, expires) VALUES (?, ?, DATE_ADD(NOW(), INTERVAL ? DAY)");

? DAY, this question mark needs to be an integer. You need to calculate.

edited Mar 17 '21 at 05:55

Suraj Rao

29,388
11
94
103

answered Mar 17 '21 at 05:46

Ewaner

11
3

JDBC SQL prepared statement calling function but getting expression error

3 Answers3