Use of DatabaseMetadata#getProcedures (and functions) should take search string escape into account

[bashtanov]

All versions including master.
As far as I could see, SimpleJdbcCall calls java.sql.databaseMetaData.getProcedures with just procedure and schema names, not with patterns for them to match. This results in problems with procedures having underscores in their names:

            // create some functions
            JdbcTemplate jt = new JdbcTemplate(dataSource);
            for (String procName: ImmutableList.of("just_a_procedure_name_with_underscores", "just_a_procedure_name_with2underscores")){
                jt.execute("create or replace function " + procName + "() returns int as $$ select 1; $$ language sql");
            }

            // call one of them
            jt.execute("");
            SimpleJdbcCall sjc = new SimpleJdbcCall(jt);
            sjc.withProcedureName("just_a_procedure_name_with_underscores").execute();

org.springframework.dao.InvalidDataAccessApiUsageException: Unable to determine the correct call signature - multiple procedures/functions/signatures for just_a_procedure_name_with_underscores found [null.public.just_a_procedure_name_with2underscores, null.public.just_a_procedure_name_with_underscores]

Passing underscores backslash-escaped to withProcedure doesn't help either: the correct function gets found but not called as its name is different from the pattern to find it.

[bashtanov]

I should have said I used it with pgjdbc

[snicoll]

with just procedure and schema names, not with patterns for them to match

@bashtanov I can reproduce but I don't think there's anything we can do about this. We call java.sql.DatabaseMetaData#getFunctions with the function you gave us, i.e. just_a_procedure_name_with_underscores and Postgres decides to return two hits for that name.

What do you mean by "not with patterns for them to match"?

[bashtanov]

@snicoll

We call java.sql.DatabaseMetaData#getFunctions with the function you gave us

And this is wrong, because, according to its documentation, java.sql.DatabaseMetaData#getFunctions accepts not the function name, but a pattern to look for functions. If you want to use this API to get info on a function with name just_a_procedure_name_with_underscores you should pass just@_a@_procedure@_name@_with@_underscores, where @ is what is returned by java.sql.DatabaseMetaData#getSearchStringEscape (in reality I would imagine it to be \, not @). Does it sound reasonable?

[snicoll]

@bashtanov well, this is embarrassing. I did notice the pattern but missed what that actually means and the use of the escape character. Thanks for getting back to us, we'll get this fixed.