http://coldfusion.com logo
Join Slack
Powered by
I have an .env question about connection to a MSSQ...
# box-products
m
I have an .env question about connection to a MSSQL DB. I was able to get connected to the DB on startup of commandbox but I had an error that required me to manual change a setting in the CFAdmin every time (Server environment Adobe CF 2018).. right now I have this for my .env 
# Database Information
DB_CONNECTIONSTRING="jdbc:macromedia:<sqlserver://localhost:1433>;databaseName=shadow;SelectMethod=direct;sendStringParametersAsUnicode=false;querytimeout=0;MaxPooledStatements=100;jdbcbehavior=0"
DB_CLASS=macromedia.jdbc.MacromediaDriver
DB_DRIVER=MSSQLServer
DB_HOST=localhost
DB_PORT=1433
DB_DATABASE=customers
DB_SCHEMA=shadow
DB_USER=sa
DB_PASSWORD=MyDBPASSWORD
So what happens is, that a DSN named customers is setup which is what I want however, the database name should be shadow but its set as customers. I have to manually change it to shadow. Is there a setting that I am missing or have misconfigured?
g
I see this in your .env file 
DB_DATABASE=customers
Can you change that to the correct database name? Also, should the 
DB_SCHEMA
be shadow or dbo?
m
Gonna try it and find out.
g
if you are changing env values, you may need to reload the CommandBox shell. Type 
reload
or 
r
from the box command line
m
So for fun commented the DB_Schema line and restarted no noticeable effect. I'm going to change DB_database=shadow and see what we get
So changing that, changes the DSN name but also changes the Database name, so if I can keep that and find a way to set the DSN this would work
g
it sounds like you are using the same env value for the DSN and the database name in cfconfig file
you can create a new env value for DSN name, something like 
DB_DSN=whatever
and then update the cfconfig accordingly
then you can have different values for the database and DSN names
b
if you are changing env values, you may need to reload the CommandBox shell.
This is not correct. CommandBox never caches env vars. There was a bug in the migrations module specifically that used to cache env var information, but Eric fixed that at ITB 🙂
🙌 1
g
I sit corrected. Thanks!
b
@malllory.woods Before we can help you, please tell us more about how your config is being loaded. You mentined .env, but • are you using CFConfig • Do you have a 
.cfconfig.json
file • do you have other CFConfig settings in your server.json • Are you using any 
cfconfig_xxx
env vars • Are you using a 
.env
file? • Do you have 
${}
placeholders in an of your JSON files As you can see there are a great many possibllities here and we're all just making guesses until we understand where exactly your config is and how it's being loaded.
g
I also said 
may
just in case 🙂
m
Currently away from the machine but will chime back in once I am there. It will be a while. Thanks for the replies
Ok here are the answers I am not using CFConfig, I do have a .cfconfig.json file (will include), I don't have any other cfconfig settings in the server.json file, I am not using any env variables, I do have a .env file and I don't have any place holders in the JSON files
{
"cacheDefaultObject":"coldbox",
"debuggingEnabled":true,
"debuggingReportExecutionTimes":false,
"disableInternalCFJavaComponents":false,
"requestTimeoutEnabled":true,
"requestTimeout":"0,0,5,0",
"robustExceptionEnabled":true,
"caches":{
"coldbox":{
"storage":"true",
"type":"RAM",
"custom":{
"timeToIdleSeconds":"1800",
"timeToLiveSeconds":"3600"
},
"class":"lucee.runtime.cache.ram.RamCache",
"readOnly":"false"
}
},
"datasources" : {
"${DB_DATABASE}":{
"bundleName": "${DB_BUNDLENAME}",
"bundleVersion": "${DB_BUNDLEVERSION}",
"class":"${DB_CLASS}",
"connectionLimit":"100",
"connectionTimeout":"1",
"custom":"useUnicode=true&characterEncoding=UTF8&serverTimezone=UTC&useLegacyDatetimeCode=true&autoReconnect=true&useSSL=false",
"database":"${DB_DATABASE}",
"dbdriver":"${DB_DRIVER:MySQL}",
"dsn":"jdbc:mysql://{host}:{port}/{database}",
"host":"${DB_HOST:127.0.0.1}",
"password":"${DB_PASSWORD}",
"port":"${DB_PORT:3306}",
"username":"${DB_USER:root}",
"storage":"false"
}
},
"whitespaceManagement":"white-space-pref"
}
.cfconfig.json file
b
lol
I am not using CFConfig, I do have a .cfconfig.json file
So you aren't... but you are??
I am not using any env variables, I do have a .env file
Again, so you aren't, but you are??
m
Ummmm I didn't think I was.. 😁
b
I don't have any place holders in the JSON files
Then, what are these??
Copy code
"class":"${DB_CLASS}",
m
I thought those .cfconfig fields were just generated automatically... I haven't modified them
b
It would appear from what I can tell, that • you are using CFConfig (via a JSON convention file) • you are using env vars (from an 
.env
file) • You are using en var placeholders in your JSON file So, you the overall fix here is simply to update the JSON/env vars appropriate to have whatever values it is you desire.
I thought those .cfconfig fields were just generated automatically... I haven't modified them
Whether they were created by you or the same aliens who build the pyramids, that doesn't mean you're not using it 🙂
m
Those aliens will get ya..
👽 1
g
when generating a site using the ColdBox template or app wizard, we create those for you by default as it is a good practice to use cfconfig with environment variables
we try to make things easy for you 🙂
❤️ 1
b
And in case it isn't clear (I know there's a fair amount of "magic" here.) • Assuming the 
commandbox-cfconfig
and 
commandbox-dotenv
modules are installed (which they are be default in the newest version of CommandBox) • Any file called 
.cfconfig.json
in the web root will be automatically imported into your server every time you start it. • Any properties in a file called 
.env
, if it exists in your web root or the working directory of the shell where you ran the 
start
command, will automatally be loaded to the CLI as env vars • Any 
${foo}
placeholders in any JSON files will be replaced with the matching env vars, if they exist
And, of course, any manual chances you make to the settings in the CF admin will be overwritten on the next server start (see bullet #2 above)
g
the quick fix for what you are looking for is to create a new env value for DSN name, something like 
DB_DSN=whatever
and then update the cfconfig accordingly. So instead of
Copy code
"datasources" : {
        "${DB_DATABASE}":{
make it
Copy code
"datasources" : {
        "${DB_DSN}":{
and then through the magic of CommandBox and @bdw429s, it will just work!
m
Ahhhh I think I see. Let me try a quick edit/test
b
Or, honesty, you can just hard-code the DNS name.
I've found most people don't need that to be dynamic
g
when you have never used environment variables before, it can be a little tough to think through, but once you start using them, they are awesome 🙂
💯 1
m
let me try that first, its easy
b
Whoever put that sample JSON together may have been a little "high" on dynamic-ness
g
yea, they made the assumption that you would want to name the DSN the same as the database
might be a good update to make to the template to break it out
b
Meh, there's a million ways to do it, and all of them won't match somebody's workflow out there.
Generally speaking, our sample JSON does too much IMO. For example,
Copy code
"bundleName": "${DB_BUNDLENAME}",
            "bundleVersion": "${DB_BUNDLEVERSION}",
            "class":"${DB_CLASS}",
those aren't even necessary in 95% of cases. I would have omitted them entirely from the example
g
that's true, but think of how much more flexible it would be 🤣
b
Plus, who on earth makes those dynamic?
The JSON is a little overcomplicated IMO
g
I do when I use the ColdBox template...
b
Most people are fine just having the password be an env var! (and maybe the host)
g
I admittedly get a little environment variable happy...
m
Is there documentation for all of the variables that go in cfconfig? (this is ortus, there has to be docs)
g
but I have run into too many occasions between local, shared dev, Test, UAT, Demo, and Production where putting it all into .env makes it very easy to configure for different servers, etc
b
Yeah, but do you use • a different datasource name • different database name • different username • different port • different Java class/bundle name/bundle version • different schema • different JDBC URL between dev/qa/prod? I highly doubt it
g
different database name and usernames, yes, ports, sometimes
b
Half of that stuff will be automatically defaulted by CFConfig and there's no need to even have it in the JSON. The rest of it is likely the same across the board except the hostname, password, and maybe username.
g
especially when there are databases for testing vs the regular environment
m
Thanks for the resource I'll yell back if I get stuck
b
Ok, so even if you add database, that's still only 3-4 out of 10 things that actually need an env var!
Open in Slack
PreviousNext
Powered by