Configuring Data Sources using Admin API in ColdFusion

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

 

15 thoughts on “Configuring Data Sources using Admin API in ColdFusion

  1. Hi Charlie,

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

    Thanks,
    Priyank

  2. 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)

  3. Folks, the code in this post is a mess, on so many levels. I share here lots of corrective and elaborated info for any readers who come across this post, wanting to create DSNs using the Admin API.

    I do hope someone from Adobe could make some vital needed corrections. The code literally FAILS until these are corrected, and there are also things that are just not correct even though they’d not fail.

    Also, there were some questions asked by others above. I hope to address some of those here as well.

    (Adobe, I hope you will address the key issues in points 1 and 2 below, and hopefully also point 3.

    And as for people offering kind regards about the post here, perhaps they were more just appreciating the level of depth, which was indeed nice to see, especially since there are few examples of the Admin API in the docs. But they can’t have run the code, or they would have experienced and I hope complained of these things sooner.)

    1) First, and worst of all, note that all 4 examples mistakenly use:

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

    There should be no space between “data” and “sources”, and it’s “datasource” singular, not plural. So this MUST BE CHANGED to be:

    myObj = createObject(“component”,”cfide.adminapi.datasource”);

    And for case-sensitive setups, the CFC name is also lowercase, and the CFIDE by default is capitalized in the file system as CF is installed, so it should be:

    myObj = createObject(“component”,”CFIDE.adminapi.datasource”);

    2) Second, why is the first example for MSSQL using the “setother” method? There is a much simpler setMSSQL, where the user need not know how to build a JDBC URL, and need not set ANY of those many listed args.

    The following would be the least that one would need to create a SQL Server DSN using the Adobe-provided SQL Server driver (focus of the first example above):

    myObj.setMSSQL(name=”mydsn”, host=”localhost”, database=”mydb”, port=”1433″, username=”myuname”, password=”mypwd”);

    That’s just a lot cleaner. (And the same could be done in the first Oracle example, which could use setOracle. Only the 2nd example for each needs to use setOther, because you are pointing to a non Adobe-provided JDBC driver.)

    Now, I say these args are “the least one would need” because there are of course many other DSN settings which CAN be configured, indeed MORE than are listed in the examples in the post above. (And I’ll have more to say about the defaults they would take, if not specified, in a moment.)

    3) But going back to the first MSSQL example above, the values provided for the arguments used are not at ALL like the defaults that a new DSN would have if added in the CF Admin. For instance, note that it uses:
    login_timeout = “29”,
    timeout = “23”,
    interval = 6,
    as well as:
    maxpooledstatements = 999,
    enableMaxConnections = “true”,
    maxConnections = “299”,
    and so on
    Well, those are not at ALL the defaults that CF would create if you created a new DSN in the Admin.

    And they are also NOT the defaults that CF would create if you created a new DSN in the Admin API and left them unspecified. They are just some really curious values that someone put into the example. (Fortunately they are not in the next 3 examples.)

    4) Speaking of defaults (if you did not specify some arguments), I would add, as Aaron noted above, that the defaults that all these Admin API set* methods set for DSNs are indeed different **than the CF admin would make if you added a DSN that way**.

    For instance, and this is pretty shocking to discover: the selectmethod is set to “cursor” when a DSN is created this way. If created in the Admin, the selectmethod is set to “direct”. (This is one of those things you see only if you click “advanced settings” in the CF Admin DSN page.)

    But those two values are VERY different from each other (and can have a dramatic effect on the underlying JDBC processing that would happen for SQL statements against the DSN), so it’s a real surprise to see that the Admin API chooses this default.

    And that means that anyone who has used this code above since it was posted a year ago is running with that “cursor” mode. You can correct that for existing DSNs by editing the DSN in the CF admin.

    But as for this code here, I’m saying that most folks would want to add an argument of selectmethod=”direct” in the example above:

    myObj.setMSSQL(name=”mydsn”, host=”localhost”, database=”mydb”, port=”1433″, username=”myuname”, password=”mypwd”, selectmethod=”direct”);

    5) And guess what: if you are just reading this post and had just created some DSN this way, you can just run that method again with that new argument added, and it will replace the DSN, in place (no need to delete it).

    But note that it is not really “updating” it, in that it will ONLY set the DSN to have whatever values you DO set in the method call (so you can’t use this to update some existing DSN if it may have OTHER settings which you DO NOT set in that call. Those would instead revert to defaults.) A real update method would be nice, but I am not aware of one.

    6) So what are the defaults, if you create a DSN in the Admin API without specifying them? Well (again, I am only focused on setMSSQL here, and per CF2016 update 5), I also see that it sets the MaxPooledStatements to 1000 if not specified, whereas in the Admin a new DSN would default to 100. Wow. And the applicationintent is set by default to “readwrite”, whereas the default in the admin would set that to the empty string.

    Finally as for the defaults for the arguments related to the “allowed sql” section (of the “advanced settings” for a DSN), these are set by default (in the setMSSQL method, if not specified) to all be true: select, insert, update, and delete, as well as create, drop, alter, grant, revoke, and stored procedures.

    That’s the same as the CF Admin…except…that if one enables the “secure profile” feature (a global admin security feature new to CF 10 and above), then in later releases of CF a newly created DSN in the admin would have defaults of only select, insert, update, and delete being enabled. But the AdminAPI setMSSQL method is not paying attention to that “secure profile” (in a test I just did) and still created a DSN with ALL of these enabled.

    As for other options, I find the rest to be the same between a DSN created in the Admin API (if left off) as in the Admin. (For instance, username and password would be the empty string.)

    (If you’re curious, when running setMSSQL, the minimum required arguments are name, host, and database. Everything else will assume the defaults above.)

    7) There’s of course a real need for more and better docs of the Admin API, which has pretty much languished for the lack of any since its release in CF7.

    I will add, for those who didn’t know, you can indeed see built-in API docs via web-based the CFC explorer (which has been in CF since 6):

    http://[yourserver]/CFIDE/adminapi/CFIDE/adminapi/datasource.cfc

    This would show all the methods in that CFC, and their arguments, etc. (And you can use it to see any CFC at all, including the others in that adminapi folder.)

    Sadly (for some users) this mechanism requires that the AdminAPI folder be web-accessible. And for any who have locked down their server so that this CFIDE/adminapi path is blocked, you won’t be able to use it.

    Fortunately, for those on CF2016, since it uses is built-in web server for access to the Administrator, by default on a non-standard like 8500, that means that you may find that this URL DOES work for you. (One does also need to know the CF Admin or RDS password to get past this page, of course, so it’s not “wide open”.)

    But the docs don’t show what the defaults would be. That really would require someone testing and documenting separately. That would be a great community project, rather than waiting still longer for it to come from official sources. 🙂

    Hope all that’s helpful.

  4. Wow, here’s some more bad news/good news.

    1) First the bad news: I just discovered that these “strange values” (point 3) are in fact also used in the one page in the CF docs that has long shown a demo of the use of the Admin API, buried deep in the “Configuring and Administering ColdFusion” manual:

    https://helpx.adobe.com/coldfusion/configuring-administering/using-the-coldfusion-administrator.html#main-pars_text_1834188211

    (Tragically, I find this exact same example and those weird values on this page in all versions of the docs going back to CF7, when the API was first documented! Grr.)

    So if someone from Adobe tweaks this blog post’s content, I hope they could also tweak at least the example in the online version of the CF2016 docs (the link above).

    2) But here also is some very, very good news (for those interested in the Admin API), and this seems something very new.

    I had lamented in my last comment about the lack of any documentation of the various CFCs and their methods.

    Well, I found that it indeed IS now documented, COMPLETELY, though only as of the CF2016 online version of the docs (for which we can’t currently get PDFs but can only view it online)

    Specifically it is now the last chapter of that same “Configuring and Administering ColdFusion” manual, at:
    https://helpx.adobe.com/coldfusion/configuring-administering/coldfusion-administrator-api-reference.html

    If one wonders how I could tell it was the last last chapter of that manual, it is hard, since the online docs lack good breadcrumps or an always-present table of contents. I did find it as the last section in this online version of that manual’s table of contents:
    https://helpx.adobe.com/coldfusion/configuring-administering/topics/configuring-and-administering.html

    It has all the CFCs, all their methods, and each method’s arguments, including descriptions. Basically everything that one would have seen by visiting that CFC explorer I mentioned above, so that those who can’t use that tool can now see all about the Admin API. Sweet. Thanks, to whoever put it there!

    (In case anyone may wonder if that was added since I posted my comment last night, no. It was not. 🙂 I can see in the google cache of the page that it existed before then. But hey, I’m just thrilled it’s now there for all to find! )

  5. Well, grr. No sooner had I posted that “very good news” in the previous comment, when I then noticed that the page ONLY documents the first 5 of the Admin API CFCs. There are 13 more it does NOT document.

    I wonder if anyone at Adobe might be able to look into whether those do exist somehow somewhere and perhaps are on a “next page”. Sadly, the CF online docs (as currently implemented don’t offer any “next page” feature, nor again any breadcrumbs.)

    So for now we have docs of at least 5 of the 18 admin API CFCs. It’s a step in the right direction.

    • Thank you for writing this. I thought his post seemed slightly off, and having connected to various db’s of various flavors I’m glad to see I was correct. Your posts clarified things nicely, and my solution is working like a charm.

      Thanks again-

Leave a Reply

Your email address will not be published. Required fields are marked *