Re: Not able to execute function in a UDF

[Rick Hillegas]

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

Re: Updating several consecutive fields in a record

[Bob M]

Thank you Bryan

Simple and elegant :)

Bob M



--
View this message in context: 
http://apache-database.10148.n7.nabble.com/Updating-several-consecutive-fields-in-a-record-tp146385p146387.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Updating several consecutive fields in a record

[Bryan Pendleton]

psUpdate = conn.prepareStatement("UPDATE  SET PROFIT_LOSS = ? WHERE




Now, I wish to update seven consecutive fields in the latest derby record


You can list multiple columns in the UPDATE statement:

UPDATE my-table SET col-1=?, col-2=?, col-3=? WHERE ...

And then use multiple "setInt", "setString", etc. statements
to associate the various column values to be used for the
placeholders.

bryan

Updating several consecutive fields in a record

[Bob M]

Hi

So far, I have only needed to update a single field in the latest derby
record with code as follows:-

// retrieve the latest trade record
rs = s.executeQuery("SELECT * FROM  ORDER BY TRADE_NO DESC NULLS LAST
FETCH NEXT 1 ROW ONLY");
rs.next(); 
String Date3 = rs.getString("Trading_Date");
int Time3 = rs.getInt("Trading_Time");
// and now update the latest trade record
s.setCursorName("MYCURSOR");
rs = s.executeQuery("SELECT PROFIT_LOSS FROM  WHERE Trading_Date = '"
+ Date3 + "' AND Trading_Time = " + Time3 
+ " FOR UPDATE");
rs.next();
psUpdate = conn.prepareStatement("UPDATE  SET PROFIT_LOSS = ? WHERE
CURRENT OF MYCURSOR");
statements.add(psUpdate);
psUpdate.setDouble(1, profit);
psUpdate.executeUpdate();
} // end of updating the latest trade record

Now, I wish to update seven consecutive fields in the latest derby record

What is the best way to recode the above section of code?
Many thanks
Bob M



--
View this message in context: 
http://apache-database.10148.n7.nabble.com/Updating-several-consecutive-fields-in-a-record-tp146385.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.