print Printer View e-mail E-mail this page Bookmark Bookmark
How to store and retrieve references to external data

How to store and retrieve references to external data

Date: February 09, 2004

How to store and retrieve references to external data

After completing this How-to you should be able to:
  • Use DATALINK Datatype in a JDBC application.

Reader should be familiar with

To understand this document the readers should have basic knowledge about JDBC.

Introduction

This document illustrates how to use the datalink datatype to store and retrieve, references to the external resources.

Description

As part of the changes to the JDBC3.0 specifications, two new datatypes DATALINK and BOOLEAN have been added. The DATALINK datatype enables distributed JDBC applications to retrieve and store references to external data, from and into the database. The newly added type refer to the SQL type of the same names. DATALINK provides access to external resources, or URLs. The value of a DATALINK column is retrieved from an instance of ResultSet using the new getURL() methods.

Methods are added to classes in oracle.jdbc package to support,

  • Retrieving references to external data with a DATALINK.
  • Storing references to external data
  • Obtaining DATALINK related metadata information.

The following methods are available in java.sql.ResultSet,

public java.net.URL getURL(int columnIndex) throws SQLException;
public java.net.URL getURL(String columnName) throws SQLException;

Methods in java.sql.CallableStatement

public java.net.URL getURL(int parameterIndex) throws SQLException;
public java.net.URL getURL(String parameterName) throws SQLException;

Method in java.sql.PreparedStatement

public void setURL(int parameterIndex, java.net.URL x) throws SQLException;

To retrieve the all the type codes available in the java.sql.Types,  you can use the following methods from the java.sql.DatabaseMetaData class,

public java.sql.ResultSet getTypeInfo(...)
public java.sql.ResultSet getColumns(....)

Code Snippet

The code below inserts an HTTP URL object in to the database using the PreparedStatement.
    

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"; 

pstmt.setString(1,"Save Point Sample");

// Set the URL object
pstmt.setURL(2,new URL(sampleUrl));


The below code shows how to retrieve the url and display the contents in the HTTP URL. 

ResultSet rs = stmt.executeQuery( "SELECT sample_name, url FROM sample_repository " );

if ( rs.next() ) { 
  sampleName = rs.getString(1);
  // Retrieve the value as a URL object.
  url = rs.getURL(2); 
 
  if ( url != null ) { 
    HttpURLConnection urlConnection=(HttpURLConnection) 
                                        url.openConnection();
    BufferedReader bReader = new BufferedReader( 
                                    new InputStreamReader( 
                                         urlConnection.getInputStream())); 
    String pageContent = null; 
    while ( ( pageContent=bReader.readLine() )!=null ) { 
      System.out.println(pageContent);
 
    } 
  } 
}

You can view the complete source code here.

Pre-requisites for running the example

You will need the following to run this example -
  • JDK1.4.x or higher. This can be downloaded from here

  • Oracle Database 10g or higher running SQL*Net TCP/IP Listener. This can be downloaded from here.

  • Oracle Database 10g JDBC driver. This can be downloaded from here.

Running the Sample

  • Copy and save the sample source in your working directory.

  • Compile the source file. To run the sample code execute the following command,
    java oracle.otnsamples.jdbc.Datalink

  • If you are behind the firewall, execute the following command,
    java -Dhttp.proxyHost=<your_proxy_server> oracle.otnsamples.jdbc.Datalink

Summary

This How-To document explained how to store and retrieve URLs into and from the database.

Please enter your comments about this sample in the OTN Sample Code Discussion Forum.






E-mail this page
Printer View Printer View
Oracle Is The Information Company About Oracle | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Privacy