cf_sql_integer vs cf_sql_bigint vs cf_sql_int??

[lovewebdev]

I have an ID (primary key) set as a BIGINT in SQL SERVER. I have a query with the following:

WHERE whateverid = <cfqueryparam cfsqltype="cf_sql_integer" value="#URL.id#">

I then got an invalid data type for it. I then tried:

WHERE whateverid = <cfqueryparam cfsqltype="cf_sql_bigint" value="#URL.id#">

I still got an invalid data type for it. i then tried:

WHERE whateverid = <cfqueryparam cfsqltype="cf_sql_int" value="#URL.id#">

and it worked.

The weird thing is cf_sql_int is not even listed as being accepted:
http://www.adobe.com/livedocs/coldfusion/5.0/CFML_Reference/Tags79.htm

Can some explain this to me.

[-__cfSearching__-]

> WHERE whateverid = <cfqueryparam cfsqltype="cf_sql_bigint" value="#URL.id#">

What is the value of #url.id#? I had no problems with either using CF8.

> The weird thing is cf_sql_int is not even listed as being accepted:

IIRC, when you pass in an invalid sql type CF just uses the default: cf_sql_char, which accepts just about anything. MS SQL probably does an implicit convert() to the right type and that is why it works.

[lovewebdev]

The number was like 200 something? I thought BIGINT covered the same as INT but just more.

[-__cfSearching__-]

Yes, bigint can handle 64 bit and integer 32 bit. I had no problems with either using CF8 and the built in driver. Assuming your setup is the same, my guess would be the value as the most likely cause of the problem.

http://msdn.microsoft.com/en-us/library/ms378715(SQL.90).aspx

[jjonney]

Try using TRIM function for #URL.id# as
#trim(URL.id)#

[James Moberg]

FYI: "CF_SQL_INT" now throws an error with CF2016u17+, CF2018 and CF2021.