db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: setting column to NULL
Date Wed, 20 Jun 2012 17:09:58 GMT
On 6/20/12 9:55 AM, Pavel Bortnovskiy wrote:
>
> Hello,
>
> It seems that running the following SQL statement results in an error 
> in Derby, while it’s working fine in other DBs (such as Oracle or Sybase):
>
> select
>
> a as “ColumnA”,
>
> null as “ColumnB”
>
> from SomeTable
>
> java.sql.SQLSyntaxErrorException: Syntax error: Encountered "null" at 
> line 10, column 2.
>
> at 
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown 
> Source)
>
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
>
> at 
> org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown 
> Source)
>
> at 
> org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown 
> Source)
>
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown 
> Source)
>
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown 
> Source)
>
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown 
> Source)
>
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown 
> Source)
>
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown 
> Source)
>
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown 
> Source)
>
> at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown 
> Source)
>
> at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown 
> Source)
>
> at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown 
> Source)
>
> Is this by design? And is there a work-around? What needs to be done 
> to set a column to null?
>
Hi Pavel,

Derby does not have a default datatype for untyped nulls. You need to 
use a cast clause in order to give the null a type. Here's an example:

connect 'jdbc:derby:memory:db;create=true';

create table t( a int );
insert into t( a ) values ( 1 );

-- untyped null fails
select null as b from t;

-- typed null succeeds
select cast( null as int ) as b from t;

Hope this helps,
-Rick
>
> Thank you,
>
> Pavel.
>
>
>             Jefferies archives and monitors outgoing and incoming
>             e-mail. The contents of this email, including any
>             attachments, are confidential to the ordinary user of the
>             email address to which it was addressed. If you are not
>             the addressee of this email you may not copy, forward,
>             disclose or otherwise use it or any part of it in any form
>             whatsoever. This email may be produced at the request of
>             regulators or in connection with civil litigation.
>             Jefferies accepts no liability for any errors or omissions
>             arising as a result of transmission. Use by other than
>             intended recipients is prohibited. In the United Kingdom,
>             Jefferies operates as Jefferies International Limited;
>             registered in England: no. 1978621; registered office:
>             Vintners Place, 68 Upper Thames Street, London EC4V 3BJ.
>             Jefferies International Limited is authorised and
>             regulated by the Financial Services Authority.
>


Mime
View raw message