/*
* $Id: OracleModule.java,v 1.9 2002/09/16 08:05:03 jkl Exp $
*
* Copyright (c) 2002 Njet Communications Ltd. All Rights Reserved.
*
* Use is subject to license terms, as defined in
* Anvil Sofware License, Version 1.1. See LICENSE
* file, or http://njet.org/license-1.1.txt
*/
package anvil.core.sql.oracle;
import anvil.core.Any;
import anvil.database.PooledConnection;
import anvil.core.sql.AnyConnection;
import anvil.script.Context;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.io.PrintWriter;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.Writer;
import java.io.Reader;
import oracle.sql.CLOB;
///
/// @module anvil.sql.oracle
/// Oracle specific module for handling clobs (Character Long Objects).
/// Reading from/writing to a Oracle clob is impossible using normal
/// JDBC interfaces/classes (when using thin jdbc drivers). This module
/// uses Oracle's own oracle.* classes to access clobs. You need to have
/// classes12.zip (Oracle JDBC 2.0 drivers) in your classpath.
public class OracleModule
{
/// @function setClob
/// Writes parameters data to clobs returned by querystring. Query string should
/// end with "for update" so that Oracle would lock the clob object.
/// @synopsis object setClob(connection conn, string querystring, object parameters...)
/// @param conn Database connection to a Oracle database
/// @param querystring Select querystring (example. select lob from table for update)
/// @param parameters Number of parameters should be same than number of fields
/// returned by querystring. Parameter values are written into those clobs returned
/// by the querystring.
/// @return <code>false</code> if error occurred, or <code>true</code> if everything
/// went ok.
/// @throws SQLException If SQL error occured
/// @throws IOException If an error occured while writing to a clob
public static final Any setClob(Context context, Any aconn, String query, Any[] parameters)
{
PreparedStatement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
conn = ((AnyConnection)aconn).getConnection();
boolean saved_auto_commit = conn.getAutoCommit();
conn.setAutoCommit(false);
stmt = conn.prepareStatement(query);
rs = stmt.executeQuery();
int i = 0;
if (rs.next() && i < parameters.length) {
Writer writer = null;
try {
String param = parameters[i++].toString();
CLOB clob = (CLOB)rs.getClob(i);
writer = clob.getCharacterOutputStream();
writer.write(param.toCharArray(), 0, param.length());
} finally {
try {
if (writer != null) {
writer.close();
}
} catch (Throwable t) {
}
}
}
conn.commit();
conn.setAutoCommit(saved_auto_commit);
return Any.TRUE;
} catch (IOException e) {
throw context.exception(e);
} catch (SQLException e) {
try {
conn.close();
} catch (Throwable t) {
}
throw context.exception(e);
} finally {
try {
if (rs != null) {
rs.close();
}
} catch (Throwable t) {
}
try {
if (stmt != null) {
stmt.close();
}
} catch (Throwable t) {
}
}
}
/// @function getClob
/// Reads value of a clob field returned by querystring. This function
/// works for one field at time.
/// @synopsis object getClob(connection conn, string querystring)
/// @param conn Database connection to a Oracle database
/// @param querystring Select querystring (example. select lob from table)
/// @return string containing clob data, or <code>null</code> if no rows
/// were found.
/// @throws SQLException If SQL error occured
/// @throws IOException If error occured while writing to a clob
public static final Any getClob(Context context, Any aconn, String query)
{
Statement stmt = null;
ResultSet rs = null;
Reader reader = null;
Connection conn = null;
try {
conn = ((AnyConnection)aconn).getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
CLOB clob = (CLOB)rs.getClob(1);
if (clob == null) {
return Any.NULL;
}
reader = clob.getCharacterStream();
int length = (int)clob.length();
//System.err.println("-OracleModule.getClob() length: "+length);
char[] buffer = new char[length];
int offset = 0;
while(offset < length) {
int read = reader.read(buffer, offset, length - offset);
if (read == -1) {
break;
}
offset += read;
}
return Any.create(new String(buffer, 0, offset));
}
return Any.NULL;
} catch (IOException e) {
throw context.exception(e);
} catch (SQLException e) {
try {
conn.close();
} catch (Exception e2) {
}
throw context.exception(e);
} finally {
try {
if (reader != null) {
reader.close();
}
} catch (Throwable t) {
}
try {
if (rs != null) {
rs.close();
}
} catch (Throwable t) {
}
try {
if (stmt != null) {
stmt.close();
}
} catch (Throwable t) {
}
}
}
public static final anvil.script.compiler.NativeNamespace __module__ =
new anvil.script.compiler.NativeNamespace(
"oracle",
OracleModule.class,
new Class[] {},
//DOC{{
""+
" \n" +
" @module anvil.sql.oracle\n" +
" Oracle specific module for handling clobs (Character Long Objects).\n" +
" Reading from/writing to a Oracle clob is impossible using normal\n" +
" JDBC interfaces/classes (when using thin jdbc drivers). This module\n" +
" uses Oracle's own oracle.* classes to access clobs. You need to have \n" +
" classes12.zip (Oracle JDBC 2.0 drivers) in your classpath.\n" +
" @function setClob\n" +
" Writes parameters data to clobs returned by querystring. Query string should\n" +
" end with \"for update\" so that Oracle would lock the clob object.\n" +
" @synopsis object setClob(connection conn, string querystring, object parameters...)\n" +
" @param conn Database connection to a Oracle database\n" +
" @param querystring Select querystring (example. select lob from table for update)\n" +
" @param parameters Number of parameters should be same than number of fields\n" +
" returned by querystring. Parameter values are written into those clobs returned\n" +
" by the querystring.\n" +
" @return <code>false</code> if error occurred, or <code>true</code> if everything\n" +
" went ok.\n" +
" @throws SQLException If SQL error occured\n" +
" @throws IOException If an error occured while writing to a clob\n" +
" @function getClob\n" +
" Reads value of a clob field returned by querystring. This function\n" +
" works for one field at time.\n" +
" @synopsis object getClob(connection conn, string querystring)\n" +
" @param conn Database connection to a Oracle database\n" +
" @param querystring Select querystring (example. select lob from table)\n" +
" @return string containing clob data, or <code>null</code> if no rows\n" +
" were found.\n" +
" @throws SQLException If SQL error occured\n" +
" @throws IOException If error occured while writing to a clob\n"
//}}DOC
);
}