Invalid object name from SQL when using CommandBox + CFConfig?

Communities CommandBox CLI
1

Hi all,

I’m hoping somebody has run into this before…

I’m on CommandBox 3.8.0+00766 w/ CFconfig and Lucee 5. We’re using CFConfig to connect to a MS SQL 2014 database on another server (the CommandBox stuff is my laptop, SQL database is on a co-lo). I’ve got the datasource variables (server IP, database name, username, password) stored as environment variables. This is all running on Windows 7. That part all seems to be fine. Here’s the weird part…

About two thirds of the time when I do “box start” the site will start up, but I will get this error from Lucee:

Invalid object name ‘tParts’.

…so it can’t find the tParts table, which wouldn’t be weird except that table -is- there and has records in it. And it gets weirder from there. Sometimes it can find that table fine, and finishes that query. But Lucee will move on to another query in the request and I’ll get something like:

Invalid object name ‘tOrderItems’.

…which is also a valid table in the database.

Sometimes it fails when trying to run a query that usese that tParts table. Othertimes it does that okay but fails on a different query looking for a different database table (everything uses the same datasource via CFConfig, it’s all in the same database). It’s almost like the JDBC driver needs a few minutes to finish “finding” all the tables in the database or something.

The reason I think this might be CommandBox related is, I’ve got the same codebase connecting to the same database on a co-lo “QA” web server, and that works fine. The only difference (I think) between my Dev laptop and the QA box is I’m using CommandBox / CFConfig and the QA server isn’t.

Any ideas?

Thanks,
Nolan

2

Usually when I see errors were a table can’t be found, it’s because the datasource is connecting to the master database instead of the one you want it to. Can you confirm what database the datasource connections are getting? I wonder if only some of the connections in your connection pool are messed up.

You mentioned CFConfig. Can you do a test where you manually create the datasource and see if it makes a difference. If so, maybe you can directly send me the server xml files that were created with cfconfig and by hand so I can see if something is getting missed in the configuration.

Thanks!

~Brad

ColdBox/CommandBox Developer Advocate
Ortus Solutions, Corp

E-mail: brad@coldbox.org
ColdBox Platform: http://www.coldbox.org
Blog: http://www.codersrevolution.com

3

Hi Brad,

I just discovered that if I go into the datasource in Lucee Admin and turn on “Validate the connection before use (only works with JDBC 4.0 Drivers)” it seems to fix the issue. Not sure if that makes it more or less a CFConfig than I thought earlier, but I’ll leave that up to you. :slight_smile:

Best,

Nolan