r0ng r0ng - 2 months ago 12
C++ Question

MySQL c++ connector: what is the correct way to use SetBlob() to set multiple blob data in a query?

I asked the same question on stackexchange db admin, that seems to be the proper place for the question, but the problem is no one even view the question. So I post the same question here again:

I am working on Mysql table insertion. The table creation statement is like:

CREATE TABLE files_table (fid INT NOT NULL AUTO_INCREMENT, idx TINYBLOB, head TINYBLOB, size BLOB, PRIMARY KEY(fid));


I could insert record manually

mysql> select * from files_table order by fid asc;
+-----+------+------+------------+
| fid | idx | head | size |
+-----+------+------+------------+
| 1 | 1 | 1 | 1 |
+-----+------+------+------------+


But when I use the connector to add the next value by using c++ connector:

class StreamBufferData : public std::streambuf
{
public:
StreamBufferData(const char *in_data, size_t in_size)
{
setg(in_data, in_data, in_data + in_size);
}
};

enum{QUERY_SIZE=256;}
char ins[QUERY_SIZE];

strcpy(ins, "INSERT INTO files_table (idx, head, size) VALUES (?,?,?)");

try
{
std::unique_ptr<sql::PreparedStatement> statement(ptr_connection->prepareStatement(ins));

char test_data[2] = "0";
StreamBufferData buffer0(test_data, 2);
std::istream test_s0(&buffer0);
statement->setBlob(1, &test_s0);

strcpy(test_data, "1");
StreamBufferData buffer1(test_data, 2);
std::istream test_s1(&buffer1);
statement->setBlob(2, &test_s1);

strcpy(test_data, "2");
StreamBufferData buffer2(test_data, 2);
std::istream test_s2(&buffer2);
statement->setBlob(3, &test_s2);

statement->executeUpdate();
}
catch(sql::SQLException &e)
{
std::cout << e.what() << ā€˜\nā€™;
return;
}


The result is :

+-----+------+------+------------+
| fid | idx | head | size |
+-----+------+------+------------+
| 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 |
+-----+------+------+------------+


Only the last value is correctly inserted into the table. My question is: what is the proper way to user msql::PreparedStatement::setBlob() to set multiple blobs in a query?

[environment]

Ubuntu 16.04.2

MySQL 5.7

MySQL connector version: 7.1.1.7

boost 1.58.0

g++ 5.4.0

Thanks for your help

Rong

Answer Source

The different std::istream objects point to the same buffer which allocated by test_data. I thought the istream object will copy the value in the char array. The fact is, it is not.

It also worths to point out that if the std::istream object is destroyed before the statement->executeUpdate() executed. The last stream object will be filled into the first column of the table, which might be an undefined behavior on my machine (Ubuntu 16.04.2 LTS). It looks like:

+-----+------+------+------------+
| fid | idx  | head | size       |
+-----+------+------+------------+
|   1 | 1    | 1    | 1          |
|   2 | 2    |      |            |
+-----+------+------+------------+

So, if the table has strange results like the previous table, check the code to make sure the std::stream, the StreamBufferData and the pointer pointing to the buffer are still valid until statement->executeUpdate() executed.