Need setMSSQL adminAPI help

[Perkley1]

I am running CF2016 with MSSQL 2016 Express Edition on a development server.

I have the following script:

<cfscript>
     bLoggedIn = createObject("component", "cfide.adminapi.administrator").login('#cfadminpassword#');
     myDatasourceObj = createObject("component", "cfide.adminapi.datasource");
     if (bLoggedIn == FALSE)
          writeoutput('not loggged in');
     else
          writeoutput('logged in');
     //cfdump(var=myDatasourceObj.setMSSQL);
     myDatasourceObj.setMSSQL(
          driver="MSSQLServer",
          name="my_dsn_name",
          host="localhost",
          port="1433",
          database="my_db_name",
          username="my_db_username",
          password="my_db_password"
     );
</cfscript>

It shows I am logged in just fine to the adminapi, but I get this error when I try using setMSSQL:

-1 : Unable to display error's location in a CFML template.

The error occurred in setdsn.cfm: line 13

Called from setdsn.cfm: line 9

Called from setdsn.cfm: line 1

Called from datasource.cfc: line 580

How do I debug this?  When I use CF Admin and use the same credentials everything works just fine, I am stumped at why it won't work via code.  Any thoughts or suggestions on where to start looking would be great.

[Priyank Shrivastava.]

Hi,

I tried with your script and it worked without any issue. Can you please try with below script, change the database information according to your DB.

<cfscript>

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

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

    adminObj.login("admin");  //CF admin password.

    // Instantiate the data source object.

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

    // Create a DSN.

    myObj.setMSSQL( name="jd_name", host="localhost", port="1433", database="CaseResolution" );

</cfscript>

Database successfully added

[Perkley1]

I copied your example exactly and I still get the same error.  I am on the latest update 4 for CF2016 enterprise.  I wonder what the problem is for me, why the unknown error.

[BKBK]

Does it help to add the class, like this

myDatasourceObj.setMSSQL( 

    driver="MSSQLServer",

    class="macromedia.jdbc.MacromediaDriver",

    name="my_dsn_name", 

    host="localhost", 

    port="1433", 

    database="my_db_name", 

    username="my_db_username", 

    password="my_db_password"

);

[Perkley1]

No, that did not make a difference, but what did make a difference was me taking it out of my .cfc file to run it.  Do I need to give my .cfc file permission to run something like this or can anyone explain why the difference?

[BKBK]

The code you posted should work. Unless there is a bug (As implied by the presence of the system files setdsn.cfm and datasource.cfc in the error message). But I think a bug is unlikely. For two reasons.

Firstly, I copied your exact code to my ColdFusion 2016 test server. Like @Priyanks97293812 I get no problems creating the datasource. Secondly, code similar to yours is present all across my CF2016 applications and I obtain no errors.

<cfscript>

try {

     bLoggedIn = createObject("component", "cfide.adminapi.administrator").login('#cfadminpassword#');

     myDatasourceObj = createObject("component", "cfide.adminapi.datasource");

     if (bLoggedIn == FALSE)

          writeoutput('not loggged in');

     else

          writeoutput('logged in');

  /*** Verify that the values for host and port are correct ***/

     myDatasourceObj.setMSSQL(

          driver="MSSQLServer",

          name="my_dsn_name",

          host="localhost",

          port="1433",

          database="my_db_name",

          username="my_db_username",

          password="my_db_password"

     );

} catch (any e) {

    WriteDump(var=e);

}

</cfscript>

Check which datasources have been created:

<cfscript>

    bLoggedIn = createObject("component", "cfide.adminapi.administrator").login('#cfadminpassword#');

    myDatasourceObj = createObject("component", "cfide.adminapi.datasource");

    writedump(var=myDatasourceObj.getdatasources());

</cfscript>

There might have been a problem during the creation of the test datasource above. Just to be sure, delete it before rerunning the original code:

<cfscript>

    bLoggedIn = createObject("component", "cfide.adminapi.administrator").login('#cfadminpassword#');

    myDatasourceObj = createObject("component", "cfide.adminapi.datasource");

    myDatasourceObj.deletedatasource('my_dsn_name');

</cfscript>

[Perkley1]

I have been trying to figure out what causes the problem and I have finally traced it down to this:

In my application.cfc at the top I have:

<cfcomponent output="false">
<cfscript>
this.name = "camtivahq";
</cfscript>
etc.

I have searched all my code and nothing else mentions that name, yet when I leave it named 'camtivahq' I get the setdsn.cfm error.  If I change it to any other name, I have tried all sorts of combinations, it works fine!  What is up with that name?  I don't have datasource names or tables named that.  I do have a database called 'camtiva_hq'.  Could this really be conflicting with something?

I even tried 'Camtivahq' or 'cAmtivahq' and it works fine, but as soon as it is all lowercase 'camtivahq' it throws the error!  Is that strange or what?  It makes me think I have some other thing set somewhere that it is conflicting with, but I can't find it yet!

[BKBK]

True, weird. Might an old error template have been cached?

Go to the caching page in the Administrator. Clear all the caches. Does the weirdness continue?

[Perkley1]

Yeah, the weirdness of the name is gone, but now it won't work with any name.

I finally just went with this solution:

<cfhttp url="<mypath>" method="post">
<cfhttpparam name="sDatasource" type="formField" value="#sDatasource#">
<cfhttpparam name="sDatabase" type="formField" value="#sDatabase#">
etc.
</cfhttp>

In the folder where I have my code I have a separate application.cfm that is blank, but now it works fine.  I will just add some security to it and at least it will work.  I wish the error I was getting was something I could debug.