Jean, Thanks for the reply. I understand this very well, and I have read this page many times over the past few years:
http://www.sqlite.org/datatype3.html My argument is that regardless of a column's type or type affinity, a method called ReadBytes() should read the bytes as stored in the database and let the programmer figure out what to do with them. The latest doc available here (very last link on page): http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki shows no methods for reading BLOBs. There are methods like GetString, GetFloat, GetDouble, etc., but nothing called GetBlob. GetBytes is describes as follows: SQLiteDataReader.GetBytes Method Retrieves a column as an array of bytes (blob) public override long GetBytes( int i, long fieldOffset, byte[] buffer, int bufferoffset, int length ); Parameters i The index of the column to retrieve fieldOffset The zero-based index of where to begin reading the data buffer The buffer to write the bytes into bufferoffset The zero-based index of where to begin writing into the array length The number of bytes to retrieve Return Value The actual number of bytes written into the array That implies that it will read the bytes of any column regardless of type. Why on Earth would a method called GetBytes restrict itself to only those columns with blob affinity? It doesn't make any sense. It should be called GetBlob to match the naming convention of all the other Getxxx methods. I realize this is an ADO.NET thing and I'm not casting blame on the System.Data.SQLite devs. The spec is the spec. Sorry for the rant... Have a good weekend, and thanks again for spending the time on this. I have learned more from the people on the list than I ever imagined. Regards, -Bill On 7/3/2015 1:51 PM, Jean Chevalier wrote: > In SQLite, every value you store is stored alongside its type. This is unlike > other databases where the column determines the type and every value stored > against it share it. In SQLite you could have a table in which all value > types as stored contradict all column types as declared, if you so craft it > (except integer primary key). As for a column declared BLOB, it results in an > affinity of NONE meaning "perform no implicit conversions" (as long as > lossless and reversible), it doesn't mean "affinity binary" because there is > no such thing as something more generic than binary that should implicitly be > cast to binary upon storing it. So a blob-declared column would not try and > implicitly convert, but it will still store exactly what you put in, > including its type, inferred from the way in which you did the assignment, > e.g., for set c = '1' it will store {text-1} for integer it will store > {integer-of-1-<nothing>} (a special case), for x'01' it will store {blob-01}, > and so on. Th! > is lower > case 'blob' is the most generic case (your 'bytes') but to obtain that one > must cast() to a blob which means "strip from what I'm giving you any > indication that it may be a text, or an integer, or a real, as it is really > neither". Think of blob not as a type ('binary') but the absence of types. > You can further apply a check constraint to the column to ensure that no > value stored will be stored along with a tag meaning "I'm of a certain kind". > This check(typeof(c)='blob') will ensure the data you put in will be 'naked' > so to speak, and when the data you put in is naked, SQLite can only store it > as bytes, and by default return it as bytes. So you can see how it differs, > blob as column type meaning 'suggest no type' or 'add no type' and blob as > value type meaning 'a value with no type tag attached' or 'convey no type'. > They're complementary. Think of the two together as "none given - none taken" > (or 'none added'). Naturally the constraint will not let you set c = '1' > because '! > 1' implie > s text by way of the quotes, same for Int (and how would it know if you > meant a one-byte int, or two, or four?), and I think that's what you want in > your application, but it will let you assign something in the x'' notation, > or a cast() expression. So a check constraint makes the column more > 'demanding' so to speak, type-wise, stricter, and closer to what you observe > in other databases. Just remember to tweak the check constraint to allow > nulls for nullable columns. After which, we'd expect that if no type marker > was carried in, none will come out upon querying it, unless you have some > other layer wrapped around it such as some ODBC driver assuming that it's its > duty to further cast a column to a target type based on the column's declared > type. But this is because some wrapper drivers assume the user will try and > use SQLite as he uses most other databases, or because the driver cannot > support exposing a series of column values of varying types. A native SQLite > driver (one b! > uilt in a > ccordance to how SQLite really works) shouldn't take this freedom. > > > You wrote: >> My assumption was that GetBytes() could be used to read >> the bytes of any column regardless of type. What difference >> should column type or affinity make if I'm asking for bytes? >> If I'm using GetBytes() on a column with REAL affinity, >> it should put 8 bytes in the read buffer, but it doesn't. >> If I send text to a BLOB column it's typeof() changes to >> TEXT which confuses GetBytes(). I think this is pretty silly >> because bytes are bytes, right? Why bother verifying type >> when the method name already tells you what you are getting >> and bytes can be obtained from any data type? >> (Rhetorical questions, but feel free to respond anyway.) >> > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ----- > No virus found in this message. > Checked by AVG - www.avg.com > Version: 2015.0.6037 / Virus Database: 4365/10155 - Release Date: 07/03/15 > >
