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

Generate T-SQL for restoring multiple databases

Let’s say we are migrating the database server to a new host. After SQL Server has been successfully installed on the new host, we need to migrate the databases. Let’s say we need to restore well over 100 databases. A better way to do this is to generate the T-SQL restore scripts from the backup files, including the transaction logs.

The following assumptions are made when creating the scripts:

  • Full backup of all user databases daily
  • Transaction log backup every 12 hours at a specified hour
  • A subfolder is created for each database to store the backup files (.bak and .trn)
  • File extensions are:
    • .bak – full backup
    • .trn – transaction log

Here’s the script that will generate the required T-SQL script to restore all the user databases from the full backup and transaction log files. Plesae note that this script is based on someone else’s creation. I can’t remember who it was. But full credit to him. I have adapted his script for my purposes.

USE Master;
GO
-- turn off (x row(s) affected) messages
SET NOCOUNT ON;

DECLARE @BasePath varchar(1000);
DECLARE @Path varchar(1000);
DECLARE @FullPath varchar(2000);
DECLARE @Id int;
DECLARE @todayDate varchar(20);
DECLARE @yesterdayDate varchar(20);
DECLARE @fName varchar (500);
DECLARE @fExt varchar(10);
DECLARE @dbName varchar(200);
DECLARE @cmd varchar(500);

-- set backup file's date in the format yyyy_mm_dd. we restore yesterday's backups
SET @yesterdayDate = replace(convert(NVARCHAR, dateadd(day,-1, getdate()), 111), '/', '_');

--This is the backup parent folder. Can use drive-folder or UNC notation
SET @BasePath = '<enter-your-full-db-backup-path-here>';

--Create a temp table to hold the results. remove the table if exists
IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
   DROP TABLE #DirectoryTree;

CREATE TABLE #DirectoryTree (
   id int IDENTITY(1,1)
   ,fullpath varchar(2000)
   ,fileName nvarchar(512)
   ,fileExt varchar(10)
   ,dbName varchar (200)
   ,depth int
   ,isfile bit
);

--Create a clustered index to keep everything in order.
ALTER TABLE #DirectoryTree
   ADD CONSTRAINT PK_DirectoryTree PRIMARY KEY CLUSTERED (id);

--Populate the table using the initial base path.
INSERT #DirectoryTree (fileName,depth,isfile)
EXEC master.sys.xp_dirtree @BasePath,1,1;

UPDATE #DirectoryTree SET fullpath = @BasePath;

--Loop through the table as long as there are still folders to process.
WHILE EXISTS (SELECT id FROM #DirectoryTree WHERE isfile = 0)
BEGIN
   --Select the first row that is a folder.
   SELECT TOP (1)
      @Id = id
      ,@FullPath = fullpath
      ,@Path = @BasePath + '\' + fileName FROM #DirectoryTree WHERE isfile = 0;

   IF @FullPath = @Path
   BEGIN
      --Do this section if the we are still in the same folder.
      INSERT #DirectoryTree (fileName,depth,isfile)
      EXEC master.sys.xp_dirtree @Path,1,1;

      UPDATE #DirectoryTree
         SET fullpath = @Path,
         fileExt = RIGHT(fileName,3)
      WHERE fullpath IS NULL;

      --Delete the processed folder.
      DELETE FROM #DirectoryTree WHERE id = @Id;
   END
   ELSE
   BEGIN
      --Do this section if we need to jump down into another subfolder.
      SET @BasePath = @FullPath;

      --Select the first row that is a folder.
      SELECT TOP (1)
         @Id = id
         ,@FullPath = fullpath
         ,@Path = @BasePath + '\' + fileName
         ,@dbName = fileName
      FROM #DirectoryTree WHERE isfile = 0;

      INSERT #DirectoryTree (fileName,depth,isfile)
      EXEC master.sys.xp_dirtree @Path,1,1;

      UPDATE #DirectoryTree
         SET fullpath = @Path,
         fileExt = RIGHT(fileName,3),
         dbName = @dbName
      WHERE fullpath IS NULL;

      --Delete the processed folder.
      DELETE FROM #DirectoryTree WHERE id = @Id;
   END
END

-- create cursor to loop through the backup files
DECLARE backupFiles CURSOR FOR
SELECT fileName,fileExt,fullpath,dbName
FROM #DirectoryTree
WHERE fileName LIKE '%' + @yesterdayDate + '%'

OPEN backupFiles

-- Loop through all the database backup files (.bak and .trn) from the basepath
FETCH NEXT FROM backupFiles INTO @fName, @fExt, @fullpath, @dbName

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @cmd = '';
   IF (@fExt = 'bak')
   BEGIN
      IF (@dbName != 'model' and @dbName != 'master' and @dbName != 'msdb')
      BEGIN
         SET @cmd = 'RESTORE DATABASE [' + @dbName + ']' + CHAR(13)+CHAR(10) + ' FROM DISK = ''' + @fullpath + '\' + @fName + '''' + CHAR(13)+CHAR(10) + ' WITH NORECOVERY;';
      END
   END
   ELSE IF (@fExt = 'trn')
   BEGIN
      IF (@dbName != 'model')
      BEGIN
         SET @cmd = 'RESTORE LOG [' + @dbName + ']' + CHAR(13)+CHAR(10) + ' FROM DISK = ''' + @fullpath + '\' + @fName + '''' + CHAR(13)+CHAR(10) + ' WITH RECOVERY;';
      END
   END
   PRINT @cmd
   FETCH NEXT FROM backupFiles INTO @fName, @fExt, @fullpath, @dbName
END

CLOSE backupFiles
DEALLOCATE backupFiles

--Clean-up temporary table.
IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
   DROP TABLE #DirectoryTree;
GO

Note:

If databases already exist on the new host (i.e. restoring databases on subsequent run, then use the WITH REPLACE option in the restore script for the .bak files.

Click on the Messages tab to see the generated script. Copy and paste the script into a new screen to execute and restore the databases on the new host.

Here’s a sample of what the generated scripts looks like: