db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (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 Mon, 28 Jul 2014 12:32:38 GMT

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

Rick Hillegas commented on DERBY-6678:
--------------------------------------

Hi Peter,

I suspect that the real error is in the Derby server's derby.log file. Could you attach derby.log?
To get Derby to print out enough error information, you may need to play around with the Derby
server's setting of derby.stream.error.logSeverityLevel and derby.language.logStatementText.
See http://db.apache.org/derby/docs/10.10/ref/rrefproper26985.html and http://db.apache.org/derby/docs/10.10/ref/rrefproper43517.html

The following settings should work:

{noformat}
-Dderby.language.logStatementText=true
-Dderby.stream.error.logSeverityLevel=0
{noformat}

Thanks,
-Rick

> 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