Highlighted

CFQUERYPARAM Question - what to do when query is complex?

Contributor ,
Jul 22, 2019

Copy link to clipboard

Copied

Running Windows, CF 2016, SQL Server db 2008R2

Creating new queries that I need assistance on how to implement CFQUERYPARAM into the code.

Below is an example of the code without the CFQUERYPARAM (my calendar dates are a text field, not a date field in the SQL Server db):

SELECT date, day FROM calendar

  WHERE (calendar.date >= '#todays_date#' and calendar.type = 'B')

Is the placement of the CFQUERYPARAM correct on the below?  I haven't been able to find anything showing this type of SQL code.

SELECT date, day FROM calendar

  WHERE <cfqueryparam value="calendar.date" cfsqltype="cf_sql_char"></cfqueryparam> =

               (<cfqueryparam value="'#todays_date#'" cfsqltype="cf_sql_char"></cfqueryparam> AND

           calendar.type = 'B')

I was able to find documented that you need not use CFQUERYPARAM around constants.

Verification would be helpful.  Thanks.

Libby H.

Adobe Community Professional
Correct answer by BKBK | Adobe Community Professional

Hi Libby,

also, given the comparison '>=', the cfsql type should be cf_sql_date or cf_sql_timestamp:

SELECT date, day

FROM calendar

WHERE calendar.date >=  <cfqueryparam value="'#todays_date#'" cfsqltype="cf_sql_date">

AND calendar.type = 'B'

Views

304

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

CFQUERYPARAM Question - what to do when query is complex?

Contributor ,
Jul 22, 2019

Copy link to clipboard

Copied

Running Windows, CF 2016, SQL Server db 2008R2

Creating new queries that I need assistance on how to implement CFQUERYPARAM into the code.

Below is an example of the code without the CFQUERYPARAM (my calendar dates are a text field, not a date field in the SQL Server db):

SELECT date, day FROM calendar

  WHERE (calendar.date >= '#todays_date#' and calendar.type = 'B')

Is the placement of the CFQUERYPARAM correct on the below?  I haven't been able to find anything showing this type of SQL code.

SELECT date, day FROM calendar

  WHERE <cfqueryparam value="calendar.date" cfsqltype="cf_sql_char"></cfqueryparam> =

               (<cfqueryparam value="'#todays_date#'" cfsqltype="cf_sql_char"></cfqueryparam> AND

           calendar.type = 'B')

I was able to find documented that you need not use CFQUERYPARAM around constants.

Verification would be helpful.  Thanks.

Libby H.

Adobe Community Professional
Correct answer by BKBK | Adobe Community Professional

Hi Libby,

also, given the comparison '>=', the cfsql type should be cf_sql_date or cf_sql_timestamp:

SELECT date, day

FROM calendar

WHERE calendar.date >=  <cfqueryparam value="'#todays_date#'" cfsqltype="cf_sql_date">

AND calendar.type = 'B'

Views

305

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
Jul 22, 2019 0
Adobe Community Professional ,
Jul 22, 2019

Copy link to clipboard

Copied

You only need it around variables, not static values (and definitely NOT around the sql columns names). Also no need of a closing tag, so try this instead:

SELECT date, day FROM calendar

  WHERE (calendar.date >= <cfqueryparam value="'#todays_date#'" cfsqltype="cf_sql_char">

  and calendar.type = 'B')

/Charlie (server troubleshooter, carehart.org)

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
Reply
Loading...
Jul 22, 2019 1
Adobe Community Professional ,
Jul 22, 2019

Copy link to clipboard

Copied

Hi Libby,

also, given the comparison '>=', the cfsql type should be cf_sql_date or cf_sql_timestamp:

SELECT date, day

FROM calendar

WHERE calendar.date >=  <cfqueryparam value="'#todays_date#'" cfsqltype="cf_sql_date">

AND calendar.type = 'B'

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
Reply
Loading...
Jul 22, 2019 2
Contributor ,
Jul 23, 2019

Copy link to clipboard

Copied

Thanks everyone for all of the replies.

Seems I need (at least with this calendar table) to figure out what I am querying for on each table.  Unfortunately, this date field in SQL Server is  smalldatetime.  I've tried cfsqltype="cf_sql_date" and cfsqltype="cf_sql_datetime" and neither worked.

But thank you everyone for the information - they all are the correct  answers!

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
Reply
Loading...
Jul 23, 2019 0
New Here ,
May 21, 2020

Copy link to clipboard

Copied

Have you tried 

CF_SQL_TIMESTAMP

ColdFusion

JDBC

DB2

Oracle

MSSQL

CF_SQL_ARRAY

ARRAY

 

 

 

CF_SQL_BIGINT

BIGINT

Bigint

 

bigint

CF_SQL_BINARY

BINARY

Char for Bit Data

 

binaryt

CF_SQL_BIT

BIT

 

 

bit

CF_SQL_BLOB

BLOB

Blob

blob, bfile

longvarbinary

CF_SQL_CHAR

CHAR

Char

char, nchar

char

CF_SQL_CLOB

CLOB

Clob

clob,nclob

 

CF_SQL_DATE

DATE

Date

 

date

CF_SQL_DECIMAL

DECIMAL

Decimal

number

decimal

CF_SQL_DISTINCT

DISTINCT

 

 

 

CF_SQL_DOUBLE

DOUBLE

Double

 

double

CF_SQL_FLOAT

FLOAT

Float

number

real

CF_SQL_IDSTAMP

CHAR

Char

char, nchar

char

CF_SQL_INTEGER

INTEGER

Integer

 

integer

CF_SQL_LONGVARBINARY

LONGVARBINARY

Long Varchar for Bit Data

long raw

image

CF_SQL_LONGNVARCHAR

LONGNVARCHAR

LONGVARGRAPHIC

NVARCHAR2

ntext

CF_SQL_LONGVARCHAR

LONGVARCHAR

Long Varchar

long

text

CF_SQL_MONEY

DOUBLE

Double

 

double

CF_SQL_MONEY4

DOUBLE

Double

 

double

CF_SQL_NCHAR

NCHAR

NCHAR

NCHAR

nchar

CF_SQL_NCLOB

CLOB

NCLOB

NCLOB

nvarchar

CF_SQL_NULL

NULL

 

 

 

CF_SQL_NUMERIC

NUMERIC

Numeric

 

numeric

CF_SQL_NVARCHAR

NVARCHAR

NVARCHAR

NVARCHAR2

nvarchar

CF_SQL_OTHER

OTHER

 

 

 

CF_SQL_REAL

REAL

Real

 

real

CF_SQL_REFCURSOR

REF

 

 

 

CF_SQL_SMALLINT

SMALLINT

Smallint

 

smallint

CF_SQL_STRUCT

STRUCT

 

 

 

CF_SQL_SQLXML

 

 

 

xml

CF_SQL_TIME

TIME

Time

 

time

CF_SQL_TIMESTAMP

TIMESTAMP

Timestamp

date

datetime

CF_SQL_TINYINT

TINYINT

 

 

tinyint

CF_SQL_VARBINARY

VARBINARY

Rowid

raw

varbinary

CF_SQL_VARCHAR

VARCHAR

Varchar

varchar2, nvarchar2

varchar

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
Reply
Loading...
May 21, 2020 0