From 0d49ea1fe08cf1eab41a00149393a291c65a59d7 Mon Sep 17 00:00:00 2001 From: Karen Arutyunov Date: Thu, 25 Jan 2024 20:32:06 +0300 Subject: Turn odb-tests repository into package for muti-package repository --- odb-tests/mssql/stored-proc/driver.cxx | 231 +++++++++++++++++++++++++++++++++ 1 file changed, 231 insertions(+) create mode 100644 odb-tests/mssql/stored-proc/driver.cxx (limited to 'odb-tests/mssql/stored-proc/driver.cxx') diff --git a/odb-tests/mssql/stored-proc/driver.cxx b/odb-tests/mssql/stored-proc/driver.cxx new file mode 100644 index 0000000..2389798 --- /dev/null +++ b/odb-tests/mssql/stored-proc/driver.cxx @@ -0,0 +1,231 @@ +// file : mssql/stored-proc/driver.cxx +// 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_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])" + " VALUES(@n, @s);" + " SET @id = SCOPE_IDENTITY();" + " RETURN 123;"); + + typedef mssql::query 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 ( + 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 ( + 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; + } +} -- cgit v1.1