db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dinesh Bajaj <dinesh.ba...@ymail.com>
Subject Re: Not able to execute function in a UDF
Date Thu, 07 Jul 2016 08:40:10 GMT
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



  
Mime
View raw message