Hi, I have defined a user-defined function in a Derby Database that reads as follows: CREATE FUNCTION invoice_total( invoiceNumber int )RETURNS DECIMALLANGUAGE JAVAPARAMETER STYLE JAVAREADS SQL DATA EXTERNAL NAME 'FXBilling.DBJAR.DBMethods.getInvoiceTotal' On executing this function from the Services tab of the Netbeans IDE, the following error is thrown: Error code 20000, SQL state XCL16: ResultSet not open. Operation 'next' not permitted. Verify that autocommit is off.
And while executing the same UDF from my Java program, I get the following error: Caused by: ERROR 38000: The exception 'java.sql.SQLException: The external routine is not allowed to execute SQL statements.' was thrown while evaluating an expression. at org.apache.derby.iapi.error.StandardException.newException(Unknown Source) at org.apache.derby.impl.jdbc.SQLExceptionFactory.wrapArgsForTransportAcrossDRDA(Unknown Source) ... 86 moreCaused by: java.sql.SQLException: The external routine is not allowed to execute SQL statements. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source) at org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(Unknown Source) at FXBilling.DBJAR.DBMethods.getItemsTotal(DBMethods.java:108) at FXBilling.DBJAR.DBMethods.getInvoiceTotal(DBMethods.java:75) at org.apache.derby.exe.acf81e0010x0155xbe62xb6e5x0000029c03b83.e3(Unknown Source) at org.apache.derby.impl.services.reflect.DirectCall.invoke(Unknown Source) at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.doProjection(Unknown Source) at org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(Unknown Source) at org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.getNextRow(Unknown Source) ... 78 moreCaused by: ERROR 38001: The external routine is not allowed to execute SQL statements. at org.apache.derby.iapi.error.StandardException.newException(Unknown Source) at org.apache.derby.iapi.error.StandardException.newException(Unknown Source) at org.apache.derby.impl.sql.conn.GenericAuthorizer.externalRoutineException(Unknown Source) at org.apache.derby.impl.sql.conn.GenericAuthorizer.authorize(Unknown Source) at org.apache.derby.impl.sql.execute.CursorActivation.decorateResultSet(Unknown Source) at org.apache.derby.impl.sql.execute.BaseActivation.execute(Unknown Source) at org.apache.derby.impl.sql.GenericActivationHolder.execute(Unknown Source) at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source) at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source) ... 88 more I am baffled as even though the UDF contains the "Reads SQL Data" clause, Derby is now allowing it to execute SQL. Could you kindly help resolve this issue. Thanks,Dinesh Bajaj
