Copy link to clipboard
Copied
I have been using ColdFusion's storedProc() service for a while with no issue. However, I have run into a scenario where I have a local stored procedure executing a remote stored procedure. When I attempt this via ColdFusion, I receive a very cryptic error:
The DBMS has returned the command code 224...
The following is the code I am using:
variables.storedProcService = new storedProc();
variables.storedProcService.clear();
variables.storedProcService.setAttributes(datasource="DATASOURCE", procedure="STOREDPROCNAME");
variables.storedProcService.addParam(cfsqltype="CF_SQL_INTEGER", type="in", value=0);
variables.storedProcService.execute();
However, executing the exact same procedures from SQL Server Management Studio results in no issues.
Then, when I use the following code:
variables.storedProcService = new storedProc();
variables.storedProcService.clear();
variables.storedProcService.setAttributes(datasource="DATASOURCE", procedure="STOREDPROCNAME", username="USERNAME", password="PASSWORD");
variables.storedProcService.addParam(cfsqltype="CF_SQL_INTEGER", type="in", value=0);
variables.storedProcService.execute();
The execution runs without issue. My question is this, does ColdFusion cache SQL Server/Datasource credentials? Why does it [seem to] not use the credentials I provided in Jrun?
Copy link to clipboard
Copied
HiTopp wrote:
variables.storedProcService = new storedProc(); variables.storedProcService.clear(); variables.storedProcService.setAttributes(datasource="DATASOURCE", procedure="STOREDPROCNAME", username="USERNAME", password="PASSWORD"); variables.storedProcService.addParam(cfsqltype="CF_SQL_INTEGER", type="in", value=0); variables.storedProcService.execute();
The execution runs without issue.
I can actually just include the password attribute and the stored procedure runs just fine.
Copy link to clipboard
Copied
Quick idea - try disabling the "Maintain Connections" checkbox in the datasource tab, it could be maintaining a connection rather than creating a new one.
Copy link to clipboard
Copied
Are you talking about CFAdmin or Jrun? Right off the bat I am not finding your referenced option in either.
Copy link to clipboard
Copied
In CFAdmin go to the DSN, click "Show Advanced Settings" and it's in there. Should then create a new connection for every request.
Copy link to clipboard
Copied
This is all that I am given in the "Advanced Settings". Right now, I am referencing developer edition, but I verified on an enterprise edition that these are the same options as well.
Copy link to clipboard
Copied
I think it may be because I am using a JNDI driver and not a SQL Server driver.
Copy link to clipboard
Copied
Ah, quite possibly. Why aren't you using the native driver?
Copy link to clipboard
Copied
Honestly, I am not entirely sure. I am not an expert in ColdFusion and the application I am supporting was using JNDI drivers before I started supporting it.
Copy link to clipboard
Copied
In which case it might be worth creating a second DSN with the native driver, and use that; it may sort your issue.
Copy link to clipboard
Copied
But if the correct datasource credentials are specified in Jrun, why would ColdFusion not use those credentials? Using those same credentials in SQL Server Management Studio and running the stored procedures works just fine.
Copy link to clipboard
Copied
Honestly I have no idea. Just throwing ideas out there.
Copy link to clipboard
Copied
I kept attempting to troubleshoot the problem from a different standpoint because an associate of mine was able to run the stored procedures with no problem. He did not have to include the credentials in the procedure call and his CFAdmin datasources used the JNDI driver.
So I compared our two ColdFusion environments and noticed that he was using the System Registry to store client variables (i.e. CFID, CFTOKEN, etc) instead of a database. I switch my environment to use the System Registry and the two procedure calls worked just fine. Isn't using a database for this purpose a useable replacement for the System Registry? Why would using a database not work in this instance?