types.md 9.48 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11
# Data Types

## Static binding

The static binding for types is most useful when the types used in the database are known at compile time - this was already presented above with the help of `into` and `use` functions.

The following types are currently supported for use with `into` and `use` expressions:

* `char` (for character values)
* `short`, `int`, `unsigned long`, `long long`, `double` (for numeric values)
* `std::string` (for string values)
12
* `std::tm` (for datetime values)
13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
* `soci::statement` (for nested statements and PL/SQL cursors)
* `soci::blob` (for Binary Large OBjects)
* `soci::row_id` (for row identifiers)

See the test code that accompanies the library to see how each of these types is used.

### Static binding for bulk operations

Bulk inserts, updates, and selects are supported through the following `std::vector` based into and use types:

* `std::vector<char>`
* `std::vector<short>`
* `std::vector<int>`
* `std::vector<unsigned long>`
* `std::vector<long long>`
* `std::vector<double>`
* `std::vector<std::string>`
* `std::vector<std::tm>`

Use of the vector based types mirrors that of the standard types, with the size of the vector used to specify the number of records to process at a time.
See below for examples.

Bulk operations are supported also for `std::vector`s of the user-provided types that have appropriate conversion routines defines.

## Dynamic binding

For certain applications it is desirable to be able to select data from arbitrarily structured tables (e.g. via "`select * from ...`") and format the resulting data based upon its type.

SOCI supports binding dynamic resultset through the `soci::row` and `soci::column_properties` classes.

Data is selected into a `row` object, which holds `column_properties` objects describing the attributes of data contained in each column.
Once the data type for each column is known, the data can be formatted appropriately.

For example, the code below creates an XML document from a selected row of data from an arbitrary table:

48 49 50
```cpp
row r;
sql << "select * from some_table", into(r);
51

52 53 54 55 56
std::ostringstream doc;
doc << "<row>" << std::endl;
for(std::size_t i = 0; i != r.size(); ++i)
{
    const column_properties & props = r.get_properties(i);
57

58
    doc << '<' << props.get_name() << '>';
59

60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80
    switch(props.get_data_type())
    {
    case dt_string:
        doc << r.get<std::string>(i);
        break;
    case dt_double:
        doc << r.get<double>(i);
        break;
    case dt_integer:
        doc << r.get<int>(i);
        break;
    case dt_long_long:
        doc << r.get<long long>(i);
        break;
    case dt_unsigned_long_long:
        doc << r.get<unsigned long long>(i);
        break;
    case dt_date:
        std::tm when = r.get<std::tm>(i);
        doc << asctime(&when);
        break;
81
    }
82 83 84 85 86

    doc << "</" << props.get_name() << '>' << std::endl;
}
doc << "</row>";
```
87 88 89 90 91 92 93 94 95 96 97 98 99 100 101

The type `T` parameter that should be passed to `row::get<T>()` depends on the SOCI data type that is returned from `column_properties::get_data_type()`.

`row::get<T>()` throws an exception of type `std::bad_cast` if an incorrect type `T` is requested.

| SOCI Data Type | `row::get<T>` specialization |
|----------------|------------------------------|
| `dt_double`    | `double`                     |
| `dt_integer`   | `int`                        |
| `dt_long_long` | `long long`                  |
| `dt_unsigned_long_long` | `unsigned long long`|
| `dt_string`    | `std::string`                |
| `dt_date`      | `std::tm`                    |

The mapping of underlying database column types to SOCI datatypes is database specific.
102
See the [backend documentation](backends/index.md) for details.
103 104 105

The `row` also provides access to indicators for each column:

106 107 108 109 110 111 112 113
```cpp
row r;
sql << "select name from some_table where id = 1", into(r);
if (r.get_indicator(0) != soci::i_null)
{
    std::cout << r.get<std::string>(0);
}
```
114 115 116

It is also possible to extract data from the `row` object using its stream-like interface, where each extracted variable should have matching type respective to its position in the chain:

117 118 119
```cpp
row r;
sql << "select name, address, age from persons where id = 123", into(r);
120

121 122
string name, address;
int age;
123

124 125
r >> name >> address >> age;
```
126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152

Note, however, that this interface is *not* compatible with the standard `std::istream` class and that it is only possible to extract a single row at a time - for "safety" reasons the row boundary is preserved and it is necessary to perform the `fetch` operation explicitly for each consecutive row.

## User-defined C++ types

SOCI can be easily extended with support for user-defined datatypes.

The extension mechanism relies on appropriate specialization of the `type_conversion` structure that converts to and from one of the following SOCI base types:

* `double`
* `int`
* `long long`
* `unsigned long long`
* `std::string`
* `char`
* `std::tm`

There are three required class members for a valid `type_conversion` specialization:

* the `base_type` type definition, aliasing either one of the base types *or another ser-defined type*
* the `from_base()` static member function, converting from the base type
* the `to_base()` static member function, converting to the base type

Note that no database-specific code is required to define user conversion.

The following example shows how the user can extend SOCI to support his own type `MyInt`, which here is some wrapper for the fundamental `int` type:

153 154 155 156 157 158
```cpp
class MyInt
{
public:
    MyInt() {}
    MyInt(int i) : i_(i) {}
159

160 161
    void set(int i) { i_ = i; }
    int get() const { return i_; }
162

163 164 165
private:
    int i_;
};
166

167 168 169 170
namespace soci
{
    template <<
    struct type_conversion<MyInt>
171
    {
172
        typedef int base_type;
173

174 175 176
        static void from_base(int i, indicator ind, MyInt & mi)
        {
            if (ind == i_null)
177
            {
178
                throw soci_error("Null value not allowed for this type");
179 180
            }

181 182 183 184 185 186 187 188 189 190 191
            mi.set(i);
        }

        static void to_base(const MyInt & mi, int & i, indicator & ind)
        {
            i = mi.get();
            ind = i_ok;
        }
    };
}
```
192 193 194

The above specialization for `soci::type_conversion<MyInt>` is enough to enable the following:

195 196
```cpp
MyInt i;
197

198
sql << "select count(*) from person", into(i);
199

200 201
cout << "We have " << i.get() << " persons in the database.\n";
```
202

203
Note that there is a number of types from the Boost library integrated with SOCI out of the box, see [Integration with Boost](boost.md) for complete description. Use these as examples of conversions for more complext data types.
204

205
Another possibility to extend SOCI with custom data types is to use the `into_type<T>` and `use_type<T>` class templates, which specializations can be user-provided. These specializations need to implement the interface defined by, respectively, the `into_type_base` and `use_type_base`
206 207 208 209 210 211 212 213 214 215 216 217 218
classes.

Note that when specializing these template classes the only convention is that when the indicator
variable is used (see below), it should appear in the second position. Please refer to the library source code to see how this is done for the standard types.

## Object-Relational Mapping

SOCI provides a class called `values` specifically to enable object-relational mapping via `type_conversion` specializations.

For example, the following code maps a `Person` object to and from a database table containing columns `"ID"`, `"FIRST_NAME"`, `"LAST_NAME"`, and `"GENDER"`.

Note that the mapping is non-invasive - the `Person` object itself does not contain any SOCI-specific code:

219 220 221 222 223 224 225 226 227 228 229 230 231
```cpp
struct Person
{
    int id;
    std::string firstName;
    std::string lastName;
    std::string gender;
};

namespace soci
{
    template<>
    struct type_conversion<Person>
232
    {
233
        typedef values base_type;
234

235
        static void from_base(values const & v, indicator /* ind */, Person & p)
236
        {
237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254
            p.id = v.get<int>("ID");
            p.firstName = v.get<std::string>("FIRST_NAME");
            p.lastName = v.get<std::string>("LAST_NAME");

            // p.gender will be set to the default value "unknown"
            // when the column is null:
            p.gender = v.get<std::string>("GENDER", "unknown");

            // alternatively, the indicator can be tested directly:
            // if (v.indicator("GENDER") == i_null)
            // {
            //     p.gender = "unknown";
            // }
            // else
            // {
            //     p.gender = v.get<std::string>("GENDER");
            // }
        }
255

256 257 258 259 260 261 262 263 264 265 266
        static void to_base(const Person & p, values & v, indicator & ind)
        {
            v.set("ID", p.id);
            v.set("FIRST_NAME", p.firstName);
            v.set("LAST_NAME", p.lastName);
            v.set("GENDER", p.gender, p.gender.empty() ? i_null : i_ok);
            ind = i_ok;
        }
    };
}
```
267 268 269

With the above `type_conversion` specialization in place, it is possible to use `Person` directly with SOCI:

270 271
```cpp
session sql(oracle, "service=db1 user=scott password=tiger");
272

273 274 275 276 277 278
Person p;
p.id = 1;
p.lastName = "Smith";
p.firstName = "Pat";
sql << "insert into person(id, first_name, last_name) "
        "values(:ID, :FIRST_NAME, :LAST_NAME)", use(p);
279

280 281 282 283 284
Person p1;
sql << "select * from person", into(p1);
assert(p1.id == 1);
assert(p1.firstName + p.lastName == "PatSmith");
assert(p1.gender == "unknown");
285

286 287 288 289
p.firstName = "Patricia";
sql << "update person set first_name = :FIRST_NAME "
        "where id = :ID", use(p);
```
290

291
Note: The `values` class is currently not suited for use outside of `type_conversion`specializations.
292
It is specially designed to facilitate object-relational mapping when used as shown above.