Skip to main content
cherdt
cherdt
Inspiring
April 2, 2008
Answered

cfsqltype mismatch in CF8 with Oracle?

  • April 2, 2008
  • 1 reply
  • 1076 views
I'm running into a strange problem, and I'm wondering if anyone else has noticed the same. I have a query that is returning zero results in ColdFusion, but the same query returns 22 results via the database.

The problem appears to be the cfsqltype. The database column is data type CHAR(5). When I specify the cfsqltype as cf_sql_char, the query returns zero results. When I specify the cfsqltype as cf_sql_integer, the query returns 22 results.

Has anyone else run into this?
This topic has been closed for replies.
Correct answer Newsgroup_User
cherdt wrote:
> I'm running into a strange problem, and I'm wondering if anyone else has
> noticed the same. I have a query that is returning zero results in ColdFusion,
> but the same query returns 22 results via the database.
>
> The problem appears to be the cfsqltype. The database column is data type
> CHAR(5). When I specify the cfsqltype as cf_sql_char, the query returns zero
> results. When I specify the cfsqltype as cf_sql_integer, the query returns 22
> results.

Your code is exactly the opposite of your explanation. However, you
should try to append spaces to you value to make sure it is exactly the
correct number of characters, i.e.:
<cfqueryparam
value="#Left(trim(value) & ' ', y)#"
cfsqltype="cf_sql_char"
maxlength="#y#">

Jochem

--
Jochem van Dieten
Adobe Community Expert for ColdFusion

1 reply

N
Newsgroup_UserCorrect answer
Inspiring
April 3, 2008
cherdt wrote:
> I'm running into a strange problem, and I'm wondering if anyone else has
> noticed the same. I have a query that is returning zero results in ColdFusion,
> but the same query returns 22 results via the database.
>
> The problem appears to be the cfsqltype. The database column is data type
> CHAR(5). When I specify the cfsqltype as cf_sql_char, the query returns zero
> results. When I specify the cfsqltype as cf_sql_integer, the query returns 22
> results.

Your code is exactly the opposite of your explanation. However, you
should try to append spaces to you value to make sure it is exactly the
correct number of characters, i.e.:
<cfqueryparam
value="#Left(trim(value) & ' ', y)#"
cfsqltype="cf_sql_char"
maxlength="#y#">

Jochem

--
Jochem van Dieten
Adobe Community Expert for ColdFusion
cherdt
cherdtAuthor
Inspiring
April 3, 2008
Thanks, Jochem. It was fundamental misunderstanding on my part of the difference between Oracle's CHAR and VARCHAR2 data types: I didn't realize that CHAR(5) would not accept fewer than 5 characters.

Now I just need to track down my DBA....
D
Dan_Bracuk
Inspiring
April 3, 2008
quote:
Originally posted by: cherdt

Now I just need to track down my DBA....

Why?

For 5 characters, char is probably a more efficient datatype in oracle. I know that it is in redrick.
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices