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 "Rick Hillegas (JIRA)" <ji...@apache.org> on 2014/07/28 14:34:39 UTC
[jira] [Updated] (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:all-tabpanel ]
Rick Hillegas updated DERBY-6678:
---------------------------------
Priority: Blocker (was: Major)
Urgency: Normal (was: Blocker)
> 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
>
> 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)