diff options
Diffstat (limited to 'odb-tests/mssql/stored-proc')
-rw-r--r-- | odb-tests/mssql/stored-proc/buildfile | 34 | ||||
-rw-r--r-- | odb-tests/mssql/stored-proc/driver.cxx | 233 | ||||
-rw-r--r-- | odb-tests/mssql/stored-proc/test.hxx | 63 | ||||
-rw-r--r-- | odb-tests/mssql/stored-proc/testscript | 29 |
4 files changed, 359 insertions, 0 deletions
diff --git a/odb-tests/mssql/stored-proc/buildfile b/odb-tests/mssql/stored-proc/buildfile new file mode 100644 index 0000000..c92eabd --- /dev/null +++ b/odb-tests/mssql/stored-proc/buildfile @@ -0,0 +1,34 @@ +# file : mssql/stored-proc/buildfile +# license : GNU GPL v2; see accompanying LICENSE file + +if ($build.meta_operation != 'dist') +{ + assert ($mssql) "mssql should be configured for this test" + assert (!$multi) "multi-database mode is not supported by this test" +} + +import libodb = libodb%lib{odb} + +import libs = libodb-mssql%lib{odb-mssql} +import libs += lib{common} + +exe{driver}: {hxx cxx}{* -*-odb} {hxx ixx cxx}{test-odb} testscript + +# Introduce the metadata library target to make sure the libodb library is +# resolved for the odb_compile ad hoc rule (see build/root.build for details). +# +libue{test-meta}: $libodb + +<{hxx ixx cxx}{test-odb}>: hxx{test} libue{test-meta} + +exe{driver}: libue{test-meta} $libs + +# Specify the ODB custom options to be used by the odb_compile ad hoc rule +# (see build/root.build for details). +# +odb_options = --table-prefix mssql_stored_proc_ \ + --generate-schema \ + --default-database common \ + --generate-query + +cxx.poptions =+ "-I$out_base" "-I$src_base" diff --git a/odb-tests/mssql/stored-proc/driver.cxx b/odb-tests/mssql/stored-proc/driver.cxx new file mode 100644 index 0000000..36f8d82 --- /dev/null +++ b/odb-tests/mssql/stored-proc/driver.cxx @@ -0,0 +1,233 @@ +// file : mssql/stored-proc/driver.cxx +// license : GNU GPL v2; see accompanying LICENSE file + +// Test SQL Server stored procedure support. +// + +#include <memory> // std::unique_ptr +#include <iostream> + +#include <odb/mssql/database.hxx> +#include <odb/mssql/transaction.hxx> + +#include <libcommon/common.hxx> + +#include "test.hxx" +#include "test-odb.hxx" + +#undef NDEBUG +#include <cassert> + +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<default_schema> ().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 + { + unique_ptr<database> db (create_specific_database<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<select_all_objects> result; + + transaction t (db->begin ()); + + result r (db->query<select_all_objects> ()); + + 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<select_objects> query; + typedef odb::result<select_objects> result; + + transaction t (db->begin ()); + + result r (db->query<select_objects> ( + 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<objects_min_max> ()); + cout << omm.num_min << " " << omm.num_max << endl + << endl; + + t.commit (); + } + + { + create_procedure ( + *db, "insert_object_id", + "(@n INT, @s VARCHAR(512))" + "AS" + " INSERT INTO mssql_stored_proc_object([num], [str])" + " VALUES(@n, @s);"); + + { + typedef mssql::query<insert_object> query; + + transaction t (db->begin ()); + + db->query_one<insert_object> ( + query::_val (4) + "," + query::_val ("d")); + + unique_ptr<object> o (db->load<object> (4)); + cout << o->num << " " << o->str << endl + << endl; + + t.commit (); + } + + { + typedef mssql::query<no_result> query; + + transaction t (db->begin ()); + + db->query_one<no_result> ( + "EXEC insert_object_id" + query::_val (5) + "," + query::_val ("e")); + + unique_ptr<object> o (db->load<object> (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])" + " VALUES(@n, @s);" + " SET @id = SCOPE_IDENTITY();" + " RETURN 123;"); + + typedef mssql::query<insert_object_id> query; + + { + create_procedure ( + *db, "insert_object_id_odb", + "(@n INT, @s VARCHAR(512))" + "AS" + " DECLARE @id INT;" + " DECLARE @ret INT;" + " EXEC @ret = insert_object_id @n, @s, @id OUTPUT;" + " SELECT @ret, @id;"); + + transaction t (db->begin ()); + + insert_object_id io ( + db->query_value<insert_object_id> ( + query::_val (6) + "," + query::_val ("f"))); + + cout << io.ret << " " << io.id << endl + << endl; + + 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<insert_object_id> ( + query::_val (7) + "," + query::_val ("g"))); + + cout << io.ret << " " << io.id << endl + << endl; + + t.commit (); + } + } + } + catch (const odb::exception& e) + { + cerr << e.what () << endl; + return 1; + } +} diff --git a/odb-tests/mssql/stored-proc/test.hxx b/odb-tests/mssql/stored-proc/test.hxx new file mode 100644 index 0000000..5958ea3 --- /dev/null +++ b/odb-tests/mssql/stored-proc/test.hxx @@ -0,0 +1,63 @@ +// file : mssql/stored-proc/test.hxx +// license : GNU GPL v2; see accompanying LICENSE file + +#ifndef TEST_HXX +#define TEST_HXX + +#include <string> + +#include <odb/core.hxx> + +#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 +struct no_result {}; + +#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_id (?)") +struct insert_object {}; + +#pragma db view query("EXEC insert_object_id_odb (?)") +struct insert_object_id +{ + unsigned int ret; + unsigned long id; +}; + +#endif // TEST_HXX diff --git a/odb-tests/mssql/stored-proc/testscript b/odb-tests/mssql/stored-proc/testscript new file mode 100644 index 0000000..0de46a7 --- /dev/null +++ b/odb-tests/mssql/stored-proc/testscript @@ -0,0 +1,29 @@ +# file : mssql/stored-proc/testscript +# license : GNU GPL v2; see accompanying LICENSE file + +.include ../../database-options.testscript +.include ../../mssql.testscript + ++$create_schema + +: basics +: +$* >>EOO + 1 a + 2 b + 3 c + + a + b + + 1 3 + + 4 d + + 5 e + + 123 6 + + 123 7 + + EOO |