1

I'm using H2 DB v1.4.182 for my test environment. It acts as a substitute in place of Oracle, which is the production vendor. I have a few aliases I create using a script in my test environment:

CREATE ALIAS IF NOT EXISTS REGEXP_LIKE as $$ boolean regexpLike(String s, String p) { return java.util.regex.Pattern.compile(p).matcher(s).find(); } $$;

CREATE ALIAS IF NOT EXISTS TO_NUMBER as $$ long toNumber(String s) { return Long.valueOf(s); } $$;

CREATE ALIAS if NOT EXISTS fn_val_check3 for "fakedata.testutil.TestUtil.fn_val_check";

The first two aliases show up in the INFORMATION_SCHEMA.FUNCTION_ALIASES table, the last one (fn_val_check3) doesn't. 

SELECT ALIAS_NAME FROM INFORMATION_SCHEMA.FUNCTION_ALIASES;

Gives me two rows

ALIAS_NAME  
TO_NUMBER
REGEXP_LIKE

I verified that H2 has the other alias somewhere by running the CREATE ALIAS ... directly in the console and it says 

CREATE ALIAS fn_val_check3 for "fakedata.testutil.TestUtil.fn_val_check";
Function alias "FN_VAL_CHECK3" already exists; SQL statement: CREATE ALIAS fn_val_check3 for "fakedata.testutil.TestUtil.fn_val_check" [90076-182] 90076/90076 (Help)

I'm using a validation component to verify the function exists in the database, which is why I'm wondering. Am I missing something or is this a known thing?

MadConan
  • 3,749
  • 1
  • 16
  • 27
  • Does it work if you call `fn_val_check3` (is the class in the classpath and s on)? – Thomas Mueller Mar 10 '15 at 14:08
  • If you mean does the function get called when the code runs, then yes. Everything works great. I just can't see the alias name in the FUNCTION_ALIASES table. – MadConan Mar 10 '15 at 14:24
  • @ThomasMueller My current work around is to use `select 1 from dual` for my test sql to validate the function exists, and that gets me by. – MadConan Mar 11 '15 at 12:02
  • I can't reproduce the problem. I tried this, and the method (actually 2 methods) showed up. Could you try this? `CREATE ALIAS IF NOT EXISTS test_2 for "java.lang.Long.parseLong";` – Thomas Mueller Mar 13 '15 at 13:47
  • Yes, that shows up as well. About the only thing I can think of is that the class the alias refers to isn't available in the console. I'm trying to launch the console with the proper classpath, but can't seem to make the class available (always get CNF). Querying for the name WORKS during runtime. – MadConan Mar 13 '15 at 16:35

0 Answers0