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'FFFFFEFFFDFFFCFFFBFFFAFFFAFFFBFFFCFFFDFF',
32767,X'FF7F018001000200030004000500060007000000',
2147483647,X'FFFFFF7F000000800000000001000000',
9223372036854775807,X'FFFFFFFFFFFFFF7F000000000000008000000000000000000100000000000000',
3.40282346638528897567e+38,X'FFFF7F7F70443901000000000000803F0000004000004040000080400000A0400000C0400000E040',
1.79769313486231063271e+308,X'6666666666E62440AE47E17A14AEF33F3333333333B323C0007862A441A7C043'),
('This is
text2',x'22626C75652220227265642220226F72616E676522202279656C6C6F7722',
0,X'00010001',
255,X'FFFEFDFCFBFAF9F8F7F6',
65535,X'FFFFFEFFFDFFFCFFFBFFFAFFFAFFFBFFFCFFFDFF',
32767,X'FF7F018001000200030004000500060007000000',
2147483647,X'FFFFFF7F000000800000000001000000',
9223372036854775807,X'FFFFFFFFFFFFFF7F000000000000008000000000000000000100000000000000',
3.40282346638528897567e+38,X'FFFF7F7F70443901000000000000803F0000004000004040000080400000A0400000C0400000E040',
1.79769313486231063271e+308,X'6666666666E62440AE47E17A14AEF33F3333333333B323C0007862A441A7C043');
// 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
>>
>> 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'FFFFFEFFFDFFFCFFFBFFFAFFFAFFFBFFFCFFFDFF',32767,X'FF7F018001000200030004000500060007000000',2147483647,X'FFFFFF7F000000800000000001000000',9223372036854775807,X'FFFFFFFFFFFFFF7F000000000000008000000000000000000100000000000000',3.40282346638528897567e+38,X'FFFF7F7F70443901000000000000803F0000004000004040000080400000A0400000C0400000E040',1.79769313486231063271e+308,X'6666666666E62440AE47E17A14AEF33F3333333333B323C0007862A441A7C043');
>>
>>
>> COMMIT;
>>
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
