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 Tomcat Programmer <tc...@yahoo.com> on 2011/07/20 04:42:45 UTC
nulls in prepared statement
Hi Everyone,
I've tried doing research on this on the web but can't seem to find a clear answer, I hope someone can help. I'm currently working on an application using Derby where the data being entered is optional for a lot of the fields. The columns in the tables are defined using VARCHAR data type and accept nulls. If, in the code to do the insert or update, someone leaves a field empty in the application it will result in a null value being passed through setString method like this:
pstmt.setString(1,cobj.getPartNo());
where the getPartNo() method returns null. When this happens I get a null pointer exception. Is there a configuration parameter for Derby to accept Java nulls and treat them as SQL nulls? If not, the code is going to get incredibly ugly fast if each value has to be checked for null and then issue a separate setNull() method call.
Thanks in advance,
TC
Re: nulls in prepared statement
Posted by "dev@xx" <de...@proxiflex.fr>.
Well I don't think you can redefine the classes used by the database driver.
What I use is wrapper classes for PreparedStatement instances.
BTW it has been very useful when Derby change the way they manage setBoolean when column data type is a string type.
Before 10.8.2 setBoolean was setting '0' or '1'. From 10.8.2, setBoolean set 'true' or 'false' !!
I have had to change just one line of code to keep the same behaviour.
public void setBoolean( int index, boolean value )
{
// stmt.setBoolean( index, value ) ;
stmt.setInt( index, param ? 1 : 0 ) ;
}
----- Original Message -----
From: Tomcat Programmer
To: Derby Discussion ; Tomcat Programmer
Sent: Thursday, July 21, 2011 5:31 PM
Subject: Re: nulls in prepared statement
>
> Well may be you won't have less ugly code, but at least it will be hidden ;-)
>
> I think the easiest way it to use you own PreparedStatement class. So you can do
> any special treatment or workaround in a centralized and unique place.
>
Hi JYL this is a very insightful and interesting solution, which I would not have thought of. Is this as simple as creating my own class with the derby version as its superclass and then just overriding the method? That seems too easy .. is there any catches or pitfalls you can give me a heads up on? Thanks again for your help!
Adding one specific thought: if I extend the class, how do I get my version to be instantiated? (this is a web application and so it will be picking up driver registration and so forth from JNDI.)
Re: nulls in prepared statement
Posted by Peter Ondruška <pe...@kaibo.eu>.
You would only subclass PreparedStatement as public
MyPreparedStatement extends PreparedStatement and override setString
method. And in your code use replace PrepareStatement with
MyPreparedStatement.
On Thu, Jul 21, 2011 at 5:31 PM, Tomcat Programmer
<tc...@yahoo.com> wrote:
>
>>
>> Well may be you won't have less ugly code, but at least it will be hidden
>> ;-)
>>
>> I think the easiest way it to use you own PreparedStatement class. So you
>> can do
>> any special treatment or workaround in a centralized and unique place.
>>
> Hi JYL this is a very insightful and interesting solution, which I would not
> have thought of. Is this as simple as creating my own class with the derby
> version as its superclass and then just overriding the method? That seems
> too easy .. is there any catches or pitfalls you can give me a heads up on?
> Thanks again for your help!
> Adding one specific thought: if I extend the class, how do I get my version
> to be instantiated? (this is a web application and so it will be picking up
> driver registration and so forth from JNDI.)
>
>
Re: nulls in prepared statement
Posted by Tomcat Programmer <tc...@yahoo.com>.
>
> Well may be you won't have less ugly code, but at
least it will be hidden ;-)
>
> I think the easiest way it to use you own
PreparedStatement class. So you can do
> any special treatment or workaround in a centralized and unique place.
>
Hi JYL this is a very insightful and interesting solution, which I would not have thought of. Is this as simple as creating my own class with the derby version as its superclass and then just overriding the method? That seems too easy .. is there any catches or pitfalls you can give me a heads up on? Thanks again for your help!
Adding one specific thought: if I extend the class, how do I get my version to be instantiated? (this is a web application and so it will be picking up driver registration and so forth from JNDI.)
Re: nulls in prepared statement
Posted by Tomcat Programmer <tc...@yahoo.com>.
>
> Well may be you won't have less ugly code, but at
least it will be hidden ;-)
>
> I think the easiest way it to use you own
PreparedStatement class. So you can do
> any special treatment or workaround in a centralized and unique place.
>
Hi JYL this is a very insightful and interesting solution, which I would not have thought of. Is this as simple as creating my own class with the derby version as its superclass and then just overriding the method? That seems too easy .. is there any catches or pitfalls you can give me a heads up on? Thanks again for your help!
Re: nulls in prepared statement
Posted by "dev@xx" <de...@proxiflex.fr>.
Hi,
Well may be you won't have less ugly code, but at least it will be hidden ;-)
I think the easiest way it to use you own PreparedStatement class. So you can do any special treatment or workaround in a centralized and unique place.
JYL
----- Original Message -----
From: Peter Ondruska
To: Derby Discussion ; Tomcat Programmer
Sent: Thursday, July 21, 2011 12:45 AM
Subject: Re: nulls in prepared statement
Eclipselink or Hibernate might help if you want less ugly code.
Dne 20.7.2011 23:38 "Tomcat Programmer" <tc...@yahoo.com> napsal(a):
>
>
>>
>> You must explicitly set value to null:
>>
>> if (cobj.getPartNo()==null) pstmt.setNull(1, java.sql.Types.VARCHAR);
>> else pstmt.setString(1,cobj.getPartNo());
>>
>
> Hi Peter, thanks for responding but I am aware of this as I indicated in my post. You realize how tedious this will be with any significant number of fields? What I am asking about is if there is an alternate solution.
>
> Thanks in advance,
> TC
Re: nulls in prepared statement
Posted by Peter Ondruška <pe...@kaibo.eu>.
Eclipselink or Hibernate might help if you want less ugly code.
Dne 20.7.2011 23:38 "Tomcat Programmer" <tc...@yahoo.com> napsal(a):
>
>
>>
>> You must explicitly set value to null:
>>
>> if (cobj.getPartNo()==null) pstmt.setNull(1, java.sql.Types.VARCHAR);
>> else pstmt.setString(1,cobj.getPartNo());
>>
>
> Hi Peter, thanks for responding but I am aware of this as I indicated in
my post. You realize how tedious this will be with any significant number of
fields? What I am asking about is if there is an alternate solution.
>
> Thanks in advance,
> TC
Re: nulls in prepared statement
Posted by Tomcat Programmer <tc...@yahoo.com>.
>
> You must explicitly set value to null:
>
> if (cobj.getPartNo()==null) pstmt.setNull(1, java.sql.Types.VARCHAR);
> else pstmt.setString(1,cobj.getPartNo());
>
Hi Peter, thanks for responding but I am aware of this as I indicated in my post. You realize how tedious this will be with any significant number of fields? What I am asking about is if there is an alternate solution.
Thanks in advance,
TC
Re: nulls in prepared statement
Posted by Peter Ondruška <pe...@kaibo.eu>.
You must explicitly set value to null:
if (cobj.getPartNo()==null) pstmt.setNull(1, java.sql.Types.VARCHAR);
else pstmt.setString(1,cobj.getPartNo());
On Wed, Jul 20, 2011 at 4:42 AM, Tomcat Programmer
<tc...@yahoo.com> wrote:
> Hi Everyone,
>
> I've tried doing research on this on the web but can't seem to find a clear answer, I hope someone can help. I'm currently working on an application using Derby where the data being entered is optional for a lot of the fields. The columns in the tables are defined using VARCHAR data type and accept nulls. If, in the code to do the insert or update, someone leaves a field empty in the application it will result in a null value being passed through setString method like this:
>
> pstmt.setString(1,cobj.getPartNo());
>
> where the getPartNo() method returns null. When this happens I get a null pointer exception. Is there a configuration parameter for Derby to accept Java nulls and treat them as SQL nulls? If not, the code is going to get incredibly ugly fast if each value has to be checked for null and then issue a separate setNull() method call.
>
> Thanks in advance,
> TC
>
Re: nulls in prepared statement
Posted by Tomcat Programmer <tc...@yahoo.com>.
>
> It's not an exact match, but your description sounds VERY close to
> https://issues.apache.org/jira/browse/DERBY-1938
>
> What version of Derby are you using?
>
> If you are in fact encountering DERBY-1938, can you upgrade to 10.8 and
> see if your results are better?
>
Yes Bryan, I agree it sounds close, thanks for the heads up on this. I'm using derby 10.8.1.2, so this fix alone doesn't solve it. I'm using a relatively recent 1.6 SDK, though not the absolute newest, but I would not expect that to be a factor.
>
> Can you post a full stack trace of your NullPointerException?
>
I'll get this tonight and send it along.
Re: nulls in prepared statement
Posted by Bryan Pendleton <bp...@gmail.com>.
> pstmt.setString(1,cobj.getPartNo());
>
> where the getPartNo() method returns null. When this happens I get a null pointer exception.
It's not an exact match, but your description sounds VERY close to
https://issues.apache.org/jira/browse/DERBY-1938
Can you post a full stack trace of your NullPointerException?
What version of Derby are you using?
If you are in fact encountering DERBY-1938, can you upgrade to 10.8 and
see if your results are better?
thanks,
bryan