/*
 * @author  Shrinivas Bhat
 * @version 1.0
 *
 * Development Environment        :  Oracle JDeveloper 10g
 * Name of the Application        :  Datalink.java
 * Creation/Modification History  :
 *
 *    Shrinivas Bhat     09-Feb-2004      Created.
 */
package oracle.otnsamples.jdbc;

// Java io imports
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;

// Java net imports
import java.net.HttpURLConnection;
import java.net.URL;

// SQL imports
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.DriverManager;


 /**
  * This class demonstrates how to insert a URL object into the database and 
  * then retrieve the contents using the getURL method of the ResultSet class.
  *
  * Create a database table as follows, before you run this file
  * 
  *     CREATE TABLE sample_repository (
  *       sample_name VARCHAR2(50), 
  *       url         VARCHAR2(200)
  *     );
  */     
public class Datalink  {

  public static void main(String[] args)  {
    try  {
            
      DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
      
      // Change the following URL to point to your database
      Connection conn = DriverManager.getConnection(
                       "jdbc:oracle:thin:@localhost.com:1521:ora10g",
                               "scott",
                               "tiger"
                               );
      
      PreparedStatement pstmt = conn.prepareStatement(
                "INSERT INTO sample_repository(sample_name,url) VALUES (?,?) "
                                                  );
      String sampleUrl = 
                "http://otn.oracle.com/sample_code/tech/java/sqlj_jdbc/"+
                "files/jdbc30/savepoint/Readme.html";
       
      // Set the String value for the first bind variable
      pstmt.setString(1,"Save Point Sample");
      // Set the URL object for the second bind variable.
      pstmt.setURL(2,new URL(sampleUrl));
      // Execute the query
      pstmt.execute();

      Statement stmt = conn.createStatement();      
      ResultSet rs = stmt.executeQuery(
                            " SELECT sample_name, url FROM sample_repository "
                           );
      if ( rs.next() )  {
        
            String sampleName = null;
        java.net.URL url = null;
        
        sampleName = rs.getString(1);
        // Retrieve the value as a URL object.
        url = rs.getURL(2);    
            
        if ( url != null ) {
          // Retrieve the contents from the URL. 
          HttpURLConnection urlConnection =(HttpURLConnection)url.openConnection();
          BufferedReader bReader = new BufferedReader(
                                       new InputStreamReader(
                                           urlConnection.getInputStream()));
          String pageContent = null;
          
          while ( ( pageContent=bReader.readLine() )!=null )  {
            // Print the URL contents
            System.out.println(pageContent);
          }
        }  else { 
          System.out.println("URL is null");
        } 
      }
    }  catch (SQLException sqlEx)  { 
       sqlEx.printStackTrace();
    }  catch(IOException ioEx)  {
       ioEx.printStackTrace(); 
    }  catch(Exception ex)  {
       ex.printStackTrace();
    }
  }
}