db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@gmail.com>
Subject Re: Not able to execute function in a UDF
Date Fri, 08 Jul 2016 01:33:52 GMT
On 7/7/16 1:40 AM, Dinesh Bajaj wrote:
> Hello Rick,
>
> Many thanks for your response.
>
> I can surely share the Java Code of the UDF. Here is the code:
>
> //************** START OF FUNCTION CODE *****************************
>
> public static BigDecimal getInvoiceTotal(int invoiceNumber)
>             throws SQLException {
>         Connection connection = 
> DriverManager.getConnection("jdbc:default:connection");
>         BigDecimal invoiceTotal = getItemsTotal(connection, 
> invoiceNumber);
>
>         String sql = "SELECT discount, additionalcharge FROM invoices "
>                 + "WHERE id = " + invoiceNumber;
>
>         try (Statement statement = connection.createStatement()) {
>             try (ResultSet result = statement.executeQuery(sql)) {
>                 if (result.next()) {
>                     BigDecimal amount = result.getBigDecimal(1); 
> //discount
>                     if (!result.wasNull()) {
>                         invoiceTotal = invoiceTotal.subtract(amount);
>                     }
>                     amount = result.getBigDecimal(2); // additional charge
>                     if (!result.wasNull()) {
>                         invoiceTotal = invoiceTotal.add(amount);
>                     }
>                 }
>
>             }
>         }
>         return invoiceTotal;
>
>     }
>
>     private static BigDecimal getItemsTotal(Connection connection,
>             int invoiceNumber) throws SQLException {
>         String sql = "SELECT rate * quantity FROM invoice_items WHERE "
>                 + "invoice_id = " + invoiceNumber;
>
>         BigDecimal total = BigDecimal.ZERO;
>         try (Statement statement = connection.createStatement()) {
>             try (ResultSet result = statement.executeQuery(sql)) {
>                 BigDecimal amount = null;
>                 while (result.next()) {
>                     amount = result.getBigDecimal(1).setScale(2,
>                             RoundingMode.HALF_UP);
>                     total = total.add(amount);
>                 }
>             }
>         }
>         return total;
>     }
>
> //**************END OF FUNCTION CODE *****************************
>
> Should you require any other info, kindly let me know.
>
> Thanks,
> Dinesh
>
>
> On Thursday, 7 July 2016 7:34 AM, Rick Hillegas 
> <rick.hillegas@gmail.com> wrote:
>
>
> On 7/5/16 9:11 PM, Dinesh Bajaj wrote:
> > CREATE FUNCTION invoice_total
> > ( invoiceNumber int )
> > RETURNS DECIMAL
> > LANGUAGE JAVA
> > PARAMETER STYLE JAVA
> > READS SQL DATA
> > EXTERNAL NAME 'FXBilling.DBJAR.DBMethods.getInvoiceTotal'
> Hi Dinesh,
>
> It's hard to say what's going on. Could you share the Java code which
> implements getInvoiceTotal()? The following code works for me (I changed
> your function declaration to return a DECIMAL with a non-zero scale).
> Here is my version of the function...
>
> public class SampleUDF
> {
>   public static BigDecimal getInvoiceTotal(int invoiceNumber)
>     throws SQLException
>   {
>     Connection conn =
> DriverManager.getConnection("jdbc:default:connection");
>
>     try (PreparedStatement ps = conn.prepareStatement("select total
> from invoices where invoiceNumber = ?"))
>     {
>       ps.setInt(1, invoiceNumber);
>
>       try (ResultSet rs = ps.executeQuery())
>       {
>         if (rs.next())
>         {
>           return rs.getBigDecimal(1);
>         }
>         else { return null; }
>       }
>     }
>   }
> }
>
> ...and here is a script which invokes it...
>
> connect 'jdbc:derby:memory:db;create=true';
>
> create table invoices(invoiceNumber int primary key, total decimal(10,2));
> insert into invoices values (1, 100.30), (2, 200.60);
>
> CREATE FUNCTION invoice_total
> ( invoiceNumber int )
> RETURNS DECIMAL(10,2)
>
> LANGUAGE JAVA
> PARAMETER STYLE JAVA
> READS SQL DATA
>
> EXTERNAL NAME 'SampleUDF.getInvoiceTotal';
>
> values invoice_total(1);
>
> Hope this helps,
> -Rick
>
>
>
>
Hi Dinesh,

I'm afraid that I can't explain your results from the evidence you have 
provided. I successfully compiled the invoice_total() function. It 
executed correctly when I declared the function with the READS SQL DATA 
clause. However, when I changed that clause to be NO SQL, then I saw the 
error which you are seeing: "The external routine is not allowed to 
execute SQL statements."

Could you provide some additional information:

1) What version of Derby are you using?

2) What does dblook produce as the DDL for invoice_total()? You can run 
dblook as follows:

     java org.apache.derby.tools.dblook -d $connectionURL

where $connectionURL is your connection string, e.g., "jdbc:derby:db"

Thanks,
-Rick

Mime
View raw message