Clarification on Storage

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Clarification on Storage

Stephen Chrzanowski
I'm fully aware that any kind of data can be stored in any kind of field.
Strings can be in integer declared fields, etc.

What I'm interested in finding out, without changing my code at this time,
is if SQLITE3.EXE is handling things differently than what SQLite Expert is.

In "SQLite Expert", if I declare a field as BLOB or BLOB_TEXT, the UI
itself "hides" the information pending certain declaration types on the
tables field.  SQLITE3.EXE does not.

Case in point:
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Clarification on Storage

Stephen Chrzanowski
That was NOT supposed to happen. :P

CREATE TABLE [Test](
    [ID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [T1] TEXT,
    [T2] BLOB_TEXT);

insert into Test (T1,T2) values ('Test 1','Test 2');

In SQLite Expert, it hides "Test 2" as a (blob).  I'm fine with that.
In SQLITE3.exe, it shows "Test 2".  I'm also fine with that.

What I'm not entirely sure of is whether SQLite Expert is understanding and
handling the BLOB_TEXT field and just not doing the actual query to get the
text, or, if it understand if there is some kind of special handling that
SQLite Expert is not doing and just shows (blob).

The reason I'm asking is that I've got an application that handles bulk
text inserts, and I want to make sure that I don't run into some kind of
stupid (My code, not SQLite) limitation that'd chop text out, or what have
you.

On Tue, Feb 20, 2018 at 11:17 AM, Stephen Chrzanowski <[hidden email]>
wrote:

> I'm fully aware that any kind of data can be stored in any kind of field.
> Strings can be in integer declared fields, etc.
>
> What I'm interested in finding out, without changing my code at this time,
> is if SQLITE3.EXE is handling things differently than what SQLite Expert is.
>
> In "SQLite Expert", if I declare a field as BLOB or BLOB_TEXT, the UI
> itself "hides" the information pending certain declaration types on the
> tables field.  SQLITE3.EXE does not.
>
> Case in point:
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Clarification on Storage

David Raymond
Well, according to the standard that field should have a text affinity.

http://www.sqlite.org/datatype3.html#determination_of_column_affinity

And using single quotes makes what you're giving it text values rather than blobs

http://www.sqlite.org/lang_expr.html
"A string constant is formed by enclosing the string in single quotes ('). A single quote within the string can be encoded by putting two single quotes in a row - as in Pascal. C-style escapes using the backslash character are not supported because they are not standard SQL.

BLOB literals are string literals containing hexadecimal data and preceded by a single "x" or "X" character. Example: X'53514C697465'"

So my not-expert opinion is that SQLite Expert is doing something weird.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Stephen Chrzanowski
Sent: Tuesday, February 20, 2018 11:22 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Clarification on Storage

That was NOT supposed to happen. :P

CREATE TABLE [Test](
    [ID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [T1] TEXT,
    [T2] BLOB_TEXT);

insert into Test (T1,T2) values ('Test 1','Test 2');

In SQLite Expert, it hides "Test 2" as a (blob).  I'm fine with that.
In SQLITE3.exe, it shows "Test 2".  I'm also fine with that.

What I'm not entirely sure of is whether SQLite Expert is understanding and
handling the BLOB_TEXT field and just not doing the actual query to get the
text, or, if it understand if there is some kind of special handling that
SQLite Expert is not doing and just shows (blob).

The reason I'm asking is that I've got an application that handles bulk
text inserts, and I want to make sure that I don't run into some kind of
stupid (My code, not SQLite) limitation that'd chop text out, or what have
you.

On Tue, Feb 20, 2018 at 11:17 AM, Stephen Chrzanowski <[hidden email]>
wrote:

> I'm fully aware that any kind of data can be stored in any kind of field.
> Strings can be in integer declared fields, etc.
>
> What I'm interested in finding out, without changing my code at this time,
> is if SQLITE3.EXE is handling things differently than what SQLite Expert is.
>
> In "SQLite Expert", if I declare a field as BLOB or BLOB_TEXT, the UI
> itself "hides" the information pending certain declaration types on the
> tables field.  SQLITE3.EXE does not.
>
> Case in point:
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Clarification on Storage

Richard Hipp-3
In reply to this post by Stephen Chrzanowski
One thing that it is important to be clear on is that SQLite Expert is
a 3rd-party product that is not supported nor even understood by the
official SQLite developers.  SQLite Expert, if I am not mistaken, does
try to do some magic under the hood to make SQLite function more like
how the SQLite-Expert authors think it should function, rather than
how it actually functions.  So your theory of the difference in output
being due to shenanigans perpetrated by SQLite Expert might be
correct, for all I know.

The sqlite3.exe program, on the other hand, is an officially supported
product, developed by and for the core SQLite developers.  SQLite3.exe
behaves as the SQLite core developers intend SQLite to behave.

All that said, I'm not exactly sure what you are asking.....

On 2/20/18, Stephen Chrzanowski <[hidden email]> wrote:

> That was NOT supposed to happen. :P
>
> CREATE TABLE [Test](
>     [ID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
>     [T1] TEXT,
>     [T2] BLOB_TEXT);
>
> insert into Test (T1,T2) values ('Test 1','Test 2');
>
> In SQLite Expert, it hides "Test 2" as a (blob).  I'm fine with that.
> In SQLITE3.exe, it shows "Test 2".  I'm also fine with that.
>
> What I'm not entirely sure of is whether SQLite Expert is understanding and
> handling the BLOB_TEXT field and just not doing the actual query to get the
> text, or, if it understand if there is some kind of special handling that
> SQLite Expert is not doing and just shows (blob).
>
> The reason I'm asking is that I've got an application that handles bulk
> text inserts, and I want to make sure that I don't run into some kind of
> stupid (My code, not SQLite) limitation that'd chop text out, or what have
> you.
>
> On Tue, Feb 20, 2018 at 11:17 AM, Stephen Chrzanowski <[hidden email]>
> wrote:
>
>> I'm fully aware that any kind of data can be stored in any kind of field.
>> Strings can be in integer declared fields, etc.
>>
>> What I'm interested in finding out, without changing my code at this time,
>> is if SQLITE3.EXE is handling things differently than what SQLite Expert
>> is.
>>
>> In "SQLite Expert", if I declare a field as BLOB or BLOB_TEXT, the UI
>> itself "hides" the information pending certain declaration types on the
>> tables field.  SQLITE3.EXE does not.
>>
>> Case in point:
>>
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Clarification on Storage

R Smith
On 2018/02/20 6:35 PM, Richard Hipp wrote:
> All that said, I'm not exactly sure what you are asking.....

I had some trouble with that too, but I think his question boils down to:

Considering that SQLiteExpert is doing seemingly funny things under the
hood on top of SQLite, would those be safe / necessary when I make my
own thing, or is just them being silly?

To which the answer is of course that Bogdan (who mostly reads this list
and might comment st some point) may have decided to implement some
special circumstances to perhaps pull extra functionality out of SQLite,
but it isn't needed - the standard way (like sqlite3.exe does) is
perfectly fine and certainly won't break for any normal circumstances.


Cheers!
Ryan


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Clarification on Storage

Jean-Christophe Deschamps-3
In reply to this post by Richard Hipp-3
At 17:35 20/02/2018, you wrote:
>SQLite Expert, if I am not mistaken, does
>try to do some magic under the hood to make SQLite function more like
>how the SQLite-Expert authors think it should function, rather than
>how it actually functions.  So your theory of the difference in output
>being due to shenanigans perpetrated by SQLite Expert might be
>correct, for all I know.

There is maybe a much simpler reason: SQLite uses a grid component of
some Delphi library (ACAICT) which refers to the column definition
rather than individual values' datatype. For instance if a column is
declared as IMAGE datatype, then the data grid won't display text or
numeric values, only thumbnails if you've allowed them in options.

The definitive answer will come from Expert support.


--
Jean-Christophe Deschamps
2891 route de Pouillon
40180 Heugas
France
06 15 10 19 29  

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Clarification on Storage

Stephen Chrzanowski
In reply to this post by Richard Hipp-3
I'm absolutely clear that the SQLite3 developers have absolutely NOTHING to
do with SQLite Expert.  I apologize if there was any indication that I was
thinking that I was asking for the devs of SQLite3 would have a direct
answer to how that 3rd party tool works.

My hope was that someone who uses SQLite Expert (Or if Bogdan is watching)
could offer a hint to whether or not my software will require special
requirements to make absolute certain that the full amount of text I'm
entering is inserted properly.

I'm just trying to understand if there is ever going to be a potential
limitation on data entry using a standard insert/update command versus my
having to convert what I'm inserting into the "X" prefixed-hex-type.
Honestly, the GUI component I'm using I believe is limited to 65k worth of
text, but that was in the Delphi 5 days, and I'm unsure if Berlin has
changed that limitation.



On Tue, Feb 20, 2018 at 11:35 AM, Richard Hipp <[hidden email]> wrote:

> One thing that it is important to be clear on is that SQLite Expert is
> a 3rd-party product that is not supported nor even understood by the
> official SQLite developers.  SQLite Expert, if I am not mistaken, does
> try to do some magic under the hood to make SQLite function more like
> how the SQLite-Expert authors think it should function, rather than
> how it actually functions.  So your theory of the difference in output
> being due to shenanigans perpetrated by SQLite Expert might be
> correct, for all I know.
>
> The sqlite3.exe program, on the other hand, is an officially supported
> product, developed by and for the core SQLite developers.  SQLite3.exe
> behaves as the SQLite core developers intend SQLite to behave.
>
> All that said, I'm not exactly sure what you are asking.....
>
> On 2/20/18, Stephen Chrzanowski <[hidden email]> wrote:
> > That was NOT supposed to happen. :P
> >
> > CREATE TABLE [Test](
> >     [ID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
> >     [T1] TEXT,
> >     [T2] BLOB_TEXT);
> >
> > insert into Test (T1,T2) values ('Test 1','Test 2');
> >
> > In SQLite Expert, it hides "Test 2" as a (blob).  I'm fine with that.
> > In SQLITE3.exe, it shows "Test 2".  I'm also fine with that.
> >
> > What I'm not entirely sure of is whether SQLite Expert is understanding
> and
> > handling the BLOB_TEXT field and just not doing the actual query to get
> the
> > text, or, if it understand if there is some kind of special handling that
> > SQLite Expert is not doing and just shows (blob).
> >
> > The reason I'm asking is that I've got an application that handles bulk
> > text inserts, and I want to make sure that I don't run into some kind of
> > stupid (My code, not SQLite) limitation that'd chop text out, or what
> have
> > you.
> >
> > On Tue, Feb 20, 2018 at 11:17 AM, Stephen Chrzanowski <
> [hidden email]>
> > wrote:
> >
> >> I'm fully aware that any kind of data can be stored in any kind of
> field.
> >> Strings can be in integer declared fields, etc.
> >>
> >> What I'm interested in finding out, without changing my code at this
> time,
> >> is if SQLITE3.EXE is handling things differently than what SQLite Expert
> >> is.
> >>
> >> In "SQLite Expert", if I declare a field as BLOB or BLOB_TEXT, the UI
> >> itself "hides" the information pending certain declaration types on the
> >> tables field.  SQLITE3.EXE does not.
> >>
> >> Case in point:
> >>
> >>
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Clarification on Storage

Simon Slavin-3
In reply to this post by Stephen Chrzanowski
On 20 Feb 2018, at 4:21pm, Stephen Chrzanowski <[hidden email]> wrote:

> What I'm not entirely sure of is whether SQLite Expert is understanding and
> handling the BLOB_TEXT field and just not doing the actual query to get the
> text, or, if it understand if there is some kind of special handling that
> SQLite Expert is not doing and just shows (blob).

sqlite.exe always tells the unvarnished truth.  Especially if you do things like

        SELECT T1,typeof(T1),T2,typeof(T2) FROM Test

So if you're trying to work out what some other product is really doing, use that third-party product to make the database, then use sqlite3.exe to open and explore it.  Probably starting with this command

        .schema

Of course, you cannot predict the shortcomings of the third-party tool.  For instance, a product called (I just made this name up) _SQLite Manipulator_ may use a string library which limits strings to 2048 characters.  So it may, undocumented, truncate all strings heading to the database.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Clarification on Storage

Drago, William @ CSG - NARDA-MITEQ-2
In reply to this post by Stephen Chrzanowski
> What I'm interested in finding out, without changing my code at this time, is if
> SQLITE3.EXE is handling things differently than what SQLite Expert is.

It absolutely is. I am a heavy user of SQLite Expert and was stumped by weird problems in my databases until I realized the SQLite Expert does strange things with types behind the scenes. So, I create/manage my databases with SQLite and use SQLite Expert only for viewing data.

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / [hidden email]
CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of the intended recipient and may contain material that is proprietary, confidential, privileged or otherwise legally protected or restricted under applicable government laws. Any review, disclosure, distributing or other use without expressed permission of the sender is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies without reading, printing, or saving..

Beginning April 1, 2018, L3 Technologies, Inc. will discontinue the use of all @L-3Com.com email addresses. To ensure delivery of your messages to this recipient, please update your records to use [hidden email].
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Clarification on Storage

Stephen Chrzanowski
Thanks all.  I'll consider this thread done.  With Ryan and Bills comments,
I've decided that I'll truck on as is, and not worry about special string
considerations.

Thinking about how Delphi Berlin is handling strings at the compiler level,
and how the wrapper I use is handling the strings based on Delphi 5/7
innuendos, I've kind of got a my finger on its pulse to how long I'm going
to use it, and just start rolling my own or find a different, more recent,
and free library that isn't completely overkill.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users