Use
In order to use the nanodbc library, add nanodbc/nanodbc.h and nanodbc/nanodbc.cpp source files to your project. On Visual C++, nanodbc/variant_row_cached_result.h and nanodbc/variant_row_cached_result.cpp come along with them; they are built only there, as they depend on _variant_t.
Alternatively, you can build the library with CMake as static or shared library and add it to your project as linker input.
Add #include <nanodbc/nanodbc.h> in source files where you wish to use nanodbc functions and classes.
The entirety of nanodbc can be found within the single nanodbc namespace.
Strings are nanodbc::string, which is std::string unless the library was built with NANODBC_ENABLE_UNICODE, and string literals passed to nanodbc are wrapped in NANODBC_TEXT, which selects the matching literal prefix. Writing both makes the code build either way, see iODBC and unixODBC below.
Quickstart
#include <nanodbc/nanodbc.h>
#include <cstdlib>
#include <exception>
#include <iostream>
int main() try
{
auto const connstr = NANODBC_TEXT("..."); // an ODBC connection string to your database
nanodbc::connection conn(connstr);
nanodbc::execute(conn, NANODBC_TEXT("create table t (i int)"));
nanodbc::execute(conn, NANODBC_TEXT("insert into t values (1)"));
auto result = nanodbc::execute(conn, NANODBC_TEXT("select i from t"));
while (result.next())
{
std::cout << result.get<int>(0) << std::endl;
}
return EXIT_SUCCESS;
}
catch (std::exception const& e)
{
std::cerr << e.what() << std::endl;
return EXIT_FAILURE;
}
iODBC and unixODBC
Notes about using nanodbc with iODBC and unixODBC in Unix systems.
On Windows, sizeof(wchar_t) == sizeof(SQLWCHAR) == 2. On Unix, sizeof(wchar_t) == 4.
On unixODBC, sizeof(SQLWCHAR) == 2. On iODBC, sizeof(SQLWCHAR) == sizeof(wchar_t) == 4.
This leads to incompatible ABIs between applications and drivers. If building against iODBC and the build option NANODBC_ENABLE_UNICODE is ON, then nanodbc::string will be std::u32string.
In every other Unicode build it is a 2-byte string: std::wstring on Visual C++, std::u16string elsewhere. With NANODBC_ENABLE_UNICODE left OFF, which is the default, it is plain std::string.
The nanodbc continuous integration tests run with GitHub Actions. The build platform does not make available a Unicode-enabled iODBC driver. As such there is no guarantee that tests will pass in entirety on a system using iODBC. Our recommendation is to use unixODBC.
If you must use iODBC, consider disabling Unicode mode in nanodbc build configuration to avoid wchar_t issues, see Build.
Non-ASCII text
In a narrow build, which is the default, text crosses between nanodbc and the driver as bytes in whatever the client character set happens to be, and a character that set cannot represent does not survive the trip. On Linux with unixODBC that set is normally UTF-8, which covers everything; on Windows it is the system ANSI codepage, which does not, so a character outside the basic multilingual plane is lost in both directions. Build with NANODBC_ENABLE_UNICODE set to ON to exchange UTF-16 with the driver instead.
Statement text is the more fragile of the two, because unlike a bound parameter it reaches the driver with neither a length nor a type. Bind values as parameters rather than writing them into statement text.
Batches and multiple result sets
A batch of statements returns one result set per statement, in order, and the counts from INSERT, UPDATE and DELETE count as result sets of their own. execute hands back the first of them, so a batch that modifies rows before selecting any arrives positioned on a count, which has no columns and no rows to read. Calling next() on it raises 24000 Invalid cursor state rather than returning the rows the SELECT produced.
result::next_result moves to the following result set:
#include <nanodbc/nanodbc.h>
#include <cstdlib>
#include <exception>
#include <iostream>
int main() try
{
nanodbc::connection conn(NANODBC_TEXT("..."));
auto results = nanodbc::execute(conn, NANODBC_TEXT(
"declare @t table (id int); "
"insert into @t values (1), (2); "
"select id from @t;"));
results.next_result(); // past the insert's count, onto the select's rows
while (results.next())
{
std::cout << results.get<int>(0) << std::endl;
}
return EXIT_SUCCESS;
}
catch (std::exception const& e)
{
std::cerr << e.what() << std::endl;
return EXIT_FAILURE;
}
On SQL Server, SET NOCOUNT ON withholds the counts instead, leaving the rows as the only result set and removing the need to step over anything.
Dates and times as strings
A string bound to a date, time or timestamp parameter is read by the driver, which accepts the literals ODBC spells out and little else: yyyy-mm-dd, hh:mm:ss and yyyy-mm-dd hh:mm:ss[.f...], a space between date and time and no time zone offset. That form is the portable one and every driver here stores it correctly.
ISO 8601 looks close enough to pass and is not. Given 2020-09-03T15:27:38-02:00, the PostgreSQL driver stores 2020-09-03 00:00:00 and reports success, the T having stopped it reading the time, with nothing in the return code or the diagnostics to say so. SQL Server refuses the same value outright. Writing it as 2020-09-03 15:27:38 stores the time on both.
Binding a nanodbc::timestamp avoids the question, since it carries its fields rather than a spelling of them.
Batch parameters and how far they carry
Binding arrays and executing them as a batch sets SQL_ATTR_PARAMSET_SIZE, and what happens next is the driver’s to decide. A driver that implements array binding sends the sets together; one that does not is free to walk them, executing the statement once per set, and the ODBC API gives the caller no way to tell which it got.
The difference is large. Inserting 5000 rows of two columns, over a local network, at several batch sizes:
Batch size |
PostgreSQL (rows/s) |
SQL Server (rows/s) |
|---|---|---|
1 |
4,322 |
858 |
10 |
21,887 |
7,480 |
100 |
29,043 |
52,731 |
1000 |
32,328 |
123,524 |
5000 |
33,051 |
140,003 |
SQL Server’s driver keeps gaining as the batch grows. The PostgreSQL driver stops gaining after a hundred or so, a batch fifty times larger buying another tenth, which is the shape of a driver walking the sets rather than sending them.
Where a driver walks them, a single statement carrying many rows is faster than many parameter sets — around thirteen times, in the same measurement. It costs the safety of bound parameters, so build it from values you trust or bind a smaller batch and accept the rate.
None of this applies to a database in the same process. SQLite has no round trip to save and shows no difference between the two.
Binding a batch held as rows
Parameters are bound a column at a time, an array to each marker, which is the shape the drivers take. Data usually arrives as rows instead, a struct to each, and turning one into the other means a vector per column and a loop to fill them.
bind_rows does that. Each accessor names one parameter, in the order the markers appear, either as a pointer to a member or as anything callable with a row:
#include <nanodbc/nanodbc.h>
#include <vector>
struct person
{
long id;
nanodbc::string name;
};
int main()
{
nanodbc::connection conn(NANODBC_TEXT("dsn"));
std::vector<person> people{{1, NANODBC_TEXT("Ada")}, {2, NANODBC_TEXT("Grace")}};
nanodbc::statement stmt(conn);
prepare(stmt, NANODBC_TEXT("insert into people (id, name) values (?, ?)"));
bind_rows(stmt, people, &person::id, &person::name);
execute(stmt, people.size());
}
The values are copied into the statement, so the rows are free to go out of scope before it runs. The overloads of bind taking a pointer bind the caller’s buffer instead, which has to stay alive and unchanged until execution.
Built as C++17 or later, an accessor yielding std::optional binds an absent value as null, which is how a nullable column is filled from a row that has no value for it.
What reaches the driver is a parameter array either way, so this is the same batch described above, and the same limits apply to how far it carries.
Reading a result other than forwards
next() works on any result. first(), last(), prior(), move() and skip() ask the driver to fetch somewhere other than the next row, and a cursor has to be able to scroll for that. ODBC does not give one by default, so the calls fail:
HY106: [Microsoft][ODBC Driver 18 for SQL Server]Fetch type out of range
position() reporting zero comes from the same place.
The cursor type is a statement attribute, set before the statement runs:
#include <nanodbc/nanodbc.h>
#include <cstdint>
#include <list>
#include <sql.h>
#include <sqlext.h>
int main()
{
nanodbc::connection conn(NANODBC_TEXT("dsn"));
std::list<nanodbc::statement::attribute> attributes;
attributes.push_back({SQL_ATTR_CURSOR_TYPE, 0, (std::uintptr_t)SQL_CURSOR_STATIC});
nanodbc::statement stmt(conn, attributes);
prepare(stmt, NANODBC_TEXT("select i from t order by i asc"));
auto results = execute(stmt);
results.last();
results.prior();
results.first();
results.move(2); // absolute, counted from one
}
SQL_CURSOR_STATIC takes a snapshot of the rows and scrolls over it. SQL_CURSOR_DYNAMIC and SQL_CURSOR_KEYSET_DRIVEN scroll as well and see later changes to differing degrees, at a cost the driver decides. A scrolling cursor is dearer than the forward only one, which is why it is not what you get without asking.
SQL Server, PostgreSQL, MySQL, SQLite and Oracle all honour SQL_CURSOR_STATIC through their ODBC drivers. A driver that cannot give the cursor asked for is free to substitute another and say so through SQLGetDiagRec rather than to fail, so a result that still will not scroll is worth checking the diagnostics for.
Reading many rows into containers
Results are read a row at a time, and there is no binding of output buffers to read a column straight into a container. What makes a read bulk is the rowset: execute takes the number of rows to fetch per round trip, and the driver fills that many at once while next() walks what it fetched.
get_ref reads a column into a variable already to hand rather than returning a new one, which keeps a string’s buffer in play across the loop instead of allocating one per row:
#include <nanodbc/nanodbc.h>
#include <string>
#include <vector>
int main()
{
nanodbc::connection conn(NANODBC_TEXT("dsn"));
// A thousand rows per round trip rather than one.
auto results = execute(conn, NANODBC_TEXT("select a, b from t"), 1000);
std::vector<nanodbc::string> a, b;
nanodbc::string value;
while (results.next())
{
results.get_ref(0, value);
a.push_back(value);
results.get_ref(1, value);
b.push_back(value);
}
}
Reserving on the vectors is worth it where the row count is known or can be estimated, since the loop otherwise grows them as it goes.
The rowset size is what to vary if the read is slow. Its effect is the same as the one batch parameters have, described above: the round trip is what costs, and fetching a thousand rows in one is far cheaper than a thousand round trips. Sizes beyond a few thousand rarely pay for the memory they take, since the rowset is held in full.
batch_ops sets the rowset size where a statement also carries parameters, so that the two are chosen separately.
Setting connection attributes
Some ODBC connection attributes govern how the connection is made, so they have to reach the driver before it connects rather than after. The constructors and connect() overloads taking a list of attributes set them on the handle between allocating it and connecting with it:
#include <nanodbc/nanodbc.h>
#include <cstdint>
#include <list>
#include <sql.h>
#include <sqlext.h>
int main()
{
std::list<nanodbc::connection::attribute> attributes;
attributes.push_back({SQL_ATTR_LOGIN_TIMEOUT, SQL_IS_UINTEGER, (std::uintptr_t)30});
nanodbc::connection conn(NANODBC_TEXT("dsn"), attributes);
// or, on a connection that has not connected yet
nanodbc::connection other;
other.connect(NANODBC_TEXT("dsn"), attributes);
}
Allocating a connection and setting attributes on it by hand before calling connect() does not work, and is not meant to: connect() frees the handle it is given and allocates another, so anything set on the old one goes with it. The overloads above are how the ordering is expressed.
Where the library is built as C++17 or later, an attribute’s value may also be a string or a binary buffer, which it holds for as long as the attribute lives. Below that the value is a std::uintptr_t, which covers the integer attributes.
Threads
A connection belongs to the thread that made it. Several threads working at once, each with a connection of its own, is the arrangement nanodbc is built for and needs no locking of its own:
#include <nanodbc/nanodbc.h>
#include <thread>
#include <vector>
int main()
{
std::vector<std::thread> threads;
for (int t = 0; t < 8; ++t)
{
threads.emplace_back(
[]()
{
nanodbc::connection conn(NANODBC_TEXT("dsn"));
nanodbc::statement stmt(conn);
prepare(stmt, NANODBC_TEXT("insert into t (i) values (?)"));
// ... bind and execute
});
}
for (auto& thread : threads)
thread.join();
}
Each connection allocates an ODBC environment of its own, so nothing is shared between them and the driver sees one connection per thread, which is what a threading-enabled driver expects.
Sharing one connection between threads is a different matter, and nanodbc makes no guarantee about it. A connection, a statement and a result are handles onto driver state; two threads using one at the same time is for the caller to synchronize. Every thread joining before the objects it used go out of scope is the caller’s business too — a crash on shutdown is more often threads outliving what they captured than anything the driver did.
Binding without preparing
Binding a value asks the driver what the parameter is — its type, its size, its scale — through SQLDescribeParam, and that needs a prepared statement. So a bind before prepare() fails:
HY010: [unixODBC][Driver Manager]Function sequence error
For a stored procedure whose parameters the caller already knows, preparing is a round trip spent asking a question with a known answer. describe_parameters supplies it instead, after which binding has nothing to ask and execute_direct runs the statement:
#include <nanodbc/nanodbc.h>
#include <sql.h>
#include <sqlext.h>
#include <vector>
int main()
{
nanodbc::connection conn(NANODBC_TEXT("dsn"));
nanodbc::statement stmt(conn);
std::vector<short> const index{0, 1};
std::vector<short> const type{SQL_INTEGER, SQL_VARCHAR};
std::vector<unsigned long> const size{10, 20};
std::vector<short> const scale{0, 0};
stmt.describe_parameters(index, type, size, scale);
int const i = 7;
nanodbc::string const s = NANODBC_TEXT("no prepare");
stmt.bind(0, &i);
stmt.bind(1, s.c_str());
stmt.execute_direct(conn, NANODBC_TEXT("insert into t (i, s) values (?, ?)"));
}
A description holds for as long as the statement does, so a statement executed repeatedly is described once. Describing only some of the parameters is allowed; the rest are asked about as usual, which then needs the statement prepared.
Examples
The programs under example are built by the examples target. Most of them take a connection string as their first argument. usage.cpp walks through most of the library:
#include "example_unicode_utils.h"
#include <nanodbc/nanodbc.h>
#include <algorithm>
#include <cstring>
#include <iostream>
using namespace std;
using namespace nanodbc;
void show(nanodbc::result& results);
void run_test(nanodbc::string const& connection_string)
{
// Establishing connections
nanodbc::connection connection(connection_string);
// or connection(connection_string, timeout_seconds);
// or connection("data source name", "username", "password");
// or connection("data source name", "username", "password", timeout_seconds);
cout << "Connected with driver " << convert(connection.driver_name()) << endl;
// Setup
execute(connection, NANODBC_TEXT("drop table if exists simple_test;"));
execute(connection, NANODBC_TEXT("create table simple_test (a int, b varchar(10));"));
// Direct execution
{
execute(connection, NANODBC_TEXT("insert into simple_test values (1, 'one');"));
execute(connection, NANODBC_TEXT("insert into simple_test values (2, 'two');"));
execute(connection, NANODBC_TEXT("insert into simple_test values (3, 'tri');"));
execute(connection, NANODBC_TEXT("insert into simple_test (b) values ('z');"));
nanodbc::result results = execute(connection, NANODBC_TEXT("select * from simple_test;"));
show(results);
}
// Accessing results by name, or column number
{
nanodbc::result results = execute(
connection,
NANODBC_TEXT("select a as first, b as second from simple_test where a = 1;"));
results.next();
auto const value = results.get<nanodbc::string>(1);
cout << endl << results.get<int>(NANODBC_TEXT("first")) << ", " << convert(value) << endl;
}
// Binding parameters
{
nanodbc::statement statement(connection);
// Inserting values
prepare(statement, NANODBC_TEXT("insert into simple_test (a, b) values (?, ?);"));
const int eight_int = 8;
statement.bind(0, &eight_int);
nanodbc::string const eight_str = NANODBC_TEXT("eight");
statement.bind(1, eight_str.c_str());
execute(statement);
// Inserting null values
prepare(statement, NANODBC_TEXT("insert into simple_test (a, b) values (?, ?);"));
statement.bind_null(0);
statement.bind_null(1);
execute(statement);
// Inserting multiple null values
prepare(statement, NANODBC_TEXT("insert into simple_test (a, b) values (?, ?);"));
statement.bind_null(0, 2);
statement.bind_null(1, 2);
execute(statement, 2);
prepare(statement, NANODBC_TEXT("select * from simple_test;"));
nanodbc::result results = execute(statement);
show(results);
}
// Transactions
{
{
cout << "\ndeleting all rows ... " << flush;
nanodbc::transaction transaction(connection);
execute(connection, NANODBC_TEXT("delete from simple_test;"));
// transaction will be rolled back if we don't call transaction.commit()
}
nanodbc::result results =
execute(connection, NANODBC_TEXT("select count(1) from simple_test;"));
results.next();
cout << "still have " << results.get<int>(0) << " rows!" << endl;
}
// Batch inserting
{
nanodbc::statement statement(connection);
execute(connection, NANODBC_TEXT("drop table if exists batch_test;"));
execute(
connection,
NANODBC_TEXT(
"create table batch_test (x varchar(10), x2 varchar(10), y int, z float);"));
prepare(
statement, NANODBC_TEXT("insert into batch_test (x, x2, y, z) values (?, ?, ?, ?);"));
const size_t elements = 4;
nanodbc::string::value_type xdata[elements][10] = {
NANODBC_TEXT("this"), NANODBC_TEXT("is"), NANODBC_TEXT("a"), NANODBC_TEXT("test")};
statement.bind_strings(0, xdata);
std::vector<nanodbc::string> x2data(xdata, xdata + elements);
statement.bind_strings(1, x2data);
int ydata[elements] = {1, 2, 3, 4};
statement.bind(2, ydata, elements);
float zdata[elements] = {1.1f, 2.2f, 3.3f, 4.4f};
statement.bind(3, zdata, elements);
transact(statement, elements);
nanodbc::result results = execute(connection, NANODBC_TEXT("select * from batch_test;"));
show(results);
execute(connection, NANODBC_TEXT("drop table if exists batch_test;"));
}
// Dates and Times
{
execute(connection, NANODBC_TEXT("drop table if exists date_test;"));
execute(connection, NANODBC_TEXT("create table date_test (x datetime);"));
execute(connection, NANODBC_TEXT("insert into date_test values (current_timestamp);"));
nanodbc::result results = execute(connection, NANODBC_TEXT("select * from date_test;"));
results.next();
nanodbc::date date = results.get<nanodbc::date>(0);
cout << endl << date.year << "-" << date.month << "-" << date.day << endl;
results = execute(connection, NANODBC_TEXT("select * from date_test;"));
show(results);
execute(connection, NANODBC_TEXT("drop table if exists date_test;"));
}
// Inserting NULL values with a sentry
{
nanodbc::statement statement(connection);
prepare(statement, NANODBC_TEXT("insert into simple_test (a, b) values (?, ?);"));
const int elements = 5;
const int a_null = 0;
nanodbc::string::value_type const* b_null = NANODBC_TEXT("");
int a_data[elements] = {0, 88, 0, 0, 0};
nanodbc::string::value_type b_data[elements][10] = {
NANODBC_TEXT(""),
NANODBC_TEXT("non-null"),
NANODBC_TEXT(""),
NANODBC_TEXT(""),
NANODBC_TEXT("")};
statement.bind(0, a_data, elements, &a_null);
statement.bind_strings(1, b_data, b_null);
execute(statement, elements);
nanodbc::result results = execute(connection, NANODBC_TEXT("select * from simple_test;"));
show(results);
}
// Inserting NULL values with flags
{
nanodbc::statement statement(connection);
prepare(statement, NANODBC_TEXT("insert into simple_test (a, b) values (?, ?);"));
const int elements = 2;
int a_data[elements] = {0, 42};
nanodbc::string::value_type b_data[elements][10] = {
NANODBC_TEXT(""), NANODBC_TEXT("every")};
bool nulls[elements] = {true, false};
statement.bind(0, a_data, elements, nulls);
statement.bind_strings(1, b_data, nulls);
execute(statement, elements);
nanodbc::result results = execute(connection, NANODBC_TEXT("select * from simple_test;"));
show(results);
}
// Cleanup
execute(connection, NANODBC_TEXT("drop table if exists simple_test;"));
}
void show(nanodbc::result& results)
{
const short columns = results.columns();
long rows_displayed = 0;
cout << "\nDisplaying " << results.affected_rows() << " rows "
<< "(" << results.rowset_size() << " fetched at a time):" << endl;
// show the column names
cout << "row\t";
for (short i = 0; i < columns; ++i)
cout << convert(results.column_name(i)) << "\t";
cout << endl;
// show the column data for each row
nanodbc::string const null_value = NANODBC_TEXT("null");
while (results.next())
{
cout << rows_displayed++ << "\t";
for (short col = 0; col < columns; ++col)
{
auto const value = results.get<nanodbc::string>(col, null_value);
cout << "(" << convert(value) << ")\t";
}
cout << endl;
}
}
void usage(ostream& out, std::string const& binary_name)
{
out << "usage: " << binary_name << " connection_string" << endl;
}
int main(int argc, char* argv[])
{
if (argc != 2)
{
char* app_name = strrchr(argv[0], '/');
app_name = app_name ? app_name + 1 : argv[0];
if (0 == strncmp(app_name, "lt-", 3))
app_name += 3; // remove libtool prefix
usage(cerr, app_name);
return EXIT_FAILURE;
}
try
{
auto const connection_string(convert(argv[1]));
run_test(connection_string);
return EXIT_SUCCESS;
}
catch (const exception& e)
{
cerr << e.what() << endl;
}
return EXIT_FAILURE;
}
The rest cover a topic apiece:
northwind.cppqueries the Northwind sample database through a data source of that name, so it takes no argument.rowset_iteration.cppfetches results a rowset at a time.table_schema.cppreads catalog information, and takes a table name after the connection string, optionally followed by a schema name.table_valued_parameter.cppbinds a SQL Server table-valued parameter.empty.cppis generated fromempty.cpp.inon the first build and left untracked, as a place to try things out.
example_unicode_utils.h, which they share, is where the convert calls in the listing above come from: it converts between nanodbc::string and std::string so that the examples print the same way in either build.