[groovy] 01/02: Added example in sql module for calling stored function with in out parameter

[pa...@apache.org]

This is an automated email from the ASF dual-hosted git repository.

paulk pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/groovy.git

commit 3502c9d08400c58e30299c7c3d3ad35511d7b009
Author: Mariusz Wasak <ma...@gmail.com>
AuthorDate: Sat Mar 9 23:54:38 2019 +0100

    Added example in sql module for calling stored function with in out parameter
---
 .../groovy-sql/src/spec/doc/sql-userguide.adoc     | 13 ++++++++
 .../groovy-sql/src/spec/test/SqlTest.groovy        | 38 ++++++++++++++++++++++
 2 files changed, 51 insertions(+)

diff --git a/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc b/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc
index 31c1e34..357f1bf 100644
--- a/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc
+++ b/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc
@@ -461,6 +461,19 @@ as parameters to the method call. For output parameters, the resulting type must
 include::{rootProjectDir}/subprojects/groovy-sql/src/spec/test/SqlTest.groovy[tags=sql_use_stored_proc_inout,indent=0]
 ----
 
+[source,groovy]
+.Using a stored function with input/output parameter
+----
+include::{rootProjectDir}/subprojects/groovy-sql/src/spec/test/SqlTest.groovy[tags=sql_use_stored_fun_inout_parameter,indent=0]
+----
+
+Printed message is "result: RET_OK -- message: MESSAGE_OK"
+
+Function `CHECK_ID_POSITIVE_IN_OUT` is defined in Oracle as:
+----
+include::{rootProjectDir}/subprojects/groovy-sql/src/spec/test/SqlTest.groovy[tags=sql_create_stored_fun_inout_parameter,indent=0]
+----
+
 == Using DataSets
 
 (TBD)
diff --git a/subprojects/groovy-sql/src/spec/test/SqlTest.groovy b/subprojects/groovy-sql/src/spec/test/SqlTest.groovy
index a8c191f..72dcce6 100644
--- a/subprojects/groovy-sql/src/spec/test/SqlTest.groovy
+++ b/subprojects/groovy-sql/src/spec/test/SqlTest.groovy
@@ -687,5 +687,43 @@ class SqlTest extends GroovyTestCase {
         }
         '''
     }
+    
+    void testStoredFunWithInOutParameter() {
+        assertScript '''
+        import groovy.sql.Sql
+
+        def url = 'jdbc:hsqldb:mem:yourDB'
+        def user = 'sa'
+        def password = ''
+        def driver = 'org.hsqldb.jdbcDriver'
+        Sql.withInstance(url, user, password, driver) { sql ->
+          // tag::sql_create_stored_fun_inout_parameter[]
+          sql.execute """
+           CREATE OR REPLACE FUNCTION CHECK_ID_POSITIVE_IN_OUT ( p_err IN OUT VARCHAR2, pparam NUMBER)
+           RETURN VARCHAR2 IS
+           re VARCHAR2(15);
+
+           BEGIN
+            IF pparam > 0 THEN
+              p_err := p_err || '_OK';
+              re := 'RET_OK';
+            ELSE 
+              p_err := p_err || '_ERROR';
+              re := 'RET_ERROR';   
+            END IF;
+
+            RETURN re;
+           END;
+          """
+          // end::sql_create_stored_fun_inout_parameter[]
+          // tag::sql_use_stored_fun_inout_parameter[]
+          def scall = "{? = call CHECK_ID_POSITIVE_IN_OUT(?, ?)}"
+          sql.call scall, [Sql.VARCHAR, Sql.inout(Sql.VARCHAR("MESSAGE")), 1], {
+            res, p_err -> println("result: $res -- message: $p_err")
+          }
+          // end::sql_use_stored_fun_inout_parameter[]
+        }
+        '''
+    }   
 
 }