<HTML>
<HEAD><TITLE>Films</TITLE></HEAD>
<BODY>
<H2>Film Information</H2>

<CFTRY>
  <!--- Retrieve Films from live database --->
  <CFQUERY NAME="GetFilms" DATASOURCE="#REQUEST.DataSource#">
    SELECT FilmID, MovieTitle
    FROM Films
    ORDER BY Films.MovieTitle
  </CFQUERY>

  <!--- If any database errors occur during above query, --->
  <CFCATCH TYPE="Database">
    
    <!--- Attempt to get information from txt file instead --->
    <CFTRY>
      <!--- Construct connection string so we can query Films.txt file --->
      <CFSET Driver = "{MERANT 3.70 ColdFusion OEM 32-BIT TextFile (*.*)}">
      <CFSET DBDir  = GetDirectoryFromPath(GetCurrentTemplatePath())>
      <CFSET ConStr = "DRIVER=#Driver#;DB=#DBDir#;FLN=1;tT=COMMA;UT=GUESS">
    
      <!--- Retrieve Films from Text file --->
      <CFQUERY NAME="GetFilms" DBTYPE="dynamic" CONNECTSTRING="#ConStr#">
        SELECT FilmID, MovieTitle
        FROM Films.txt
        ORDER BY Films.MovieTitle
      </CFQUERY>
  
      <!--- Let user know that text file is being used --->
      <I><P>NOTE: 
      We are not able to connect to our real-time database at the moment.<BR>
      Instead, we are using information from our most recent archives.<BR>
      Please try again later today for an up to date listing.<BR></I>  

      <!--- If any database occurs *during this second query* --->
      <CFCATCH TYPE="Database">
        <!--- Let user know that the Films data can't be shown right now --->
        <I>Sorry, we are not able to provide you with a list of films.<BR></I>
      </CFCATCH>
    </CFTRY>
  </CFCATCH>
</CFTRY>

<!--- Attempt database operation --->
<CFTRY>
  <!--- Retrieve Ratings from database --->
  <CFQUERY NAME="GetRatings" DATASOURCE="#REQUEST.DataSource#">
    SELECT RatingID, Rating
    FROM FilmsRatings
    ORDER BY Rating
  </CFQUERY>  
    
  <!--- Silently catch any database errors from above query --->
  <CFCATCH TYPE="Database"/>
</CFTRY>
  
<!--- Create self-submitting form --->
<CFFORM ACTION="#CGI.SCRIPT_NAME#" METHOD="Post">

  <!--- If, after all is said and done, we were able to get Film data --->
  <CFIF IsDefined("GetFilms")>
    <!--- Display Film names in a drop-down list --->
    <P>Films:
    <CFSELECT
      QUERY="GetFilms" NAME="FilmID"
      VALUE="FilmID" DISPLAY="MovieTitle"/>
    <INPUT TYPE="Submit" VALUE="Go">
  </CFIF>
      
  <!--- If, after all is said and done, we were able to get Ratings data --->
  <CFIF IsDefined("GetRatings")>
    <!--- Display Ratings in a drop-down list --->
    <P>Ratings:
    <CFSELECT
      QUERY="GetRatings" NAME="RatingID"
      VALUE="RatingID" DISPLAY="Rating"/>
    <INPUT TYPE="Submit" VALUE="Go">
  </CFIF>    

</CFFORM>

</BODY>
</HTML>
