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? Thanks, Julian On Mon, Apr 13, 2009 at 11:47 AM, P Kishor <[email protected]> wrote: > On Mon, Apr 13, 2009 at 1:43 PM, Julian Bui <[email protected]> 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 <[email protected]> > 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 <[email protected]> > 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 > >>> [email protected] > >>> > >>> > >>> > >>> _______________________________________________ > >>> sqlite-users mailing list > >>> [email protected] > >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >>> > >> > >> > > _______________________________________________ > > sqlite-users mailing list > > [email protected] > > 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 > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
