cfsqltype mismatch in CF8 with Oracle?

[cherdt]

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?

[Newsgroup_User · Accepted solution]

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]

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....

[Dan_Bracuk]

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.

[cherdt]

Since Oracle doesn't want to accept a 3 character value for a CHAR(5) column from a properly formatted cfqueryparam statement unless the value is padded with spaces, I think we'd be better off with VARCHAR2. (Not that I understand why it doesn't work, since what I've read suggests that the database should supply the extra spaces as needed.)

There is some disagreement as to whether CHAR is preferable to VARCHAR2 for short character fields, but Oracle's "Ask Tom" site suggests that VARCHAR2 is always preferable to CHAR: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:72243456808191

[paross1]

Because CHAR is fixed-length, and VARCHAR2 is variable, so if you intend on having a string of varying size, then I would go with VARCHAR2.

Note: I've been working with Oracle since 1997, and have NEVER used CHAR as a datatype....... ever.

Phil