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 declared to the driver as text, so the server reads it rather than the driver does. Drivers accept little beyond the literals ODBC spells out — 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 — while servers read a good deal more, ISO 8601 among it. Both 2020-09-03 15:27:38 and 2020-09-03T15:27:38 therefore store the time, and a time zone offset is applied or ignored according to the type of the column it lands in.
What a server accepts remains the server’s business. SQL Server rejects an offset on a datetime column, and says so.
Binding a nanodbc::timestamp avoids the question, since it carries its fields rather than a spelling of them.
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.