binding.md 7.57 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 90 91 92 93 94

### Portability note

Older versions of the PostgreSQL client API do not allow to use input parameters at all.
In order to compile SOCI with those old client libraries, define the `SOCI_POSTGRESQL_NOPARAMS` preprocessor name passing `-DSOCI_POSTGRESQL_NOPARAMS=ON` variable to CMake.

## 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:

95 96 97
```cpp
string firstName = "John", lastName = "Smith";
int personId = 7;
98

99
sql << "insert into person(id, firstname, lastname) values(:id, :fn, :ln)",
100 101
        use(personId), use(firstName), use(lastName);

102
sql << "select firstname, lastname from person where id = :id",
103
        into(firstName), into(lastName), use(personId);
104
```
105 106 107 108 109 110 111 112 113

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:

114 115 116 117 118 119
```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");
```
120 121 122

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

123 124 125 126 127 128 129
```cpp
string addr = "...";
sql << "update person"
        " set mainaddress = :addr, contactaddress = :addr"
        " where id = 7",
        use(addr, "addr");
```
130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148

### Portability notes

The PostgreSQL backend allows to use the "native" PostgreSQL way of naming parameters in the query, which is by numbers like `$1`, `$2`, `$3`, etc.
In fact, the backend *rewrites* the given query to the native form - and this is also one of the very few places where SOCI intrudes into the SQL query.
For portability reasons, it is recommended to use named parameters, as shown in the examples above.

The query rewriting can be switched off by compiling the backend with the `SOCI_POSTGRESQL_NOBINDBYNAME` name defined (pass `-DSOCI_POSTGRESQL_NOBINDBYNAME=ON` variable to CMake).
Note that in this case it is also necessary to define `SOCI_POSTGRESQL_NOPREPARE` (controlled by CMake variable `-DSOCI_POSTGRESQL_NOPREPARE=ON`), because statement preparation relies on successful query rewriting.

In practice, both macros will be needed for PostgreSQL server older than 8.0.

## 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:

149 150 151 152 153
```cpp
std::vector<int> v;
// ...
sql << "insert into t ...", use(v);
```
154 155 156

as well as a destination:

157 158 159 160 161
```cpp
std::vector<int> v;
v.resize(100);
sql << "select ...", into(v);
```
162 163 164 165 166 167

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:

168 169 170 171 172 173
```cpp
std::vector<int> v;
// ...
std::size_t begin = ...;
std::size_t end = ...;
sql << "insert into t ...", use(v, begin, end);
174

175
// or:
176

177 178
sql << "select ...", into(v, begin, end);
```
179 180 181 182 183 184

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.