[DERBY-6678] Array Index out of bounds when accessing Oracle through the thin driver using a derby table function. - ASF JIRA

Details

Type: Bug
Status: Closed
Priority: Blocker
Resolution: Not A Problem
Affects Version/s: 10.10.2.0
Fix Version/s: None
Component/s: JDBC
Labels:
None
Environment:
Windows 7
JDK 1.7 u 65 (64bit)
Oracle 11.2 (on local machine)
Derby 10.2 (db-derby-10.10.2.0-bin)

Urgency:
Normal
Issue & fix info:

Newcomer, Repro attached
Bug behavior facts:

Crash

Description

Hi,

I'm really struggling with this.

I'm trying to create a connection to Oracle using a derby style table function. I am using the thin driver (have tried both ojdbc6 and ojdbc7) and have even tried the TNS version of JDBC url but to no avail.

The jdbc URL I am using is:

jdbc:oracle:thin:@localhost:1521:orcl

Driver class is:

oracle.jdbc.driver.OracleDriver

------------ DATABASE CREATION SCRIPT ------------------------------

create schema apollobi authorization apollobi;

------------------
– INITIALISE JARS
------------------

– Clear the database classpath so it is possible to remove and install the jar files.

call syscs_util.syscs_set_database_property(
'derby.database.classpath',
'');

– Remove Jars

call sqlj.remove_jar('apollobi.ojdbc',0);
call sqlj.remove_jar('apollobi.ojdbc',0);

call sqlj.remove_jar('apollobi.commonslogging',0);
call sqlj.remove_jar('apollobi.hivejdbc',0);
call sqlj.remove_jar('apollobi.hivemetastore',0);
call sqlj.remove_jar('apollobi.hiveservice',0);
call sqlj.remove_jar('apollobi.libfb303',0);
call sqlj.remove_jar('apollobi.libthrift',0);
call sqlj.remove_jar('apollobi.log4j',0);
call sqlj.remove_jar('apollobi.slf4j',0);
call sqlj.remove_jar('apollobi.slf4jlog4j',0);

call sqlj.remove_jar('apollobi.ApolloBIDerby',0);

– Install Jars

– Oracle JDBC Driver

call sqlj.install_jar('D:\ApolloBI\oracle\ojdbc6.jar','apollobi.ojdbc',0);
--call sqlj.install_jar('D:\ApolloBI\oracle\ojdbc7.jar','apollobi.ojdbc',0);

– Cloudera Hive Driver Jars

call sqlj.install_jar('D:\ApolloBI\cloudera\impala-jdbc-driver\impala-jdbc-0.5-2\commons-logging-1.0.4.jar','apollobi.commonslogging',0);
call sqlj.install_jar('D:\ApolloBI\cloudera\impala-jdbc-driver\impala-jdbc-0.5-2\hive-jdbc-0.10.0-cdh4.2.0.jar','apollobi.hivejdbc',0);
call sqlj.install_jar('D:\ApolloBI\cloudera\impala-jdbc-driver\impala-jdbc-0.5-2\hive-metastore-0.10.0-cdh4.2.0.jar','apollobi.hivemetastore',0);
call sqlj.install_jar('D:\ApolloBI\cloudera\impala-jdbc-driver\impala-jdbc-0.5-2\hive-service-0.10.0-cdh4.2.0.jar','apollobi.hiveservice',0);
call sqlj.install_jar('D:\ApolloBI\cloudera\impala-jdbc-driver\impala-jdbc-0.5-2\libfb303-0.9.0.jar','apollobi.libfb303',0);
call sqlj.install_jar('D:\ApolloBI\cloudera\impala-jdbc-driver\impala-jdbc-0.5-2\libthrift-0.9.0.jar','apollobi.libthrift',0);
call sqlj.install_jar('D:\ApolloBI\cloudera\impala-jdbc-driver\impala-jdbc-0.5-2\log4j-1.2.16.jar','apollobi.log4j',0);
call sqlj.install_jar('D:\ApolloBI\cloudera\impala-jdbc-driver\impala-jdbc-0.5-2\slf4j-api-1.6.4.jar','apollobi.slf4j',0);
call sqlj.install_jar('D:\ApolloBI\cloudera\impala-jdbc-driver\impala-jdbc-0.5-2\slf4j-log4j12-1.6.1.jar','apollobi.slf4jlog4j',0);

call sqlj.install_jar('D:\ApolloBI\apollobi\derby\jars\ApolloBIDerby.jar','apollobi.ApolloBIDerby',0);

– Add the jars to the database classpath.

call syscs_util.syscs_set_database_property(
'derby.database.classpath',
'apollobi.ojdbc:apollobi.commonslogging:apollobi.hivejdbc:apollobi.hivemetastore:apollobi.hiveservice:apollobi.libfb303:apollobi.libthrift:apollobi.log4j:apollobi.slf4j:apollobi.slf4jlog4j:apollobi.ApolloBIDerby');

------------------------
– APPLICATION FUNCTIONS
------------------------

drop function Q2Toracle;

create function Q2Toracle(s varchar(1000))
returns table
(
employeeid int,
firstName varchar(50),
lastName varchar(50)
)
language java
parameter style DERBY_JDBC_RESULT_SET
no sql
external name 'apollo.bi.derby.StaticTableFunctions.Q2Toracle';

---------------- APPLICATION JAVA CLASS StaticTableFunctions -------------------------

package apollo.bi.derby;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.log4j.BasicConfigurator;
import org.apache.log4j.Logger;

public class StaticTableFunctions {

// Initialise log4j

static Logger logger = Logger.getLogger(StaticTableFunctions.class);

// Config

static String defaultJDBCConnStr = "jdbc:default:connection";

static String impalaJDBCDriverClassName = "org.apache.hive.jdbc.HiveDriver";
static String impalaJDBCConnStr = "jdbc:hive2://192.168.56.1:21050/;auth=noSasl";

// Can't get this driver to work.
static String oracleJDBCDriverClassName = "oracle.jdbc.driver.OracleDriver";
static String oracleJDBCConnStr = "jdbc:oracle:thin:@localhost:1521:orcl";

// Can't get this driver to work.
// static String oracleJDBCDriverClassName = "oracle.jdbc.driver.OracleDriver";
// static String oracleJDBCConnStr = "jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl.config)))";

// The following driver requires a local ODBC datasource to have been defined.
// In windows, this can be defined using the ODBC Datasource Administrator.
// static String oracleJDBCDriverClassName = "sun.jdbc.odbc.JdbcOdbcDriver"; // JDBC=ODBC Bridge Driver
// static String oracleJDBCConnStr = "jdbc:odbc:OracleDSPRJ"; // OracleDSPRJ is the name of the ODBC connection defined locally
static String oracleUserID = "prj";
static String oraclePassword = "prj";

static Properties oracleConnectionProperties = new java.util.Properties();

static

{ oracleConnectionProperties.put("user", oracleUserID); oracleConnectionProperties.put("password", oraclePassword); }

private static Connection getDefaultConnection() throws SQLException

{ return DriverManager.getConnection(defaultJDBCConnStr); }

private static Connection getImpalaConnection() throws SQLException, ClassNotFoundException

{ Class.forName(impalaJDBCDriverClassName); return DriverManager.getConnection(impalaJDBCConnStr); }

private static Connection getOracleConnection() throws SQLException, ClassNotFoundException

{ Class.forName(oracleJDBCDriverClassName); return DriverManager.getConnection(oracleJDBCConnStr, oracleConnectionProperties); }

public static ResultSet Q2Tdefault(String sqlQry) throws SQLException

{ return getDefaultConnection().prepareStatement(sqlQry).executeQuery(); }

public static ResultSet Q2Timpala(String sqlQry) throws SQLException, ClassNotFoundException

{ return getImpalaConnection().prepareStatement(sqlQry).executeQuery(); }

public static ResultSet Q2Toracle(String sqlQry) throws SQLException, ClassNotFoundException

{ return getOracleConnection().prepareStatement(sqlQry).executeQuery(); }

public static void main(String[] args) throws SQLException,
ClassNotFoundException

{ // log4j - Set up a simple configuration that logs on the console. BasicConfigurator.configure(); }

}

---- CLASS THAT I RUN (FROM ECLIPSE) TO TEST ------

package apollo.bi.derby;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class ClientServices {

// Config

static String derbyJDBCConnStr = "jdbc:derby://localhost:1528/D:/ApolloBI/apollobi/derby/apollobi";
static String derbyUserID = "apollobi";
static String derbyPassword = "apollobi";

static Properties derbyConnectionProperties = new java.util.Properties();

static

{ derbyConnectionProperties.put("user", derbyUserID); derbyConnectionProperties.put("password", derbyPassword); }

private static Connection getDerbyConnection() throws SQLException

{ return DriverManager.getConnection(derbyJDBCConnStr, derbyConnectionProperties); }

// util

public static String rsToHtml(ResultSet rs) throws SQLException{

StringBuffer sb = new StringBuffer();

int colCount = rs.getMetaData().getColumnCount();

sb.append("<table>");

while(rs.next()) {

sb.append("<tr>");
for (int i=1; i<=colCount; i++)

{ sb.append("<td>").append(rs.getObject(i).toString()).append("</td>"); }
sb.append("</tr>");

}

sb.append("</table>");

return sb.toString();

}

public static String displayRs(ResultSet rs) throws SQLException{

StringBuffer sb = new StringBuffer();

int colCount = rs.getMetaData().getColumnCount();

sb.append("<table>\n");

while(rs.next()) {

sb.append("<tr>");
for (int i=1; i<=colCount; i++) { sb.append("<td>").append(rs.getObject(i).toString()).append("</td>"); }

sb.append("</tr>\n");

}

sb.append("</table>\n");

return sb.toString();

}

// public static String qryToHtml(String qryStr) throws SQLException

{ // return rsToHtml(readDerby(qryStr)); // }

/**

@param args
@throws SQLException
*/
public static void main(String[] args) throws SQLException { System.out.println("Establishing a connection ...."); Connection conn = getDerbyConnection(); System.out.println("Connected."); // ResultSet rs = conn.prepareStatement("select * from table (Q2Toracle('with datum as (select employeeid, firstname, lastname from employeetable where rownum <= 100) select d1.* from datum d1, datum d2')) t1 join table (Q2Toracle('select employeeid, firstname, lastname from employeetable')) t2 on t2.employeeid = t1.employeeid").executeQuery(); ResultSet rs = conn.prepareStatement("select * from table (Q2Toracle('select employeeid, firstname, lastname from employeetable')) t1").executeQuery(); // ResultSet rs = conn.prepareStatement("select * from oracledataset").executeQuery(); System.out.println(displayRs(rs)); // CallableStatement cs = conn.prepareCall("create table "); // cs.execute(); }

}

-------------- ERROR I GET -------------------

Establishing a connection ....
Connected.
Exception in thread "main" java.sql.SQLTransactionRollbackException: The exception 'java.lang.ArrayIndexOutOfBoundsException: -1' was thrown while evaluating an expression.
at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
at org.apache.derby.client.am.PreparedStatement.executeQuery(Unknown Source)
at apollo.bi.derby.ClientServices.main(ClientServices.java:104)
Caused by: org.apache.derby.client.am.SqlException: The exception 'java.lang.ArrayIndexOutOfBoundsException: -1' was thrown while evaluating an expression.
at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
at org.apache.derby.client.am.Statement.completeOpenQuery(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.parseOpenQueryFailure(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.parseOPNQRYreply(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.readOpenQuery(Unknown Source)
at org.apache.derby.client.net.StatementReply.readOpenQuery(Unknown Source)
at org.apache.derby.client.net.NetStatement.readOpenQuery_(Unknown Source)
at org.apache.derby.client.am.Statement.readOpenQuery(Unknown Source)
at org.apache.derby.client.am.PreparedStatement.flowExecute(Unknown Source)
at org.apache.derby.client.am.PreparedStatement.executeQueryX(Unknown Source)
... 2 more
Caused by: org.apache.derby.client.am.SqlException: Java exception: '-1: java.lang.ArrayIndexOutOfBoundsException'.
... 12 more

Array Index out of bounds when accessing Oracle through the thin driver using a derby table function.

Details

Description

Attachments

Attachments

Activity

People

Dates