Quick Tip on using ColdFusion cfquery in cfscript


We’re all familiar with using cfquery to query databases in ColdFusion. But what is its equivalent in cfscript? Here are a few query examples to illustrate. For each example we will show the cfquery and its equivalent in cfscript. For these examples we will be performing the CRUD operations on the clients database.

1. Create (i.e. INSERT into the database)

Let’s start with the cfquery tag version:

<cfquery name="qInsert" datasource="clientsDS">
  INSERT INTO clients
  (
    title,
    firstname,
    lastname,
    email,
    phone
  )
  VALUES
  (
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.title#">,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.firstname#">,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.lastname#">,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.email#">,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.phone#">
  )
</cfquery>

Here’s the cfscript equivalent:

<cfscript>
  qService = new query();
  qService.setDatasource("clientsDS");
  qService.setName("qInsert");
  qService.addParam(name="title", value="#trim(form.title)#", cfsqltype="cf_sql_varchar");
  qService.addParam(name="firstname", value="#trim(form.firstname)#", cfsqltype="cf_sql_varchar");
  qService.addParam(name="lastname", value="#trim(form.lastname)#", cfsqltype="cf_sql_varchar");
  qService.addParam(name="email", value="#trim(form.email)#", cfsqltype="cf_sql_varchar");
  qService.addParam(name-"phone",  value-"#trim(form.phone)#", cfsqltype-"cf_sql_varchar");
  qService.setSql("
    INSERT INTO clients
    (title, firstname, lastname, email, phone)
    VALUES
    (:title, :firstname, :lastname, :email, :phone)
  ");
   qService.execute();
</cfscript>

2 Read (i.e. SELECT from the database)

Let’s start with the cfquery tag version:

<cfquery name="qGet" datasource="clientsDS">
  SELECT 
    title, firstname, lastname, email, phone
  FROM clients
  WHERE clientID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.clientID#">
</cfquery>

Here’s the cfscript equivalent:

  qService = new query(); 
  qService.setDatasource("clientsDS"); 
  qService.setName("qGet"); 
  qService.addParam(name="clientID", value="#trim(form.clientID)#", cfsqltype="cf_sql_integer");
  qService.setSql("
   SELECT title, firstname, lastname, email, phone
   FROM clients
   WHERE clientID = :clientID
  ");
  results = qService.execute();

3 Update (i.e. Update the database)

Let’s start with the cfquery tag version:

<cfquery name="qUpdate" datasource="clientsDS">
  UPDATE clients
  SET
    email = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.email#">,
    phone = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.phone#">
  WHERE clientID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.clientID#">
</cfquery>

Here’s the cfscript equivalent:

 qService = new query(); 
 qService.setDatasource("clientsDS"); 
 qService.setName("qUpdate");
 qService.addParam(name="email", value="#trim(form.email)#", cfsqltype="cf_sql_varchar");
 qService.addParam(name-"phone",  value-"#trim(form.phone)#", cfsqltype-"cf_sql_varchar")
 qService.addParam(name="clientID", value="#trim(form.clientID)#", cfsqltype="cf_sql_integer");
 qService.setSql("
  UPDATE clients
  SET
   email = :email, 
   phone = :phone
  WHERE clientID = :clientID
 ");
 qService.execute();

4 Delete (i.e. DELETE from the database)

Let’s start with the cfquery tag version:

<cfquery name="qDelete" datasource="clients">
  DELETE FROM clients
  WHERE clientID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.clientID#">
</cfquery>

Here’s the cfscript equivalent:

 qService = new query(); 
 qService.setDatasource("clientsDS"); 
 qService.setName("qDelete");
 qService.addParam(name="clientID", value="#trim(form.clientID)#", cfsqltype="cf_sql_integer");
 qService.setSql("
   DELETE FROM clients
   WHERE clientID = :clientID
 ");
 qService.execute();
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s