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 Alan Burlison <Al...@sun.com> on 2007/10/09 15:52:33 UTC
Returning NULL from a database-side Java function
I have a Java database-side function that does a simple lookup of a
string on a reference table and returns the corresponding integer. If
the string can't be found I want to return NULL, but if I return a Java
null I get a database exception rather than the desired behaviour:
Error: java.sql.SQLException: The exception
'java.lang.NullPointerException' was thrown while evaluating an
expression., SQL State: 38000, Error Code: -1
How do I return a 'database' NULL from a database-side Java function?
Thanks,
--
Alan Burlison
--
Re: Returning NULL from a database-side Java function
Posted by Rick Hillegas <Ri...@Sun.COM>.
Alan Burlison wrote:
> Rick Hillegas wrote:
>
>> It is hard to say where the NullPointerException originates. Could
>> you post a reproducible test case and the full stack trace from
>> derby.log?
>
> Umm, it may be my bad, I have the following:
>
> public static int iGoBang() {
> int pop = 0 == 0 ? 1 : null;
> return pop;
> }
>
> Which compiles but blows up, whereas
>
> public static int iGoBang() {
> return null;
> }
>
> won't even compile. Because you can't assign null to a primitive type
> - duh. Which leaves 2 questions:
>
> 1. Why does int pop = 0 == 0 ? 1 : null; even compile?
I find that this does not compile when I use the jdk 1.4 javac but it
succeeds when I use the Java 5 compiler. I'm guessing this is some sort
of change introduced by autoboxing.
> 2. How *do* you return a database NULL from a function with a
> primitive type as the return type? String isn't a primitive type.
You should be able to use a method which returns Integer instead of int.
However, I am finding that Derby is not binding a function invocation to
such a method. I have logged DERBY-3119 to track this issue. Hopefully
someone will close the bug quickly, pointing out some pilot error on my
part.
Regards,
-Rick
Re: Returning NULL from a database-side Java function
Posted by Alan Burlison <Al...@sun.com>.
Alan Burlison wrote:
> So it is autoboxing to Integer, then calling intValue(). This sure
> looks like a Java bug to me, I've asked internally to see if anyone can
> confirm, and if so I'll raise a bug against javac.
The relevant part of the Java spec is here:
http://java.sun.com/docs/books/jls/third_edition/html/expressions.html#15.25
To paraphrase, the compiler is using autoboxing to convert '1' and
'null' to the same type (Integer), then using intValue() to convert back
to an int.
It's probably not an out-and-out bug, more like a dark corner of
Autoboxing :-)
--
Alan Burlison
--
Re: Returning NULL from a database-side Java function
Posted by Alan Burlison <Al...@sun.com>.
Knut Anders Hatlen wrote:
>>> 1. Why does int pop = 0 == 0 ? 1 : null; even compile?
>> Maybe the compiler figures out that it's the same as pop = 1 so it
>> discards the null? Seems like it shouldn't compile.
>
> Or could it be auto-boxing that makes it compilable? This equivalent
> code (which is what auto-boxing would rewrite it to, I think) should
> compile:
>
> int pop = 0 == 0 : 1 : ((Number) null).intValue();
Close, very close ;-) Here's what happens:
int i = 0 == 0 ? null : 1;
0: aconst_null
1: checkcast #2; //class java/lang/Integer
4: invokevirtual #3; //Method java/lang/Integer.intValue:()I
7: istore_1
8: return
So it is autoboxing to Integer, then calling intValue(). This sure
looks like a Java bug to me, I've asked internally to see if anyone can
confirm, and if so I'll raise a bug against javac.
--
Alan Burlison
--
Re: Returning NULL from a database-side Java function
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
ty den 09.10.2007 klokka 09:01 (-0700) skreiv Daniel John Debrunner:
> Alan Burlison wrote:
> > Rick Hillegas wrote:
> >
> >> It is hard to say where the NullPointerException originates. Could you
> >> post a reproducible test case and the full stack trace from derby.log?
> >
> > Umm, it may be my bad, I have the following:
> >
> > public static int iGoBang() {
> > int pop = 0 == 0 ? 1 : null;
> > return pop;
> > }
> >
> > Which compiles but blows up, whereas
> >
> > public static int iGoBang() {
> > return null;
> > }
> >
> > won't even compile. Because you can't assign null to a primitive type -
> > duh. Which leaves 2 questions:
> >
> > 1. Why does int pop = 0 == 0 ? 1 : null; even compile?
>
> Maybe the compiler figures out that it's the same as pop = 1 so it
> discards the null? Seems like it shouldn't compile.
Or could it be auto-boxing that makes it compilable? This equivalent
code (which is what auto-boxing would rewrite it to, I think) should
compile:
int pop = 0 == 0 : 1 : ((Number) null).intValue();
--
Knut Anders
Re: Returning NULL from a database-side Java function
Posted by Thomas Nielsen <Th...@Sun.COM>.
Bryan Pendleton wrote:
>>>> 1. Why does int pop = 0 == 0 ? 1 : null; even compile?
>
> For what it's worth, it doesn't compile for me:
>
works with 1.5.0-13:
C:\Program Files\Java\jdk1.5.0_13\bin>javac c:\tmp\pop.java
C:\Program Files\Java\jdk1.5.0_13\bin>
even with 1.5.0-07:
C:\Program Files\Java\jdk1.5.0_07\bin>javac c:\tmp\pop.java
C:\Program Files\Java\jdk1.5.0_07\bin>
1.6.0-03 works as well:
C:\Program Files\Java\jdk1.6.0_03\bin>javac c:\tmp\pop.java
C:\Program Files\Java\jdk1.6.0_03\bin>
but 1.4.2-15 does not:
C:\Program Files\Java\jdk1.4.2_15\bin>javac c:\tmp\pop.java
c:\tmp\pop.java:5: incompatible types
found : <nulltype>
required: int
int pop = 0 == 0 ? 1 : null;
^
1 error
C:\Program Files\Java\jdk1.4.2_15\bin>
Ooops - didn't see Ricks reply until now...
Certainly something introduced in Java 5.
Thomas
Re: Returning NULL from a database-side Java function
Posted by Bryan Pendleton <bp...@amberpoint.com>.
>>> 1. Why does int pop = 0 == 0 ? 1 : null; even compile?
For what it's worth, it doesn't compile for me:
C:\bryan\src\java\pop>java -version
java version "1.4.2_05"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_05-b04)
Java HotSpot(TM) Client VM (build 1.4.2_05-b04, mixed mode)
C:\bryan\src\java\pop>type pop.java
class pop
{
void x()
{
int pop = 0 == 0 ? 1 : null;
}
}
C:\bryan\src\java\pop>javac pop.java
pop.java:5: incompatible types
found : <nulltype>
required: int
int pop = 0 == 0 ? 1 : null;
^
1 error
thanks,
bryan
Re: Returning NULL from a database-side Java function
Posted by Alan Burlison <Al...@sun.com>.
Daniel John Debrunner wrote:
>> won't even compile. Because you can't assign null to a primitive type
>> - duh. Which leaves 2 questions:
>>
>> 1. Why does int pop = 0 == 0 ? 1 : null; even compile?
>
> Maybe the compiler figures out that it's the same as pop = 1 so it
> discards the null? Seems like it shouldn't compile.
It even compiles if the contitional isn't a constant expression - most
odd...
>> 2. How *do* you return a database NULL from a function with a
>> primitive type as the return type? String isn't a primitive type.
>
> You need to use the corresponding object type, in this case
> java.lang.Integer as the return type.
I guessed that might be the case, but haven't had chance to try it -
thanks for the confirmation.
--
Alan Burlison
--
Re: Returning NULL from a database-side Java function
Posted by Daniel John Debrunner <dj...@apache.org>.
Alan Burlison wrote:
> Rick Hillegas wrote:
>
>> It is hard to say where the NullPointerException originates. Could you
>> post a reproducible test case and the full stack trace from derby.log?
>
> Umm, it may be my bad, I have the following:
>
> public static int iGoBang() {
> int pop = 0 == 0 ? 1 : null;
> return pop;
> }
>
> Which compiles but blows up, whereas
>
> public static int iGoBang() {
> return null;
> }
>
> won't even compile. Because you can't assign null to a primitive type -
> duh. Which leaves 2 questions:
>
> 1. Why does int pop = 0 == 0 ? 1 : null; even compile?
Maybe the compiler figures out that it's the same as pop = 1 so it
discards the null? Seems like it shouldn't compile.
> 2. How *do* you return a database NULL from a function with a primitive
> type as the return type? String isn't a primitive type.
You need to use the corresponding object type, in this case
java.lang.Integer as the return type.
Dan.
Re: Returning NULL from a database-side Java function
Posted by Alan Burlison <Al...@sun.com>.
Rick Hillegas wrote:
> It is hard to say where the NullPointerException originates. Could you
> post a reproducible test case and the full stack trace from derby.log?
Umm, it may be my bad, I have the following:
public static int iGoBang() {
int pop = 0 == 0 ? 1 : null;
return pop;
}
Which compiles but blows up, whereas
public static int iGoBang() {
return null;
}
won't even compile. Because you can't assign null to a primitive type -
duh. Which leaves 2 questions:
1. Why does int pop = 0 == 0 ? 1 : null; even compile?
2. How *do* you return a database NULL from a function with a primitive
type as the return type? String isn't a primitive type.
--
Alan Burlison
--
Re: Returning NULL from a database-side Java function
Posted by Rick Hillegas <Ri...@Sun.COM>.
Alan Burlison wrote:
> I have a Java database-side function that does a simple lookup of a
> string on a reference table and returns the corresponding integer. If
> the string can't be found I want to return NULL, but if I return a
> Java null I get a database exception rather than the desired behaviour:
>
> Error: java.sql.SQLException: The exception
> 'java.lang.NullPointerException' was thrown while evaluating an
> expression., SQL State: 38000, Error Code: -1
>
> How do I return a 'database' NULL from a database-side Java function?
>
> Thanks,
>
Hi Alan,
It is hard to say where the NullPointerException originates. Could you
post a reproducible test case and the full stack trace from derby.log?
In general, you should be able to return null from user-written
functions. The following experiment on the 10.4 mainline shows this:
First I write a simple class:
public class z
{
public static String returnsNull()
{ return null; }
}
Then I register and run the function under ij:
ij> create function returnsNull()
returns varchar( 30 )
language java
parameter style java
no sql
external name 'z.returnsNull'
;
0 rows inserted/updated/deleted
ij> values ( returnsNull() );
1
--------------------------------------------------------------------------------------------------------------------------------
NULL
1 row selected
Regards,
-Rick