Shaun Mccran

My digital playground

23
M
A
Y
2008

Programmatically adding a datasource to cf server

In the midsts of developing a new project I stumbled upon the need for administrators to be able to create new instances of the entire application, on the fly.

This threw up a few issues, primarily how do we re-create the generic data structure, but with specific names etc for a new instance.

I figured that you can do it in cf admin, so it must be do-able right? Handily there is an article on this in the coldFusion Cookbook (here.)

So expanding on that article we can use the same code, but populate it with variables to create the datasource:

view plain print about
1<cfscript>
2// Login is always required.
3
createObject("component","cfide.adminapi.administrator").login("admin");
4
5// Instantiate the data source object.
6
myObj = createObject("component","cfide.adminapi.datasource");
7
8// Required arguments for a data source.
9
stDSN = structNew();
10stDSN.driver = "MSSQLServer";
11stDSN.name="#variables.dbName#";
12stDSN.host = "#variables.dbServer#";
13stDSN.port = "1433";
14stDSN.database = "#variables.dbName#";
15stDSN.username = "#variables.userName#";
16stDSN.password = "#variables.password#";
17
18// Optional and advanced arguments.
19
stDSN.login_timeout = "29";
20stDSN.timeout = "23";
21stDSN.interval = 6;
22stDSN.buffer = "64000";
23stDSN.blob_buffer = "64000";
24stDSN.setStringParameterAsUnicode = "false";
25stDSN.description = "#variables.dbName# intranet datasource, auto generated.";
26stDSN.pooling = true;
27stDSN.maxpooledstatements = 999;
28stDSN.enableMaxConnections = "true";
29stDSN.maxConnections = "299";
30stDSN.enable_clob = true;
31stDSN.enable_blob = true;
32stDSN.disable = false;
33stDSN.storedProc = true;
34stDSN.alter = false;
35stDSN.grant = true;
36stDSN.select = true;
37stDSN.update = true;
38stDSN.create = true;
39stDSN.delete = true;
40stDSN.drop = false;
41stDSN.revoke = false;
42
43//Create a DSN.
44
myObj.setMSSQL(argumentCollection=stDSN);
45
</cfscript>

Then simply run a script to create the actual database.

view plain print about
1<cfquery datasource="Master">
2USE [master]
3
4CREATE DATABASE [#variables.dbName#] ON PRIMARY
5( NAME = N'#variables.dbName#_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\#variables.dbName#_Data.MDF' , SIZE = 1792KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
6 LOG ON
7( NAME = N'#variables.dbName#_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\#variables.dbName#_Log.LDF' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
8
9EXEC dbo.sp_dbcmptlevel @dbname=N'#variables.dbName#', @new_cmptlevel=80
10
11IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
12begin
13EXEC [#variables.dbName#].[dbo].[sp_fulltext_database] @action = 'disable'
14end
15</cfquery>

Note that this is based on using SQL server, and you need a datasource in cf admin configured for the 'Master' datasource, as thats where the other datasource references are held.

TweetBacks
Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
Simon Silvestor Clarke's Gravatar Educated person is respected in the community and all over the world. The talents and skills of the http://www.ukbestdissertations.com/ educated person is awarded and rewarded all over the world. Education is the land and the desires of the people. It helps to learn social skills.
# Posted By Simon Silvestor Clarke | 18/11/2015 03:21
Back to top