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 <[email protected]> Authored: Mon Apr 20 14:31:29 2015 +1000 Committer: Paul King <[email protected]> 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" + } + ''' + } + }
