binding.md 6.41 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11
# Data Binding

SOCI provides mechanisms to bind local buffers for input and output data.

**Note:** The Oracle documentation uses two terms: *defining* (for instructing the library where the *output* data should go) and *binding* (for the *input* data and *input/output* PL/SQL parameters). For the sake of simplicity, SOCI uses the term *binding* for both of these.

## Binding output data (into)

The `into` expression is used to add binding information to
the statement:

12 13 14
```cpp
int count;
sql << "select count(*) from person", into(count);
15

16 17 18
string name;
sql << "select name from person where id = 7", into(name);
```
19 20 21

In the above examples, some data is retrieved from the database and transmitted *into* the given local variable.

Astrinus's avatar
Astrinus committed
22
There should be as many `into` elements as there are expected columns in the result (see [dynamic resultset binding](types.md#dynamic-binding) for the exception to this rule).
23 24 25 26 27

## Binding input data (use)

The `use` expression associates the SQL placeholder (written with colon) with the local data:

28 29 30 31
```cpp
int val = 7;
sql << "insert into numbers(val) values(:val)", use(val);
```
32 33 34 35 36

In the above statement, the first "val" is a column name (assuming that there is appropriate table `numbers` with this column), the second "val" (with colon) is a placeholder and its name is ignored here, and the third "val" is a name of local variable.

To better understand the meaning of each "val" above, consider also:

37 38 39 40
```cpp
int number = 7;
sql << "insert into numbers(val) values(:blabla)", use(number);
```
41 42 43 44 45

Both examples above will insert the value of some local variable into the table `numbers` - we say that the local variable is *used* in the SQL statement.

There should be as many `use` elements as there are parameters used in the SQL query.

46
### Object lifetime and immutability
47 48 49 50 51

SOCI assumes that local variables provided as `use` elements live at least as long at it takes to execute the whole statement.
In short statement forms like above, the statement is executed *sometime* at the end of the full expression and the whole process is driven by the invisible temporary object handled by the library.
If the data provided by user comes from another temporary variable, it might be possible for the compiler to arrange them in a way that the user data will be destroyed *before* the statement will have its chance to execute, referencing objects that no longer exist:

52 53
```cpp
// Dangerous code!
54

55
string getNameFromSomewhere();
56

57 58
sql << "insert into person(name) values(:n)", use(getNameFromSomewhere());
```
59 60 61

In the above example, the data passed to the database comes from the temporary variable that is a result of call to `getNameFromSomewhere` - this should be avoided and named variables should be used to ensure safe lifetime relations:

62 63
```cpp
// Safe code
64

65 66 67 68 69
string getNameFromSomewhere();

string name = getNameFromSomewhere();
sql << "insert into person(name) values(:n)", use(name);
```
70 71 72 73 74 75 76

It is still possible to provide `const` data for use elements.

Note that some database servers, like Oracle, allow PL/SQL procedures to modify their in/out parameters - this is detected by the SOCI library and an error is reported if the database attempts to modify the `use` element that holds `const` data.

The above example can be ultimately written in the following way:

77 78
```cpp
// Safe and efficient code
79

80
string getNameFromSomewhere();
81

82 83 84
string const& name = getNameFromSomewhere();
sql << "insert into person(name) values(:n)", use(name);
```
85 86 87 88 89

## Binding by position

If there is more output or input "holes" in the single statement, it is possible to use many `into` and `use` expressions, separated by commas, where each expression will be responsible for the consecutive "hole" in the statement:

90 91 92
```cpp
string firstName = "John", lastName = "Smith";
int personId = 7;
93

94
sql << "insert into person(id, firstname, lastname) values(:id, :fn, :ln)",
95 96
        use(personId), use(firstName), use(lastName);

97
sql << "select firstname, lastname from person where id = :id",
98
        into(firstName), into(lastName), use(personId);
99
```
100 101 102 103 104 105 106 107 108

In the code above, the order of "holes" in the SQL statement and the order of `into` and `use` expression should match.

## Binding by name

The SQL placeholders that have their names (with colon) can be bound by name to clearly associate the local variable with the given placeholder.

This explicit naming allows to use different order of elements:

109 110 111 112 113 114
```cpp
string firstName = "John", lastName = "Smith";
int personId = 7;
sql << "insert into person(id, firstname, lastname) values(:id, :fn, :ln)",
    use(firstName, "fn"), use(lastName, "ln"), use(personId, "id");
```
115 116 117

or bind the same local data to many "holes" at the same time:

118 119 120 121 122 123 124
```cpp
string addr = "...";
sql << "update person"
        " set mainaddress = :addr, contactaddress = :addr"
        " where id = 7",
        use(addr, "addr");
```
125 126 127 128 129 130 131 132

## Bulk operations

Bulk operations allow the user to bind, as into or use element, whole vectors of objects.
This allows the database backend to optimize access and data transfer and benefit from the fact that `std::vector` stores data in contiguous memory blocks (the actual optimization depends on the backend and the capability of the underlying data base server).

It is possible to `use` the vector as a data source:

133 134 135 136 137
```cpp
std::vector<int> v;
// ...
sql << "insert into t ...", use(v);
```
138 139 140

as well as a destination:

141 142 143 144 145
```cpp
std::vector<int> v;
v.resize(100);
sql << "select ...", into(v);
```
146 147 148 149 150 151

In the latter case the initial size of the vector defines the maximum number of data elements that the user is willing to accept and after executing the query the vector will be automatically resized to reflect that actual number of rows that were read and transmitted.
That is, the vector will be automatically shrunk if the amount of data that was available was smaller than requested.

It is also possible to operate on the chosen sub-range of the vector:

152 153 154 155 156 157
```cpp
std::vector<int> v;
// ...
std::size_t begin = ...;
std::size_t end = ...;
sql << "insert into t ...", use(v, begin, end);
158

159
// or:
160

161 162
sql << "select ...", into(v, begin, end);
```
163 164 165 166 167 168

Above, only the sub-range of the vector is used for data transfer and in the case of `into` operation, the `end` variable will be automatically adjusted to reflect the amount of data that was actually transmitted, but the vector object as a whole will retain its initial size.

Bulk operations can also involve indicators, see below.

Bulk operations support user-defined data types, if they have appropriate conversion routines defined.