Inspiring

Answered

CFQUERYPARAM Question - what to do when query is complex?

Forum|Forum|6 years ago
July 22, 2019
3 replies
736 views

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.

This topic has been closed for replies.

Correct answer BKBK

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'

L

Libby_HAuthor

Inspiring

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!

G

Gregor_Cunningham

Participant

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

BKBK

Correct answer

Community Expert

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'

Charlie Arehart

Community Expert

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 (troubleshooter, carehart. org)

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded