coldfusion Working with databases


Example

One of ColdFusion's strengths is how easy it is to work with databases. And of course, query inputs can and should be parameterized.

Tag Implementation

<cfquery name="myQuery" datasource="myDatasource" result="myResult">
    select firstName, lastName
    from users
    where lastName = <cfqueryparam value="Allaire" cfsqltype="cf_sql_varchar">
</cfquery>

CFScript Implementation

// ColdFusion 9+
var queryService = new query(name="myQuery", datasource="myDatasource");
queryService.addParam(name="lName", value="Allaire", cfsqltype="cf_sql_varchar");
var result = queryService.execute(sql="select firstName, lastName from users where lastName = :lName");
var myQuery = result.getResult();
var myResult = result.getPrefix();

// ColdFusion 11+
var queryParams = {lName = {value="Allaire", cfsqltype="cf_sql_varchar"}};
var queryOptions = {datasource="myDatasource", result="myResult"};
var myQuery = queryExecute("select firstName, lastName from users", queryParams, queryOptions);

Inserting values is just as easy:

queryExecute("
    insert into user( firstname, lastname )
    values( :firstname, :lastname )
",{
    firstname: { cfsqltype: "cf_sql_varchar", value: "Dwayne" }
    ,lastname: { cfsqltype: "cf_sql_varchar", value: "Camacho" }
},{
    result: "local.insertResult"
});

return local.insertResult.generated_key;