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 \cfusion\lib\ 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 \cfusion\lib 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 to “Configuring Data Sources using Admin API in ColdFusion”

  1. Chris G
    I appreciate the latest blitz of technical articles. Great start. Thx
  2. Priyank
    Thanks Chris, would post few more article which will help our community.
  3. Aaron Neff
    Hi Priyank,

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

    Thanks!,
    -Aaron
  4. Priyank
    Thanks Aaron!
  5. Charlie Arehart
    Folks, I see a few problems with this post. Since this was one of a flush of them about the time of the CF Summit, I missed it at the time.

    First, in the examples shown, the first one says that it will be done using the mssql driver, and then the next would be doing the "other" driver approach. But both use the setother method. The first should be using setMSSQL. One can see an example of that on this page: http://help.adobe.com/en_US/ColdFusion/10.0/Admin/WSc3ff6d0ea77859461172e0811cbf364104-7fcf.html

    Second, I was surprised to see the mention at the top that "to access the components, use the below link" and it points to the web-based means of accessing the Admin API. Well, one does NOT need to be able to do that to "use the admin api". One can access the API purely as an internal CFC, not going through the web-based approach.

    Thankfully, all of the examples here DO show accessing the API as purely internal CFCs.

    What Priyank was getting that, though, was that as with any CFC, there is this web-based means of viewing the available methods and their arguments. But we can also just dump the CFC (via CFDUMP) after obtaining a reference, like the code above does with the myobj object, as in <cfdump var="#myobj#">.

    That would also remove the need to enable RDS. Again, that's only needed to use this web-based CFC explorer that is shown when one browses a CFC.

    Since even the CF installer and docs point out that one should not enable RDS in production (and people should even be very careful enabling it in test for any publicly accessible CF instance), it just seems that this post could have been a lot more clear about the relationship of this CFC browser feature, RDS, and separately USING the Admin API.

    I suppose if people would read this comment, it may be enough to offer clarification. But Priyank, if you may be motivated to tweak it, people do read blog posts months and years after they were written, so improvements could help any future readers.

    Hope that's helpful.
  6. Priyank
    Hi Charlie,

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

    Thanks,
    Priyank
  7. Jens
    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)
  8. Priyank
    Hi,

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

    Thanks,
    Priyank

Leave a Comment

Leave this field empty: