Welcome to nanodbc Discussions!

[mloskot]

👋 Welcome!

We’re using Discussions as a place to connect with other members of our community. We hope that you:

• Ask questions you’re wondering about.
• Share ideas.
• Engage with other community members.
• Welcome others and are open-minded. Remember that this is a community we
  build together 💪.

To get started, comment below with an introduction of yourself and tell us about what you do with this community.

[paul-rta]

Intro:
I'm Paul, a software developer in Perth, Western Australia, with 35 years experience in a wide variety of programming languages - over 20 years in C++, mainly in Windows, but also in Linux.

I hope to use this community to assist with gaining adequate knowledge of nanodbc to redevelop an existing high-level database interface in an existing application.

Today, I have run into an apparent limitation in nanodbc, and would gladly accept insults for my naïvity, should they be warranted and accompanied by friendly guidance...

As explained, I am working with an existing application so change of schema is not practical - I need to find a way of working with what I have.

My question:
In summary, how do I transfer binary data in a prepared statement?

Sample code snippets:
Direct execution:

execute(myConn, "create table simple_test (a int, b varchar(10), c binary(80));");
execute(myConn, "insert into simple_test values (1, 'one', 0x01234567a1b2c3d4e5f6);");

This works perfectly, but is plainly not using parameters.

The content of the record created can be correctly read using the following code:

typedef vector<uint8_t> bin;
nanodbc::result results = execute(myConn, "select a as rA, b as rB, c as rC from simple_test where a = 1;");
if (results.columns() > 0)
{
results.next();
int a = results.get("rA");
string b = results.get("rB");
bin c = results.get("rC");
cout << "Success!";
}

Note that it was discovered that std::string could also be used where my typedef "bin" is used, and the byte content of the string so populated was as had been written to the record.

Implicitly then, a vector<uint8_t> is the correct data-storage type for binary data. In turn, this implies that, a prepared INSERT statement would then be written something like the following:

nanodbc::statement myStmt(myConn);

prepare(myStmt, "insert into simple_test (a, b, c) values (?, ?, ?);");

const int eight_int = 8;
myStmt.bind(0, &eight_int);

const string eight_str = "eight";
myStmt.bind(1, eight_str.c_str());

bin val_bin;
uint8_t val = 0xea;
for (int i = 0; i < 80; ++i)
val_bin.push_back(val++);
myStmt.bind(2, &val_bin);

However, this causes a linker error - the bind to the vector<uint8_t> has no matching "external symbol".

Given that the read of a binary field can be performed into a std::string, this was tried instead.

string val_bin;
val_bin.reserve(80);
uint8_t val = 0xea;
for (int i = 0; i < 80; ++i)
val_bin.push_back(val++);
myStmt.bind(2, val_bin.c_str());

This links without error. However, when run the ODBC driver complains:

Invalid character value for cast specification

Implicitly, it is attempting to treat the stream of bytes as a UTF-8 string, but invalid code sequences were (unsurprisingly) found.

I have run out of ideas about how to implement this. Hence my question...

Full source uploaded as a text file.

SimpleNano.txt

[paul-rta]

Apologies if the question is posted in the wrong place - I saw the invitation to introduce myself and just kept typing...

[paul-rta]

After digging through the nanodbc.cpp source, I believe that I have resolved the issue:

    bin val_bin;
    uint8_t val = 0xea;
    for (int i = 0; i < 80; ++i)
        val_bin.push_back(val++);
    myStmt.bind(2, { val_bin });

Hopefully, this helps others...

[mloskot]

Yes, the tests are the best examples of how to use the library features.