Re: [sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Clarification on Storage

[Drago, William @ CSG - NARDA-MITEQ]

> 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 / william.dr...@l3t.com
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 william.dr...@l3t.com.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Clarification on Storage

[Simon Slavin]

On 20 Feb 2018, at 4:21pm, Stephen Chrzanowski  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Clarification on Storage

[Stephen Chrzanowski]

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  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  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 <
> pontia...@gmail.com>
> > 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
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Clarification on Storage

[Jean-Christophe Deschamps]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Clarification on Storage

[Richard Hipp]

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  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 
> 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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:sqlite-users-boun...@mailinglists.sqlite.org] 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 <pontia...@gmail.com>
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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 
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users