You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Peter Hayes (JIRA)" <ji...@apache.org> on 2014/08/23 15:52:11 UTC
[jira] [Commented] (DERBY-6678) Array Index out of bounds when
accessing Oracle through the thin driver using a derby table function.
[ https://issues.apache.org/jira/browse/DERBY-6678?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14108000#comment-14108000 ]
Peter Hayes commented on DERBY-6678:
------------------------------------
Rick,
Sorry about the code mismatch, I had continued developing so that's why the method names differed.
I think your right about this being a problem with the Oracle driver.
I have reverted to the Oracle Type 2 Driver (an OCI driver which requires me to install client services) and I'm not experiencing any problems with it.
I am invoking in exactly the same way with this and the Impala drivers as I was with the Oracle Type 3 driver and none of them are throwing any kind of errors so everything seems stable at the moment.
This ticket can be closed, but if you want me to post any more information about the circumstances under which this error occurs then let me know.
Peter
> 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
> Priority: Blocker
> Attachments: derby.log
>
>
> 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)