Is it possible to Create Database in CF

[doug777]

Is there any way to issue a SQL CREATE DATABASE request from within CF e.g.

CREATE DATABASE #Client.DSN# ON (
NAME = #Client.DSN#,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\#Client.DSN#.mdf'
);

I can then use a script to make the connection to the database using the supplied datasource.cfc.

But how to create the database in the first place??

Doug

[BKBK]

You must first create a datasource in the Coldfusion Administrator to enable Coldfusion to connect to the database server. After that, just do

<cfquery name="q" datasource="datasource_name">
create database #Client.DSN#
</cfquery>

[Guillermo1009]

I am not sure if this helps you but this is what I did in CFMX7.

1. Create a database in MS SQL Server call it DB_MASTER (it will server as a template.)
2) Run the command to create your new database from the template DB_MASTER
<cfquery name="createDB" datasource="#datasource#">
RESTORE DATABASE #trim(DBName)#
FROM DB_MASTER
WITH RECOVERY,
MOVE 'DB_MASTER_data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL$#trim(MSSQLserverName)#\Data\#trim(DBName)#_data.mdf',
MOVE 'DB_MASTER_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL$#trim(MSSQLserverName)#\Data\#trim(DBName)#_log.ldf'
</cfquery>

3. Now run the script that will create a datasource to the new database you just created in step two. After running the script below you can alter the new database as you wish GET, DELETE, UPDATE, CREATE TABLE .......

<cfscript>
adminObj = createObject("component","cfide.adminapi.administrator");
adminObj.login("[YOUR_CF_ADMIN_LOGIN]");

myObj = createObject("component","cfide.adminapi.datasource");
myObj.setMSSQL(driver="MSSQLServer",
name="[YOUR_DATASOURCE_NAME]", host = "127.0.0.1", port = "1433",
database = "[YOUR_NEW_DATABASE_NAME]", username = "[USER_NAME]", password="[PASSWORD]", login_timeout = "30",
timeout = "20", interval = 7, buffer = "64000", blob_buffer = "64000", setStringParameterAsUnicode = "false",
description = "", pooling = true, maxpooledstatements = 1000, enableMaxConnections = "true",
maxConnections = "300", enable_clob = true, enable_blob = true, disable = false, storedProc = true,
alter = false, grant = true, select = true, update = true, create = true, delete = true, drop = false,
revoke = false );
</cfscript>


I hope the code gets there in this message if not just email me at
gmcruz at email dot com

[doug777]

I can't get the suggestion from BKBK to work. Say I have a database called my_dbmaster, then if I use this as the datasource, I get an error presumably because CF generates a hidden piece of SQL something like USE my_dbmaster and SQL Server then tries to use CREATE DATABASE inside this database which surely can't work?

However, the suggestion from theMex is ingenious and does work. I can create as many databases as I like using this code. Many thanks for your time and trouble.

Doug

[BKBK]

I can't get the suggestion from BKBK to work. Say I have a
database called my_dbmaster, then if I use this as the datasource,
I get an error presumably because CF generates a hidden piece
of SQL something like USE my_dbmaster and SQL Server then
tries to use CREATE DATABASE inside this database which
surely can't work?

The code I gave you works for MS SQL Server 2005 Enterprise Edition and for MySQL 5.0. So I suppose you're on MS SQL Server 2000. Even then, you will observe that the code in theMex's cfscript tag is essentially equivalent to

<cfquery name="q" datasource="YOUR_DATASOURCE_NAME" username = "USER_NAME" password="PASSWORD">
create database YOUR_NEW_DATABASE_NAME
</cfquery>

But, hey, theMex's tip works, that's the important thing.

[doug777]

You're quite right. Many apologies. It does work. I think I must have had the user name wrong at the time.

Just for the record, the script from the ColdFusion Documentation book: Configuring and Administering ColdFusion MX / Using The ColdFusion MX Administrator / Administrator API ... has several errors in it that don't show up as errors, but do make the settings incorrect:

First the login setting should be:
adminObj.login("your_admin_user_name", "your_admin_password");

In myObj.setMSSQL, selectmethod = "direct" is missing and if not present this gets set to cursor.

The following are all numeric fields and do not get set if they are input as strings login_timeout, timeout , buffer, maxConnections and blob_buffer.

There is no such attribute as enable_clob and enable_blob though if you want clob and blob set to false, this will not matter. The actual attributes are disable_blob and disable_clob.

None of the boolean attributes should be passed as strings.

Doug