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
