db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Peter Hayes (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6678) Array Index out of bounds when accessing Oracle through the thin driver using a derby table function.
Date Fri, 25 Jul 2014 21:12:40 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6678?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14074923#comment-14074923
] 

Peter Hayes commented on DERBY-6678:
------------------------------------

Please note that I don't think this is a problem with the Oracle driver (which is very well
tested) as I can connect with it and issue queries from eclipse, just not from within the
derby db.



> 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)

Mime
View raw message