Oracle - Invalid data for CFSQLTYPE CF_SQL_INTEGER

[Roy]

I am converting to a legacy Oracle database where the telephone numbers with area codes are defined as Number(10).  CFWheels is adding a queryparam "CFSQLTYPE CF_SQL_INTEGER" when updating and it is throwing an error for invalid data.  I believe the maximum number that is a valid integer is 2147483647 which makes any phone number with an area code greater than 214 invalid.  I believe this is a bug in the Oracle Model Adapter.  Does someone have a suggested correction?

Thanks, Roy

[Risto]

Without specifying scale you should get 38 numbers.  Just change the data type to number(11) or number(12).

[Roy]

Risto, assuming the database cannot be changed; how would you handle this?

Roy

[Risto]

Hi,

Is there more detail to your error message?  Generally speaking all cfqueryparam "CFSQLTYPE CF_SQL_INTEGER" does is validate that it is an integer.

The oracle data type will determine whether you can enter data above 2147483647, not the "CFSQLTYPE CF_SQL_INTEGER" of the cfqueryparam. Since your DB has a precision of 10 set for numeric, that to me is the real problem.

That being said, you could possibly try a regular cfquery with no cfqueryparam to see if you still have the issue.

If not, you could try to change the Oracle Model Adapter. The following values available for numeric with cfqueryparam:

CF_SQL_SMALLINT, CF_SQL_INTEGER, CF_SQL_REAL, CF_SQL_FLOAT, CF_SQL_DOUBLE, CF_SQL_TINYINT, CF_SQL_MONEY, CF_SQL_MONEY4, CF_SQL_DECIMAL, CF_SQL_NUMERIC, and CF_SQL_BIGINT

The only place I see to change is line 28 in /models/adapters/ Oracle.cfc

As I said, I do not think this is your problem. I still believe you will need to change the precision of your DB column. If you look at line 25 of Oracle.cfc in /models/adapters you will see
// integer datatypes are represented by number(38,0) which to me means  cf_sql_integer can handle up to the normal 38 digits of the numeric field.  The problem in your case is it is set to numeric(10).

Hope this can be of some help. Let me know what ends up being your solution.

[Roy]

Hi Risto,

I really appreciate your suggestions.  The full error message I received is "Invalid data 406xxxxxxx for CFSQLTYPE CF_SQL_INTEGER" where 406xxxxxx is a 10 digit telephone number.  This is a CF error and not an Oracle error.  This worked fine when I was using a MySQL database during development.  I believe the maximum integer size is 2147483647 and most of the telephone numbers I'm using begin with 406. I think a Number(10) datatype/size should hold up to 9999999999 so I changed line 28 in /models/adapters/ Oracle.cfc to CF_SQL_BIGINT and its seems to be working fine.

Thanks for your help, Roy

[Risto]

That's great, thanks for sharing.

[Adam Chapman]

Nice one.. Sounds like a good candidate for a pull request?