<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">
    <!--- Unless this is SQL Error S0001, un-catch the exception --->
    <CFIF CFCATCH.SQLState NEQ "S1000">
      <CFRETHROW>

    <!--- If it is SQL Error, S0001, attempt to get data from txt file --->
    <CFELSE>
      <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 error occurs *during this second query* --->
        <CFCATCH TYPE="Database">
          <!--- Let user know that the Films data can't be shown right now --->
          <I>Sorry, we can't display a list of current films right now.<BR></I>
        </CFCATCH>
      </CFTRY>
    </CFIF>
  </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>
