• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Finding the Oracle datasource...

Participant ,
Oct 09, 2014 Oct 09, 2014

Copy link to clipboard

Copied

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.

Views

3.0K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Guide , Oct 09, 2014 Oct 09, 2014

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 dom

...

Votes

Translate

Translate
Guide ,
Oct 09, 2014 Oct 09, 2014

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Oct 09, 2014 Oct 09, 2014

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Oct 09, 2014 Oct 09, 2014

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Oct 10, 2014 Oct 10, 2014

Copy link to clipboard

Copied

Yes, no access to CF Admin.   Even if I did, it would only be on my development server.   I'll have to look into it further.  Thank you for the input.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Oct 15, 2014 Oct 15, 2014

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Oct 20, 2014 Oct 20, 2014

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Oct 20, 2014 Oct 20, 2014

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Oct 20, 2014 Oct 20, 2014

Copy link to clipboard

Copied

I've never worked with Oracle (I'm primarily a SQL Server user), so I'm not sure I can offer any other ideas.  My knowledge is limited mostly to SQL Server.

-Carl V.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Oct 20, 2014 Oct 20, 2014

Copy link to clipboard

Copied

I've been doing Oracle for years, but have always had CF programmers to deal with everything.   First time I've had to deal with it all by myself and it is one headache after another.   Hopefully, someone else will see it and help me.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Oct 20, 2014 Oct 20, 2014

Copy link to clipboard

Copied

I did not implement it, but in my company we use text files named after the apps under a folder named server-config.  This files contain configuration that is different accross environments, i.e. different datasources etc.  That means we have a file per environment, i.e. one for QAT one for Production and they get deployed to the specific target environment exactly on the same location.  This save us from having if QA do this if PRD do this if UAT do something else kind of code.  Although if you do not feel confortable taking this project that may be a good temporary solution.  We have an object that knows where to load these files and use it to configure the connections correctly.  We actually do use ColdFusion Admin datasources, but technically speaking you could do without that.

Per your description:

1. I would first request access to CFAdmin, you cnnot do CF development without that...
2. I will make troubleshoot using the guidelines from this site: ORA-00942: table or view does not exist

Best of luck,

migueL

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Oct 21, 2014 Oct 21, 2014

Copy link to clipboard

Copied

So this text files are just like a post it note to remind you of what is what, or does the application use them as config?

I doubt I can do CF Admin, but I will request it.  The only thing I have control of, is development.   The error is happening on Test and that is supported by another group and I only have access to FTP new code.  At first I thought I had modified the Application.cfc file incorrectly, but the error I am receiving now suggests that is not the case. 

Thank you

L

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Oct 24, 2014 Oct 24, 2014

Copy link to clipboard

Copied

No, we use the files to configure the environments.  This is very helpful because during development we can for instance switch any resource such as the databases (i.e. DEV to QAT) simply by changing the server config file and reloading the app.

Do you use a source code management, SCM system?  I ask becasue you suspect changes you did earlier may have created the problem.  Have you tried undoing those changes to see what happens?  That could be very easy if you use an SCM not so easy if you don't.  That is specially true if you do not always follow deployments from DEV > TEST > PRODUCTION testing at every step of the way.  I am not sure I understand correctly but it sounds like you are saying you didn't did not ensure the changes worked in TEST also?  If that's the case, you may not have a technical problem just a process flow problem.  Those are always expensive in the amount of time it takes to fix them.  Basically, you will have to revert you code back to the way it was the last time it worked, redo the last change in a manner that will work in all environments, and then finally do the change you need to do now.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Oct 24, 2014 Oct 24, 2014

Copy link to clipboard

Copied

No, we do not use SCM or anything for that matter.  I was thrown into this pool and told to swim.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Oct 24, 2014 Oct 24, 2014

Copy link to clipboard

Copied

Thank you!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 21, 2014 Oct 21, 2014

Copy link to clipboard

Copied

LouieWarren wrote:

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.

There is something in what he or she is saying. Perhaps the SQL code needs to refer to the fully qualified name of a table. What is the SQL code?

In any case, I would advise you to open a new thread in the database section of this forum. That might increase exposure of your issue to DB enthusiasts.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Oct 21, 2014 Oct 21, 2014

Copy link to clipboard

Copied

None of the SQL is fully qualified in the application.   When adding the datasource/schema/user to the prefix the table name, I still get the same error.  The SQL is a query to determine who is identified as the webmaster, prior to displaying the login.  Once the user logs in, if he is a webmaster (me) then it allows him access to specific functions. 

I seem to think my changes to Application.cfc could have caused some issue.   When fixing the getBuiltInScopes issue, i had the SysAdmin copy over the Application.cfc from production.  That one and the one I had were different.  The one on production had getBuiltInScopes call and the one I had didn't.   Prior to doing any of this, I never had to worry about datasource, it was always correct where ever the application was brought up.   I'm tracing down another issue currently, but will definitely post something to the DB portion.  Thanx to everyone for the help.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 21, 2014 Oct 21, 2014

Copy link to clipboard

Copied

LouieWarren wrote:

I seem to think my changes to Application.cfc could have caused some issue.  

Java.sql.SQLSyntaxErrorException says otherwise.Could you show us the query code?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Oct 21, 2014 Oct 21, 2014

Copy link to clipboard

Copied

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>

<cfif isdefined("URL.openFile")>

    <cfquery name="getFile" datasource="xxxxtest">

        SELECT filename, filename2, filecontent, filesize

        FROM Help_FooterInfo

        WHERE Help_id=5 and Section='Registration'

    </cfquery>

   

    <cfset sproot=#getDirectoryFromPath(getTemplatePath())#>

    <cfset newDest = #sproot#&"temp\">

       

    <cfoutput query="getFile">

        <cfheader name="Content-Disposition" value="attachment; filename=#getfile.FileName2#">

        <cfcontent type="application/msword" variable="#getfile.filecontent#">

    </cfoutput>

</cfif>

<head>

    <title>XXX Login Page</title>

</head>

<cfquery name="getWebMaster" datasource="xxxxtest">

     select * from users where secureuserid in (select secureuserid from hd_role where webmaster='Yes')

    and userstatus='Active' and UserLevel='XXX/XXIT'

</cfquery>

<cfquery name="getNoticeConsent" datasource="xxxxtest">

     select * from help_footerinfo where help_id=3

</cfquery>

<cfquery name="getRestriction" datasource="xxxxtest">

     select * from help_footerinfo where help_id=4

</cfquery>

<cfquery name="getRegistration" datasource="xxxxtest">

     select * from help_footerinfo where help_id=5

</cfquery>

<body background="Images/loginBg.gif" TOPMARGIN="30px" onLoad="document.forms[0].j_username.focus();">

<!-- This checks if the browser doesn't have the Javascript option

checked off-->

<noscript>

This program requires Javascript.  You are in need of turning the option for

accepting Javascript on Netscape or install a higher version of the browser.

You require versions 4 or above of Internet Explorer and Netscape Navigator. 

</noscript>

<CENTER>

<TABLE  BORDER=0 width=620px>

     <TR>

      <TD BGCOLOR="Silver">

           <TABLE BGCOLOR="#dbdbdb" BORDER=1 cellpadding="2" width=100%>

             <TR>

                 <TD ALIGN="center" BGCOLOR="#000080">

                    <FONT FACE="arial, helvetica, sans-serif" COLOR="White"><B>XXXX Management Tool</B></FONT>

                </TD>

            </TR>

            <TR>

                <TD>

                    <FORM name="aform" method="post" action="secure/xxxxmain.cfm">           

                    <TABLE border=0 cellspacing="3" cellPadding="1" width=100% >

                        <TR>

                            <CFIF IsDefined("url.badLogin")>

                                <TD colspan=3 height=3  style="color:red;"><B>Your login could not be validated. Please try again. <P align=left>You must have a valid user name and password. Please contact our Tool Administrator at <cfoutput query="getWebMaster">#phone#</cfoutput> to acquire your valid user name and password.</B></TD>

                            <cfelseif IsDefined("url.retired_id")>

                                <TD colspan=3 height=3  style="color:red;"><B>Your user account is inactivate. <P align=left>Please contact our Tool Administrator at <cfoutput query="getWebMaster">#phone#</cfoutput> to re-activate your account.</B></TD>

                            <cfelseif IsDefined("url.sess_expired")>

                                   <TD colspan=3 height=3  style="color:red;"><B>Your user session has expired. <P align=left>Please login to BMT to establish another session. </B></TD>

                            <cfelse>

                                <TD colspan=3 height=3 style="color:#000080;"></TD>   

                            </CFIF>

                        </TR>

                        <TR>

                            <TD width=34% align=right><FONT SIZE="-1" FACE="Arial, helvetica, sans-serif" COLOR="#000080"><B>User ID</B></FONT></TD>

                            <TD width=3%></td>

                            <TD width=64% align=left><INPUT name="j_username" type="text" style="width:150" size="21" MAXLENGTH="20"></TD>

                        </TR>

                        <TR>

                            <TD width=34% align=right><FONT SIZE="-1" FACE="Arial, helvetica, sans-serif" COLOR="#000080"><B>Password</B></FONT></TD>

                            <TD width=3%></td>

                            <TD width=64% align=left><INPUT type="password"  name="j_password" size="21" MAXLENGTH="20" style="width:150"></TD>

                        </TR>

                        <TR>

                            <TD colSpan=3 align=center>

                                <INPUT type=submit style="FONT: 8pt Arial; FONT-WEIGHT: bold; COLOR: #000080; width: 60px" value="Login">   

                            </TD>

                        </TR>

                        <TR>

                            <TD colSpan=3><hr width=95%></TD>

                        </TR>

                        <TR>

                            <TD colSpan=3 align="center"><FONT FACE="arial, helvetica, sans-serif" COLOR="#000080"><B><cfoutput>#getNoticeConsent.title#</cfoutput></B></FONT></TD>

                        </TR>

                        <TR>

                            <TD colSpan=3 align="justify"><FONT FACE="arial, helvetica, sans-serif" COLOR="#000080" size="2">      <cfoutput>#getNoticeConsent.Content#</cfoutput></FONT></TD>

                        </TR>

                        <TR>

                            <TD colSpan=3 align="center"><FONT FACE="arial, helvetica, sans-serif" COLOR="#000080"><B><cfoutput>#getRestriction.title#</cfoutput></B></FONT></TD>

                        </TR>

                        <TR>

                            <TD colSpan=3 abbr="justify"><FONT FACE="arial, helvetica, sans-serif" COLOR="#000080" size="2"><b>      <cfoutput>#getRestriction.Content#</cfoutput></b></FONT></TD>

                        </TR>

                    </TABLE>

                    </FORM>

                </td>

               </tr>

          </table>

        </td>

    </tr>

</table>

<p><span style='font-size:"13";font-family:"arial"; font-weight:normal;mso-bidi-font-weight:bold;font-style:normal;mso-bidi-font-style:italic'><cfoutput>#getRegistration.Content#</cfoutput><br><a href="<cfif #getRegistration.filename2# neq "">index.cfm?openfile=Yes</cfif>" target="_blank"><u><cfoutput>#getRegistration.FileName#</cfoutput></u></a><o:p></o:p></span></p>

</center>

</body>

</html>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 21, 2014 Oct 21, 2014

Copy link to clipboard

Copied

There are 5 queries here. To which one does the error message apply?

In any case, note that 'help_footerinfo' and 'Help_FooterInfo' may be seen as different in a case-sensitive environment.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Oct 21, 2014 Oct 21, 2014

Copy link to clipboard

Copied

The 1st one after the </head>

However, if I comment that one out, it will be the next one and so on.  I can eventually get past it, but because the commented out sections didn't return values, it complains about that.   There are numerous <cfquery name="queryname" datasource="xxxxtest"> calls throughout the code.  If my Google research is correct, the data source could be set once in Application.cfc

<cfcomponent>
<cfset this.datasource="xxxxtest">
</cfcomponent>


then, the datasource can be eliminated from cfquery if I understand correctly.


Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 21, 2014 Oct 21, 2014

Copy link to clipboard

Copied

LouieWarren wrote:

The 1st one after the </head>

However, if I comment that one out, it will be the next one and so on.

That would therefore mean that there are no tables named users or help_footerinfo in the datasource xxxxtest.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Oct 21, 2014 Oct 21, 2014

Copy link to clipboard

Copied

But when you log into xxxxtest as xxxxtest and run the query in SQL Plus, you get a result.   I don't know for a fact, because I am not allowed to do anything to Production, and on Test I am only allowed to FTP code changes to Test.  I am basing this all on confirmation from the gov't DBA, backed up with an e-mail attachment (Excel) with the results.

Is there a way to display what it thinks the data source is when the application 1st comes up?   To me, it seems as if it wasn't assigned, even though, it is.   Back when I corrected the getBuiltInScopes problem, I had the SysAdmin get me a copy of the Application.cfc file that was on Production.  I then discovered that the call to getBuiltInScopes was in the Production version but not in the version on my development or test machines.   I had to modify the data source in that file to make it work; explicitly defining it for the environment where it was being used.  The correct version is on Test and the xxxxtest data source is identified.  Could it still be "looking at" the older version?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Oct 21, 2014 Oct 21, 2014

Copy link to clipboard

Copied

LouieWarren wrote:

Is there a way to display what it thinks the data source is when the application 1st comes up? 

For that, you will need access to the ColdFusion Administrator. Do you have such access?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Oct 21, 2014 Oct 21, 2014

Copy link to clipboard

Copied

No, I do not.  It's maintained by the gov't CF Admin/SysAdmin

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation