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".
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.
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.
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.
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.
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?
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.
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.
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.
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,
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.
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.
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.
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?
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<cfquery name="getFile" datasource="xxxxtest">
SELECT filename, filename2, filecontent, filesize
WHERE Help_id=5 and Section='Registration'
<cfset newDest = #sproot#&"temp\">
<cfheader name="Content-Disposition" value="attachment; filename=#getfile.FileName2#">
<cfcontent type="application/msword" variable="#getfile.filecontent#">
<title>XXX Login Page</title>
<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 name="getNoticeConsent" datasource="xxxxtest">
select * from help_footerinfo where help_id=3
<cfquery name="getRestriction" datasource="xxxxtest">
select * from help_footerinfo where help_id=4
<cfquery name="getRegistration" datasource="xxxxtest">
select * from help_footerinfo where help_id=5
<body background="Images/loginBg.gif" TOPMARGIN="30px" onLoad="document.forms.j_username.focus();">
You require versions 4 or above of Internet Explorer and Netscape Navigator.
<TABLE BORDER=0 width=620px>
<TABLE BGCOLOR="#dbdbdb" BORDER=1 cellpadding="2" width=100%>
<TD ALIGN="center" BGCOLOR="#000080">
<FONT FACE="arial, helvetica, sans-serif" COLOR="White"><B>XXXX Management Tool</B></FONT>
<FORM name="aform" method="post" action="secure/xxxxmain.cfm">
<TABLE border=0 cellspacing="3" cellPadding="1" width=100% >
<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>
<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>
<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>
<TD colspan=3 height=3 style="color:#000080;"></TD>
<TD width=34% align=right><FONT SIZE="-1" FACE="Arial, helvetica, sans-serif" COLOR="#000080"><B>User ID</B></FONT></TD>
<TD width=64% align=left><INPUT name="j_username" type="text" style="width:150" size="21" MAXLENGTH="20"></TD>
<TD width=34% align=right><FONT SIZE="-1" FACE="Arial, helvetica, sans-serif" COLOR="#000080"><B>Password</B></FONT></TD>
<TD width=64% align=left><INPUT type="password" name="j_password" size="21" MAXLENGTH="20" style="width:150"></TD>
<TD colSpan=3 align=center>
<INPUT type=submit style="FONT: 8pt Arial; FONT-WEIGHT: bold; COLOR: #000080; width: 60px" value="Login">
<TD colSpan=3><hr width=95%></TD>
<TD colSpan=3 align="center"><FONT FACE="arial, helvetica, sans-serif" COLOR="#000080"><B><cfoutput>#getNoticeConsent.title#</cfoutput></B></FONT></TD>
<TD colSpan=3 align="justify"><FONT FACE="arial, helvetica, sans-serif" COLOR="#000080" size="2"> <cfoutput>#getNoticeConsent.Content#</cfoutput></FONT></TD>
<TD colSpan=3 align="center"><FONT FACE="arial, helvetica, sans-serif" COLOR="#000080"><B><cfoutput>#getRestriction.title#</cfoutput></B></FONT></TD>
<TD colSpan=3 abbr="justify"><FONT FACE="arial, helvetica, sans-serif" COLOR="#000080" size="2"><b> <cfoutput>#getRestriction.Content#</cfoutput></b></FONT></TD>
<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></sp an></p>
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.
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
then, the datasource can be eliminated from cfquery if I understand correctly.
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.
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?
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?
No, I do not. It's maintained by the gov't CF Admin/SysAdmin
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.
This reminds me of something we stumbled upon during your getBuiltInScopes saga. We discovered quite late in the thread that the datasource of the test environment of your application, abcdTest, and that of the production environment, abcdProd, were unmatched. That might well be the case here, too.
The code was unmatched, but the databases are identical, except their names. I don't think this issue is the same, but I'll check. I'll also check with the SysAdmin and see what CF Admin has for the datasource. It could be as simple as I have the name spelled wrong... although, I doubt it. Here's the trace and it leads me to believe it doesn't know the source, even though it's spelled out in the code.
java.sql.SQLSyntaxErrorException: [Macromedia][Oracle JDBC Driver][Oracle]ORA-00942: table or view does not exist
at macromedia.jdbc.oraclebase.ddca.b(Unknown Source)
at macromedia.jdbc.oraclebase.ddca.a(Unknown Source)
at macromedia.jdbc.oraclebase.ddb9.b(Unknown Source)
at macromedia.jdbc.oraclebase.ddb9.a(Unknown Source)
at macromedia.jdbc.oracle.ddm.q(Unknown Source)
at macromedia.jdbc.oraclebase.ddem.v(Unknown Source)
at macromedia.jdbc.oraclebase.ddem.r(Unknown Source)
at macromedia.jdbc.oraclebase.ddem.execute(Unknown Source)
at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:2 8)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.j ava:305)
at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42 )
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.j ava:243)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.jav a:539)
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.
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.
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.
No, we do not use SCM or anything for that matter. I was thrown into this pool and told to swim.
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.
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.
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.
Does any one have any ideas? I've gone into CF10 Admin and verified the connection to the 10.2 database is correct (Data & Services > Data Sources > Connected Data Sources > Verify All Connections or Verify my specific connection) and the status says "OK". What other things should I try? Thank you in advance.
PLEASE STOP ALL THE SPAM!!! I keep unsuscribing but emails keep coming in.
I don't see where this is SPAM. Contact forum support to find out why you are still receiving e-mails.