Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Stored procedures are resolved against the public PostgreSQL schema [SPR-16880] #21419

Closed
spring-projects-issues opened this issue May 29, 2018 · 2 comments
Assignees
Labels
in: data status: invalid

Comments

@spring-projects-issues
Copy link
Collaborator

@spring-projects-issues spring-projects-issues commented May 29, 2018

Marco R. opened SPR-16880 and commented

I'm connecting a Spring Integration 5.0.5 web application to a PostgreSql 10 instance using an Apache Commons DBCP2 BasicDataSource bean, but the framework isn't finding my stored procedures because it tries to resolve their names against the public schema, instead of the right one.

I'm not sure where the problem is located, I tried to follow the code within the debugger, but didn't came very far.

Given the following PostgreSQL server:

<instance>
|
`-- databases
    |
    `-- "postgres"
        |
        `-- schemas
            |
            `-- "information_schema"
            `-- "pg_catalog"
            `-- "public"
            `-- "target" *

*: This is the schema backing the application.

Is the following Spring configuration complete to support my scenario or am I missing something?

 
<bean id="dbDataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close" >
    <property name="driverClassName" value="org.postgresql.Driver"/>
    <property name="url" value="jdbc:postgresql://localhost:5432/postgres?currentSchema=target"/>
    <property name="username" value="postgres"/>
    <property name="password" value="root"/>
    <property name="defaultCatalog" value="postgres" />   
</bean>
 

If everything is fine here, then I've seen that in GenericCallMetaDataProvider#processProcedureColumns() both catalogName and schemaName are null.

The only way I found to have the correct values there is by editing CallMetaDataProviderFactory#createMetaDataProvider() like this:

else if ("PostgreSQL".equals(databaseProductName)) {
					provider = new PostgresCallMetaDataProvider((databaseMetaData));
					context.setSchemaName("target");
					context.setCatalogName("postgres");
				}

Of course that's not the right place / way to set those values, it just made my application work.


No further details from SPR-16880

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented May 29, 2018

Juergen Hoeller commented

The right place to configure those are the catalogName and schemaName bean properties on the corresponding SimpleJdbcCall instance, translating to the underlying CallMetaDataContext. It's a nuisance to have to repeat those there but it seems that's necessary in your scenario.

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented May 30, 2018

Marco R. commented

Is there another place / any higher point in the control flow where they can be specified? I'm using Spring Integration endpoints, so writing no code but configuring beans from the XML file and I don't have direct access to any SimpleJdbcCall instance (unless I write custom service activators, but I'd like to avoid that if possible).

@spring-projects-issues spring-projects-issues added type: bug status: invalid in: data labels Jan 11, 2019
@spring-projects-issues spring-projects-issues removed the type: bug label Jan 12, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
in: data status: invalid
Projects
None yet
Development

No branches or pull requests

2 participants