[sqlite] Feature request: import MySQL dumps in CLI

[Larry Brasfield]

Mr. Kurz writes:
> I suppose I am not the only one having to convert between
> MySQL/MariaDB and SQLite databases every now and then. I
> know there are converters for MySQL dumps but none of any
> I have ever tried did work nearly reliable.

If you dislike the available converters and do not wish to
bother writing or adapting one to your tastes, you could use
any of several ETL tools, such as Talend or Pentahoe Kettle.
These tools are good at extracting data and loading it into
a(nother) database. You might have to handle getting your
schema translated, but that will mainly involve minor editing
of the converter outputs you have already obtained.

Mr. Kurz wrote further:
> So my suggestion would be to add an import feature to the
> CLI that allows to directly import MySQL/MariaDB dumps into
> an SQLite database keeping as many information as possible.
> As SQLite already has a complete SQL parser I expect much
> better results than with existing converters.

I highly doubt the SQLite team will undertake this task. They
Surely have the skill to do so, but their priority is the one
software product you desire to use, undoubtedly due to its
high utility.  I doubt that utility would exist if they were
to wander off tacking the conversion challenge for the other
popular database systems.

Best regards,
- 
Larry Brasfield

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

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

[Larry Brasfield]

Keith writes:

I do not personally see the benefit of moving the repeatable read guarantee to 
the BEGIN point rather than the first database read after the BEGIN because 
only fully committed transactions will be visible anyway -- …

Improved modularity would be facilitated by making it possible to actually 
begin a transaction immediately rather than having it be deferred until the 
associated DB access occurs.  Granted, when the sequence of events (BEGIN …, 
SELECT …, … TRANSACTION)  is viewed as a whole, deferring the guarantee makes 
only a slight difference to the work that must (or should) be done by the 
application.  But the deferral changes where checking must be done for actually 
obtaining the guarantee.  Presently, that checking has to be done in the code 
which makes the queries (or updates, inserts, etc.)  And that checking, which 
is needed on the first access statement only, is probably done with different 
requirements for handling the contention error than pertain to following 
statements.

Pseudo-code examples may clarify my point.

Now:
  Begin transaction;
  if ( failed( do first access ) ) {
Handle contention error or more unusual errors;
Rollback transaction;
  }
  else {
if ( failed ( do subsequent accesses ) ) {
  Handle the rare and arcane access errors;
  Rollback transaction;
}
else {
  Commit transaction;
}
  }

With guarantee moved up to BEGIN:
  If ( failed( begin transaction ) ) {
Handle contention error;
  }
  else {
if ( failed ( do must-be-grouped accesses ) ) {
  Handle the rare and arcane access errors;
  Rollback transaction;
}
else {
  Commit transaction;
}
  }

When using C++, C# or another language with object lifetime guarantees, I might 
wrap the contention checking, waiting, etc., into a Transaction class which 
bundled the transaction mechanics with (some of) the error handling a 
contention failure entails.  The client code would read something like:
  using (var t = new Transaction(dbConnection) ) {
if ( failed ( do must-be-grouped accesses ) ) {
  Handle access errors
  // t does the rollback when it goes out of scope.
}
else {
  t.Commit();
}
  }
This grouping of handling for different error categories is made much less 
convenient by the present deferral of acquiring the necessary lock(s).

Best regards,
-
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] using System.Data.SQLite on Linux / ".Net Core 2"

[Larry Brasfield]

Is there a build of System.Data.SQLite which runs on .Net Core 2.0 when
used on a Linux platform?

Using Powershell 6 installed on my Ubuntu 16 LTS box, I can do
Add-Type -Path ./System.Data.SQLite.dll
(which loads the assembly) without issues, (on Linux or Windows), but this
$c = New-Object System.Data.SQLite.SQLiteConnection -ArgumentList "Data
Source=$home/silly.sdb"
fails with error information reading:
New-Object : Exception calling ".ctor" with "1" argument(s): "Unable to
load shared library 'SQLite.Interop.dll' or one of its dependencies. In
order to help diagnose loading problems, consider setting the LD_DEBUG
environment variable: libSQLite.Interop.dll: cannot open shared object
file: No such file or directory"
At line:1 char:6
+ $c = New-Object System.Data.SQLite.SQLiteConnection -ArgumentList "Da ...
+  
+ CategoryInfo  : InvalidOperation: (:) [New-Object],
MethodInvocationException
+ FullyQualifiedErrorId :
ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

I imagine that I need the interop DLL from the Mono build mixed with the
System.Data.SQLite assembly which targets .Net Core 2.0, but it is unclear
(to me) how to set this up.

Any insights are most welcome.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How lock is implemented upon write?

[Larry Brasfield]

Peng Yu wrote:
>> So the fundamental C API that unix_os.c is based on is
>> the only following but nothing else?
>>  [ followed by links to *nix C API fcntl(…) docs ]

The (C and fundamental) API you cite is the interface provided for applications 
running on Unix-like operating systems to lock files in various ways.  
Applications can use that API if they can pass the right parameters to the 
OS-provided entry point using C calling convention.  This is but tangentially 
related to “how lock is implemented upon write”, presumably in SQLite, (the 
subject of this thread, I guess.)  I say “tangentially” because there is far 
more to SQLite’s database locking than managing an OS-provided file lock. I can 
see this at a glance by looking at fcnt() calls (or their macro stand-ins) in 
sqlite3.c .

You appear to want to understand SQLite implements locking, apparently without 
reading the (C) code which effects that behavior.  I am not surprised than 
nobody wants to recast all that logic for you into a language you are willing 
and/or able to read.  It is complex, and changes among the platforms targeted 
by SQLite.  And it hardly matters to most SQLite users *how* it is implemented; 
their concern is how it works, which is well documented (as others have 
mentioned), and that it works well and reliably.

I notice that you have ignored repeated requests for insight into why you have 
made your inquiry.  People who may be able to help you with your objective ask 
for such information because, often, that leads to or permits a more direct 
solution to your actual problem.  Certainly in this case, where the Pascal 
translation of many lines of C would take hours to generate, a more direct 
solution is probably going to be the only one likely to be offered.

Cheers,
-
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Windows dll - Export Symbol

[Larry Brasfield]

Keith Medcalf, (quoting Llyod), writes:

>> >How can I build the dll correctly?

> cl -DSQLITE_API=__declspec(dllexport) sqlite3.c -link -dll -out:sqlite3.dll



I do not claim here that “correctly” building the DLL must be done as
either I or Keith has suggested.  However, the method using Makefile.msc
accomplishes a few things that the do-it-all-almost CL invocation does
not.  For example, it compiles sqlite3.rc as a resource source and causes
information such as the file version, source id, copyright, etc. to be
linked into the DLL as a resource.  (This can be seen in Windows file
explorer by right-clicking on the DLL, clicking the “Properties” button,
then clicking the “Details” tab.)



With either method, it is possible to set a great many compile options.
The makefile has provisions for setting them in a consistent way.  And as
‘make’ aficionados know, it is a very convenient place to keep track of and
reuse how you want to build something.



That said, standalone CL invocation is great for just getting something
made quickly when bothering with more files is just excess bother.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Windows dll - Export Symbol

[Larry Brasfield]

Lloyd writes:
I wish to build SQLite as a dll in Windows. As per the documentation here
https://www.sqlite.org/howtocompile.html, I have executed the command
cl sqlite3.c -link -dll -out:sqlite3.dll
on Visual Studio x86 command prompt.

It seems that the dll built doesn't export any symbols! I checked it using
the dumpbin utility. But the dll downloaded from www.sqlite.org exports
symbols.

How can I build the dll correctly?

The project publishes a file, Makefile.msc, which is written in the language 
understood by NMAKE.exe, (which ships with Visual Studio.)  It defines a 
target, ‘dll’ (sans quotes), recipe for which builds the DLL you want.  You 
could either study that file to see the relevant flags for CL.exe (and LINK.exe 
and a couple other tools), or you could invoke NMAKE in a command-line shell 
thusly:
nmake -f Makefile.msc dll
For the latter, you would first run the VS batch file that sets the environment 
so those tools can find necessary files.

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

Re: [sqlite] Diagnosing stale readers

[Larry Brasfield]

Gerlando Falauto writes:
thanks for you reply.
Indeed, readers are long-lived processes on a Linux system.
They're threads of a Pyhon Flask application to export data, which
uses uses peewee as a wrapper for SQLite.
Theoretically, once a request is served, the transaction should be
terminated. However, I suspect there is some issue there (it's a
pretty complex pipeline to build files to be served on the fly), and
for some reason cleanup is not performed correctly. For one thing, I
did not program it  explicitly.
I can of course investigate further, but I was wondering if there was
some way of ascertaining whether my suspicion is correct (and later,
whether I've fixed the bug or not). In other words, if there's a way
of getting a list of the current transactions or locks.
I believe this information is stored somewhere in the WAL-index file
(.db-shm), because that's the kind of information SQLite needs to
decide it can't run a checkpoint (which is believe is what happening).

If would also like to know if killing (possibly with -9) a process
with an open handle might accidentally leave the DB locked.

You’re welcome.

SQLite relies upon process synchronization objects, provided by the operating 
system, to coordinate access to the database on disk (or whatever the 
persistent storage medium is).  As Keith mentioned, the processes need not be 
identified in the stored data to do this. Doing so would be problematic for 
several reasons, among which is that such ephemeral facts do not belong in the 
persistent data which should be meaningful across power cycles, machines, and 
even platforms.

Your worry about the locking objects being orphaned by ‘kill -9 …’ or other 
sudden process death is understandable but misplaced. One very well settled 
convention in operating system design is that such orphaning should not happen, 
and if it does, that is a serious bug.  This is part of the reason that the 
operating system manages such objects.  It tracks which processes are using 
various resources subject to being used among multiple processes, and releases 
associated process-to-object links (typically ‘handles’) when a process exits 
for any reason.  I highly doubt any serious operating system provider would 
release an OS which failed to do this reliably.

The problem is (most likely) in the code you suspect, (that “pretty complex 
pipeline”), or some not-quite-intelligent caching associated with it.  Whoever 
debugs that would benefit from using an equivalent of the “Handle” utility I 
suggested.  (That is a way of getting a list of currently existing process 
synchronization objects, even ones used as locks.) It is easy for an 
application to orphan its own references to operating system resources, a sin 
which only is absolved when it dies or exits.  I suspect that when the error is 
found, there will be some improperly handled error return from SQLite involved, 
where insufficient cleanup is done and a SQLite connection is effectively 
orphaned.  SQLite itself is very good about not orphaning the objects it holds, 
but it can only do this when its “connection” object (representing by a 
pointer) is closed.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Diagnosing stale readers

[Larry Brasfield]

Gerlando Falauto writes:

I'm using SQLite in WAL mode in the following scenario:

- One writer which continuously writes new rows and deletes older ones

- Multiple readers performing queries



Writer and readers are running each in a separate process.

The (Inactive) Journal Size limit is set to 100MB by pragmas and in

normal usage this request seems to be honoured (i.e. the file does not

grow bigger than that).

I've however run into a situation where the .db-wal grows up to

several Gigabytes, and the main .db file stays small.

I believe this can be explained by some stale transaction in the

system preventing the WAL pages from getting discarded (after being

copied to the main database file).



Any suggestion on how I can diagnose this situation, i.e. identify the

offending process?



If the various readers are short-lived processes (relative to the writer),
you could treat this similarly to a memory leak.  The diagnostic method
used for those [a] can be adapted where you would wrap the transaction
begin and end operations with registration/deregistration and see whether
registrations exceed deregistrations upon program exit.



[a. Generally, it is to record, in some kind of set or associative
container, each allocated memory pointer upon the allocation, and upon
corresponding frees, to remove the pointer from the set.  Once that is
setup, leaks are diagnosed by examining the set as main() exits (or
whatever corresponds to main() in non-C derived languages.)  If the set is
not empty then, there is a leak. ]



If the reader processes are long-lived and running on the Windows OS,
another approach is viable. There is a handy set of system utilities which
allow developers to look at (Windows) OS system objects (
https://docs.microsoft.com/en-us/sysinternals/downloads/winobj ) and see
what handles are held by a process to such objects (
https://docs.microsoft.com/en-us/sysinternals/downloads/handle ).  Since
SQLite uses various system objects (the DB file and associated handles, and
mutexes and associated handles) to coordinate DB access among processes,
these utilities would be helpful for figuring out which process is failing
to relinquish its claim to DB access.  You could still use a registration
system to narrow the problem further by attaching a debugger to the
identified laggard process and examining its pending transaction registry.
(Often, resource registration associates the allocation code file and line
number with the handle or pointer representing resource.)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] where did my data go ? Re: Import data into a temporary table

[Larry Brasfield]

Luuk writes:
| sqlite> .import abc.csv temp.test
| Error: no such table: temp.test
|. . .
| Where did my data go (see above)?

The above command to the shell results in an attempted prepare of SQL like this,
  INSERT INTO “temp.text” VALUES …
which fails with a “no such table” complaint.  If shell.c had either not 
double-quoted the schema.tableName, or double-quoted only the tableName part, 
it would have succeeded.  It is easy to modify shell.c to eliminate the 
double-quoting if you really want to .import directly into a temp. table and 
can refrain from using goofy table names (such as would need double-quoting.)

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

Re: [sqlite] Sample Employee database ported to SQLite from MySQL

[Larry Brasfield]

Jungle Boogie wrote:
➢ Anyone else have issues decompressing the file? $ bzip2 -d employees.db.bz2 
bzip2: employees.db.bz2 is not a bzip2 file. bunzip2 employees.db.bz2 bunzip2: 
employees.db.bz2 is not a bzip2 file. $ file employees.db.bz2 employees.db.bz2: 
Non-ISO extended-ASCII HTML document text $ sha256 employees.db.bz2 SHA256 
(employees.db.bz2) = 
2c24eaa81d65459ec412e1e4e7a0955349f40ccff02abe98b72b0af5e84495f2

I browsed to the web page, link to which you quoted.  At that page (on GitHub) 
I clicked the “Clone or download” button, then clicked the “Download ZIP” 
option, whereupon a .zip file could be downloaded.  Within that .zip archive, 
in a subdirectory, was a file which appeared as follows to the ‘file’ utility:
> file employees.db
employees.db: SQLite 3.x database

Better yet, sqlite3 v22 thinks it is a valid database.

I don’t know what you did, but the evidence suggests you simply grabbed 
whatever the server dished up under the http(s) protocol for the given link. 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need setup code for VC++ 2017 that will ACTUALLY COMPILE

[Larry Brasfield]

Zydeholic wrote:
➢ I compile and get one error: Severity    Code    Description    Project    
File    Line    Suppression State Error    LNK2001    unresolved external 
symbol _sqlite3_version    sqlite_try_3    
C:\Users\DSNoS\source\repos\sqlite_try_3\sqlite_try_3\sqlite3.obj    1

That symbol is declared, and a definition for the object so named is coded, in 
the sqlite3.c amalgamation without the possibility of omission by the 
preprocessor.  So I find it exceedingly strange that your link operation is 
complaining of an unresolvable reference to that symbol in sqlite3.obj.  For a 
C compilation, which you certainly should be using for that C source, the name 
should be undecorated, except for the leading underscore, just as it appears in 
the above-quoted error message.  This leads me to believe you are doing 
something too strange for anybody to guess with the information provided so far.

You may notice that this thread is misnamed for this latest difficulty, since 
the code does actually compile.  If I had to name it accurately, it would be 
called: [off topic] Need build instructions for my project which uses SQLite in 
a development environment differing from the one actually supported by the 
SQLite team.

Some questions to ask yourself as you attempt to sort this out:
1. Am I compiling the .c sources as C language?
2. Have I modified the sources everybody assumes are as released by the SQLite 
team?
3. What does insight does dumpbin.exe, (the VC command line tool for showing 
compiled image content), provide into my link errors?
4. How does my sqlite3.obj differ from the one I get following step 19 at 
https://www.sqlite.org/cli.html , and why?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about floating point

[Larry Brasfield]

Tim Streater wrote:

[Responding to “There is a lot to like about BCD.”]
> And do any processors/computers have hardware for that these days?

The modern X86 descendent processors from AMD and Intel implement AA{A,D,M,S} 
instructions, which do the BCD adjustments associated with addition, 
subtraction, multiplication and division.  They do not work in 64-bit mode, 
however.  It would be interesting to know why not.

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

Re: [sqlite] Question about floating point

[Larry Brasfield]

Gerry Snyder wrote:
< I don't think anyone has pointed out that the "evil" is not floating point, 
it is the binary exponent.

Disregarding the “evil” appellation, the fundamental fact is that, with modern 
floating point hardware (implementing the IEEE-754 standard), only that subset 
of rational numbers having a denominator which is a power of 2 can be 
represented.  If that is what you were trying to say, I would point out that it 
is not the representation of the exponent (binary or otherwise) that creates 
the mismatch with (many) rational numbers having a denominator which is a power 
of 10; it is that many such denominators cannot be represented at all when the 
interpretation of the exponent Ne is as 2 ^ Ne.

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

Re: [sqlite] Simple way to import GPX file?

[Larry Brasfield]

Winfried wrote:
< Hello,
<
< I need to importe a GPX file that contains a few thousand waypoints, eg.
< Some name
<
< I tried https://mygeodata.cloud and https://geoconverter.hsr.ch, but for
< some reason, the "waypoints" table doesn't contain latitude + longitude
< infos.
<
< Before I write a Python script, is there a simple way to import GPX data
< into SQLite?

At this web page, https://www.gaia-gis.it/fossil/spatialite-tools/index ,
you can see a reference to an XML import facility, spatialite_xml_load ,
which is part of spatialite-tools.  It is very generalized, and can be
used to create a SQLite database from well-formed XML.  Once you have a
DB loaded with that tool, (which will be a several-years-old version of
the SQLite3 file format, but readable by modern SQLite3), you can attach
and query that DB to get the data into whatever DB schema you favor.


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

Re: [sqlite] Segmentation fault when using window function with (nested?) subquery

[Larry Brasfield]

Gene Sacky wrote:
> This query causes segmentation fault in SQLite 3.26.0 (on macOS 10.14.1).
> --
> SELECT * FROM(
> SELECT * FROM (SELECT 1 AS c) WHERE c IN (
> SELECT (row_number() OVER()) FROM (VALUES (0))
> )
> )
> --
> 
> However, no error occurs in the following queries. The only difference is
> whether it is wrapped with "SELECT * FROM" .
> --
> SELECT * FROM(
> SELECT 1 AS c WHERE c IN (
> SELECT (row_number() OVER()) FROM (VALUES (0))
> )
> )
> --
> 
> --
> SELECT * FROM (SELECT 1 AS c) WHERE c IN (
> SELECT (row_number() OVER()) FROM (VALUES (0))
> )
> --

The above fault and non-fault also occur on Windows 10 x64 (with a 64-bit 
build) with the SQLite3 v3.26 shell.  I can provide build options and compile 
flags if that would be useful.

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

[sqlite] geopoly_contains_point(P,X,Y) doc is overly modest

[Larry Brasfield]

The documentation at https://www.sqlite.org/geopoly.html , at 3.8. for 
geopoly_contains_point(), asserts that the function “returns true if and only 
if the coordinate X,Y is inside or on the boundary of the polygon P.”  As now 
implemented, in the v3.26 release, it returns 1 where the point is on a 
boundary and 2 where the point is inside of the boundary.  While the 
documentation is technically correct, (for a suitable definition of “true” 
differing from its meaning in SQL), this seems to be a useful behavior worthy 
of exposure.

I suggest the rewording, “returns 2 if the coordinate X,Y is inside polygon P, 
1 if on the boundary, or 0 otherwise.”  I submit that this might help avoid 
errors such as “… WHERE geopoly_contains_point(_shape, ptX, ptY) = TRUE”, which 
will produce surprise for those who read the present claim literally.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why not text mode for file from .output?

[Larry Brasfield]

I notice that the file written by the CLI shell’s .output command, on the 
Windows platform, does not have CRLF line ends.  From the code in v3.25.0 and 
v3.26.0 (pre-release), it appears that the file named as the .output parameter 
is opened in binary mode. (bTxtMode is left at its initialized value of 0.)

The rationale for this escapes me.  I think it is likely a bug.  It can be 
worked around, of course, but I cannot see why binary mode would ever be 
preferred for what is essentially text.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] segmentation fault in sqlite api call

[Larry Brasfield]

The code you provided declares and defines a pointer, named ‘db’ in main(), 
which is used with the SQLite API but never made to point to a valid object in 
memory or even to allocated memory.  Hence your segmentation fault.

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

Re: [sqlite] Using SQLite in C# without System.Data.SQLite

[Larry Brasfield]

Jordy Deweer asks about alternatives to System.Data.SQLite because: “I run into 
errors a lot, using the System.Data.SQLite libraries. It easily crashes, trhows 
exceptions and similar issues...”.

I’ve used that module for several projects and found it to be generally robust. 
I have also seen that the developers working on it are responsive here (in this 
forum) and attend to bug reports promptly. It is a quality offering, IMO.

I venture that if you are seeing exceptions thrown from that module, they 
likely signify usage contrary to the intention underlying the API design.  
“Crashes”, (if the term has its usual meanings among SDEs), tend to indicate a 
bug somewhere. If they can be properly attributed to System.Data.SQLite, (with 
a simple C# test case), I am sure that the modules developers would jump on a 
bug report tendered here. I’m not sure what “similar issues” would be, but I 
imagine that if you brought the difficulties here, you would find willing help 
to resolve them, as I have seen often.



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

Re: [sqlite] Using SQLite in C# without System.Data.SQLite

[Larry Brasfield]

Jordy Deweer asks: “Is there a way to use a SQLite database in a C# project, 
without 
installing / configuring / depending on the System.Data.SQLite libraries?

I really hope there is.”

If you do a web search for the combination of terms “SQLite”, “C#” and 
“library”, you will find several alternative libraries.  Some of them are 
thinner wrappers (of SQLite) or provide less complete exposure of SQLite’s API.

You have the option of fashioning your own wrapper using the C# P/Invoke 
mechanism, or you may find the ‘NativeMethods’ functionality of interest.  
However, doing this robustly requires enough expertise that you should be wary 
of tackling the project.

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

[sqlite] [EXTERNAL] (no subject)

[Larry Brasfield]

Lars Frederiksen larshgf at dadlnet.dk wrote:
<
From here it was copied to a folder where my program exe-file is placed
when I develop. Using flashcard.db from this folder gave the mentioned
error.
<
I have to find out why it works like that!

You apparently deploy your .exe to a location which inherits permissions
from its parent. You will be able to see this in detail if you look at the
'Security' tab in the 'Properties' dialog, available as a context menu in
Explorer (the Windows shell and file manager.)

This is off-topic in this forum.  You can learn more by investigating
"Access Control Lists" for the Windows OS.

Cheers,
-- 
Larry
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] shell edit() trips on Windows newline kink

[Larry Brasfield]

Upon thinking further about this bug, I realize that "correct" behavior is
not obvious and might warrant some discussion.

At first, I questioned whether it is appropriate to handle data differently
depending upon whether it is typed as BLOB or text.  Maybe the edit()
function needed a flag for this.  However, since this can always be
affected, either way, by a type cast, there is no such need. And, given
this (effective) option, it seems that treating a text-typed value as being
subject to the usual text transformations [a], is perfectly fine, and
convenient when they may be needed in order for a user's favored
text-editing program to be used. [b]

[a. The CRLF <-> "\n" translation is just one such transformation.
Character coding could be another. ]
[b. On Windows, notepad.exe is always available and "on the PATH", which
might make it favored sometimes. But it handles LF as line boundaries very
badly. ]

I notice an upcoming change to the edit() function, checked in a few days
ago, which appears to make the allow-text-transformation decision based
upon whether the original input contained any CRLF pairs.  I would urge not
adopting this approach because it presupposes something not necessarily
true, that the input to edit() reliably signifies what should happen to its
output.  That would not be true if, for example, a single "line" of text
without a terminating CRLF were to be edited to become multiples "lines"
containing CRLF pairs (which might number one less than "lines".)  I submit
that a better approach is to embrace the possibility of newline
transformation for text-typed edit() input, leaving in the user's hands
whether to block that by type-casting to BLOB.  That is an option that is
eliminated with the recent check-in.

Best regards,
-- 
Larry
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] When less help is more helpful

[Larry Brasfield]

Lately, the SQLite CLI shell's help has become quite voluminous as features
have been added.  On my typical console window, it fills 3 screens, and so
I usually have to scroll back a screen or two to see the help I seek.  Also
inconveniently, the last several shell interactions get pushed well
off-screen at the same time, no longer easily seen.  This induced me to
enhance the .help command to accept an optional argument specifying a
subset of help to be blurted.  The argument need only be one or a few
leading characters in the shell command(s) of interest to reduce the blurt
to just what is wanted.

Here are the relevant "fossil diff" fragments, in case anybody likes this
change, or the shell developers decide it is worthwhile and easily done.

  /*
 ** Text of a help message
 */
-static char zHelp[] =
+static char* pzHelp[] = {

[For each command's help, comma appended to its last line. For example:]
-  ".help  Show this message\n"
+  ".help ?for?Show this message\n",

-;
+  NULL
+};

   if( c=='h' && strncmp(azArg[0], "help", n)==0 ){
-utf8_printf(p->out, "%s", zHelp);
+char * zHelp;
+int nm = (nArg > 1)? strlen30(azArg[1]) : 0;
+int i;
+for (i = 0; 0 != (zHelp = pzHelp[i]); ++i){
+  if(nm > 0 && sqlite3_strnicmp(zHelp+1, azArg[1], nm) != 0)
+    continue;
+  utf8_printf(p->out, "%s", zHelp);
+}
   }else

Cheers,
--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] shell session feature, #define awkwardness

[Larry Brasfield]

In shell.c, #defining the variable SQLITE_ENABLE_SESSION, (regardless of
its value), enables the session feature.

In sqlite3.c, #defining the variables SQLITE_ENABLE_SESSION=1 and
SQLITE_ENABLE_PREUPDATE_HOOK=1 is necessary for the shell link to succeed.

I suggest this could be simplified, or at least made more coherent.  If the
update-related session behavior is optional in sqlite3, it probably should
be in the shell also.

--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] shell edit() trips on Windows newline kink

[Larry Brasfield]

I was experimenting today with a v3.25.0 variant and encountered this bug,
on my Windows 10 system, when I used the SQLite CLI shell's edit()
function.  Because I had put a newline in the text with the invoked editor,
and it was written and read back as a text file, the following code got
unhappy:
  fseek(f, 0, SEEK_END);
  sz = ftell(f);
  ...
x = fread(p, 1, sz, f);
  ...
  if( x!=sz ){
sqlite3_result_error(context, "could not read back the whole file", -1);
goto edit_func_end;
  }
The problem is that, on the Windows platform, newlines are stored on disk
as CR LF character pairs but, for text mode file I/O, are translated to a
single LF character in the C buffered file I/O library.  In the above
check, this causes x to be less than sz by the number of newlines so
translated.  There is an additional related problem whereby the 0
terminator on the read-in string (in text mode) is put in the wrong place.

Here is fossil diff output showing (what I believe to be) an effective fix,
which I have tested:
===
   if( bBin ){
 x = fread(p, 1, sz, f);
   }else{
 x = fread(p, 1, sz, f);
-p[sz] = 0;
+p[x] = 0;
   }
-  fclose(f);
-  f = 0;
-  if( x!=sz ){
+  if( ftell(f)!=sz ){
 sqlite3_result_error(context, "could not read back the whole file",
-1);
 goto edit_func_end;
   }
===
(I omit line number marking because my unrelated changes make them
inapplicable to source in the SQLite sources.)

Cheers,
--

Larry Brasfield
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Odd question

[Larry Brasfield]

Nelson, Erik - 2
​, on​
Sat, 16 Dec 2017 11:54:06
​, wrote:​


​> ​
For unfortunate reasons, I need a query that does an insert and also
returns at
​> ​
least one row... for example, something along the lines of
​>​
​> ​
Select 1 as value from (insert into table1 values(a, b, c))
​>​
​> Or
​>
​> ​
Select coalesce((insert into table1 values(a, b, c)), 1) as value
​>​
​> ​
I've tried a number of options but haven't been able to get anything to
work.
​> ​
Is it possible?

​No. Data and schema modification operations return no data from the DB.
(Data modification operations​ return a count of affected rows, but even
that is not available as part of a query.)

​What you probably want is to use a transaction.  That's a guess because
you have not explained your real problem -- only how you imagine you might
solve it.  You'll get better help, and viable alternatives, if you explain
what you are trying to do.​

​Cheers,
--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] doc puzzle, on-conflict clause in insert

[Larry Brasfield]

I was looking at http://www.sqlite.org/lang_insert.html for details on the
'INSERT' statement. In the text, it says: "The optional conflict-clause
allows the specification of an alternative constraint conflict resolution
algorithm to use during this one INSERT command. See the section titled ON
CONFLICT for additional information."

I puzzled over that text for awhile because there is no obvious conflict
option shown in the railroad syntax chart. Although I was able to sort this
out, after going to the linked page regarding "ON CONFLICT", I would like
to propose a much clearer way to document that option for the INSERT
statement.

The railroad chart would have the whole set of INSERT paths replaced with
this:
INSERT -
\-- OR --\ /
 |\-- REPLACE /|
 |\-- ROLLBACK ---/|
 |\-- ABORT --/|
 |\-- FAIL ---/|
  \-- IGNORE -/

(Best viewed with a monospaced font.)

The lead sentence referring to the confict resolution options would read:
"The optional, 'OR'-prefixed conflict-clause allows the specification of an
alternative constraint conflict resolution algorithm to use during this one
INSERT command."

Lest this be perceived as nit-picking, I justify it thusly: The SQLite
syntax documentation has been so clear and useful to me that my puzzling
experience with this issue really stood out. (For awhile, I thought it was
a plain doc bug, until I  went to a separate page to relearn how the 'OR'
syntax variant works.) The other is that the railroad diagram structure
does not clearly indicate the optionality except by recognizing the
duplication of the 'INSERT' keyword. (I'll admit to stupidity for not
seeing it sooner.)


-- 
Larry Brasfield

Re: [sqlite] pls dont send so many message - once a month is enough

[Larry Brasfield]

Ketil Froyn writes:
> Clicking the mailto works, but it's not a proper reply-to. These two
> messages have appeared as a separate thread in my mailbox, probably 
because
> Larry's message didn't add the proper message-id reference. So if you 
reply

> like this on a high volume list, people following the thread might miss
> your post.

Actually, the mailto: URL has the proper In-Reply-To header to make 
threading work.


Unfortunately, the Thunderbird email client no longer knows what to do 
with it.
This is a regression introduced in version 24.* and which appears to 
remain in the

present version 31.1.1 .

So, whether clicking that mailto: link will work (correctly) depends on 
the setup.
With Thunderbird as the handler, it will mess up threading, at least 
today.  I had
to manually set the In-Reply-To: header (using the value from that URL) 
to get

this message to thread-link correctly (assuming it does!).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] pls dont send so many message - once a month is enough

[Larry Brasfield]

John McKown writes:

>  The first option is to totally disable getting any emails at all. This
>  is often called NOMAIL mode. If you decide to do that, then you can,
>  at your convenience, go to this page:
>  http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/ and
>  review the archives. Before you can browse them, you will need to put
>  in your email address and password. This option is really nice for
>  reading threads because the software can put the thread together for
>  you in a logical manner. Unfortunately, you cannot use the archive
>  page to reply to a message, or to originate a message.

John, your kind reply is an inspiration.

You, and the OP, may be pleased to know that under the right circumstances,
you can use the archive page to reply to a message.  On every message
displayed by the web interface to the archive, the 2nd line had an
underlined,
"mailto:"; link.  For systems/accounts which are setup to handle that
"protocol",
clicking the link in most modern browsers will invoke the setup email
client,
with the correct addressee to reach the list.  (I have used that mailto:
link for
this message, in fact.)

Best regards,
-- 
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to load a blob from the shell?

[Larry Brasfield]

About my shell extension implementing ".blobextract" and
".blobreplace" commands, Richard Hipp writes:
 "apparently uses the incremental BLOB I/O interface to avoid the need
to load entire images into memory." and "That is nice, but on a modern
workstation with many GB of RAM, is it really necessary?  Maybe in
some obscure cases.  But in the common case of a smaller BLOB (a few
megabytes) I think custom functions would work better:"

Yes, that is much better for BLOBs where memory does not become a
limiting factor.  It has a nice property I think of as
"composability".

I wrote the extension when I was using a cute little TCL virtual
filesystem using SQLite for storage and BLOBs for file content.  My
thinking was that I did not wish to impose an artificial size limit in
the shell, which I consider a useful tool for all kinds of things,
including what I may dream up later.  I dislike having seemingly
reliable, simple tools suddenly pop up as failure points, blocking my
workflow with their limitations.

As I told Simon, it would be nice to combine the composability of the
extension functions with the memory gentleness of SQLite's incremental
BLOB I/O.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to load a blob from the shell?

[Larry Brasfield]

Quoting me, Simon Slavin writes:

> I, too, thought there should be something like that.
> Here is the .help portion for a shell enhancement I wrote awhile ago:
> .blobextract TABLE COL ROW FILE ?DB? Extract DB blob to a file.  Table,
> column and row must specify a blob selected by:
> SELECT column FROM DB.table WHERE rowid = row .
> FILE may be '-' for extraction to stdout.
> .blobreplace TABLE COL ROW FILE ?DB? Replace DB blob with file content,
> otherwise like .blobextract except that DB blob
> size must equal file size. (zeroblob(filesize))

Good idea for the function to create a file.  However, to conform closer to 
expectations of how SQL works, had you thought of creating a SQLite function 
which accepted a filename as a parameter and returned the contents of the file 
as the appropriate hex string ?  It could, of course, use significant memory if 
you tried to use it with a long file.


I wanted a solution which would work well with use of the shell in shell 
scripts, and I liked the streaming provision of SQLite's C API which 
allows very large BLOBs to be transferred without creating large, 
in-memory objects.  So my shell enhancement exploits the streaming API, 
using only a few pages of memory.


I sort of like your approach, and maybe there is a way to get the best 
of your's and mine.  Any ideas?

--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is there a way to load a blob from the shell?

[Larry Brasfield]

tonyp writes:

I haven't figured out how to load a blob (e.g., image) from the shell.  I
would think there should be something like this but can't find anything:

insert into table values(file('path_to_binary_file'));

Are blobs only loadable by using SQLite from C?

Any ideas?


I, too, thought there should be something like that.
Here is the .help portion for a shell enhancement I wrote awhile ago:
.blobextract TABLE COL ROW FILE ?DB? Extract DB blob to a file.  Table,
 column and row must specify a blob selected by:
 SELECT column FROM DB.table WHERE rowid = row .
 FILE may be '-' for extraction to stdout.
.blobreplace TABLE COL ROW FILE ?DB? Replace DB blob with file content,
 otherwise like .blobextract except that DB blob
 size must equal file size. (zeroblob(filesize))

--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Noticed crash on windows

[Larry Brasfield]

durga.disc wrote:
> We have an application on unix platform with sqlite database which
> is running from last 2 years. pretty well.
>
>   We recently ported on Windows too. Crashed at inserting record in a
> small database(less than 20 records, record has 10 columns). It's not
> reproducible.
>
>   call stack:
>
>   ntdll!RtlEnterCriticalSection+0x12
>   !winMutexEnter+0x13
>   !sqlite3_mutex_enter+0x19
>   !sqlite3_step+0x5e
>
>   Is it known issue with 3.7.11 on Windows.  Any idea?

That call stack shows (attempted) execution of some code which is hit
very frequently by most any Windows-hosted SQLite app.  This fact,
together with the extensive testing of SQLite on Windows, suggests
that your problem lies within code you have not shown, and most likely
within your own code.

If it is not reproducible, it may prove worthwhile to eliminate
threading issues, if you can, and to check for heap corruption and
buffer over-writes.

Going in, it is best to not read too much into your code running
"pretty well" on a Unix platform.  Have you run your code with
Valgrind (or equivalent) on that platform?  If not, doing so may help
you uncover a bug which affects behavior more badly on Windows with
your present build.

On Windows, you can use debugging aids built into the debug version of
the MS C runtime.  (See
http://msdn.microsoft.com/en-us/library/zh712wwf.aspx .)

Quite unlikely is the prospect for somebody here to say "Yes, we
routinely see crashes on Windows in the sqlite3_mutex_enter code."
You are going to have to debug this.

Good luck.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] extension search [was ... release schedule]

[Larry Brasfield]

*Jan Nijtmans wrote:*

> But not in dlopen():
> See:
>   
> Quoting:
> If filename contains a slash ("/"), then it
> is interpreted as a (relative or absolute) pathname.  Otherwise, the
> dynamic linker searches for the library as follows (see ld.so(8) for
> further details):
>
> I want the dlopen() in the Cygwin VFS to behave the same as other UNIX'es.

I suppose the issue here is whether an extension should be treated at
part of the application or something being specified by whoever set
the current directory.

For applications embedding SQLite, your preference makes much sense.

For SQLite usage where a user may be specifying extensions, it seems
to me that the usual path resolution rules for files in general
(rather than DLLs) makes more sense.  It was that usage which led to
my reply.

I do not know the best answer here, but I think more surprise will
attend using DLL loading rules.

Best regards,
-- 

Larry
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.8.4 release schedule

[Larry Brasfield]

Jan Nijtmans wrote, in part, re Cygwin :
*> *bare filenames are NOT expected to be found in the current directory

This makes Cygwin unique among all operating systems with which I am
familiar having support for hierarchical filesystems.  Most importantly, if
true, it would differ from Unixen.  I suggest that, in fact, bare
filenames, (names without any prepended directory path components), ARE
expected to be found in "the current directory" as that is known to any
process.  This is the way Unix has worked forever, and the way its
derivatives and inspirees all work.  Any other behavior will come as a
great surprise to most people.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WAL files and PERL question

[Larry Brasfield]

John Carey wrote:

[Lot's of stuff, in multiple, apparently redundant threads, cut as 
irrelevant.]



If I go into the firefox add on and run a checkpoint , the 2 new invoices
get flushed from the WL to the db file and then my program sees them
what am I doing wrong where my script is only looking at the db file and not
the WAL file?


Before getting to your topic (as I guess at it), let me remind you that 
posting the same question multiple times to a newsgroup or email list is 
considered to be rude.  If you post and get no reply, then, after a 
reasonable amount of time, (which would be several hours or more), you 
might review your earlier post for clarity and appropriate brevity, and 
decide to post a revision calculated to improve the odds of getting a 
useful response.  But just sending essentially the same thing under 
different subjects and names is more likely to reduce your chances.


I've omitted quoting most of your post(s) because it does not reach the 
problem, which is that you are paying too much attention to the WAL 
file.  Except for the fact that SQLite, under some circumstances and 
modes of usage, creates a WAL file, its content should be of no concern 
to you unless you are improving or fixing SQLite itself.  Other than 
deciding whether you need SQLite to employ the WAL file (of its own 
creation, normally), and making sure it can be created when needed, it 
is not something you have to deal with.  Only the file named in the 
sqlite3_open*() call is persistent except under certain abnormal 
termination conditions.  (You may want to study what those are, but that 
will not cure your present difficulty.)


I dare say that your puzzling query results would be the same if you 
were to run SQLite so that it does not use a WAL file.  Discovering 
whether that is so is a worthwhile experiment, and if it is so, will 
allow you to focus on your schema and query rather than details of 
SQLite implementation that should be opaque to you as a user.


Best regards,
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite, .Net and Datareader: Close method is too slow.

[Larry Brasfield]

*Mário Cardia wrote:*
> Solved:
>
> The Method Close of the Datareader was slow because it was inside a
> try-catch.
> I remove de try-catch and it´s ok now.

The SQLiteDataReader class, properly implementing the IDataReader
interface, exposes and relies upon proper use of the IDispose
interface.

In general, objects exposing IDispose should be created and
disposed using C# 'using' construct or its equivalent in whatever
CLR-targeted language you are using.  This would likely also fix
your slowness issue, and would ward off other difficulties that
are likely to arise in your use of these objects.

You should perhaps read about IDispose and the usage patterns
recommended for classes which need to implement it.  Several
classes in SQLite.NET, because they allocate resources needing
more deterministic release than the .NET garbage collector can
provide, are best used with the 'using' construct.

Best regards,
-- 

Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3DbMallocRaw is crashing

[Larry Brasfield]

Ashok wrote:

Hi All,

Have to come across any such issues?


Most experienced developers have come across such issues.  As Richard 
suggested, you are likely seeing the effect of a heap corruption that is 
occurring somewhat earlier than the "crashing" which finally reveals a 
problem.  His question, "Have you run your application using valgrind?" 
merits your attention.  If you do not yet use any tools for the early 
detection of heap corruption, you should learn to do so if you are a 
serious developer.  If you are using such a tool to help diagnose this 
particular problem, it would help others to help you if you were to say 
so.  If you cannot be troubled to use such a tool, it makes little sense 
for others to speculate as to how your not-yet-shown code is corrupting 
the heap upon which sqlite3DbMallocRaw() depends.  To be blunt, your 
hope that somebody will recognize and help cure your bug is unrealistic. 
 You have some work to do, and use of valgrind or a similar tool is the 
best advice you are likely to get that will help you do that work.


Best regards,
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to use sqlite in excel vba?

[Larry Brasfield]

Adding to previous post ...

Ready-to-run binaries are published for the Litex project, under
  https://www.assembla.com/spaces/litex/documents
via the link labeled 'litex.zip', at

https://www.assembla.com/spaces/litex/documents/ci_8Yib6ar3AVDab7jnrAJ/download/ci_8Yib6ar3AVDab7jnrAJ
and a .PDF of its documentation, 'litex.pdf', is at

https://www.assembla.com/spaces/litex/documents/c-5hiSb6ar3y3sab7jnrAJ/download/c-5hiSb6ar3y3sab7jnrAJ
.
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to use sqlite in excel vba?

[Larry Brasfield]

Wolfgang Enzinger wrote:


> Assuming you actually do need to compile something, (identity of which
> you provide few good clues), you might consider a package I had good
> luck with, a SQLite wrapper called 'Litex', available at
>https://www.assembla.com/wiki/show/litex

The download links on this page don't seem to work properly ...?

My recommendation for a SQLite COM wrapper would be this:
http://www.vbrichclient.com


I'm not getting into a debate about what is best for the OP, having too 
few clues to do so.


The vbRichClient appears to be *very sparsely* documented, with nothing 
on the SQLite interfaces.  And it is closed source.  There does not seem 
to be any demo code for the SQLite functionality.  So a user would be 
left guessing how to use it from what the type library suggests.


The Litex wrapper source is available in zipped form via the Subversion 
repository browser visible at the page I linked.  It can be built in 
Unicode and non-Unicode forms.  There is extensive documentation for it. 
 It is partitioned into a useful C++ wrapper and an ActiveX packaging 
over that wrapper.


The Litex source is a little old, and had to be modified slightly to 
build with more modern tools than VisualC++ 6.0.  That would not be an 
issue for the OP.


Cheers,
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to use sqlite in excel vba?

[Larry Brasfield]

YAN HONG YE wrote:

I wanna use sqlite in EXCEL vba, but I don't know how to compile the sqlite ocx 
file in vc6.


If you have a file with extension .ocx, you do not need to compile it. 
You just need to register it and use it.


Assuming you actually do need to compile something, (identity of which 
you provide few good clues), you might consider a package I had good 
luck with, a SQLite wrapper called 'Litex', available at

  https://www.assembla.com/wiki/show/litex
, with a few simple mods to make it acceptable to a more modern 
compiler.  (If you are running VC6, you may not have any such issues.)


Cheers,
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to interrupt a long running update without roll back?

[Larry Brasfield]

Keith Medcalf wrote:
> > I considered that as well, but it's not clear how much benefit you get
> > over the autoincrement scheme: the PK-index is there either way, so
> > that's not a slowdown.  The split table approach also makes
> > query-writing and indexing more complex, so at a minimum you'd probably
> > want to make a view that runs a UNION ALL on the two tables.
>
> Whatever for?  One table contains the data, the other contains the rowids
from the first table that require updating.  You would not be able to UNION
(ALL or otherwise) the rows from the two tables -- they are entirely
different.

He might want to write a query for that view which selects the rows in the
first table which require no updating and unions that with another select
which gets the ones in need of updating and also substitutes the computed
values which would be made durable by the update.

Cheers,
-- 
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Import skip first line

[Larry Brasfield]

Bart Smissaert wrote:

That modified version sounds useful. Could you mail it?


The source as modified is at
   http://www.filedropper.com/sqlite3modifiedshell
.

This includes a readline library that works on Win32.  The SQLite 
library source is omitted for space.  The shell incorporates the current 
release (v3.7.17) changes.  The file doall.btm is understood by JPSoft's 
TCC and the free light version, TCC/LE.  The makefile may not be 
current.  The modified shell is shell_3r7r17_mod.c .


Best regards,
--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Import skip first line

[Larry Brasfield]

Bart Smissaert wrote:

Importing a. csv file via the. import command of sqlite3.exe.
As the first line holds the field names I want to skip that.
There is no problem achieving this in code, but could I do this
purely via sqlite3 commands?


You could do it easily with a temporary table having an and additional 
autoincrement column, but the verbage to then select that table into the 
one where you want no header line would be code, IMO.


I get this functionality with a modified version of the SQLite shell. 
When headers are on, it also expects to see them upon .import and does 
the right thing, which I take to be interpreting them as column names.


--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] numeric string quotation

[Larry Brasfield]

I have table:

CREATE TABLE test (
   alfa char(5) NOT NULL default '',
);

when i .dump i obtain:

INSERT INTO test VALUES(01000);

but when i import in Mysql, i obtain

test = '1000'
and not
test = '01000'

because .dump create:
INSERT INTO test VALUES(01000);
and not:
INSERT INTO test VALUES('01000');

how to solve?


Why are you asking about an issue with Mysql in a SQLite list?

Assuming this (almost) happened with SQLite, (and assuming there was not 
really a trailing comma on the last column spec in the create 
statement), the literal in your insert statement becomes the same 
integer during parsing whether there is a leading '0' or not.  It is at 
execution time that the column type affinity causes that (same) integer 
to become a character sequence.


While it is unclear what problem you intend to solve, if you wish to 
insert the string literal '01000', write it as a string literal in your 
insert statement.


--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Order of columns in group by statement affects query performance

[Larry Brasfield]

*Daniel Winter wrote:0*
> I discovered that the order of columns in a group by affects the
> performance of a query. Is this expected?

Yes.

> For example:
>
> Table:  Column A int, Column B int, Column C int
> One Index:  A,B  (combined)
>
> Query 1:  SELECT A,B,count(*) from tableTest group by A,B
> Query 2:  SELECT A,B,count(*) from tableTest group by B,A
>
> Query 1 will use the index, while query 2 will not. (which makes Query 1 a
> lot faster with bigger tables). Both querys will result with the same
data.
> I do not really understand why it doesn't use the index for both querys.

The index has row references pre-sorted according to the criteria specified
for it.  That is why it can be used to speed up a query which sorts by
those criteria.  There is no mystery as to why it does not help to speed up
other queries which do not sort by those criteria.  Looking at the query
plan would show the index used for your first query and not for the second.

A similar effect would be seen with joins which are typically processed as
a sifted merge of sorted components.

Best regards,
-- 
Larry
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Timeline for full ALTER TABLE support?

[Larry Brasfield]

Referring to a page at 
http://web.archive.org/web/20020926232103/http://www.sqlite.org/omitted.html, 
clearly dated mid-2002, Tim Gustafson effluviated:


It's not that it's surprising.  The page currently says:

Those features near the top of the list are likely to be added in the
near future.

That's a lie.  There are no plans to implement anything on this list,
and the page should say so.


The word "lie" is ill-chosen.  Of the top 3 items in that 10-item list, 
two have been implemented.  The "likely to be added" statement is 
arguable true, even in retrospect.  To suggest that any deceit was 
intended or that the statement was known to be false when made is 
unsupported by the evidence and indicates either ignorance of what the 
word "lie" means or bad faith.


--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Newbie unable to get started with sqlite on Visual Studio 2012

[Larry Brasfield]

larrygauthier at charter.net writes:

I installed slqite, its sqlite3 command line interface, and a GUI admin
console. Have built and populated a couple of databases. But what I really
want to do is to connect to sqlite databases from Visual Studio 2012 using
Visual Basic.

Downloaded and installed the SourceForge System.Data.SQLite adaptor... now
what?  Is there a set of instructions somewhere for Creating a VS/VB 2012
project, creating a windows form, and enabling an ADO.NET adaptor to sqlite?

The instructions on these two pages do not work for me:
http://www.ehow.com/how_11386220_use-sqlite-vbnet.html
http://www.dreamincode.net/forums/topic/157830-using-sqlite-with-c%23/

Help needed. Thanks in advance.


For information specific to the ADO.NET adapter for SQLite, start at:
  http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki
.

How to create a VB project, use Visual Studio, or program using the 
ADO.NET APIs is off-topic here.  There is a wealth of information on the 
net regarding those topics.  You can find it.


I will add, (approaching the border of topicality here), that the 
adapter adheres closely to what the API designers had in mind and have 
documented, allowing me to take code which works with the SQL Server 
Compact Edition and use it nearly unchanged with SQLite.  The developers 
anticipate and well support such scenarios.


Cheers,
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I write a query

[Larry Brasfield]

Petite Abeille wrote:

That said… it seems to be odd to have a 'player' vs . 'draftedplayers' table… 
looks like a typical is_a vs has_a confusion...


The table named 'draftedplayers' is simply a many-to-many relation 
between the 'player' and 'league' tables.  It lacks attributes of 
'player' entries other than the primary key.  I see no is_a versus has_a 
confusion here.


Cheers,
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite .NET 4.0 provider available?

[Larry Brasfield]

moumita wrote, twice:
> I want to user sqlite .net 4.0 provider. from where get that one?
> Please help me in this regards.

See http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

Also, please quell the repeat postings.  Nobody is going to answer
faster, or decide to answer, just because you post redundantly.

Cheers,
-- 
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 Bug Report: The shell accepts some dot commands ending in semicolons while rejecting others.

[Larry Brasfield]

David Bicking wrote:

Actually, it looks like anything except ".header on" will turn headers off. That includes ".header 
on;" (with semicolon - it doesn't do nothing, it turns the headers off) or ".header off;" (with or 
without semincolon.) or ".header ;" (with a space and semicolon but no text.)

I could only get it to return error text with .header or .header; (no space 
before semicolon.)

.explain behaves the same way. "on;" is treated the same as "off" or "foo".

(At least with version 3.7.0 which is what I had handy to test with.)


A quick perusal of the code reveals this logic:
Any case variation of "on" or "yes", or anything that starts with a 
non-zero integer counts as "true" with regard to its effect as an option 
setting.  Anything else counts as "false".  If this was the "expected" 
behavior, it would have to be considered bug-free now.


If anybody truly cares enough to make this behave better, it is 
encapsulated in a function named "booleanValue(char *zArg)".  For 
myself, since it takes "0" and "1", which are easy to type, the present 
behavior is entirely unobjectionable.


As for whether an appended ';' (or any other junk not called for in the 
.help output) ought to produce a diagnostic instead of simply following 
the above logic, I would say that adherence to the GIGO principle is 
perfectly adequate.


--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 Bug Report: The shell accepts some dot commands ending in semicolons while rejecting others.

[Larry Brasfield]

Nathan Chung wrote:

SQLite version: 3.6.12
OS: Mac OS X 10.6.8

*Summary:
The SQLite3 shell accepts some dot commands ending in semicolons while
rejecting others without displaying proper error messages. The
behavior of the dot commands could be more consistent. Examples
include "header off;" and "header on;". The shell accepts "header
off;" while rejecting "header on;" without a proper error message.

*Steps to reproduce:
1. Launch SQLite3 shell with a populated database and type in ".header on".
2. Try a select statement. The retrieved table will show the field headers.
3. Type in ".header off;".
4. Try a select statement. The retrieved table will not show the field headers.
5. Type in ".header on;".
6. Try a select statement. The retrieved table will still not show the
field headers. Note that the ".header on;" command from the previous
step did not generate any error messages even though the command was
rejected.
7. There may be other dot commands displaying other inconsistent
behaviors. I have only verified the issue with "header on;" and
"header off;".


The code for the shell command decoder is not written to perform as you 
expect.  It is very simple, (which is also a virtue), and focused on 
accepting correctly composed commands. The shell is considered to be a 
tool for easily performing basic operations upon a SQLite database, in 
the hands of more software-savvy people than those who would be lost 
without detailed and "consistent" error reporting.  I dare say that said 
code, if modified to meet your expectations, would be less suited to its 
purpose, harder to maintain and understand, and would divert effort from 
SQLite itself.  I say this as one who has found reason to modify that 
code and who is glad that part of the task consumed little time.


--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Change in behavior between 1.0.79.0 and1.0.83.0 in System.Data.SQLite

[Larry Brasfield]

Joe Mistachkin wrote:

Does the Entity Framework make use of data adapters?  If so, I just checked
in a fix to dispose the internal commands managed by the SQLiteDataAdapter
class (i.e. because the base class DbDataAdapter apparently does not
explicitly dispose them).


I don't know about data adapter usage, but I am seeing the same behavior 
with Entity Framework version 5.  I have scrupulously disposed of 
everything my code has gotten.  I have also overridden the 
DbContext.Dispose(bool disposing) method and can see that the base 
(DbContext) is in fact disposing the connection it has.



Could you please try the latest trunk code and let us know if that clears
the issue you are seeing?


I do not see such a change anywhere in recent check-ins listed at
  http://www.sqlite.org/src/timeline?r=trunk
.  Is there a build somewhere?  I would be happy to try it.

Cheers,
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] useful Perl modules for working with SQLite databases?

[Larry Brasfield]

James Hartley wrote:

SQLite has recently been added to the base installation to the Unix-variant
I frequently use.  This is a good thing.

I'm looking at the various CPAN modules which are available in the
ancillary ports sponsored by that Unix-like project, & I see that
DBD::SQLite package could be updated.  This raises the question as to
whether other CPAN modules should be made available too.

So, I ask the fine readership here.  Are there particular Perl CPAN modules
which are especially useful to the applications you develop?


That's a very broad, and mostly off-topic question here.

That said, and without pretending it answers the question, I can say 
that I have found these modules handy for DB work in Perl, much of it 
using SQLite:

  use DateTime;
  use DBI;
  use DateTime::Format::DBI;
  use DateTime::Format::ISO8601;

Cheers,
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple network connections

[Larry Brasfield]

Çağlar Orhan


Hi Larry, thank you for your explanatory answer. I have an idea now, but
with my poor English am afraid i did understand wrong or incomplete :(

I am going to try my network layout.
We have an IIS as a server and clients have MS Win7 on their PCs.
But usually we used IIS machine as a clasical static file storage. So i do.
There is one mydb.sqlite file as DB on somewhere at IIS.
IIS not to pay any atention on the file and not to run any application on
itself.
But every client has MS *.hta files and connecting to sqlite over ODBC
driver with vbscript. That means 5-10 or few larger client counts make
connections to that unique sqlite file on the IIS server.
Every client (5-100) all over the network has their own application which
takes aim at mydb.sqlite file as DB target source.
Basically proceses are just read and write to table.

And you wrote
"What Jay and others warn about is that SQLite is not designed for
concurrent DB operations.  Also, have its file store be accessed over a
network from SQLite code running remotely is a formula for database
corruption.  But it looks like you are not doing that."

Is my situation comply with warning :(


From your first post, I understood that the IIS server would be doing 
all the SQLite operations, (likely from multiple threads, although that 
could only be surmised).


Now, it appears that you have clients on different machines accessing a 
SQLite file over the network via their own SQLite code.  This is 
precisely what the warning advises against.  The problem is this: Even 
though the SQLite design anticipates different processes operating on 
the same database file, it guards against concurrent access issues by 
using file locking and another file located "beside" the database file 
to control access and provide journalling.  If file access over a 
network had the same semantics and predictability as machine-local file 
access, then there would no reason your usage would not work. 
Unfortunately, file access over a network has known weaknesses with 
respect to file locking.  This means that such access involving running 
SQLite code and a remote database file is unreliable when multiple 
clients may perform nearly concurrent operations.  Just as network file 
locking sort of works, your application(s) may sort of work, at least 
enough for some simple tests to seem successful.  You would do well to 
avoid deploying such a solution.


You might be interested in this "Code Project" article, and perhaps its 
code:

http://www.codeproject.com/Articles/22416/A-SQLite-Server-Client-Implementation


Thank you,

You're welcome,
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiple network connections

[Larry Brasfield]

Çağlar Orhan wrote:

While i was reading "*Using SQLite*" from *Jay A. Kreibich*, i saw that
sqlite doesn't designd for over network multiple client connections.
I have a project that built on Microsoft .hta and vbscript. And I am using
sqlite on the IIS for local storage but, 5 to 10 client applications
connecting as a network database to that sqlite. SQLite file is in a shared
folder.
I made some test that 2 or few more clients inserting, updating and query
same tables. I couldn't catch any inconsistency about table integrity.
But on the book, because of there is no native support for providing access
to multiple computers over a network, cause problems.
Like database file corrupt etc.
Is there a solution for network shared sqlite problematic?


If your SQLite DB is being accessed locally by the webserver, then there 
is no reason to worry about consistency issues due to the webserver's 
network activity, provided that your application can handle the access 
delay issues that arise when multiple threads may attempt overlapping DB 
operations.  If you are happy with the performance and the number of 
clients is not likely to grow much, SQLite could be a good solution.


What Jay and others warn about is that SQLite is not designed for 
concurrent DB operations.  Also, have its file store be accessed over a 
network from SQLite code running remotely is a formula for database 
corruption.  But it looks like you are not doing that.



Thank you


You're welcome.
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert return -1

[Larry Brasfield]

Live Happy wrote:

i tried to insert this text :

BEGIN:VCARD\nVERSION:2.1\nN:;okpl;;;\nFN:okpl\nTEL;CELL:034567895566\nTEL;HOME:885666\
nEMAIL;WORK:shsjhs at ghshhs.com\nEND:VCARD\n

to my sqlite database in android but it return me -1 is there any special
characters should change or replace wish some help and thanks on advance


There is no reason such text cannot be inserted, provided that is what 
finally reaches the SQLite prepare and/or parameter binding API.  Your 
difficulty most likely arises from how your call into those APIs is 
done, which you have not shown.  To get help, you will need to show more.


Cheers,
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] System.Data.SQLite ".CHM" [was: PRAGMA foreign_keys ...]

[Larry Brasfield]

Joe Mistachkin wrote:
[regarding connection string options and a new open event in 1.0.83]

These features are both documented in the System.Data.SQLite "CHM" help file
(i.e. based on the embedded doc comments in the code).  However, I can see
how it might be difficult to find that information via the web.  Perhaps
I should work on generating HTML documentation to place on the web site?


Wow, a .CHM file!  I did not even dream that it existed.  Perhaps, 
instead of worrying about web-help, the top page at 
http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki could 
just have a link, labelled "Documentation", leading to a page that says 
something like:


Online help is a work-in-progress, but the distribution has a 
comprehensive Windows help file that is placed in the installation 
directory.


--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA foreign_keys = ?, feature request

[Larry Brasfield]

Joe Mistachkin wrote:
[regarding foreign key enforcement when user does not directly open 
connections]

> If anybody knows how to solve this more easily than suggested above, I
> would appreciate your input.
>

There are two ways to solve this:

1. The 1.0.83.0 release of System.Data.SQLite, due out before the end of
   the year, adds a static Changed event to the SQLiteConnection class.
   This allows external components to be notified when a connection is
   opened, closed, etc.

2. Alternatively, you can make sure the "Foreign Keys" property is set to
   true in the connection string.

Prior to the [pending] 1.0.83.0 release, only option #2 is available.


Thanks, Joe, for your quick and apt response.

Please do not take this as a gripe, but earlier, when I suspected that 
there might be a connection string solution, I did a search to see what 
the option name might be.  I saw nothing at www.connectionstrings.com, 
http://system.data.sqlite.org in the doc, FAQ or feature sections, and 
nothing at http://www.sqlite.org/c3ref/open.html (where I had hoped that 
'URI Filenames' might permit such an option).  If not for your providing 
it, I would be at a loss even if I knew there was such an option.


That event is a nice feature, and I look forward to seeing 1.0.83.0 .  I 
really appreciate the fine work that has been done on System.Data.SQLite.


Best regards,
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] PRAGMA foreign_keys = ?, feature request

[Larry Brasfield]

Larry Brasfield wrote:
...

The framework opens and closes the database, presumably like it treats
other DB connections.  The schema that it sets up for objects related to
each other relies on foreign keys.  Unfortunately, foreign key
enforcement in SQLite, as now specified and implemented, only lasts from
the time a PRAGMA is issued to turn it on until the connection is
closed.  Without some trickery, (or modifying System.Data.SQLite from
its distributed form), I see no way to hook DB opens so that I can get
foreign key enforcement turned on when it needs to be on.


There is a reasonable, if not readily obvious way to handle this problem.
The System.Data.Entity.DbContext object that must be created early in 
framework use scenarios has a Database property whose Connection 
property has an event called StateChange.  By adding a suitable delegate 
(event handler) to this event, it is possible to turn on foreign key 
enforcement just after the connection is opened and before any work is 
done with it.


This does not make persistence of foreign key enforcement seem much less 
desirable.


--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] PRAGMA foreign_keys = ?, feature request

[Larry Brasfield]

I have a situation here that I expect will apply to others as 
Microsoft's "Entity Framework" is used with System.Data.SQLite .


The framework opens and closes the database, presumably like it treats 
other DB connections.  The schema that it sets up for objects related to 
each other relies on foreign keys.  Unfortunately, foreign key 
enforcement in SQLite, as now specified and implemented, only lasts from 
the time a PRAGMA is issued to turn it on until the connection is 
closed.  Without some trickery, (or modifying System.Data.SQLite from 
its distributed form), I see no way to hook DB opens so that I can get 
foreign key enforcement turned on when it needs to be on.


I recognize that I can compile SQLite myself with foreign key 
enforcement defaulted to on.  I may have to do that, but it would be 
nicer to be able to use System.Data.SQLite as built and distributed by 
sqlite.org .


Perhaps it would make sense for foreign key enforcement to be made 
persistent if something like

  PRAGMA foreign_keys(1, 'PERSISTENT')
was executed.  This could work to turn enforcement off, which could be 
useful once SQLite begins having it on by default.


If anybody knows how to solve this more easily than suggested above, I 
would appreciate your input.


Thanks,
--
Larry Brasfield
*
*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] strange behavior with integer with where clause

[Larry Brasfield]

Patrik Nilsson wrote:

After dumping the database I found that the line of insertion looks like
this

INSERT INTO "repetition"
VALUES(617,X'323031322D31322D32332031393A33303A3436',X'30',X'323031322D31322D32332031393A33303A3436',0,1,1,0);

Does SQLite manage the insertion with hexadecimal characters?


Those hex digits are part of what the SQLite docs call a blob literal.

The 3rd column is greater than zero and is the ASCII code for the 
character we call zero.  I expect that your code for displaying results 
is disguising that fact.  I also expect that you will want to find 
where/why blobs are being inserted, and get the inserted types to be 
closer to what is supposed to be represented.  (number, string, etc.) 
The BLOB is really for raw data that is not to be further interpreted as 
anything else.


Cheers,
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] strange behavior with integer with where clause

[Larry Brasfield]

Patrik Nilsson wrote:

> What do you get with query
>   select * from repetition where cast(interval as integer)==0 and
> interval>0

"select * from repetition where cast(interval as integer)==0 and
interval>0 and id=617"

With this I get the same as the strange one. Without "id=617" it is
still selected.


This boils the probable possibilities down to:
1. Your "interval" column for that row contains a real number greater 
than zero which is rendered, perhaps by the column's type affinity, to 
integer 0; or
2. SQLite is broken in a very basic way that only you have encountered 
and brought here.


My money is on #1.

You might to a table dump using the SQLite shell to see which case it is.

Cheers,
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] strange behavior with integer with where clause

[Larry Brasfield]

Patrik Nilsson wrote:

When I perform a select as the following

select * from repetition where interval>0 and id=617

the result is unexpected

617|2012-12-23 19:30:46|0|2012-12-23 19:30:46|0|1|1|0

It shows a result with "interval" zero, although I requested everything
above zero.

When I select using the same statement and interval equal to zero, there
is no result lines.

select * from repetition where interval=0 and id=617

The table looks like:

0|id|integer|0||1
1|last|datetime|1|datetime('now')|0
2|interval|integer|0||0
3|next|datetime|1|datetime('now')|0
4|priority|integer|1|0|0
5|repeat|integer|1|1|0
6|valid|integer|1|0|0
7|sound|integer|1|0|0


What do you get with query
  select * from repetition where cast(interval as integer)==0 and 
interval>0

?

--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] parameters in a view, disallowed? (docs issue)

[Larry Brasfield]

Kevin Benson wrote:

At least, there's this:

http://sqlite.org/docsrc/info/7276f4a4a3e338ea187cb5e50c57e4f9806aed89?sbs=0

+ERROR_MSG {parameters are not allowed in views} {
+  The right-hand side of a CREATE VIEW (that is to say,
+  the SELECT statement that defines the view) may not contain
+  bound parameter names like "?", "?123", ":term", "@xyz", or "$var".
+}


That certainly indicates what the program's author(s) intend, so we can 
say this is a documentation issue (as detailed in my 3rd post on this 
thread).  The syntax diagrams show that, just as parameters may appear 
in select expressions, they may appear in a "create view xxx as select 
...", and there is no *documentation* to the contrary.


Best regards,
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3.c:134542: warning: assignment from incompatible pointer type

[Larry Brasfield]

Pavel Volkov wrote:

Please consider the following error:

sqlite3.c: In function 'deserializeGeometry':
sqlite3.c:134542: warning: assignment from incompatible pointer type

And the patch for it.


Three questions arise from your message:
1. Which version of sqlite3.c ?  (It's not the latest, apparently.)
2. What patch?  (Attachments are lost when posting here.)
3. What has this got to do with the referenced post "Sqllite3 dll crash 
on ..."?


--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] parameters in a view, disallowed? (docs issue)

[Larry Brasfield]

On 12/20/2012 4:10 PM, Larry Brasfield wrote:
> Igor Tandetnik wrote:
>>>> [regarding where parameters allowed, "where literals are"]
>> >
>> > How did you discern this?
>>
>> I know from experience where parameters work (SELECT, INSERT and
>> similar; also ATTACH as one of my projects happens to use it this way),
>> and where they don't (all forms of CREATE; I haven't tried ALTER but I'm
>> 99% sure it won't work there either). I've just tested PRAGMA. I briefly
>> scanned the rest at http://sqlite.org/lang.html to confirm that their
>> syntax doesn't involve expressions.
>
> I do not mean to be argumentative here, but I think the documentation on
> this leads to a different result.  If you examine the syntax diagram for
> "create view"

Why should I? I never made any claim about CREATE VIEW's syntax. In my
explanation, "All forms of CREATE", which includes CREATE VIEW, fall
under "know from experience" bucket, not "syntax doesn't involve
expressions" bucket.


You assert (believably) that SQLite has not allowed parameters in views 
for awhile, and that you know this from experience rather than the 
documentation.  I assert (without refutation) that the documentation 
clearly allows parameters in a view.  We have no disagreement, but it 
does appear that SQLite's operation differs from what its documentation 
clearly implies.  Hence my revision of the topic.


I will not suggest that you should be interested in this discrepancy, 
but others might be.


Best regards,
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] parameters in a view, disallowed? (docs issue)

[Larry Brasfield]

Igor Tandetnik wrote:

[regarding where parameters allowed, "where literals are"]

>
> How did you discern this?

I know from experience where parameters work (SELECT, INSERT and
similar; also ATTACH as one of my projects happens to use it this way),
and where they don't (all forms of CREATE; I haven't tried ALTER but I'm
99% sure it won't work there either). I've just tested PRAGMA. I briefly
scanned the rest at http://sqlite.org/lang.html to confirm that their
syntax doesn't involve expressions.


I do not mean to be argumentative here, but I think the documentation on 
this leads to a different result.  If you examine the syntax diagram for 
"create view", it refers to the construct 'select-stmt', which in turn 
refers to the construct 'expr'.  (See 
http://sqlite.org/lang_createview.html , 
http://sqlite.org/lang_select.html , http://sqlite.org/lang_expr.html ) 
 There, one alternative for expr is given as 'bind-parameter', which 
seems to not be defined under that name.  However, the same page on 
'expr' defines parameters.  Since using parameters in a view appears to 
be allowed by the syntax diagrams, and since I saw nothing to the 
contrary where it logically should appear (as a semantic exception to 
the syntax), I went ahead and tried to use them in a view.


Best regards,
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] parameters in a view, disallowed?

[Larry Brasfield]

Igor Tandetnik wrote:

On 12/20/2012 1:27 PM, Larry Brasfield wrote:
> With recent versions of SQLite, a prepare call fails when there are
> parameters in the SQL for a 'create view' statement.

Did it ever work with any version of SQLite? Parameters in DDL
statements don't make sense.


Agreed with respect to DDL.  However, creating a view is not quite fully 
DDL.  A view, at least in read-only usage, can be considered a deferred 
or encapsulated query (or subquery).



>  I stumbled into
> this for two reasons: The documentation for parameters and ..._prepare
> does not contra-indicate such usage; it seemed perfectly sensible; and
> it was useful in my application.  (I have a complex view setup which I
> had wanted to vary, parametrically, when the view was queried.)

By what mechanism did you plan to feed parameter values into the view
while SELECTing against it? What would the (hypothetical) syntax be like
for such a beast?


In whatever query finally uses the view, the parameters would have to 
substituted just as when they appear directly in a query.



> Would there be any downside to allowing named parameters to remain in a
> view's SQL?

Ah, I think I see what you have in mind. If you do "select * from
MyView", this query, even though it doesn't on the face of it appear to
contain any placeholders, would be deemed to sort of incorporate by
reference parameter placeholders from MyView.


Yes.


Specifying such a feature would sure be fun. Say, if I select against
two views that each have parameter named :X, should the query be
considered to have one parameter (so that a single bound value applies
to both views), or two (and then how would you refer to them?) What to
do with a parameter like ?1 - where do you start counting?


If somebody were such a masochist as to use placement-identified 
parameters, they would have to be counted as if appearing where the view 
is expanded.  This seems harmless except to those who invite the 
potential for confusion by using nameless parameters.



> I am tempted to offer a paragraph with which to enhance the doc page on
> parameters, explaining where they are allowed and where not.  But I can
> only guess what it should say without a lot of experimentation or study
> of the code.

I believe it's pretty simple. Parameters are allowed everywhere a
literal may appear, except in schema definition statements, namely
various CREATE statements as well as ALTER TABLE; and also PRAGMA (not
sure why). Currently, this leaves SELECT, INSERT, UPDATE, DELETE,
REPLACE, ATTACH (the file name is an expression which allows parameters)
and EXPLAIN (when the query being explained itself allows parameters).
None of the remaining statements allow for literals in their syntax, so
the issue is moot for them.


How did you discern this?  If it is in the docs, it is scattered and not 
tied to the sections that are primarily about parameters.


By the way, thanks for your help elsewhere.

Best regards,
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] parameters in a view, disallowed?

[Larry Brasfield]

With recent versions of SQLite, a prepare call fails when there are 
parameters in the SQL for a 'create view' statement.  I stumbled into 
this for two reasons: The documentation for parameters and ..._prepare 
does not contra-indicate such usage; it seemed perfectly sensible; and 
it was useful in my application.  (I have a complex view setup which I 
had wanted to vary, parametrically, when the view was queried.)


Upon thinking over how parameter substitution must be implemented, I can 
see that there may be difficulty with parameters in a view since its 
select appears to be stored as SQL, not subject to substitution at the 
time the view is created.  However, that seems like a feature rather 
than a bug.


Would there be any downside to allowing named parameters to remain in a 
view's SQL?


I am tempted to offer a paragraph with which to enhance the doc page on 
parameters, explaining where they are allowed and where not.  But I can 
only guess what it should say without a lot of experimentation or study 
of the code.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Suggested Improvement of Date Time Function

[Larry Brasfield]

Somebody claiming to be "IQ Support Team" wrote:

Suggesting addition of two new modifiers to SQLITE Date Time functions.

Add new modifier(14):javams and modifier(15):javanano similar to
unixepoch modifier but extended to support milliseconds and
nanoseconds.


This seems like an application-level feature.

If it were to go into SQLite, then I have a few questions:
1. Should the nanoSecond and milliSecond resolution time functions take 
into account the gradual slowing of Earth's rotation as reflected in 
occasional leap-Seconds?
2. Does anybody actually keep times with such high resolution where they 
are simultaneously concerned with converting to day-of-month, 
month-of-year and the like?


These questions arise from observing that SQLite's time functions are 
useful mainly for their conversions between time-into-era numbers and 
more people-friendly forms.  The only reason I see to stack this higher 
resolution functionality onto the existing functionality is to achieve 
some efficiency that would be less practical with the implementation 
split between SQLite and application code.  However, since anybody can 
#define SQLITE_OMIT_DATETIME_FUNCS during a build, and use their more 
specialized (or accurate) functions instead, this efficiency concern 
seems misplaced.


If the SQLite developers were to undertake this enhancement, it should 
certainly come with another #define to chop out the extra code it would 
entail.  (SQLITE_APPROXIMATE_TIME?)


Since you (Mr. Team) have apparently already created this much more 
accurate set of conversions, perhaps you could offer them as a plug-in 
replacement for the readily omitted functions.

--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to move around records in sqlite as the way as in CDaoRecordset of MFC??

[Larry Brasfield]

tigeryth wrote:

i used CDaoRecordset of MFC to handle records in ACCESS, whick is convenient to 
me move around records in table and edit certain record.

according to my habit, i wish sqlite has some kind of functions like, move(int) 
to get to cerctain record, getbookmark(...) to the record marked record and 
setbookmark() to mark the record, moveprov() to move one record back and 
movenext() to the record forward, IsEOF and IsBOF to know we are not out of 
table domain. all in one word, not only move forwardly but also backwardly 
through records in table.

i know sqlite3_get_table( )  ,  sqlite3_step() function and struct sqlite3_stmt 
 archive some goal mentioned above, but not all!!
how ?


SQLite does not provide any equivalent to a backward-moving cursor.

If you insist on using the SQLite C API, (when it appears you are using 
a library offering a putatively higher level of abstraction), you will 
need to look at the 'limit' and 'offset' qualifiers for 'select' 
queries.  These can be inefficient for otherwise large datasets, so be 
careful and consider incorporating similar result subsetting criteria 
into the 'where' clause.


Now, some unsolicited advice: While MFC's database interface was useful 
in its time, that time is past except for old projects in maintenance. 
You should be using the ADO.NET SQLite adapter on the .NET platform.  (See
http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki .)  I 
won't elaborate on this here, (as it is off-topic), but I doubt any sane 
developer could regret making that transition.


--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to manage Table inside Table scenarios

[Larry Brasfield]

Guillaume Saumure wrote:


I have to create a small program to track changes done on multiple
machines. The data structure look like :

Structure Notice
 PrimaryKeyID.l
 Type.l
 Date.l
 ExecutedBy.s
 RequestedBy.s
 Description.s
EndStructure

Structure Machine
 PrimaryKeyID.l
 Name.s
 Location.s
 List Notices.Notice()
EndStructure

To make a long story short, I have a Linked list nested inside Linked
list but I really don't know how to manage a scenarios like this using
Database recording system. Any suggestions ?


If I understand your "Linked list" and "nested" terminology as you 
intend, it could be recast in data modeling terms this way:

1. There is a set of Machine objects.
2. With each Machine object there is an associated set, possibly empty, 
of Notice objects.


The way to represent this in a database is to have a foreign key in the 
table representing Notice objects, where its value for each row refers 
to the Machine object, by its ID, with which it is associated.  So, your

Notice table definition would have a column definition such as:
  MachineID as integer references Machines(ID)

Your List structure has ordering in addition to defining sets.  If you 
wish to retain the order of the Notice object in the database, there 
will need to be a column in the Notices table giving its sequence number 
within the set which can be used to recreate the ordering in a query.


Regards,
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] multiple outstanding sqlite3_stmt objects

[Larry Brasfield]

At http://www.sqlite.org/c3ref/stmt.html , there is a weak implication 
that SQLite may expect single sqlite3_stmt objects to exist for a 
connection.  The pattern I intend to follow instead of the one shown as 
"life of a statement object goes something like this" is:

1. Create sqlite3_stmt #1
2. Create sqlite3_stmt #2
3. Bind values to sqlite3_stmt #1
4. Execute sqlite3_stmt #1
5. Reset sqlite3_stmt #1
6. Bind values to sqlite3_stmt #2
7. Execute sqlite3_stmt #2
8. Reset sqlite3_stmt #2
9. Repeat steps 6-8 as needed
10. Repeat steps 3-9 as needed
11. Destroy sqlite3_stmt #2
12. Destroy sqlite3_stmt #1

Is there any reason not to do this?  (I would not be asking if I could 
see any hint in the API docs that sqlite3_stmt objects can be used 
independently, in arbitrary order, during their lifetimes.)


Thanks,
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Denormalized view, "clumping" into length-limited groups

[Larry Brasfield]

Tom B. wrote:

So, do you mean something like:

select group_concat(RefDes) as Clump
from MyTable
group by cast ((substr(RefDes, 2, 99) - 1) / 50 as integer)


Yes, something like that is just what I needed.  Thanks!

(The designators are not always in such a monotonic sequence, nor is 
their numerical part so predictable, but those issues can be met in the 
source table design.)


Thanks, too, for helping me see that I should look at 'group by' clauses 
more fundamentally.


Best regards,
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Denormalized view, "clumping" into length-limited groups

[Larry Brasfield]

I've got a table which can be described as holding one "RefDes" per 
row.  (They are reference designators in a electronic PCB schematic or 
its bill-of-materials.)  There are thousands of them, and each one is 
very short, inducing the convention of clumping them together for 
display in a bill-of-materials so that line counts are held down and 
horizontal space is well used.  So, starting with a column which might be:

RefDes
--
R1
R2
...
R190
I want to convert it to something like:
R1,R2,...,R50,
R51,R52,...,R100,
R101,R102,...,R150,
R151,...,R190
where the clump length, setting the number of designators per line, is a 
parameter.  (Ideally, the string length of the clump would be the 
criterion, but this is dispensible.)


The converted result is destined for a spreadsheet, and I would really 
like the pseudo-table created therein to be a simple projection or view 
of the real data.  I realize this is not hard to do programmatically, 
(having done it a few times), but it would be nice for process/work-flow 
reasons to be able to use a query to get the data in presentation form.  
If there is no way to get a view that does this, I can create a 
derivative database table with the clumping already done 
programmatically.  But that presents its own issues, such as when to run 
the code that creates it.


I'm stumped as to how to do this.  I've done the reverse transformation 
in SQL, so it seems like this should not be as hard as I have found it 
to be.


Thanks for any tips, (even "Give up.")
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug: Segmentation fault in libsqlite3.so.0.8.6[b69a4000+ac000]

[Larry Brasfield]

On 4 December 2012, Paul Menzel wrote:

After doing `apt-get source sqlite3` and building it myself with
`debuild -b -us -uc`, I have the source file `sqlite3.c` and I am able
to look at the code statements.

> The backtrace from the core dump file is the following.
>
> Thread 1 (Thread 0x8acf1b70 (LWP 15522)):
> #0  0xb69bafe3 in pcache1Fetch (p=0xb8effb00, iKey=35985, 
createFlag=2) at sqlite3.c:36093
> h = 1169
> nPinned = 
> pCache = 0xb8effb00
> pGroup = 0xb8effb30
> pPage = 0xbf8ab0e8

The following code caused the segmentation fault.


Paul, I offer this to help you get your problem solved sooner, not
as criticism or complaint.

Earlier in this thread, Richard Hipp wrote:

Just because SQLite appears in a stack trace does not mean that SQLite is
at fault here.  In fact, far more often than not, when SQLite appearing in
a stack trace it means that some other unrelated component of the
application has corrupted the heap and SQLite just happened to be the
unlucky subsystem to trip over that corruption.

If you can generate some evidence that SQLite is malfunctioning, we will be
happy to look into the situation for you, and perhaps open a ticket.  But,
unfortunately, a single unreproducible segfault with a stacktrace that
includes SQLite routines does not constitute evidence of an SQLite
malfunction.


What you have supplied is evidence that SQLite either has a bug that your
code has exposed or its correct operation is subject to the various forms
of the "undefined behavior" that typically result from stray memory
overwrites by other code.  There is no way, given the data you provide,
to distinguish between these cases, and it is a fool's errand to attempt
such an effort.  The SQLite developers have better uses for their time
than to try to improve the codebase in response to allegations that it
might have a bug because its code exhibits the same vulnerability to
other code that every human-created C/C++ program must have.

To provide the most useful evidence of a SQLite bug, you need to create
some SQL that will crash the SQLite shell, or which will crash a C
program that is so simple that its correctness is apparent from some
reasonably small effort.

In all likelihood, you will be unable to create such a reproduction
scenario, and that should encourage you to start using a debug heap
or sophisticated tools such as Purify to discover where your code
has a stray memory write occurring.

Best regards,
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .dump ... [was: Please test the latest SQLite snapshot]

[Larry Brasfield]

On 3 Dec 2012, Richard Hipp wrote:

... You can get a tarball or ZIP archive of the latest raw
sources from Fossil at http://www.sqlite.org/src/info/trunk


I notice that in this (3.7.15 pre-release snapshot) version of
the shell the .help out for the .dump command reads:
  .dump ?TABLE? ...  Dump the database in an SQL text format
 If TABLE specified, only dump tables matching
 LIKE pattern TABLE.
This seems to indicate that more than one TABLE argument could be
supplied.  I happen to have a good use for that feature, and when
it did not work, I went to see why and found this code
if( c=='d' && strncmp(azArg[0], "dump", n)==0 && nArg<3 ){
guarding/selecting the .dump implementation.  By commenting out the
last && term, it happily does what I wanted, doing a combined .dump
output for just the tables I wish to recreate later.

Is multiple table dumping suspected to not work for some reason?
If so, the .help output should lose the '...'.  If not, perhaps
the shell.c source should lose that "&& nArg<3" clause.

Thanks,
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] light weight write barriers

[Larry Brasfield]

Nico Williams emitted:

You keep saying that programmers don't understand "barriers".  You've
provided no evidence of this.  Meanwhile memory barriers are generally
well understood, and every programmer I know understands that a
"barrier" is a synchronization primitive that says that all operations
of a certain type will have completed prior to the barrier returning
control to its caller.


Well, since you don't know me, this does not contradict you, but ...

My understanding of a memory barrier, formed from close study of the 
need for them and some implementations, is that they cause a partial 
ordering of memory operations, such that all accesses instigated before 
the barrier is created occur before all accesses instigated after the 
barrier is created.  This does not mean that the caller of a 
barrier-creating function (or the executor of a barrier-creating 
instruction) does not get control until all prior accesses have been 
"completed".  The "caller" may well continue executing instructions from 
cache, and other execution units may not be held up at all unless they 
instigate an "after" access.


I will be happy to become differently educated on this subject. 
(perhaps via some evidence ;-)


Cheers,
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do you load a ".csv" and skip the first line?

[Larry Brasfield]

joe.fis...@tanguaylab.com wrote:


Is there some way to load a ".csv" file into a SQLite database table
when the first row (record) of the file contains headers?


Awhile ago, I published a modification of the SQLite shell which I
wrote specifically to solve this problem.  When headers are turned
on, (only for output in the original version), the behavior of an
.import command is changed so that the first input line names the
columns, which must exist.

If you send email to l *dot* brasfield *at* computer *dot* org , I
can send the modified source for SQLite version 3.7.13 shell, or
a Windows executable, or both.  It has a few other changes that do
not interfere with conventional use of the shell which show up in
the .help response.

Cheers,
--
Larry Brasfield

(Examples of problem cut.)


This does not work?
--
.separator ,
.import test.csv ld_assay

Here's my database / table:
CREATE TABLE [ld_assay] (
   [id] INTEGER PRIMARY KEY,
   [barcode_id] NCHAR(9),
   [light] BOOLEAN,
   [bpm] REAL);

Here's my "test.csv":
id,barcode_id,light,bpm
1,02455,1,180.2
2,02455,1,168.3
3,02455,1,189.4
--

This works (example from different program):
--
When R loads a ".csv" file it uses the [header = TRUE] flag to skip the
first row of the file:
data.frame_ld_assay <- read.table("test.csv", header = TRUE, sep = ",",
colClasses = c("integer", "character", "integer", "numeric"))

Most likely utilizing the "skip, integer: the number of lines of the
data file to skip before beginning to read data." option.
--




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXT :Re: Multi-Thread Reads to SQLite Database

[Larry Brasfield]

Black, Michael wrote:

Why should shared cached be serialized when all the threads are reading?  I can 
see it for writing, but not just for reading.  There must be some logic that be 
done to allow this I would think (he said without looking at the code).


(from another non-student of the relevant code)
While it may be true that reading specific cache content, at 
sufficiently fine granularity, would be immune to interference from 
other readers, the cache structure, book-keeping, and content lifetime 
is just as likely to be altered by the action of other readers as by 
writers.  The database could be read-only (such as when no writers are 
busy), but the cache certainly will be in flux when readers alone are busy.


Cheers,
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use SQLite on VxWorks5.5

[Larry Brasfield]

Dear all,

 Hello, resently I have met some troubles when using SQLite on
VxWorks5.5. Does anybody know how to transplant SQLite to VxWorks5.5,
please help me, thank you very much.
 The VxWorks' vesion must be 5.5.


Having used VxWorks in the past, I knew that threading and file locking 
issues would be most likely to present issues for this port.  So I did a 
quick web search, which turned up this interesting result: 
http://marc.info/?l=sqlite-users&m=108060346504084


If you do not need multi-threading support, the VxWorks port should be 
fairly trivial as you can stub out those system calls.  For a 
single-threaded (and single-process) access pattern, SQLite is pretty 
much just another C program doing file I/O.  The above-linked page 
suggests preprocessor settings you need to consider.  It also mentions 
that there is a Berkeley DB port to VxWorks, which is likely to have 
done most of the work already.  (Berkeley DB is layered over SQLite.)


Best regards,
--
Larry Brasfield

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C++ programming - sqlite3_column() question (doc flaw?)

[Larry Brasfield]

After creating a database, I, now, would like to create a table called 
'friend', but for some reason 'sqlite3_column()' reports zero.
 What elese do I have to do to have the table created, this is what I have so 
far done.
 int main() {
 sqlite3 *db; // Data Base
 sqlite3_stmt* stmt;
 int rc; // return code

 // SQL statement
 std::string create_table("CREATE TABLE friend (name TEXT, address TEXT, age 
INT)");
 // 1.) Open database
 rc = sqlite3_open_v2(dbName.c_str(),
 &db,
 SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
 NULL);
 if(rc != SQLITE_OK) {
 sqlite3_close(db);
 std::cout << "error open_v2: " << rc << std::endl;
 exit(-1);
 } else {
 std::cout << "Welcome to a whole new world!" << std::endl;
 std::cin.get();
 }
 // 2.) Convert SQL text into a prepared statement and receive a valid 
validated statement
 rc = sqlite3_prepare_v2(
 db, /* Database handle */
 create_table.c_str() , /* SQL statement, UTF-8 encoded */
 create_table.length(), /* Maximum length of zSql in bytes. */
 &stmt, /* OUT: Statement handle */
 NULL /* OUT: Pointer to unused portion of zSql */
 );
 if(rc != SQLITE_OK) {
 sqlite3_close(db);
 std::cout << "error prepare_v2: " << rc << std::endl;
 exit(-2);
 }
 //3.) Evaluate the SQL stament passed to sqlite3_prepare_v2()
 rc = sqlite3_step(stmt);
 if(rc != SQLITE_DONE) {
 sqlite3_close(db);
 std::cout << "error sqlite3_step: " << rc << std::endl;
 exit(-3);
 }
 //3.1
 rc = sqlite3_column_count(stmt);
 std::cout << "Number of colums: " << rc << std::endl;
 sqlite3_finalize(stmt);
 sqlite3_close(db);
 std::cout << "Good bye!" << std::endl;
 return 0;
 }



The documentation does not say this in so many words, but:
You should place your call to sqlite3_column_count(...) after the 
prepare and before any sqlite3_step(...) which exhaust the result set.


The documentation for the sqlite3_column_count function says "This 
routine returns 0 if pStmt is an SQL statement that does not return 
data".  If this includes a pStmt for which data will no longer be 
returned, (as appears to be the case from the OP's report), this more 
stringent requirement should be mentioned.


--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Quoting "id" versus 'id' in query

[Larry Brasfield]

On June 11, rick wrote:

Yes, it can be explained: <http://www.sqlite.org/lang_keywords.html> says:

'keyword'  A keyword in single quotes is a string literal.
"keyword"  A keyword in double-quotes is an identifier

So, "id" is interpreted as a column name, not as the string literal 'id',
unless the value in the double quotes is not an identifier (column name).


You have overstated the requirements upon SQL identifiers.  Quoting via
surrounding double-quote is only necessary if the identifier would not meet
the definition of a "regular identifier", loosely understood to be a letter
followed by letter, digit or underscore characters.  Hence, the text "id"
without any quotes would be a valid SQL identifier.

From http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt :

  ::=

  | 

  ::= 

  ::=
   [ {  |  
}... ]



  ::= !! See the Syntax Rules

  ::=

  | 

1) An  is one of:

a) A ; or

b) A character that is identified as a letter in the character
  repertoire identified by the  or by the ; or

c) A character that is identified as a syllable in the char-
  acter repertoire identified by the  or by the ; or

d) A character that is identified as an ideograph in the char-
  acter repertoire identified by the  or by the .

--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Quoting "id" versus 'id' in query

[Larry Brasfield]

On June 11, rick wrote:

Something I noticed today:

sqlite> select * FROM words WHERE word = "id";


sqlite> select * FROM words WHERE word = 'id';
13556|id

sqlite> .schema
CREATE TABLE words (
 id integer primary key,
 word   varchar(64)
);

Yes, it can be explained: <http://www.sqlite.org/lang_keywords.html> says:

'keyword'  A keyword in single quotes is a string literal.
"keyword"  A keyword in double-quotes is an identifier

So, "id" is interpreted as a column name, not as the string literal 'id',
unless the value in the double quotes is not an identifier (column name).

I think this is quite nasty behaviour. Hope it prevents someone making the
same mistake!


You should take up the nastiness issue with the authors of the SQL-92 
standard and its successors.  Supporting the convention you just noticed 
is the least surprising behavior for those who have learned SQL as 
defined, and hence is not generally regarded as a nasty feature.


Good luck.
--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Use with asp.net

[Larry Brasfield]

On May 30, Mike wrote:

On 5/29/12 12:02 PM, Mike wrote:
> I'd like to use a SQLite db as a back end for asp.net web pages. Is
> there information somewhere on how to deploy it and set it up?
>
> I gather I need to use system.data.sqlite.dll. That's the part I'm
> most interested in.
>
> ___
> sqlite-users mailing list
> sqlite-users at sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
No one has ever done this?


Good start on using SQLite on the .NET platform:
   http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki

Good start on using a DB with ASP.NET:
https://www.google.com/search?q=asp.net+database+tutorial

People have done things close enough to "this" that you should be able 
to take it from here.  Help with programming that is not SQLite-specific 
is kind of off-topic here.  At the abstraction level exposed by 
System.Data.SQLite, you will not have many SQLite-specific issues.  For 
those that do arise, you are likely to find willing help here.


Regards,
--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What does "The use of the amalgamation is recommended for all applications." mean, precisely?

[Larry Brasfield]

On May 24, Dan Kennedy wrote:

On 05/24/2012 10:53 PM, Larry Brasfield wrote:
>> On the support page http://www.sqlite.org/howtocompile.html, it says:
>>
>> "The use of the amalgamation is recommended for all applications."
>>
>> Is this a general recommendation, to use the amalgamated source file
>> as the preferred way of including SQLite functionality in one's
>> application, rather than using a separately compiled library?
>>
>> Or should I read this as a recommendation just in case I need to
>> compile SQLite from source, and need to decide between using the
>> amalgamation or using the individual source files?
>
>  From the context of the recommendation, one can clearly infer that it
> addresses only how SQLite source is presented to the compiler. Whether
> the compiler's output is embedded in its own object file, a DLL, or
> directly into a calling program is not addressed.
>

The statement above is accurate.

But at the same time, it is a general recommendation that
you just pull sqlite3.c into your project instead of messing
around with shared libraries and so on.

Why risk ending up with an unexpected (possibly old) version
by linking at runtime just to save users less than 300K of disk
space?


I don't want to quibble about language here, but I see nothing at the 
quoted link about whether sqlite3.c should be used as a shared library, 
(or a static library).


For most projects, I expect that the recommendation you suggest makes 
sense.  However, the OP has indicated little that would allow us to 
guess whether his project should follow the norm or not.  There may well 
be issues other than disk space at stake.  For example, the .NET 
adaptation of SQLite benefits from being a shared library as this fits 
well into the "black box with well defined interface" usage model the 
.NET developers routinely use with a variety of packages.


Best regards,
--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What does "The use of the amalgamation is recommended for all applications." mean, precisely?

[Larry Brasfield]

On the support page http://www.sqlite.org/howtocompile.html, it says:

"The use of the amalgamation is recommended for all applications."

Is this a general recommendation, to use the amalgamated source file
as the preferred way of including SQLite functionality in one's
application, rather than using a separately compiled library?

Or should I read this as a recommendation just in case I need to
compile SQLite from source, and need to decide between using the
amalgamation or using the individual source files?


From the context of the recommendation, one can clearly infer that it 
addresses only how SQLite source is presented to the compiler.  Whether 
the compiler's output is embedded in its own object file, a DLL, or 
directly into a calling program is not addressed.


--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] store db from sqlite to online mysql or online sqlite

[Larry Brasfield]

On May 12, rebornishard wrote:

i browse but didn't find it
there is 5 apps running sqlite and they will export it intoonline
mysql or online sqlite database
search from doc but still didn't get anything
thanks


Your question is unclear, which is likely why it has not been answered. 
 I am guessing that you wish to transfer the content of a SQLite DB to 
some other DB.  The shell that is included with the amalgamation release 
has a command, ".dump", which writes to stdout a sequence of SQL 
statements which will generate the open DB contents.  Here is a sample 
shell session that demonstrates creating a DB, using .dump to get its 
generating SQL, and using that SQL to create a duplicate DB:


sqlite3 mydb.sldb
create table Silly (id int, what text);
create table Goofy (id int, how text);
insert into Silly values (1, 'silly one');
insert into Goofy values (2, 'goofy two');
.quit
echo .dump | sqlite3 mydb.sldb > mkdb.sql
sqlite3 dupe.sldb < mkdb.sql

With suitable DDL or a simple transformation of the DDL generated by 
.dump, that SQL can be used to "export it into online mysql" or some such.


Cheers,
--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.SQLite and pooling problem

[Larry Brasfield]

On 27 April, Greg Carter wrote:


On 27 April 2012 11:39, Larry Brasfield  wrote:

[snip]

> You "works as it should" is only assured (inasmuch as any
> software can) when you use the .Net framework's assured disposition
> mechanism ("using ..." and properly implemented IDispose) or effect the
> equivalent in your own code.
>
> Yes thanks I'm familiar with using native resources from .net.


My commentary was motivated by your assertion that the fault only 
occurred when you let the garbage collector dispose of your connection 
object.  Hence, my response was akin to the doctor who advises a patient 
complaining of pain when he does X, "Don't do X".  As detailed below, 
there is other good reason to not do X in this case.



After much more debugging I have found what I think is a bug.

...
It's good to see this sort of thing uncovered.


>  So is this intended behaviour or a bug?  I can try to come up with a test
>> case that reproduces this if needed.
>
> It's not really intended behavior but intent cannot cure this problem
> unless SQLite's finalizing API is used.
>
I'm not sure I follow you.  Users of System.Data.SQLite shouldn't have to
concern themselves with calling dispose.


I have to disagree with this, or be educated as to why it is true.  DB 
connections have always represented a hold on resources that should be 
timely released, which is why IDisposable is exposed by IDbConnection. 
Is there something that makes it useless to arrange that its Dispose 
method is timely called rather than letting the garbage collector do so 
whenever it gets around to collecting no-longer-referenced 
SQLiteConnection objects?



Regardless of that, even if you
do call dispose on the SQLiteConnection instance explicitly (or through a
using statement) the underlying SQLiteConnectionHandle is added to the
pool, where it can be garbage collected before it is removed.  I was wrong
when I said in my first post that this only happened when I let the
connection be implicitly garbage collected.  This would appear to be a
problem with any SQLiteConnectionHandle added to the pool.  I think the
only reason our code exposed this is that we had many
SQLiteConnectionHandle end up in the pool over a long period of time so the
garbage collector hand a chance to run on those SQLiteConnectionHandle.


That appears to be a fixable problem.  Thanks for digging it out.

--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Replying to posts here, threaded [was: SQLITE3 64-bit version]

[Larry Brasfield]

Gabriel Corneanu (and others) ask:


Off-topic: what do I need to do for a proper reply??


If your email client is setup to handle 'mailto:' URLs, clicking on the 
one just to the right of the poster's name will initiate a reply which 
has the 'Subject' and 'In-Reply-To' headers filled in.  The latter is 
what allows the replies to be usefully arranged in the threaded view.


Cheers,
--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.SQLite and pooling problem

[Larry Brasfield]

Greg Carter, on Fri Apr 27, wrote:

We recently upgraded to 1.0.80.0 from 1.0.77.0 and have found when we turn
connection pooling on we get seemingly random memory access violations that
happen in calls to UnsafeNativeMethods.sqlite3_busy_timeout ,call stack -
Open/SetTimeout (line 259 of SQLite3.cs).

After compiling SQLite debug what I have seen is that if the
SQLiteConnection object was disposed of by the garbage collector then the
object left in the pool has an invalid _sql pointer.  If the
SQLiteConnection is disposed explicitly such as at the end of a using
statement everything seems to work.


[Valiant and likely perspicacious diagnostic gleanings cut for space.]


... then fails with memory
access violation as the _sql pointer is no longer valid.

In the cases where everything works as it should I see a clear stack trace
back to my code where the connection is disposed of at the end of a using
when it is added to the pool.  It's when we have connections that are
disposed of as a result of garbage collection that things go wrong.


When you use a collection of native code from managed code, and when 
that native code has cleanup to do, defined by its own conventions 
rather than the conventions established for the managed code system, you 
cannot count on garbage collection to get the job done.  In addition to 
memory allocations, there are likely to be on-disk data and files that 
need to be brought to a proper state when SQLite's designed close/exit 
mechanisms are invoked.  You "works as it should" is only assured 
(inasmuch as any software can) when you use the .Net framework's assured 
disposition mechanism ("using ..." and properly implemented IDispose) or 
effect the equivalent in your own code.



So is this intended behaviour or a bug?  I can try to come up with a test
case that reproduces this if needed.


It's not really intended behavior but intent cannot cure this problem 
unless SQLite's finalizing API is used.



Thanks
Greg.

...
Best regards,
--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Update problem with Sqlite (one PC one single operation) Vb.net

[Larry Brasfield]

On April 8, Marco wrote:

OpenConnection(TMP_ConnectionString, OBJ_Connection)

sql= "UPDATE ANG_Operatori Set NomeOperatore = 'Amministratore',UserID =
'admin',ParolaChiave = 'prova',DataUltimoAccesso = '08/04/2012
15:04:00',Supervisore = 1,ID_Dentista = 1 where ID_ANG_Operatore = 1"


 Execute("BEGIN TRANSACTION")   '''''''''''''''''''''("BEGIN EXCLUSIVE
TRANSACTION")
 Execute(SQL)
 Execute("END TRANSACTION")

//


According to http://www.sqlite.org/lang_transaction.html , "An attempt 
to invoke the BEGIN command within a transaction will fail with an 
error, regardless of whether the transaction was started by SAVEPOINT or 
a prior BEGIN."


That alone would be enough to suggest the above code should be avoided. 
 The SQLite3 shell throws an error when given the second "BEGIN ..." 
command.



With this code the db don't save.


Are you checking for error returns?  (I would guess not.  In that case, 
your first fix should be to check every single call into SQLite that can 
return an error.)



If I add 'EXCLUSIVE' the program stop after 30 seconds and return "The
database file is locked"


Probably the first transaction is still in effect.


If I remove the transaction some result.


Show us the code that fails.  It's alright to cut out what is truly 
irrelevant, but please do not submit pseudocode.



With MS access this query work without need to insert transaction.


I see no queries here at all, so I cannot judge what "this query work" 
means.



How I can solve this problem ?


Too little information to guess.

--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In-memory and temporary database

[Larry Brasfield]

On March 28, Simone P. Lossani wrote:


Hello,
   I'm doing some test with SQlite 3.7.11 in Windows Embedded industrial PC.
I need to store data in a table very quickly. Let's say new data each 10 msec.
I tryed with DB on disk but obviously was not possible to reach the correct 
performance.
So I tryed with in-memory DB and in this case the performance results are good.
The problem is that I can't share the in-memory database with other 
applications.
I red a lot of article and documentation but I have not solved the problem.
Maybe with temporary DB I can have the same performance and also I can share DB?
Have you some suggestion?


There are a couple approaches that meet the constraints stated so far.

It would help, when trying to find a viable approach, to know what 
characteristics the solution must have.  Does it have to be robust 
against database corruption due to power loss?  On what schedule does 
the database sharing occur?  What delay is acceptable between when data 
is put into the fast database and when it is available in the shared 
database?


I would look at using the Windows memory-mapped file API.  This would be 
exposed to SQLite using its virtual filesystem mechanism.


An alternative is to keep two databases in sync.  One would be the 
in-memory database.  The other would be the one you share, kept in an 
ordinary file.  They would be kept in sync by one of several means: 
Triggers on table inserts or updates to replicate the data; parallel 
updates or inserts at the prepare/execute level; or periodic 
whole-database copying.


If there can be some delay between when data is to be stored and when it 
is available to consumers, you might just do the database modifications 
in a separate thread.  (I am suggesting that "I need to store data in a 
table very quickly." might mean only "I need the thread which accepts 
the data for storage to be quickly available for other work.")


Regards,
--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQL or C for data analysis?

[Larry Brasfield]

Hi there,
  I'm about to start a project I have been thinking about for a long
while.  I basically wish to analyse stock market data.  I already have the
data in a table and I'm now in the process of writing my own indicators and
oscillators.  I hope to learn while re-inventing this wheel and perhaps
explore different things unavailable in most stocks analysis software I
found.

  I would love to do an sql query that would look like the following ones.
I wonder if they are possible and valid applications for SQL and what would
be the proper implementation for these.  I know I can calculate all this
using C, but it would be most useful (to my later projects) to do it in SQL
directly (my last example gives you an idea where I'm going).

select max(opening_price, closing_price, high_price) - min(opening_price,
closing_price, low_price) as day_range...
select closing_price, moving_average(20,closing_price),
exp_mov_avg(20,closing_price)...
select closing_price, moving_average( funky_oscillator( closing_price ) )...

I think creating a module and using virtual tables may be an idea...  but
doing the above selects would involve some serious twists I think.  What
I'd need are virtual functions or something like that...


A DBMS is a good way to keep your raw data.  But I highly doubt that a 
majority of your analysis algorithms are going to be expressible in SQL 
without going way beyond the intended purpose of the language.  You will 
either find yourself limiting the analyses to what is convenient to 
express in SQL, or you will spend much more time writing queries than 
you would spend describing your data processing in a form more suited to 
functions.  SQL is primarily a language for extracting sets from other 
sets, according to defined criteria (which include set relationships). 
Your analyses, unless they are like nothing I've ever seen or imagined, 
are going to be derived from functions on time series rather than sets, 
per se.  I expect you would find a signal processing library, such as 
can be found in Matlab, Octave, or Scilab, to be a much better start 
than what you might write in SQL in reasonable time.


That said, it is not hard to imagine that selection of datasets might be 
done with SQL, and perhaps some query criteria might include measures 
derived from your own custom functions on time series.



Can you guys confirm with me whether I'm asking too much and I should
concentrate on a C or C++ implementation of my functions, or is there a way
to make it happen in sqlite?


There is no real either/or choice posed here.  You can create your own 
custom functions to be incorporated into SQLite queries, including 
aggregate functions.  The aggregate functions might produce some output 
other than what is returned to SQLite.  What I think will be 
unreasonable or unduly limiting is making it happen in bare SQLite.  You 
will need more.



Thanks,
  Simon

Have fun!
--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to export to html file with table

[Larry Brasfield]

On March 26, YAN HONG YE wrote:
(apparently quoting Steinar Midtskogen, quotation demarked)

> to export to the html file  command is:


sqlite3 -html film.db "select * from film" > mm.html


but the result had no table,only a text file without line feed, how to export 
the html with tables and table column header?


The output in the SQLite shell's html mode is not a valid instance of 
HTML.  It is something that can be embedded in a valid HTML document. 
To use that output, you will have to have all the usual HTML opening and 
closing constructs.  You will need to bone up on HTML, or do some study 
of HTML instances that have tables in them, and figure out how to put a 
sqlite shell generated table into one which otherwise says what you 
want.  (For example, tables usually have a title, or something 
explaining what they are.)

--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Handle multiple results using sqlite3_step

[Larry Brasfield]

On Mar 25, Neo Anderson wrote:

Do not be confused by the sample SQL statement. What I really wanted to do is 
return two different resultsets in one statement and wanted an API to handle 
the two resultsets. For example:

select * form table1;
select * from table2;

And I do not want to use big union.

By reading other replies I think it's actually not practical to do this.

...

> From: itandetnik at mvps.org

...

> Why do you want two resultsets? What is the actual problem you are trying to 
solve?


I think it would be good to answer Igor's question.  You have only 
shared the low-level "solutions" you thought might work and now think 
impractical.


It would help those who might help to understand what use of a union 
fails to accomplish that your separate queries would accomplish.  I'm 
having a hard time imagining it, and my guesses are so arcane that it 
seems silly to engage in such when you have already know.


To break it down a bit:  What will you do with these two result sets? 
How does combining the queries, one after the other, yield something 
usefully different from a union?  Why not prepare one query after 
another, or at least execute one then the other?


At this point, I doubt anybody can help without better information as to 
what the objective is.

--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] question about zipvfs & performance

[Larry Brasfield]

On March 25, Tal Tabakman wrote:

Hi,
I am writing an application that performs a lot of DB writes. I am using a
lot of recommended optimizations (like using transactions and more...)
I want to improve my recording time by reducing the amount of I/O. one way
to do so is by compressing the data before dumping it to DISK.
I am evaluating a sqlite extension called zipvfs. this VFS extension
compresses pages before writing them to disk
I am using zlib compress/uncompress as my compression callback functions
for this VFS. I assumed that database writing will  be faster with this VFS
since
compression [means less I/O], in reality I see no difference (but the data
is indeed compressed)...
any idea why I don't see any recording time improvement ? is there an
overhead with zipvfs ?  any other recommended compression callback
functions ?


I suspect you are seeing the effect of SQLite's choice of block size on 
disk.  If, for some reason, you application would work better with much 
larger block sizes than SQLite uses by default, you might see an 
improvement with on-the-fly compression/decompression, (provided CPU 
time does not take back the I/O time saving).  The compression schemes 
generally have some overhead, creating and writing out a dictionary in 
ZLib's case.  You are likely not reducing the number of I/O operations, 
but just reducing their size a little.  With disk caching and read-ahead 
working pretty well for disk sectors that are contiguous, the effect is 
bound to be small with block size already resembling the cluster size.


You are tackling a problem that has already been subject to a lot of 
optimization effort.  Further improvement is likely to be difficult 
unless there is something unusual about the access pattern of your 
application.



cheers
Tal


Good luck.
--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Could/should Windows build of SQLite use #define

[Larry Brasfield]

On March 24, Roger Binns wrote:


[I am the author of APSW]


Thanks for that, BTW.


On 24/03/12 14:22, Larry Brasfield wrote:
> Except for its clever hiding of SQLite's C API names (via "#define
> SQLITE_API static"),

There is nothing "clever" about it - SQLite exposes various things like
that so compilation can be controlled.  Examples are calling convention
and visibility.


Please understand that I did not mean "clever" in a denigrative way.  I 
saw the inclusion of sqlite3.c into apsw.c as a clever way to hide a set 
of names.  The mechanism is not such a leap.



> there is no particular reason that apsw.c needed to expose so many
> different name sources to each other.

Believe it or not, I am not insane.  APSW actually supports static
including of the amalgamation, and static and dynamic linking.


For the record, I believed you were sane shortly after starting to read 
this list and have had no cause to suspect otherwise.



Static amalgamation has two major benefits.  The first is performance -
because everything is a single translation unit the compiler does a lot of
inlining.  The last time I measured the benefit a few years back it was
around 10% compared to static linking.


Please forgive a bit of skepticism here.  Do you mean that the 
amalgamation of sqlite3.c into apsw.c provides a 10% speed advantage? 
Or is your claim that SQLite itself enjoys this benefit?  The former 
seems unlikely to me, while the latter is quite believable.



The second has to do with visibility of symbols.  When doing the static
amalgamation exactly one symbol is visible - the shared library entry
point for the Python extension.

On some platforms, Mac especially, there will be other copies of SQLite
loaded into the process.  (Mac is especially evil and will override your
directions and use system SQLite no matter what.)  If your shared library
exposes any SQLite related symbols which could happen with static or
dynamic linking then it is a crapshot as to how all the pieces of code
using SQLite interact with the various SQLite's available.


I had no idea that such linkages could reach across DLL boundaries. 
Does not an unsatisfied reference refer to a combination of DLL and 
entry point?  If they can be satisfied by entry point name alone, I 
would expect to see some severe naming conventions recommended and 
perhaps enforced.  (Either that, or a continuing series of strange 
incompatibilities.)



Consequently the single best thing to do is include the SQLite
amalgamation statically as it is far more robust.


So, the choice is between courting compile-time name collisions and 
risking run-time name collisions.  With a Hobson's choice like that, the 
result will certainly be more robust, once the build succeeds.



> Well, you can inject a -DWINDOWS_LEAN_AND_MEAN into the compiler
> invocation for just that source, avoiding the need to modify its text.

Or I'll just modify APSW for the next release.  The issue actually only
happens for some combinations of the Microsoft dev tools and sdks.  It
doesn't happen on the combination I use.  (Use is also constrained for
binary compatibility reasons.)


FYI, I'm having difficulty getting windows.h from the v7.0A Windows SDK 
and the VS10 Express edition of the standard C headers to coexist with 
the headers in Python 2.7.  In particular, I cannot compile apsw.c 
whether WINDOWS_LEAN_AND_MEAN is defined or not.  (I am not asking for 
help with this.  I just imagine you could be interested.)



>> A shame to have to deal with this bad behavior from Windows.h...

Indeed.


As I stated, windows.h violated one of the conventions we use to reduce 
the problem of name collision.  But it is a problem that exists whenever 
huge collections of definitions, in the same namespace, are brought 
together.  SQLite does a respectable job of mitigating this by 
restricting its exposed names via prefixing.


Cheers,
--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Could/should Windows build of SQLite use #define

[Larry Brasfield]

On March 23, Jeff Robbins wrote:

While #define WINDOWS_LEAN_AND_MEAN is only a compile-time setting, it trims 
down extraneous #defines that Windows.h creates that can interfere with 
reasonable uses of SQLite, such as the excellent APSW extension for Python:

The problem I happened upon was trying to build a Python extension called APSW 
( http://code.google.com/p/apsw/ )

This extension includes the SQLite amalgamation, which in turn includes 
Windows.h.

By not defining WINDOWS_LEAN_AND_MEAN, Windows.h includes RpcNdr.h which 
(sadly) does a #define small char.


It is generally recognized that the C (or C++) preprocessor makes this 
sort of problem hard to systematically avoid.  And it is rude for that 
header to flout the widely observed convention that symbols subjected to 
#define are in uppercase.



And that #define in turn collides with a Python.h header (accu.h for those 
still awake!).

It isn't SQLite's fault or problem, but if the WINDOWS_LEAN_AND_MEAN approach 
works for SQLite, why not use it, if only out of parsimony (Occam's razor etc)? 
It would thereby reduce potential build conflicts when embedding SQLite into 
other apps or libraries.


It's a good practice, I suppose.  Of course, where the amalgamation is 
treated as a separate translation unit, this issue does not arise.  It 
is apsw.c's combination of the amalgamation, with its #include 
, and another very inclusive header, Python.h, which has set 
the stage for this.


Except for its clever hiding of SQLite's C API names (via "#define 
SQLITE_API static"), there is no particular reason that apsw.c needed to 
expose so many different name sources to each other.



My current workaround is to #define WINDOWS_LEAN_AND_MEAN prior to including 
the amalgamation, but that required me changing an APSW file (apsw.c). I'm in 
communication with the author of APSW as well on this, and with the Python team.


Well, you can inject a -DWINDOWS_LEAN_AND_MEAN into the compiler 
invocation for just that source, avoiding the need to modify its text.



A shame to have to deal with this bad behavior from Windows.h...


Hmmm.  It's not clear that the blame belongs solely to Windows.h or its 
numerous subsidiary headers.  This is a C problem, one which led to the 
introduction of namespaces in C++, and one which leads developers to 
prefer smaller translation units.


Nevertheless, it would help head off such issues to #include no more 
than is necessary, and a "#define WINDOWS_LEAN_AND_MEAN" would get 
partway there.


--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users