001/* -*- c-basic-offset: 4; indent-tabs-mode: nil -*- */
002/*
003 *                    BioJava development code
004 *
005 * This code may be freely distributed and modified under the
006 * terms of the GNU Lesser General Public Licence.  This should
007 * be distributed with the code.  If you do not have a copy,
008 * see:
009 *
010 *      http://www.gnu.org/copyleft/lesser.html
011 *
012 * Copyright for this code is held jointly by the individual
013 * authors.  These should be listed in @author doc comments.
014 *
015 * For more information on the BioJava project and its aims,
016 * or to join the biojava-l mailing list, visit the home page
017 * at:
018 *
019 *      http://www.biojava.org/
020 *
021 */
022
023package org.biojava.bio.seq.db.biosql;
024import java.lang.reflect.Method;
025import java.sql.Clob;
026import java.sql.Connection;
027import java.sql.DatabaseMetaData;
028import java.sql.PreparedStatement;
029import java.sql.ResultSet;
030import java.sql.SQLException;
031import java.sql.Statement;
032
033import javax.sql.DataSource;
034
035import org.biojava.bio.BioRuntimeException;
036
037/**
038 * This is a <code>DBHelper</code> that provides support for Oracle
039 * databases.
040 *
041 * @author Len Trigg
042 * @author Eric Haugen
043 * @author Richard Holland
044 * @deprecated Use hibernate and org.biojavax.bio.db.*
045 */
046public class OracleDBHelper extends DBHelper {
047    
048    private final JoinStyle mJoinStyle;
049    private final BioSequenceStyle mBseqStyle;
050    
051    public OracleDBHelper(Connection connection) {
052        JoinStyle joinStyle = JOIN_GENERIC;
053        BioSequenceStyle bseqStyle = BIOSEQUENCE_GENERIC;
054        try {
055            DatabaseMetaData metadata = connection.getMetaData();
056            String version = metadata.getDatabaseProductVersion();
057            if ((version != null) && version.startsWith("Oracle8")) {
058                joinStyle = JOIN_ORACLE8;
059            }
060            // Describe the biosequence table
061            Statement st = null;
062            ResultSet rs = null;
063            try {
064                // For CLOB access, the Oracle 9i (or better) JDBC drivers are required on the ClassPath.
065                // This simple test for the BioSequence seq storage type makes some basic assumptions:
066                //    1. That if you are using Len Trigg's schema, you are logged into it directly and
067                //       not using views onto it.
068                //    2. That if you are logged into any schema using views, then they are views onto
069                //       the standard CLOB based schema.
070                st = connection.createStatement();
071                rs = st.executeQuery("select data_type from user_tab_columns where table_name='BIOSEQUENCE' and column_name='SEQ'");
072                String seqType = null;
073                if (rs.next()) {
074                    seqType = rs.getString(1);
075                }
076                // If it's missing or says CLOB, then use the CLOB interfaces.
077                // Else, use BIOSEQUENCE_GENERIC (and assume it allows normal get/set calls)
078                if (seqType==null || "CLOB".equals(seqType)) bseqStyle = BIOSEQUENCE_ORACLECLOB;
079            } finally {
080                if (rs != null) try { rs.close(); } catch (SQLException se) { }
081                if (st != null) try { st.close(); } catch (SQLException se) { }
082            }
083        } catch (SQLException e) {
084            System.err.println("Exception getting DatabaseMetaData:" +  e.getMessage());
085            // Stick with generic style
086        }
087        mJoinStyle = joinStyle;
088        mBseqStyle = bseqStyle;
089    }
090    
091    
092    // Inherit docs
093    public JoinStyle getJoinStyle() {
094        return mJoinStyle;
095    }
096    
097    
098    // Inherit docs
099    public int getInsertID(Connection conn, String table, String columnName) throws SQLException {
100        Statement st = null;
101        ResultSet rs = null;
102        try {
103            st = conn.createStatement();
104            // We assume that the Oracle BioSQL schema uses sequences for the autoincrement fields,
105            // one sequence per table.
106            rs = st.executeQuery("select " + table + "_pk_seq.CURRVAL from dual");
107            int id = -1;
108            if (rs.next()) {
109                id = rs.getInt(1);
110            }
111            
112            if (id < 1) {
113                throw new SQLException("Couldn't get last insert id");
114            }
115            return id;
116        } finally {
117            if (rs != null) try { rs.close(); } catch (SQLException se) { }
118            if (st != null) try { st.close(); } catch (SQLException se) { }
119        }
120    }
121    
122    // Inherit docs
123    public boolean containsTable(DataSource ds, String tablename) {
124        if (ds == null) {
125            throw new NullPointerException("Require a datasource.");
126        }
127        if ((tablename == null) || (tablename.length() == 0)) {
128            throw new IllegalArgumentException("Invalid table name given");
129        }
130        //System.err.println("Checking for table existence: " + tablename);
131        Connection conn = null;
132        try {
133            boolean present;
134            conn = ds.getConnection();
135            PreparedStatement ps = conn.prepareStatement("select rownum from " + tablename + " where rownum < 1");
136            try {
137                ps.executeQuery();
138                present = true;
139            } catch (SQLException ex) {
140                //System.err.println("Table " + tablename + " does not exist.");
141                present = false;
142            } finally {
143                ps.close();
144                if (conn != null) {
145                    conn.close();
146                }
147            }
148            return present;
149        } catch (SQLException ex) {
150            if (conn!=null) try {conn.close();} catch (SQLException ex3) {}
151            throw new BioRuntimeException(ex);
152        }
153    }
154    
155    // Inherit docs
156    public BioSequenceStyle getBioSequenceStyle() {
157        return mBseqStyle;
158    }
159    
160    /*
161     * Use this to retrieve a CLOB value.
162     * @param conn a connection to an Oracle database.
163     * @param rs the ResultSet to retrieve the CLOB from.
164     * @param column the number of the column in the ResultSet that the CLOB lives in.
165     * @return String value of the CLOB.
166     */
167    public String clobToString(Connection conn, ResultSet rs, int column) {
168        try {
169            Clob seqclob = rs.getClob(column);
170            StringBuffer buf = new StringBuffer();
171            int bufSize = 1024;
172            long start = 1L;
173            long remain = seqclob.length();
174            while (remain>0L) {
175                if (bufSize>remain) bufSize=(int)remain;
176                buf.append(seqclob.getSubString(start,bufSize));
177                start+=bufSize;
178                remain-=bufSize;
179            }
180            return buf.toString().trim();
181        } catch (Exception ex) {
182            throw new BioRuntimeException(ex);
183        }
184    }
185    
186    /*
187     * Use this to set a CLOB value. OJDBC version 9i must be on the ClassPath.
188     * @param conn a connection to an Oracle database.
189     * @param rs the ResultSet to retrieve the CLOB from.
190     * @param column the number of the column in the ResultSet that the CLOB lives in.
191     * @param the value to set to the CLOB.
192     */
193    public void stringToClob(Connection conn, ResultSet rs, int column, String value) {
194        try {
195            // Can't use oracle.sql.CLOB directly as we'd need it at compile time otherwise.
196            Class clob = Class.forName("oracle.sql.CLOB");
197            Method putString = clob.getDeclaredMethod("putString",new Class[]{long.class,String.class});
198            // Only get here if we have some data to write.
199            if (value==null) value=""; // To stop null pointer exceptions. End result is the same.
200            putString.invoke(rs.getClob(column), new Object[]{new Long(1L),value});
201        } catch (Exception ex) {
202            throw new BioRuntimeException(ex);
203        }
204    }
205    
206}