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