Money.java and Money.sql
==========================================================================
Money.java:

//  Copyright  2003  -  FirstSQL, Inc.

import java.math.*;
import java.io.*;
import java.sql.*;
import COM.FirstSQL.Dbcp.Database;

// Money.java - FirstSQL/J Example Database Class

public class Money implements Serializable
{
  protected String currency;
  protected BigDecimal amount;
  public Money(BigDecimal a, String c)
  {
    amount = a;
    currency = c;
  }
  public Money(double a, String c)
  {
    this(new BigDecimal(a), c);
  }
  public Money(String a, String c)
  {
    this(new BigDecimal(a), c);
  }
  public String getCurrency()
  {
    return currency;
  }
  public double doubleValue()
  {
    return amount.doubleValue();
  }
  public BigDecimal decValue()
  {
    return amount;
  }

  public String toString()
  {
    return amount.setScale(2, BigDecimal.ROUND_HALF_UP).toString();
  }

  // change a Money object to a new currency
  public static Money convert(Money source, String currency)
  {
    Money target = null;
    // Database is builtin class for retrieving an internal DBMS connection
    Connection conn = Database.getConnection();
    if (source != null && conn != null)
    {
      PreparedStatement stnt = null;
      ResultSet rs = null;
      try
      {

        stnt = conn.prepareStatement("SELECT " +
               "? * (SELECT usdollar FROM money WHERE code = ?) / usdollar " +
               "FROM money WHERE code = ?");

        stnt.setBigDecimal(1, source.decValue());
        stnt.setString(2, source.getCurrency());
        stnt.setString(3, currency);
        rs = stnt.executeQuery();
        if (rs.next())
          target = new Money(rs.getBigDecimal(1), currency);
      }
      catch (SQLException ex)
      {
      }
      finally
      {
        if (stnt != null)
          try
          {
            if (rs != null)
              try
              {
                rs.close();
              }
              catch (SQLException ex)
              {
              }
            stnt.close();
          }
          catch (SQLException ex)
          {
          }
      }
    }
    return target;
  }
}

==========================================================================
money.sql:

create table money
(
  code varchar(4) primary key,
  country varchar(32),
  name varchar(16),
  usdollar double
)
go
insert into money Values('USD', 'USA', 'Dollar', 1.00)
go
insert into money Values('GBP', 'UK', 'Pound', 1.47)
go
insert into money Values('Euro', 'European Union', 'Dollar', .95)
go
insert into money Values('CAD', 'Canada', 'Dollar', .65)
go
insert into money Values('AUD', 'Australia', 'Dollar', .57)
go
insert into money Values('JPY', 'Japan', 'Yen', .00798)
go

=========================================================================
FIRSTSQL/J STORED PROCEDURE EXAMPLE

This is a note giving a real-world example of using Stored Procedures in
FirstSQL/J. For an example, we will use the Money class (Java code is
included below). The Money class is for storage of monetary amounts in
mixed currencies. It includes a static method - convert(), that is called
as a stored procedure. convert() converts a monetary amount from one
currency to another.

The Money class is placed in the FirstSQL/J catalog with CREATE CLASS:

  CREATE CLASS Money FROM 'Money'

Note: CREATE CLASS requires that the class file (Money.class) be in a
special location. For the Professional Edition, the default location for
Money.class is the directory where CREATE CLASS is run. For the Enterprise
Edition, the default location is the directory where the server is started
up.

An object of the Money class has two values - a decimal amount and a
currency type string.

The convert() method receives 2 parameters - a Money object and a currency
type string. It returns a new Money object for the specified currency with
the amount converted to the new currency:

  public static Money convert(Money source, String currency)

The convert() method uses a database table - money, to do the conversion.
The money table contains a row for each currency type. Each row has the
value in US Dollars of a unit in the specified currency. The SQL commands
for creating the money table and adding some example rows are included
below.

The SQL command for calling the convert() method as a stored procedure is:

  {?=call Money.convert(?,?)}

The Java code to use the convert() stored procedure:

// value to be converted
Money source = new Money(1445.36, "USD");

CallableStatement call = conn.prepareCall("{?=call Money.convert(?,?)}");
// register return value
call.registerOutParameter(1, Types.OTHER);
// set arguments to convert()
call.setObject(2, source);
call.setString(3, "Euro");
// execute call
call.execute();
// retrieve result
Money target = (Money) call.getObject(1);