import java.io.IOException;
import java.io.Reader;
import java.sql.*;
import java.util.*;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
/**
* @author Sandeep Desai
*/
interface RowSetCallBack {
public void processRowSet(ResultSet rs) throws SQLException;
}
/**
* @author Sandeep Desai
*/
public class DatabaseUtils
{
public static Connection GetConnection(String url, String user, String password) throws SQLException {
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
return DriverManager.getConnection(url, user, password);
}
public static List GetSchemas(Connection conn) throws SQLException {
List schemas = new ArrayList();
ResultSet rs = null;
try {
DatabaseMetaData md = conn.getMetaData();
rs = md.getSchemas();
while (rs.next())
schemas.add(rs.getObject(1));
} finally {
if (rs != null)
rs.close();
}
return schemas;
}
public static List GetTables(Connection conn, String schema) throws SQLException {
ArrayList tables = new ArrayList();
ResultSet rs = null;
try {
DatabaseMetaData md = conn.getMetaData();
rs = md.getTables(null, schema, null, null);
while (rs.next())
tables.add(rs.getObject(3));
} finally {
if (rs != null)
rs.close();
}
return tables;
}
public static List GetViews(Connection conn, String schema) throws SQLException {
List views = new ArrayList();
String sql = "select view_name from user_views";
List resultList = ExecuteSQL(conn, sql);
for (Iterator it = resultList.iterator(); it.hasNext();) {
List cols = (List) it.next();
views.add(cols.get(0));
}
return views;
}
public static List GetTableColumns(Connection conn, String schema, String table) throws SQLException {
List columns = new ArrayList();
ResultSet rs = null;
try {
DatabaseMetaData md = conn.getMetaData();
rs = md.getColumns(null, schema, table, null);
int cc = rs.getMetaData().getColumnCount();
// 4 is COLUMN_NAME
// 6 is TYPE_NAME
while (rs.next()) {
String name = (String) rs.getObject(4);
String dataType = (String) rs.getObject(6);
TableColumn tc = new TableColumn(name, dataType);
columns.add(tc);
}
} finally {
if (rs != null)
rs.close();
}
return columns;
}
public static List ExecuteSQL(Connection conn, String sql) throws SQLException {
List rows = new ArrayList();
Statement s = null;
try {
s = conn.createStatement();
if ( sql.trim().toUpperCase().startsWith("SELECT") ) {
ResultSet rs = s.executeQuery(sql);
String result = "";
if (rs != null) {
ResultSetMetaData metaData = rs.getMetaData();
if (metaData != null) {
int numcols = metaData.getColumnCount();
while (rs.next()) {
ArrayList cols = new ArrayList();
for (int i = 0; i < numcols; ++i) {
Object value = rs.getObject(i+1);
if (value != null)
cols.add(value.toString().trim());
}
rows.add(cols);
}
}
}
}
else if ( sql.trim().toUpperCase().startsWith("COMMIT")) {
conn.commit();
}
else if ( sql.trim().toUpperCase().startsWith("ROLLBACK")) {
conn.rollback();
}
else {
// Non-select statement
int updateCount = s.executeUpdate(sql);
}
}
finally {
if (s != null) s.close();
}
return rows;
}
/**
*
* public interface RowSetCallBack {
* public void processRowSet(ResultSet rs) throws SQLException;
* }
* @param conn
* @param sql
* @param rowSetCallBack
* @throws SQLException
*/
public static void ExecuteSQL(
Connection conn,
String sql,
RowSetCallBack rowSetCallBack)
throws SQLException {
Statement s = null;
try {
s = conn.createStatement();
if (sql.trim().toUpperCase().startsWith("SELECT")) {
ResultSet rs = s.executeQuery(sql);
String result = "";
if (rs != null) {
ResultSetMetaData metaData = rs.getMetaData();
if (metaData != null) {
int numcols = metaData.getColumnCount();
while (rs.next()) {
rowSetCallBack.processRowSet(rs);
}
}
}
}
else if ( sql.trim().toUpperCase().startsWith("COMMIT")) {
conn.commit();
}
else if ( sql.trim().toUpperCase().startsWith("ROLLBACK")) {
conn.rollback();
}
else {
// Non-select statement
int updateCount = s.executeUpdate(sql);
}
}
finally {
if (s != null) s.close();
}
}
public static String ExecuteSPL(Connection conn, String spl) throws SQLException, IOException {
OracleCallableStatement ocs = (OracleCallableStatement)
conn.prepareCall("begin ? := dbms_aw.interp(?); end;");
ocs.registerOutParameter(1, OracleTypes.CLOB);
ocs.setString(2, spl.toString());
ocs.execute();
oracle.sql.CLOB clob = ocs.getCLOB(1);
Reader instream = clob.getCharacterStream();
char[] buffer = new char[1000];
int length = 0;
StringBuffer outStr = new StringBuffer(2000);
while ((length = instream.read(buffer)) != -1) {
String addStr = new String(buffer, 0, length);
outStr.append(addStr);
}
instream.close();
ocs.close();
return outStr.toString();
}
/**
*
* @param sp Stored procedure name
* @param param1
* @return
* @throws SQLException
*/
public String ExecuteStoredProc(Connection conn, String sp, String param1) throws SQLException {
StringBuffer out = new StringBuffer();
String sql = "begin ? :=" + sp + "(" + param1 + "); end;";
System.out.println("WASession.executeStoredProc(): sql=" + sql);
CallableStatement cs = conn.prepareCall(sql);
cs.registerOutParameter(1, Types.CHAR);
cs.executeUpdate();
String result = cs.getString(1);
out.append(result);
return out.toString();
}
public static void test1(String[] args) {
Connection conn = null;
try {
conn = GetConnection("jdbc:oracle:thin:@stacx25:1522:main2", "scott", "tiger");
List schemas = DatabaseUtils.GetSchemas(conn);
Iterator iter = schemas.iterator();
while (iter.hasNext()) {
String schema = (String) iter.next();
System.out.print(schema +",");
}
System.out.println("");
List tables = DatabaseUtils.GetTables(conn, "XADEMO");
iter = tables.iterator();
while (iter.hasNext()) {
String table = (String) iter.next();
System.out.print(table + ",");
}
System.out.println("");
String sql = "select * from dimtable";
if (args.length > 0)
sql = args[0];
List rows = ExecuteSQL(conn, sql);
Iterator rowIter = rows.iterator();
while (rowIter.hasNext()) {
ArrayList row = (ArrayList) rowIter.next();
Iterator colIter = row.iterator();
while (colIter.hasNext()) {
String col = (String) colIter.next();
System.out.print(col + ",");
}
System.out.println("");
}
conn.close();
} catch (SQLException e) {
System.out.println(e);
}
finally {
try {
if (conn != null) conn.close();
} catch (SQLException e) {
System.out.println(e);
}
}
}
public static void test2(String[] args) {
Connection conn = null;
try {
conn = GetConnection("jdbc:oracle:thin:@stacx25:1522:main2", "xademo", "xademo");
String sql = "call dbms_aw.execute('aw attach zz1 ro')";
ExecuteSQL(conn, sql);
sql = "call dbms_aw.execute('limit TIME66_HIERLIST to ''STANDARD'' ')";
ExecuteSQL(conn, sql);
sql = "call dbms_aw.execute('limit TIME66 to TIME66_INHIER ')";
ExecuteSQL(conn, sql);
sql = "rpr w 50 down TIME66 TIME66_PARENTREL TIME66_LONG_DESCRIPTION";
List rows = ExecuteSQL(conn, sql);
Iterator rowIter = rows.iterator();
while (rowIter.hasNext()) {
ArrayList row = (ArrayList) rowIter.next();
Iterator colIter = row.iterator();
while (colIter.hasNext()) {
String col = (String) colIter.next();
System.out.print(col + ",");
}
System.out.println("");
}
conn.close();
} catch (SQLException e) {
System.out.println(e);
}
finally {
try {
if (conn != null) conn.close();
} catch (SQLException e) {
System.out.println(e);
}
}
}
public static void test3(String[] args) {
Connection conn = null;
try {
conn = GetConnection("jdbc:oracle:thin:@localhost:1521:orcl", "xademo", "xademo");
ExecuteSPL(conn, "aw attach simple11 ro");
//String s = ExecuteSPL(conn, "show eversion");
//System.out.println(s);
String name = "cubea11_sales";
//String spl = "rpr nohead fill '$' w 100 down " + name + " fill '$' w 100 " + name + "_PARENTREL " + attrList;
String spl = "rpr nohead fill '$' w 100 down " + name;
System.out.println(ExecuteSPL(conn, spl));
ExecuteSPL(conn, "aw detach xademo");
} catch (SQLException e) {
System.out.println(e);
} catch (IOException e2) {
System.out.println(e2);
} finally {
try {
if (conn != null) conn.close();
} catch (SQLException e) {
System.out.println(e);
}
}
}
public static void main(String[] args) {
test3(args);
System.out.println(new java.util.Date());
}
}