Need help in configuring datasource for SQL Editor window

Larry, you need to have configured a few things to get that sort of feature working. (And you’re not at all alone in finding that things don’t work “out of the box”. There are a lot of moving parts, but it’s because CFB is doing a lot for you, with its many features.)

Because there are a lot of moving parts, this is a much longer response than you (and many) will have expected. If it was easy to answer in a tweet-length reply, I don’t think people would be having as much trouble!

As for the query/datasource-related stuff, the most important is that first the CF instance you’re trying to use (from with CFB) needs to be configured to support RDS.

Whether it is or not is a question asked during installation of CF. If the person installing CF said “no, don’t enable it”, then that’s certainly the first reason that you can’t get CFB to talk to it to get the database info. Enabling that after installation is easy in CF10: there’s a checkbox for it now in the CF admin. In earlier releases, you need to tweak the web.xml file. Googling about enabling RDS should find what you need.

Then, you need to properly configure CFB so that the RDS password is provided. That is requested while you’re adding a server, and can be edited by right-clicking on a server (in Window>Show View>Servers) and choosing “edit server” (as well as another way, discussed below).

Of course, you need to know the RDS password for the server you’re trying to connect to, which is configured in the CF Admin RDS Password page. See the online help there or the CF docs “Admin and Config” manual for more. Here too is another potential gotcha: if the CF Admin may have enabled the “multiuser” feature in the CF Admin, so that there are different user names to be used, then you need to know the right username AND password.

Once all this is configured, then open the RDS Data View in CFB (Window>Show View). See if you can see the datasources for your server there. If not, then again, until that’s resolved, you won’t be able to use this info to build SQL in CFB.

There are still more potential gotchas with that (having the RDS Dataview work). You may provide the wrong URL to connect to the server, or if it requires a port (perhaps if the server is configured to use port 8500 or 8300 or something like that to connect to CF), then it could be that you’re trying to connect to that server from another machine, and a firewall may be blocking your access (from the CFB machine to the CF server machine.)

To test and configure things, note that there is an RDS configuration page with a nice TEST feature, and more. You can access that in either of two ways: use Window>Preferences>Adobe>RDS Configuration , or when you have the RDS Dataview open, right-click in that interface and choose “RDS Configuration”.

I could keep going, but I don’t want to speculate on ALL that could be causing things not to work for you. I hope some of the above will help. I’ll also point out that there is documentation for CFBulider, and it does address getting this sort of configuration done right. See Help>ColdFusion Builder Help (within CFB) to be taken to the page for the docs. See especially:

http://help.adobe.com/en_US/ColdFusionBuilder/2.0/Using//WS0ef8c004658c1089-38317734121cdfd5fd3-7fff.html <http://help.adobe.com/en_US/ColdFusionBuilder/2.0/Using/WS0ef8c004658c1089-38317734121cdfd5fd3-7fff.html>

http://help.adobe.com/en_US/ColdFusionBuilder/2.0/Using/WS0ef8c004658c1089-2cf13501121af8ece2c-7ffb.html

http://help.adobe.com/en_US/ColdFusionBuilder/2.0/Using/WS0ef8c004658c1089-616145612392f9b889-8000.html

http://help.adobe.com/en_US/ColdFusionBuilder/2.0/Using/WS0ef8c004658c1089-9fb652212374f1cb10-8000.html

There are also some blog entries that discuss facets of setup that may interest you (see also the comments and answers to those from others at the bottom of each):

http://blogs.adobe.com/cfbuilder/2009/07/remote_server_setup_is_it_real.html

http://blogs.adobe.com/cfbuilder/2011/03/where-coldfusion-builder-finds-my-cfcs.html

Also, once you DO get the RDS Dataview working, you may find (as many have) that if you try to launch the RDS Query Viewer (by right-clicking on a table in its display), you may get an error that it “does not exist”. Here is one of many blog entries for solving that problem:

http://www.cfchimp.com/wordpress/2012/01/rds-query-viewer-not-working/

Finally, once all is working, you mentioned not getting intellisense for writing the query. You refer to using the SQL Editor window (which for those reading this and unfamiliar, you get by right-clicking on your text in the editor and choosing SQL Editor.) If you can get the datasources listed (by proper configuration of RDS for the server you’re selecting), then all should work find.

But even with that working, I find that the intellisense for popping up tables, columns, etc within just the text editor window (while building a CFQUERY) won’t open until you add the closing CFQUERY tag. This is in CFBuilder 2.01, so that’s a shame that it’s still an issue. But I just want to share it for you or any other readers who may hit that as a seeming problem.

Then of course, to use the SQL editor, the page you’re opening needs to be opened as a file within a project, which has been properly connected (in the project properties) to a server, which has itself been properly connected to RDS (as tested by the above), and so on.

So again, lots of moving parts. Hope that helps. Besides the docs, and the blog entries above, I’ll add a final resource: I offer a service where I will help anyone with a single problem (getting CFBuilder configured) for a rather low fixed price (for up to 2 hours of assistance, done over the web/phone). For more, see www.carehart.org/consulting. But as I hope my note above shows, I’m not here trolling for business. I offer the info you should need to solve things on your own, but since again there are a lot of moving parts and people are often stumped, I offer this additional service if you may benefit.

Let us know if the info above gets you going.

/charlie

PS About that blog entry I pointed to, on fixing when the query viewer “does not exist”, I added a comment there just now, but it’s being moderated, so in case that may take a while to show up, here’s the extra info I added (as it may be relevant to someone reading this thread):

Nice tips. Just a couple more that I found I needed to do.

First, on Windows 7 or 2008, one will need to open the command prompt with "run as administrator" to be able to run that fsutil command. (Use Start>All Programs>Accessories, then right-click on the "command prompt" menu option, choosing "run as administrator".)

Second, even on doing that, when I copy/pasted the command above (for the FSUTIL), it kept failing, simply showing me the "usage" syntax for the command. I finally figured out that it seems that the double-quotes in the web page here are not what DOS wants on the command line. I just typed over the quotes, and it worked.

But even on doing that, and refreshing the Dataview list, it would still give the error, though clearly the file was there.

So I used the navigator (the project explorer showing directory and files in your project) within CFB to open that .rdsTempFiles directory, and opened the file ("RDS Query Viewer") that was now there, then I entered a single space and saved the file. Then instantly the query viewer tab (which had been opened with that error) now opened and was working. (Or one may need to right-click on a table and try to open it again.)

(Finally, speaking of editing the "right" file in the "right directory", here's another tip: right-click on your project. That will offer a "Command Shell" menu option near the bottom, and that will open the command prompt to the exact right directory which is the root for the project. But again, beware: on Windows 7/2008, you need to take note of that, and then open the command prompt as admin as described above, which CFB does not do.)

Hope that helps someone.