Problem with cfqueryparam and Oracle's char datatype.

{ Posted By : Eric Cobb on January 8, 2010 }
Comments (3)
1190 Views
Related Categories: Databases, CFML

I ran into a problem this afternoon with cfqueryparam when querying an Oracle database that really had me scratching my head. In my query's WHERE clause I was filtering against a column that had a CHAR(8) datatype, but whenever I ran the query with cfqueryparam it wouldn't return any results.

It wasn't anything fancy, just your run of the mill everyday query like:

<cfquery name="q" datasource="dsn">
    SELECT *
    FROM PRICE
    WHERE item = <cfqueryparam cfsqltype="cf_sql_numeric" value="#item#" />
    AND item_type = <cfqueryparam cfsqltype="cf_sql_char" value="#item_type#" />
</cfquery>

The really strange part was that I could copy the sql from the debugging output in ColdFire (which produced a record count of 0) and paste into SQL Developer and it would return the desired results. If I ran the query passing in the value without the cfqueryparam tag, it would return the correct results as well. But, cfqueryparam killed it every time. I tried changing from "cf_sql_char" to "cf_sql_varchar", but that didn't work either. I didn't get an error, just zero results.

After a good amount of Googling, I finally came across forum post from way back in 2004 where someone mentioned having to use LJustify() with CF 6. The value I was passing in to my CHAR(8) column was only 5 characters long, so I gave it a try. Guess what...it worked! Woo-hoo!

<cfquery name="q" datasource="dsn">
    SELECT *
    FROM PRICE
    WHERE item = <cfqueryparam cfsqltype="cf_sql_numeric" value="#item#" />
    AND item_type = <cfqueryparam cfsqltype="cf_sql_char" value="#LJustify(item_type,8)#" />
</cfquery>

I guess this really shouldn't come as a surprise, but I didn't consider it because the query worked without the extra spaces when I wasn't using cfqueryparam. Even hard coding the 5 character value in my CF query worked correctly without the spaces.

<cfquery name="q" datasource="dsn">
    SELECT *
    FROM PRICE
    WHERE item = <cfqueryparam cfsqltype="cf_sql_numeric" value="#item#" />
    AND item_type = '12345'
</cfquery>

Same thing in SQL Developer, I could use '12345' just fine even though the value was really '12345   ' (with 3 spaces) in the database.

Now, I'm sure this may be common knowledge for those of you that work with Oracle a lot, but I'm just getting back into Oracle after about a 5 year hiatus from it, so cut me some slack. ;-)

Send | del.icio.us | Digg It! | Linking Blogs
Comments
[ Add Comment ] [ Subscribe to Comments ]
James Brown's Gravatar Thanks for the update. Coming from mySQL and MS SQL, I've been using Oracle quite a bit and have run into some weird nuances (I try to post about them on my blog whenever I run into them).

Thanks for this post. I bet that was frustrating.
# Posted By James Brown | 1/9/10 8:45 AM
Steph's Gravatar I handle this by using TRIM() for both values.
# Posted By Steph | 1/10/10 8:35 AM
Eric Cobb's Gravatar @Steph - that's true, it would definitely do the trick, but I always try to avoid running functions like that on the actual DB columns in my WHERE and ORDER BY clauses. No particular reason why, I just don't like to do it.
# Posted By Eric Cobb | 1/11/10 3:50 PM
[ Add Comment ]