On Mon, Apr 13, 2009 at 1:43 PM, Julian Bui <julian...@gmail.com> wrote:
> Oops didn't read your other comment:
>
>
>> Why? SQLite is perfectly capable of storing multiple datatypes in a
>> single column. It sounds to me like you are making your problem much
>> harder than it needs to be.
>
>
> Are you talking about the column affinity option?
What Richard is saying is that SQLite will happily take whatever kind
of value you want to stuff in a column (except, of course, for an
INTEGER PRIMARY KEY column). So, why bother BLOBbing your inputs if
they are not binary... from your original post, they are not binary,
but could be INTEGER, TEXT, REAL.... well, just stuff them in the
column. No need to convert them to BLOB. Consider the following --
sqlite> CREATE TABLE foo (a INTEGER PRIMARY KEY, b NOTABLOB);
sqlite> INSERT INTO foo (b) VALUES (1);
sqlite> INSERT INTO foo (b) VALUES ('one');
sqlite> INSERT INTO foo (b) VALUES (1.001);
sqlite> SELECT * FROM foo;
1|1
2|one
3|1.001
>
> -Julian
>
> On Mon, Apr 13, 2009 at 11:41 AM, Julian Bui <julian...@gmail.com> wrote:
>
>> By encoding I mean using some function to transform my binary data in such
>> a way that removes the terminators and single quote characters. However,
>> from what you said, it sounds like I do not need to worry about encoding.
>>
>> I am using ...VALUES(?) in a prepared statement and I will be using either
>> .setBytes() or .setBlob() in my JDBC driver. And again, it sounds like I
>> will not need to encode, from your reply.
>>
>> Thanks,
>> Julian
>>
>>
>> On Mon, Apr 13, 2009 at 11:26 AM, D. Richard Hipp <d...@hwaci.com> wrote:
>>
>>>
>>> On Apr 13, 2009, at 2:14 PM, Julian Bui wrote:
>>>
>>> > Hi all,
>>> >
>>> > I have a question about encoding blob bytes before inserting a
>>> > record into
>>> > SQLite.
>>> >
>>> > CONTEXT:
>>> > I have a base java (I'm using JDBC) class that has a many children.
>>> > Each
>>> > child may store a different data type (such as String, int, long,
>>> > etc.) in
>>> > its VALUE field. Since this value field could be many different
>>> > data types,
>>> > I will be storing the field's bytes into the database as a blob.
>>>
>>> Why? SQLite is perfectly capable of storing multiple datatypes in a
>>> single column. It sounds to me like you are making your problem much
>>> harder than it needs to be.
>>>
>>> >
>>> >
>>> > QUESTIONS:
>>> >
>>> > -I have seen a couple of places on the internet saying that SQLite
>>> > cannot
>>> > inserting binary data unless it has been encoded. Can someone
>>> > confirm this
>>> > or preferrably point me to an OFFICIAL sqlite statement/page saying
>>> > that
>>> > this is true?
>>>
>>> I'm not sure what you mean by "encoded". If you do:
>>>
>>> INSERT INTO sometable VALUES(?)
>>>
>>> And then bind a blob to the ?, you do not need to make any
>>> transformations to the blob ahead of time. On the other hand, if you
>>> say:
>>>
>>> INSERT INTO sometable VALUES(x'0123456789abcdef');
>>>
>>> Then clearly you have had to convert your 8-byte blob into hexadecimal
>>> in order to insert it into the middle of your INSERT statement.
>>>
>>> The first technique (the use of ? and sqlite3_bind_blob()) is
>>> preferred since it is both faster and less error-prone.
>>>
>>>
>>> D. Richard Hipp
>>> d...@hwaci.com
>>>
>>>
>>>
>>> _______________________________________________
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Carbon Model http://carbonmodel.org/
Open Source Geospatial Foundation http://www.osgeo.org/
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
