Re: [sqlite] Clarification on Storage
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
> 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
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
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
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
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
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
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 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
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
[sqlite] Clarification on Storage
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