check out the application.cfc this.datasources syntax…
open a datasource definition in the web admin, have a look at the bottom of an existing datasource, there’s a cfml snippet
this.datasources["xxxxx"] = {
class: 'com.microsoft.sqlserver.jdbc.SQLServerDriver'
, bundleName: 'com.microsoft.sqlserver.mssql-jdbc'
, bundleVersion: '6.2.2.jre8'
, connectionString: 'jdbc:sqlserver://x.x.x.x:xxx'
, username: 'xxxxxx'
, password: "encrypted:xxxxx"
// optional settings
, clob:true // default: false
, connectionLimit:500 // default:-1
, connectionTimeout:60 // default: 1; unit: seconds
, validate:false // default: false
};
Mik, did you ever solve this? I not, did you heed Brad’s observation that it could be an anti-virus doing a scan of the files being loaded or modified? It could even be more than one a/v.
I’ve seen it happen. You could try turning them off for the test, and if it helps try excluding affected folders from being scanned.
1 Like
@Michael_Muller Can you refactor your code to use Application.cfc rather than Application.cfm?
If that’s the case, and you need a solution that works on Lucee (as opposed to a cross-engine solution), then you do not need to use cfadmin’s updateDatasource. You can simply add new datasources in the body of Application.cfc by adding data to this.datasources. For example:
/** Application.cfc */
component {
this.name = "SomeApp";
this.datasources = {
DsOne : {
class : "com.microsoft.jdbc.sqlserver.SQLServerDriver"
,connectionString: "jdbc:sqlserver://hostnameone.org:1433"
,username : "user1"
,password : "S3cret" // can be encrypted too
}
,DsTwo : {
class : "com.microsoft.jdbc.sqlserver.SQLServerDriver"
,connectionString: "jdbc:sqlserver://hostnametwo.org:1433"
,username : "user2"
,password : "S3cret"
}
}
If you define one datasource in the Lucee Admin, you can go into its details and at the bottom of the page you will find a snippet that you can copy and paste into your Application.cfc file.
I ultimately fixed it by adding the DSNs directly to the XML and setting the Luccee configuration check-for-changes=“true”.
I need all the DSNs available server wide as there are two applications running that need access to all the databases. Otherwise I’d use the cfc method and be done with it.
@Michael_Muller If you resorted to editing the XMl directly, look into CFConfig. It’s a CLI tool (and service layer) that will manage the configuration files for Lucee and Adobe servers for you.
Adding a new datasource can be scripted like this:
cfconfig datasource save name=myDS dbdriver=mysql host=localhost port=3306 username=root password=secret to=/path/to/server/home
Or stored in JSON and imported in mass like so:
cfconfig import from=mySettings.json to=/path/to/server/home
I’ll give that a shot. Also wondering if there’s a command line that forces Lucee to reload the XML without a full restart.
cfconfig set watchConfigFilesForChangesEnabled=true
Ok, a little over a year later and I still haven’t fully solved the issue. While I was able to edit the XML file and have Lucee find it and reload, it still wasn’t optimal. So, now I’m thinking of a different solution, which someone suggested to me back a year ago, but I simply moved on with other issues and projects that cropped up.
What if I used a single DSN to access the MS SQL Server, and then queried the specific database within the query itself? Something like this:
<cfquery name="qsSample" datasource="mainDSN">
SELECT this, that, theOtherThing
FROM [specificDatabase].[dbo].[tableName]
</cfquery>
<cfquery name="qsShmample" datasource="mainDSN">
SELECT thingOne, thingTwo, thingThree
FROM [anotherDatabase].[dbo].[otherTableName]
</cfquery>
This means I won’t have to create any new DSNs for this application, ever, which is my only issue. Just create the database as usual, and always hit it through “mainDSN” or whatever.
The question then arises, how many connections can I open through a DSN? Is there a limit? If there is a limit, is the limit per total connections from Lucee, or connections per DSN?
Actually, my buddy suggested not adding the datasource argument to cfquery at all, relying ONLY on the FROM command in the query itself.
<cfquery name="qsStuff">
SELECT stuff
FROM [aDatabase].[dbo].[table]
</cfquery>
Thoughts?
Thanks in advance.
1 Like
Nothing wrong with that.
how many connections can I open through a DSN? Is there a limit? If there is a limit, is the limit per total connections from Lucee, or connections per DSN?
Look at the settings available to you when editing a datasource in the admin and let us know if it doesn’t answer those questions.
Actually, my buddy suggested not adding the datasource argument to cfquery at all, relying ONLY on the FROM command in the query itself.
This makes no sense at all. It is possible to leave off the datasource attribute to you cfquery tag if you have a default datasource set in your Application.cfc’s this.datasource. however, that has nothing at all to do with the SQL inside the actual CFQuery tag and whether or not it happens to reference a database as part of the table’s name.
1 Like
Just as a follow-up, I’ve been using this method for a couple weeks now, and it works fine. I have Connection Limit set to 1000, though, as “infinity” scares me a little. The server does hang now and then for up to 10 seconds, but I attribute that to some pages on my sites using upwards of 100 queries to pull all the info together. Working hard to reduce that. Would also be nice if the limit could be higher, like 5000.