Insert record using default value

[Paul]

Hi,
I use ResultSet to insert record to a table.
The table has several fields with default value.
When I do insert, I do not fill value for those default values.
I expect the inserted record will have value from the default values,
but it's not, still NULL.
Is this bugs?
I read from JDBC tutorial about insert record using moveToInsertRow it
said something like this:
"You might also wonder what happens if you insert a row without
supplying a value for every column in the row. If a column has a
default value or accepts SQL NULL values, you can get by with not
supplying a value. If a column does not have a default value, you will
get an SQLException if you fail to set a value for it. You will also
get an SQLException if a required table column is missing in your
ResultSet object."
Isn't it means if default value even the field is not filled, it
should use the default value?


Example:
Table Customer
Fields:
- CustomerId INT AUTO_INCREMENT PRIMARY KEY
- CustomerCode VARCHAR
- CustomerName VARCHAR
- LastModified TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Connection conn;
String SQL = "SELECT * FROM Customer";
PreparedStatement ps = conn.prepareStatement(SQL,
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = ps.executeQuery();
rs.moveToInsertRow();
rs.updateString("CustomerCode", "001");
rs.updateString("CustomerName", "AAA");
rs.insertRow();
rs.close();

The result:
CustomerId = 1 (auto filled)
CustomerCode = 001
CustomerName = AAA
LastModified = NULL (I expect it to fill the default value, the
current timestamp)

If it is default behaviour of H2, is there a way to change this
behaviour, using system property or anything?

Thanks

[Thomas Mueller]

Hi,

I didn't know about this. I consider this a bug, and will fix it in the next release, that is, use the default value instead of NULL. This is how MySQL, PostgreSQL, and Apache Derby work according to my tests. Please note some databases require all columns to be set (for example HSQLDB).

Regards,

Thomas

[Paul]

Thanks Thomas,
Looks forward for the next version.
So in the next version, in my example above, the LastModified would be
the default value (current timestamp), correct?

On Oct 21, 1:10 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

[Thomas Mueller]

Hi,

> So in the next version, in my example above, the LastModified would be
> the default value (current timestamp), correct?

Yes.

Regards,
Thomas

[Tim Haley]

Thomas,

If the LastModified field were explicitly set to NULL, would the default value still be used? I believe that it should be. 

Especially if the field were specified as: TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP.

Also, when is the next version that will include this expected to be available?

Thanks,

Tim

[Rami Ojares]

If you explicitly say to the system that you want something to be "X"
then I don't think it is very clever if the system makes it "Y".
It may be useful shortcut in your particular usecase but is not logical
from a more generic point of view.

- rami

[Thomas Mueller]

Hi,

If the LastModified field were explicitly set to NULL, would the default value still be used? I believe that it should be. 

Especially if the field were specified as: TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP.

No, I believe it will be set to NULL.

Also, when is the next version that will include this expected to be available?

There is a release every few weeks. So this year.

Regards,

Thomas

[Rami Ojares]

Hi Tim,

You could try to define your column in the following way to get what you
want

create table t1(a int as casewhen(a is null, 1, a))

Now column a is not defined as not null but it can never get any null
values and thus in practice is "not null".
And you can insert nulls but get the default value instead (which in my
example is 1).

- rami