001: package test;
002:
003: import java.io.BufferedReader;
004: import java.io.InputStream;
005: import java.io.InputStreamReader;
006: import java.io.Reader;
007: import java.sql.Connection;
008: import java.sql.DriverManager;
009: import java.sql.PreparedStatement;
010: import java.sql.ResultSet;
011: import java.sql.SQLException;
012: import java.sql.Statement;
013:
014: import net.sourceforge.squirrel_sql.client.ApplicationArguments;
015: import net.sourceforge.squirrel_sql.fw.sql.SQLUtilities;
016: import oracle.sql.OPAQUE;
017: import oracle.xdb.XMLType;
018:
019: /*
020: * Copyright (C) 2007 Rob Manning
021: * manningr@users.sourceforge.net
022: *
023: * This library is free software; you can redistribute it and/or
024: * modify it under the terms of the GNU Lesser General Public
025: * License as published by the Free Software Foundation; either
026: * version 2.1 of the License, or (at your option) any later version.
027: *
028: * This library is distributed in the hope that it will be useful,
029: * but WITHOUT ANY WARRANTY; without even the implied warranty of
030: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
031: * Lesser General Public License for more details.
032: *
033: * You should have received a copy of the GNU Lesser General Public
034: * License along with this library; if not, write to the Free Software
035: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
036: */
037:
038: public class OracleSqlXmlTypeTest {
039:
040: private static void test(Connection con) throws Exception {
041: OPAQUE opaque = getOpaque(con);
042: readOpaqueAsBytes(opaque);
043: opaque = getOpaque(con);
044: readOpaqueAsCharacterStream(opaque);
045: opaque = getOpaque(con);
046: readOpaqueAsJdbc(opaque);
047: opaque = getOpaque(con);
048: readOpaqueAsAsciiStreamValue(opaque);
049: opaque = getOpaque(con);
050: readObjectUsingXmlType(opaque);
051: setNullXmlValue(con);
052: }
053:
054: private static OPAQUE getOpaque(Connection con) throws Exception {
055: OPAQUE result = null;
056: ResultSet rs = null;
057: Statement stmt = null;
058: try {
059: stmt = con.createStatement();
060: //System.out.println("Selecting xml_data");
061: rs = stmt.executeQuery("select xml_data from xmltable");
062: if (rs.next()) {
063: result = (oracle.xdb.XMLType) rs.getObject(1);
064: }
065: } catch (SQLException e) {
066: e.printStackTrace();
067: } finally {
068: SQLUtilities.closeResultSet(rs);
069: }
070: return result;
071: }
072:
073: /**
074: * @param args
075: */
076: public static void main(String[] args) throws Exception {
077: ApplicationArguments.initialize(new String[] {});
078: Class.forName("oracle.jdbc.OracleDriver");
079: String jdbcUrl = "jdbc:oracle:thin:@192.168.1.100:1521:XE";
080: Connection con = DriverManager.getConnection(jdbcUrl,
081: "testdrop", "password");
082: test(con);
083: }
084:
085: private static void readOpaqueAsCharacterStream(OPAQUE opaque) {
086: try {
087: // The following gives
088: // java.sql.SQLException: Conversion to character stream failed
089: System.out.println("\nreadOpaqueAsCharacterStream: ");
090: Reader reader = opaque.characterStreamValue();
091: char[] buffer = new char[32];
092:
093: while (reader.read(buffer) != -1) {
094: System.out.println("buffer: " + buffer.toString());
095: }
096: } catch (Exception e) {
097: e.printStackTrace();
098: }
099: }
100:
101: private static void readOpaqueAsBytes(OPAQUE opaque)
102: throws SQLException {
103: byte[] bytes = (byte[]) opaque.getValue();
104: System.out.println("\nreadOpaqueAsBytes: " + new String(bytes));
105: }
106:
107: private static void readOpaqueAsJdbc(OPAQUE opaque)
108: throws SQLException {
109: Object o = opaque.toJdbc();
110: System.out.println("\readOpaqueAsJdbc: " + o.toString());
111: }
112:
113: private static void readOpaqueAsAsciiStreamValue(OPAQUE opaque) {
114: try {
115: InputStream is = opaque.asciiStreamValue();
116: BufferedReader reader = new BufferedReader(
117: new InputStreamReader(is));
118: String line = null;
119: while ((line = reader.readLine()) != null) {
120: System.out.println("\readOpaqueAsAsciiStreamValue: "
121: + line);
122: }
123: } catch (Exception e) {
124: e.printStackTrace();
125: }
126:
127: }
128:
129: private static void readObjectUsingXmlType(OPAQUE opaque)
130: throws Exception {
131: XMLType xml = XMLType.createXML(opaque);
132: System.out.println("readObjectUsingXmlType: "
133: + xml.getStringVal());
134: }
135:
136: private static void setNullXmlValue(Connection con) {
137: PreparedStatement pstmt = null;
138: try {
139: String sql = "update xmltable set XML_DATA = ? where DOC_ID = 3";
140: pstmt = con.prepareStatement(sql);
141: pstmt.setObject(1, null);
142: if (pstmt.executeUpdate() == 1) {
143: System.out.println("Successully set XML_DATA to null");
144: }
145: } catch (Exception e) {
146: e.printStackTrace();
147: } finally {
148: SQLUtilities.closeStatement(pstmt);
149: }
150: }
151: }
|