Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to set a formula in a JDBC PreparedStatement

I have a PreparedStatement that I use to insert a bunch of rows with a lot of column data. However, for one of the columns (a Timestamp), if the value is null, I want it to execute getDate() on the server.

I understand that I could just do new Date() but that's not really going to work, because, due to legacy reports, it needs to match exactly another column that has the value automatically inserted.

Let's also say I can't change the schema and add a default value to the column.

Is there any way I can set a formula as a parameter in the PreparedStatement?

Edit to Add: Here's essentially what it is currently:

PreparedStatement statement = connection.prepareStatement("insert into row (name, starttime) values (?, ?)")
statement.setString(1, name);
statement.setDate(2, date);

What I want is, something like:

PreparedStatement statement = connection.prepareStatement("insert into row (name, starttime) values (?, ?)")
statement.setString(1, name);
if(date != null)
  statement.setDate(2, date);
else
  statement.setFormula(2, "getdate()");

I understand that:

if(date == null)
  date = new Date();

is essentially the same thing, but in our case, it's really not.

like image 280
Reverend Gonzo Avatar asked Feb 24 '23 18:02

Reverend Gonzo


1 Answers

Does it work to simply prepare a statement of the form

insert into row(name, starttime)
   values(?, coalesce(?, getdate())

? and then setting a null 2nd parameter should cause a getdate() call?

like image 150
araqnid Avatar answered Feb 26 '23 11:02

araqnid