Getting 0 rows returned when selecting with null values

[Revanth reddy]

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]

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]

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]

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]

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]

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.

[Revanth reddy]

Thank you somuch omar. let me try that...

[Revanth reddy]

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]

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]

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]

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]

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

select KIT,NO,CAT, FILE, FILE, TYPE ,"COMMENT", VER from <table> where KIT= 'SVN6064KA' and TYPE = 'PRL'

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]

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]

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]

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]

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]

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]

Thank you somuch.. i'll implement this ...

[Omar Al Kababji]

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

if(file_name = null)
  query.append(" and file_name = " + file_name);
else
 query.append(" and file_name is null");
    

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

(peace)

[Bauke Scholtz]

omar al kababji wrote:

if(file_name = null)
  query.append(" and file_name = " + file_name);
else
 query.append(" and file_name is null");
    

Improved:

if (file_name != null) {
    query.append(" and file_name = ?");
    values.add(file_name);
} else {
    query.append(" and file_name is null");
}

[Revanth reddy]

Thank you somuch for both of you.. Its working!!!1