Just following up on this, after a little more research: This has come up in the past, ref a conversation with the subject “SQLite and pike strings in bindings”. It seems that the SQLite binding is unique in this behavior and works as expected when you a) only use the pike module to access a database and b) always use bindings.
The binding emulation code which is used by mysql (at least) doesn’t work his way. Grubba pointed out that the Oracle binding code considers a string set within a multiset as binary data. I’m not sure how I feel about that… it’s probably lower overhead than something like Sql.Binary(“somedata”) but it’s certainly not intuitive (though it doesn’t seem like the binding behavior is really documented anywhere so I could be convinced that writing documentation might be an ok solution. Does anyone have any objections to me removing the problematic code in the Sqlite module in 8.1? In 8.0? I will let this simmer for a week or so before I do anything. Bill > On May 24, 2017, at 4:09 AM, Stephen R. van den Berg <[email protected]> wrote: > > H. William Welliver III wrote: >> 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. > > The "datatype" that comes closest to being a suitable native byte array > these days is Stdio.Buffer. > >> Of course, this causes a compatibility problem for anyone who was >> using pike binding with bindings previously (is there anyone who us > > I presume you mean SQLite with typed queries here? > >> 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). > > Two things: > - The contortions you describe to get the queries right with the current > behaviour would indicate that anyone who would have tried to do the same > would likely have ended up complaining here while trying to get it right. > - In general I've noticed that very few, if any, people are actually using > *typed* queries from Sql.Sql. > > So that would suggest that your change would be beneficial, and would > (for safety) require a bit of compat code to get it right for anyone > unfortunate enough to rely on older behaviour. > -- > Stephen.
