Nanodbc now supports nvarchar(max) and varchar(max)

“Unlimited” data fields

I am in the process of rewriting some software, and one of the steps is to replace an ancient ODBC client library with a more modern C++ SQL server wrapper client library.

Searching for alternatives, with a permissive (and commercial) license turned into a few possible candidates. First was TinyODBC, but it hasn’t been developed for quite a while, and it had some bugs. In the various forums, Nanodbc was listed as an alternative. And it has a MIT license, allowing commercial use without the license “infecting” rest of the software (like the GPL).

For our purposes, buying a commercial ODBC client library was out of the question, as there was only a couple of fields in a couple of tables that were interesting. Performance wasn’t critical, and an ORM were too

Getting started with Nanodbc is pretty straight forward, even with Windows and Visual Studio. Just add the header and cpp-files to your project and start connecting to the database.

However, there was one major issue with it, nvarchar(max) and varchar(max) always returned an empty string or garbage. Not very useful.

To figure out what was happening, only the debugger could help, and reading the ODBC docs. Those docs I was hoping to do without.

For a while, I thought my usage of the library was wrong. However, it turned out to be a bug in the library itself.

Luckily, it’s actively developed at GitHub and I decided to fix it for my needs.

To make a long story short, when requesting data for those types, the driver (SQL Server Native Client) returns 0 bytes field length, confusing the client library. The solution was to deal with the data as a blob when binding.

The gory details are in the source code at GitHub, enjoy!

Example

This test program can be used to confirm the varchar(max) and nvarchar(max) fields.

#include <iostream>
#include <nanodbc.h>

#ifdef NANODBC_USE_UNICODE
# define UNI(a) L ## a
#else
# define UNI(a) a
#endif

int main()
{
	typedef nanodbc::string_type str;

#ifdef NANODBC_USE_UNICODE
	str encoding = UNI("Unicode");
	auto & out = std::wcout;
#else
	str encoding = UNI("Ascii");
	auto & out = std::cout;
#endif

	try
	{
		// Standard output device

		str dsn  = UNI("nanobug");
		str user = UNI("");
		str pass = UNI("");

		nanodbc::connection nanoConnection(dsn, user, pass);

		if (nanoConnection.connected())
		{

			out << UNI("Connected with driver ") << nanoConnection.driver_name() << " with encoding " << encoding << std::endl ;

			std::vector<str> statements
			{
				UNI("DROP DATABASE [nanobug];"),
				UNI("CREATE DATABASE [nanobug];"),
				UNI("USE [nanobug];"),
				UNI("IF OBJECT_ID('simple_test', 'U') IS NOT NULL DROP TABLE simple_test;"),
				UNI("create table simple_test (id int, limited varchar(10), unilimited nvarchar(10), val varchar(max), uni nvarchar(max));"),
				UNI("insert into simple_test values (1, 'one', 'one', 'one', 'one');"),
				UNI("insert into simple_test values (2, 'two', 'two', 'two', 'two');"),
				UNI("insert into simple_test values (3, 'three', 'three', 'three', 'three');")
			};

			for (const auto & stmt : statements)
			{
				out << stmt << std::endl;
				auto result = execute(nanoConnection, stmt);
			}

			str query = UNI("select id, limited, unilimited, val, uni from simple_test");

			nanodbc::statement statement(nanoConnection, query);
			nanodbc::result nanoresults = statement.execute();

			while(nanoresults.next())
			{
				auto id			= nanoresults.get<int>(UNI("id"));
				auto limited	= nanoresults.get<str>(UNI("limited"));
				auto unilimited	= nanoresults.get<str>(UNI("unilimited"));
				auto val		= nanoresults.get<str>(UNI("val"));
				auto uni		= nanoresults.get<str>(UNI("uni"));

				out <<
					UNI("Id: '") << id <<
					UNI("' limited: '") << limited <<
					UNI("' unicode limited: '") << unilimited <<
					UNI("' val: '") << val <<
					UNI("' unicode: '") << uni << UNI("'")
					<< std::endl;
			}

			int a_mighty_fine_place_to_break=0;
		}

	}
	catch (const std::exception & ex)
	{
		std::cout << ex.what();
	}

	return 0;
}

Then the output should be:

Connected with driver sqlncli11.dll with encoding Ascii
DROP DATABASE [nanobug];
CREATE DATABASE [nanobug];
USE [nanobug];
IF OBJECT_ID('simple_test', 'U') IS NOT NULL DROP TABLE simple_test;
create table simple_test (id int, limited varchar(10), unilimited nvarchar(10), val varchar(max), uni nvarchar(max));
insert into simple_test values (1, 'one', 'one', 'one', 'one');
insert into simple_test values (2, 'two', 'two', 'two', 'two');
insert into simple_test values (3, 'three', 'three', 'three', 'three');
Id: '1' limited: 'one' unicode limited: 'one' val: 'one' unicode: 'one'
Id: '2' limited: 'two' unicode limited: 'two' val: 'two' unicode: 'two'
Id: '3' limited: 'three' unicode limited: 'three' val: 'three' unicode: 'three'

If you have an old version of Nanodbc, the values val and unicode will be empty or full of garbage.