GeeCON Prague 2014*
The moose likes JDBC and the fly likes Getting 0 rows returned when selecting with null values Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Getting 0 rows returned when selecting with null values" Watch "Getting 0 rows returned when selecting with null values" New topic
Author

Getting 0 rows returned when selecting with null values

Revanth reddy
Ranch Hand

Joined: Oct 10, 2008
Posts: 52
Hi,
I have to execute a qury to display some results. i am passing the parameters to the query at run time. here is the query

select PROGKIT,XCVR_NO,CATEGORY, FILE_NAME, FILE_PATH, TYPE ,"COMMENT", VERSION from PROG_EXTRA where PROGKIT=? AND CATEGORY =? AND FILE_NAME = ? AND FILE_PATH = ? AND TYPE = ? AND "COMMENT" = ? AND VERSION = ? AND XCVR_NO = ?

the problem is one or more columns are null, when executing this query causing 0 rows returned.

ex:

select PROGKIT,XCVR_NO,CATEGORY, FILE_NAME, FILE_PATH, TYPE ,"COMMENT", VERSION from PROG_EXTRA where PROGKIT='SVN147852' AND CATEGORY ='UPDATING SECOND ROW'

returned 2 rows

where as

select PROGKIT,XCVR_NO,CATEGORY, FILE_NAME, FILE_PATH, TYPE ,"COMMENT", VERSION from PROG_EXTRA where PROGKIT='SVN147852' and CATEGORY ='UPDATING SECOND ROW' AND FILE_NAME = ''

returned 0 rows (where file_name column value is null in db) similar with other null value columns

how to solve this ???
any help is greatly appreciated..

Thanks
Kumar

Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Kumar,

In SQL nothing can be compared to NULL... for example, you will never, ever return a row in SQL by saying something equal to NULL... i.e., where mycolumn = NULL (or mycolumn != NULL).

You can check for NULL... where mycolumn IS NULL (or mycolumn IS NOT null).

Revanth reddy
Ranch Hand

Joined: Oct 10, 2008
Posts: 52
Paul Campbell wrote:Kumar,

You can check for NULL... where mycolumn IS NULL (or mycolumn IS NOT null).



Thanks paul for the reply, but here i am passing the values to the query at run time, so i am not sure which values will be null, so how do we handlle this ??
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
1) Determine if you really need those columns to uniquely identify the rows (do this in SQL an not Java first... this is where I think most Java programmers get surprised/confused by SQL (it looks similar... but things like = null do not work the same)

Or

2) Examine modeling your data differently so that you have a primary key or a compound key that uniquely identifies the row.


Or

3) If the null values are required for you to uniquely identify your row and you can't change your table structure... you will have to have a different sql statement for those rows.

if (mycolumn == null) {
do this query;
} else {do this other query;}

Or

4) Something else that I can't think of because I don't have enough information to hazard a suggestion.
Revanth reddy
Ranch Hand

Joined: Oct 10, 2008
Posts: 52
Thank you so much paul;
Here actually what i am doing is checking the user entered value against the existing values in the db. unfortunatly this table doesn't have the primary key. so i have to check al the values and discard the user entry if all the values entered by the user exactly matches to the existing row. in this scenario some of the user entries may be null. where ecactly i am getting this problem




Omar Al Kababji
Ranch Hand

Joined: Jan 13, 2009
Posts: 357
construct the query at runtime only if the values are not null

StringBuilder queryString = new StringBuilder();

queryString.append("select * from myTable where 1 = 1 ");

if(file_name != null){
queryString.append("and file_name =" + file_name);
}


... and so on ... ofcourse you will not be adding the file_name directly to the query string to prevent maliciouse code in it.


Omar Al Kababji - Electrical & Computer Engineer
[SCJP - 90% - Story] [SCWCD - 94% - Story] [SCBCD - 80% - Story] | My Blog
Revanth reddy
Ranch Hand

Joined: Oct 10, 2008
Posts: 52
Thank you somuch omar. let me try that...
Revanth reddy
Ranch Hand

Joined: Oct 10, 2008
Posts: 52
StringBuilder queryString = new StringBuilder();
queryString.append("select PROGKIT,XCVR_NO,CATEGORY, FILE_NAME, FILE_PATH, TYPE ,\"COMMENT\", VERSION from PROG_EXTRA where PROGKIT= " + svnkit);

if(!StringUtils.isEmpty(fileName)){
queryString.append(" and FILE_NAME =" + fileName);
}
if(!StringUtils.isEmpty(filePath)){
queryString.append(" and FILE_PATH =" + filePath);
}

if(!StringUtils.isEmpty(category)){
queryString.append(" and CATEGORY =" + category);
}
if(!StringUtils.isEmpty(fileVersion)){
queryString.append(" and VERSION =" + fileVersion);
}
if(!StringUtils.isEmpty(config)){
queryString.append(" and TYPE =" + config);
}
if(!StringUtils.isEmpty(xcvr)){
queryString.append(" and XCVR_NO =" + xcvr);
}
if(!StringUtils.isEmpty(comments)){
queryString.append(" and \"COMMENT\" =" + comments);
}

String query =queryString.toString();

System.out.println("Query is" + query);

st = db.prepareStatement(query);

rs = st.executeQuery();

if(rs.next()){
exist=true;
}

Hi omar..
I build the query dynamically as said. (String query =queryString.toString(); ) String query holding the total query.. when i ran the query on oracle it is working fine, but in java the controll is coming out after rs = st.executeQuery(); statement when i debug. any suggetion ??



Omar Al Kababji
Ranch Hand

Joined: Jan 13, 2009
Posts: 357
I think you should place your strings between single quotations for example the file name should be ' + file_name + '

However you will have some security problems i will inform you as soon as i reach home
Revanth reddy
Ranch Hand

Joined: Oct 10, 2008
Posts: 52
omar al kababji wrote:I think you should place your strings between single quotations for example the file name should be ' + file_name + '



it worked!! Thankyou so much
Omar Al Kababji
Ranch Hand

Joined: Jan 13, 2009
Posts: 357
Ok am home I am glad that it works however i told you that you will be facing some security issues doing it this way, the reason is that users can use SQL injection to get some information from your DB, if you google for "SQL injection" you will find a lot of posts talking about it and showing you examples.

the ideal way is to use a preparedStatement that will allow you to add the parameters to the string by removing the malicious characters, however you will always use the same method to construct the query.

best regards

(peace)
Revanth reddy
Ranch Hand

Joined: Oct 10, 2008
Posts: 52
Omar,
Thank you so much, but here i found another issue. basically the query will be executed before the insert.(to check the exact duplicate exits in the db; insert will take the user entered values from GUI).
lets say there are 6 fields for user to enter. the user entered 2 fileds and left 4 fields blank.then i am preparing the dynamic query with the 4 fields(basically eleminating the null values from the query)
lets say the generated query is



the problem here is the rest of the column values in the database are exists, but the user in the front ends hasn't entered the rest of 4 values. but still i am checking aganist the db.

in therun of above query i got 1 row returned. and insert failed saying that duplicate data exists. infact this is not a duplicate record, as the db has more column values are present for that row, how to handle this.. i think it is not clear... if it is not clear reply me .... i will try to elaborate more.
Bauke Scholtz
Ranch Hand

Joined: Oct 08, 2006
Posts: 2458
Revanth reddy wrote:to check the exact duplicate exits in the db
Why not let the DB handle it using some UNIQUE constraint and catch the constraint violation exception in Java? This is definately a database concern.

That said, you should be using preparedstatement placeholders instead of string concatenations.
Revanth reddy
Ranch Hand

Joined: Oct 10, 2008
Posts: 52
Bauke Scholtz wrote:Why not let the DB handle it using some UNIQUE constraint and catch the constraint violation exception in Java? This is definately a database concern.

That said, you should be using preparedstatement placeholders instead of string concatenations.


Thanks for reply.. but unfortunately this table doesn't have unique constraints and any of constraints. that is the reason why i am checking for exact row duplicate in the db.
Bauke Scholtz
Ranch Hand

Joined: Oct 08, 2006
Posts: 2458
Revanth reddy wrote:
Bauke Scholtz wrote:Why not let the DB handle it using some UNIQUE constraint and catch the constraint violation exception in Java? This is definately a database concern.

That said, you should be using preparedstatement placeholders instead of string concatenations.


Thanks for reply.. but unfortunately this table doesn't have unique constraints and any of constraints. that is the reason why i am checking for exact row duplicate in the db.
At any way, I would discuss it with the DBA and/or project lead. This is a poor datamodel.
Revanth reddy
Ranch Hand

Joined: Oct 10, 2008
Posts: 52
ya .. i know this is poor data model... now we are on a situation that we can't change the design. thats the problem
Bauke Scholtz
Ranch Hand

Joined: Oct 08, 2006
Posts: 2458
OK, back to your new problem, in this case you should check if other columns are null or not. If they are all null, exactly as the entered data, then simply concat " AND field = NULL" to your SQL string.

And you should still be using preparedstatement instead. Concat a new " AND field = ?" to the SQL string for every column, get hold of all values in an array or collection and use the preparedstatement the usual way.
Revanth reddy
Ranch Hand

Joined: Oct 10, 2008
Posts: 52
Thank you somuch.. i'll implement this ...
Omar Al Kababji
Ranch Hand

Joined: Jan 13, 2009
Posts: 357
Hi, well you can always solve it while constructing the query string by putting in the else statetment the check of the null value with the is null here is an example



Also remember that an empty string is not considered a null value

(peace)
Bauke Scholtz
Ranch Hand

Joined: Oct 08, 2006
Posts: 2458
omar al kababji wrote:


Improved:

Revanth reddy
Ranch Hand

Joined: Oct 10, 2008
Posts: 52
Thank you somuch for both of you.. Its working!!!1
 
GeeCON Prague 2014
 
subject: Getting 0 rows returned when selecting with null values