Stored Procedure Channel Adapter Add Schema [INT-3348]

[spring-operator]

Emil Glatz opened INT-3348* and commented

I need to be able to specify the schema in a stored procedure channel adapter. For example:

<jdbc:stored-proc-outbound-channel-adapter data-source="dataSource" channel="splitCjisJobs"
  stored-procedure-name="PRC_GET_CJIS_DATA" auto-startup="true" schema="SCHEMA">
  <jdbc:parameter name="AUDIT_ID" expression="payload"/>
</jdbc:stored-proc-outbound-channel-adapter>

---

Reference URL: https://stackoverflow.com/questions/44787659/how-to-specify-schema-name-in-int-jdbcstored-proc-outbound-gateway

[spring-operator]

Oliver Drotbohm commented

How does that relate to Spring Data JPA?

[spring-operator]

Artem Bilan commented

Can you explain, please, your use-case.

The stored-procedure-name supports property-paceholder and there is stored-procedure-name-expression attribute to determine prcedureName (and even with schema) at runtime.

[spring-operator]

Emil Glatz commented

We want to be able to access stored procedures in our database with the caveat that the user accounts that actually own the schemas are not what our web application uses to access the database. In order to do this, we current have to setup public synonyms, but would like to access stored procedures with a non-schema owner and no public synonym.

Current code:

<jdbc:stored-proc-outbound-channel-adapter data-source="dataSource" channel="splitCjisJobs"
  stored-procedure-name="PRC_GET_CJIS_DATA" auto-startup="true">
  <jdbc:parameter name="AUDIT_ID" expression="payload"/>
</jdbc:stored-proc-outbound-channel-adapter>

Desired code:

<jdbc:stored-proc-outbound-channel-adapter data-source="dataSource" channel="splitCjisJobs"
  stored-procedure-name="PRC_GET_CJIS_DATA" auto-startup="true" schema="SCHEMA">
  <jdbc:parameter name="AUDIT_ID" expression="payload"/>
</jdbc:stored-proc-outbound-channel-adapter>

I am sorry I do not understand how a property placeholder, or a stored procedure name expression helps solve this.

[spring-operator]

Artem Bilan commented

Actually, this works as is:

stored-procedure-name="SCHEMA.PRC_GET_CJIS_DATA"

Regarding property-paceholder I meant this:

stored-procedure-name="${user.schema}.PRC_GET_CJIS_DATA"

where user.schema comes from:

<context:property-placeholder location="classpath*:app.properties"/>

http://docs.spring.io/spring/docs/current/spring-framework-reference/html/beans.html#beans-factory-placeholderconfigurer.

[spring-operator]

Emil Glatz commented

Ok thank you. I must be experiencing some other configuration issue and will need to do more research on my end. In some of our environments the incorrect number of arguments, none, are used in the call to the stored procedure and creating a public synonym fixed that.

[spring-operator]

Artem Bilan commented

Sam Brannen, wouldn't you mind to help here a bit?

The Spring Integration StoredProcExecutor uses SimpleJdbcCall on the background. We have only one argument to configure - procedureName. However the last one has schemaName and catalogName. Looks like providing these three parameters makes sence in case of automatic determination the procedure signature with its parameters and their types - accessCallParameterMetaData = true.

However I see it doesn't work, if we provide procedure "path" as single line - CATALOG.SCHEMA.PROCEDURE.

How do you think will it work, if we split our procedureName by dot to those three parts and populate them to the SimpleJdbcCall?

I have an expiriance on the matter only with Oracle and can say that it will work. But I'm not sure for other vendors. Especially I'm afraid for MS SQL Server. My short expiriance with that looks like as it is possible, but it doesn't support package concept, however there is some object to refer to procedures, e.g. stored-procedure-name="dbo.procedure".

WDYT?

Or it won't work at all, because we might need to provide the CATALOG, but don't want SCHEMA or vise versa. My concern here just because we have stored-procedure-name-expression and I don't want to overburden it with expression pairs for CATALOG and SCHEMA attributes, respectively.

Thanks in advance

[spring-operator]

Artem Bilan commented

There is more generic robust solution like jdbc-call-operations reference, where end-user is free to configure target SimpleJdbcCall by the requirements and environment.