I’ve noticed an odd problem a few years ago with using parameter bindings with SQLite and never really took the time to figure out the cause until now. I think it’ a pretty major problem that can be easily fixed, though would cause problems for anyone relying on the (imo) currently broken behavior.
Basically, the problem is that when using parameter bindings with strings, the pike glue determines the type of the data based on the width of the string: if it’s a wide string, the parameter is bound as a text, but if it’s 8 bit, it’s bound as a blob. The problem is that text and blob values aren’t evaluated the same, so even though a text value and blob value may be identical byte-wise, they are not equal when doing comparisons and so queries that would seem to match actually don’t.
This is compounded by the fact that SQLite has no restrictions on what may be stored in a field (type definitions on a table are merely default suggestions) and SQLite will happily store whatever you want (such as a blob value in a text field).
For example, imagine a table m with an integer field id and a char field value:
s->query(“INSERT INTO m VALUES(1, 'value a’)); s->query(“INSERT INTO m VALUES(2, :value)”, ([“:value”, “value a”])); s->query(“SELECT * FROM m where value=‘value a’”);
The third query will return 1 record instead of the expected 2 because the field value in the second row is stored as a blob and thus does not match a text query. In order to retrieve both records, you must use LIKE or know to cast the value to text using “AS TEXT” in the query, which is obviously a lot less ideal in most cases.
If all of your data is stored using the pike glue, and you’re always using parameters, it’s possible never to see the problem, but if not, this causes all kinds of problems because of the need for constantly casting values and possibly using blob literals, which are not human readable when you’re really trying to query for a non-wide string.
My suggestion is to change the binding type for 8 bit string parameters to text in order to match the rest of the string handling. It would be good to have a datatype that caused a parameter to be bound as a blob, either a native byte array or some object that wraps an 8 bit string (Sql.BinaryString?) in order to cause the binding to act accordingly.
Of course, this causes a compatibility problem for anyone who was using pike binding with bindings previously (is there anyone who us doing this and hasn’t run into this problem?), as any 8-bit strings that weren’t binary data would be stored as blobs, even if they were in a text typed field. These records would need to be re-stored with the proper text type, which could be done with a query to update the table. My sense is that this is the proper thing to do, as blob fields should be reserved for data that’s actually binary data (as opposed to text).
Thoughts?