From 39e1dc3a83b05eea91f31af17f599a0c2982dbcb Mon Sep 17 00:00:00 2001 From: Boris Kolpackov Date: Mon, 26 Jan 2015 14:31:40 +0200 Subject: Document SQL Server stored procedure support --- NEWS | 4 ++ doc/manual.xhtml | 152 ++++++++++++++++++++++++++++++++++++++++++++++++++++++- 2 files changed, 155 insertions(+), 1 deletion(-) diff --git a/NEWS b/NEWS index 2aec85e..51fb133 100644 --- a/NEWS +++ b/NEWS @@ -36,6 +36,10 @@ Version 2.4.0 * Support for calling MySQL stored procedures. For details and limitations refer to Section 17.7, "MySQL Stored Procedures" in the ODB manual. + * Support for calling SQL Server stored procedures. For details and + limitations refer to Section 21.7, "SQL Server Stored Procedures" in + the ODB manual. + * New option, --oracle-warn-truncation, makes ODB warn about SQL names that are longer than 30 characters and are therefore truncated. ODB now also detects when such truncations lead to Oracle name conflicts diff --git a/doc/manual.xhtml b/doc/manual.xhtml index 6ed1911..42f4ae8 100644 --- a/doc/manual.xhtml +++ b/doc/manual.xhtml @@ -845,6 +845,7 @@ for consistency. 21.6SQL Server Index Definition + 21.7SQL Server Stored Procedures @@ -20182,7 +20183,7 @@ t.commit ();

Note that as with all native views, the order and types of data members - must match the order of columns in the SELECT list inside + must match those of columns in the SELECT list inside the stored procedure.

There are also a number of limitations when it comes to support for @@ -24143,6 +24144,155 @@ class object }; +

21.7 SQL Server Stored Procedures

+ +

ODB native views (Section 10.5, "Native Views") + can be used to call SQL Server stored procedures. For example, assuming + we are using the person class from Chapter + 2, "Hello World Example" (and the corresponding person + table), we can create a stored procedure that given the min and max + ages returns some information about all the people in that range:

+ + 
+CREATE PROCEDURE dbo.person_range (
+  @min_age SMALLINT,
+  @max_age SMALLINT)
+AS
+  SELECT age, first, last FROM person
+    WHERE age >= @min_age AND age <= @max_age;
+
+ +

Given the above stored procedure we can then define an ODB view + that can be used to call it and retrieve its result:

+ + 
+#pragma db view query("EXEC person_range (?)")
+struct person_range
+{
+  unsigned short age;
+  std::string first;
+  std::string last;
+};
+
+ +

The following example shows how we can use the above view to + print the list of people in a specific age range:

+ + 
+typedef odb::query<person_range> query;
+typedef odb::result<person_range> result;
+
+transaction t (db.begin ());
+
+result r (
+  db.query<person_range> (
+    query::_val (1) + "," + query::_val (18)));
+
+for (result::iterator i (r.begin ()); i != r.end (); ++i)
+  cerr << i->first << " " << i->last << " " << i->age << endl;
+
+t.commit ();
+
+ +

Note that as with all native views, the order and types of data members + must match those of columns in the SELECT list inside + the stored procedure.

+ +

There are also a number of limitations when it comes to calling + SQL Server stored procedures with ODB views. There is currently + no support for output parameters, however, this is planned for + a future version. In the meantime, to call a stored procedure + that has output parameters we have to use a wrapper procedure + that converts such parameters to a SELECT + result. For example, given the following procedure that + calculates the age range of the people in our database:

+ + 
+CREATE PROCEDURE dbo.person_age_range (
+  @min_age SMALLINT = NULL OUTPUT,
+  @max_age SMALLINT = NULL OUTPUT)
+AS
+  SELECT @min_age = MIN(age), @max_age = MAX(max) FROM person;
+
+ +

We can create a wrapper procedure like this:

+ + 
+CREATE PROCEDURE dbo.person_age_range_odb
+AS
+  DECLARE @min_age SMALLINT, @max_age SMALLINT;
+  EXEC person_age_range @min_age OUTPUT, @max_age OUTPUT;
+  SELECT @min_age, @max_age;
+
+ +

And a view like this:

+ + 
+#pragma db view query("EXEC person_age_range_odb")
+struct person_age_range
+{
+  unsigned short min_age;
+  unsigned short max_age;
+};
+
+ +

Which we can then use to call the stored procedure:

+ + 
+transaction t (db.begin ());
+
+person_age_range ar (db.query_value<person_age_range> ());
+cerr << ar.min_age << " " << ar.max_age << endl;
+
+t.commit ();
+
+ +

In SQL Server, a stored procedure can produce multiple results. + For example, if a stored procedure executes several + SELECT statements, then the result of calling such + a procedure consists of two row sets, one for each SELECT + statement. Because such multiple row sets can contain varying number + and type of columns, they cannot be all extracted into a + single view. Consequently, these kind of stored procedures are + currently not supported.

+ +

A stored procedure may also produce no row sets at all. For + example, a stored procedure that only executes DML statements + would exhibit this behavior. To call such a procedure we use + an empty view, for example:

+ + 
+CREATE PROCEDURE dbo.insert_person (
+  @first VARCHAR(512),
+  @last VARCHAR(512),
+  @age SMALLINT)
+AS
+  INSERT INTO person(first, last, age)
+    VALUES(@first, @last, @age);
+
+ + 
+#pragma db view
+struct no_result {};
+
+transaction t (db.begin ());
+
+db.query_one<no_result> (
+  "EXEC insert_person" +
+    query::_val ("John") + "," +
+    query::_val ("Doe") + "," +
+    query::_val (21));
+
+t.commit ();
+
+ +

Finally, an SQL Server stored procedure can also return an + integer status code. Similar to output parameters, this code + can only be observed by an ODB view if it is converted to a + SELECT result. For more information on how to + do this and for other examples of stored procedure calls, + refer to the mssql/stored-proc test in the + odb-tests package.

-- cgit v1.1