How to create a data source in ColdFusion

Create a data source in ColdFusion Administrator

The ColdFusion Administrator organizes information about all ColdFusion server database connections in a single location. In addition to adding data sources, you can use the Administrator to specify changes to your database configuration, such as relocation, renaming, or changes in security permissions.

Adding data sources in the Administrator

You use the ColdFusion Administrator to quickly add a data source for use in your ColdFusion applications. When you add a data source, you assign it a data source name (DSN) and set all information required to establish a connection.

To add a data source,

  1. In the ColdFusion Administrator, select Data & Services > Data Sources.

  2. In Add New Data Source, enter a data source name; for example, MyTestDSN. The following names are reserved; you cannot use them for data source names:

    • service
    • jms_provider
    • comp
    • jms
  3. Select a driver from the drop-down list; for example, Microsoft SQL Server. To add the driver, click Add.

    1. In the Database field, enter the name of the database.
    2. In the Server field, enter the network name or IP address of the server that hosts the database, and enter any required Port value. For example, the bullwinkle server on the default port.
    3. If your database requires login information, enter your user name and password.
  4. Click Show Advanced Settings to specify any ColdFusion specific settings; for example, to configure which SQL commands can interact with this data source.

  5. To create the data source, click Submit. ColdFusion automatically verifies that it can connect to the data source.

    (Optional) To verify this data source later, click the verify icon in the Actions column.

For more information on creating dat asources, see Data Source Management for ColdFusion.

Create a data source using Admin API

In ColdFusion, there are Admin APIs available through which developers can add, modify, and delete Admin task programmatically. This is helpful for developers who do not have access to ColdFusion Administrator, for example, component event gateway, data sources, mail, and so on.

To access the Admin APIs, enable RDS.

To enable RDS, click Security > RDS and enable the option Enable RDS Service.

Connect to Microsoft SQL Server

ColdFusion provides MSSQL driver in both Standard and Enterprise editions. This example is with Macromedia drivers:

<cfscript>
// Login is always required. This example uses two lines of code.
adminObj = createObject("component","cfide.adminapi.administrator");
adminObj.login("PASSWORD"); //CF admin password.
// Instantiate the data source object.
myObj = createObject("component","cfide.adminapi.Data Sources");
// Create a DSN.
myObj.setOther(driver="macromedia.jdbc.MacromediaDriver",
url="jdbc:macromedia:sqlserver://localhost:1433;databaseName=CaseResolution",
class="macromedia.jdbc.MacromediaDriver",
name="jd",
login_timeout = "29",
timeout = "23",
interval = 6,
buffer = "64000",
blob_buffer = "64000",
setStringParameterAsUnicode = "false",
description = "JD",
pooling = true,
maxpooledstatements = 999,
enableMaxConnections = "true",
maxConnections = "299",
disable_clob = true,
disable_blob = true,
disable = false,
storedProc = true,
alter = false,
grant = true,
select = true,
update = true,
create = true,
delete = true,
drop = false,
revoke = false
);
</cfscript>
<cfscript> // Login is always required. This example uses two lines of code. adminObj = createObject("component","cfide.adminapi.administrator"); adminObj.login("PASSWORD"); //CF admin password. // Instantiate the data source object. myObj = createObject("component","cfide.adminapi.Data Sources"); // Create a DSN. myObj.setOther(driver="macromedia.jdbc.MacromediaDriver", url="jdbc:macromedia:sqlserver://localhost:1433;databaseName=CaseResolution", class="macromedia.jdbc.MacromediaDriver", name="jd", login_timeout = "29", timeout = "23", interval = 6, buffer = "64000", blob_buffer = "64000", setStringParameterAsUnicode = "false", description = "JD", pooling = true, maxpooledstatements = 999, enableMaxConnections = "true", maxConnections = "299", disable_clob = true, disable_blob = true, disable = false, storedProc = true, alter = false, grant = true, select = true, update = true, create = true, delete = true, drop = false, revoke = false ); </cfscript>
<cfscript>
    // Login is always required. This example uses two lines of code.
    adminObj = createObject("component","cfide.adminapi.administrator");
    adminObj.login("PASSWORD");  //CF admin password.
    // Instantiate the data source object.
    myObj = createObject("component","cfide.adminapi.Data Sources");
    // Create a DSN.
    myObj.setOther(driver="macromedia.jdbc.MacromediaDriver",
        url="jdbc:macromedia:sqlserver://localhost:1433;databaseName=CaseResolution",      
     class="macromedia.jdbc.MacromediaDriver",
     name="jd",
     login_timeout = "29",
     timeout = "23", 
     interval = 6,
     buffer = "64000",
     blob_buffer = "64000",
     setStringParameterAsUnicode = "false",
     description = "JD",
     pooling = true,
     maxpooledstatements = 999,
     enableMaxConnections = "true",
     maxConnections = "299",
     disable_clob = true,
     disable_blob = true,
     disable = false,
     storedProc = true,
     alter = false,
     grant = true,
     select = true,
     update = true,
     create = true,
     delete = true,
     drop = false,
     revoke = false 
     );
</cfscript>

For more information, see Data sources in ColdFusion Admin API reference.

Supported Databases

You can create a data source for the following databases:

  • Apache Derby
  • DB2
  • JEE Data Source (JNDI)
  • Microsoft Access
  • Microsoft SQL Server
  • MySQL (DataDirect)
  • MySQL 5
  • Oracle
  • PostgreSQL
  • Sybase

Get help faster and easier

New user?