You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@groovy.apache.org by pa...@apache.org on 2015/04/20 06:31:33 UTC
incubator-groovy git commit: groovy-sql doco: stored procs
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 <pa...@asert.com.au>
Authored: Mon Apr 20 14:31:29 2015 +1000
Committer: Paul King <pa...@asert.com.au>
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"
+ }
+ '''
+ }
+
}