October 6, 2016
Configuring Data Sources using Admin API in ColdFusion
Comments
(8)
October 6, 2016
Configuring Data Sources using Admin API in ColdFusion
I am working with ColdFusion Support team.
Staff 11 posts
Followers: 11 people
(8)

Today, APIs are widely used and are very popular in the developer community. APIs make work easier, as developers can perform difficult task programmatically and automate repeatable routines.

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 components, use the below link, RDS needs to be enabled.

    http://{ip address}:<port>/CFIDE/adminapi/

 

How to enable RDS in ColdFusion Administrator

  • Login to ColdFusion Administrator>Security>RDS

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Let us see how the Data Sources can be added in ColdFusion Administrator using the Data Sources Admin API. You can use the syntax to add and modify the Data Sources without affecting the other admin settings. There are multiple attributes that can be used to add the DB which are supported in ColdFusion. For attribute details, please refer to this doc:
http://helpx.adobe.com/coldfusion/configuring-administering/data-source-management-for-coldfusion.html

We will discuss about Microsoft SQL server and Oracle (RAC) with Macromedia driver and Thin drivers. Before that, let us take a look into the Data Source Management for ColdFusion, where users can add parameters.

 

Data Source Management for ColdFusion

This document has all the components which user can define create database connection using Admin API. Please refer to the link below https://helpx.adobe.com/coldfusion/configuring-administering/data-source-management-for-coldfusion.html

 

Using Macromedia driver MSSQL

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

See the sample script below:

<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>

 

Using Macromedia driver MSSQL when using OTHER as driver

Copy the MSSQL driver in cfusionlib and restart ColdFusion service.

See the sample script below:

<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:microsoft:sqlserver://HOST:1433;DatabaseName=DATABASE",      

                                class=" com.microsoft.jdbc.sqlserver.SQLServerDriver",

        name="jd",

        username="",

        password="");

</cfscript> 

 

Using Macromedia driver Oracle when using OTHER as driver

This example describes when users using Oracle RAC with a service name, as ColdFusion do not allow users to add Service name in ColdFusion Admin because only SID is available.

See the sample script below:

<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:oracle://localhost:1521; service_name=DEV",            

                                class="macromedia.jdbc.MacromediaDriver",

        name="DATA SOURCE NAME",

        username="DB USERNAME",

        password="PASSWORD");

</cfscript>

 

Using Oracle thin driver Oracle when using OTHER as driver

ColdFusion Standard does not provide Oracle driver as it is part of ColdFusion Enterprise. To add Oracle DB in Standard edition, use Oracle thin driver and place it in cfusionlib and restart ColdFusion to load it.

See the sample script below:

<cfscript>

    // Login is always required. This example uses two lines of code.

    adminObj = createObject("component","cfide.adminapi.administrator");

    adminObj.login("admin");

    // Instantiate the data source object.

    myObj = createObject("component","cfide.adminapi.Data Sources");

    // Create a DSN.

    myObj.setOther(driver="oracle.jdbc.driver.OracleDriver",

                                url="jdbc:oracle:thin:@//localhost:1521/DEV", 

                                class="oracle.jdbc.driver.OracleDriver",

        name="DATA SOURCE NAME",

        username="DB USERNAME",

        password="DB PASSWORD");

</cfscript>

 

Note: If you are using Sandbox security, user has to provide access to Admin API. Refer this doc: http://help.adobe.com/en_US/ColdFusion/10.0/Admin/WSc3ff6d0ea77859461172e0811cbf364104-7fcf.html

 

8 Comments
2017-07-10 14:44:57
2017-07-10 14:44:57

I’m looking for a sample using setMSSQL()

Like
2017-01-20 05:00:47
2017-01-20 05:00:47

Hi,

Let me check this with ColdFusion 2016. I saw you logged a bug for the same.

Thanks,
Priyank

Like
2017-01-19 09:37:12
2017-01-19 09:37:12

Hi,

There seems to be a bug when setting up MSSQL DSNs where the setMSSQL function completely ignores the “drop” parameter and therefore it isn’t possible to prgrammatically set up a DSN where SQL DROPs are allowed. Obviously one could go into the CFIDE admin and manually tick it but we have hundreds of databases and need to automate it.

Has anybody found a workaround for this or know if this is a known bug with Adobe?
PS: This is happening in CF2016 (we are upgrading a legacy app from CF9 so not sure in which version the Drop default restriction got introduced)

Like
2016-11-28 13:32:32
2016-11-28 13:32:32

Hi Charlie,

Thanks for the suggestion, I will correct it. I replaced it with a wrong version. Thanks for pointing it out.

Thanks,
Priyank

Like
2016-10-10 14:36:50
2016-10-10 14:36:50

Thanks Aaron!

Like
2016-10-07 22:51:33
2016-10-07 22:51:33

Hi Priyank,

Excellent article! Just a note: CF Admin and Admin API have different defaults when creating DSNs.

Thanks!,
-Aaron

Like
2016-10-07 15:48:15
2016-10-07 15:48:15

Thanks Chris, would post few more article which will help our community.

Like
2016-10-07 10:40:38
2016-10-07 10:40:38

I appreciate the latest blitz of technical articles. Great start. Thx

Like
Add Comment