[sqlite] System.Data.SQLite for Pocket PC & RTREE

[Green Fields]

Hi

I hope this message doesn't double up but the last one did not appear to
make it.

 

I'm new to the list and newish to SQLite and would appreciate some tips.

I'm attempting to create an application that requires a spatial rtree query,
and this works extremely well using the x86 version of the
System.Data.SQLite library
(sqlite-netFx40-static-binary-bundle-Win32-2010-1.0.94.0.zip). However, when
I attempt to run the same query using
(sqlite-netFx35-binary-PocketPC-ARM-2008-1.0.94.0.zip) in a pocketPC port,
the app crashes, and I have been unable to get any feedback from the debug
because the connection to the device is broken. Standard SQL queries and
sqlite_version() work fine.

 

I'm pretty sure rtree would be enabled in the cf binary looking at the
source config, but I don't know of a way to check for this in the compiled
binary short of running an rtree query.

 

I am new to debugging on mobile devices so there may be a way to log the
error that I'm not aware of, but so far all attempts to trap the error have
failed.

 

Does anyone have any suggestions where the problem might lie?

 

Thanks for any help

 

Duncan

 

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

Re: [sqlite] sqlite3 tool bug

[Scott Robison]

On Jan 17, 2015 7:29 PM, "Dave Dyer" 
> Here in the real world, when everything is working, we ask "why upgrade".

But it wasn't working correctly so the statement doesn't really answer the
question asked. :)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 tool bug

[Dave Dyer]

>
>The  has been in shell.c since 3.8.6.  We are on 3.8.8.  Why
>not upgrade?
>-- 

Here in the real world, when everything is working, we ask "why upgrade".

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

[sqlite] sqlite3 tool bug

[Dave Dyer]

>
>The  has been in shell.c since 3.8.6.  We are on 3.8.8.  Why
>not upgrade?
>-- 

Here in the real world, when everything is working, we ask "why upgrade".

Re: [sqlite] sqlite3 tool bug

[Richard Hipp]

On 1/17/15, Dave Dyer  wrote:
>
>>
>>
>>OK.  Dave, please try this patch at let us know if it works better for
>>you:  https://www.sqlite.org/src/info/80541e8b94b7
>>
>
> It needs #include  to compile in my sources.
> With that, it seems to fix the problem.
>

The  has been in shell.c since 3.8.6.  We are on 3.8.8.  Why
not upgrade?
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] System.Data.SQLite - Exception Calling SQLiteModule.DeclareTable

[Mike Nicolino]

I'm getting an exception calling SQLiteModule.DeclareTable that seems to imply 
the 'create table' sql being passed is invalid: "SQL logic error or missing 
database".  Yet using that same sql on the same connection as a create table 
call succeeds.  Reviewing the virtual table docs don't imply there are 
restrictions on the create table sql for virtual tables so I'm at a loss to 
what's wrong.

The create table sql (the line breaks here are for readability and not present 
in the actual string send to DeclareTable):

create table xxx(
"Username" text,
"DisplayName" text,
"Email" text,
"LastLogin" integer,
"LastInvite" integer,
"Status" text,
"SourceDs" text,
"Data" text,
"SourceDsLocalized" text
)

Anyone have any input on what might be wrong?
Thanks!

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

Re: [sqlite] sqlite3 tool bug

[Dave Dyer]

>
>
>OK.  Dave, please try this patch at let us know if it works better for
>you:  https://www.sqlite.org/src/info/80541e8b94b7
>

It needs #include  to compile in my sources.
With that, it seems to fix the problem.

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

[sqlite] sqlite3 tool bug

[Dave Dyer]

>
>
>OK.  Dave, please try this patch at let us know if it works better for
>you:  https://www.sqlite.org/src/info/80541e8b94b7
>

It needs #include  to compile in my sources.
With that, it seems to fix the problem.

Re: [sqlite] sqlite3 tool bug

[Richard Hipp]

On 1/17/15, Roger Binns  wrote:
>>
> The bug in the SQLite shell is that it tries to manage the encoding
> itself, which is fine if the file is in binary mode.  But with
> stdin/out in text mode doing so will lead to extra data mangling.  The
> shell needs to change stdin/out to binary mode:
>

OK.  Dave, please try this patch at let us know if it works better for
you:  https://www.sqlite.org/src/info/80541e8b94b7


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

Re: [sqlite] sqlite3 tool bug

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 01/17/2015 12:20 PM, Graham Holden wrote:
> I would echo this: it's good at mangling the command-line, but
> I've not been aware of it ever mangling data sent to a file/stream
> (other than the binary/text mode conversions).

In text mode (the default for stdin/out on Windows) control Z (ascii
26) is also considered end of file.

Microsoft don't document everything that is done to text streams, but
there is likely to also be some conversions for the current code page
and MBCS.  Here for example is what is done with stdio when using the
wide character methods:

  http://msdn.microsoft.com/en-us/library/c4cy2b8e.aspx

The bug in the SQLite shell is that it tries to manage the encoding
itself, which is fine if the file is in binary mode.  But with
stdin/out in text mode doing so will lead to extra data mangling.  The
shell needs to change stdin/out to binary mode:

  http://msdn.microsoft.com/en-us/library/tw4k6df8.aspx

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlS68dsACgkQmOOfHg372QSWRQCfeBr7J/p0VhqsDwRAhDcSDq3d
MuYAoLG9R5Z3DiEHQgYTY/Ulpu7ilgIi
=/lr/
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Best Practice: Storing Dates

[Stephan Buchert]

>
> This is also fast: SELECT * FROM data WHERE tstr BETWEEN tbegstr AND
> tendstr; And it works just as well if dates are in the ISO8601 format.


Aha, yes, thanks, this is certainly a better SELECT than converting string
data to numeric Julian Days. And the string can have as much resolution as
needed and represent times in leap seconds (with "60" in the seconds field).

I still see potential advantages with the numeric (day segmented) timestamp
regarding disk storage, e.g. my data are continuously over 1-2 years with
sub-second sampling. Using time strings I would need all 23 bytes allowed
in the Sqlite date and time functions.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 shell in windows

[James K. Lowden]

On Fri, 16 Jan 2015 14:31:40 -0800
Random Coder  wrote:

> If you're seeing the "Error: The specified procedure could not be
> found." error, and you're not specifying an entry point in the .load
> command, then no doubt the sqlite3_load_extension symbol isn't
> properly exported.  I'd verify that your DLL has this symbol exported
> using a tool like depends (http://www.dependencywalker.com/).  I'm
> guessing you're missing an entry in your .def file, or a similar
> location.

Bingo, and thanks for the pointer.   I forgot that DLL symbols have to
be exported explicitly.  Up until now my module was compiled only for
NetBSD, and lacks any dllexport notation.  

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

Re: [sqlite] sqlite3 shell in windows

[James K. Lowden]

On Fri, 16 Jan 2015 16:24:21 -0700
"Keith Medcalf"  wrote:

> >1.  The architecture of an executable file, x86 or x64.
> 
> dumpbin -- comes with the dev kit

I would have thought so, but I didn't find an option that reports it.  

> Importantly make sure you are exporting "C" names.  

I'm pretty sure, but thanks for the reminder.  IIRC files ending in .c
are compiled as C by default.  Definitely I forgot an explicit export.  

> Telling you the name of the service and the name of the file (and
> where it expected to find it) would be useful, but I think rule 1
> prevails "Everything Useful is Prohibited".  

Don't I know it.  Amen, brother, say it!  

> The compiler is called CL and it can give you its help with "cl -?".
> There is a silly batch file somewhere in the VS install directories
> that set all the environment variables properly so you can then use a
> real editor and compile from the command line.

So silly it is that when I choose the "x86 command-line" option in VS,
it doesn't DTRT.  I had to reverse engineer which vsvars32.bat to run.  

I decided to give http://mxe.cc a try.  News at 11.  

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

Re: [sqlite] sqlite3 tool bug summary

[Dave Dyer]

>
> .once '| sqlite3 new.db'
> .dump

.Once is not a command in the version of sqlite3 I use.

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

[sqlite] sqlite3 tool bug summary

[Dave Dyer]

>
> .once '| sqlite3 new.db'
> .dump

.Once is not a command in the version of sqlite3 I use.

Re: [sqlite] Best Practice: Storing Dates

[Richard Hipp]

On 1/17/15, Stephan Buchert  wrote:
> If selecting rows according to a date/timestamp is ever needed, numeric
> time stamps are clearly advantageous, e.g.
>
> SELECT * FROM data WHERE t BETWEEN julianday(tbegstr) AND
> julianday(tendstr);
>
> is much more efficient than
>
> SELECT * FROM data WHERE julianday(tstr) BETWEEN julianday(tbegstr) AND
> julianday(tendstr);
>

This is also fast:

   SELECT * FROM data WHERE tstr BETWEEN tbegstr AND tendstr;

And it works just as well if dates are in the ISO8601 format.

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

[sqlite] Best Practice: Storing Dates

[Stephan Buchert]

If selecting rows according to a date/timestamp is ever needed, numeric
time stamps are clearly advantageous, e.g.

SELECT * FROM data WHERE t BETWEEN julianday(tbegstr) AND
julianday(tendstr);

is much more efficient than

SELECT * FROM data WHERE julianday(tstr) BETWEEN julianday(tbegstr) AND
julianday(tendstr);

particularly if there is an index on t in the first SELECT;

Sqlite's date and time functions support Julian Day, which is in some
circumstances not the best:

1) With 64 bit floats the resolution is about 1 millisecond which is not
sufficient for some real life technical data.

2) It cannot handle leap seconds, such as the one that will be inserted on
June 30, 2015.

An alternative is a "day segmented time code", e. g.

CREATE TABLE timestamped_data (
   day2000 INTEGER,  --nr of days since Jan 1,2000, 0 UTC
   msec INTEGER,  --nr of milliseconds in day
   usec INTEGER,  --microseconds in msec
...
)
-- to speed up searches in time:
CREATE INDEX ON  timestamped_data (day2000,msec,usec);

day2000 can be stored in 16 bit for contemporary data; usec is optional, 16
bit would be enough, msec of course in 32 bit.

I have an Sqlite extension cds2datestr(day2000,msec,usec) returning a human
readable format (only -MM-ddThh:mm:ss.sss supported) which I would be
happy to share.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 tool bug summary

[Richard Hipp]

On 1/17/15, Dave Dyer  wrote:
>
>>
>>> But that doesn't explain the difference between redirecting to a file
>>> and redirecting to a pipe.
> using .output file works
> using > to direct stdout to a file works and produces the same file as
> .output
> using .read file works
> using < file does not work.
> using | to shortcut > and < doesn't work.

Thanks for the excellent summary.

But what about this case:

 .once '| sqlite3 new.db'
 .dump


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

Re: [sqlite] sqlite3 tool bug summary

[Dave Dyer]

>
>> But that doesn't explain the difference between redirecting to a file
>> and redirecting to a pipe.
using .output file works
using > to direct stdout to a file works and produces the same file as .output
using .read file works
using < file does not work.
using | to shortcut > and < doesn't work.


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

[sqlite] sqlite3 tool bug summary

[Dave Dyer]

>
>> But that doesn't explain the difference between redirecting to a file
>> and redirecting to a pipe.
using .output file works
using > to direct stdout to a file works and produces the same file as .output
using .read file works
using < file does not work.
using | to shortcut > and < doesn't work.

Re: [sqlite] sqlite3 tool bug

[Graham Holden]

> I'm skeptical of the notion that cmd.exe is diddling with your data en
> route to the pipe.  I can't think of a time Windows munged my data in
> that particular way despite more years using that lousy tool than I
> care to remember.  Quotes and escapes, sure, don't get me started.

I would echo this: it's good at mangling the command-line, but I've
not been aware of it ever mangling data sent to a file/stream (other
than the binary/text mode conversions).

> Shot in the dark: maybe a string is being continued by ending the
> line with a backslash.  If the output handle is opened as text with
> fopen, the sequence would be
> 5c 0d 0a
> which the escape-reader wouldn't recognize, expecting only
> 5c 0a
> leading to a noncontinued, incomplete line.

I don't believe this would be the case, if both stdin and stdout have
been left in "text" mode: '5c 0a' sent to stdout would be converted
into '5c 0d 0a' but when this was read in "text" mode it would get
converted back to '5c 0a' which would be what the "escape-reader" of
SQLite would see.

> But that doesn't explain the difference between redirecting to a file
> and redirecting to a pipe.

Even if the data coming from the first command contained characters
that "confused" the "text" mode of Windows, I would expect things to
work or fail the same whether the output's being piped or sent to a
file.

My "shot in the dark" would be that some buffer-size limit in the
piping process is being exceeded.

Graham Holden



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

Re: [sqlite] Database corrupted 28 billion bytes

[Richard Hipp]

On 1/16/15, MayW  wrote:
> Pages on the freelist (per header) 2144 0.008%
> Pages on the freelist (calculated) 3344382 12.1%

The fact that the freelist size as reported by the header is different
from the actual freelist size is troubling.  A VACUUM should clear the
problem.  But I wish I understood how the problem arose in the first
place

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

Re: [sqlite] sqlite3 tool bug

[Dave Dyer]

>
>Not, at least, when your database contains string data with unusual
>characters that Windows feels like it should translate for you...

Who can guarantee what characters are used in all their text strings,
much less guarantee what unnamed transformations windows is helpfully
doing to pipe data.  

Saying "it probably will work" is not very satisfactory.

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

Re: [sqlite] sqlite3 tool bug

[Dave Dyer]

I have a class of database for which using sqlite3 to create
a copy via the "pipe" method fails.   Using an explicit intermediate
file seems to work ok.

I can supply a sample database to anyone interested in investigating.

--

F:\2013 YearTech\Yearbook Tools\Resource>sqlite3 -version
3.7.3

F:\2013 YearTech\Yearbook Tools\Resource>sqlite3 po.sqlite .dump | sqlite3 
po2.sqlite
Error: incomplete SQL: INSERT INTO "imageblob" VALUES(1,'G:\share\e ...

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

Re: [sqlite] Database corrupted 28 billion bytes

[MayW]

Thank you very much!

/** Disk-Space Utilization Report For junk.db3

Page size in bytes 1024  
Pages in the whole file (measured) 27606264  
Pages in the whole file (calculated).. 24264026  
Pages that store data. 2426188287.9% 
Pages on the freelist (per header) 2144 0.008% 
Pages on the freelist (calculated) 3344382 12.1% 
Pages of auto-vacuum overhead. 00.0% 
Number of tables in the database.. 5 
Number of indices. 1 
Number of defined indices. 1 
Number of implied indices. 0 
Size of the file in bytes. 28268814336
Bytes of user payload stored.. 18312217251  64.8% 

*** Page counts for all tables with their indices
*

MYTABLE... 2426058787.9% 
PARTS. 1292 0.005% 
QUERY. 10.0% 
SQLITE_MASTER. 10.0% 
SQLITE_SEQUENCE... 10.0% 

*** Page counts for all tables and indices separately
*

MYTABLE... 2426058787.9% 
PARTS. 1116 0.004% 
MANUF. 176  0.0% 
QUERY. 10.0% 
SQLITE_MASTER. 10.0% 
SQLITE_SEQUENCE... 10.0% 

*** All tables and indices


Percentage of total database..  87.9%
Number of entries. 704297145 
Bytes of storage consumed. 24844167168
Bytes of payload.. 18312362684  73.7% 
Average payload per entry. 26.00 
Average unused bytes per entry 1.02  
Average fanout 84.00 
Maximum payload per entry. 426   
Entries that use overflow. 00.0% 
Index pages used.. 285997
Primary pages used 23975885  
Overflow pages used... 0 
Total pages used.. 24261882  
Unused bytes on index pages... 3471326711.9% 
Unused bytes on primary pages. 6833870342.8% 
Unused bytes on overflow pages 0 
Unused bytes on all pages. 7181003012.9% 

*** All tables


Percentage of total database..  87.9%
Number of entries. 704287145 
Bytes of storage consumed. 24843986944
Bytes of payload.. 18312217804  73.7% 
Average payload per entry. 26.00 
Average unused bytes per entry 1.02  
Average fanout 84.00 
Maximum payload per entry. 426   
Entries that use overflow. 00.0% 
Index pages used.. 285997
Primary pages used 23975709  
Overflow pages used... 0 
Total pages used.. 24261706  
Unused bytes on index pages... 3471326711.9% 
Unused bytes on primary pages. 6833837982.8% 
Unused bytes on overflow pages 0 
Unused bytes on all pages. 7180970652.9% 

*** All indices
***

Percentage of total database..   0.0%
Number of entries. 1 
Bytes of storage consumed. 180224
Bytes of payload.. 144880  80.4% 
Average payload per entry. 14.49 
Average unused bytes per entry 0.32  
Maximum payload per entry. 35
Entries that use overflow. 00.0% 
Primary pages used 176   
Overflow pages used... 0 
Total pages used.. 176