tdscpp is a C++ library for Microsoft's Tabluar Data Stream (TDS) protocol, which is used to communicate with Microsoft SQL Server. Using this library you can write programs which interface directly with MSSQL, without relying on ODBC or OLEDB. It requires C++20, and works on the latest versions of GCC, MSVC, and Clang.
Installation is much the same as for any CMake project:
mkdir build
cd build
cmake ..
make
make install
On Linux, to add Kerberos support add -DENABLE_KRB5=ON
to cmake
. To add encryption
support, add -DWITH_OPENSSL=ON
to cmake
.
On Windows, both Kerberos support and encryption are provided by the operating system,
so you don't need to do this. If you set WITH_OPENSSL
OpenSSL will be linked in,
but otherwise Windows' own Schannel will be used instead.
Once installed, you can add it to your CMakeLists.txt:
set(CMAKE_CXX_STANDARD 20)
find_package(tdscpp REQUIRED)
target_link_libraries(project tdscpp)
Make sure that you're using at least C++20.
For GCC, just add -ltdscpp
:
g++ -std=c++20 -ltdscpp test.cpp
- 20240212: Initial release
#include <tdscpp.h>
#include <nlohmann/json.hpp>
#include <format>
#include <iostream>
int main() {
try {
/* Connect to server mssql with username "sa" and password "password". If you omit
the credentials, Kerberos / Active Directory authentication will be used.
You can also pass more options by using a tds::options struct instead. */
tds::tds conn("mssql", "sa", "password");
/* Issue commands by using the run method. These will throw an exception if the server
returns an error. */
conn.run("DROP TABLE IF EXISTS dbo.tmp");
conn.run("CREATE TABLE dbo.tmp(num INT, str VARCHAR(10), str2 NVARCHAR(10), dt DATETIME2(0), not_set INT)");
{
/* Begin a transaction. This will automatically get rolled back if it goes out of
scope without tran.commit() being called, such as if an exception is thrown. */
tds::trans tran(conn);
/* You can use question marks for placeholder variables. There's basic constexpr
sanity checking, meaning that if you forget a variable or have mismatched brackets
your code won't compile. There's also full Unicode support. */
conn.run("INSERT INTO dbo.tmp(num, str, str2, dt) VALUES(42, ?, ?, GETDATE())",
"hello", u8"๐ฅ");
{
/* Run a query. You can only have one active query at a time per connection,
so it's a good idea to wrap it in curly brackets so it gets released when
it goes out of scope. */
tds::query sq(conn, "SELECT * FROM dbo.tmp WHERE num = ?", 42);
/* fetch_row() returns false when there's no more results. */
while (sq.fetch_row()) {
/* The returned data is in tds::value structs, which can be cast to int,
std::string, etc., or passed to std::format as below. */
std::cout << std::format("{}, {}, {}, {}", sq[0], sq[1], sq[2], sq[3]) << std::endl;
/* Or you can pass it to nlohmann::json. */
auto j = nlohmann::json::object();
for (unsigned int i = 0; i < sq.num_columns(); i++) {
/* MSSQL stores column names at UTF-16, but you can use tds::utf16_to_utf8
to fix this. */
j[tds::utf16_to_utf8(sq[i].name)] = sq[i];
}
std::cout << j.dump() << std::endl;
}
}
/* Commit the transaction we opened before. */
tran.commit();
}
} catch (const std::exception& e) {
std::cerr << std::format("Exception: {}\n", e.what());
return 1;
}
return 0;
}
Sample output:
42, hello, ๐ฅ, 2024-02-12 21:21:05
{"dt":"2024-02-12 21:21:05","not_set":null,"num":42,"str":"hello","str2":"๐ฅ"}
You can call Remote Procedure Calls, either inbuilt ones or custom stored procedures,
by using tds::rpc
in much the same way as you use tds::query
.
conn.run("DROP VIEW IF EXISTS dbo.test_view");
conn.run("CREATE VIEW dbo.test_view AS SELECT * FROM dbo.tmp");
{
tds::rpc r(conn, "sp_depends", "dbo.test_view");
while (r.fetch_row()) {
for (size_t i = 0; i < r.num_columns(); i++) {
std::cout << (std::string)r[i] << " (" << tds::utf16_to_utf8(r[i].name) << ")" << std::endl;
}
std::cout << "---" << std::endl;
}
}
Output:
dbo.tmp (name)
user table (type)
no (updated)
yes (selected)
num (column)
---
...
You can use tds::bcp
to load data into the database quickly. It takes three arguments:
the name of the table, an input range of the column names, and an input range of
an input range of the values. If you pass normal strings rather than UTF-16, they will
get converted for you.
std::optional
can be used to represent nullable values.
conn.bcp(u"dbo.tmp", std::vector{u"num", u"str", u"str2"}, std::vector<std::vector<tds::value>>{
{28, "foo", "bar"},
{29, "baz", std::optional<std::string>{std::nullopt}}
});
The tds::tds
constructor can also be passed a function pointer or a lambda to receive
any messages that are sent by the server, including error messages. The default behaviour
is to throw an exception if any messages are received with a severity more than 10.
static void show_msg(std::string_view server, std::string_view message, std::string_view proc_name,
int32_t msgno, int32_t line_number, int16_t state, uint8_t severity,
bool error) {
if (severity > 10) /* print errors in red */
std::cout << std::format("\x1b[31;1mError {}: {}\x1b[0m\n", msgno, message);
else if (msgno == 50000) /* match SSMS by not displaying message no. if 50000 (RAISERROR etc.) */
std::cout << std::format("{}\n", message);
else
std::cout << std::format("{}: {}\n", msgno, message);
}
int main() {
/* See the definition of tds::options for an exhaustive list of all
the possible parameters here. */
tds::tds conn("mssql", "sa", "password", "test program", "", show_msg);
conn.run("RAISERROR('hello world', 10, 1)");
conn.run("THROW 50000, ':-(', 1");
return 0;
}
Output:
hello world
Error 50000: :-(
The struct tds::value
represents any value that might be stored in a MSSQL database. The spaceship
operator <=>
is defined, meaning that you can compare values using (hopefully) the
exact same logic as on the server itself.
tds::numeric<N>
can be used to represent NUMERIC(N) or DECIMAL(N) types, where
N is a number between 0 and 38.
tds::datetime
can be used to represent DATETIME and DATETIME2 types. tds::datetimeoffset
adds an offset value, for DATETIMEOFFSET. DATE maps to std::chrono::year_month_day
,
and TIME to std::chrono::time_point
.