[sqlite] Reader.GetBytes() - when is a byte not a byte?
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-} (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] Reader.GetBytes() - when is a byte not a byte?
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-} (a
special case), for x'01' it will store {blob-01}, and so on. This 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'
implies 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 built in
accordance 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] Reader.GetBytes() - when is a byte not a byte?
Lots of good replies. My problem is solved, thank you.
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.)
Anyway, the problem is solved. Thank you all.
( cast('x y z' as blob) ) is especially convenient. Thank you for that
excellent suggestion, Jean (and thanks for the Stack Overflow link, that helped
too).
And thanks to R. Smith for posting the informative examples that also helped me
understand what was happening.
--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Jean Chevalier
> Sent: Thursday, July 02, 2015 1:40 PM
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] Reader.GetBytes() - when is a byte not a byte?
>
> It's not mandatory to use x'' notation to insert into a blob, when
> one can use cast.
>
> The following should return blob content correctly without explicitly
> lying it down as Hex:
>
> sqlite> create table T (c blob check(typeof(c) = 'blob'));
>
> sqlite> insert into T values ( cast('x y z' as blob) );
>
> sqlite> select c from T;
>
> x y z
>
> Please check whether your calling program consumes the above as text or
> blob, I'd expect blob.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any
attachments are solely for the use of the addressee and may contain information
that is privileged or confidential. Any disclosure, use or distribution of the
information contained herein is prohibited. In the event this e-mail contains
technical data within the definition of the International Traffic in Arms
Regulations or Export Administration Regulations, it is subject to the export
control laws of the U.S.Government. The recipient should check this e-mail and
any attachments for the presence of viruses as L-3 does not accept any
liability associated with the transmission of this e-mail. If you have received
this communication in error, please notify the sender by reply e-mail and
immediately delete this message and any attachments.
[sqlite] Reader.GetBytes() - when is a byte not a byte?
It's not mandatory to use x'' notation to insert into a blob, when one can
use cast.
The following should return blob content correctly without explicitly lying it
down as Hex:
sqlite> create table T (c blob check(typeof(c) = 'blob'));
sqlite> insert into T values ( cast('x y z' as blob) );
sqlite> select c from T;
x y z
Please check whether your calling program consumes the above as text or blob,
I'd expect blob.
[sqlite] Reader.GetBytes() - when is a byte not a byte?
On 2015-07-02 03:24 PM, R.Smith wrote:
> That exception is only thrown if the source column is not a BLOB (as
> far as I can tell).
>
> You specify the column Type (or type affinity) as BLOB but then you
> store a non-BLOB TEXT value in it ('"blue" "red" "orange" "yellow"'
> <-- Not a BLOB)
> SQLite will store this as TEXT, not BLOB.
>
> A BLOB starts with an x followed by a string of Hex, like in your
> other inserted values such as: X'FFFEFDFCFBFAF9F8F7F6'
>
> The BLOB for your '"blue" "red" "orange" "yellow"' string will look
> like this:
> x'22626C75652220227265642220226F72616E676522202279656C6C6F7722'
>
> Insert that and getBytes() will work just fine.
>
> PS: This solution presupposes the enforcement of BLOB-ness, I'm not
> exactly sure if this is the intention from SQLite or indeed a bug /
> undesired side-effect.
>
To be sure, here is a script to point out the difference:
CREATE TABLE myTable (
myText Text,
myTextArray BLOB, --Text
myBool Boolean,
myBoolArray BLOB, --Boolean
myUInt8 UInt8,
myUInt8Array BLOB, --UInt8
myUInt16 UInt16,
myUInt16Array BLOB, --UInt16
myInt16 Int16,
myInt16Array BLOB, --Int16
myInt32 Int32,
myInt32Array BLOB, --Int32
myInt64 Int64,
myInt64Array BLOB, --Int64
myReal32 Real32,
myReal32Array BLOB, --Real32
myReal64 Real64,
myReal64Array BLOB --Real64
);
INSERT INTO "myTable" VALUES
('This is text','"blue" "red" "orange" "yellow"',
0,X'00010001',
255,X'FFFEFDFCFBFAF9F8F7F6',
65535,X'FEFFFDFFFCFFFBFFFAFFFAFFFBFFFCFFFDFF',
32767,X'FF7F01800100020003000400050006000700',
2147483647,X'FF7F00800100',
9223372036854775807,X'FF7F00800100',
3.40282346638528897567e+38,X'7F7F70443901803F004040408040A040C040E040',
1.79769313486231063271e+308,X'66E62440AE47E17A14AEF33F33B323C0007862A441A7C043'),
('This is
text2',x'22626C75652220227265642220226F72616E676522202279656C6C6F7722',
0,X'00010001',
255,X'FFFEFDFCFBFAF9F8F7F6',
65535,X'FEFFFDFFFCFFFBFFFAFFFAFFFBFFFCFFFDFF',
32767,X'FF7F01800100020003000400050006000700',
2147483647,X'FF7F00800100',
9223372036854775807,X'FF7F00800100',
3.40282346638528897567e+38,X'7F7F70443901803F004040408040A040C040E040',
1.79769313486231063271e+308,X'66E62440AE47E17A14AEF33F33B323C0007862A441A7C043');
// Query showing only [BLOB] placeholders for all actual BLOBs
SELECT typeof(myTextArray), myText, myTextArray, myBool, myBoolArray,
myUInt8, myUInt8Array FROM myTable;
-- typeof(m- | ||| | |
-- yTextArr- | ||| myBoolAr-
| | myUInt8A-
-- ay) | myText | myTextArray|
myBool | ray | myUInt8 | rray
-- - | --- | -- |
-- | - | --- | -
-- text | This is text| "blue" "red" "orange" "yellow" |
0 | [Blob]| 255 | [Blob]
-- blob | This is text2 | [Blob] |
0 | [Blob]| 255 | [Blob]
// Same query showing full BLOB values(this program uses getBytes()
internally when an actual BLOB is encountered)
SELECT typeof(myTextArray), myText, myTextArray, myBool, myBoolArray,
myUInt8, myUInt8Array FROM myTable;
-- typeof(m- | | || | |
-- yTextArr- | | || | |
-- ay) | myText |
myTextArray |
myBool | myBoolArray | myUInt8 | myUInt8Array
-- - | --- |
|
-- | | --- |
-- text | This is text| "blue" "red" "orange"
"yellow" |0 | 0x00010001 |
255 | 0xFFFEFDFCFBFAF9F8F7F6
-- blob | This is text2 |
0x22626C75652220227265642220226F72616E676522202279656C6C6F7722 |0
| 0x00010001 | 255 | 0xFFFEFDFCFBFAF9F8F7F6
--
>
>
> On 2015-07-02 01:47 PM, William Drago wrote:
>> All,
>>
>> Below is a .dump of the database I'm working with. I can use
>> GetBytes() successfully on all the BLOB columns except myTextArray.
>> On that column I get a System.InvalidCastException error. Can anyone
>> tell me why? Thanks.
>>
>> -Bill
>>
>>
>> ---.dump file---
>>
>> PRAGMA foreign_keys=OFF;
>> BEGIN TRANSACTION;
>> CREATE TABLE myTable (
>>
>> myText Text,
>>
>> myTextArray BLOB, --Text
>>
>> myBool Boolean,
>>
>> myBoolArray BLOB, --Boolean
>>
>> myUInt8 UInt8,
>>
>> myUInt8Array BLOB, --UInt8
>>
>> myUInt16 UInt16,
>>
>> myUInt16Array BLOB, --UInt16
>>
[sqlite] Reader.GetBytes() - when is a byte not a byte?
That exception is only thrown if the source column is not a BLOB (as far
as I can tell).
You specify the column Type (or type affinity) as BLOB but then you
store a non-BLOB TEXT value in it ('"blue" "red" "orange" "yellow"' <--
Not a BLOB)
SQLite will store this as TEXT, not BLOB.
A BLOB starts with an x followed by a string of Hex, like in your other
inserted values such as: X'FFFEFDFCFBFAF9F8F7F6'
The BLOB for your '"blue" "red" "orange" "yellow"' string will look like
this:
x'22626C75652220227265642220226F72616E676522202279656C6C6F7722'
Insert that and getBytes() will work just fine.
PS: This solution presupposes the enforcement of BLOB-ness, I'm not
exactly sure if this is the intention from SQLite or indeed a bug /
undesired side-effect.
On 2015-07-02 01:47 PM, William Drago wrote:
> All,
>
> Below is a .dump of the database I'm working with. I can use
> GetBytes() successfully on all the BLOB columns except myTextArray. On
> that column I get a System.InvalidCastException error. Can anyone tell
> me why? Thanks.
>
> -Bill
>
>
> ---.dump file---
>
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE myTable (
>
> myText Text,
>
> myTextArray BLOB, --Text
>
> myBool Boolean,
>
> myBoolArray BLOB, --Boolean
>
> myUInt8 UInt8,
>
> myUInt8Array BLOB, --UInt8
>
> myUInt16 UInt16,
>
> myUInt16Array BLOB, --UInt16
>
> myInt16 Int16,
>
> myInt16Array BLOB, --Int16
>
> myInt32 Int32,
>
> myInt32Array BLOB, --Int32
>
> myInt64 Int64,
>
> myInt64Array BLOB, --Int64
>
> myReal32 Real32,
>
> myReal32Array BLOB, --Real32
>
> myReal64 Real64,
>
> myReal64Array BLOB --Real64
>
> );
> INSERT INTO "myTable" VALUES('This is text','"blue" "red" "orange"
> "yellow"',0,X'00010001',255,X'FFFEFDFCFBFAF9F8F7F6',65535,X'FEFFFDFFFCFFFBFFFAFFFAFFFBFFFCFFFDFF',32767,X'FF7F01800100020003000400050006000700',2147483647,X'FF7F00800100',9223372036854775807,X'FF7F00800100',3.40282346638528897567e+38,X'7F7F70443901803F004040408040A040C040E040',1.79769313486231063271e+308,X'66E62440AE47E17A14AEF33F33B323C0007862A441A7C043');
>
>
> COMMIT;
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Reader.GetBytes() - when is a byte not a byte?
Please provide your code context, as in, http://stackoverflow.com/questions/10746237/sqlite-in-c-sharp-throws-invalidcastexception-using-getbytes Today, Bill wrote: > > All, > Below is a .dump of the database I'm working with. I can use > GetBytes() successfully on all the BLOB columns except > myTextArray. On that column I get a > System.InvalidCastException error. Can anyone tell me why? > Thanks > -Bill >
[sqlite] Reader.GetBytes() - when is a byte not a byte?
On 2 Jul 2015, at 12:47pm, William Drago wrote: > Below is a .dump of the database I'm working with. I can use GetBytes() > successfully on all the BLOB columns except myTextArray. On that column I get > a System.InvalidCastException error. Can anyone tell me why? Thanks. SQLite has no inherent understanding of arrays. Although your code looks like it should just be storing and recalling the string "blue" "red" "orange" "yellow" with its quotes and space I suspect that some part of the Reader.GetBytes() routine is assuming it will get an array, whereas it's really just getting a chunk of octets. You may have to read the column into text, then use your language to split the text up into array elements. Simon.
[sqlite] Reader.GetBytes() - when is a byte not a byte?
All,
Below is a .dump of the database I'm working with. I can use
GetBytes() successfully on all the BLOB columns except
myTextArray. On that column I get a
System.InvalidCastException error. Can anyone tell me why?
Thanks.
-Bill
---.dump file---
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE myTable (
myText Text,
myTextArray BLOB, --Text
myBool Boolean,
myBoolArray BLOB, --Boolean
myUInt8 UInt8,
myUInt8Array BLOB, --UInt8
myUInt16 UInt16,
myUInt16Array BLOB, --UInt16
myInt16 Int16,
myInt16Array BLOB, --Int16
myInt32 Int32,
myInt32Array BLOB, --Int32
myInt64 Int64,
myInt64Array BLOB, --Int64
myReal32 Real32,
myReal32Array BLOB, --Real32
myReal64 Real64,
myReal64Array BLOB --Real64
);
INSERT INTO "myTable" VALUES('This is text','"blue" "red"
"orange"
"yellow"',0,X'00010001',255,X'FFFEFDFCFBFAF9F8F7F6',65535,X'FEFFFDFFFCFFFBFFFAFFFAFFFBFFFCFFFDFF',32767,X'FF7F01800100020003000400050006000700',2147483647,X'FF7F00800100',9223372036854775807,X'FF7F00800100',3.40282346638528897567e+38,X'7F7F70443901803F004040408040A040C040E040',1.79769313486231063271e+308,X'66E62440AE47E17A14AEF33F33B323C0007862A441A7C043');
COMMIT;
