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.
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'
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')
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'
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!
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 |