Support StoredProcedure OUT parameters for Oracle #5224

tnleeuw · 2023-08-09T08:33:36Z

For Oracle the type of OUT or INOUT parameters needs to be specified in the configuration of the StoredProcedureQuerySender.

This should be done in the list of Sender Parameters. Each output parameter needs to have the attribute mode="OUTPUT".

@alisihab:
When a function in Oracle should be called, the syntax of the query is:

{ ? = call TheFunction(?, ?, ?) }

The curly braces are important!

The first parameter in this query is considered the OUT parameter and should be specified as such. The parameter type should match that of the type in the database as close as possible. (Mapping to be documented still, for now see JdbcUtil#mapParameterTypeToSqlType).

For an example of calling a function done in code, see StoredProcedureQuerySenderTest#testCallFunction.

…UT parameter.

sonatype-lift · 2023-08-09T08:33:40Z

Sonatype Lift is retiring

Sonatype Lift will be retiring on Sep 12, 2023, with its analysis stopping on Aug 12, 2023. We understand that this news may come as a disappointment, and Sonatype is committed to helping you transition off it seamlessly. If you’d like to retain your data, please export your issues from the web console.
We are extremely grateful and thank you for your support over the years.

📖 Read about the impacts and timeline

sonatype-lift · 2023-08-09T09:17:20Z

core/src/main/java/nl/nn/adapterframework/jdbc/StoredProcedureQuerySender.java

+			} else {
+				parameterMetaData = null;
+			}
+			return getResult(new StoredProcedureResultWrapper((CallableStatement) statement, parameterMetaData, outputParameterDefs));


RESOURCE_LEAK: resource of type nl.nn.adapterframework.jdbc.StoredProcedureResultWrapper acquired by call to new() at line 212 is not released after line 212.

ℹ️ Expand to see all @sonatype-lift commands

You can reply with the following commands. For example, reply with @sonatype-lift ignoreall to leave out all findings.

Command Usage

@sonatype-lift ignore Leave out the above finding from this PR

@sonatype-lift ignoreall Leave out all the existing findings from this PR

@sonatype-lift exclude <file|issue|path|tool> Exclude specified file|issue|path|tool from Lift findings by updating your config.toml file

Note: When talking to LiftBot, you need to refresh the page to see its response.
_{Click here to add LiftBot to another repo.}

core/src/main/java/nl/nn/adapterframework/jdbc/StoredProcedureParamDef.java

nielsm5 · 2023-08-09T09:10:32Z

core/src/main/java/nl/nn/adapterframework/jdbc/StoredProcedureQuerySender.java

-			return getResult(new StoredProcedureResultWrapper((CallableStatement) statement, statement.getParameterMetaData(), outputParameterPositions));
+			ParameterMetaData parameterMetaData;
+			if (getDbmsSupport().canFetchStatementParameterMetaData()) {
+				parameterMetaData = statement.getParameterMetaData();


Is het niet 'gewoon' zo dat wanneer hier null uit komt het niet ondersteund wordt en anders wel? De canFetchStatementParameterMetaData lijkt zo overbodig?

Maar er komt geen null uit, er komt een ParameterMetaData uit die exceptions gooit als je probeert iets uit te vragen (SqlFeatureNotSupported of zoiets).
In plaats van een nieuwe feature-flag zou ik ook kunnen controleren op getDbmsSupport().getDbmsName().equals("Oracle") maar dat lijkt me ook niet erg netjes.

Is het niet 'gewoon' zo dat wanneer hier null uit komt het niet ondersteund wordt en anders wel? De canFetchStatementParameterMetaData lijkt zo overbodig?

Helaas heb ik deze nog steeds nodig want voor sommige databases is onze mapping van Parameter Types naar JDBC types niet goed genoeg, en zouden we meer ParameterTypes nodig hebben.

nielsm5 · 2023-08-09T09:18:44Z

core/src/test/java/nl/nn/adapterframework/jdbc/StoredProcedureQuerySenderTest.java

+		Parameter dummyParam = new Parameter("dummy", "0");
+		dummyParam.setType(Parameter.ParameterType.INTEGER);
+		dummyParam.configure();
+		sender.addParameter(dummyParam);
+		Parameter p1 = new Parameter("one", "1");
+		p1.setType(Parameter.ParameterType.INTEGER);
+		p1.configure();
+		sender.addParameter(p1);
+		Parameter p2 = new Parameter("two", "2");
+		p2.setType(Parameter.ParameterType.INTEGER);
+		p2.configure();
+		sender.addParameter(p2);


Waarom heb je hier nu 3 parameters?

De output is ook een parameter, de dummy-parameter als eerste. Omdat vanwege de syntax van Oracle die vooraan staat, moet die als Parameter worden gedefinieerd. Anders lukt het me niet om de andere 2 op hun plek te krijgen, en komen er exceptions dat niet alle parameters gevuld zijn.

Bonus: door nu het type te gebruiken van de Parameter hoeft het niet te worden gespecificeerd in het outputParameters attribuut.

(En deze test-case zit erin omdat Ali een Oracle Function heeft die vergelijkbaar is).

Dan is het handiger om altijd alle parameters (in als uit) op te geven, en dan een attribuut waar je de param namen op moet geven die output params zijn?

Dat zou ook kunnen ja, dat outputParameters de namen geeft ipv posities.

Ik zit nog even met 1 ding, en dat is de parameter-types die niet altijd goed mappen naar JDBC types. Als een stored procedure een CURSOR of REF_CURSOR terug geeft in een OUT-parameter, dan krijg je in die parameter een hele result-set.
Er is nu geen parameter type waarmee dat kan worden aangegeven.

Zorg voor later wellicht.

…ParamDef.java Co-authored-by: Niels <nielsmeijer@hotmail.com>

…this.

sonatype-lift · 2023-08-09T11:19:27Z

core/src/main/java/nl/nn/adapterframework/jdbc/StoredProcedureQuerySender.java

+		} else {
+			parameter = parameterList.findParameter(name);
+		}
+		return JdbcUtil.mapParameterTypeToSqlType(parameter.getType());


NULL_DEREFERENCE: object parameter last assigned on line 169 could be null and is dereferenced at line 171.

ℹ️ Expand to see all @sonatype-lift commands

You can reply with the following commands. For example, reply with @sonatype-lift ignoreall to leave out all findings.

Command Usage

@sonatype-lift ignore Leave out the above finding from this PR

@sonatype-lift ignoreall Leave out all the existing findings from this PR

@sonatype-lift exclude <file|issue|path|tool> Exclude specified file|issue|path|tool from Lift findings by updating your config.toml file

Note: When talking to LiftBot, you need to refresh the page to see its response.
_{Click here to add LiftBot to another repo.}

core/src/test/java/nl/nn/adapterframework/jdbc/StoredProcedureQuerySenderTest.java

… Param tags for that.

core/src/main/java/nl/nn/adapterframework/jdbc/StoredProcedureQuerySender.java

sonatype-lift · 2023-08-10T09:16:31Z

🛠 Lift Auto-fix

Some of the Lift findings in this PR can be automatically fixed. You can download and apply these changes in your local project directory of your branch to review the suggestions before committing.¹

# Download the patch
curl https://lift.sonatype.com/api/patch/github.com/ibissource/iaf/5224.diff -o lift-autofixes.diff

# Apply the patch with git
git apply lift-autofixes.diff

# Review the changes
git diff

Want it all in a single command? Open a terminal in your project's directory and copy and paste the following command:

curl https://lift.sonatype.com/api/patch/github.com/ibissource/iaf/5224.diff | git apply

Once you're satisfied, commit and push your changes in your project.

You can preview the patch by opening the patch URL in the browser. ↩

nielsm5 · 2023-08-10T11:11:38Z

core/src/test/java/nl/nn/adapterframework/jdbc/StoredProcedureQuerySenderTest.java

+		sender.addParameter(p1);
+		Parameter p2 = new Parameter("two", "2");
+		p2.setType(Parameter.ParameterType.INTEGER);
+		p2.configure();


configure zou de sender moeten doen :)

Some fixes to type conversions required for some output types.

commit bf96226 Author: Niels <nielsmeijer@hotmail.com> Date: Tue Sep 5 16:51:13 2023 +0200 Attempt to enable SonarCloud (frankframework#5332) commit 9556f5e Author: J. Koster <j.koster@gmx.com> Date: Tue Sep 5 16:49:23 2023 +0200 Use public FF repo by default, to resolve jfileserver-enterprise library (frankframework#5334) * This avoids the use of a mandatory profile setting commit 2e7adbe Author: Niels <nielsmeijer@hotmail.com> Date: Tue Sep 5 13:57:13 2023 +0200 ASPOSE: Strip MimeType params for converters (frankframework#5315) commit c653455 Author: Niels <nielsmeijer@hotmail.com> Date: Tue Sep 5 13:57:05 2023 +0200 Add ContentDisposition header to WSS responses (frankframework#5314) commit 6382219 Author: Sergi Philipsen <philipsen.sergi@gmail.com> Date: Tue Sep 5 13:56:29 2023 +0200 Update information in Docker.md (frankframework#5325) commit 217d15e Author: Niels <nielsmeijer@hotmail.com> Date: Tue Sep 5 13:37:37 2023 +0200 Allow global setting for the use of eTags and disable by default (frankframework#5317) commit ba3f824 Author: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com> Date: Tue Sep 5 11:51:37 2023 +0200 Bump org.apache.tomcat:tomcat-catalina from 9.0.78 to 9.0.80 (frankframework#5322) commit 27d3049 Author: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com> Date: Tue Sep 5 11:51:23 2023 +0200 Bump org.apache.ant:ant from 1.10.13 to 1.10.14 (frankframework#5323) commit 5a1a4d8 Author: Niels <nielsmeijer@hotmail.com> Date: Thu Aug 31 17:47:52 2023 +0200 Fix detection of named params in QuerySenders (frankframework#5313) (cherry picked from commit 9cc2994) commit d154d9d Author: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com> Date: Thu Aug 31 09:49:47 2023 +0200 Bump spring.boot.version from 2.7.14 to 2.7.15 (frankframework#5309) Updates `org.springframework.boot:spring-boot-starter` from 2.7.14 to 2.7.15 - [Release notes](https://github.com/spring-projects/spring-boot/releases) - [Commits](spring-projects/spring-boot@v2.7.14...v2.7.15) Updates `org.springframework.boot:spring-boot-starter-tomcat` from 2.7.14 to 2.7.15 - [Release notes](https://github.com/spring-projects/spring-boot/releases) - [Commits](spring-projects/spring-boot@v2.7.14...v2.7.15) Updates `org.springframework.boot:spring-boot-loader` from 2.7.14 to 2.7.15 - [Release notes](https://github.com/spring-projects/spring-boot/releases) - [Commits](spring-projects/spring-boot@v2.7.14...v2.7.15) commit 038e502 Author: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com> Date: Thu Aug 31 09:42:52 2023 +0200 Bump org.eclipse.jetty:jetty-maven-plugin (frankframework#5310) Bumps [org.eclipse.jetty:jetty-maven-plugin](https://github.com/eclipse/jetty.project) from 9.4.51.v20230217 to 9.4.52.v20230823. - [Release notes](https://github.com/eclipse/jetty.project/releases) - [Commits](jetty/jetty.project@jetty-9.4.51.v20230217...jetty-9.4.52.v20230823) commit 0c69d02 Author: Niels <nielsmeijer@hotmail.com> Date: Wed Aug 30 15:47:28 2023 +0200 Fix CORS headers (frankframework#5257) commit 7f86c1d Author: Niels <nielsmeijer@hotmail.com> Date: Wed Aug 30 15:38:26 2023 +0200 ASPOSE: Preserve message when converting an image (frankframework#5299) commit 4967b35 Author: Tim van der Leeuw <tnleeuw@gmail.com> Date: Wed Aug 30 15:38:02 2023 +0200 Make logger `static final` for some of the most common classes (frankframework#5303) commit 6ce0e89 Author: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com> Date: Wed Aug 30 11:33:28 2023 +0200 Bump spring-security.version from 5.8.5 to 5.8.6 (frankframework#5304) commit d06ee3c Author: Tim van der Leeuw <tnleeuw@gmail.com> Date: Wed Aug 30 11:32:34 2023 +0200 Miscellaneous minor code cleanups (frankframework#5306) commit 99e8389 Author: Niels <nielsmeijer@hotmail.com> Date: Tue Aug 29 17:29:03 2023 +0200 Add an application log that also prints to the std-out (frankframework#5256) commit 2b611e3 Author: Niels <nielsmeijer@hotmail.com> Date: Tue Aug 29 11:28:58 2023 +0200 Re-enable version attribute and allow XML root elements on JsonPipe (frankframework#5280) commit 630b164 Author: Sergi Philipsen <philipsen.sergi@gmail.com> Date: Tue Aug 29 10:04:59 2023 +0200 Add non-xa db2 datasource to DB2 compose file (frankframework#5297) commit 1a6d9d2 Author: Niels <nielsmeijer@hotmail.com> Date: Tue Aug 29 10:03:58 2023 +0200 Add more debug and trace logging (frankframework#5255) commit 9b327ed Author: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com> Date: Tue Aug 29 10:00:56 2023 +0200 Bump software.amazon.awssdk:sqs from 2.20.124 to 2.20.135 (frankframework#5295) commit 7792aa1 Author: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com> Date: Tue Aug 29 10:00:18 2023 +0200 Bump software.amazon.awssdk:utils from 2.20.122 to 2.20.135 (frankframework#5296) commit 8eda416 Author: Jeroen Jansen van Rosendaal <jjansenvr@gmail.com> Date: Wed Aug 23 15:51:54 2023 +0200 Update MD files (frankframework#5268) commit bb37053 Author: Niels <nielsmeijer@hotmail.com> Date: Wed Aug 23 09:41:24 2023 +0200 Globally fix java imports (frankframework#5258) commit 4d8182c Author: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com> Date: Fri Aug 11 16:02:55 2023 +0200 Bump com.microsoft.azure:msal4j from 1.13.9 to 1.13.10 (frankframework#5235) Bumps [com.microsoft.azure:msal4j](https://github.com/AzureAD/microsoft-authentication-library-for-java) from 1.13.9 to 1.13.10. - [Release notes](https://github.com/AzureAD/microsoft-authentication-library-for-java/releases) - [Changelog](https://github.com/AzureAD/microsoft-authentication-library-for-java/blob/dev/changelog.txt) - [Commits](AzureAD/microsoft-authentication-library-for-java@v1.13.9...v1.13.10) commit 90c5fc5 Author: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com> Date: Fri Aug 11 16:02:46 2023 +0200 Bump com.google.protobuf:protobuf-java from 3.23.4 to 3.24.0 (frankframework#5236) Bumps [com.google.protobuf:protobuf-java](https://github.com/protocolbuffers/protobuf) from 3.23.4 to 3.24.0. - [Release notes](https://github.com/protocolbuffers/protobuf/releases) - [Changelog](https://github.com/protocolbuffers/protobuf/blob/main/protobuf_release.bzl) - [Commits](protocolbuffers/protobuf@v3.23.4...v3.24.0) commit da2252d Author: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com> Date: Fri Aug 11 16:02:35 2023 +0200 Bump software.amazon.awssdk:sqs from 2.20.122 to 2.20.124 (frankframework#5237) commit a19607c Author: Tim van der Leeuw <tnleeuw@gmail.com> Date: Fri Aug 11 16:01:48 2023 +0200 Support StoredProcedure OUT parameters for Oracle (frankframework#5224) commit e417c44 Author: Niels <nielsmeijer@hotmail.com> Date: Thu Aug 10 16:59:05 2023 +0200 Add logLevelParam to LogSender (frankframework#5231) commit 89726d0 Author: Niels <nielsmeijer@hotmail.com> Date: Thu Aug 10 15:36:57 2023 +0200 Allow create folder when creating/writing to a file (frankframework#5220) commit 9bf93c2 Author: Niels <nielsmeijer@hotmail.com> Date: Thu Aug 10 15:36:08 2023 +0200 Fix global forward Frank/Java-doc (frankframework#5230) commit a3d9541 Author: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com> Date: Thu Aug 10 10:03:49 2023 +0200 Bump com.hierynomus:smbj from 0.12.1 to 0.12.2 (frankframework#5225) Bumps [com.hierynomus:smbj](https://github.com/hierynomus/smbj) from 0.12.1 to 0.12.2. - [Commits](hierynomus/smbj@v0.12.1...v0.12.2) commit f18eadd Author: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com> Date: Thu Aug 10 10:03:16 2023 +0200 Bump software.amazon.awssdk:cloudwatch from 2.20.120 to 2.20.122 (frankframework#5227) commit dd70307 Author: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com> Date: Thu Aug 10 10:03:05 2023 +0200 Bump org.apache.maven:maven-artifact from 3.9.3 to 3.9.4 (frankframework#5226) Bumps [org.apache.maven:maven-artifact](https://github.com/apache/maven) from 3.9.3 to 3.9.4. - [Release notes](https://github.com/apache/maven/releases) - [Commits](apache/maven@maven-3.9.3...maven-3.9.4) commit 1adffc2 Author: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com> Date: Wed Aug 9 10:58:12 2023 +0200 Bump org.apache.wss4j:wss4j-ws-security-dom from 2.4.1 to 2.4.2 (frankframework#5214) commit 75d649d Author: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com> Date: Wed Aug 9 10:24:39 2023 +0200 Bump software.amazon.awssdk:sqs from 2.20.103 to 2.20.122 (frankframework#5221) commit d84ee77 Author: dependabot[bot] <49699333+dependabot[bot]@users.noreply.github.com> Date: Wed Aug 9 10:24:20 2023 +0200 Bump software.amazon.awssdk:utils from 2.20.117 to 2.20.122 (frankframework#5223) commit 0089983 Author: Tim van der Leeuw <tnleeuw@gmail.com> Date: Wed Aug 9 10:23:52 2023 +0200 Make JDBC and JTA tests more stable when testing with real databases (frankframework#5184)

tnleeuw added 3 commits August 8, 2023 23:02

Start adding output parameter support for Oracle stored procedures.

d7fc2a7

Fix the tests and the support code for Oracle.

3ad20f8

Allow using Sender Parameters to specify type of a stored procedure O…

d56be7f

…UT parameter.

tnleeuw added the Feature label Aug 9, 2023

tnleeuw requested review from nielsm5 and alisihab August 9, 2023 08:33

tnleeuw self-assigned this Aug 9, 2023

tnleeuw linked an issue Aug 9, 2023 that may be closed by this pull request

Add full support for Oracle (OUT Parameters not yet supported in Oracle) #5219

Closed

Add the forgotten license.

b8dfabe

sonatype-lift bot reviewed Aug 9, 2023

View reviewed changes

nielsm5 reviewed Aug 9, 2023

View reviewed changes

tnleeuw and others added 2 commits August 9, 2023 11:22

Update core/src/main/java/nl/nn/adapterframework/jdbc/StoredProcedure…

e6683d8

…ParamDef.java Co-authored-by: Niels <nielsmeijer@hotmail.com>

More correct mapping of named parameters; need to add more tests for …

f7a2260

…this.

sonatype-lift bot reviewed Aug 9, 2023

View reviewed changes

alisihab reviewed Aug 9, 2023

View reviewed changes

core/src/test/java/nl/nn/adapterframework/jdbc/StoredProcedureQuerySenderTest.java Show resolved Hide resolved

Rewrite the way output parameters are specified. Now use the existing…

516fc0d

… Param tags for that.

tnleeuw requested review from nielsm5 and alisihab August 10, 2023 08:55

sonatype-lift bot reviewed Aug 10, 2023

View reviewed changes

core/src/main/java/nl/nn/adapterframework/jdbc/StoredProcedureQuerySender.java Show resolved Hide resolved

nielsm5 reviewed Aug 10, 2023

View reviewed changes

nielsm5 approved these changes Aug 10, 2023

View reviewed changes

tnleeuw added 2 commits August 10, 2023 15:57

Merge branch 'master' into feature/5219_OracleStoredProcedureOUTParams

977211b

Extend the stored procedure tests in IAF-Test with output parameters.

13d24cb

Some fixes to type conversions required for some output types.

nielsm5 approved these changes Aug 11, 2023

View reviewed changes

nielsm5 merged commit a19607c into master Aug 11, 2023

nielsm5 deleted the feature/5219_OracleStoredProcedureOUTParams branch August 11, 2023 14:01

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Support StoredProcedure OUT parameters for Oracle #5224

Support StoredProcedure OUT parameters for Oracle #5224

tnleeuw commented Aug 9, 2023 •

edited

Loading

sonatype-lift bot commented Aug 9, 2023

sonatype-lift bot Aug 9, 2023

nielsm5 Aug 9, 2023

tnleeuw Aug 9, 2023

tnleeuw Aug 10, 2023

nielsm5 Aug 9, 2023

tnleeuw Aug 9, 2023

nielsm5 Aug 9, 2023

tnleeuw Aug 9, 2023

sonatype-lift bot Aug 9, 2023

sonatype-lift bot commented Aug 10, 2023

nielsm5 Aug 10, 2023

Command	Usage
`@sonatype-lift ignore`	Leave out the above finding from this PR
`@sonatype-lift ignoreall`	Leave out all the existing findings from this PR
`@sonatype-lift exclude <file\|issue\|path\|tool>`	Exclude specified `file\|issue\|path\|tool` from Lift findings by updating your config.toml file

Support StoredProcedure OUT parameters for Oracle #5224

Support StoredProcedure OUT parameters for Oracle #5224

Conversation

tnleeuw commented Aug 9, 2023 • edited Loading

sonatype-lift bot commented Aug 9, 2023

Sonatype Lift is retiring

sonatype-lift bot Aug 9, 2023

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

sonatype-lift bot Aug 9, 2023

Choose a reason for hiding this comment

sonatype-lift bot commented Aug 10, 2023

🛠 Lift Auto-fix

Footnotes

Choose a reason for hiding this comment

tnleeuw commented Aug 9, 2023 •

edited

Loading