H2 database - How to access function created in another alias

I am trying to replicate our database in H2 for testing purpose. I would like to create 2 functions and one of them depends on the other. The following codes are for oracle :

GET_FULL_ADDRESS:

CREATE OR REPLACE FUNCTION "GET_FULL_ADDRESS"
(
    flat IN VARCHAR2,
    street IN VARCHAR2,
    district IN VARCHAR2,
    country_code IN VARCHAR2
)
RETURN VARCHAR2
IS
BEGIN
    RETURN flat || ', ' || street || ', ' || district || ', ' || CONVERT_COUNTRY_CODE(country_code);
END;

CONVERT_COUNTRY_CODE:

CREATE OR REPLACE FUNCTION "CONVERT_COUNTRY_CODE"
(
    country_code IN VARCHAR2
)
RETURN VARCHAR2
IS
BEGIN
    CASE country_code
        WHEN 'UK' THEN RETURN 'United Kingdom';
        WHEN 'US' THEN RETURN 'United States';
        -- Skipping the rest of the cases...
        ELSE RETURN country_code;
    END CASE;
END;

I tried to convert them for H2 with ALIAS

GET_FULL_ADDRESS:

CREATE ALIAS GET_FULL_ADDRESS AS '
    String getFullAddress(String flat, String street, String district, String country_code) {
        return flat + ", " + street + ", " + district + ", " + convertCountryCode(country_code);
    }
';

CONVERT_COUNTRY_CODE:

CREATE ALIAS CONVERT_COUNTRY_CODE AS '
    String convertCountryCode(String country_code) {
        switch(country_code) {
            case "UK": return "United Kingdom";
            case "US": return "United States";
            // Skipping the rest of the cases...
            default: return country_code;
        }
    }
';

The above codes produce a "cannot find symbol" error

    convertCountryCode(country_code);
    ^
symbol:   method convertCountryCode(String)
location: class GET_FULL_ADDRESS

If I put the 2 functions into a single CREATE ALIAS statement it works, but both functions are referenced in my views so I need separated CREATE ALIAS statement.

How can I call the function defined in other CREATE ALIAS statement?

1 answer

  • answered 2019-08-13 05:32 Evgenij Ryazanov

    There are two possible approaches.

    1. You can call another alias from the SQL code.
    CREATE ALIAS CONVERT_COUNTRY_CODE AS '
        String convertCountryCode(String country_code) {
            switch(country_code) {
                case "UK": return "United Kingdom";
                case "US": return "United States";
                // Skipping the rest of the cases...
                default: return country_code;
            }
        }
    ';
    
    CREATE ALIAS GET_FULL_ADDRESS AS '
        String getFullAddress(Connection conn, String flat, String street, String district, String country_code)
                throws SQLException {
            PreparedStatement prep = conn.prepareStatement("CALL CONVERT_COUNTRY_CODE(?)");
            prep.setString(1, country_code);
            ResultSet rs = prep.executeQuery();
            rs.next();
            return flat + ", " + street + ", " + district + ", " + rs.getString(1);
        }
    ';
    
    1. If you can add Java classes to the classpath (or classpath of H2 Server process if you use the client-server model), you can define both functions as a normal Java code and call another method directly.
    package test;
    
    public class CustomFunctions {
    
        public static String convertCountryCode(String country_code) {
            switch(country_code) {
                case "UK": return "United Kingdom";
                case "US": return "United States";
                // Skipping the rest of the cases...
                default: return country_code;
            }
        }
    
        public static String getFullAddress(String flat, String street, String district, String country_code) {
            return flat + ", " + street + ", " + district + ", " + convertCountryCode(country_code);
        }
    
    }
    
    CREATE ALIAS CONVERT_COUNTRY_CODE FOR "test.CustomFunctions.convertCountryCode";
    CREATE ALIAS GET_FULL_ADDRESS FOR "test.CustomFunctions.getFullAddress";