Thank you everybody; casing to BLOB on insert is the solution I was looking
for. This will ensure length() returns the number of bytes, which is
exactly what I want. Thanks!
-david
On Sun, Mar 1, 2015 at 1:05 PM, Keith Medcalf <kmedcalf at dessus.com> wrote:
>
> length() on a blob returns the length of the blob without examining the
> blob itself. The length is encoded in the data structure.
> length() on text returns the number of characters in the text. This
> requires retrieving the data and counting.
>
> Whether sqlite stores a blob or text in the column depends on what you
> pass it. For example:
>
> sqlite> create table x(x blob);
> sqlite> insert into x values ('x');
> sqlite> insert into x values (cast('x' as blob));
> sqlite> select * from x;
> x
> x
> sqlite> select typeof(x) from x;
> text
> blob
>
> So, if you insert text into a column, text is stored. If you insert a
> blob into a column, a blob is stored. Column affinity is (as in all cases)
> practically irrlevant. What you put will depend on how you put it (if
> inline as in the above example), or whether you use bind_blob or bind_text
> when storing the data -- just as what you get out depends on the type you
> ask for when retrieving the data.
>
> ---
> Theory is when you know everything but nothing works. Practice is when
> everything works but no one knows why. Sometimes theory and practice are
> combined: nothing works and no one knows why.
>
>
> >-----Original Message-----
> >From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> >bounces at mailinglists.sqlite.org] On Behalf Of David Barrett
> >Sent: Sunday, 1 March, 2015 11:41
> >To: General Discussion of SQLite Database
> >Subject: [sqlite] Does length() use strlen() on blobs?
> >
> >Hi! If I define a column as BLOB type, will length() run strlen() on the
> >data, or will it consult some kind of internal value to determine the
> >length? The documentation says:
> >
> >"For a string value X, the length(X) function returns the number of
> >characters (not bytes) in X prior to the first NUL character. ... For a
> >blob value X, length(X) returns the number of bytes in the blob."
> >
> >This suggests to me it uses some internal length value (eg, not strlen),
> >but given that sqlite doesn't use strong typing, I'm wondering if I
> >insert
> >a string into that blob if it'll return the blob length or the string
> >length.
> >
> >I ask because I'm going to be using sqlite on a performance-sensitive
> >application to store small strings (as blobs), and one of the most
> >frequent
> >queries will be to get the length of the blob. I'm wondering if I should
> >create a separate length column, or if I should just use length().
> >
> >Thanks!
> >
> >-david
> >_______________________________________________
> >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
>
