Not the best solution, i guess. But its working now.
I wrote a java class in oracle to access the mysql (and others DB´s) through JDBC.
Than I wrote a pl/sql package to use this class.
Know i can get blob and clob from mysql.
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED JDBC_HANDLER
AS
import java.util.Properties;
import java.sql.*;
import java.net.URL;
import java.net.URLClassLoader;
import oracle.sql.*;
import java.io.File;
import oracle.jdbc.*;
public class jdbc_handler
{
private static final int MAX_QUERY = 16;
private static final String mysql_driver = "com.mysql.jdbc.Driver";
private static String status = "Desconectado.";
private static String error_message = null;
private static int error_code = 0;
private static String conn_string = "";
private static String jdbc_driver = "";
private static String driver_url = "";
private static String db_user = "";
private static String db_pass = "";
private static java.sql.Connection db_conn = null;
private static java.sql.Statement[] stmt = new java.sql.Statement[MAX_QUERY];
private static java.sql.ResultSet[] rs = new java.sql.ResultSet[MAX_QUERY];
private static java.sql.PreparedStatement[] pstmt = new java.sql.PreparedStatement[MAX_QUERY];
private static int _idx = -1;
private static boolean isMySQL = false;
//-------------------------------------------------------------------------------------
public static void setDBUser(String db_user) {
jdbc_handler.db_user = db_user;
}
//-------------------------------------------------------------------------------------
public static void setDBPassword(String db_pass) {
jdbc_handler.db_pass = db_pass;
}
//-------------------------------------------------------------------------------------
public static void defineMySQL(String db_host, String db_base, String db_user, String db_pass) {
jdbc_handler.setDriver(mysql_driver);
jdbc_handler.setConnectionString("jdbc:mysql://" + db_host + "/" + db_base + "?useAffectedRows=true&cacheResultSetMetadata=true&useCursorFetch=true&defaultFetchSize=256&connectTimeout=0&socketTimeout=0&rewriteBatchedStatements=true&useServerPrepStmts=true&cachePrepStmts=false&useCompression=false&useDynamicCharsetInfo=false");
jdbc_handler.setDBUser(db_user);
jdbc_handler.setDBPassword(db_pass);
jdbc_handler.isMySQL = true;
}
//-------------------------------------------------------------------------------------
public static void setDriverPath(String jar_file) {
File f = new File(jar_file);
if ( f.exists() ) {
jdbc_handler.driver_url = f.toURI().toString();
} else {
jdbc_handler.driver_url = "";
}
f = null;
}
//-------------------------------------------------------------------------------------
public static void setDriver(String jdbc_driver) {
jdbc_handler.jdbc_driver = jdbc_driver;
}
//-------------------------------------------------------------------------------------
public static void setConnectionString(String str) {
jdbc_handler.conn_string = str;
}
//-------------------------------------------------------------------------------------
public static String getDriver() {
return(jdbc_handler.jdbc_driver);
}
//-------------------------------------------------------------------------------------
public static String getDriverURL() {
return(jdbc_handler.driver_url);
}
//-------------------------------------------------------------------------------------
public static String getConnectionString() {
return(jdbc_handler.conn_string);
}
//-------------------------------------------------------------------------------------
private static void error(String msg) {
jdbc_handler.error(msg, -1, msg);
}
private static void error(String msg, String error_message) {
jdbc_handler.error(msg, -1, error_message);
}
private static void error(String msg, int error_code) {
jdbc_handler.error(msg, error_code, msg);
}
private static void error(String msg, int error_code, String error_message) {
jdbc_handler.status = msg;
jdbc_handler.error_code = error_code;
jdbc_handler.error_message = error_message;
if ( jdbc_handler.isEmptyOrNull(jdbc_handler.status) && !jdbc_handler.isEmptyOrNull(jdbc_handler.error_message) ) {
jdbc_handler.status = jdbc_handler.error_message;
}
if ( !jdbc_handler.isEmptyOrNull(jdbc_handler.status) && jdbc_handler.isEmptyOrNull(jdbc_handler.error_message) ) {
jdbc_handler.error_message = jdbc_handler.status;
}
}
//-------------------------------------------------------------------------------------
private static void success(String msg) {
jdbc_handler.status = msg;
jdbc_handler.error_code = 0;
jdbc_handler.error_message = "";
}
//-------------------------------------------------------------------------------------
public static int isOpen() {
try {
if ( jdbc_handler.db_conn == null ) {
return(0);
}
if ( jdbc_handler.db_conn.isClosed() ) {
return(0);
} else {
return(1);
}
} catch (SQLException e) {
return(0);
}
}
public static int openDatabase() {
int i, x;
oracle.sql.NUMBER o;
String s;
if ( jdbc_handler.isEmptyOrNull(jdbc_handler.conn_string) ) {
jdbc_handler.error("String de conexão inválida");
return(0);
} else if ( jdbc_handler.isEmptyOrNull(jdbc_handler.db_user) ) {
jdbc_handler.error("Usuário Inválido");
return(0);
} else if ( jdbc_handler.isEmptyOrNull(jdbc_handler.db_pass) ) {
jdbc_handler.error("Senha Inválida");
return(0);
} else if ( jdbc_handler.isEmptyOrNull(jdbc_handler.jdbc_driver) ) {
jdbc_handler.error("Driver JDBC Inválido");
return(0);
}
try {
closeDatabase();
jdbc_handler.db_conn = DriverManager.getConnection(jdbc_handler.conn_string, jdbc_handler.db_user, jdbc_handler.db_pass);
if (jdbc_handler.db_conn != null) {
jdbc_handler.db_conn.setAutoCommit(false);
jdbc_handler.success("Conectado.");
} else {
jdbc_handler.error("Erro conectando ao banco de dados.");
}
} catch (SQLException e) {
jdbc_handler.error("Erro conectando ao banco de dados", e.getErrorCode(), e.getMessage());
return 0;
} catch ( Exception e ) {
jdbc_handler.error(e.getMessage());
return 0;
}
return 1;
}
//-------------------------------------------------------------------------------------
private static int newRecordSetIndex() {
int idx = -1;
boolean achou = false;
for (idx = 0; idx < MAX_QUERY; idx++) {
if ( jdbc_handler.rs[idx] == null ) {
achou = true;
break;
}
}
if ( !achou ) {
jdbc_handler.error("Máximo de query simultaneas ultrapassado.");
return(-1);
} else {
return(idx);
}
}
//-------------------------------------------------------------------------------------
public static int openQuery(String sql) {
int idx = jdbc_handler.newRecordSetIndex();
if ( idx != -1 ) {
try {
jdbc_handler.stmt[idx] = jdbc_handler.db_conn.createStatement() ;
jdbc_handler.rs[idx] = jdbc_handler.stmt[idx].executeQuery(sql);
jdbc_handler._idx = idx;
} catch (SQLException e) {
jdbc_handler.error(e.getMessage(), e.getErrorCode());
closeDatabase();
} catch (Throwable ignore) {
} ;
}
return idx;
}
//-------------------------------------------------------------------------------------
public static int executeDML(String sql) {
int rows = 0;
try {
java.sql.Statement stmt = jdbc_handler.db_conn.createStatement() ;
rows = stmt.executeUpdate(sql);
if ( stmt != null ) {
stmt.close();
stmt = null;
}
} catch (SQLException e) {
rows = -1;
jdbc_handler.error(e.getMessage(), e.getErrorCode());
jdbc_handler.rollback();
closeDatabase();
} ;
return rows;
}
//-------------------------------------------------------------------------------------
private static int newPreparedIndex() {
int idx = -1;
boolean achou = false;
for (idx = 0; idx < MAX_QUERY; idx++) {
if ( jdbc_handler.pstmt[idx] == null ) {
achou = true;
break;
}
}
if ( !achou ) {
jdbc_handler.error("Máximo de query simultaneas ultrapassado.");
return(-1);
} else {
return(idx);
}
}
//-------------------------------------------------------------------------------------
public static int prepareDML(String sql) {
int idx = jdbc_handler.newPreparedIndex();
if ( idx != -1 ) {
try {
jdbc_handler.pstmt[idx] = jdbc_handler.db_conn.prepareStatement(sql) ;
} catch (SQLException e) {
jdbc_handler.error(e.getMessage(), e.getErrorCode());
closeDatabase();
} catch (Throwable ignore) {
} ;
}
return idx;
}
//-------------------------------------------------------------------------------------
public static int executePreparedDML(oracle.sql.NUMBER pidx, oracle.sql.NUMBER pclose) throws Exception {
int idx = -1;
int rows = 0;
try {
idx = pidx.intValue();
rows = jdbc_handler.pstmt[idx].executeUpdate();
if ( jdbc_handler.pstmt[idx] != null && pclose.intValue() == 1 ) {
jdbc_handler.pstmt[idx].close();
jdbc_handler.pstmt[idx] = null;
}
} catch (SQLException e) {
rows = -1;
jdbc_handler.error(e.getMessage(), e.getErrorCode());
jdbc_handler.rollback();
closeDatabase();
} catch ( Exception e ) {
jdbc_handler.error(e.getMessage() + "\n" + getMySQLPreparedSQL(pidx));
jdbc_handler.rollback();
jdbc_handler.closeDatabase();
throw e;
} ;
return rows;
}
//-------------------------------------------------------------------------------------
public static String getMySQLPreparedSQL(oracle.sql.NUMBER pidx) {
String s;
try {
s = (((com.mysql.jdbc.JDBC4PreparedStatement)jdbc_handler.pstmt[pidx.intValue()])).asSql();
} catch (SQLException e) {
s = "Erro";
jdbc_handler.error(e.getMessage(), e.getErrorCode());
} ;
return s;
}
//-------------------------------------------------------------------------------------
public static int executePreparedDML(oracle.sql.NUMBER pidx) throws Exception {
oracle.sql.NUMBER c = new oracle.sql.NUMBER(0);
int idx;
idx = executePreparedDML(pidx, c);
c = null;
return idx;
}
//-------------------------------------------------------------------------------------
public static void closePreparedDML(oracle.sql.NUMBER pidx) {
int idx = -1;
try {
idx = pidx.intValue();
if ( jdbc_handler.pstmt[idx] != null ) {
jdbc_handler.pstmt[idx].close();
jdbc_handler.pstmt[idx] = null;
}
} catch (SQLException e) {
jdbc_handler.error(e.getMessage(), e.getErrorCode());
jdbc_handler.rollback();
closeDatabase();
} ;
}
//-------------------------------------------------------------------------------------
public static void commit() {
try {
jdbc_handler.db_conn.commit();
} catch (SQLException e) {
jdbc_handler.error(e.getMessage(), e.getErrorCode());
}
}
//-------------------------------------------------------------------------------------
public static void rollback() {
try {
jdbc_handler.db_conn.commit();
} catch (SQLException e) {
jdbc_handler.error(e.getMessage(), e.getErrorCode());
}
}
//-------------------------------------------------------------------------------------
public static int fetchRows(oracle.sql.NUMBER pidx) throws SQLException {
int retorno = 0;
int idx = pidx.intValue();
if ( jdbc_handler.rs[idx].next() ) {
retorno = 1;
} else {
retorno = 0;
}
return retorno;
}
//-------------------------------------------------------------------------------------
public static int fetchRows() throws SQLException {
int retorno = 0;
if ( jdbc_handler.rs[jdbc_handler._idx].next() ) {
retorno = 1;
} else {
retorno = 0;
}
return retorno;
}
//-------------------------------------------------------------------------------------
public static void first(oracle.sql.NUMBER pidx) {
int idx = -1;
try {
idx = pidx.intValue();
jdbc_handler.rs[idx].beforeFirst();
} catch (SQLException e) {
jdbc_handler.error(e.getMessage(), e.getErrorCode());
closeDatabase();
} catch (Throwable ignore) {
};
}
//-------------------------------------------------------------------------------------
public static void closeQuery(oracle.sql.NUMBER pidx) {
int idx = -1;
try {
idx = pidx.intValue();
if ( jdbc_handler.rs[idx] != null ) {
jdbc_handler.rs[idx].close();
}
if ( jdbc_handler.stmt[idx] != null ) {
jdbc_handler.stmt[idx].close();
}
if ( jdbc_handler.pstmt[idx] != null ) {
jdbc_handler.pstmt[idx].close();
}
} catch (SQLException e) {
jdbc_handler.error(e.getMessage(), e.getErrorCode());
closeDatabase();
} catch (Throwable ignore) {
} finally {
jdbc_handler.rs[idx] = null;
jdbc_handler.stmt[idx] = null;
jdbc_handler.pstmt[idx] = null;
};
}
//-------------------------------------------------------------------------------------
private static void closeAllQueries() {
oracle.sql.NUMBER idx;
for ( int i = 0; i < MAX_QUERY; i++ ) {
idx = new oracle.sql.NUMBER(i);
closeQuery(idx);
idx = null;
}
}
//-------------------------------------------------------------------------------------
public static void setRs(oracle.sql.NUMBER pidx) throws SQLException {
jdbc_handler._idx = pidx.intValue();
}
//-------------------------------------------------------------------------------------
private static java.sql.Array array_wrapper(String typeName, Object elements) throws java.sql.SQLException {
oracle.jdbc.OracleDriver ora = new oracle.jdbc.OracleDriver();
java.sql.Connection conn = ora.defaultConnection();
oracle.sql.ArrayDescriptor desc = oracle.sql.ArrayDescriptor.createDescriptor(typeName.toUpperCase(), conn);
java.sql.Array arr = new oracle.sql.ARRAY(desc, conn, elements);
return arr;
}
private static String[] getRowAsArray() throws SQLException {
String[] r;
ResultSetMetaData rsMetaData = jdbc_handler.rs[jdbc_handler._idx].getMetaData();
int ColumnsCount = rsMetaData.getColumnCount();
r = new String[ColumnsCount];
for (int i = 1; i <= ColumnsCount; i++) {
if ( jdbc_handler.rs[jdbc_handler._idx].getObject(i) == null ){
r[i-1] = "";
} else {
r[i-1] = jdbc_handler.rs[jdbc_handler._idx].getObject(i).toString();
}
}
return (r);
}
public static java.sql.Array getRow() throws SQLException {
return( array_wrapper("VARCHAR_TABLE", getRowAsArray() ) );
}
//-------------------------------------------------------------------------------------
public static float getFloat(String column) throws SQLException {
return(jdbc_handler.rs[jdbc_handler._idx].getFloat(column));
}
//-------------------------------------------------------------------------------------
public static long getLong(String column) throws SQLException {
return(jdbc_handler.rs[jdbc_handler._idx].getLong(column));
}
//-------------------------------------------------------------------------------------
public static Timestamp getDate(String column) throws SQLException {
return(jdbc_handler.rs[jdbc_handler._idx].getTimestamp(column));
}
//-------------------------------------------------------------------------------------
public static int getInteger(String column) throws SQLException {
return(jdbc_handler.rs[jdbc_handler._idx].getInt(column));
}
//-------------------------------------------------------------------------------------
public static String getString(String column) throws SQLException {
return jdbc_handler.rs[jdbc_handler._idx].getString(column);
}
//-------------------------------------------------------------------------------------
public static oracle.sql.BLOB getBlob(String column) throws SQLException {
Blob r;
long s = 0;
oracle.sql.BLOB b;
java.sql.Connection conn = DriverManager.getConnection("jdbc:default:connection:");
b = oracle.sql.BLOB.createTemporary(conn, true, 10);
r = jdbc_handler.rs[jdbc_handler._idx].getBlob(column);
b.open(1);
s = b.setBytes(1, r.getBytes((long)1, (int)r.length()));
b.close();
conn = null;
return(b);
}
//-------------------------------------------------------------------------------------
public static float getFloat(oracle.sql.NUMBER pidx, String column) throws SQLException {
int idx = pidx.intValue();
return(jdbc_handler.rs[idx].getFloat(column));
}
//-------------------------------------------------------------------------------------
public static long getLong(oracle.sql.NUMBER pidx, String column) throws SQLException {
int idx = pidx.intValue();
return(jdbc_handler.rs[idx].getLong(column));
}
//-------------------------------------------------------------------------------------
public static Timestamp getDate(oracle.sql.NUMBER pidx, String column) throws SQLException {
int idx = pidx.intValue();
return(jdbc_handler.rs[idx].getTimestamp(column));
}
//-------------------------------------------------------------------------------------
public static int getInteger(oracle.sql.NUMBER pidx, String column) throws SQLException {
int idx = pidx.intValue();
return(jdbc_handler.rs[idx].getInt(column));
}
//-------------------------------------------------------------------------------------
public static String getString(oracle.sql.NUMBER pidx, String column) throws SQLException {
int idx = pidx.intValue();
return jdbc_handler.rs[idx].getString(column);
}
//-------------------------------------------------------------------------------------
public static oracle.sql.BLOB getBlob(oracle.sql.NUMBER pidx, String column) throws SQLException {
int idx = pidx.intValue();
Blob r;
long s = 0;
oracle.sql.BLOB b;
java.sql.Connection conn = DriverManager.getConnection("jdbc:default:connection:");
b = oracle.sql.BLOB.createTemporary(conn, true, 10);
r = jdbc_handler.rs[idx].getBlob(column);
b.open(1);
s = b.setBytes(1, r.getBytes((long)1, (int)r.length()));
b.close();
conn = null;
return(b);
}
//-------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------
public static void setFloat(oracle.sql.NUMBER pidx, oracle.sql.NUMBER column, oracle.sql.NUMBER value) throws SQLException {
if ( value == null ) {
jdbc_handler.pstmt[pidx.intValue()].setNull(column.intValue(), java.sql.Types.FLOAT);
} else {
jdbc_handler.pstmt[pidx.intValue()].setFloat(column.intValue(), value.floatValue());
}
}
//-------------------------------------------------------------------------------------
public static void setLong(oracle.sql.NUMBER pidx, oracle.sql.NUMBER column, oracle.sql.NUMBER value) throws SQLException {
if ( value == null ) {
jdbc_handler.pstmt[pidx.intValue()].setNull(column.intValue(), java.sql.Types.BIGINT);
} else {
jdbc_handler.pstmt[pidx.intValue()].setLong(column.intValue(), value.longValue());
}
}
//-------------------------------------------------------------------------------------
public static void setDate(oracle.sql.NUMBER pidx, oracle.sql.NUMBER column, oracle.sql.DATE value) throws SQLException {
if ( value == null ) {
jdbc_handler.pstmt[pidx.intValue()].setNull(column.intValue(), java.sql.Types.TIMESTAMP);
} else {
jdbc_handler.pstmt[pidx.intValue()].setTimestamp(column.intValue(), value.timestampValue());
}
}
//-------------------------------------------------------------------------------------
public static void setInteger(oracle.sql.NUMBER pidx, oracle.sql.NUMBER column, oracle.sql.NUMBER value) throws SQLException {
if ( value == null ) {
jdbc_handler.pstmt[pidx.intValue()].setNull(column.intValue(), java.sql.Types.INTEGER);
} else {
jdbc_handler.pstmt[pidx.intValue()].setInt(column.intValue(), value.intValue());
}
}
//-------------------------------------------------------------------------------------
public static void setString(oracle.sql.NUMBER pidx, oracle.sql.NUMBER column, String value) throws SQLException {
if ( value == null || value == "" ) {
jdbc_handler.pstmt[pidx.intValue()].setNull(column.intValue(), java.sql.Types.VARCHAR);
} else {
jdbc_handler.pstmt[pidx.intValue()].setString(column.intValue(), value);
}
}
//-------------------------------------------------------------------------------------
public static void setBlob(oracle.sql.NUMBER pidx, oracle.sql.NUMBER column, oracle.sql.BLOB value) throws SQLException {
if ( value == null ) {
jdbc_handler.pstmt[pidx.intValue()].setNull(column.intValue(), java.sql.Types.BLOB);
} else {
jdbc_handler.pstmt[pidx.intValue()].setBlob(column.intValue(), value);
}
}
//-------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------
public static String getStatus() {
return jdbc_handler.status;
}
//-------------------------------------------------------------------------------------
public static int getErrorCode() {
return jdbc_handler.error_code;
}
//-------------------------------------------------------------------------------------
public static String getErrorMessage() {
return jdbc_handler.error_message;
}
//-------------------------------------------------------------------------------------
public static void closeDatabase() {
closeAllQueries();
try {
if ( jdbc_handler.db_conn != null ) {
jdbc_handler.db_conn.close();
jdbc_handler.status = "Desconectado.";
}
} catch (SQLException e) {
jdbc_handler.error(e.getMessage(), e.getErrorCode());
} finally {
jdbc_handler.db_conn = null;
System.gc();
}
}
//-------------------------------------------------------------------------------------
private static boolean isEmptyOrNull(String str) {
String x;
if ( str != null && str.trim() != null ) {
x = str.trim();
if ( x == null ) {
return true;
} else {
if ( x.length() == 0 ) {
return true;
}
}
} else {
return true;
}
return false;
}
};