Peter Hayes created DERBY-6678:
----------------------------------
Summary: Array Index out of bounds when accessing Oracle through
the thin driver using a derby table function.
Key: DERBY-6678
URL: https://issues.apache.org/jira/browse/DERBY-6678
Project: Derby
Issue Type: Bug
Components: JDBC
Affects Versions: 10.10.2.0
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)
Reporter: Peter Hayes
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
--
This message was sent by Atlassian JIRA
(v6.2#6252)
