diff options
| author | Boris Kolpackov <boris@codesynthesis.com> | 2011-11-18 16:18:55 +0200 |
|---|---|---|
| committer | Boris Kolpackov <boris@codesynthesis.com> | 2012-01-20 15:44:49 +0200 |
| commit | e64485293cdf16fa5bfcc747b938752efa1869ad (patch) | |
| tree | c4dbd00e9514b0ad545adfb528d07b0a947fb094 /doc | |
| parent | 893c0b2d1f646390f6cf27935424f64a22eaf1f1 (diff) | |
Add new chapter for SQL Server and update profile chapters
Diffstat (limited to 'doc')
| -rw-r--r-- | doc/manual.xhtml | 1421 |
1 files changed, 1283 insertions, 138 deletions
diff --git a/doc/manual.xhtml b/doc/manual.xhtml index ef36090..f5bf6dc 100644 --- a/doc/manual.xhtml +++ b/doc/manual.xhtml @@ -609,14 +609,36 @@ for consistency. <tr> <th>16.5</th><td><a href="#16.5">Oracle Limitations</a> <table class="toc"> - <tr><th>16.5.1</th><td><a href="#16.5.1">16.5.1 Identifier Truncation</a></td></tr> - <tr><th>16.5.2</th><td><a href="#16.5.2">16.5.2 Query Result Caching</a></td></tr> - <tr><th>16.5.3</th><td><a href="#16.5.3">16.5.3 Foreign Key Constraints</a></td></tr> - <tr><th>16.5.4</th><td><a href="#16.5.4">16.5.4 Unique Constraint Violations</a></td></tr> - <tr><th>16.5.5</th><td><a href="#16.5.5">16.5.5 Large <code>FLOAT</code> and - <code>NUMBER</code> Types</a></td></tr> - <tr><th>16.5.6</th><td><a href="#16.5.6">16.5.6 Timezones</a></td></tr> - <tr><th>16.5.7</th><td><a href="#16.5.7">16.5.7 <code>LONG</code> Types</a></td></tr> + <tr><th>16.5.1</th><td><a href="#16.5.1">Identifier Truncation</a></td></tr> + <tr><th>16.5.2</th><td><a href="#16.5.2">Query Result Caching</a></td></tr> + <tr><th>16.5.3</th><td><a href="#16.5.3">Foreign Key Constraints</a></td></tr> + <tr><th>16.5.4</th><td><a href="#16.5.4">Unique Constraint Violations</a></td></tr> + <tr><th>16.5.5</th><td><a href="#16.5.5">Large <code>FLOAT</code> and <code>NUMBER</code> Types</a></td></tr> + <tr><th>16.5.6</th><td><a href="#16.5.6">Timezones</a></td></tr> + <tr><th>16.5.7</th><td><a href="#16.5.7"><code>LONG</code> Types</a></td></tr> + </table> + </td> + </tr> + </table> + </td> + </tr> + + <tr> + <th>17</th><td><a href="#17">Microsoft SQL Server Database</a> + <table class="toc"> + <tr><th>17.1</th><td><a href="#17.1">SQL Server Type Mapping</a></td></tr> + <tr><th>17.2</th><td><a href="#17.2">SQL Server Database Class</a></td></tr> + <tr><th>17.3</th><td><a href="#17.3">SQL Server Connection and Connection Factory</a></td></tr> + <tr><th>17.4</th><td><a href="#17.4">SQL Server Exceptions</a></td></tr> + <tr> + <th>17.5</th><td><a href="#17.5">SQL Server Limitations</a> + <table class="toc"> + <tr><th>17.5.1</th><td><a href="#17.5.1">Query Result Caching</a></td></tr> + <tr><th>17.5.2</th><td><a href="#17.5.2">Foreign Key Constraints</a></td></tr> + <tr><th>17.5.3</th><td><a href="#17.5.3">Unique Constraint Violations</a></td></tr> + <tr><th>17.5.4</th><td><a href="#17.5.4">Multithreaded Windows Applications</a></td></tr> + <tr><th>17.5.5</th><td><a href="#17.5.5">Affected Row Count and DDL Statements</a></td></tr> + <tr><th>17.5.6</th><td><a href="#17.5.6">Long Data and Automatically Assigned Object Ids</a></td></tr> </table> </td> </tr> @@ -629,22 +651,23 @@ for consistency. </tr> <tr> - <th>17</th><td><a href="#17">Profiles Introduction</a></td> + <th>18</th><td><a href="#18">Profiles Introduction</a></td> </tr> <tr> - <th>18</th><td><a href="#18">Boost Profile</a> + <th>19</th><td><a href="#19">Boost Profile</a> <table class="toc"> - <tr><th>18.1</th><td><a href="#18.1">Smart Pointers Library</a></td></tr> - <tr><th>18.2</th><td><a href="#18.2">Unordered Containers Library</a></td></tr> - <tr><th>18.3</th><td><a href="#18.3">Optional Library</a></td></tr> + <tr><th>19.1</th><td><a href="#19.1">Smart Pointers Library</a></td></tr> + <tr><th>19.2</th><td><a href="#19.2">Unordered Containers Library</a></td></tr> + <tr><th>19.3</th><td><a href="#19.3">Optional Library</a></td></tr> <tr> - <th>18.4</th><td><a href="#18.4">Date Time Library</a> + <th>19.4</th><td><a href="#19.4">Date Time Library</a> <table class="toc"> - <tr><th>18.4.1</th><td><a href="#18.4.1">MySQL Database Type Mapping</a></td></tr> - <tr><th>18.4.2</th><td><a href="#18.4.2">SQLite Database Type Mapping</a></td></tr> - <tr><th>18.4.3</th><td><a href="#18.4.3">PostgreSQL Database Type Mapping</a></td></tr> - <tr><th>18.4.4</th><td><a href="#18.4.4">Oracle Database Type Mapping</a></td></tr> + <tr><th>19.4.1</th><td><a href="#19.4.1">MySQL Database Type Mapping</a></td></tr> + <tr><th>19.4.2</th><td><a href="#19.4.2">SQLite Database Type Mapping</a></td></tr> + <tr><th>19.4.3</th><td><a href="#19.4.3">PostgreSQL Database Type Mapping</a></td></tr> + <tr><th>19.4.4</th><td><a href="#19.4.4">Oracle Database Type Mapping</a></td></tr> + <tr><th>19.4.5</th><td><a href="#19.4.5">SQL Server Database Type Mapping</a></td></tr> </table> </td> </tr> @@ -653,27 +676,29 @@ for consistency. </tr> <tr> - <th>19</th><td><a href="#19">Qt Profile</a> + <th>20</th><td><a href="#20">Qt Profile</a> <table class="toc"> <tr> - <th>19.1</th><td><a href="#19.1">Basic Types Library</a> + <th>20.1</th><td><a href="#20.1">Basic Types Library</a> <table class="toc"> - <tr><th>19.1.1</th><td><a href="#19.1.1">MySQL Database Type Mapping</a></td></tr> - <tr><th>19.1.2</th><td><a href="#19.1.2">SQLite Database Type Mapping</a></td></tr> - <tr><th>19.1.3</th><td><a href="#19.1.3">PostgreSQL Database Type Mapping</a></td></tr> - <tr><th>19.1.4</th><td><a href="#19.1.4">Oracle Database Type Mapping</a></td></tr> + <tr><th>20.1.1</th><td><a href="#20.1.1">MySQL Database Type Mapping</a></td></tr> + <tr><th>20.1.2</th><td><a href="#20.1.2">SQLite Database Type Mapping</a></td></tr> + <tr><th>20.1.3</th><td><a href="#20.1.3">PostgreSQL Database Type Mapping</a></td></tr> + <tr><th>20.1.4</th><td><a href="#20.1.4">Oracle Database Type Mapping</a></td></tr> + <tr><th>20.1.5</th><td><a href="#20.1.5">SQL Server Database Type Mapping</a></td></tr> </table> </td> </tr> - <tr><th>19.2</th><td><a href="#19.2">Smart Pointers Library</a></td></tr> - <tr><th>19.3</th><td><a href="#19.3">Containers Library</a></td></tr> + <tr><th>20.2</th><td><a href="#20.2">Smart Pointers Library</a></td></tr> + <tr><th>20.3</th><td><a href="#20.3">Containers Library</a></td></tr> <tr> - <th>19.4</th><td><a href="#19.4">Date Time Library</a> + <th>20.4</th><td><a href="#20.4">Date Time Library</a> <table class="toc"> - <tr><th>19.4.1</th><td><a href="#19.4.1">MySQL Database Type Mapping</a></td></tr> - <tr><th>19.4.2</th><td><a href="#19.4.2">SQLite Database Type Mapping</a></td></tr> - <tr><th>19.4.3</th><td><a href="#19.4.3">PostgreSQL Database Type Mapping</a></td></tr> - <tr><th>19.4.4</th><td><a href="#19.4.4">Oracle Database Type Mapping</a></td></tr> + <tr><th>20.4.1</th><td><a href="#20.4.1">MySQL Database Type Mapping</a></td></tr> + <tr><th>20.4.2</th><td><a href="#20.4.2">SQLite Database Type Mapping</a></td></tr> + <tr><th>20.4.3</th><td><a href="#20.4.3">PostgreSQL Database Type Mapping</a></td></tr> + <tr><th>20.4.4</th><td><a href="#20.4.4">Oracle Database Type Mapping</a></td></tr> + <tr><th>20.4.5</th><td><a href="#20.4.5">SQL Server Database Type Mapping</a></td></tr> </table> </td> </tr> @@ -2876,19 +2901,19 @@ for (unsigned short retry_count (0); ; retry_count++) deduced from the argument being passed. The following example shows how we can call these functions:</p> -<pre class="c++"> + <pre class="c++"> person john ("John", "Doe", 33); shared_ptr<person> jane (new person ("Jane", "Doe", 32)); transaction t (db.begin ()); -db->persist (john); +db.persist (john); unsigned long jane_id (db.persist (jane)); t.commit (); cerr << "Jane's id: " << jane_id << endl; -</pre> + </pre> <p>Notice that in the above code fragment we have created instances that we were planning to make persistent before starting the @@ -4378,7 +4403,7 @@ private: </pre> <p>The resulting database table (called <code>person_nicknames</code>) will - contain the object id column of type <code>unsigned long</code> + contain the object id column of type <code>unsigned long</code> (called <code>object_id</code>), the index column of an integer type (called <code>index</code>), and the value column of type <code>std::string</code> (called <code>value</code>).</p> @@ -4467,7 +4492,7 @@ private: </pre> <p>The resulting database table (called <code>person_emails</code>) will - contain the object id column of type <code>unsigned long</code> + contain the object id column of type <code>unsigned long</code> (called <code>object_id</code>) and the value column of type <code>std::string</code> (called <code>value</code>).</p> @@ -4528,7 +4553,7 @@ private: </pre> <p>The resulting database table (called <code>person_age_weight_map</code>) - will contain the object id column of type <code>unsigned long</code> + will contain the object id column of type <code>unsigned long</code> (called <code>object_id</code>), the key column of type <code>unsigned short</code> (called <code>key</code>), and the value column of type <code>float</code> (called <code>value</code>).</p> @@ -4770,7 +4795,7 @@ typedef odb::result<employee> result; session s; transaction t (db.begin ()); -result r (db->query<employee> ( +result r (db.query<employee> ( query::employer->name == "Example Inc" && query::last == "Doe")); for (result::iterator i (r.begin ()); i != r.end (); ++i) @@ -4787,7 +4812,7 @@ t.commit (); <code>employer</code> object:</p> <pre class="c++"> -result r (db->query<employee> (query::employer.is_null ())); +result r (db.query<employee> (query::employer.is_null ())); </pre> <p>An important concept to keep in mind when working with object @@ -5710,7 +5735,7 @@ private: are not allowed. The following example illustrates some of the possible use cases:</p> -<pre class="c++"> + <pre class="c++"> #pragma db value class basic_name { @@ -5747,12 +5772,12 @@ class person name name_; }; -</pre> + </pre> <p>A composite value type can also be defined as an instantiation of a C++ class template, for example:</p> -<pre class="c++"> + <pre class="c++"> template <typename T> struct point { @@ -5771,7 +5796,7 @@ class object int_point center_; }; -</pre> + </pre> <p>Note that the database support code for such a composite value type is generated when compiling the header containing the @@ -5781,7 +5806,7 @@ class object <code>std::pair</code> defined in the <code>utility</code> standard header file:</p> -<pre class="c++"> + <pre class="c++"> #include <utility> // std::pair typedef std::pair<std::string, std::string> phone_numbers; @@ -5794,7 +5819,7 @@ class person phone_numbers phone_; }; -</pre> + </pre> <p>We can also use data members from composite value types in database queries (<a href="#4">Chapter 4, "Querying the @@ -5816,9 +5841,9 @@ class person typedef odb::query<person> query; typedef odb::result<person> result; -transaction t (db->begin ()); +transaction t (db.begin ()); -result r (db->query<person> ( +result r (db.query<person> ( query::name.extras.nickname == "Squeaky")); ... @@ -5835,7 +5860,7 @@ t.commit (); types things are slightly more complex since they are mapped to multiple columns. Consider the following example:</p> -<pre class="c++"> + <pre class="c++"> #pragma db value class name { @@ -5855,7 +5880,7 @@ class person name name_; }; -</pre> + </pre> <p>The column names for the <code>first_</code> and <code>last_</code> members are constructed by using the sanitized name of the @@ -5909,7 +5934,7 @@ CREATE TABLE person ( <p>We can also make the column prefix empty, for example:</p> -<pre class="c++"> + <pre class="c++"> #pragma db object class person { @@ -6145,7 +6170,7 @@ namespace odb consider using a more efficient implementation of the <em>optional value</em> concept such as the <code>optional</code> class template from Boost - (<a href="#18.3">Section 18.3, "Optional Library"</a>).</p> + (<a href="#19.3">Section 19.3, "Optional Library"</a>).</p> <p>Another common C++ representation of a value that can be <code>NULL</code> is a pointer. ODB will automatically @@ -6238,7 +6263,7 @@ class contractor: public person { ... }; -</pre> + </pre> <p>In the above example both the <code>employee</code> and <code>contractor</code> classes inherit the <code>first_</code> @@ -6299,7 +6324,7 @@ public: return self_employed; } }; -</pre> + </pre> <p>With this inheritance style, which we will call <em>polymorphism inheritance</em>, the application code normally works with derived @@ -6354,7 +6379,7 @@ public: class is only inherited once. The following example shows a persistent class hierarchy employing reuse inheritance:</p> -<pre class="c++"> + <pre class="c++"> // Abstract person class. Note that it does not declare the // object id. // @@ -6412,11 +6437,11 @@ class contractor: public person #pragma db id std::string email_; }; -</pre> + </pre> <p>The sample database schema for this hierarchy is shown below.</p> -<pre class="sql"> + <pre class="sql"> CREATE TABLE permanent_employee ( first TEXT NOT NULL, last TEXT NOT NULL, @@ -6432,7 +6457,7 @@ CREATE TABLE contractor ( first TEXT NOT NULL, last TEXT NOT NULL, email VARCHAR (255) NOT NULL PRIMARY KEY); -</pre> + </pre> <p>The complete version of the code presented in this section is available in the <code>inheritance</code> example in the @@ -9437,8 +9462,8 @@ class person If you have a high object turnover (that is, objects are routinely made persistent and then erased), then care must be taken not to run out of object ids. In such situations, using - <code>unsigned long long</code> as the identifier type is a safe - choice.</p> + <code>unsigned long long</code> as the identifier type + is a safe choice.</p> <p>For additional information on the automatic identifier assignment, refer to <a href="#3.7">Section 3.7, "Making Objects Persistent"</a>.</p> @@ -9504,7 +9529,7 @@ class account #pragma db not_null shared_ptr<person> holder_; }; -</pre> + </pre> <p>The <code>NULL</code> semantics can also be specified on the per-type basis (<a href="#12.3.3">Section 12.3.3, @@ -9844,7 +9869,7 @@ class person required argument to this specifier is the corresponding data member name in the referenced object. For example:</p> -<pre class="c++"> + <pre class="c++"> using std::tr1::shared_ptr; using std::tr1::weak_ptr; @@ -9903,7 +9928,7 @@ class person an integer or similar database type. Note also that object versions are not reused. If you have a high update frequency, then care must be taken not to run out of versions. In such situations, using - <code>unsigned long long</code> as the version type is a safe + <code>unsigned long long</code> as the version type is a safe choice.</p> <p>For a more detailed discussion of optimistic concurrency, refer to @@ -10394,6 +10419,7 @@ aCC +W2161 ... <tr><th>14</th><td><a href="#14">SQLite Database</a></td></tr> <tr><th>15</th><td><a href="#15">PostgreSQL Database</a></td></tr> <tr><th>16</th><td><a href="#16">Oracle Database</a></td></tr> + <tr><th>17</th><td><a href="#17">Microsoft SQL Server Database</a></td></tr> </table> @@ -10518,7 +10544,7 @@ aCC +W2161 ... </table> <p>Note that the <code>std::string</code> type is mapped - differently depending on whether the member of this type + differently depending on whether a member of this type is an object id or not. If the member is an object id, then for this member <code>std::string</code> is mapped to the <code>VARCHAR(255)</code> MySQL type. Otherwise, @@ -11205,8 +11231,9 @@ class object <p>Note also that SQLite only operates with signed integers and the largest value that an SQLite database can store is a signed 64-bit integer. As - a result, greater <code>unsigned long long</code> values will be - represented in the database as negative values.</p> + a result, greater <code>unsigned long</code> and + <code>unsigned long long</code> values will be represented in + the database as negative values.</p> <h2><a name="14.2">14.2 SQLite Database Class</a></h2> @@ -11681,7 +11708,7 @@ namespace odb (<a href="#12.4.3">Section 12.4.3, "<code>type</code>"</a>). For example:</p> -<pre class="c++"> + <pre class="c++"> #pragma db object class person { @@ -11692,7 +11719,7 @@ class person #pragma db id auto // Ok, Mapped to INTEGER. unsigned int id_; }; -</pre> + </pre> <h3><a name="14.5.3">14.5.3 Foreign Key Constraints</a></h3> @@ -11721,7 +11748,7 @@ class person re-creating the schema. The following code fragment shows how this can be done:</p> -<pre> + <pre> #include <odb/connection.hxx> #include <odb/transaction.hxx> #include <odb/schema-catalog.hxx> @@ -11739,7 +11766,7 @@ odb::database& db = ... c->execute ("PRAGMA foreign_keys=ON"); } -</pre> + </pre> <p>Finally, ODB relies on standard SQL behavior which requires that foreign key constraints checking is deferred until the @@ -11764,7 +11791,7 @@ CREATE TABLE Employee ( to distinguish between the duplicate primary key and other constraint violations. As a result, when making an object persistent, the SQLite ODB runtime will translate all constraint violation errors to the - <code>object_not_persistent</code> exception (<a href="#3.13">Section + <code>object_already_persistent</code> exception (<a href="#3.13">Section 3.13, "ODB Exceptions"</a>).</p> <h3><a name="14.5.5">14.5.5 Sharing of Queries</a></h3> @@ -11952,10 +11979,10 @@ class object semantics being <code>NOT NULL</code>.</p> <p>Note also that because PostgreSQL does not support unsigned integers, - the <code>unsigned short</code>, <code>unsigned int</code>, and - <code>unsigned long long</code> C++ types are by default mapped - to the <code>SMALLINT</code>, <code>INTEGER</code>, and - <code>BIGINT</code> PostgreSQL types, respectively. The sign bit + the <code>unsigned short</code>, <code>unsigned int</code>, and + <code>unsigned long</code>/<code>unsigned long long</code> C++ types + are by default mapped to the <code>SMALLINT</code>, <code>INTEGER</code>, + and <code>BIGINT</code> PostgreSQL types, respectively. The sign bit of the value stored by the database for these types will contain the most significant bit of the actual unsigned value being persisted.</p> @@ -12376,7 +12403,7 @@ CREATE TABLE Employee ( to distinguish between the duplicate primary key and other unique constraint violations. As a result, when making an object persistent, the PostgreSQL ODB runtime will translate all unique constraint violation - errors to the <code>object_not_persistent</code> exception + errors to the <code>object_already_persistent</code> exception (<a href="#3.13">Section 3.13, "ODB Exceptions"</a>).</p> <h3><a name="15.5.4">15.5.4 Date-Time Format</a></h3> @@ -12588,8 +12615,10 @@ class object <h2><a name="16.2">16.2 Oracle Database Class</a></h2> - <p>The Oracle <code>database</code> class has the following - interface:</p> + <p>The Oracle <code>database</code> class encapsulates the OCI environment + handle as well as the database connection string and user credentials + that are used to establish connections to the database. It has the + following interface:</p> <pre class="c++"> namespace odb @@ -12628,7 +12657,6 @@ namespace odb static void print_usage (std::ostream&); - public: const std::string& user () const; @@ -12655,7 +12683,7 @@ namespace odb ncharset () const; OCIEnv* - environment () const; + environment (); public: connection_ptr @@ -12668,10 +12696,6 @@ namespace odb <p>You will need to include the <code><odb/oracle/database.hxx></code> header file to make this class available in your application.</p> - <p>The Oracle <code>database</code> class encapsulates the OCI environment - handle as well as the database connection string and user credentials - that are used to establish connections to the database.</p> - <p>The overloaded <code>database</code> constructors allow us to specify the Oracle database parameters that should be used when connecting to the database. The <code>db</code> argument in the first constructor is a @@ -12805,7 +12829,7 @@ namespace odb providing an already connected Oracle service context. Note that the <code>connection</code> instance assumes ownership of this handle. The <code>handle()</code> accessor returns the OCI service context handle - associated with the connection.</p> + associated with the <code>connection</code> instance.</p> <p>An OCI error handle is allocated for each <code>connection</code> instance and is available via the <code>error_handle()</code> accessor @@ -13139,7 +13163,7 @@ CREATE TABLE Employee ( to distinguish between the duplicate primary key and other unique constraint violations. As a result, when making an object persistent, the Oracle ODB runtime will translate all unique constraint violation - errors to the <code>object_not_persistent</code> exception + errors to the <code>object_already_persistent</code> exception (<a href="#3.13">Section 3.13, "ODB Exceptions"</a>).</p> <h3><a name="16.5.5">16.5.5 Large <code>FLOAT</code> and @@ -13174,6 +13198,934 @@ CREATE TABLE Employee ( <code>LONG RAW</code> data types.</p> + <!-- CHAPTER --> + + + <hr class="page-break"/> + <h1><a name="17">17 Microsoft SQL Server Database</a></h1> + + <p>To generate support code for the SQL Server database you will need + to pass the "<code>--database mssql</code>" + (or "<code>-d mssql</code>") option to the ODB compiler. + Your application will also need to link to the SQL Server ODB runtime + library (<code>libodb-mssql</code>). All SQL Server-specific ODB + classes are defined in the <code>odb::mssql</code> namespace.</p> + + <h2><a name="17.1">17.1 SQL Server Type Mapping</a></h2> + + <p>The following table summarizes the default mapping between basic + C++ value types and SQL Server database types. This mapping can be + customized on the per-type and per-member basis using the ODB + Pragma Language (<a href="#12">Chapter 12, "ODB Pragma Language"</a>).</p> + + <!-- border="1" is necessary for html2ps --> + <table id="mapping" border="1"> + <tr> + <th>C++ Type</th> + <th>SQL Server Type</th> + <th>Default <code>NULL</code> Semantics</th> + </tr> + + <tr> + <td><code>bool</code></td> + <td><code>BIT</code></td> + <td><code>NOT NULL</code></td> + </tr> + + <tr> + <td><code>char</code></td> + <td><code>TINYINT</code></td> + <td><code>NOT NULL</code></td> + </tr> + + <tr> + <td><code>signed char</code></td> + <td><code>TINYINT</code></td> + <td><code>NOT NULL</code></td> + </tr> + + <tr> + <td><code>unsigned char</code></td> + <td><code>TINYINT</code></td> + <td><code>NOT NULL</code></td> + </tr> + + <tr> + <td><code>short</code></td> + <td><code>SMALLINT</code></td> + <td><code>NOT NULL</code></td> + </tr> + + <tr> + <td><code>unsigned short</code></td> + <td><code>SMALLINT</code></td> + <td><code>NOT NULL</code></td> + </tr> + + <tr> + <td><code>int</code></td> + <td><code>INT</code></td> + <td><code>NOT NULL</code></td> + </tr> + + <tr> + <td><code>unsigned int</code></td> + <td><code>INT</code></td> + <td><code>NOT NULL</code></td> + </tr> + + <tr> + <td><code>long</code></td> + <td><code>BIGINT</code></td> + <td><code>NOT NULL</code></td> + </tr> + + <tr> + <td><code>unsigned long</code></td> + <td><code>BIGINT</code></td> + <td><code>NOT NULL</code></td> + </tr> + + <tr> + <td><code>long long</code></td> + <td><code>BIGINT</code></td> + <td><code>NOT NULL</code></td> + </tr> + + <tr> + <td><code>unsigned long long</code></td> + <td><code>BIGINT</code></td> + <td><code>NOT NULL</code></td> + </tr> + + <tr> + <td><code>float</code></td> + <td><code>REAL</code></td> + <td><code>NOT NULL</code></td> + </tr> + + <tr> + <td><code>double</code></td> + <td><code>FLOAT</code></td> + <td><code>NOT NULL</code></td> + </tr> + + <tr> + <td><code>std::string</code></td> + <td><code>VARCHAR(512)/VARCHAR(256)</code></td> + <td><code>NOT NULL</code></td> + </tr> + + <tr> + <td><code>std::wstring</code></td> + <td><code>NVARCHAR(512)/NVARCHAR(256)</code></td> + <td><code>NOT NULL</code></td> + </tr> + + <tr> + <td><code>GUID</code></td> + <td><code>UNIQUEIDENTIFIER</code></td> + <td><code>NOT NULL</code></td> + </tr> + + </table> + + <p>Note that the <code>std::string</code> and <code>std::wstring</code> + types are mapped differently depending on whether a member of one of + these types is an object id or not. If the member is an object id, + then for this member <code>std::string</code> is mapped + to <code>VARCHAR(256)</code> and <code>std::wstring</code> — + to <code>NVARCHAR(256)</code>. Otherwise, <code>std::string</code> + is mapped to <code>VARCHAR(512)</code> and <code>std::wstring</code> + — to <code>NVARCHAR(512)</code>. Note also that you can + always change this mapping using the <code>db type</code> pragma + (<a href="#12.4.3">Section 12.4.3, "<code>type</code>"</a>).</p> + + <p>The SQL Server ODB runtime library also provides support for mapping the + <code>std::string</code> type to the SQL Server <code>CHAR</code> and + <code>TEXT</code> types as well as <code>std::wstring</code> + to <code>NCHAR</code> and <code>NTEXT</code>. There is also support + for mapping the <code>std::vector<char></code>, + <code>std::vector<unsigned char></code>, + <code>char[N]</code>, and <code>unsigned char[N]</code> types to + the SQL Server <code>BINARY</code>, <code>VARBINARY</code>, and + <code>IMAGE</code> types. However, these + mappings are not enabled by default (in particular, by default, + <code>std::vector</code> will be treated as a container). To enable the + alternative mappings for these types we need to specify the + database type explicitly using the <code>db type</code> pragma + (<a href="#12.4.3">Section 12.4.3, "<code>type</code>"</a>), for + example:</p> + + <pre class="c++"> +#pragma db object +class object +{ + ... + + #pragma db type ("CHAR(5)") + std::string str_; + + #pragma db type("VARBINARY(max)") + std::vector<char> buf_; + + #pragma db type("BINARY(16)") + unsigned char[16] uuid_; +}; + </pre> + + <p>Alternatively, this can be done on the per-type basis, for example:</p> + + <pre class="c++"> +typedef std::vector<char> buffer; +#pragma db value(buffer) type("VARBINARY(max)") + +#pragma db object +class object +{ + ... + + buffer buf_; // Mapped to VARBINARY(max). +}; + </pre> + + <p>Additionally, by default, C++ enumerations are automatically + mapped to <code>INT</code> with the default <code>NULL</code> + semantics being <code>NOT NULL</code>.</p> + + <p>For SQL Server, ODB handles character, national character, and + binary data in two different ways depending on its maximum length. + If the maximum length (in bytes) is less than or equal to the limit + specified with the <code>--mssql-short-limit</code> ODB compiler + option (1024 by default), then it is treated as <i>short data</i>, + otherwise — <i>long data</i>. For short data ODB pre-allocates + an intermediate buffer of the maximum size and binds it directly + to a parameter or result column. This way the underlying database + API (ODBC) can read/write directly from/to this buffer. In the case + of long data, the data is read/written in chunks using the + <code>SQLGetData()</code>/<code>SQLPutData()</code> ODBC functions. + While the long data approach reduces the amount of memory used by + the application, it may require greater CPU resources.</p> + + <p>Long data has a number of limitations. In particular, when setting + a custom short data limit, make sure that it is sufficiently large + so that no object id in the application is treated as long data. + It is also impossible to load an object or view with long data more + than once as part of a query result iteration (<a href="#4.4">Section + 4.4, "Query Result"</a>). Any such attempt will result in the + <code>odb::mssql::long_data_reload</code> exception + (<a href="#17.4">Section 17.4, "SQL Server Exceptions"</a>). For + example:</p> + + <pre class="c++"> +#pragma db object +class object +{ + ... + + int num_; + + #pragma db type("VARCHAR(max)") // Long data. + std::string str_; +}; + +typedef odb::query<object> query; +typedef odb::result<object> result; + +transaction t (db.begin ()); + +result r (db.query<object> (query::num < 100)); + +for (result::iterator i (r.begin ()); i != r.end (); ++i) +{ + if (!i->str_.empty ()) // First load. + { + object o; + i.load (o); // Error: second load, long_data_reload is thrown. + } +} + +t.commit (); + </pre> + + <p>Finally, if a native view (<a href="#9.5">Section 9.5, "Native + Views"</a>) contains one or more long data members, then such + members should come last both in the select-list of the native + SQL query and the list of data members in the C++ class.</p> + + <p>Note also that because SQL Server does not support unsigned integers, + the <code>unsigned short</code>, <code>unsigned int</code>, and + <code>unsigned long</code>/<code>unsigned long long</code> C++ types + are by default mapped to the <code>SMALLINT</code>, <code>INT</code>, + and <code>BIGINT</code> SQL Server types, respectively. The sign bit + of the value stored by the database for these types will contain + the most significant bit of the actual unsigned value being + persisted. Similarly, because there is no signed version of the + <code>TINYINT</code> SQL Server type, by default, <code>char</code> + and <code>signed char</code> C++ types are mapped to + <code>TINYINT</code>. As result, the most significant bit of + the value stored by the database for these types will contain the + sign bit of the actual signed value being persisted.</p> + + <h2><a name="17.2">17.2 SQL Server Database Class</a></h2> + + <p>The SQL Server <code>database</code> class encapsulates the ODBC + environment handle as well as the server instance address and + user credentials that are used to establish connections to the + database. It has the following interface:</p> + + <pre class="c++"> +namespace odb +{ + namespace mssql + { + enum protocol + { + protocol_auto, + protocol_tcp, // TCP/IP. + protocol_lpc, // Shared memory (local procedure call). + protocol_np // Named pipes. + }; + + class database: public odb::database + { + public: + database (const std::string& user, + const std::string& password, + const std::string& db, + const std::string& server, + const std::string& driver = "", + const std::string& extra_connect_string = "", + SQLHENV environment = 0, + std::auto_ptr<connection_factory> = 0); + + database (const std::string& user, + const std::string& password, + const std::string& db, + protocol_type protocol = protocol_auto, + const std::string& host = "", + const std::string& instance = "", + const std::string& driver = "", + const std::string& extra_connect_string = "", + SQLHENV environment = 0, + std::auto_ptr<connection_factory> = 0); + + database (const std::string& user, + const std::string& password, + const std::string& db, + const std::string& host, + unsigned int port, + const std::string& driver = "", + const std::string& extra_connect_string = "", + SQLHENV environment = 0, + std::auto_ptr<connection_factory> = 0); + + database (const std::string& connect_string, + SQLHENV environment = 0, + std::auto_ptr<connection_factory> = 0); + + database (int& argc, + char* argv[], + bool erase = false, + const std::string& extra_connect_string = "", + SQLHENV environment = 0, + std::auto_ptr<connection_factory> = 0); + + static void + print_usage (std::ostream&); + + public: + const std::string& + user () const; + + const std::string& + password () const; + + const std::string& + db () const; + + protocol_type + protocol () const; + + const std::string& + host () const; + + const std::string& + instance () const; + + unsigned int + port () const; + + const std::string& + server () const; + + const std::string& + driver () const; + + const std::string& + extra_connect_string () const; + + const std::string& + connect_string () const; + + SQLHENV + environment (); + + public: + connection_ptr + connection (); + }; + } +} + </pre> + + <p>You will need to include the <code><odb/mssql/database.hxx></code> + header file to make this class available in your application.</p> + + <p>The overloaded <code>database</code> constructors allow us to specify the + SQL Server database parameters that should be used when connecting to the + database. The <code>user</code> and <code>password</code> arguments + specify the login name and password. If <code>user</code> is empty, + then Windows authentication is used and the <code>password</code> + argument is ignored. The <code>db</code> argument specifies the + database name to open. If it is empty, then the default database for + the user is used.</p> + + <p>The <code>server</code> argument in the first constructor specifies + the SQL Server instance address in the standard SQL Server address + format:</p> + + <p> + <code>[<i>protocol</i><b>:</b>]<i>host</i>[<b>\</b><i>instance</i>][<b>,</b><i>port</i>]</code> + </p> + + <p>Where <code><i>protocol</i></code> can be <code>tcp</code> + (TCP/IP), <code>lpc</code> (shared memory), or + <code>np</code> (named pipe). If protocol is not specified, then a + suitable protocol is automatically selected based on the SQL Server + Native Client configuration. The <code><i>host</i></code> component + can be a host name or an IP address. If <code><i>instance</i></code> + is not specified, then the default SQL Server instance is assumed. + If port is not specified, then the default SQL Server port is + used (1433). Note that you would normally specify either the + instance name or the port, but not both. If both are specified, + then the instance name is ignored by the SQL Server Native Client + ODBC driver. For more information on the format of the SQL + Server address, refer to the SQL Server Native Client ODBC + driver documentation.</p> + + <p>The second and third constructors allow us to specify all these address + components (protocol, host, instance, and port) as separate + arguments. The third constructor always connects using TCP/IP + to the specified host and port.</p> + + <p>The <code>driver</code> argument specifies the SQL Server Native + Client ODBC driver that should be used to connect to the database. + If not specified, then the latest available version is used. The + following examples show common ways of connecting to the database + using the first three constructors:</p> + + <pre class="c++"> +// Connect to the default SQL Server instance on the local machine +// using the default protocol. Login as 'test' with password 'secret' +// and open the 'example_db' database. +// +odb::mssql::database db1 ("test", + "secret", + "example_db"); + +// As above except use Windows authentication and open the default +// database for this user. +// +odb::mssql::database db2 ("", + "", + ""); + +// Connect to the default SQL Server instance on 'onega' using the +// default protocol. Login as 'test' with password 'secret' and open +// the 'example_db' database. +// +odb::mssql::database db3 ("test", + "secret", + "example_db" + "onega"); + +// As above but connect to the 'production' SQL Server instance. +// +odb::mssql::database db4 ("test", + "secret", + "example_db" + "onega\\production"); + +// Same as above but specify protocol, host, and instance as separate +// arguments. +// +odb::mssql::database db5 ("test", + "secret", + "example_db", + odb::mssql::protocol_auto, + "onega", + "production"); + +// As above, but use TCP/IP as the protocol. +// +odb::mssql::database db6 ("test", + "secret", + "example_db" + "tcp:onega\\production"); + +// Same as above but using separate arguments. +// +odb::mssql::database db7 ("test", + "secret", + "example_db", + odb::mssql::protocol_tcp, + "onega", + "production"); + +// As above, but use TCP/IP port instead of the instance name. +// +odb::mssql::database db8 ("test", + "secret", + "example_db" + "tcp:onega,1435"); + +// Same as above but using separate arguments. Note that here we +// don't need to specify protocol explicitly since it can only +// be TCP/IP. +// +odb::mssql::database db9 ("test", + "secret", + "example_db", + "onega", + 1435); + +// As above but use the specific SQL Server Native Client ODBC +// driver version. +// +odb::mssql::database dbA ("test", + "secret", + "example_db" + "tcp:onega,1435", + "SQL Server Native Client 10.0"); + </pre> + + + <p>The forth constructor allows us to pass a custom ODBC connection + string that provides all the information necessary to connect to + the database. Note also that all the other constructors have the + <code>extra_connect_string</code> argument which can be used to + specify additional ODBC connection attributes. For more information + on the format of the ODBC connection string, refer to the SQL + Server Native Client ODBC driver documentation.</p> + + <p>The last constructor extracts the database parameters + from the command line. The following options are recognized:</p> + + <pre class="terminal"> + --user | -U <login> + --password | -P <password> + --database | -d <name> + --server | -S <address> + --driver <name> + --options-file <file> + </pre> + + <p>The <code>--options-file</code> option allows us to specify some + or all of the database options in a file with each option appearing + on a separate line followed by a space and an option value.</p> + + <p>If the <code>erase</code> argument to this constructor is true, + then the above options are removed from the <code>argv</code> + array and the <code>argc</code> count is updated accordingly. + This is primarily useful if your application accepts other + options or arguments and you would like to get the SQL Server + options out of the <code>argv</code> array.</p> + + <p>This constructor throws the <code>odb::mssql::cli_exception</code> + exception if the SQL Server option values are missing or invalid. See + section <a href="#17.4">Section 17.4, "SQL Server Exceptions"</a> for + more information on this exception.</p> + + <p>The static <code>print_usage()</code> function prints the list of options + with short descriptions that are recognized by this constructor.</p> + + <p>Additionally, all the constructors have the <code>environment</code> + argument which allows us to provide a custom ODBC environment handle. + If this argument is not <code>NULL</code>, then the passed handle is + used in all the ODBC function calls made by this <code>database</code> + class instance. Note also that the <code>database</code> instance + does not assume ownership of the passed environment handle and this + handle should be valid for the lifetime of the <code>database</code> + instance.</p> + + <p>The last argument to all of the constructors is a pointer to a + connection factory. If we pass a non-<code>NULL</code> value, the + database instance assumes ownership of the factory instance. The + connection factory interface as well as the available implementations + are described in the next section.</p> + + <p>The set of accessor functions following the constructors allows us + to query the parameters of the <code>database</code> instance.</p> + + <p>The <code>connection()</code> function returns a pointer to the + SQL Server database connection encapsulated by the + <code>odb::mssql::connection</code> class. For more information + on <code>mssql::connection</code>, refer to <a href="#17.3">Section + 17.3, "SQL Server Connection and Connection Factory"</a>.</p> + + <h2><a name="17.3">17.3 SQL Server Connection and Connection Factory</a></h2> + + <p>The <code>mssql::connection</code> class has the following interface:</p> + + <pre class="c++"> +namespace odb +{ + namespace mssql + { + class connection: public odb::connection + { + public: + connection (database&); + connection (database&, SQLHDBC handle); + + SQLHDBC + handle (); + + details::buffer& + long_data_buffer (); + }; + + typedef details::shared_ptr<connection> connection_ptr; + } +} + </pre> + + <p>For more information on the <code>odb::connection</code> interface, refer + to <a href="#3.5">Section 3.5, "Connections"</a>. The first overloaded + <code>mssql::connection</code> constructor creates a new ODBC connection. + The created connection is configured to use the manual commit mode with + multiple active result sets (MARS) enabled. The second constructor allows + us to create a <code>connection</code> instance by providing an already + established ODBC connection. Note that the <code>connection</code> + instance assumes ownership of this handle. The <code>handle()</code> + accessor returns the underlying ODBC connection handle associated with + the <code>connection</code> instance.</p> + + <p>Additionally, each <code>connection</code> instance maintains a long + data buffer. This buffer is used by the SQL Server ODB runtime + as an intermediate storage for piecewise handling of long data. + By default, the long data buffer has zero initial capacity and is + expanded to 4096 bytes when the first long data operation is performed. + If your application requires a bigger or smaller long data buffer, + you can specify a custom capacity using the <code>long_data_buffer()</code> + accessor.</p> + + <p>The <code>mssql::connection_factory</code> abstract class has the + following interface:</p> + + <pre class="c++"> +namespace odb +{ + namespace mssql + { + class connection_factory + { + public: + virtual void + database (database&) = 0; + + virtual connection_ptr + connect () = 0; + }; + } +} + </pre> + + <p>The <code>database()</code> function is called when a connection + factory is associated with a database instance. This happens in + the <code>odb::mssql::database</code> class constructors. The + <code>connect()</code> function is called whenever a database + connection is requested.</p> + + <p>The two implementations of the <code>connection_factory</code> + interface provided by the SQL Server ODB runtime are + <code>new_connection_factory</code> and + <code>connection_pool_factory</code>. You will need to include + the <code><odb/mssql/connection-factory.hxx></code> + header file to make the <code>connection_factory</code> interface + and these implementation classes available in your application.</p> + + <p>The <code>new_connection_factory</code> class creates a new + connection whenever one is requested. When a connection is no + longer needed, it is released and closed. The + <code>new_connection_factory</code> class has the following + interface:</p> + + <pre class="c++"> +namespace odb +{ + namespace mssql + { + class new_connection_factory: public connection_factory + { + public: + new_connection_factory (); + }; +}; + </pre> + + <p>The <code>connection_pool_factory</code> class implements a + connection pool. It has the following interface:</p> + + <pre class="c++"> +namespace odb +{ + namespace mssql + { + class connection_pool_factory: public connection_factory + { + public: + connection_pool_factory (std::size_t max_connections = 0, + std::size_t min_connections = 0); + + protected: + class pooled_connection: public connection + { + public: + pooled_connection (database_type&); + pooled_connection (database_type&, SQLHDBC handle); + }; + + typedef details::shared_ptr<pooled_connection> pooled_connection_ptr; + + virtual pooled_connection_ptr + create (); + }; +}; + </pre> + + <p>The <code>max_connections</code> argument in the + <code>connection_pool_factory</code> constructor specifies the maximum + number of concurrent connections that this pool factory will + maintain. Similarly, the <code>min_connections</code> argument + specifies the minimum number of available connections that + should be kept open.</p> + + <p>Whenever a connection is requested, the pool factory first + checks if there is an unused connection that can be returned. + If there is none, the pool factory checks the + <code>max_connections</code> value to see if a new connection + can be created. If the total number of connections maintained + by the pool is less than this value, then a new connection is + created and returned. Otherwise, the caller is blocked until + a connection becomes available.</p> + + <p>When a connection is released, the pool factory first checks + if there are blocked callers waiting for a connection. If so, then + one of them is unblocked and is given the connection. Otherwise, + the pool factory checks whether the total number of connections + maintained by the pool is greater than the <code>min_connections</code> + value. If that's the case, the connection is closed. Otherwise, the + connection is added to the pool of available connections to be + returned on the next request. In other words, if the number of + connections maintained by the pool exceeds <code>min_connections</code> + and there are no callers waiting for a new connection, + the pool will close the excess connections.</p> + + <p>If the <code>max_connections</code> value is 0, then the pool will + create a new connection whenever all of the existing connections + are in use. If the <code>min_connections</code> value is 0, then + the pool will never close a connection and instead maintain all + the connections that were ever created.</p> + + <p>The <code>create()</code> virtual function is called whenever the + pool needs to create a new connection. By deriving from the + <code>connection_pool_factory</code> class and overriding this + function we can implement custom connection establishment + and configuration.</p> + + <p>If you pass <code>NULL</code> as the connection factory to one of the + <code>database</code> constructors, then the + <code>connection_pool_factory</code> instance will be created by default + with the min and max connections values set to <code>0</code>. The + following code fragment shows how we can pass our own connection factory + instance:</p> + + <pre class="c++"> +#include <odb/database.hxx> + +#include <odb/mssql/database.hxx> +#include <odb/mssql/connection-factory.hxx> + +int +main (int argc, char* argv[]) +{ + auto_ptr<odb::mssql::connection_factory> f ( + new odb::mssql::connection_pool_factory (20)); + + auto_ptr<odb::database> db ( + new mssql::database (argc, argv, false, "", 0, f)); +} + </pre> + + <h2><a name="17.4">17.4 SQL Server Exceptions</a></h2> + + <p>The SQL Server ODB runtime library defines the following + SQL Server-specific exceptions:</p> + + <pre class="c++"> +namespace odb +{ + namespace mssql + { + class database_exception: odb::database_exception + { + public: + class record + { + public: + SQLINTEGER + error () const; + + const std::string& + sqlstate () const; + + const std::string& + message () const; + }; + + typedef std::vector<record> records; + + typedef records::size_type size_type; + typedef records::const_iterator iterator; + + iterator + begin () const; + + iterator + end () const; + + size_type + size () const; + + virtual const char* + what () const throw (); + }; + + class cli_exception: odb::exception + { + public: + virtual const char* + what () const throw (); + }; + + class long_data_reload: odb::exception + { + public: + virtual const char* + what () const throw (); + }; + } +} + </pre> + + <p>You will need to include the <code><odb/mssql/exceptions.hxx></code> + header file to make these exceptions available in your application.</p> + + <p>The <code>odb::mssql::database_exception</code> is thrown if + an SQL Server database operation fails. The SQL Server-specific error + information is stored as a series of records, each containing + the error code as a signed 4-byte integer, the SQLSTATE code, + and the message string. All this information is also combined + and returned in a human-readable form by the <code>what()</code> + function.</p> + + <p>The <code>odb::mssql::cli_exception</code> is thrown by the + command line parsing constructor of the <code>odb::mssql::database</code> + class if the SQL Server option values are missing or invalid. The + <code>what()</code> function returns a human-readable description + of an error.</p> + + <p>The <code>odb::mssql::long_data_reload</code> is thrown if an + attempt is made to re-load an object or view with long data as + part of a query result iteration. For more information, refer + to <a href="#17.1">Section 17.1, "SQL Server Type Mapping"</a>.</p> + + <h2><a name="17.5">17.5 SQL Server Limitations</a></h2> + + <p>The following sections describe SQL Server-specific limitations imposed + by the current SQL Server and ODB runtime versions.</p> + + <h3><a name="17.5.1">17.5.1 Query Result Caching</a></h3> + + <p>SQL Server ODB runtime implementation does not perform query result + caching (<a href="#4.4">Section 4.4, "Query Result"</a>) even when + explicitly requested. The ODBC API and the SQL Server Native Client ODBC + driver support interleaving execution of multiple prepared statements + on a single connection. As a result, it is possible to have multiple + uncached results and calls to other database functions do not invalidate + them. The only limitation of the uncached SQL Server results is the + unavailability of the <code>result::size()</code> function. If you + call this function on an SQL Server query result, then the + <code>odb::result_not_cached</code> exception (<a href="#3.13">Section + 3.13, "ODB Exceptions"</a>) is always thrown. Future versions of the + SQL Server ODB runtime library may add support for result caching.</p> + + <h3><a name="17.5.2">17.5.2 Foreign Key Constraints</a></h3> + + <p>ODB relies on standard SQL behavior which requires that foreign + key constraints checking is deferred until the transaction is + committed. The only behavior supported by SQL Server is to check + such constraints immediately. As a result, schemas generated by + the ODB compiler for SQL Server have foreign key definitions + commented out. They are retained only for documentation.</p> + + <h3><a name="17.5.3">17.5.3 Unique Constraint Violations</a></h3> + + <p>Due to the granularity of the ODBC error codes, it is impossible + to distinguish between the duplicate primary key and other unique + constraint violations. As a result, when making an object persistent, + the SQL Server ODB runtime will translate all unique constraint violation + errors to the <code>object_already_persistent</code> exception + (<a href="#3.13">Section 3.13, "ODB Exceptions"</a>).</p> + + <h3><a name="17.5.4">17.5.4 Multithreaded Windows Applications</a></h3> + + <p>Multithreaded Windows applications must use the + <code>_beginthread()</code>/<code>_beginthreadex()</code> and + <code>_endthread()</code>/<code>_endthreadex()</code> CRT functions + instead of the <code>CreateThread()</code> and <code>EndThread()</code> + Win32 functions to start and terminate threads. This is a limitation of + the ODBC implementation on Windows.</p> + + <h3><a name="17.5.5">17.5.5 Affected Row Count and DDL Statements</a></h3> + + <p>SQL Server always returns zero as the number of affected rows + for DDL statements. In particular, this means that the + <code>database::execute()</code> (<a href="#3.11">Section 3.11, + "Executing Native SQL Statements"</a>) function will always + return zero for such statements.</p> + + <h3><a name="17.5.6">17.5.6 Long Data and Automatically Assigned Object Ids</a></h3> + + <p>SQL Server 2005 has a bug that causes it to fail on an <code>INSERT</code> + statement with the <code>OUTPUT</code> clause (used to return + automatically assigned object ids) if one of the inserted columns + is long data. The symptom of this bug in ODB is an exception thrown + by the <code>database::persist()</code> function when used on an + object that contains long data and has an automatically assigned + object id. The error message reads "This operation conflicts with + another pending operation on this transaction. The operation failed."</p> + + <p>ODB includes a workaround for this bug which uses a less efficient + method to obtain automatically assigned object ids for objects that + contain long data. To enable this workaround you need to specify + that the generated code will be used with SQL Server 2005 or later + by passing the <code>--mssql-server-version 9.0</code> ODB + compiler option.</p> + + <!-- PART --> @@ -13185,9 +14137,9 @@ CREATE TABLE Employee ( and libraries. It consists of the following chapters.</p> <table class="toc"> - <tr><th>17</th><td><a href="#17">Profiles Introduction</a></td></tr> - <tr><th>18</th><td><a href="#18">Boost Profile</a></td></tr> - <tr><th>19</th><td><a href="#19">Qt Profile</a></td></tr> + <tr><th>18</th><td><a href="#18">Profiles Introduction</a></td></tr> + <tr><th>19</th><td><a href="#19">Boost Profile</a></td></tr> + <tr><th>20</th><td><a href="#20">Qt Profile</a></td></tr> </table> @@ -13195,7 +14147,7 @@ CREATE TABLE Employee ( <hr class="page-break"/> - <h1><a name="17">17 Profiles Introduction</a></h1> + <h1><a name="18">18 Profiles Introduction</a></h1> <p>ODB profiles are a generic mechanism for integrating ODB with widely-used C++ frameworks and libraries. A profile provides glue @@ -13249,7 +14201,7 @@ odb --profile boost/date-time ... <hr class="page-break"/> - <h1><a name="18">18 Boost Profile</a></h1> + <h1><a name="19">19 Boost Profile</a></h1> <p>The ODB profile implementation for Boost is provided by the <code>libodb-boost</code> library and consists of multiple sub-profiles @@ -13274,7 +14226,7 @@ odb --profile boost/date-time ... that can be thrown by the Boost sub-profiles are described in the following sections.</p> - <h2><a name="18.1">18.1 Smart Pointers Library</a></h2> + <h2><a name="19.1">19.1 Smart Pointers Library</a></h2> <p>The <code>smart-ptr</code> sub-profile provides persistence support for a subset of smart pointers from the Boost @@ -13313,7 +14265,7 @@ class person "Lazy Pointers"</a>. The following example shows how we can use these smart pointers to establish a relationship between persistent objects.</p> -<pre class="c++"> + <pre class="c++"> class employee; #pragma db object @@ -13333,7 +14285,7 @@ class employee #pragma db not_null boost::shared_ptr<position> position_; }; -</pre> + </pre> <p>Besides providing persistence support for the above smart pointers, the <code>smart-ptr</code> sub-profile also changes the default @@ -13344,7 +14296,7 @@ class employee this behavior, add the <code>--default-pointer</code> option specifying the alternative pointer type after the <code>--profile</code> option.</p> - <h2><a name="18.2">18.2 Unordered Containers Library</a></h2> + <h2><a name="19.2">19.2 Unordered Containers Library</a></h2> <p>The <code>unordered</code> sub-profile provides persistence support for the containers from the Boost <code>unordered</code> library. To enable @@ -13361,16 +14313,16 @@ class employee the <code>unordered_set</code> container may be used within a persistent object.</p> -<pre class="c++"> + <pre class="c++"> #pragma db object class person { ... boost::unordered_set<std::string> emails_; }; -</pre> + </pre> - <h2><a name="18.3">18.3 Optional Library</a></h2> + <h2><a name="19.3">19.3 Optional Library</a></h2> <p>The <code>optional</code> sub-profile provides persistence support for the <code>boost::optional</code> container from the Boost @@ -13402,7 +14354,7 @@ class person this profile is used, the <code>NULL</code> values are automatically enabled for data members of the <code>boost::optional</code> type.</p> - <h2><a name="18.4">18.4 Date Time Library</a></h2> + <h2><a name="19.4">19.4 Date Time Library</a></h2> <p>The <code>date-time</code> sub-profile provides persistence support for a subset of types from the Boost <code>date_time</code> library. It is @@ -13427,20 +14379,20 @@ class person sub-sections that follow. The example below shows how <code>gregorian::date</code> may be used within a persistent object.</p> -<pre class="c++"> + <pre class="c++"> #pragma db object class person { ... boost::gregorian::date date_of_birth_; }; -</pre> + </pre> <p>The concrete exceptions that can be thrown by the <code>date-time</code> sub-profile implementation are presented below.</p> -<pre class="c++"> + <pre class="c++"> namespace odb { namespace boost @@ -13461,7 +14413,7 @@ namespace odb } } } -</pre> + </pre> <p>You will need to include the <code><odb/boost/date-time/exceptions.hxx></code> header file to @@ -13475,7 +14427,7 @@ namespace odb exceptions are thrown are database system dependent and are discussed in more detail in the following sub-sections.</p> - <h3><a name="18.4.1">18.4.1 MySQL Database Type Mapping</a></h3> + <h3><a name="19.4.1">19.4.1 MySQL Database Type Mapping</a></h3> <p>The following table summarizes the default mapping between the currently supported Boost <code>date_time</code> types and the MySQL database @@ -13518,7 +14470,7 @@ namespace odb (<a href="#12.4.3">Section 12.4.3, "<code>type</code>"</a>), as shown in the following example:</p> -<pre class="c++"> + <pre class="c++"> #pragma db object class person { @@ -13526,7 +14478,7 @@ class person #pragma db type("TIMESTAMP") not_null boost::posix_time::ptime updated_; }; -</pre> + </pre> <p>Some valid Boost date-time values cannot be stored in a MySQL database. An attempt to persist any Boost date-time special value other than @@ -13536,7 +14488,7 @@ class person the <code>out_of_range</code> exception. Refer to the MySQL documentation for more information on the MySQL data type ranges.</p> - <h3><a name="18.4.2">18.4.2 SQLite Database Type Mapping</a></h3> + <h3><a name="19.4.2">19.4.2 SQLite Database Type Mapping</a></h3> <p>The following table summarizes the default mapping between the currently supported Boost <code>date_time</code> types and the SQLite database @@ -13582,7 +14534,7 @@ class person <code>db type</code> pragma (<a href="#12.4.3">Section 12.4.3, "<code>type</code>"</a>), as shown in the following example:</p> -<pre class="c++"> + <pre class="c++"> #pragma db object class person { @@ -13590,7 +14542,7 @@ class person #pragma db type("INTEGER") boost::gregorian::date born_; }; -</pre> + </pre> <!-- @@ -13614,7 +14566,7 @@ class person will result in the <code>out_of_range</code> exception.</p> - <h3><a name="18.4.3">18.4.3 PostgreSQL Database Type Mapping</a></h3> + <h3><a name="19.4.3">19.4.3 PostgreSQL Database Type Mapping</a></h3> <p>The following table summarizes the default mapping between the currently supported Boost <code>date_time</code> types and the PostgreSQL database @@ -13665,7 +14617,7 @@ class person result in the <code>special_value</code> exception.</p> - <h3><a name="18.4.4">18.4.4 Oracle Database Type Mapping</a></h3> + <h3><a name="19.4.4">19.4.4 Oracle Database Type Mapping</a></h3> <p>The following table summarizes the default mapping between the currently supported Boost <code>date_time</code> types and the Oracle database @@ -13709,11 +14661,83 @@ class person the <code>special_value</code> exception.</p> + <h3><a name="19.4.5">19.4.5 SQL Server Database Type Mapping</a></h3> + + <p>The following table summarizes the default mapping between the currently + supported Boost <code>date_time</code> types and the SQL Server database + types.</p> + + <!-- border="1" is necessary for html2ps --> + <table id="mapping" border="1"> + <tr> + <th>Boost <code>date_time</code> Type</th> + <th>SQL Server Type</th> + <th>Default <code>NULL</code> Semantics</th> + </tr> + + <tr> + <td><code>gregorian::date</code></td> + <td><code>DATE</code></td> + <td><code>NULL</code></td> + </tr> + + <tr> + <td><code>posix_time::ptime</code></td> + <td><code>DATETIME2</code></td> + <td><code>NULL</code></td> + </tr> + + <tr> + <td><code>posix_time::time_duration</code></td> + <td><code>TIME</code></td> + <td><code>NULL</code></td> + </tr> + </table> + + <p>The Boost special value <code>date_time::not_a_date_time</code> is stored + as a <code>NULL</code> value in an SQL Server database.</p> + + <p>Note that the <code>DATE</code>, <code>TIME</code>, and + <code>DATETIME2</code> types are only available in SQL Server 2008 and + later. SQL Server 2005 only supports the <code>DATETIME</code> and + <code>SMALLDATETIME</code> date-time types and the new types are + also unavailable when connecting to an SQL Server 2008 or + later with the SQL Server 2005 Native Client ODBC driver.</p> + + <p>The <code>date-time</code> sub-profile implementation provides + support for mapping <code>posix_time::ptime</code> to the + <code>DATETIME</code> and <code>SMALLDATETIME</code> types, + however, this mapping has to be explicitly requested using the + <code>db type</code> pragma (<a href="#12.4.3">Section 12.4.3, + "<code>type</code>"</a>), as shown in the following example:</p> + + <pre class="c++"> +#pragma db object +class person +{ + ... + #pragma db type("DATETIME") + boost::posix_time::ptime updated_; +}; + </pre> + + <p>Some valid Boost date-time values cannot be stored in an SQL Server + database. An attempt to persist a <code>gregorian::date</code>, + <code>posix_time::ptime</code>, or <code>posix_time::time_duration</code> + value representing any special value other than + <code>date_time::not_a_date_time</code> will result in the + <code>special_value</code> exception. The range of the <code>TIME</code> + type in SQL server is from <code>00:00:00.0000000</code> to + <code>23:59:59.9999999</code>. An attempt to persist a + <code>posix_time::time_duration</code> value out of this range will + result in the <code>value_out_of_range</code> exception.</p> + + <!-- CHAPTER --> <hr class="page-break"/> - <h1><a name="19">19 Qt Profile</a></h1> + <h1><a name="20">20 Qt Profile</a></h1> <p>The ODB profile implementation for Qt is provided by the <code>libodb-qt</code> library and consists of multiple sub-profiles @@ -13739,7 +14763,7 @@ class person that can be thrown by the Qt sub-profiles are described in the following sections.</p> - <h2><a name="19.1">19.1 Basic Types</a></h2> + <h2><a name="20.1">20.1 Basic Types</a></h2> <p>The <code>basic</code> sub-profile provides persistence support for basic types defined by Qt. To enable only this profile, pass @@ -13752,16 +14776,16 @@ class person sub-sections that follow. The example below shows how <code>QString</code> may be used within a persistent object.</p> -<pre class="c++"> + <pre class="c++"> #pragma db object class Person { ... QString name_; }; -</pre> + </pre> - <h3><a name="19.1.1">19.1.1 MySQL Database Type Mapping</a></h3> + <h3><a name="20.1.1">20.1.1 MySQL Database Type Mapping</a></h3> <p>The following table summarizes the default mapping between the currently supported basic Qt types and the MySQL database types.</p> @@ -13792,7 +14816,7 @@ class Person function returns <code>true</code>.</p> <p>Note also that the <code>QString</code> type is mapped - differently depending on whether the member of this type + differently depending on whether a member of this type is an object id or not. If the member is an object id, then for this member <code>QString</code> is mapped to the <code>VARCHAR(255)</code> MySQL type. Otherwise, @@ -13818,7 +14842,7 @@ class Person </pre> - <h3><a name="19.1.2">19.1.2 SQLite Database Type Mapping</a></h3> + <h3><a name="20.1.2">20.1.2 SQLite Database Type Mapping</a></h3> <p>The following table summarizes the default mapping between the currently supported basic Qt types and the SQLite database types.</p> @@ -13848,7 +14872,7 @@ class Person are stored as a NULL value if their <code>isNull()</code> member function returns <code>true</code>.</p> - <h3><a name="19.1.3">19.1.3 PostgreSQL Database Type Mapping</a></h3> + <h3><a name="20.1.3">20.1.3 PostgreSQL Database Type Mapping</a></h3> <p>The following table summarizes the default mapping between the currently supported basic Qt types and the PostgreSQL database types.</p> @@ -13897,7 +14921,7 @@ class Person }; </pre> - <h3><a name="19.1.4">19.1.4 Oracle Database Type Mapping</a></h3> + <h3><a name="20.1.4">20.1.4 Oracle Database Type Mapping</a></h3> <p>The following table summarizes the default mapping between the currently supported basic Qt types and the Oracle database types.</p> @@ -13950,7 +14974,68 @@ class Person }; </pre> - <h2><a name="19.2">19.2 Smart Pointers</a></h2> + <h3><a name="20.1.5">20.1.5 SQL Server Database Type Mapping</a></h3> + + <p>The following table summarizes the default mapping between the currently + supported basic Qt types and the SQL Server database types.</p> + + <!-- border="1" is necessary for html2ps --> + <table id="mapping" border="1"> + <tr> + <th>Qt Type</th> + <th>SQL Server Type</th> + <th>Default <code>NULL</code> Semantics</th> + </tr> + + <tr> + <td><code>QString</code></td> + <td><code>VARCHAR(512)/VARCHAR(256)</code></td> + <td><code>NULL</code></td> + </tr> + + <tr> + <td><code>QByteArray</code></td> + <td><code>VARBINARY(max)</code></td> + <td><code>NULL</code></td> + </tr> + </table> + + <p>Instances of the <code>QString</code> and <code>QByteArray</code> types + are stored as a NULL value if their <code>isNull()</code> member + function returns <code>true</code>.</p> + + <p>Note also that the <code>QString</code> type is mapped + differently depending on whether a member of this type + is an object id or not. If the member is an object id, + then for this member <code>QString</code> is mapped + to the <code>VARCHAR(256)</code> SQL Server type. Otherwise, + it is mapped to <code>VARCHAR(512)</code>.</p> + + <p>The <code>basic</code> sub-profile also provides support + for mapping <code>QString</code> to the <code>CHAR</code>, + <code>NCHAR</code>, <code>NVARCHAR</code>, <code>TEXT</code>, and + <code>NTEXT</code> SQL Server types, and for mapping + <code>QByteArray</code> to the <code>BINARY</code> and + <code>IMAGE</code> SQL Server types. However, these alternative + mappings have to be explicitly requested using the <code>db type</code> + pragma (<a href="#12.4.3">Section 12.4.3, "type"</a>), as shown in the + following example:</p> + + <pre class="c++"> +#pragma db object +class Person +{ + ... + + #pragma db type("NVARCHAR(256)") not_null + QString firstName_; + + #pragma db type("BINARY(16)") null + QByteArray uuid_; +}; + </pre> + + <h2><a name="20.2">20.2 Smart Pointers</a></h2> <p>The <code>smart-ptr</code> sub-profile provides persistence support the Qt smart pointers. To enable only this profile, pass @@ -13988,7 +15073,7 @@ class person "Lazy Pointers"</a>. The following example shows how we can use these smart pointers to establish a relationship between persistent objects.</p> -<pre class="c++"> + <pre class="c++"> class Employee; #pragma db object @@ -14008,7 +15093,7 @@ class Employee #pragma db not_null QSharedPointer<Position> position_; }; -</pre> + </pre> <p>Besides providing persistence support for the above smart pointers, the <code>smart-ptr</code> sub-profile also changes the default @@ -14019,7 +15104,7 @@ class Employee this behavior, add the <code>--default-pointer</code> option specifying the alternative pointer type after the <code>--profile</code> option.</p> - <h2><a name="19.3">19.3 Containers Library</a></h2> + <h2><a name="20.3">20.3 Containers Library</a></h2> <p>The <code>container</code> sub-profile provides persistence support for Qt containers. To enable only this profile, pass @@ -14035,16 +15120,16 @@ class Employee The following example shows how the <code>QSet</code> container may be used within a persistent object.</p> -<pre class="c++"> + <pre class="c++"> #pragma db object class Person { ... QSet<QString> emails_; }; -</pre> + </pre> - <h2><a name="19.4">19.4 Date Time Types</a></h2> + <h2><a name="20.4">20.4 Date Time Types</a></h2> <p>The <code>date-time</code> sub-profile provides persistence support for the Qt date-time types. To enable only this profile, pass @@ -14057,20 +15142,20 @@ class Person discussed in the sub-sections that follow. The example below shows how <code>QDate</code> may be used within a persistent object.</p> -<pre class="c++"> + <pre class="c++"> #pragma db object class Person { ... QDate dateOfBirth_; }; -</pre> + </pre> <p>The single concrete exception that can be thrown by the <code>date-time</code> sub-profile implementation is presented below.</p> -<pre class="c++"> + <pre class="c++"> namespace odb { namespace qt @@ -14085,7 +15170,7 @@ namespace odb } } } -</pre> + </pre> <p>You will need to include the <code><odb/qt/date-time/exceptions.hxx></code> header file to @@ -14097,7 +15182,7 @@ namespace odb system dependent and is discussed in more detail in the following sub-sections.</p> - <h3><a name="19.4.1">19.4.1 MySQL Database Type Mapping</a></h3> + <h3><a name="20.4.1">20.4.1 MySQL Database Type Mapping</a></h3> <p>The following table summarizes the default mapping between the currently supported Qt date-time types and the MySQL database types.</p> @@ -14140,7 +15225,7 @@ namespace odb (<a href="#12.4.3">Section 12.4.3, "<code>type</code>"</a>), as shown in the following example:</p> -<pre class="c++"> + <pre class="c++"> #pragma db object class Person { @@ -14148,7 +15233,7 @@ class Person #pragma db type("TIMESTAMP") not_null QDateTime updated_; }; -</pre> + </pre> <p>Some valid Qt date-time values cannot be stored in a MySQL database. An attempt to persist a Qt date-time value that is out of the MySQL type @@ -14156,7 +15241,7 @@ class Person the MySQL documentation for more information on the MySQL data type ranges.</p> - <h3><a name="19.4.2">19.4.2 SQLite Database Type Mapping</a></h3> + <h3><a name="20.4.2">20.4.2 SQLite Database Type Mapping</a></h3> <p>The following table summarizes the default mapping between the currently supported Qt date-time types and the SQLite database types.</p> @@ -14202,7 +15287,7 @@ class Person (<a href="#12.4.3">Section 12.4.3, "<code>type</code>"</a>), as shown in the following example:</p> -<pre class="c++"> + <pre class="c++"> #pragma db object class Person { @@ -14210,7 +15295,7 @@ class Person #pragma db type("INTEGER") QDate born_; }; -</pre> + </pre> <p>Some valid Qt date-time values cannot be stored in an SQLite database. An attempt to persist any Qt date-time value representing a negative UNIX @@ -14218,7 +15303,7 @@ class Person epoch) as an SQLite <code>INTEGER</code> will result in the <code>out_of_range</code> exception.</p> - <h3><a name="19.4.3">19.4.3 PostgreSQL Database Type Mapping</a></h3> + <h3><a name="20.4.3">20.4.3 PostgreSQL Database Type Mapping</a></h3> <p>The following table summarizes the default mapping between the currently supported Qt date-time types and the PostgreSQL database types.</p> @@ -14254,7 +15339,7 @@ class Person <code>QDateTime</code> types are stored as a NULL value if their <code>isNull()</code> member function returns true.</p> - <h3><a name="19.4.4">19.4.4 Oracle Database Type Mapping</a></h3> + <h3><a name="20.4.4">20.4.4 Oracle Database Type Mapping</a></h3> <p>The following table summarizes the default mapping between the currently supported Qt date-time types and the Oracle database types.</p> @@ -14290,6 +15375,66 @@ class Person <code>QDateTime</code> types are stored as a NULL value if their <code>isNull()</code> member function returns true.</p> + <h3><a name="20.4.5">20.4.5 SQL Server Database Type Mapping</a></h3> + + <p>The following table summarizes the default mapping between the currently + supported Qt date-time types and the SQL Server database types.</p> + + <!-- border="1" is necessary for html2ps --> + <table id="mapping" border="1"> + <tr> + <th>Qt Date Time Type</th> + <th>SQL Server Type</th> + <th>Default <code>NULL</code> Semantics</th> + </tr> + + <tr> + <td><code>QDate</code></td> + <td><code>DATE</code></td> + <td><code>NULL</code></td> + </tr> + + <tr> + <td><code>QTime</code></td> + <td><code>TIME(3)</code></td> + <td><code>NULL</code></td> + </tr> + + <tr> + <td><code>QDateTime</code></td> + <td><code>DATETIME2(3)</code></td> + <td><code>NULL</code></td> + </tr> + </table> + + <p>Instances of the <code>QDate</code>, <code>QTime</code>, and + <code>QDateTime</code> types are stored as a NULL value if their + <code>isNull()</code> member function returns true.</p> + + <p>Note that the <code>DATE</code>, <code>TIME</code>, and + <code>DATETIME2</code> types are only available in SQL Server 2008 and + later. SQL Server 2005 only supports the <code>DATETIME</code> and + <code>SMALLDATETIME</code> date-time types and the new types are + also unavailable when connecting to an SQL Server 2008 or + later with the SQL Server 2005 Native Client ODBC driver.</p> + + <p>The <code>date-time</code> sub-profile implementation provides + support for mapping <code>QDateTime</code> to the <code>DATETIME</code> + and <code>SMALLDATETIME</code> types, however, this mapping has to + be explicitly requested using the <code>db type</code> pragma + (<a href="#12.4.3">Section 12.4.3, "<code>type</code>"</a>), as + shown in the following example:</p> + + <pre class="c++"> +#pragma db object +class person +{ + ... + #pragma db type("DATETIME") + QDateTime updated_; +}; + </pre> + </div> </div> |