 |
AppletTalk.com
Java discussions newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Ian deSouza
Guest
|
 Posted: Thu Apr 01, 2004 4:43 pm Post subject: prepared statement : finding records with null values in whe |
 |
|
OK,
After reviewing some articles, it appears that we can't use
"select * from mytable where mycol = ?"
when we >>might<< have a null value for mycol and we have to use a
variant of the sql "mycol is null" when the value were setting is
null.
The problem I'm having is that for my many table, each may have many
columns in the where clause, where any column can be null (can you see
the permutations growing)..
Any ideas on how to do this with PreparedStatement generically
(database neutral).
Thanks in advance, Ian
|
|
| Back to top |
|
 |
Andree Große
Guest
|
| Posted: Fri Apr 02, 2004 8:35 am Post subject: Re: prepared statement : finding records with null values in |
|
|
Ian deSouza wrote:
| Quote: | OK,
After reviewing some articles, it appears that we can't use
"select * from mytable where mycol = ?"
when we >>might<< have a null value for mycol and we have to use a
variant of the sql "mycol is null" when the value were setting is
null.
The problem I'm having is that for my many table, each may have many
columns in the where clause, where any column can be null (can you see
the permutations growing)..
Any ideas on how to do this with PreparedStatement generically
(database neutral).
|
where (field1 like '%something%' or filed1 is null)
and (field2 like '%another thing%' or filed2 is null)
and ...
or ...
HTH A.G.
|
|
| Back to top |
|
|
Robert Klemme
Guest
|
| Posted: Fri Apr 02, 2004 8:56 am Post subject: Re: prepared statement : finding records with null values in |
|
|
"Andree Große" <user (AT) example (DOT) net> schrieb im Newsbeitrag
news:c4j8ku$o93$1 (AT) ppd00021 (DOT) deutschepost.de...
| Quote: | Ian deSouza wrote:
OK,
After reviewing some articles, it appears that we can't use
"select * from mytable where mycol = ?"
when we >>might<< have a null value for mycol and we have to use a
variant of the sql "mycol is null" when the value were setting is
null.
The problem I'm having is that for my many table, each may have many
columns in the where clause, where any column can be null (can you see
the permutations growing)..
Any ideas on how to do this with PreparedStatement generically
(database neutral).
where (field1 like '%something%' or filed1 is null)
and (field2 like '%another thing%' or filed2 is null)
and ...
or ...
HTH A.G.
|
I don't think that's what the OP wanted. The only idea I have looks quite
ugly:
"select * from mytable where ( ( 1 = ? and mycol = ? ) or ( 0 = ? and
mycol is null ) ) and ..."
Continue for other columns as well. Use 1 for parameter 1 and 3 if you
want to provide a value, use 0 if you want to check for NULL etc. I
didn't test it but it might work. Of course this increases the number of
prepared statement parameters by a factor 3 but that might still be better
than permutations...
Wait, there's another option: use a function like ISNULL:
"select * from mytable where ISNULL(mycol, 'a_value_you_dont_expect') = ?
and ...."
Then map null to 'a_value_you_dont_expect'. Downside is, that this is
type and db dependend, i.e., you have to map numerical values to something
different from varchar values.
Regards
robert
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
