[sqlite] Problem when upgrading from FTS3/4 to FTS5 modules (revisited)

[a...@zator.com]

Hi list:

In a C++ Windows app that uses SQLite v. 3.9.1 and behaves well, I try change 
the search engine from FTS3/4 modules to FTS5, by means off:

1. Define the directive
#define SQLITE_ENABLE_FTS5 1

2.-  Create the table:
CREATE VIRTUAL TABLE ftsm USING fts5(nm,tokenize='porter unicode61 
remove_diacritics 0',columnsize=0)

3.- Populate the table:
INSERT INTO ftsm (row,nm) SELECT id,nm FROM atm WHERE(..)

After that, the app and the search engine works as espected.

To update the ftsm table after several inserts and deletes, I try to follow 
this steps

1a.- Delete the previous table.
DROP TABLE IF EXIST ftsm

2a.- Create table (as above)

3a.- Populate table (as above).

This scheme work ok with the FST3/4 modules, but compiling with FTS5 gives an 
erroro in 1a:  "database disk image is malformed".

Note that in previous attemps I believed that the problem was into try to 
delete a ftsm table build with the previous modules, but the error happen when 
trying delete a table build with the FTS5 module.

I managed to drop the ftsm table by means of create a new dbase; create the 
same tables (except ftsm); populate the tables, and replacing the previous 
dbase with the new one. But obviously, this method is primitive; time 
consuming, and has problems when the dbase is in use.

Some clues?

Thanks in advance.

--
Adolfo J. Millan

[sqlite] INSERT OR IGNORE consuming too many rowids

[R Smith]

On 2015/12/09 10:35 PM, Wade, William wrote:
> I may be reading different documentation (or perhaps just misreading it).
>
> https://www.sqlite.org/autoinc.html says (for the no AUTOINCREMENT case, 
> which applies to the original post) "If no ROWID is specified on the insert 
> ... [the] usual algorithm is to give the newly created row a ROWID that is 
> one larger than the largest ROWID in the table prior to the insert."
>
> And goes on to strongly imply that "not usual" means either the table is 
> currently empty, or the largest ROWID is already at the largest possible 
> integer. The implication is pretty strong that leaving ROWID unspecified on 
> an insert is equivalent (in the usual case) to specifying MAX(ROWID) + 1.
>
> I understand the difference between "documenting some details about how 
> things currently happen to behave" and "documenting the intended behavior." 
> When I read about the -journal or -wal files, I'm usually happy with the 
> thought that in the future SQLite may support (or use exclusively) an 
> entirely different rollback mechanism. It isn't entirely obvious which 
> category autoinc.html falls into, but I believe that a reasonable reader can 
> interpret that as documenting intended behavior.

You are correct, but I think misreading what Richard said. Richard 
specifically referred to what the OP stated is /his/ "desired" behaviour 
(hence the quotes), not what is the SQLite Dev's or SQLite or indeed the 
SQL standard's desired behaviour, and Richard mentioned that THAT 
specific behaviour is indeed NOT the way it works or is documented.

To be clear, it is documented very specifically that in the absence of 
AUTOINCREMENT, any value that isn't yet used for the INT PK may be used, 
typically (or usually) the MAX(pk)+1. It then goes on to say that when 
AUTOINCREMENT IS indeed specified, you can be guaranteed that any value 
that was ever used before cannot be used again, regardless of whether 
the data was deleted and such. (i.e. specifically NOT MAX(pk)+1).

Hope that makes more sense!
Ryan

[sqlite] Obscure peculiarity with 3.9.2

[R Smith]

On 2015/12/09 10:55 PM, Richard Hipp wrote:
> On 12/9/15, R Smith  wrote:
>> One of our systems suddenly started to play up after upgrading to 3.9.2
>> from 3.8.8 (so I am not sure exactly when the oddity was introduced).
>>
>> SQLite:
>> v 3.9.2 linked on WIndows (various versions) in a 32 bit application via
>> the exact 32bit DLL published on the downloads page on sqlite.org.
>>
>> The error:
>> During an update to an attached DB, the statement fails and reports
>> "Disk I/O Error" and leaves a hot journal (even though the statement
>> wasn't in an explicit transaction).
>>
> We can make a script to do this.  And we get this error (when we
> enable logging - https://www.sqlite.org/errlog.html - which you really
> should do too).
>
> {SQLITE_IOERR_DELETE {os_win.c:39624: (32) 
> winDelete(C:\Users\drh\sqlite\sqlite\
> test2.db-journal) - The process cannot access the file because it is being 
> used
> by another process.}
>
> The problem is that one connection is trying to delete the journal
> file that the other connection is holding open.
>
> The thing is this:  We can reproduce this going all the way back to
> version 3.8.0 (which is as far back as I looked.)  So I don't know how
> you managed to get it working on your 3.8.8 version.

This is slightly confusing - I think perhaps other actions we do allowed 
it to slip through (or perhaps complete the deletion before the other 
file access) in our code. But that is just me guessing now.

Also, apologies for the laziness - I would have switched the logger on 
(we do have that of course) but I simply stepped through the code, saw 
the return value, went back to the connection origins, saw the different 
modes, corrected it and then things worked, so I did not do a full 
analysis, but decided it's probably worth reporting it.

I'll do better next time before reporting :)

[sqlite] Obscure peculiarity with 3.9.2

[R Smith]

One of our systems suddenly started to play up after upgrading to 3.9.2 
from 3.8.8 (so I am not sure exactly when the oddity was introduced).

SQLite:
v 3.9.2 linked on WIndows (various versions) in a 32 bit application via 
the exact 32bit DLL published on the downloads page on sqlite.org.

The error:
During an update to an attached DB, the statement fails and reports 
"Disk I/O Error" and leaves a hot journal (even though the statement 
wasn't in an explicit transaction).

It's unfortunately not something I can make an SQL script for or send 
code since it has to do with the creating of connections and the code is 
quite involved and will cause more confusion than answers (The same SQL 
succeeds on other connections), but I can lay out the exact steps 
causing it:

The steps:
We create 2 different connections at program start-up, the main data DB 
and a cache DB.
I will refer to them as conMain and conCache.
They are initialized as follows:
conCache:
  PRAGMA journal_mode = TRUNCATE;
  PRAGMA cache_size = 8000; -- 8K Pages cached
  PRAGMA temp_store = 2;-- Memory
  PRAGMA synchronous = 0;   -- More Speed

conMain:
  PRAGMA journal_mode = DELETE; -- Normal Mode
  PRAGMA cache_size = 16000;-- 16K Pages cached
  PRAGMA synchronous = 0;   -- Speed Mode
  PRAGMA soft_heap_limit = 0;   -- No lim
  PRAGMA temp_store = 2;-- Memory

(yes, these do not need to be very secure, they are copies of the real 
data, but they are updated internally in the short term)

We then also attach the cache DB file to the main DB as "TmpDB" with the 
usual SQL:
conMain.Execute("ATTACH 'd:\path\to\cache_db_file.db' AS TmpDB;");

Now, essentially 2 connections exist to the cache DB file, one direct 
connection set to TRUNCATE journaling, and one as attachment to the main 
DB connection with DELETE journaling (I mention this because my theory 
is that this may cause the problem).

Then, upon user request, 2 tables are created (or dropped and recreated) 
in the cache DB using the conCache connection object, and then they are 
populated with data (without incident).

Some data for the second table depends on data in the main DB, so we 
calculate and store it in a TEMP table (here called "local") using 
conMain. We then add it to the second of those previously created cache 
DB tables via an UPDATE query on the conMain connection to the attached 
cache db as "TmpDB".

To be clear, this is the format of the query:

s = 'UPDATE TmpDB.Tbl2 SET
   TmpDB.Tbl2.aa = (SELECT(local.a) FROM local WHERE local.id = 
TmpDB.Tbl2.id);
   TmpDB.Tbl2.bb = (SELECT(local.b) FROM local WHERE local.id = 
TmpDB.Tbl2.id);
   TmpDB.Tbl2.cc = (SELECT(local.c) FROM local WHERE local.id = 
TmpDB.Tbl2.id);
   // etc.
';
conMain.Execute(s); // Not explicitly transacted, and this simply 
wraps the normal sqlite3_prepareV2() .. step() .. reset. chain.

This all worked perfectly in 3.8.8, but dropping in the 3.9.2 DLL, this 
last UPDATE statement fails with "Disk I/O error" (which is just the 
error translator's answer, I think sqlite_step() returns error code 
2570) and leaves the hot journal (with about 360 kbytes in) on the CACHE 
DB.
Operation can resume normally after it - apart from the fact that the 
updates did not happen, everything else works without incident and the 
data is fine.

If I change the conCache journal mode to DELETE in stead of TRUNCATE, it 
works again - and that is indeed what I have done which solves my 
problem, so the fix isn't urgent or even required. (There might even be 
documentation stating not to mix journal modes like this, but I am 
unaware of it).


Thanks,
Ryan

[sqlite] INSERT OR IGNORE consuming too many rowids

[Simon Slavin]

On 9 Dec 2015, at 9:10pm, David Baird  wrote:

> My cocnern is that: the database engine shouldn't
> needlessly waste/discard perectly good chunks of rowids

Please don't think of them as 'chunks'.  There is no order to the numbers it 
picks.  An alternative way to think of the AUTOINCREMENT mechanism is that it 
simply picks a random number, checks to see that it's not already in use, and 
returns that.

You shouldn't depend on anything 'sequency' about the rowids it creates.  If 
your program depends on them being a sequence, generate the numbers you want 
yourself, in the sequence you want.

Simon.

[sqlite] INSERT OR IGNORE consuming too many rowids

[David Baird]

On Dec 9, 2015 7:14 PM, "Simon Slavin"  wrote:
>
>
> On 9 Dec 2015, at 9:10pm, David Baird  wrote:
>
> > My cocnern is that: the database engine shouldn't
> > needlessly waste/discard perectly good chunks of rowids
>
> Please don't think of them as 'chunks'.  There is no order to the numbers
it picks.  An alternative way to think of the AUTOINCREMENT mechanism is
that it simply picks a random number, checks to see that it's not already
in use, and returns that.
>
> You shouldn't depend on anything 'sequency' about the rowids it creates.
If your program depends on them being a sequence, generate the numbers you
want yourself, in the sequence you want.
>

Yes, I am aware. Note that the application is correct as it doesn't care
about rowid 'sequenceyness'- but that's not what the OP is about at all.
The OP was concerning a minor technical issue regarding the implementation
details of SQLite. All other points, I agree with, but they're moot anyways
because this isn't about application correctness (the application is
already happily correct!).

[sqlite] System.Data.SQLite version 1.0.99.0 released

[Joe Mistachkin]

System.Data.SQLite version 1.0.99.0 (with SQLite 3.9.2) is now available on
the System.Data.SQLite website:

 https://system.data.sqlite.org/

Further information about this release can be seen at:

 https://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki

Please post on the SQLite mailing list (sqlite-users at sqlite.org) if you
encounter any problems with this release.

--
Joe Mistachkin

[sqlite] Setting locale for ORDER BY

[Cecil Westerhof]

I want that ? comes after e, not after z. When using Python I get the right
ordering of ORDER BY with the following statement:
locale.setlocale(locale.LC_ALL, 'en_GB.UTF-8')
but I am now using Java. I am using here:
Locale.setDefault(new Locale("en_GB.UTF-8"));

But that does change the ordering of ORDER BY (? comes after z). What is
the correct way to get the same result in Java?

-- 
Cecil Westerhof

[sqlite] INSERT OR IGNORE consuming too many rowids

[Simon Slavin]

On 9 Dec 2015, at 8:35pm, Wade, William  wrote:

> https://www.sqlite.org/autoinc.html says (for the no AUTOINCREMENT case, 
> which applies to the original post) "If no ROWID is specified on the insert 
> ... [the] usual algorithm is to give the newly created row a ROWID that is 
> one larger than the largest ROWID in the table prior to the insert."

As well as the exceptions you've noted don't forget to take into account the 
possibility that a row may be deleted after others have been inserted after it.

Simon.

[sqlite] INSERT OR IGNORE consuming too many rowids

[Wade, William]

I may be reading different documentation (or perhaps just misreading it).

https://www.sqlite.org/autoinc.html says (for the no AUTOINCREMENT case, which 
applies to the original post) "If no ROWID is specified on the insert ... [the] 
usual algorithm is to give the newly created row a ROWID that is one larger 
than the largest ROWID in the table prior to the insert."

And goes on to strongly imply that "not usual" means either the table is 
currently empty, or the largest ROWID is already at the largest possible 
integer. The implication is pretty strong that leaving ROWID unspecified on an 
insert is equivalent (in the usual case) to specifying MAX(ROWID) + 1.

I understand the difference between "documenting some details about how things 
currently happen to behave" and "documenting the intended behavior." When I 
read about the -journal or -wal files, I'm usually happy with the thought that 
in the future SQLite may support (or use exclusively) an entirely different 
rollback mechanism. It isn't entirely obvious which category autoinc.html falls 
into, but I believe that a reasonable reader can interpret that as documenting 
intended behavior.

Best Regards,
Bill

-Original Message-
From: Richard Hipp [mailto:d...@sqlite.org]
Sent: Wednesday, December 09, 2015 7:07 AM
To: SQLite mailing list
Subject: Re: [sqlite] INSERT OR IGNORE consuming too many rowids

As others have a pointed out, though, the "desired" behavior is "undocumented". 
 SQLite nor any other SQL database engine is obligated to provide the behavior 
you want.  It happens as you like purely by chance.  Do not depend on this 
behavior since it might change at any moment, without warning.


**
This e-mail and any attachments thereto may contain confidential information 
and/or information protected by intellectual property rights for the exclusive 
attention of the intended addressees named above. If you have received this 
transmission in error, please immediately notify the sender by return e-mail 
and delete this message and its attachments. Unauthorized use, copying or 
further full or partial distribution of this e-mail or its contents is 
prohibited.
**

[sqlite] Making data unique

[Andrew Stewart]

Thanks Keith.
This works.

Andrew

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf
Sent: Monday, December 07, 2015 6:53 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Making data unique


create table dataset
(
   id integer not null,
   timestamp integer not null,
   data integer not null,
   unique (id, timestamp)
);

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org 
> [mailto:sqlite-users- bounces at mailinglists.sqlite.org] On Behalf Of 
> Andrew Stewart
> Sent: Monday, 7 December, 2015 08:01
> To: 'SQLite mailing list'
> Subject: [sqlite] Making data unique
> 
> Hi,
> I have a table that consists of 3 elements:
> ID - integer
> Date/time - integer
> Data - integer
> A single ID can exist multiple times.
> A single Date/time can exist multiple times.
> An ID & Date/time combination is unique.
> 
> What is the best way to ensure uniqueness in this table.
> 
> Thanks,
> Andrew Stewart
> Software Designer
> 
> Argus Controls
> #101 - 18445 53 AVE
> Surrey, BC  V3S 7A4
> 
> t: 1-888-667-2091  ext : 108
> t: 1-604-536-9100  ext : 108
> f: 604-538-4728
> w: www.arguscontrols.com
> e: astewart at arguscontrols.com
> 
> Notice: This electronic transmission contains confidential 
> information, intended only for the person(s) named above. If you are 
> not the intended recipient, you are hereby notified that any 
> disclosure, copying, distribution, or any other use of this email is 
> strictly prohibited. If you have received this transmission by error, 
> please notify us immediately by return email and destroy the original 
> transmission immediately and all copies thereof.
> ___
> 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

[sqlite] How to see SQLite debugging information

[Bart Smissaert]

> 1) Click Start etc.

That won't show the debug output.
Wish it was that simple.

> If you want to prove your dll is working (or not
> working), you should attempt to isolate it, for direct testing.

Not sure what you mean with that. I know an error occurs in sqlite3.dll and
hopefully the debug output will tell me what it is.

RBS






On Wed, Dec 9, 2015 at 6:14 PM, Adam Devita  wrote:

> "This is on a Win7 machine. How do I bring up that console window?"
>
> 1) Click Start
> 2) type cmd
> you will see cmd.exe in the list of programs to run.
> 3) click on cmd.exe
>
> To run MS-excell from the command line, you can call it from the full
> path (check version):
>
> "C:\Program Files\Microsoft Office\Office15\Excel.exe"
>
>
> I agree with David.  If you want to prove your dll is working (or not
> working), you should attempt to isolate it, for direct testing. Many
> hours of many people's lives have been wasted attempting to indirectly
> test things that have more than 1 unknown or potential source of error
>  in a chain.
>
> regards,
> Adam DeVita
>
>
> On Wed, Dec 9, 2015 at 10:12 AM, Bart Smissaert
>  wrote:
> > Will look at this, it is a VB6 console app and that may just do the job:
> > http://vb.mvps.org/samples/Console/
> >
> > RBS
> >
> > On Wed, Dec 9, 2015 at 1:03 PM,  wrote:
> >
> >> On Windows you will get a console and standard output if you are
> running a
> >> console application, and otherwise not.
> >>
> >> I think you need a simple console app to call your ActiveX DLL, or find
> >> some
> >> other way. Windows GUI app and standard output do not play well
> together.
> >>
> >> Regards
> >> David M Bennett FACS
> >>
> >> Andl - A New Database Language - andl.org
> >>
> >>
> >>
> >> On 12/8/15, Bart Smissaert  wrote:
> >> > So, what/where is that standard output channel?
> >> > This is on a Win7 machine. How do I bring up that console window?
> >> >
> >>
> >> The standard output is what displays on your screen when you are in a
> DOS
> >> box.
> >>
> >> SQLite does not have any facilities for debugging in a GUI on Windows.
> >>
> >> --
> >> D. Richard Hipp
> >> drh at sqlite.org
> >> ___
> >> 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
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> --
> VerifEye Technologies Inc.
> 151 Whitehall Dr. Unit 2
> Markham, ON
> L3R 9T1
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Bug with DATETIME('localtime')

[Vitaly Baranov]

Hi,
I've found a bug with using 'localtime' in functions DATETIME(), DATE(), 
TIME().

Platform: Windows 7.
Steps to reproduce:
1. Set your system time zone as "Russia Time Zone 2, (UTC+03:00) Moscow, 
St. Petersburg, Volgograd)".
2. Execute the following script:

SELECT DATETIME(1414267200, 'unixepoch');
SELECT DATETIME(1414267200, 'unixepoch', 'localtime');

Actual results:
2014-10-25 20:00:00
2014-10-25 23:00:00

Expected results:
2014-10-25 20:00:00
2014-10-26 00:00:00

Current local time in Moscow is "UTC+3", however it was "UTC+4" on this 
date.

The following is the result of my own investigation, I hope this will be 
useful. sqlite uses C library function localtime(). However Microsoft's 
implementation of this function sometimes returns incorrect value. I 
sent this information to Microsoft (see 
https://connect.microsoft.com/VisualStudio/feedback/details/1984408 ), 
and they advised to use WinAPI instead of localtime_s(). As far as I can 
see, WinAPI function SystemTimeToTzSpecificLocalTime() works correctly 
for any date.

[sqlite] INSERT OR IGNORE consuming too many rowids

[David Baird]

On Dec 9, 2015 8:06 AM, "Richard Hipp"  wrote:
>
> On 12/9/15, David Baird  wrote:
> >
> > Looks like it's fixed, as long as I stick to new versions. *cross
fingers*
>
> Bisecting shows that the change in behavior occurred here:
> https://www.sqlite.org/src/timeline?c=56bc5ce8
>
> As others have a pointed out, though, the "desired" behavior is
> "undocumented".  SQLite nor any other SQL database engine is obligated
> to provide the behavior you want.  It happens as you like purely by
> chance.  Do not depend on this behavior since it might change at any
> moment, without warning.

I tend to agree with this, that clients shouldn't depend on specific rowids
being generated repeatably/deterministically. But that's not the concern I
wanted to highlight. My cocnern is that: the database engine shouldn't
needlessly waste/discard perectly good chunks of rowids: That is what's
actually undesired. Aside from that, I agree that client shouldn't care
what particular rowids were generated. If it started at 9 and
counted down, I wouldn't care, as long ad it is making good (contiguous)
allocation of the key space.

[sqlite] Obscure peculiarity with 3.9.2

[Richard Hipp]

On 12/9/15, R Smith  wrote:
> One of our systems suddenly started to play up after upgrading to 3.9.2
> from 3.8.8 (so I am not sure exactly when the oddity was introduced).
>
> SQLite:
> v 3.9.2 linked on WIndows (various versions) in a 32 bit application via
> the exact 32bit DLL published on the downloads page on sqlite.org.
>
> The error:
> During an update to an attached DB, the statement fails and reports
> "Disk I/O Error" and leaves a hot journal (even though the statement
> wasn't in an explicit transaction).
>

We can make a script to do this.  And we get this error (when we
enable logging - https://www.sqlite.org/errlog.html - which you really
should do too).

{SQLITE_IOERR_DELETE {os_win.c:39624: (32) winDelete(C:\Users\drh\sqlite\sqlite\
test2.db-journal) - The process cannot access the file because it is being used
by another process.}

The problem is that one connection is trying to delete the journal
file that the other connection is holding open.

The thing is this:  We can reproduce this going all the way back to
version 3.8.0 (which is as far back as I looked.)  So I don't know how
you managed to get it working on your 3.8.8 version.

To reproduce this with a script, to "nmake /f makefile.msc
testfixture.exe" then run "testfixture x7.txt" where you have filled
the file x7.txt with the following text:

file delete -force test.db test.db-journal
proc dolog {args} {
  puts [list $args]
  flush stdout
}
sqlite3_shutdown
test_sqlite3_log dolog
sqlite3 db test.db
puts [db eval {SELECT sqlite_source_id()}]
db eval {
  PRAGMA journal_mode=DELETE;
  CREATE TABLE t1(x);
}
file delete -force test2.db test2.db-journal
sqlite3 db2 test2.db
db2 eval {
  PRAGMA journal_mode=TRUNCATE;
  CREATE TABLE t2(y);
  INSERT INTO t2(y) VALUES(1),(2),(3);
}
db eval {
  ATTACH 'test2.db' AS TmpDB;
  SELECT rowid, y FROM TmpDB.t2;
} x {
  puts [unset -nocomplain x(*); array get x]
}
puts "***"
db2 eval {
  INSERT INTO t2(y) VALUES(3),(4),(5);
}
db eval {
  UPDATE TmpDb.t2 SET y=y+100;
  SELECT rowid, y FROM t2;
} x {unset -nocomplain x(*); puts [array get x]}
puts "***"
db2 eval {
  SELECT rowid, y FROM t2;
} x {unset -nocomplain x(*); puts [array get x]}
db close
db2 close


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] How to see SQLite debugging information

[Bart Smissaert]

Will look at this, it is a VB6 console app and that may just do the job:
http://vb.mvps.org/samples/Console/

RBS

On Wed, Dec 9, 2015 at 1:03 PM,  wrote:

> On Windows you will get a console and standard output if you are running a
> console application, and otherwise not.
>
> I think you need a simple console app to call your ActiveX DLL, or find
> some
> other way. Windows GUI app and standard output do not play well together.
>
> Regards
> David M Bennett FACS
>
> Andl - A New Database Language - andl.org
>
>
>
> On 12/8/15, Bart Smissaert  wrote:
> > So, what/where is that standard output channel?
> > This is on a Win7 machine. How do I bring up that console window?
> >
>
> The standard output is what displays on your screen when you are in a DOS
> box.
>
> SQLite does not have any facilities for debugging in a GUI on Windows.
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> 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
>

[sqlite] Obscure peculiarity with 3.9.2

[J Decker]

Once upon a time, journals would delete when all statements were
closed.  I find now to get all journals to delete you must also close
all connections(opens). Figure at some point someone decided it was
more optimal to leave journals around instead of recreating them on
every statement (which it is).

On Wed, Dec 9, 2015 at 1:05 PM, R Smith  wrote:
>
>
> On 2015/12/09 10:55 PM, Richard Hipp wrote:
>>
>> On 12/9/15, R Smith  wrote:
>>>
>>> One of our systems suddenly started to play up after upgrading to 3.9.2
>>> from 3.8.8 (so I am not sure exactly when the oddity was introduced).
>>>
>>> SQLite:
>>> v 3.9.2 linked on WIndows (various versions) in a 32 bit application via
>>> the exact 32bit DLL published on the downloads page on sqlite.org.
>>>
>>> The error:
>>> During an update to an attached DB, the statement fails and reports
>>> "Disk I/O Error" and leaves a hot journal (even though the statement
>>> wasn't in an explicit transaction).
>>>
>> We can make a script to do this.  And we get this error (when we
>> enable logging - https://www.sqlite.org/errlog.html - which you really
>> should do too).
>>
>> {SQLITE_IOERR_DELETE {os_win.c:39624: (32)
>> winDelete(C:\Users\drh\sqlite\sqlite\
>> test2.db-journal) - The process cannot access the file because it is being
>> used
>> by another process.}
>>
>> The problem is that one connection is trying to delete the journal
>> file that the other connection is holding open.
>>
>> The thing is this:  We can reproduce this going all the way back to
>> version 3.8.0 (which is as far back as I looked.)  So I don't know how
>> you managed to get it working on your 3.8.8 version.
>
>
> This is slightly confusing - I think perhaps other actions we do allowed it
> to slip through (or perhaps complete the deletion before the other file
> access) in our code. But that is just me guessing now.
>
> Also, apologies for the laziness - I would have switched the logger on (we
> do have that of course) but I simply stepped through the code, saw the
> return value, went back to the connection origins, saw the different modes,
> corrected it and then things worked, so I did not do a full analysis, but
> decided it's probably worth reporting it.
>
> I'll do better next time before reporting :)
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to see SQLite debugging information

[David Bennett]

On Windows you will get a console and standard output if you are running a
console application, and otherwise not.

I think you need a simple console app to call your ActiveX DLL, or find some
other way. Windows GUI app and standard output do not play well together.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org



On 12/8/15, Bart Smissaert  wrote:
> So, what/where is that standard output channel?
> This is on a Win7 machine. How do I bring up that console window?
>

The standard output is what displays on your screen when you are in a DOS
box.

SQLite does not have any facilities for debugging in a GUI on Windows.

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

[sqlite] JSON1 detecting if a path exists vs. json null value

[Richard Hipp]

On 12/9/15, jeremy at copiousfreetime.org  wrote:
> So far it looks as if the way to distinguish between a json key existing
> with a
> value of null vs. not existing is to use `json_type`
>
>   sqlite> select json_type('{"a":2,"c":[4,5,{"f":7}]}', '$.x') IS NULL;
>   1
>   sqlite> select json_type('{"x": null, "a":2,"c":[4,5,{"f":7}]}', '$.x');
>   null
>
> Is this correct?

Yes.

>
> Also are there optimizations in place so that a column that is a json string
> is only
> parsed once if it is involved in json1 functions? For example:
>

No.  The JSON parsing turned out to be so fast that such optimizations
didn't seem worth the effort.  Of course, things might change in the
future.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] INSERT OR IGNORE consuming too many rowids

[R Smith]

On 2015/12/09 2:21 PM, David Baird wrote:
> Looks like it's fixed, as long as I stick to new versions. *cross 
> fingers* Thanks!

Just to add to my previous reply: It wasn't "fixed", since it was never 
before "wrong", it was merely changed in a way that happens to currently 
suit your needs, and it might change back or otherwise be altered at 
some point in a new version - please don't use it if you depend on the 
resulting table order.

In stead, you can do something like (SELECT MAX(ID)+1 FROM StringIntern) 
to get the real next-in-line value for the column.

[sqlite] INSERT OR IGNORE consuming too many rowids

[R Smith]

On 2015/12/09 2:21 PM, David Baird wrote:
> On Wed, Dec 9, 2015 at 7:14 AM, Clemens Ladisch  wrote:
>
> Just tried two different versions:
>
> SQLite version 3.8.2 2013-12-06 14:53:30 (that is bundled with my OS): has
> the undesired behavior
>
> SQLite version 3.9.2 2015-11-02 18:31:45 (that I built myself): has desired
> behavior
>
> Looks like it's fixed, as long as I stick to new versions. *cross fingers*
> Thanks!

There is no reason on Earth to trust the auto-incrementing to produce an 
exact sequence, and no reason to need it. You may well assign the PK 
yourself and not depend on autoincrement  - which is what I would very 
much advise if you are depending on the specific values or order of 
assigned values ending up in that column.

Autoincrement is just a side-facility to allow lazy programming (not 
meant in a bad way, I use it too), but it only promises uniqueness, not 
order - and you should not expect/depend on anything that is not 
explicitly promised. You can at any time insert a value with a negative 
ID, for instance, and the next auto ID will likely not be the one 
directly following that.

Also - your version of the table is not necessarily reflecting the 
actual rowid alias though it might work like that (but I don't think it 
is documented like that - it is only promised when you have the specific 
phrase: "Col INTEGER PRIMARY KEY" with the optional autoincrement 
specifier).

Cheers,
Ryan

[sqlite] JSON1 detecting if a path exists vs. json null value

[jer...@copiousfreetime.org]

On December 9, 2015 at 13:05:37 PM, Richard Hipp (drh at sqlite.org) wrote:
>?
> Also are there optimizations in place so that a column that is a json string?
> is only?
> parsed once if it is involved in json1 functions? For example:?
>?

No. The JSON parsing turned out to be so fast that such optimizations?
didn't seem worth the effort. Of course, things might change in the?
future.?
Thanks, appreciated.

-jeremy

[sqlite] INSERT OR IGNORE consuming too many rowids

[Clemens Ladisch]

David Baird wrote:
> # INSERT INTO "StringIntern" VALUES(5,'c'); <-- undesired behavior
> # INSERT INTO "StringIntern" VALUES(3,'c'); <-- desired behavior

Works for me.  Are you sure you actually executed this actual code?

 guarantees that a newly inserted
row uses the next available rowid value, if you have an INTEGER PRIMARY
KEY column.  You might get the undesired behaviour only with an INTEGER
PRIMARY KEY AUTOINCREMENT column.


Regards,
Clemens

[sqlite] How to see SQLite debugging information

[Adam Devita]

"This is on a Win7 machine. How do I bring up that console window?"

1) Click Start
2) type cmd
you will see cmd.exe in the list of programs to run.
3) click on cmd.exe

To run MS-excell from the command line, you can call it from the full
path (check version):

"C:\Program Files\Microsoft Office\Office15\Excel.exe"


I agree with David.  If you want to prove your dll is working (or not
working), you should attempt to isolate it, for direct testing. Many
hours of many people's lives have been wasted attempting to indirectly
test things that have more than 1 unknown or potential source of error
 in a chain.

regards,
Adam DeVita


On Wed, Dec 9, 2015 at 10:12 AM, Bart Smissaert
 wrote:
> Will look at this, it is a VB6 console app and that may just do the job:
> http://vb.mvps.org/samples/Console/
>
> RBS
>
> On Wed, Dec 9, 2015 at 1:03 PM,  wrote:
>
>> On Windows you will get a console and standard output if you are running a
>> console application, and otherwise not.
>>
>> I think you need a simple console app to call your ActiveX DLL, or find
>> some
>> other way. Windows GUI app and standard output do not play well together.
>>
>> Regards
>> David M Bennett FACS
>>
>> Andl - A New Database Language - andl.org
>>
>>
>>
>> On 12/8/15, Bart Smissaert  wrote:
>> > So, what/where is that standard output channel?
>> > This is on a Win7 machine. How do I bring up that console window?
>> >
>>
>> The standard output is what displays on your screen when you are in a DOS
>> box.
>>
>> SQLite does not have any facilities for debugging in a GUI on Windows.
>>
>> --
>> D. Richard Hipp
>> drh at sqlite.org
>> ___
>> 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
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
--
VerifEye Technologies Inc.
151 Whitehall Dr. Unit 2
Markham, ON
L3R 9T1

[sqlite] How to see SQLite debugging information

[Dominique Devienne]

On Wed, Dec 9, 2015 at 12:59 PM, Graham Holden 
wrote:

> If you have, or create, a VB6 standalone EXE that calls SQLite, you should
> be possible to get it to show stdout/stderr.
>

OP already explained VB code is wrapped in an ActiveX dynamically
used/loaded by MS Excel.

So no user-controlled EXE, and chances that EDITBIN /SUBSYSTEM:CONSOLE
works on the host EXE (Excel) are uncertain at best.

So in the case of a 3rd party host EXE, beside SQLite itself allowing to
redirect it's output to a file, via a pragma (ideally) or an new API or
config in init() [1], I don't see any solution. --DD

[1] https://www.sqlite.org/c3ref/initialize.html

[sqlite] JSON1 detecting if a path exists vs. json null value

[jer...@copiousfreetime.org]

Hi,

I?m experimenting with the json1 extension, and I?d like to confirm the proper
way to detect if a key exists in the json, vs its value being null. For example:

? sqlite> select json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x');

? sqlite> select json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.x') IS NULL;
? 1
? sqlite> select json_extract('{"x":null, "a":2,"c":[4,5,{"f":7}]}', '$.x');

? sqlite> select json_extract('{"x":null, "a":2,"c":[4,5,{"f":7}]}', '$.x') IS 
NULL;
? 1

So far it looks as if the way to distinguish between a json key existing with a
value of null vs. not existing is to use `json_type`

? sqlite> select json_type('{"a":2,"c":[4,5,{"f":7}]}', '$.x') IS NULL;
? 1
? sqlite> select json_type('{"x": null, "a":2,"c":[4,5,{"f":7}]}', '$.x');
? null

Is this correct?

Also are there optimizations in place so that a column that is a json string is 
only
parsed once if it is involved in json1 functions? For example:

? sqlite> create table t1(doc);
? sqlite> insert into t1(doc) values(json('{"x": null, 
"a":2,"c":[4,5,{"f":7}]}'));
? sqlite> select json_type(doc,'$.a') IS NOT NULL, json_extract(doc, '$.a') 
from t1;
? 1|2

In this case, is doc parsed twice? I'm not actually worried about
performance, just wondering.

enjoy,

-jeremy

--?

Jeremy Hinegarnder
Copious Free Time, LLC

[sqlite] How to see SQLite debugging information

[Bart Smissaert]

If all else fails it would be no problem to write a little VB6 exe that
calls the ActiveX dll.
Not much code should be needed in that to reproduce that crash situation.
Would something like the free DOSBox do the job? Will try later.

RBS



On Wed, Dec 9, 2015 at 12:12 PM, Dominique Devienne 
wrote:

> On Wed, Dec 9, 2015 at 12:59 PM, Graham Holden 
> wrote:
>
> > If you have, or create, a VB6 standalone EXE that calls SQLite, you
> should
> > be possible to get it to show stdout/stderr.
> >
>
> OP already explained VB code is wrapped in an ActiveX dynamically
> used/loaded by MS Excel.
>
> So no user-controlled EXE, and chances that EDITBIN /SUBSYSTEM:CONSOLE
> works on the host EXE (Excel) are uncertain at best.
>
> So in the case of a 3rd party host EXE, beside SQLite itself allowing to
> redirect it's output to a file, via a pragma (ideally) or an new API or
> config in init() [1], I don't see any solution. --DD
>
> [1] https://www.sqlite.org/c3ref/initialize.html
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] How to see SQLite debugging information

[Graham Holden]

If you have, or create, a VB6 standalone EXE that calls SQLite, you should be 
possible to get it to show stdout/stderr.

By default, such EXEs are marked as "GUI" programs: if launched from a command 
prompt, the prompt returns immediately and they don't have a "console".

However, you can change the way an EXE is launched. ?It might be possible to 
choose the mode when you first create the project (you can in later versions of 
DevStudio; I can't remember if VB6 has this option). If you can't choose (or 
you have an existing project) you can use a Microsoft utility called EDITBIN:

EDITBIN /SUBSYSTEM:CONSOLE MYVB6.EXE

should switch the mode. ?If launched from a command prompt, the prompt 
shouldn't return until the program is closed, and any stdour/stderr should 
(with a bit of luck) appear in the command-prompt window (or can be redirected 
to a file). ?I think EDITBIN comes with the Visual C component of DevStudio: 
you may need to install that to get it, if it's not available as a download 
from MSDN.

Graham.

Sent from Samsung Mobile

 Original message 
From: Bart Smissaert  
Date: 09/12/2015  08:32  (GMT+00:00) 
To: SQLite mailing list  
Subject: Re: [sqlite] How to see SQLite debugging information 

> Are you using Excel or VB6?? They're very different things.
I use both. Have done for many years and by now I did indeed figure out
they are not the same :)

> freopen etc.
Thanks, will give that a try.

> can step through the sqlite code in a debug build
That would be great, but not sure how that works. Will ask somebody who
knows these things.
I do in fact have MS VS 2013, but no idea how to step through the code in
debug mode.

RBS

On Wed, Dec 9, 2015 at 1:44 AM, Random Coder  wrote:

> On Tue, Dec 8, 2015 at 4:30 PM, Bart Smissaert 
> wrote:
> > So, what/where is that standard output channel?
> > This is on a Win7 machine. How do I bring up that console window?
> > There is no development environment here. I am running this from Excel.
> > So, I have a standard Windows sqlite3.dll, a std_call dll (to make SQLite
> > accessible to VB6)
>
> Are you using Excel or VB6?? They're very different things.
>
> You could try adding something like the following to somewhere near
> the beginning of sqlite3_initialize
>
> freopen("sqlite_stdout.txt","a",stdout);
> freopen("sqlite_stderr.txt","a",stderr);
>
> This will create two text files for all of sqlite's output.? There
> might be side effects to doing this .. honestly I have no idea if
> it'll work, and finding where the files are created might be
> interesting (they'll be the current directory, but I have no idea what
> that is when you're running Excel, or VB6)
>
> Really, this problem is best solved with a debugger.? Even windbg is
> better than flying blind, and you can step through the sqlite code in
> a debug build even if it's being loaded by something like Excel where
> you don't have the source code.
> ___
> 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

[sqlite] How to see SQLite debugging information

[Random Coder]

On Wed, Dec 9, 2015 at 10:14 AM, Adam Devita  wrote:
> To run MS-excell from the command line, you can call it from the full
> path (check version):
>
> "C:\Program Files\Microsoft Office\Office15\Excel.exe"

You can also run "start excel" to launch the current version of Excel.
Both of these are nearly useless, though.  Because the Excel
executable is marked as a GUI application, it won't attach to the
console window.

> I agree with David.  If you want to prove your dll is working (or not
> working), you should attempt to isolate it, for direct testing. Many
> hours of many people's lives have been wasted attempting to indirectly
> test things that have more than 1 unknown or potential source of error
>  in a chain.

Agreed.

[sqlite] How to see SQLite debugging information

[Dominique Devienne]

On Wed, Dec 9, 2015 at 3:50 AM, Richard Hipp  wrote:

> On 12/8/15, Bart Smissaert  wrote:
> > So, what/where is that standard output channel?
> SQLite does not have any facilities for debugging in a GUI on Windows.


Any chance to add a way to redirect debugging / troubleshooting output to a
file instead?

Perhaps via a new pragma debug_output = filename ? Would useful in
embedding cases where one does not control the host program. --DD

[sqlite] How to see SQLite debugging information

[Bart Smissaert]

That would be very useful and I take it wouldn't be difficult to add.

RBS



On Wed, Dec 9, 2015 at 7:53 AM, Dominique Devienne 
wrote:

> On Wed, Dec 9, 2015 at 3:50 AM, Richard Hipp  wrote:
>
> > On 12/8/15, Bart Smissaert  wrote:
> > > So, what/where is that standard output channel?
> > SQLite does not have any facilities for debugging in a GUI on Windows.
>
>
> Any chance to add a way to redirect debugging / troubleshooting output to a
> file instead?
>
> Perhaps via a new pragma debug_output = filename ? Would useful in
> embedding cases where one does not control the host program. --DD
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] How to see SQLite debugging information

[Bart Smissaert]

> The standard output is what displays on your screen when you are in a DOS
box.

OK, thanks, will look into that.

RBS

On Wed, Dec 9, 2015 at 2:50 AM, Richard Hipp  wrote:

> On 12/8/15, Bart Smissaert  wrote:
> > So, what/where is that standard output channel?
> > This is on a Win7 machine. How do I bring up that console window?
> >
>
> The standard output is what displays on your screen when you are in a DOS
> box.
>
> SQLite does not have any facilities for debugging in a GUI on Windows.
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] How to see SQLite debugging information

[Bart Smissaert]

> Are you using Excel or VB6?  They're very different things.
I use both. Have done for many years and by now I did indeed figure out
they are not the same :)

> freopen etc.
Thanks, will give that a try.

> can step through the sqlite code in a debug build
That would be great, but not sure how that works. Will ask somebody who
knows these things.
I do in fact have MS VS 2013, but no idea how to step through the code in
debug mode.

RBS

On Wed, Dec 9, 2015 at 1:44 AM, Random Coder  wrote:

> On Tue, Dec 8, 2015 at 4:30 PM, Bart Smissaert 
> wrote:
> > So, what/where is that standard output channel?
> > This is on a Win7 machine. How do I bring up that console window?
> > There is no development environment here. I am running this from Excel.
> > So, I have a standard Windows sqlite3.dll, a std_call dll (to make SQLite
> > accessible to VB6)
>
> Are you using Excel or VB6?  They're very different things.
>
> You could try adding something like the following to somewhere near
> the beginning of sqlite3_initialize
>
> freopen("sqlite_stdout.txt","a",stdout);
> freopen("sqlite_stderr.txt","a",stderr);
>
> This will create two text files for all of sqlite's output.  There
> might be side effects to doing this .. honestly I have no idea if
> it'll work, and finding where the files are created might be
> interesting (they'll be the current directory, but I have no idea what
> that is when you're running Excel, or VB6)
>
> Really, this problem is best solved with a debugger.  Even windbg is
> better than flying blind, and you can step through the sqlite code in
> a debug build even if it's being loaded by something like Excel where
> you don't have the source code.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] INSERT OR IGNORE consuming too many rowids

[Richard Hipp]

On 12/9/15, David Baird  wrote:
>
> Looks like it's fixed, as long as I stick to new versions. *cross fingers*

Bisecting shows that the change in behavior occurred here:
https://www.sqlite.org/src/timeline?c=56bc5ce8

As others have a pointed out, though, the "desired" behavior is
"undocumented".  SQLite nor any other SQL database engine is obligated
to provide the behavior you want.  It happens as you like purely by
chance.  Do not depend on this behavior since it might change at any
moment, without warning.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] INSERT OR IGNORE consuming too many rowids

[David Baird]

On Wed, Dec 9, 2015 at 7:14 AM, Clemens Ladisch  wrote:

> David Baird wrote:
> > # INSERT INTO "StringIntern" VALUES(5,'c'); <-- undesired behavior
> > # INSERT INTO "StringIntern" VALUES(3,'c'); <-- desired behavior
>
> Works for me.  Are you sure you actually executed this actual code?
>
>  guarantees that a newly inserted
> row uses the next available rowid value, if you have an INTEGER PRIMARY
> KEY column.  You might get the undesired behaviour only with an INTEGER
> PRIMARY KEY AUTOINCREMENT column.
>

Just tried two different versions:

SQLite version 3.8.2 2013-12-06 14:53:30 (that is bundled with my OS): has
the undesired behavior

SQLite version 3.9.2 2015-11-02 18:31:45 (that I built myself): has desired
behavior

Looks like it's fixed, as long as I stick to new versions. *cross fingers*
Thanks!

[sqlite] INSERT OR IGNORE consuming too many rowids

[David Baird]

Hi,

I have a use case in which a table is being used to associate integers to
unique strings. When loading the strings, it's possible there may be some
redundancies, which INSERT OR IGNORE successfully ignores. However,
redundant values that should have been ignored _sometimes_ have a side
effect that is undesired: they consume extra rowids. Here are a couple
examples:


-- Undesired behavior, side effect on rowid:
CREATE TABLE StringIntern (
id INTEGER,
string TEXT,
PRIMARY KEY (id)
);
CREATE UNIQUE INDEX StringIntern_string ON StringIntern(string);
INSERT OR IGNORE INTO StringIntern(string) VALUES ('a'), ('b');
INSERT OR IGNORE INTO StringIntern(string) VALUES ('a'), ('b'), ('c'); --
'a' and 'b' have a side effect on the rowid
.dump
# >>>
# INSERT INTO "StringIntern" VALUES(1,'a');
# INSERT INTO "StringIntern" VALUES(2,'b');
# INSERT INTO "StringIntern" VALUES(5,'c'); <-- undesired behavior


-- Desired behavior:
CREATE TABLE StringIntern (
id INTEGER,
string TEXT,
PRIMARY KEY (id)
);
CREATE UNIQUE INDEX StringIntern_string ON StringIntern(string);
INSERT OR IGNORE INTO StringIntern(string) VALUES ('a'), ('b');
INSERT OR IGNORE INTO StringIntern(string) VALUES ('a'), ('b'); -- 'a' and
'b' are ignored (all good here!)
INSERT OR IGNORE INTO StringIntern(string) VALUES ('c');
.dump
# INSERT INTO "StringIntern" VALUES(1,'a');
# INSERT INTO "StringIntern" VALUES(2,'b');
# INSERT INTO "StringIntern" VALUES(3,'c'); <-- desired behavior


Is what I call undesired behavior actually intentional by sqlite3? Would it
be possible to change it to the desired behavior?

Thanks,
David

[sqlite] How to see SQLite debugging information

[Simon Slavin]

On 9 Dec 2015, at 12:45am, Bart Smissaert  wrote:

> Surely there must be simple way to dump debugging data to say a text file.

Sure.  Run Excel as a command rather than launching it in the Windows GUI.

Simon.

[sqlite] How to see SQLite debugging information

[Simon Slavin]

On 9 Dec 2015, at 12:45am, Bart Smissaert  wrote:

> Excel doesn't do much, all the real work is done by that ActiveX dll and I
> have full access to that as I coded that.
> Surely there must be simple way to dump debugging data to say a text file.

Sure.  Run your own program, not one someone else wrote like Excel.

Simon.

[sqlite] How to see SQLite debugging information

[Bart Smissaert]

Excel doesn't do much, all the real work is done by that ActiveX dll and I
have full access to that as I coded that.
Surely there must be simple way to dump debugging data to say a text file.

RBS



On Wed, Dec 9, 2015 at 12:37 AM, Simon Slavin  wrote:

>
> On 9 Dec 2015, at 12:35am, Bart Smissaert 
> wrote:
>
> > So, do I need to somehow re-direct this output to a text file?
>
> I don't think it can be done.  If the actual program you're running is
> Excel then you're relying on Excel to do this stuff.  And since Excel is a
> spreadsheet program and not a development environment there's no reason why
> it should show you stdout.
>
> Can you run Excel from a command-line environment ?  Like a program called
> Terminal or cli.exe or something ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] How to see SQLite debugging information

[Simon Slavin]

On 9 Dec 2015, at 12:35am, Bart Smissaert  wrote:

> So, do I need to somehow re-direct this output to a text file?

I don't think it can be done.  If the actual program you're running is Excel 
then you're relying on Excel to do this stuff.  And since Excel is a 
spreadsheet program and not a development environment there's no reason why it 
should show you stdout.

Can you run Excel from a command-line environment ?  Like a program called 
Terminal or cli.exe or something ?

Simon.

[sqlite] How to see SQLite debugging information

[Bart Smissaert]

So, do I need to somehow re-direct this output to a text file?

RBS



On Wed, Dec 9, 2015 at 12:32 AM, Simon Slavin  wrote:

>
> On 9 Dec 2015, at 12:30am, Bart Smissaert 
> wrote:
>
> > So, what/where is that standard output channel?
> > This is on a Win7 machine. How do I bring up that console window?
> > There is no development environment here. I am running this from Excel.
>
> Then Excel is getting the output.  And since Excel is not a development
> environment I suppose there's no way for you to see the standard output.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] How to see SQLite debugging information

[Simon Slavin]

On 9 Dec 2015, at 12:30am, Bart Smissaert  wrote:

> So, what/where is that standard output channel?
> This is on a Win7 machine. How do I bring up that console window?
> There is no development environment here. I am running this from Excel.

Then Excel is getting the output.  And since Excel is not a development 
environment I suppose there's no way for you to see the standard output.

Simon.

[sqlite] How to see SQLite debugging information

[Bart Smissaert]

So, what/where is that standard output channel?
This is on a Win7 machine. How do I bring up that console window?
There is no development environment here. I am running this from Excel.
So, I have a standard Windows sqlite3.dll, a std_call dll (to make SQLite
accessible to VB6)
a .tlb file an ActiveX dll that is then called from Excel VBA.

RBS

On Wed, Dec 9, 2015 at 12:16 AM, Simon Slavin  wrote:

>
> On 8 Dec 2015, at 10:18pm, Bart Smissaert 
> wrote:
>
> >> on standard output
> > But how this work in concrete steps? Does code have to be added to SQLite
> > to write for example to a text file?
>
> SQLite sends text to the standard output channel while your program is
> making SQLite calls.
>
> You should be able to see this text in a console window.  You bring this
> up various ways depending on your development environment but one example
> is ...
>
> 
>
> If instead you run your program by typing its name into a terminal command
> like the output will appear in your terminal window.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] How to see SQLite debugging information

[Simon Slavin]

On 8 Dec 2015, at 10:18pm, Bart Smissaert  wrote:

>> on standard output
> But how this work in concrete steps? Does code have to be added to SQLite
> to write for example to a text file?

SQLite sends text to the standard output channel while your program is making 
SQLite calls.

You should be able to see this text in a console window.  You bring this up 
various ways depending on your development environment but one example is ...



If instead you run your program by typing its name into a terminal command like 
the output will appear in your terminal window.

Simon.