Can't manipulate blob data

Question

Can't manipulate blob data

blastrock opened this issue 7 years ago · 20 comments

I have a sqlite3 database containing binary data, but sqlpp11 seems to lack support for that. I don't know if I should be opening this issue here on sqlpp11...

Anyway, I took the master branch of this repo and I replaced SampleTest.cpp:93 with this line:

  di.insert_list.add(tab.beta = std::string("\0\0test", 6));

I got the following error:

terminate called after throwing an instance of 'sqlpp::exception'
  what():  Sqlite3 error: Could not prepare statement: unrecognized token: "'" (statement was >>INSERT INTO tab_sample (gamma,beta) VALUES(1,'

It would also be nice to use something else than std::string with columns of type blob. I use std::vector<uint8_t> everywhere else in my program, but sqlpp11 won't allow me to pass that.

Answer 1 · 2017-07-21T19:22:44.000Z

Good question whether it is sqlpp11 or the connector, I'll have to investigate...

Any help is appreciated :-)

Answer 2 · 2017-07-23T16:29:40.000Z

So here are my results so far:

You need to use prepared statements if you want to use blobs
At least in sqlite3 you need to use sqlite3_bind_blob or sqlite3_bind_blob64

I guess I need to

add a new datatype (blob)
add support for blob in the sqlite3 connector.

This will take a few days.

Best,

Roland

Answer 3 · 2017-07-23T19:59:37.000Z

I don't know if it's a better idea or not, but you can avoid prepared statements if you escape blobs like this:

insert into t values (x'410041');

Which stores "A\x00A".

I only tested it on the sqlite3 tool, not in code, but it should work there too.

Answer 4 · 2017-07-24T04:54:15.000Z

Good to know! Thanks for the info!

This seems like a nice alternative for small blobs. For big ones it might be considered slow and bloated.

Answer 5 · 2017-08-06T18:23:45.000Z

Hi,

Took longer than I had hoped for, but I have a very first version online now, see https://github.com/rbock/sqlpp11/tree/feature/data-type-blob

You probably cannot obtain blobs, but you should be able to manipulate them, see also test_serialize/Blob.cpp

More to follow :-)

Cheers,

Roland

Answer 6 · 2017-10-30T16:08:56.000Z

It seems that the feature branch never got integrated, is there a reason for that? (lack of time?)

Answer 7 · 2017-10-30T16:59:37.000Z

Lack of time and public interest (I did not get any feedback on the first prototype).

Answer 8 · 2017-10-31T15:50:59.000Z

I am testing performance of sqlite3 + sqlpp11 in my use case, if it fits i'll try test blob stuff as I need to store binary strings

Answer 9 · 2017-10-31T17:57:25.000Z

Please let me know in either case.

Thanks!

Answer 10 · 2017-11-06T09:30:10.000Z

I am stuck with another problem, it seems that the connector does not handle the SQLITE_LOCKED case and simply throws an exception (I could go past the SQLITE_BUSY using the PRAGMA busy_timeout), I could no go further yet with sqlpp11.

Answer 11 · 2017-11-06T11:14:26.000Z

What behavior would you expect in the SQLITE_LOCKED case?

Answer 12 · 2017-11-06T12:59:36.000Z

exactly the same as in SQLITE_BUSY, I use sqlite in an "insert-only" scenario (with another select-only access aside) so waiting is the only thing to do.

Answer 13 · 2017-11-07T05:41:25.000Z

According to the sqlite3 documentation:

The SQLITE_LOCKED result code differs from SQLITE_BUSY in that SQLITE_LOCKED indicates a conflict on the same database connection (or on a connection with a shared cache) whereas SQLITE_BUSY indicates a conflict with a different database connection, probably in a different process.

Based on your description, you should have SQLITE_BUSY only. Maybe you are re-using the same connection for inserts on different threads?

Answer 14 · 2017-11-07T08:10:09.000Z

It seems to happen as well when two different processes attempt to create the database file and the tables in there (though I specified the IF NOT EXISTS clause)

Answer 15 · 2017-11-07T08:13:51.000Z

Correct me, if I am wrong, but that seems to be an issue outside of the library.

Answer 16 · 2018-03-07T09:54:34.000Z

Sorry for the long silence. We are still interested in this feature, but I lacked time to test your prototype.

Anyway, I just did it and it seemed to work with a few fixes. I opened rbock/sqlpp11#223 and pushed https://github.com/SuperTanker/sqlpp11-connector-sqlite3/commits/feature/data-type-blob . I did not open a PR on the sqlite3 connector since there is no feature branch there. You can pick it from my repo if you want.

As for the feedback: it works :)
The only issue we have with our code is that since last time, some things changed. We have some types that are not stored in std::vector<uint8_t> but in std::array<uint8_t, X>. So every time we need to pass those types to sqlpp, we need to allocate and copy a temporary vector.

In our code we make extensive use of gsl::span (sorry, I couldn't find a good documentation) to solve this kind of problems. I don't know what would be the best solution for sqlpp as I guess you do not want to add a dependency.

Your prototype is still better than nothing, copying vectors is still cheaper than encoding in base64 ^^

Answer 17 · 2018-03-07T11:10:06.000Z

Thanks for the feedback. Adding std::array as input should be rather simple to add. Do you want to give it a try?

Regarding span: Right, I am not keen on adding a gsl dependency. You could create your own data type in your project.

sqlpp17 will make this much easier, btw.

Answer 18 · 2018-05-29T09:10:27.000Z

Hi,
I was wondering if you had any date in mind for the next release? Are there some blocking issues?

Answer 19 · 2018-05-29T09:17:43.000Z

Hi, I don't have fixed date yet. I need to do a few tests as there were reports on some compilation failures that I need to look into first. I hope to do so next weekend.

Answer 20 · 2018-06-01T09:51:49.000Z

Release is done.