On Mon, Apr 13, 2009 at 2:00 PM, Julian Bui <julian...@gmail.com> wrote:
> Thanks for your response Puneet.
>
> I was originally just converting my non-blobs (ints, text, reals) to byte
> arrays since I need to do that in my program anyway.
>
> But now since you've mentioned it, I will look into storing multiple types
> in the same column..
>
> One thing I don't understand is, you wrote:
>
>> sqlite> CREATE TABLE foo (a INTEGER PRIMARY KEY, b NOTABLOB);
>
> I take it that "NOTABLOB" is any data type I want, since "NOTABLOB" is not a
> keyword/datatype. Doesn't that mean I will restrict myself to a single
> datatype?
If you don't like the datatype NOTABLOB, try datatype THINGAMAJIG
Short answer, SQLite doesn't care other than what it states in the
docs (see the page dataypes on sqlite.org). And, even if you declare
the legit datatypes that it understands (INTEGER, TEXT, REAL and
BLOB), it will croak only if there is a CHECK constraint (for how that
works, see Richard's email on that subject from a few minutes ago).
It does care about "INTEGER PRIMARY KEY," a magical combination of
keywords that makes it croak if the inserted value is not an INTEGER
or not unique. Consider
sqlite> CREATE TABLE foo (a INTEGER PRIMARY KEY, b SLUMDOGMILLIONAIRE);
sqlite> INSERT INTO foo (b) VALUES (1);
sqlite> SELECT * FROM foo;
1|1
sqlite> INSERT INTO foo (a, b) VALUES (1, 'one');
SQL error: PRIMARY KEY must be unique
sqlite> INSERT INTO foo (a, b) VALUES ('one', 'one');
SQL error: datatype mismatch
>
> Thanks,
> Julian
>
> On Mon, Apr 13, 2009 at 11:47 AM, P Kishor <punk.k...@gmail.com> wrote:
>>
>> 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
>
>
--
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/
Sent from Madison, WI, United States
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
