cf_sql_integer vs cf_sql_bigint vs cf_sql_int??

Participant ,
Feb 07, 2009 Feb 07, 2009

Copy link to clipboard

Copied

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.
TOPICS
Database access

Views

4.2K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Feb 07, 2009 Feb 07, 2009

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Feb 08, 2009 Feb 08, 2009

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Feb 08, 2009 Feb 08, 2009

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Feb 10, 2009 Feb 10, 2009

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
May 18, 2021 May 18, 2021

Copy link to clipboard

Copied

LATEST

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines