You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Dyre Tjeldvoll <Dy...@oracle.com> on 2015/02/01 11:07:51 UTC
Re: Initializing a new record where field is NULL by default
> On 31. jan. 2015, at 20.22, Bob M <rg...@orcon.net.nz> wrote:
>
> Hi Dyre
>
> I am still not grasping this.................
>
> I have
> psInsert = conn.prepareStatement("INSERT INTO TABLE VALUES(?, ?......, ?)
> [27 of them]
> psInsert.setString(1, date);
> ................
> psInsert.setString(25, class);
> psInsert.setString(26, Trade_ID);
>
> but I am unclear what to put for the final line referring to Profit and
> where I wish to set it to NULL initially which is the default
There are two different strategies here:
1) Just insert an SQL NULL manually:
psinsert.setNull(<number>, java.sql.Types.DOUBLE); // See javadoc for PreparedStatement
or, psinsert.setNull(“PROFIT_LOSS”, java.sql.Types.DOUBLE)
2) Change the definition of the table:
CREATE TABLE T(….., PROFIT_LOSS DOUBLE DEFAULT NULL, …)
then when inserting
INSERT INTO T(<col1>, <col2>, …, <col after PROFIT_LOSS>, <more columns>,…) VALUES (?,?,… ?)
You have to spell out the columns you are inserting into, unless the columns with default values are the last columns in the table. In that case you can just omit them in the insert
>
> Bob M
>
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/Initializing-a-new-record-where-field-is-NULL-by-default-tp143732p143735.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Initializing a new record where field is NULL by default
Posted by Bob M <rg...@orcon.net.nz>.
Thank's Bryan for that information...............
I have sorted it out now
The profit field is a dec(5,2) field and so I am using DEFAULT 999.99
Then when I wish to update the latest record with the profit I use a WHERE
profit=999.99
All good :)
Thanks
Bob M
--
View this message in context: http://apache-database.10148.n7.nabble.com/Initializing-a-new-record-where-field-is-NULL-by-default-tp143732p143738.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: Initializing a new record where field is NULL by default
Posted by Dyre Tjeldvoll <Dy...@oracle.com>.
On 02/01/2015 05:42 PM, Bryan Pendleton wrote:
> > You have to spell out the columns you are inserting into,
> > unless the columns with default values are the last columns
> > in the table. In that case you can just omit them in the insert
> >
>
> I'm pretty sure it's a bit more flexible than that. I believe
> that if you don't say NOT NULL, and you don't have a DEFAULT
> clause, then DEFAULT NULL is automatic.
>
> And I believe you can name the columns in any order (so long
> as they line up with the VALUES values), so they don't have
> to be the last columns in the table.
>
> So you can INSERT INTO T (c, f, a, g, b ) VALUES ( .. )
> and columns d and e and h will get NULL values since they
> weren't mentioned in the insert.
That is indeed much more convenient. Thanks for pointing that out :)
--
Regards,
Dyre
Re: Initializing a new record where field is NULL by default
Posted by Bryan Pendleton <bp...@gmail.com>.
> You have to spell out the columns you are inserting into,
> unless the columns with default values are the last columns
> in the table. In that case you can just omit them in the insert
>
I'm pretty sure it's a bit more flexible than that. I believe
that if you don't say NOT NULL, and you don't have a DEFAULT
clause, then DEFAULT NULL is automatic.
And I believe you can name the columns in any order (so long
as they line up with the VALUES values), so they don't have
to be the last columns in the table.
So you can INSERT INTO T (c, f, a, g, b ) VALUES ( .. )
and columns d and e and h will get NULL values since they
weren't mentioned in the insert.
thanks,
bryan