Inspiring

Question

Problem Using SQL Like Condition On Column With Null Values

Forum|Forum|17 years ago
September 30, 2008
15 replies
2179 views

Hello,

I have written a query in Coldfusion that returns records from a table matching a parsed keyword. However, because the table column I am using contains some null values I am getting the following error.

***
Exceptions

15:39:26.026 - java.lang.NullPointerException - in C:\ColdFusion8\wwwroot\Websites\morepeoplemoreactive.com\components\clubs\clubs.cfc : line 132
15:39:26.026 - java.lang.NullPointerException - in C:\ColdFusion8\wwwroot\Websites\morepeoplemoreactive.com\components\clubs\clubs.cfc : line 132
15:39:26.026 - java.lang.NullPointerException - in C:\ColdFusion8\wwwroot\Websites\morepeoplemoreactive.com\components\clubs\clubs.cfc : line 132
***

Here is my SQL statement:

***
lower(address) like '%#lcase(arguments.keyword)#%'
***

Is there an easy way of overcoming this problem?

Thank you in advance for your help.

Database access

This topic has been closed for replies.

Show previous replies

simonbinghamAuthor

Inspiring

Ok. The following query works and does not throw an error.

***
(
lower(facilityName) like '%#lcase(arguments.keyword)#%'
or lower(town) like '%#lcase(arguments.keyword)#%'
or lower(postcode) like '%#lcase(arguments.keyword)#%'

)
***

However, the moment I include one of the commented out fields I get the error. The commented out fields all exist in the database. The only difference I can find between the first three fields and the commented out fields is that the commented out fields include NULL values whereas the first three fields do not.

Any ideas?

Thank you again for your assistance.

K

Kronin555

Participating Frequently

I just created a test table with a varchar column that has some null values.
I did a select * into a CF query
I did a select * from cfquery where varcharcolumn like '%foo%'
I did a select * from cfquery where varcharcolumn like '%foo%' or varcharcolumn like '%bar%'
It worked fine, no NullPointerException.

What does the query that creates application.GetClubs look like?

simonbinghamAuthor

Inspiring

Here you go...

<cfif not StructKeyExists(application,"getClubs")>
<cflock type="exclusive" scope="application" timeout="30">
<cfquery name="application.getClubs" datasource="#variables.ds#" username="#variables.dsUsername#" password="#variables.dsPassword#">
select clubs.clubID, packages.package, clubs.packageID, classifications.classification, clubs.classificationID, clubs.groupName, clubs.facilityName, clubs.tradingName, clubs.companyRegNo, clubs.address1, clubs.address2, clubs.address3, clubs.town, clubs.county, clubs.postcode, regions.region, clubs.regionID, clubs.latitude, clubs.longitude, clubs.telephone, clubs.email, clubs.website, clubs.firstName, clubs.lastName, clubs.numberOfMembers, clubs.freeTrial, activityID
from (((classifications right join clubs on classifications.classificationID = clubs.classificationID) left join packages on clubs.packageID = packages.packageID) left join regions on clubs.regionID = regions.regionID) left join clubactivitylinks on clubs.clubID = clubactivitylinks.clubID
order by clubs.facilityName;
</cfquery>
</cflock>
</cfif>

K

Kronin555

Participating Frequently

Where does application.getClubs get defined? Are you sure that it exists and is defined correctly? Are you sure all the columns you expect are defined in that query?

D

Dan_Bracuk

Inspiring

Before your query, cfdump your arguments. See if any are missing.

next, change this:
and
(
lower(groupName) like '%#lcase(arguments.keyword)#%'
or lower(facilityName) like '%#lcase(arguments.keyword)#%'
or lower(tradingName) like '%#lcase(arguments.keyword)#%'
or lower(address1) like '%#lcase(arguments.keyword)#%'
or lower(address2) like '%#lcase(arguments.keyword)#%'
or lower(address3) like '%#lcase(arguments.keyword)#%'
or lower(town) like '%#lcase(arguments.keyword)#%'
or lower(county) like '%#lcase(arguments.keyword)#%'
or lower(postcode) like '%#lcase(arguments.keyword)#%'
)
to this:
and
(
1 = 2 or <---
lower(groupName) like '%#lcase(arguments.keyword)#%'
or lower(facilityName) like '%#lcase(arguments.keyword)#%'
or lower(tradingName) like '%#lcase(arguments.keyword)#%'
or lower(address1) like '%#lcase(arguments.keyword)#%'
or lower(address2) like '%#lcase(arguments.keyword)#%'
or lower(address3) like '%#lcase(arguments.keyword)#%'
or lower(town) like '%#lcase(arguments.keyword)#%'
or lower(county) like '%#lcase(arguments.keyword)#%'
or lower(postcode) like '%#lcase(arguments.keyword)#%' --->
)

If your query runs, uncomment a line and keep going until it fails. Then you'll know what line caused the problem.

D

Dan_Bracuk

Inspiring

You are misinterpreting the error message. It has nothing to do with your sql or database. Records with null values in the address field will simply be excluded from your results.

What does the first part of your error message say?

simonbinghamAuthor

Inspiring

Here is the error message:

***
Error Occurred While Processing Request

The system has attempted to use an undefined value, which usually indicates a programming error, either in your code or some system code.

Null Pointers are another name for undefined values.

Resources:

* Enable Robust Exception Information to provide greater detail about the source of errors. In the Administrator, click Debugging & Logging > Debug Output Settings, and select the Robust Exception Information option.
* Check the ColdFusion documentation to verify that you are using the correct syntax.
* Search the Knowledge Base to find a solution to your problem.

Browser Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB; rv:1.9.0.3) Gecko/2008092417 Firefox/3.0.3
Remote Address 127.0.0.1
Referrer http://127.0.0.1:8500/websites/morepeoplemoreactive.com/index.cfm?fuseaction=c_pages.showPage&pageID=1
Date/Time 30-Sep-08 04:18 PM
***

And here is my CFQUERY:

***
<cfquery name="getClubs" dbtype="query">
select clubID, package, packageID, classification, classificationID, groupName, facilityName, tradingName, companyRegNo, address1, address2, address3, town, county, postcode, region, regionID, latitude, longitude, telephone, email, website, firstName, lastName, numberOfMembers, freeTrial
from application.getClubs
where clubID <> 0
<cfif arguments.regionID neq 0>
and regionID=<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.regionID#" />
</cfif>
<cfif arguments.activityID neq 0>
and activityID=<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.activityID#" />
</cfif>
<cfif Len(arguments.town)>
and town=<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.town#" />
</cfif>
<cfif Len(arguments.postcode)>
and lower(postcode) like '%#lcase(left(arguments.postcode,2))#%'
</cfif>
<cfif arguments.freeTrialOnly neq 0>
and freeTrial <> ''
</cfif>
<cfif Len(Trim(arguments.keyword))>
and
(
lower(groupName) like '%#lcase(arguments.keyword)#%'
or lower(facilityName) like '%#lcase(arguments.keyword)#%'
or lower(tradingName) like '%#lcase(arguments.keyword)#%'
or lower(address1) like '%#lcase(arguments.keyword)#%'
or lower(address2) like '%#lcase(arguments.keyword)#%'
or lower(address3) like '%#lcase(arguments.keyword)#%'
or lower(town) like '%#lcase(arguments.keyword)#%'
or lower(county) like '%#lcase(arguments.keyword)#%'
or lower(postcode) like '%#lcase(arguments.keyword)#%'
)
</cfif>
group by clubID, package, packageID, classification, classificationID, groupName, facilityName, tradingName, companyRegNo, address1, address2, address3, town, county, postcode, region, regionID, latitude, longitude, telephone, email, website, firstName, lastName, numberOfMembers, freeTrial
</cfquery>
***

It's the following bit of code that is producing the error:

***
<cfif Len(Trim(arguments.keyword))>
and
(
lower(groupName) like '%#lcase(arguments.keyword)#%'
or lower(facilityName) like '%#lcase(arguments.keyword)#%'
or lower(tradingName) like '%#lcase(arguments.keyword)#%'
or lower(address1) like '%#lcase(arguments.keyword)#%'
or lower(address2) like '%#lcase(arguments.keyword)#%'
or lower(address3) like '%#lcase(arguments.keyword)#%'
or lower(town) like '%#lcase(arguments.keyword)#%'
or lower(county) like '%#lcase(arguments.keyword)#%'
or lower(postcode) like '%#lcase(arguments.keyword)#%'
)
</cfif>
***

Thank you for your help.

K

Kronin555

Participating Frequently

The only way that a query is flagged as throwing a null pointer error is if you're using a variable that doesn't exist. What's your <cfargument> line look like for arguments.keyword?

D

Dan_Bracuk

Inspiring

You are misinterpreting the error message. It has nothing to do with your sql or database. Records with null values in the address field will simply be excluded from your results.

What does the first part of your error message say?

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded