0

When I look at

QueryExecute ("select from Artists where artistid=? and country=?",
    [1, "USA"], {datasource="cfartgallery"});

I am wondering if artistid is queried as if were

<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="1">

From https://wikidocs.adobe.com/wiki/display/coldfusionen/QueryExecute

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
  • FWIW, enabling full debugging will show the cfsqltype in CF11. Example `Query Parameter Value(s) - Parameter #1(CF_SQL_CHAR) = 0` – Leigh Sep 02 '15 at 00:24

1 Answers1

3

If you need to provide additional attribute as a Query parameter (like CFSQLType, list, and separator), you can pass these as struct against that column name.

Example 2

QueryExecute("select from Employee where country=:country 
  and citizenship=:country", 
  {country={value='USA', CFSQLType='CF_SQL_CLOB', list=true}});

https://wikidocs.adobe.com/wiki/display/coldfusionen/QueryExecute

Community
  • 1
  • 1
Henry
  • 32,689
  • 19
  • 120
  • 221
  • So if CFSQLType is not specified, then it defaults to `cf_sql_varchar` ? – James A Mohler Sep 01 '15 at 22:35
  • 1
    @JamesAMohler I believe so, just like `cfqueryparam` without specifying type. Usually it'll work but if there's an index for that column, but the type is not specified, the DB may not utilize the index. – Henry Sep 01 '15 at 22:42
  • 3
    Henry, actually the default for cfqueryparam is `cf_sql_char`. *the DB may not utilize the index* Most db's are usually smart enough to coerce the literal into the correct type, rather than the column values, which would hinder index usage. Though not always. However, omitting the type also means you lose validation and submit the value as a string [which can cause bigger issues](http://stackoverflow.com/questions/29434159/with-stored-procedures-is-cfsqltype-necessary/29436751#29436751) – Leigh Sep 01 '15 at 23:52
  • As an aside, what the heck is with the `CF_SQL_CLOB` in the CF docs? I am having Dreamweaver flashbacks ;-) – Leigh Sep 01 '15 at 23:56
  • 1
    @Leigh ya, sometimes it's scary to think what sort of CFML Adobe engineers write... – Henry Sep 02 '15 at 00:07