incubator-groovy git commit: groovy-sql doco: stored procs

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

incubator-groovy git commit: groovy-sql doco: stored procs

paulk
Repository: incubator-groovy
Updated Branches:
  refs/heads/master 01609605a -> 513b5f791


groovy-sql doco: stored procs


Project: http://git-wip-us.apache.org/repos/asf/incubator-groovy/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-groovy/commit/513b5f79
Tree: http://git-wip-us.apache.org/repos/asf/incubator-groovy/tree/513b5f79
Diff: http://git-wip-us.apache.org/repos/asf/incubator-groovy/diff/513b5f79

Branch: refs/heads/master
Commit: 513b5f79163114ef05929feb5d4ef3ebcb75611f
Parents: 0160960
Author: Paul King <[hidden email]>
Authored: Mon Apr 20 14:31:29 2015 +1000
Committer: Paul King <[hidden email]>
Committed: Mon Apr 20 14:31:29 2015 +1000

----------------------------------------------------------------------
 .../groovy-sql/src/spec/doc/sql-userguide.adoc  |  52 ++++++++-
 .../groovy-sql/src/spec/test/SqlTest.groovy     | 117 +++++++++++++++++++
 2 files changed, 168 insertions(+), 1 deletion(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/incubator-groovy/blob/513b5f79/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc
----------------------------------------------------------------------
diff --git a/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc b/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc
index c371412..18db669 100644
--- a/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc
+++ b/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc
@@ -388,7 +388,57 @@ include::{rootProjectDir}/subprojects/groovy-sql/src/spec/test/SqlTest.groovy[ta
 
 === Stored procedures
 
-(TBD)
+The exact syntax for creating a stored procedure or function varies slightly between different databases.
+For the HSQLDB database we are using, we can create a stored function which returns the initials of all authors in a table
+as follows:
+
+[source,groovy]
+.Creating a stored function
+----
+include::{rootProjectDir}/subprojects/groovy-sql/src/spec/test/SqlTest.groovy[tags=sql_create_stored_proc,indent=0]
+----
+
+We can use a SQL `CALL` statement to invoke the function using Groovy's normal SQL retrieval methods.
+Here is an example using `eachRow`.
+
+[source,groovy]
+.Creating a stored procedure or function
+----
+include::{rootProjectDir}/subprojects/groovy-sql/src/spec/test/SqlTest.groovy[tags=sql_use_stored_proc,indent=0]
+----
+
+Here is the code for creating another stored function, this one taking the lastname as a parameter:
+
+[source,groovy]
+.Creating a stored function with a parameter
+----
+include::{rootProjectDir}/subprojects/groovy-sql/src/spec/test/SqlTest.groovy[tags=sql_create_stored_proc_param,indent=0]
+----
+
+We can use the placeholder syntax to specify where the parameter belongs and note the special placeholder position to indicate the result:
+
+[source,groovy]
+.Using a stored function with a parameter
+----
+include::{rootProjectDir}/subprojects/groovy-sql/src/spec/test/SqlTest.groovy[tags=sql_use_stored_proc_param,indent=0]
+----
+
+Finally, here is a stored procedure with input and output parameters:
+
+[source,groovy]
+.Creating a stored procedure with input and output parameters
+----
+include::{rootProjectDir}/subprojects/groovy-sql/src/spec/test/SqlTest.groovy[tags=sql_create_stored_proc_inout,indent=0]
+----
+
+To use the `CONCAT_NAME` stored procedure parameter, we make use of a special `call` method. Any input parameters are simply provided
+as parameters to the method call. For output parameters, the resulting type must be specified as shown here:
+
+[source,groovy]
+.Using a stored procedure with input and output parameters
+----
+include::{rootProjectDir}/subprojects/groovy-sql/src/spec/test/SqlTest.groovy[tags=sql_use_stored_proc_inout,indent=0]
+----
 
 == Using DataSets
 

http://git-wip-us.apache.org/repos/asf/incubator-groovy/blob/513b5f79/subprojects/groovy-sql/src/spec/test/SqlTest.groovy
----------------------------------------------------------------------
diff --git a/subprojects/groovy-sql/src/spec/test/SqlTest.groovy b/subprojects/groovy-sql/src/spec/test/SqlTest.groovy
index dc16d91..4cc8577 100644
--- a/subprojects/groovy-sql/src/spec/test/SqlTest.groovy
+++ b/subprojects/groovy-sql/src/spec/test/SqlTest.groovy
@@ -571,4 +571,121 @@ class SqlTest extends GroovyTestCase {
         '''
     }
 
+    void testStoredProcSimple() {
+        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 ->
+          sql.execute """
+          DROP TABLE Author IF EXISTS
+          """
+          sql.execute """
+          CREATE TABLE Author (
+            id          INTEGER GENERATED BY DEFAULT AS IDENTITY,
+            firstname   VARCHAR(64),
+            lastname    VARCHAR(64)
+          )
+          """
+          sql.execute "INSERT INTO Author (firstname, lastname) VALUES ('Dierk', 'Koenig')"
+          sql.execute "INSERT INTO Author (firstname, lastname) VALUES ('Jon', 'Skeet')"
+          sql.execute "INSERT INTO Author (firstname, lastname) VALUES ('Guillaume', 'Laforge')"
+          // tag::sql_create_stored_proc[]
+          sql.execute """
+            CREATE FUNCTION SELECT_AUTHOR_INITIALS()
+            RETURNS TABLE (firstInitial VARCHAR(1), lastInitial VARCHAR(1))
+            READS SQL DATA
+            RETURN TABLE (
+              SELECT LEFT(Author.firstname, 1) as firstInitial, LEFT(Author.lastname, 1) as lastInitial
+              FROM Author
+            )
+          """
+          // end::sql_create_stored_proc[]
+          // tag::sql_use_stored_proc[]
+          def result = []
+          sql.eachRow('CALL SELECT_AUTHOR_INITIALS()') {
+            result << "$it.firstInitial$it.lastInitial"
+          }
+          assert result == ['DK', 'JS', 'GL']
+          // end::sql_use_stored_proc[]
+          sql.execute "DROP FUNCTION SELECT_AUTHOR_INITIALS"
+        }
+        '''
+    }
+
+    void testStoredProcWithParam() {
+        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 ->
+          sql.execute """
+          DROP TABLE Author IF EXISTS
+          """
+          sql.execute """
+          CREATE TABLE Author (
+            id          INTEGER GENERATED BY DEFAULT AS IDENTITY,
+            firstname   VARCHAR(64),
+            lastname    VARCHAR(64)
+          )
+          """
+          sql.execute "INSERT INTO Author (firstname, lastname) VALUES ('Dierk', 'Koenig')"
+          sql.execute "INSERT INTO Author (firstname, lastname) VALUES ('Jon', 'Skeet')"
+          sql.execute "INSERT INTO Author (firstname, lastname) VALUES ('Guillaume', 'Laforge')"
+          // tag::sql_create_stored_proc_param[]
+          sql.execute """
+            CREATE FUNCTION FULL_NAME (p_lastname VARCHAR(64))
+            RETURNS VARCHAR(100)
+            READS SQL DATA
+            BEGIN ATOMIC
+              DECLARE ans VARCHAR(100);
+              SELECT CONCAT(firstname, ' ', lastname) INTO ans
+              FROM Author WHERE lastname = p_lastname;
+              RETURN ans;
+            END
+          """
+          // end::sql_create_stored_proc_param[]
+          // tag::sql_use_stored_proc_param[]
+          def result = sql.firstRow("{? = call FULL_NAME(?)}", ['Koenig'])
+          assert result[0] == 'Dierk Koenig'
+          // end::sql_use_stored_proc_param[]
+          sql.execute "DROP FUNCTION FULL_NAME"
+        }
+        '''
+    }
+
+    void testStoredProcWithInOut() {
+        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_proc_inout[]
+          sql.execute """
+            CREATE PROCEDURE CONCAT_NAME (OUT fullname VARCHAR(100),
+              IN first VARCHAR(50), IN last VARCHAR(50))
+            BEGIN ATOMIC
+              SET fullname = CONCAT(first, ' ', last);
+            END
+          """
+          // end::sql_create_stored_proc_inout[]
+          // tag::sql_use_stored_proc_inout[]
+          sql.call("{call CONCAT_NAME(?, ?, ?)}", [Sql.VARCHAR, 'Dierk', 'Koenig']) {
+            fullname -> assert fullname == 'Dierk Koenig'
+          }
+          // end::sql_use_stored_proc_inout[]
+          sql.execute "DROP PROCEDURE CONCAT_NAME"
+        }
+        '''
+    }
+
 }