1 person found this helpful
Yes there is a way, what I did I created a CF datasource to master database and then used this connection to create/alter/drop tables.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[Users](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [nvarchar](50) NOT NULL,
[Password] [nvarchar](15) NOT NULL,.....etc
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
You mentioned that you are able to create datasource, this is something in my to-do list and I would apprechiate some help. All of this sounds very familiar to me and you might be working on somehing that I've already did a lot. One thing that might be helpfull to you that I've done is to generate scripts to create site in IIS and virtual folders for the sites. Let me know if you need some more info. thanks
Technically no, because ColdFusion doesn't do anything. All it does is bundle up the SQL string and pass it to the database driver.
But yes, you can provide any valid SQL inside a <cfquery...> block and it will be passeed to the database and the database will do whatever the SQL statement is trying to tell it to do.
This assumes, of course, that security has not been put in place to prevent this type of use in the database. I.E. It is usually considered a very bad idea to have the database account that ColdFusion connects with from having create, alter and drop permissions. The ColdFusion administrator also offers a security feature to try and deny these command strings from the SQL statments being processed.
But if you are willing to live with that risk, you can have your CFML code do all kinds of powerful things with your database, just be very aware that if you can get your CFML to do this, hackers maybe able to do so as well.
coldfusion has a variety of support for creating tables, altering them etc,
CF doesn't support any SQL in the sense you are thinking. All CF does is pass the SQL string to the DB driver, which passes it to the DB. CF doesn't execute any of the SQL itself.
For example would it be possible to run this script where #FORM.datasource# is a form variable from a web form...
Well... the best way to find out is to try it, I guess.
You'll need to be connecting with a DB user that has DB-create privileges...
It turns out that it was really quite simple.
All it took was a simple:
<cfquery name="NAME" datasource="EXISTING DATASOURCE">
CREATE DATABASE database_name
The answer to using the #FORM.datasource# is yes. It works just fine, just as you can build a query variable and run it like so:
<cfset mydatasource = "newData">
<cfset query = "SELECT * FROM my_table">
<cfquery name="name" datasource="#mydatasource#">
This is the same thing in CF as:
<cfquery name="name" datasource="newData">
SELECT * FROM my_table
If you are going to do this, you need something to prevent duplicate db names on your db server and duplicate datasources in cf.
CF and/or MS SQL will throw an error if you try to create a duplicate database, but a simple check would be easy to place in.
As for the commend above on how to remotely create a CF datasource with <cfscript> you'll find the code below. For more information, search coldfusion API on the CF eDocs.
// Login is always required. This example uses two lines of code.
adminObj = createObject("component","cfide.adminapi.administrator");
// Instantiate the data source object.
myObj = createObject("component","cfide.adminapi.datasource");
// Create a DSN.
host = "18.104.22.168", // sql server host
port = "1433",
database = "database_name",
username = "your_sql_username",
password = "your_sql_password", // if neccessary
login_timeout = "29",
timeout = "23",
interval = 6,
buffer = "64000",
blob_buffer = "64000",
setStringParameterAsUnicode = "false",
description = "datasource_description",
pooling = true,
maxpooledstatements = 999,
enableMaxConnections = "true",
maxConnections = "299",
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 );
I just realized that I posted example on how to create table, sorry. Here is how you can create db and check if it exists first.
if not exists(select * from sys.databases where name = '#dbname#')
CREATE DATABASE #dbname#
( NAME = #dbname#_dat,
FILENAME = 'C:\SQL\#dbname#.mdf' )
( NAME = '#dbname#_log',
FILENAME = 'C:\SQL\#dbname#.ldf')
You can use the rest of the db options you need, just plug them into the query