From 092cb7d916b8ebc83cacab849010acd04f56d38d Mon Sep 17 00:00:00 2001 From: Boris Kolpackov Date: Tue, 18 Nov 2014 14:54:23 +0200 Subject: Add SQL Server stored procedure test --- mssql/makefile | 1 + mssql/stored-proc/driver.cxx | 181 +++++++++++++++++++++++++++++++++++++++++++ mssql/stored-proc/makefile | 108 ++++++++++++++++++++++++++ mssql/stored-proc/test.hxx | 58 ++++++++++++++ mssql/stored-proc/test.std | 11 +++ 5 files changed, 359 insertions(+) create mode 100644 mssql/stored-proc/driver.cxx create mode 100644 mssql/stored-proc/makefile create mode 100644 mssql/stored-proc/test.hxx create mode 100644 mssql/stored-proc/test.std diff --git a/mssql/makefile b/mssql/makefile index 9337a8e..646e776 100644 --- a/mssql/makefile +++ b/mssql/makefile @@ -10,6 +10,7 @@ custom \ database \ native \ query \ +stored-proc \ types $(default): $(addprefix $(out_base)/,$(addsuffix /,$(tests))) diff --git a/mssql/stored-proc/driver.cxx b/mssql/stored-proc/driver.cxx new file mode 100644 index 0000000..60c4d27 --- /dev/null +++ b/mssql/stored-proc/driver.cxx @@ -0,0 +1,181 @@ +// file : mssql/stored-proc/driver.cxx +// copyright : Copyright (c) 2009-2013 Code Synthesis Tools CC +// license : GNU GPL v2; see accompanying LICENSE file + +// Test SQL Server stored procedure support. +// + +#include // std::auto_ptr +#include +#include + +#include +#include + +#include + +#include "test.hxx" +#include "test-odb.hxx" + +using namespace std; +namespace mssql = odb::mssql; +using namespace mssql; + +void +create_procedure (database& db, const string& name, const string& body) +{ + transaction t (db.begin ()); + + string s (db.query_value ().name); + + db.execute ( + "IF EXISTS (" + " SELECT * FROM sysobjects" + " WHERE name = '" + name + "' AND user_name(uid) = '" + s +"')" + " DROP PROCEDURE [" + s + "].[" + name + "]"); + + db.execute ("CREATE PROCEDURE [" + s + "].[" + name + "] " + body); + + t.commit (); +} + +int +main (int argc, char* argv[]) +{ + try + { + auto_ptr db (create_specific_database (argc, argv)); + + object o1 (1, "a"); + object o2 (2, "b"); + object o3 (3, "c"); + + { + transaction t (db->begin ()); + db->persist (o1); + db->persist (o2); + db->persist (o3); + t.commit (); + } + + { + create_procedure ( + *db, "select_all_objects", + "AS" + " SELECT num, str FROM mssql_stored_proc_object ORDER BY id;"); + + typedef odb::result result; + + transaction t (db->begin ()); + + result r (db->query ()); + + for (result::iterator i (r.begin ()); i != r.end (); ++i) + cout << i->num << " " << i->str << endl; + cout << endl; + + t.commit (); + } + + { + create_procedure ( + *db, "select_objects", + "(@id INT, @n VARCHAR(512))" + "AS" + " SELECT str FROM mssql_stored_proc_object " + " WHERE [id] = @id OR [num] = @n ORDER BY id;"); + + typedef mssql::query query; + typedef odb::result result; + + transaction t (db->begin ()); + + result r (db->query ( + query::_val (o1.id) + "," + query::_val (o2.num))); + + for (result::iterator i (r.begin ()); i != r.end (); ++i) + cout << i->str << endl; + cout << endl; + + t.commit (); + } + + { + create_procedure ( + *db, "objects_min_max", + "(@min INT = NULL OUTPUT, @max INT = NULL OUTPUT)" + "AS" + " SELECT @min = MIN(num), @max = MAX(num)" + " FROM mssql_stored_proc_object;"); + + create_procedure ( + *db, "objects_min_max_odb", + "AS" + " DECLARE @min INT, @max INT;" + " EXEC objects_min_max @min OUTPUT, @max OUTPUT;" + " SELECT @min, @max;"); + + transaction t (db->begin ()); + + objects_min_max omm (db->query_value ()); + cout << omm.num_min << " " << omm.num_max << endl + << endl; + + t.commit (); + } + + { + create_procedure ( + *db, "insert_object", + "(@n INT, @s VARCHAR(512), @id INT = NULL OUTPUT)" + "AS" + " INSERT INTO mssql_stored_proc_object([num], [str])" + " VALUES(@n, @s);" + " SET @id = SCOPE_IDENTITY();" + " RETURN 123;"); + + create_procedure ( + *db, "insert_object_odb", + "(@n INT, @s VARCHAR(512))" + "AS" + " DECLARE @id INT;" + " DECLARE @ret INT;" + " DECLARE @tbl TABLE(dummy INT);" + " EXEC @ret = insert_object @n, @s, @id OUTPUT;" + " SELECT @ret, @id;"); + + // An alternative implementation that produces a different + // result set configuration at the ODBC level. + // + /* + create_procedure ( + *db, "insert_object_odb", + "(@n INT, @s VARCHAR(512))" + "AS" + " DECLARE @id INT;" + " DECLARE @ret INT;" + " DECLARE @tbl TABLE(dummy INT);" + " INSERT INTO @tbl EXEC @ret = insert_object @n, @s, @id OUTPUT;" + " SELECT @ret, @id;"); + */ + + typedef mssql::query query; + + transaction t (db->begin ()); + + insert_object io ( + db->query_value ( + query::_val (4) + "," + query::_val ("d"))); + + cout << io.ret << " " << io.id << endl + << endl; + + t.commit (); + } + } + catch (const odb::exception& e) + { + cerr << e.what () << endl; + return 1; + } +} diff --git a/mssql/stored-proc/makefile b/mssql/stored-proc/makefile new file mode 100644 index 0000000..f590150 --- /dev/null +++ b/mssql/stored-proc/makefile @@ -0,0 +1,108 @@ +# file : mssql/stored-proc/makefile +# copyright : Copyright (c) 2009-2013 Code Synthesis Tools CC +# license : GNU GPL v2; see accompanying LICENSE file + +include $(dir $(lastword $(MAKEFILE_LIST)))../../build/bootstrap.make + +cxx_tun := driver.cxx +odb_hdr := test.hxx +genf := $(call odb-gen,$(odb_hdr)) +gen := $(addprefix $(out_base)/,$(genf)) +cxx_obj := $(addprefix $(out_base)/,$(cxx_tun:.cxx=.o)) $(filter %.o,$(gen:.cxx=.o)) +cxx_od := $(cxx_obj:.o=.o.d) + +common.l := $(out_root)/libcommon/common/common.l +common.l.cpp-options := $(out_root)/libcommon/common/common.l.cpp-options + +# Import. +# +$(call import,\ + $(scf_root)/import/odb/stub.make,\ + odb: odb,odb-rules: odb_rules) + +# Build. +# +$(driver): $(cxx_obj) $(common.l) +$(cxx_obj) $(cxx_od): cpp_options := -I$(out_base) -I$(src_base) +$(cxx_obj) $(cxx_od): $(common.l.cpp-options) + +$(gen): $(odb) +$(gen): odb := $(odb) +$(gen) $(dist): export odb_options += --database mssql --default-database \ +common --generate-schema --generate-query --table-prefix mssql_stored_proc_ +$(gen): cpp_options := -I$(src_base) +$(gen): $(common.l.cpp-options) + +$(call include-dep,$(cxx_od),$(cxx_obj),$(gen)) + +# Alias for default target. +# +$(out_base)/: $(driver) + +# Dist +# +$(dist): sources := $(cxx_tun) +$(dist): headers := $(odb_hdr) +$(dist): data_dist := test.std +$(dist): export name := $(subst /,-,$(subst $(src_root)/mssql/,,$(src_base))) +$(dist): export extra_dist := $(data_dist) \ +$(name)-vc8.vcproj $(name)-vc9.vcproj \ +$(name)-vc10.vcxproj $(name)-vc10.vcxproj.filters \ +$(name)-vc11.vcxproj $(name)-vc11.vcxproj.filters \ +$(name)-vc12.vcxproj $(name)-vc12.vcxproj.filters +$(dist): + $(call dist-data,$(sources) $(headers) $(data_dist)) + $(call meta-automake,../template/Makefile.am) + $(call meta-vc8proj,../template/template-vc8.vcproj,$(name)-vc8.vcproj) + $(call meta-vc9proj,../template/template-vc9.vcproj,$(name)-vc9.vcproj) + $(call meta-vc10proj,../template/template-vc10.vcxproj,$(name)-vc10.vcxproj) + $(call meta-vc11proj,../template/template-vc11.vcxproj,$(name)-vc11.vcxproj) + $(call meta-vc12proj,../template/template-vc12.vcxproj,$(name)-vc12.vcxproj) + +# Test. +# +$(eval $(call test-rule)) + +# Clean. +# +$(clean): \ + $(driver).o.clean \ + $(addsuffix .cxx.clean,$(cxx_obj)) \ + $(addsuffix .cxx.clean,$(cxx_od)) \ + $(addsuffix .hxx.clean,$(filter %.cxx,$(gen))) + $(call message,,rm -f $(out_base)/test.out) + +# Generated .gitignore. +# +ifeq ($(out_base),$(src_base)) +$(driver): | $(out_base)/.gitignore + +$(out_base)/.gitignore: files := driver $(genf) +$(clean): $(out_base)/.gitignore.clean + +$(call include,$(bld_root)/git/gitignore.make) +endif + +# How to. +# +$(call include,$(bld_root)/dist.make) +$(call include,$(bld_root)/meta/vc8proj.make) +$(call include,$(bld_root)/meta/vc9proj.make) +$(call include,$(bld_root)/meta/vc10proj.make) +$(call include,$(bld_root)/meta/vc11proj.make) +$(call include,$(bld_root)/meta/vc12proj.make) +$(call include,$(bld_root)/meta/automake.make) + +$(call include,$(bld_root)/cxx/standard.make) # cxx_standard +ifdef cxx_standard +$(gen): odb_options += --std $(cxx_standard) +$(call include,$(odb_rules)) +endif + +$(call include,$(bld_root)/cxx/cxx-d.make) +$(call include,$(bld_root)/cxx/cxx-o.make) +$(call include,$(bld_root)/cxx/o-e.make) + +# Dependencies. +# +$(call import,$(src_root)/libcommon/makefile) diff --git a/mssql/stored-proc/test.hxx b/mssql/stored-proc/test.hxx new file mode 100644 index 0000000..16f3c52 --- /dev/null +++ b/mssql/stored-proc/test.hxx @@ -0,0 +1,58 @@ +// file : mssql/stored-proc/test.hxx +// copyright : Copyright (c) 2009-2013 Code Synthesis Tools CC +// license : GNU GPL v2; see accompanying LICENSE file + +#ifndef TEST_HXX +#define TEST_HXX + +#include + +#include + +#pragma db view query("SELECT SCHEMA_NAME()") +struct default_schema +{ + std::string name; +}; + +#pragma db object +struct object +{ + object () {} + object (unsigned int n, std::string s): num (n), str (s) {} + + #pragma db id auto + unsigned long id; + + unsigned int num; + std::string str; +}; + +#pragma db view query("EXEC select_all_objects") +struct select_all_objects +{ + unsigned int num; + std::string str; +}; + +#pragma db view query("EXEC select_objects (?)") +struct select_objects +{ + std::string str; +}; + +#pragma db view query("EXEC objects_min_max_odb (?)") +struct objects_min_max +{ + unsigned int num_min; + unsigned int num_max; +}; + +#pragma db view query("EXEC insert_object_odb (?)") +struct insert_object +{ + unsigned int ret; + unsigned long id; +}; + +#endif // TEST_HXX diff --git a/mssql/stored-proc/test.std b/mssql/stored-proc/test.std new file mode 100644 index 0000000..528007c --- /dev/null +++ b/mssql/stored-proc/test.std @@ -0,0 +1,11 @@ +1 a +2 b +3 c + +a +b + +1 3 + +123 4 + -- cgit v1.1 From 0aa976237df26be9415c5aac335ab43e026db077 Mon Sep 17 00:00:00 2001 From: Boris Kolpackov Date: Wed, 19 Nov 2014 13:54:39 +0200 Subject: Allow empty runtime and execute views Use them to handle INSERT/UPDATE SQL Server stored procedures. --- mssql/stored-proc/driver.cxx | 116 +++++++++++++++++++++++++++++++------------ mssql/stored-proc/test.hxx | 10 +++- mssql/stored-proc/test.std | 8 ++- 3 files changed, 99 insertions(+), 35 deletions(-) diff --git a/mssql/stored-proc/driver.cxx b/mssql/stored-proc/driver.cxx index 60c4d27..2878486 100644 --- a/mssql/stored-proc/driver.cxx +++ b/mssql/stored-proc/driver.cxx @@ -126,7 +126,46 @@ main (int argc, char* argv[]) { create_procedure ( - *db, "insert_object", + *db, "insert_object_id", + "(@n INT, @s VARCHAR(512))" + "AS" + " INSERT INTO mssql_stored_proc_object([num], [str])" + " VALUES(@n, @s);"); + + { + typedef mssql::query query; + + transaction t (db->begin ()); + + db->query_one ( + query::_val (4) + "," + query::_val ("d")); + + auto_ptr o (db->load (4)); + cout << o->num << " " << o->str << endl + << endl; + + t.commit (); + } + + { + typedef mssql::query query; + + transaction t (db->begin ()); + + db->query_one ( + "EXEC insert_object_id" + query::_val (5) + "," + query::_val ("e")); + + auto_ptr o (db->load (5)); + cout << o->num << " " << o->str << endl + << endl; + + t.commit (); + } + } + + { + create_procedure ( + *db, "insert_object_id", "(@n INT, @s VARCHAR(512), @id INT = NULL OUTPUT)" "AS" " INSERT INTO mssql_stored_proc_object([num], [str])" @@ -134,43 +173,56 @@ main (int argc, char* argv[]) " SET @id = SCOPE_IDENTITY();" " RETURN 123;"); - create_procedure ( - *db, "insert_object_odb", - "(@n INT, @s VARCHAR(512))" - "AS" - " DECLARE @id INT;" - " DECLARE @ret INT;" - " DECLARE @tbl TABLE(dummy INT);" - " EXEC @ret = insert_object @n, @s, @id OUTPUT;" - " SELECT @ret, @id;"); + typedef mssql::query query; - // An alternative implementation that produces a different - // result set configuration at the ODBC level. - // - /* - create_procedure ( - *db, "insert_object_odb", - "(@n INT, @s VARCHAR(512))" - "AS" - " DECLARE @id INT;" - " DECLARE @ret INT;" - " DECLARE @tbl TABLE(dummy INT);" - " INSERT INTO @tbl EXEC @ret = insert_object @n, @s, @id OUTPUT;" - " SELECT @ret, @id;"); - */ + { + create_procedure ( + *db, "insert_object_id_odb", + "(@n INT, @s VARCHAR(512))" + "AS" + " DECLARE @id INT;" + " DECLARE @ret INT;" + " DECLARE @tbl TABLE(dummy INT);" + " EXEC @ret = insert_object_id @n, @s, @id OUTPUT;" + " SELECT @ret, @id;"); - typedef mssql::query query; + transaction t (db->begin ()); - transaction t (db->begin ()); + insert_object_id io ( + db->query_value ( + query::_val (6) + "," + query::_val ("f"))); - insert_object io ( - db->query_value ( - query::_val (4) + "," + query::_val ("d"))); + cout << io.ret << " " << io.id << endl + << endl; - cout << io.ret << " " << io.id << endl - << endl; + t.commit (); + } - t.commit (); + // An alternative implementation that produces a different + // result set configuration at the ODBC level. + // + { + create_procedure ( + *db, "insert_object_id_odb", + "(@n INT, @s VARCHAR(512))" + "AS" + " DECLARE @id INT;" + " DECLARE @ret INT;" + " DECLARE @tbl TABLE(dummy INT);" + " INSERT INTO @tbl EXEC @ret = insert_object_id @n, @s, @id OUTPUT;" + " SELECT @ret, @id;"); + + transaction t (db->begin ()); + + insert_object_id io ( + db->query_value ( + query::_val (7) + "," + query::_val ("g"))); + + cout << io.ret << " " << io.id << endl + << endl; + + t.commit (); + } } } catch (const odb::exception& e) diff --git a/mssql/stored-proc/test.hxx b/mssql/stored-proc/test.hxx index 16f3c52..d302630 100644 --- a/mssql/stored-proc/test.hxx +++ b/mssql/stored-proc/test.hxx @@ -28,6 +28,9 @@ struct object std::string str; }; +#pragma db view +struct no_result {}; + #pragma db view query("EXEC select_all_objects") struct select_all_objects { @@ -48,8 +51,11 @@ struct objects_min_max unsigned int num_max; }; -#pragma db view query("EXEC insert_object_odb (?)") -struct insert_object +#pragma db view query("EXEC insert_object (?)") +struct insert_object {}; + +#pragma db view query("EXEC insert_object_id_odb (?)") +struct insert_object_id { unsigned int ret; unsigned long id; diff --git a/mssql/stored-proc/test.std b/mssql/stored-proc/test.std index 528007c..6635bcc 100644 --- a/mssql/stored-proc/test.std +++ b/mssql/stored-proc/test.std @@ -7,5 +7,11 @@ b 1 3 -123 4 +4 d + +5 e + +123 6 + +123 7 -- cgit v1.1 From 0a4df2bc0c7962acbddc55b4b5668e09fb54ef7f Mon Sep 17 00:00:00 2001 From: Boris Kolpackov Date: Wed, 19 Nov 2014 14:48:06 +0200 Subject: Bump version to 2.4.0.a4 --- version | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/version b/version index 26d9ac3..42645da 100644 --- a/version +++ b/version @@ -1 +1 @@ -2.4.0.a3 +2.4.0.a4 -- cgit v1.1