A

Anonymous

Answered

Finding the Oracle datasource...

Forum|Forum|11 years ago
October 9, 2014
7 replies
4797 views

I've inherited a ColdFusion 10 application with an Oracle 11g backend; Windows server. I am mostly a DBA and not a ColdFusion expert. In the code for the application, they have the datasource hard coded. This means when I move it to Test I must change the datasource, upload the modules, and test. After a successful test, I have to change the datasource, yet again, upload and have it promoted to production. I seem to remember in the old application I used to support, the code was generic and the datasource depended on what server you were on; if on development, test, or production you didn't have to worry. I don't remember how this was done. What would be the best way to eliminate this hard coding and have it more automated? I know I'm probably missing something, but you all have been very helpful to me in the past few weeks, so hopefully, it's not a completely stupid question. Thank you.

This topic has been closed for replies.

Correct answer Carl Von Stetten

I do not have CF Admin

Can you clarify that? Do you mean you don't have access to CF Admin?

Without access to CF Admin, I'm not sure you can do anything differently than you are now., unless you write in some logic to examine the server's host name or the site's domain name and set the datasource accordingly.

If you can get access to CF Admin, create two datasources: one for production and one for testing. Then in application.cfc you can write some logic to examine the server's host name or domain name and point "this.datasource" to which ever datasource is appropriate.

-Carl V.

J

joseChemist

New Participant

PLEASE STOP ALL THE SPAM!!! I keep unsuscribing but emails keep coming in.

A

Anonymous

I don't see where this is SPAM. Contact forum support to find out why you are still receiving e-mails.

A

Anonymous

This problem has just popped up on my Development server, but I think I have caused it. During all this mess, I was instructed to update my CF to 10, and Oracle to 11g. Due to other issues, I got CF updated to 10 and can get into CF Admin on this server. The Oracle 11g, isn't complete so I have disabled all the services to the instance and still have my old 10.2 instance. I set up the Data source in CF Admin to point to the 10.2 instance, but when I attempt to bring up my application, I get the error that the datasource is not found, even though I've set it up. CF Admin tells me everything connects fine and the status is ok... much like the gov't SysAdmin tells me my Test database/datasource is ok, but the Test application keeps giving me the datasource is not found message. Just thought the two issues could be related.

M

Miguel_U

New Participant

LouieWarren when you setup your datasource on CFAdmin did you use the right version and type? Also have you tried creating small templates, say one to basically just use the datasource in a query and then dump it. Sometimes trying to fix an app could be complicated because there may be more than one issue so breaking up the problems into small test templates may help you narrow down your issues. Tell us what you find.

A

Anonymous

I set it up on the same server with the same info (DSN, SID, user credentials) that was used on the 8 version. I have yet to upgrade the 10.2 Oracle DB to 11g. I run the Verify in CF Admin and it says things are ok. What can I do to find out what I did wrong? Right now, all I am trying to do is bring up the index.cfm page. I believe if I can solve this problem, I can also solve the original problem I was having getting into my Test application.

A

Anonymous

The gov't SysAdmin just verified the connection is there and OK with a screen shot. Completely at a lost as to where to go next. I will reference this thread in the database forum. Thank you.

BTW... when it was suggested that I post in the database forum... just where is it? Did you mean on the Oracle site? Thanx.

BKBK

Community Expert

LouieWarren wrote:

BTW... when it was suggested that I post in the database forum... just where is it?

Database Access

A

Anonymous

Update (I think it's relevant)...

I suspected that my updates to Application.cfc, to correct the getBuiltInScopes issue previously. The Application.cfc file exists in a subdirectory, which after you login, called secure. I have attempted to do the change to the cfquery I mentioned above (set this.datasource and elminate it from the cfquery). I did this and uploaded the Application.cfc with the set datasource in the cfcomponent area. I then attempted to remove the datasource attribute and couldn't. I got the error message that the datasource attribute was missing. This, to me, means it's not finding the changes to Application.cfc. I copied the Application.cfc to the root, and it hung with session expired. When I take off the datasource attribute on the cfquery calls, it still complains... could I put a modified Application.cfc in root with just the info I need to get past this error? The gov't SysAdmin assures me the datasource is correct in CF Admin. Still searching for answers.

Carl Von Stetten

Brainiac

That pretty much rules everything out. Even writing a CFM page to open the Server API and pull information about datasources would require you to have a username/password setup in the CF Administrator.

-Carl V.

A

Anonymous

I have solved, I think, the datasource problem. I changed the datasource to the correct one for test. This time, the application knows it's there, but says the tables in the 1st query don't exist. The error message dump references the directories from Production. To fix another problem, I changed the Application.cfc and had it moved directly to Production. The Application.cfc currently on Test is the one from Production (hence the directory issue). I have loaded up the correct one, but I get the error when attempting to start the application (index.cfm). Is there a way to get the new Application.cfc to be recognized? I'm still VERY new at ColdFusion. Any further help would be greatly appreciated.

[Macromedia][Oracle JDBC Driver][Oracle]ORA-00942: table or view does not exist The source is set up and the tables exist when using SQL*Plus. Seriously confused.

Carl Von Stetten

Brainiac

Louie,

Sorry for not getting back to you sooner. I was attending conferences all last week and wasn't keeping up on the forums.

Can you clarify the difference between your "Test" environment and your "Production" environment? Are they separate sets of ColdFusion code files? Separate web sites? Separate servers?

It sounds like you copied the updated Application.cfc file (where you corrected the datasource assignment) to the "Test" environment, and it threw errors. You might want to restart the ColdFusion service for the "Test" environment (if possible). Also, what credentials are you using to connect your "Test" ColdFusion server to your "Test" Oracle database? Are you using the exact same credentials to view the tables in SQL*Plus?

-Carl V.

A

Anonymous

They are two completely separate ColdFusion/Oracle servers for each. It appears I've fixed the datasource problem, but now it doesn't know tables that exist. The gov't DBA supporting things, told me that my queries weren't fully qualified. I seem to remember that the application starts up logged on as the user/schema being accessed which means that is not necessary. I verified this in SQL Plus by logging in as the user/schema and was able to successfully find the tables it says it can't. I had the gov't DBA verify the same thing. However, when I bring up the application, I get the following error.

java.sql.SQLSyntaxErrorException: [Macromedia][Oracle JDBC Driver][Oracle]ORA-00942: table or view does not exist

To me, that means the normal connection I use isn't there. The gov't SysAdmiin, after restarting CF.for the getBuiltInScopes error I was getting, has refused to do so any more, due to the fact that I am one of over 30 customers and he can't inconvenience them on something that isn't a sure thing; which it wasn't earlier. Being on the outside looking in and depending on other people to do the job I used to do all by myself is very frustrating. I thank you for helping me out.

Carl Von Stetten

Brainiac

If you only have one application, or this is the only application that uses that datasource, you can go into the ColdFusion Administrator and change where that datasource points. Point it to Test, do your testing, then point it back to production. No code changes required.

I like to set the name of the datasource into an application scope variable, then in all of my <cfquery> tags I set the datasource attribute to "#application.datasource#". With ColdFusion 10+, this is no longer necessary. In your application.cfc, set "this.datasource" to the name of the datasource configured in ColdFusion Administrator, then you can simply omit the datasource attribute from all queries and ColdFusion will automatically use the one in "this.datasource".

-Carl V.

A

Anonymous

Yes. Each database is only used by this application. I do not have CF Admin, and on Test & Production, I must depend on the SysAdmins/DBAs to do anything to my stuff. Is there anyway to do the opposite of that? I know I could have a row in the database and do a query when it's loaded, but wanted to avoid that if I could. <cfdbinfo> seemed as if it would work, but I guess I don't get it.

Carl Von StettenCorrect answer

Brainiac

I do not have CF Admin

Can you clarify that? Do you mean you don't have access to CF Admin?

Without access to CF Admin, I'm not sure you can do anything differently than you are now., unless you write in some logic to examine the server's host name or the site's domain name and set the datasource accordingly.

If you can get access to CF Admin, create two datasources: one for production and one for testing. Then in application.cfc you can write some logic to examine the server's host name or domain name and point "this.datasource" to which ever datasource is appropriate.

-Carl V.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded