Re: [sqlite] Frequent database corruptions on Windows 10

[William Drago]

On 9/10/2016 1:16 PM, Olivier Mascia wrote:

Le 10 sept. 2016 à 11:21, Alexander Täschner 
 a écrit :

since upgrading to Windows 10 I have trouble with several different C#
programs I wrote, that uses System.Data.SQLite to access sqlite
database files

...

The programs are using multiple threads sharing one SQLiteConnection
per process. I use lock statements to prevent the different threads from
accessing this connection object and all derived SQLiteCommand
objects simultaneously.

One connection per process, shared between threads is calling for needless 
complications (your efforts to prevent different threads from using this 
connection object simultaneously, for instance).

I would first refactor this a little bit, in order for each thread to use their 
own connection, not sharing any of these, nor any of the descendant objects 
from those connections. It is very simple to do and you will get rid of all 
that code to handle mutual exclusion.  In essence, you program each thread as 
if they were a distinct process (regarding SQLite).   It *might% fix your 
problem or help you find where it exactly is.  And if you're using WAL mode, it 
will bring you some level of true read-concurrency.


In your current model, you should make sure that SQLite library is set for:

   sqlite3_config(SQLITE_CONFIG_SERIALIZED);// SQLite enforces a mutual 
exclusion of threads


And in the 'each thread has its own connection(s)' model which I suggest, you 
could downgrade that to:

   sqlite3_config(SQLITE_CONFIG_MULTITHREAD);   // SQLite does NO attempt to 
isolate threads (because you do
// by giving a distinct 
connection to each and every thread)

I don't know what the default it with System.Data.SQLite.


Default is Serialized for SQLite and System.Data.SQLite, and 
it's probably best to leave it at that even when each thread 
has it's own connection. More info: 
https://sqlite.org/threadsafe.html


I'd do as Olivier suggests and refactor the code a little. 
Welcome to Windows 10 ;)


-Bill

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

Re: [sqlite] How to use "cursors" in c#

[William Drago]

I am programming in c# and I have to insert into a .sqlite file a new row
and get back the id of this last inserted row. This file "unfortunately"
might be located on an internal network and other users might try to add
other rows in the meantime.



I can't help with your locking issues, but if you are using 
the System.Data.SQLite ADO.NET provider in your C# program 
you can get the ID of the last row inserted with the 
LastInsertRowID property of your connection like this:



long rowID = connection.LastInsertRowId;

I use it after I commit the transaction.


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

Re: [sqlite] Messages posted on Nabble not getting to list

[William Drago]

On 5/27/2016 1:23 AM, Jean-Christophe Deschamps wrote:


At 03:46 27/05/2016, you wrote:
If SQLite goes this route, I will probably (as with the 
others) stop reading it too.


Seconded.


Thirded.

I have mostly given up on the email lists that have 
converted to forums. There are two left that I bother with 
and I rarely even check those anymore. In my experience if 
there's one sure fire way to kill a vibrant user community 
it's to replace their email reflector with a forum.


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

Re: [sqlite] What could be the cause of a zero length database file?

[William Drago]

The sequence is:
* open a new database file
* issue a sequence of several SQL commands in a single transaction (one
BEGIN, one COMMIT at the end).

The database ends up as a zero length file. Is this
a) a normal consequence of the above if the single transaction is aborted
(say due to an SQL error)
This happens to me from time to time and in my case it is 
due to scenario a.


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

[sqlite] How to get 1 row with no null columns

[William Drago]

All,

Is there a simple way to find a row in a table where none of 
columns contain a null value? For example:

SELECT * FROM AnyTable WHERE (all columns IS NOT NULL) LIMIT 1;


Or do I have to do this manually in my application scanning 
every column in every row until I find a row with no nulls?

Thanks,
-Bill

[sqlite] Pascal (almost) style BLOBs

[William Drago]

On 4/18/2016 6:46 AM, Paul Sanderson wrote:
> You could use a binary PList or some other form of structured data -
> the BPList blob could then contain, ints, floats, strings byte arrays
> or even embedded binary plists. As it is a structured format then to
> an extent it is self documenting (in terms of the stored data types)
> Paul

Of course! I don't have to be that complicated though, do I? 
I've pretty well convinced myself that adding a type 
indicator byte to the blob will provide all the information 
I need to unblob it properly. I haven't heard any major 
objections from the list here, so I'm going try it out.

Thanks for the suggestion,
-Bill

[sqlite] Pascal (almost) style BLOBs

[William Drago]

On 4/17/2016 10:13 AM, Brad Stiles wrote:
> An ADO.NET DataTable *can* have type information for table columns, if you 
> add a parameter to the WriteXml method to have it write the schema along with 
> the data. Obviously, *their* data must be typed from their source, or be 
> inferred, for this to work.

If the DataTable is the result of a SELECT that involves 
more than one table is the schema information included for 
each of the tables?

> See if your provider can do this for you. If they can, you should have little 
> problem reading it, assuming you're not using .Net yourself.

I'm confused by your statement. I am using .NET, that's why 
I'm dealing with ADO.NET DataTables.

Thanks,
-Bill

[sqlite] Pascal (almost) style BLOBs

[William Drago]

On 4/17/2016 9:45 AM, Simon Slavin wrote:
> On 17 Apr 2016, at 1:02pm, William Drago  wrote:
>
>> I am forced to consider this approach because my function will be receiving 
>> blobs but will have no idea what those blobs are.
> Why do you care ?  A BLOB is a BLOB.  It doesn't matter whether it's an 
> integer, a string, a car, a peace treaty or the smell of newly cut grass.  
> You store it when told to and retrieve it when told to.
My job is to find all the blob columns and unblob them into 
arrays of whatever type they originally were before they 
were blob'd.
>> if this "feature" is not clearly documented someone in the future will have 
>> a very hard time figuring out why the blobs don't make sense.
> BLOBs don't make sense.  They're opaque.  You are not meant to understand 
> anything about what's in a BLOB.  That's for the layer of your software which 
> packs the information into a blob or unpacks the blob back into information.
Understood. I'm writing the code that does the packing and 
unpacking. The users are designing their own databases. When 
they store for example, an array of 16 bit integers, or an 
array of 64 bit floats, I want to give them the same thing 
back when they query the database not just an array of bytes 
that they have to decode themselves.

Since I have control over how the blobs are generated, I can 
easily add a type identifier to each blob so that I know how 
to unblob it.

-Bill

[sqlite] Pascal (almost) style BLOBs

[William Drago]

On 4/17/2016 9:20 AM, Ketil Froyn wrote:
> I'm not sure I understand why you can't specify the type in a separate
> column, rather than prepending it to the blob? That seems like a more
> flexible way to have access to the information.
I have no control over the database schema, table structure, 
etc.. I also have no control of the select statement that is 
generating that data.

-Bill

[sqlite] Pascal (almost) style BLOBs

[William Drago]

On 4/17/2016 9:45 AM, Simon Slavin wrote:
> On 17 Apr 2016, at 1:02pm, William Drago  wrote:
>
>> I am forced to consider this approach because my function will be receiving 
>> blobs but will have no idea what those blobs are.
> Why do you care ?  A BLOB is a BLOB.  It doesn't matter whether it's an 
> integer, a string, a car, a peace treaty or the smell of newly cut grass.  
> You store it when told to and retrieve it when told to.

My job is to find all the blob columns and unblob them into 
arrays of whatever type they originally were before they 
were blob'd.

>> if this "feature" is not clearly documented someone in the future will have 
>> a very hard time figuring out why the blobs don't make sense.
> BLOBs don't make sense.  They're opaque.  You are not meant to understand 
> anything about what's in a BLOB.  That's for the layer of your software which 
> packs the information into a blob or unpacks the blob back into information.

Understood. I'm writing the code that does the packing and 
unpacking. The users are designing their own databases. When 
they store for example, an array of 16 bit integers, or an 
array of 64 bit floats, I want to give them the same thing 
back when they query the database not just an array of bytes 
that they have to decode themselves.

Since I have control over how the blobs are generated, I can 
easily add a type identifier to each blob so that I know how 
to unblob it.

-Bill

[sqlite] Pascal (almost) style BLOBs

[William Drago]

On 4/17/2016 9:20 AM, Ketil Froyn wrote:
> I'm not sure I understand why you can't specify the type in a separate
> column, rather than prepending it to the blob? That seems like a more
> flexible way to have access to the information.

I have no control over the database schema, table structure, 
etc.. I also have no control of the select statement that is 
generating that data.

-Bill

[sqlite] Pascal (almost) style BLOBs

[William Drago]

All,

Any thoughts on using the first byte of a BLOB to indicate 
what kind of data that BLOB contains?

For example:
0 = plain old bytes
1 = 16 bit integers
2 = 32 bit integers
3 = singles
4 = doubles, etc.

I am forced to consider this approach because my function 
will be receiving blobs but will have no idea what those 
blobs are. However, I can ask the sender to prepend the blob 
with an indicator byte to provide the necessary information.

In the past I have used comments in the table structure and 
even used custom types (e.g. i16BLOB, for a blob that 
contains 16 bit ints), but in this case I will not have 
access to that information. The data is coming to me in the 
form of an ADO.NET DataTable which does not contain such 
information.

Of course tagging the blobs like this will increase the size 
of the database, but not by much, and if this "feature" is 
not clearly documented someone in the future will have a 
very hard time figuring out why the blobs don't make sense.

Is there anything else I should be aware of?

Thanks,
Bill

[sqlite] Error help

[William Drago]

>>   I was taught the basics in SQLite with Firefox, and
>> am already disappointed that I can't really do what I want to do with the
>> Firefox plug in.
> Plenty of other ways to use SQLite with a graphical GUI:
>
> 
> 
> 
>
> Simon.

Don't forget about SQLite Expert. It's one of the best free 
admin tools I've used.

http://www.sqliteexpert.com/

-Bill

[sqlite] Slow real world performance - Any suggestions please (warning long)

[William Drago]

On 7/3/2015 8:39 AM, Rob Willett wrote:
> Simon,
>
> We had missed the incorrect defn of Calculate in the index. We?ve been 
> changing around stuff and suspect we omitted to check as carefully as we we 
> should when changing :(
>
> We?ll also update BayesAttribute as well.
>
> We?re actually rebuilding the database from scratch anyway, it?ll take around 
> 3-4 days now as opposed to 12-16 days as before :)
>
> We?ll check if we get any improvements from rebuilding using .dump by copying 
> the database to another server and doing it there. Thats one of the great 
> things about SQLite, pick the database up and copy it somewhere else :)
>
> is Vacuum the same as doing the .dump and restore or is it different? We like 
> the .dump as it gives us a nice easy to use backup :)
>
> Rob
>
>> On 3 Jul 2015, at 13:15, Simon Slavin  wrote:
>>
>>
>> On 3 Jul 2015, at 11:35am, Rob Willett  
>> wrote:
>>
>>> CREATE TABLE "RAG" (
>>> "Id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
>>> "Count" integer NOT NULL DEFAULT 0,
>>> "Text" TEXT COLLATE NOCASE ,
>>> "Peak" integer,
>>> "Calculation" integer NOT NULL DEFAULT 0,
>>> "Red" integer DEFAULT 0,
>>> "Amber" integer DEFAULT 0,
>>> "Green" integer DEFAULT 0,
>>> "BayesAttributes" TEXT
>>> );
>> [...]
>>
>>> CREATE UNIQUE INDEX "RAG_Idx1" ON RAG ("Text" COLLATE NOCASE ASC);
>>> CREATE UNIQUE INDEX "RAG_Idx2" ON RAG ("Calculation" COLLATE NOCASE ASC, 
>>> "Text" COLLATE NOCASE ASC);
>>> CREATE INDEX "Rag_Idx3" ON RAG ("Calculation" COLLATE NOCASE ASC, 
>>> "BayesAttributes" COLLATE NOCASE ASC);
>> To explain further, now that you have declared your "Text" column as being 
>> COLLATE NOCASE, NOCASE is now the default collation for it, and all sorting 
>> and matching on Text will be NOCASE unless you state otherwise.  So it's not 
>> necessary to state COLLATE NOCASE when you create indexes on it or mention 
>> it elsewhere.  It won't do any harm, though.  Shout out to Clemens for his 
>> corrections to my earlier post.
Clearly, in this case, using COLLATE NOCASE in the table 
definition is the right thing to do. Under what conditions 
would using it in the index instead be the right
thing to do?

-Bill

[sqlite] Reader.GetBytes() - when is a byte not a byte?

[William Drago]

Jean,

Thanks for the reply. I understand this very well, and I 
have read this page many times over the past few years:

http://www.sqlite.org/datatype3.html


My argument is that regardless of a column's type or type 
affinity, a method called ReadBytes() should read the bytes 
as stored in the database and let the programmer figure out 
what to do with them.

The latest doc available here (very last link on page):

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

shows no methods for reading BLOBs. There are methods like 
GetString, GetFloat, GetDouble, etc., but nothing called 
GetBlob.

GetBytes is describes as follows:

SQLiteDataReader.GetBytes Method
Retrieves a column as an array of bytes (blob)

public override long GetBytes(
int i,
long fieldOffset,
byte[] buffer,
int bufferoffset,
int length
);

Parameters

i
The index of the column to retrieve

fieldOffset
The zero-based index of where to begin reading the data

buffer
The buffer to write the bytes into

bufferoffset
The zero-based index of where to begin writing into the array

length
The number of bytes to retrieve

Return Value
The actual number of bytes written into the array

That implies that it will read the bytes of any column 
regardless of type. Why on Earth would a method called 
GetBytes restrict itself to only those columns with blob 
affinity? It doesn't make any sense. It should be called 
GetBlob to match the naming convention of all the other 
Getxxx methods.

I realize this is an ADO.NET thing and I'm not casting blame 
on the System.Data.SQLite devs. The spec is the spec.

Sorry for the rant...

Have a good weekend, and thanks again for spending the time 
on this. I have learned more from the people on the list 
than I ever imagined.

Regards,
-Bill

On 7/3/2015 1:51 PM, Jean Chevalier wrote:
> In SQLite, every value you store is stored alongside its type. This is unlike 
> other databases where the column determines the type and every value stored 
> against it share it. In SQLite you could have a table in which all value 
> types as stored contradict all column types as declared, if you so craft it 
> (except integer primary key). As for a column declared BLOB, it results in an 
> affinity of NONE meaning "perform no implicit conversions" (as long as 
> lossless and reversible), it doesn't mean "affinity binary" because there is 
> no such thing as something more generic than binary that should implicitly be 
> cast to binary upon storing it. So a blob-declared column would not try and 
> implicitly convert, but it will still store exactly what you put in, 
> including its type, inferred from the way in which you did the assignment, 
> e.g., for set c = '1' it will store {text-1} for integer it will store 
> {integer-of-1-} (a special case), for x'01' it will store {blob-01}, 
> and so on. Th!
>   is lower
>   case 'blob' is the most generic case (your 'bytes') but to obtain that one 
> must cast() to a blob which means "strip from what I'm giving you any 
> indication that it may be a text, or an integer, or a real, as it is really 
> neither". Think of blob not as a type ('binary') but the absence of types. 
> You can further apply a check constraint to the column to ensure that no 
> value stored will be stored along with a tag meaning "I'm of a certain kind". 
> This check(typeof(c)='blob') will ensure the data you put in will be 'naked' 
> so to speak, and when the data you put in is naked, SQLite can only store it 
> as bytes, and by default return it as bytes. So you can see how it differs, 
> blob as column type meaning 'suggest no type' or 'add no type' and blob as 
> value type meaning 'a value with no type tag attached' or 'convey no type'. 
> They're complementary. Think of the two together as "none given - none taken" 
> (or 'none added'). Naturally the constraint will not let you set c = '1' 
> because '!
>   1' implie
>   s text by way of the quotes, same for Int (and how would it know if you 
> meant a one-byte int, or two, or four?), and I think that's what you want in 
> your application, but it will let you assign something in the x'' notation, 
> or a cast() expression. So a check constraint makes the column more 
> 'demanding' so to speak, type-wise, stricter, and closer to what you observe 
> in other databases. Just remember to tweak the check constraint to allow 
> nulls for nullable columns. After which, we'd expect that if no type marker 
> was carried in, none will come out upon querying it, unless you have some 
> other layer wrapped around it such as some ODBC driver assuming that it's its 
> duty to further cast a column to a target type based on the column's declared 
> type. But this is because some wrapper drivers assume the user will try and 
> use SQLite as he uses most other databases, or because the driver cannot 
> support exposing a series of column values of varying types. A native SQLite 
> driver (one b!
>   uilt in a
>   ccordance to how SQLit

[sqlite] Reader.GetBytes() - when is a byte not a byte?

[William Drago]

All,

Below is a .dump of the database I'm working with. I can use 
GetBytes() successfully on all the BLOB columns except 
myTextArray. On that column I get a 
System.InvalidCastException error. Can anyone tell me why? 
Thanks.

-Bill


---.dump file---

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE myTable (

myText Text,

myTextArray BLOB, --Text

myBool Boolean,

myBoolArray BLOB, --Boolean

myUInt8 UInt8,

myUInt8Array BLOB, --UInt8

myUInt16 UInt16,

myUInt16Array BLOB, --UInt16

myInt16 Int16,

myInt16Array BLOB, --Int16

myInt32 Int32,

myInt32Array BLOB, --Int32

myInt64 Int64,

myInt64Array BLOB, --Int64

myReal32 Real32,

myReal32Array BLOB, --Real32

myReal64 Real64,

myReal64Array BLOB --Real64

);
INSERT INTO "myTable" VALUES('This is text','"blue" "red" 
"orange" 
"yellow"',0,X'00010001',255,X'FFFEFDFCFBFAF9F8F7F6',65535,X'FEFFFDFFFCFFFBFFFAFFFAFFFBFFFCFFFDFF',32767,X'FF7F01800100020003000400050006000700',2147483647,X'FF7F00800100',9223372036854775807,X'FF7F00800100',3.40282346638528897567e+38,X'7F7F70443901803F004040408040A040C040E040',1.79769313486231063271e+308,X'66E62440AE47E17A14AEF33F33B323C0007862A441A7C043');
COMMIT;

[sqlite] Please explain SQLiteConnection.GetSchema restrictionValues

[William Drago]

Thank you very much, Joe. I'll chew on this for a while and 
see if I can make sense of it.

-Bill

On 5/15/2015 11:34 PM, Joe Mistachkin wrote:
> I just updated the documentation with more details, here:
>
>   https://system.data.sqlite.org/index.html/ci/4be7ad3120577d30?sbs=0
>
> --
> Joe Mistachkin
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> -
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2015.0.5941 / Virus Database: 4342/9787 - Release Date: 05/15/15
>
>

[sqlite] Please explain SQLiteConnection.GetSchema restrictionValues

[William Drago]

All,

I'm using SQLiteConnection.GetSchema, and it is working, but 
I don't really understand why. Can anyone explain or point 
me to an explanation of how restrictionValues are used in 
that method? There's no explanation in SQLite.NET.chm and 
the little bit I could find on line is not very helpful.

Thanks,
-Bill

[sqlite] Please confirm what I THINK I know about blobs

[William Drago]

On 5/9/2015 6:40 AM, Eduardo Morras wrote:
> On Sat, 09 May 2015 06:09:41 -0400
> William Drago  wrote:
>
>> All,
>>
>> Say you encounter a blob in a database. There's no way to
>> tell if that blob carries bytes, floats, doubles, etc, correct?
>>
>> Assuming the above is true, then is it always prudent to
>> store some metadata along with your blobs so that they can
>> be identified in the future?
>>
>> Example table:
>>
>> ModelNo TEXT (e.g. SO-239)
>> SerialNo TEXT (e.g. 101)
>> VSWR BLOB (e.g. x'feab12c...')
>> VSWR_Type TEXT (e.g. double)
>>
>>
>> Does this make sense?
> You can use SQL comments on CREATE TABLE, those comments aren't deleted from 
> SQLITE_MASTER table, you can query it as a normal table.
>
> CREATE TABLE blob_table (
>   ModelNo TEXT,  -- e.g. S0-239
>   SerialNo TEXT, -- e.g. 101
>   VSWR BLOB  -- double, e.g. x'feab12c'
> );
>
> SELECT sql from sqlite_master where type='table' AND tbl_name='blob_table';
>
> will return
>
> CREATE TABLE blob_table (
>   ModelNo TEXT,  -- e.g. S0-239
>   SerialNo TEXT, -- e.g. 101
>   VSWR BLOB  -- double, e.g. x'feab12c'
> )

This is a clever idea and saves the addition of a column 
just for blob type. Is this a reliable feature of SQLite? 
Does anyone see any issues with this as opposed to using a 
dedicated column?

Thanks,
-Bill

[sqlite] VBA Sqllite blob data

[William Drago]

You may find what you need here:

https://sqliteforexcel.codeplex.com/

Good luck,
-Bill

On 5/8/2015 3:15 PM, Preston King wrote:
> Does anyone have an example of how to read sqlite blob records, that are not 
> pictures, into Excel? I have been trying to find some VBA code to do this but 
> am not having much luck. Thanks
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> -
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2015.0.5941 / Virus Database: 4342/9728 - Release Date: 05/08/15
>
>

[sqlite] Please confirm what I THINK I know about blobs

[William Drago]

All,

Say you encounter a blob in a database. There's no way to 
tell if that blob carries bytes, floats, doubles, etc, correct?

Assuming the above is true, then is it always prudent to 
store some metadata along with your blobs so that they can 
be identified in the future?

Example table:

ModelNo TEXT (e.g. SO-239)
SerialNo TEXT (e.g. 101)
VSWR BLOB (e.g. x'feab12c...')
VSWR_Type TEXT (e.g. double)


Does this make sense?

Thanks,
-Bill

[sqlite] System.Data.SQLite, Need alternate way to get field length

[William Drago]

All,

I am trying to determine the number of bytes in a blob. 
According to the help file under SQLiteDataReader.GetBytes 
Method in the Remarks section:

"To determine the number of bytes in the column, pass a null 
value for the buffer. The total length will be returned."

I'm working in VEE and VEE doesn't have a null keyword, so I 
tried System.DBNull.Value and I get an error (signatures 
don't match).

I've tried a few other things, but VEE rejects everything 
that isn't a byte array or pointer to a byte array. I don't 
know if I can blame VEE. The method signature says byte[], 
so VEE is expecting to see a byte array or pointer to a byte 
array.

So, are there any other ways to get the number of bytes in a 
blob?

Thanks,
-Bill

Re: [sqlite] capturing and testing a hot journal

[William Drago]

On 7/14/2014 6:38 PM, mm.w wrote:

seriously? you should fix and solve why the soft crashed in the first
place, reality check please.


The software doesn't crash on its own; I'm forcing it to 
crash with a divide-by-zero for test purposes. This doesn't 
happen in actual use and there's no reason other than a 
power failure for a transaction to not commit successfully. 
But that doesn't mean I shouldn't handle a failed 
transaction if it ever does happen.



-Bill



"But it is possible that Dropbox will copy a database and journal
files that are not consistent with each other, which can create
problems"

fix the sync process, that's easy.

Best.



On Mon, Jul 14, 2014 at 3:04 PM, Drago, William @ MWG - NARDAEAST <
william.dr...@l-3com.com> wrote:


This may be a bit simplistic, but it does give me a reasonable degree of
confidence that hot journal files are being handled correctly in my
application.

I simply put a 1/0 on the line before my commit to purposely crash my app.
Sure enough there's a journal file after the crash (I have a rather large
transaction consisting of among other things, about 35 rows inserted, each
containing a blob).

When I restart my app it looks for the presence of a journal file and will
open and read the db so that SQLite can deal with it. It also displays a
message letting the user know that something went wrong during the last run.

I do this with a test db of course, not the real one.

-Bill





-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
boun...@sqlite.org] On Behalf Of Charles Parnot
Sent: Saturday, July 12, 2014 4:38 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] capturing and testing a hot journal

Hi all,

For testing purposes of our application (a Mac app), I am generating
what I thought would be a database with a "hot" journal using this
approach (on an existing database):

- open the database (and PRAGMA journal_mode = TRUNCATE;)
- open a transaction: BEGIN IMMEDIATE TRANSACTION;
- add some rows: INSERT etc...
- **make a copy of the db and journal files** (while still hot?)
- close the transaction

Then I open the copied database+journal (naming the files
appropriately), again in TRUNCATE journal mode. As expected, the
content of the database does not include the inserted rows. However,
the journal file is not emptied, even after closing the database. Based
on the documentation
(http://www.sqlite.org/lockingv3.html#hot_journals), I would have
expected the journal file to be emptied because it is "hot".

There are 2 options here:

- the journal file is actually not "hot" and I misunderstood the
conditions that make it hot
- there is a bug in SQLite

Obviously, I strongly suspect I am misunderstanding things, and don't
think it is an SQLite bug. Despite intensive Google-ing and more
testing, I am not sure what makes the journal non-hot.

Thanks for your help!

Charles


NB: You might be wondering why I am doing the above. I realize SQLite
has already much more advanced tests for "hot" db+journals (running
custom versions of filesystems to generate all kind of edge cases). The
test case I am generating is just for a simple edge case of our
Dropbox-based syncing (see: https://github.com/cparnot/PARStore and
http://mjtsai.com/blog/2014/05/21/findings-1-0-and-parstore/). For a
given database file, there is only one device that can write to it, all
other devices being read-only (not in terms of filesystem, but sqlite-
wise). But it is possible that Dropbox will copy a database and journal
files that are not consistent with each other, which can create
problems. For instance, maybe a read-only device could try to open the
(still old) database with a new non-empty journal file and sqlite would
empty that journal file, then Dropbox could in turn empty the journal
file before the writer client had finished the transaction. I am not
(yet) going to test for and try to protect against more complicated
(and rarer) edge cases where the database is in the middle of writing a
transaction (which I suspect will only happen in case of crashes, not
because of Dropbox, in which case the recovery of the database by the
read-only client would actually be beneficial).

--
Charles Parnot
charles.par...@gmail.com
http://app.net/cparnot
twitter: @cparnot

Your Lab Notebook, Reinvented.
http://findingsapp.com

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

CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any
attachments are solely for the use of the addressee and may contain
information that is privileged or confidential. Any disclosure, use or
distribution of the information contained herein is prohibited. In the
event this e-mail contains technical data within the definition of the
International Traffic in Arms Regulations or Export Administration
Regulations, it is subject to the export control laws of the
U.S.Govern

Re: [sqlite] Basic SQLite/EF6 question

[William Drago]

On 7/5/2014 12:38 PM, Joe Mistachkin wrote:

Do you see the System.Data.SQLite option in the Data Connections
dialog within Server Explorer in Visual Studio?


Yes, and I can see my tables, etc. too.


   Which version
and edition of Visual Studio are you using?

Microsoft Visual Studio Professional 2013
Version 12.0.30501.00 Update 2

Microsoft .NET Framework
Version 4.5.50938

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

Re: [sqlite] Basic SQLite/EF6 question

[William Drago]

On 7/4/2014 10:11 PM, Joe Mistachkin wrote:

William Drago wrote:

"Your project references the latest version of Entity
Framework; however, an Entity Framework database provider
compatible with this version could not be found for your
data connection."


In my end-to-end testing of the System.Data.SQLite 1.0.93.0 /
Entity Framework 6.1 integration, I did not encounter that
particular error message.

I suspect it might be due to "outdated" System.Data.SQLite and/or
Entity Framework assemblies in the project directory somewhere, in
the GAC, or from a previous installation of the System.Data.SQLite
setup package.


I only see 1.0.93.0 in the GAC. How can I purge all previous 
SQLite installs (if there are any that I am not aware of)?



Also, check the config files for the project to make sure they look
similar to the ones checked into the repository for the "testef6"
project, paying careful attention to both the
"system.data/DbProviderFactories" and "entityFramework/providers"
sections, e.g.:

https://system.data.sqlite.org/index.html/artifact?ci=trunk&filename=testlin
q/2010/EF6/App.config&ln=6-12

-AND-

https://system.data.sqlite.org/index.html/artifact?ci=trunk&filename=testlin
q/2010/EF6/App.config&ln=16-20

Any extraneous entries in either section should be removed.
I modified my App.config file to match the above. There were 
references to SQL Server and the references to SQLite were 
did not include the version. I then rebuilt the project, 
exited and restarted VS, but I am still getting the same 
error message.


BTW, when I installed SQLite, the installer hung for several 
minutes before telling me that the install was successful. I 
don't know if that is a clue to what the problem might be.


-Bill

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

Re: [sqlite] Basic SQLite/EF6 question

[William Drago]

Thank you for the reply. I'm getting further, but still have 
an issue when trying to add an Entity Data Model. The error 
message is:


"Your project references the latest version of Entity 
Framework; however, an Entity Framework database provider 
compatible with this version could not be found for your 
data connection."


I've rebuilt the project and exited ad restarted VS, but no 
luck.


Any Ideas?

Thanks,
-Bill




On 7/4/2014 7:25 PM, Joe Mistachkin wrote:

William Drago wrote:

I found this on Stack Overflow and it's pretty scary. Do I
really have to do all this to get SQLite/EF6 to work?


http://stackoverflow.com/questions/21757843/system-data-sqlite-1-0-91-0-and-
ef6-0-2
As of version 1.0.93.0 (and probably 1.0.92.0), those steps should no
longer be necessary.

However, in order to get the Visual Studio design-time component support
for use with Entity Framework, the setup package for the version of Visual
Studio in use must be installed.

Visual Studio 2010:
https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki#sqlit
e-netFx40-setup-bundle-x86-2010

-OR-

Visual Studio 2012:
https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki#sqlit
e-netFx45-setup-bundle-x86-2012

-OR-

Visual Studio 2013:
https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki#sqlit
e-netFx451-setup-bundle-x86-2013

--
Joe Mistachkin

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


-
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2014.0.4716 / Virus Database: 3986/7800 - Release Date: 07/04/14




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

[sqlite] Basic SQLite/EF6 question

[William Drago]

All,

I am trying to use EF6.1.1 and SQLite 1.0.93.0 (3.8.5) in VS 
2013. I've never used EF6 before, but I have been using 
System.Data.SQLite the normal way for about a year. now with 
excellent results.


My question is, when I try to add a new ADO.NET Entity Data 
Model the only connection choices are Microsoft SQL Server 
Databases. I have all the req'd SQLite, SQLite.EF6, and 
SQLite.LINQ references in my project (via NuGet), but I see 
no way to add a SQLite connection.


I found this on Stack Overflow and it's pretty scary. Do I 
really have to do all this to get SQLite/EF6 to work?

http://stackoverflow.com/questions/21757843/system-data-sqlite-1-0-91-0-and-ef6-0-2

If someone could point me to a good tutorial I'd appreciate it.


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

Re: [sqlite] System.Data.SQLite fails to load

[William Drago]

On 2/26/2014 5:50 AM, Simon Slavin wrote:

On 26 Feb 2014, at 10:19am, William Drago  wrote:


VEE is a graphical programming language similar to NI LabView.

Is this Agilent VEE ?  If it's very like LabView then you shouldn't be using a 
.NET framework at all.

The normal way to use SQLite is to call SQLite API calls from C.  You can 
compile the SQLite library into a standalone app, but instead into an 
addon/toolbox/library as you see fit.


Yes, this is Agilent VEE. I can't use the sqlite3.dll 
directly with VEE. VEE only recognizes a limited number of 
data types and has no idea what typedef is, so I'd have to 
I'd have to write a wrapper for SQLite before I could use it 
(I'd love to do this, but my boss would kill me).


So, .NET is the easiest way to use SQLite with VEE. Also, 
using .NET allows me to reuse some of the ADO.NET libraries 
that I use with MS SQL Server.


I wish VEE was more capable in this regard. Productivity is 
very high in VEE because it does a lot for you, but 
flexibility is what you lose because you have little control 
over how VEE does things.


Thanks for the reply,
-Bill

More about VEE for anyone working in an electronics test & 
measurement environment or the morbidly curious:

http://cp.literature.agilent.com/litweb/pdf/5990-9117EN.pdf



There are numerous SQLite addons for LabView (e.g.

<http://www.lvs-tools.co.uk/software/sqlite-api/>
<http://www.saphir.fr/en/produits/gdatabase-for-sqlite-5.html>
<http://sine.ni.com/nips/cds/view/p/lang/en/nid/209317>

) and they don't use .NET, they address the C API.  I would expect to find 
something similar for VEE, or if not, then to write thin IO library that can 
address a SQLite database using the C API.

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


-
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2014.0.4335 / Virus Database: 3705/7124 - Release Date: 02/25/14




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

Re: [sqlite] System.Data.SQLite fails to load

[William Drago]

On 2/25/2014 3:44 PM, Joe Mistachkin wrote:

William Drago wrote:

I am using System.Data.SQLite with a relatively uncommon
language called VEE. This is an interpreted language that
runs in a 32bit development/runtime environment.


I've never heard of this language before; however, it sounds
like it hosts the CLR within its process?


I'm not sure. VEE is a graphical programming language 
similar to NI LabView. There are a lot of things in VEE that 
are beyond my ability to observe.



My application works fine when run on an internal or USB
thumb drive. However, if I try running it from a network
drive SQLite fails to load.


For the 2.0 .NET Framework, loading assemblies from a network
share can be complicated by trust issues.


If trust issues are the cause of my problem, would a C# 
program targeting 2.0 also fail? If trust issues are 
insurmountable would this be a case for using the GAC?



   Since I do not see
any network share paths in your trace output, I'm not sure if
that is the case here.
There should be network shares in the trace output since I'm 
running the program from the network. It should be looking 
for SQLite in something like 
b:\SomeNetworkFolder\MyAppFolder. Instead it's looking in 
the VEE runtime executable path. I wonder if this is a 
symptom of trust issues.




One thing that I'm noticing is that the successful load uses
the "LoadFrom context" and the failed load uses the default
context.  I'm not sure how the VEE code loads the
System.Data.SQLite assembly (or other assemblies); however,
maybe try using the "LoadFrom context" when loading from the
network share as well.
I point and click on the assemblies I want to use in the VEE 
IDE, and have no control after that.


Anyway, thanks for the help. I have a few things to look 
into, and I may file support request with Agilent if I can 
rule out all issues with my network.


-Bill



--
Joe Mistachkin

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


-
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2014.0.4335 / Virus Database: 3705/7124 - Release Date: 02/25/14




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

[sqlite] System.Data.SQLite fails to load

[William Drago]

All,

I am using System.Data.SQLite with a relatively uncommon 
language called VEE. This is an interpreted language that 
runs in a 32bit development/runtime environment.


My application works fine when run on an internal or USB 
thumb drive. However, if I try running it from a network 
drive SQLite fails to load.


In the log files below I can see that when I'm running from 
my C:\ drive, everything is fine. But when I run from the 
network the Appbase is incorrect.


I don't know if this is a problem with the network, or with VEE.

Can anyone shed some light on this?

I am using the binaries found in 
sqlite-netFx20-binary-Win32-2005-1.0.91.0.zip


Thanks,
-Bill


Here are the log files...

This is running on C:\

*** Assembly Binder Log Entry  (2/24/2014 @ 5:56:00 PM) ***

The operation was successful.
Bind result: hr = 0x0. The operation completed successfully.

Assembly manager loaded from: 
C:\Windows\Microsoft.NET\Framework\v2.0.50727\mscorwks.dll
Running under executable  C:\Program Files (x86)\Agilent\VEE 
Pro 9.3\vee.exe

--- A detailed error log follows.

=== Pre-bind state information ===
LOG: User = XX\WDRAGO
LOG: DisplayName = System.Data.SQLite, Version=1.0.91.0, 
Culture=neutral, PublicKeyToken=db937bc2d44ff139

 (Fully-specified)
LOG: Appbase = file:///C:/SQLite_Test/
LOG: Initial PrivatePath = NULL
LOG: Dynamic Base = NULL
LOG: Cache Base = NULL
LOG: AppName = vee.exe
Calling assembly : Vee2net, Version=9.32.17704.1, 
Culture=neutral, PublicKeyToken=null.

===
LOG: This bind starts in LoadFrom load context.
WRN: Native image will not be probed in LoadFrom context. 
Native image will only be probed in default load context, 
like with Assembly.Load().

LOG: No application configuration file found.
LOG: Using machine configuration file from 
C:\Windows\Microsoft.NET\Framework\v2.0.50727\config\machine.config.
LOG: Post-policy reference: System.Data.SQLite, 
Version=1.0.91.0, Culture=neutral, 
PublicKeyToken=db937bc2d44ff139

LOG: GAC Lookup was unsuccessful.
LOG: Attempting download of new URL 
file:///C:/SQLite_Test/System.Data.SQLite.DLL.
LOG: Assembly download was successful. Attempting setup of 
file: C:\SQLite_Test\System.Data.SQLite.dll

LOG: Entering run-from-source setup phase.
LOG: Assembly Name is: System.Data.SQLite, Version=1.0.91.0, 
Culture=neutral, PublicKeyToken=db937bc2d44ff139
LOG: Where-ref bind Codebase matches what is found in 
default context. Keep the result in default context.

LOG: Switch from LoadFrom context to default context.
LOG: Binding succeeds. Returns assembly from 
C:\SQLite_Test\System.Data.SQLite.dll.

LOG: Assembly is loaded in default load context.



This is what happens when I try running from a network drive:



*** Assembly Binder Log Entry  (2/24/2014 @ 5:59:40 PM) ***

The operation failed.
Bind result: hr = 0x80070002. The system cannot find the 
file specified.


Assembly manager loaded from: 
C:\Windows\Microsoft.NET\Framework\v2.0.50727\mscorwks.dll
Running under executable  C:\Program Files (x86)\Agilent\VEE 
Pro 9.3\vee.exe

--- A detailed error log follows.

=== Pre-bind state information ===
LOG: User = XX\WDRAGO
LOG: DisplayName = System.Data.SQLite, Version=1.0.91.0, 
Culture=neutral, PublicKeyToken=db937bc2d44ff139

 (Fully-specified)
LOG: Appbase = file:///C:/Program Files (x86)/Agilent/VEE 
Pro 9.3/

LOG: Initial PrivatePath = NULL
LOG: Dynamic Base = NULL
LOG: Cache Base = NULL
LOG: AppName = vee.exe
Calling assembly : (Unknown).
===
LOG: This bind starts in default load context.
LOG: Using application configuration file: C:\Program Files 
(x86)\Agilent\VEE Pro 9.3\vee.exe.config
LOG: Using machine configuration file from 
C:\Windows\Microsoft.NET\Framework\v2.0.50727\config\machine.config.
LOG: Post-policy reference: System.Data.SQLite, 
Version=1.0.91.0, Culture=neutral, 
PublicKeyToken=db937bc2d44ff139

LOG: GAC Lookup was unsuccessful.
LOG: Attempting download of new URL file:///C:/Program Files 
(x86)/Agilent/VEE Pro 9.3/System.Data.SQLite.DLL.
LOG: Attempting download of new URL file:///C:/Program Files 
(x86)/Agilent/VEE Pro 
9.3/System.Data.SQLite/System.Data.SQLite.DLL.
LOG: Attempting download of new URL file:///C:/Program Files 
(x86)/Agilent/VEE Pro 9.3/System.Data.SQLite.EXE.
LOG: Attempting download of new URL file:///C:/Program Files 
(x86)/Agilent/VEE Pro 
9.3/System.Data.SQLite/System.Data.SQLite.EXE.

LOG: All probing URLs attempted and failed.




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

[sqlite] Correct order of 'using' in System.Data.SQLite

[William Drago]

All,

I am still new to SQLite and C#. I am wondering if I have 
the correct order of 'using' statements in the code below. 
In particular, I am wondering if 'using (transaction...)' 
should come before 'using (SQLCommand...)'


Any other comments are appreciated.

Thanks,
-Bill



using System;
using System.IO;
using System.Data.SQLite;
using System.Diagnostics;

namespace Spurs
{
class Program
{
static void Main(string[] args)
{
/*
 * Spur database has the following structure:
 *
 * CREATE TABLE [spurs] (
 *[FundamentalFreq] FLOAT,
 *[SpurFreq] FLOAT,
 *[SpurdBm] FLOAT);
 *
 *
 * .CSV file is 12.3 million entries like this:
 * 1075.10,2150.449990,-49.192
 * 1075.10,2150.449990,-49.278
 * 1886.90,1887.197613,-54.701
 *
 * */

// Timer stuff
Stopwatch stopWatch = new Stopwatch();
TimeSpan insertTime, commitTime;
const string timePattern = 
"{0:00}:{1:00}:{2:00}.{3:00}";

string elapsedTime;

// File names
string fileName = "raw_data.csv";   // Input 
file name.
string dbFileName = "raw_data.db";  // Database 
file (output file) name.


// Input file read-loop variables
StreamReader myReader; // A reader 
to read the file.
int lineCount = 0; // Total 
number of lines in input file.
int reportInterval = 10;   // Update 
progress every reportInterval lines.
int intervalCount = 0; // Number of 
lines since last update report.
string line = "";  // Holds 1 
line from file.
string[] lineCSV = new string[3];  // Holds the 
values from each line.


// Database variables
string connectionString;   // Database 
connection string.
string commandString;  // Database 
SQL command string.
connectionString = String.Format("Data 
Source={0}", dbFileName);
commandString = "INSERT INTO spurs VALUES (?, 
?, ?)";


SQLiteConnection connection;   // Active 
database connection.
SQLiteTransaction transaction; // Active 
database transaction.
SQLiteCommand SQLCommand;  // Active 
database command.


SQLiteParameter @FundamentalFreq;  // UUT Test 
frequency (MHz).
SQLiteParameter @SpurFreq; // Frequency 
of largest spur (MHz).
SQLiteParameter @SpurAmp;  // Amplitude 
of largest spur (dBm).

@FundamentalFreq = new SQLiteParameter();
@SpurFreq = new SQLiteParameter();
@SpurAmp = new SQLiteParameter();

// Process .csv file.
System.Console.WriteLine("Reading file: " + 
fileName);
using (connection = new 
SQLiteConnection(connectionString))

{
using (SQLCommand = new 
SQLiteCommand(connection))

{
SQLCommand.CommandText = commandString;

SQLCommand.Parameters.Add(@FundamentalFreq);

SQLCommand.Parameters.Add(@SpurFreq);
SQLCommand.Parameters.Add(@SpurAmp);
connection.Open();
using (transaction = 
connection.BeginTransaction())

{
using (myReader = new 
StreamReader(fileName))

{
stopWatch.Start();
while ((line = 
myReader.ReadLine()) != null)

{
// Get values from one line 
in the .csv file.
lineCSV = line.Split(new 
char[] { ',' });
@FundamentalFreq.Value = 
double.Parse(lineCSV[0]);
@SpurFreq.Value = 
double.Parse(lineCSV[1]);
@SpurAmp.Value = 
double.Parse(lineCSV[2]);


// Insert them into the 
database.

SQLCommand.ExecuteNonQuery();

// Print progress every 
reportInterval lines.

lineCount++;
intervalCount++;
if (intervalCount == 
reportInterval)

{

System.Console.Write("Processing line " + lineCount + '\r');

intervalCount = 0;
}
}// End while.
stopWatch.Stop();
insertTime = stopWatch.Elapsed;
  

Re: [sqlite] a tool that allows you to graphically change sqlite schema

[William Drago]

I like SQLite Expert too. Very nice tool, you don't have to 
run Firefox to use it, and the personal edition is free. 
I've gotten a lot of good use out of it. Windows only is the 
one drawback.


-Bill

On 9/18/2013 5:09 AM, jcd wrote:


Is there a tool that allows you to graphically change 
sqlite schema as
simple as editing a spreadsheet? For example if I wanted 
to change a column
name from "my_driving_hours" to "driving_time", instead 
of writing a line
of code, I can just click on that column and type in the 
new name, letting
the tool automatically create a new database with the new 
schema and
migrate the old data into the new one. I found it very 
useful.


I am not sure if there exists such a tool after a long 
google search. Could

any pros give some thoughts? Many thanks.


Give SQLite Expert a try if you're on Windows. It might 
run well under Wine for Linux users (untested by me).


http://www.sqliteexpert.com/

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


-
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.3408 / Virus Database: 3222/6678 - Release 
Date: 09/18/13





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

Re: [sqlite] Question about date & time

[William Drago]

Thanks for the reply. I understand. I am going to do some 
experimenting just to make sure...


Regards,
-Bill

On 9/15/2013 3:13 PM, Petite Abeille wrote:

On Sep 15, 2013, at 8:31 PM, William Drago  wrote:


Thanks for the reply. Seconds since the epoch does make a good timestamp. Is 
that what is normally used to extract data between time periods?

(Date & Time seems to be a popular topic at the moment)

There is nothing prescriptive in using epoch time.

As SQLite doesn't have a dedicated date type, you are free to decide how you 
want to handle it.

There are two main encoding:

(1) As a number: Julian date, unix epoch, etc
(2) As a string: ISO 8601 & co.. Just make sure that your string representation 
sorts properly.

http://www.sqlite.org/lang_datefunc.html

The granularity of the date is up to you as well: day, hour, milliseconds, etc. 
This is more driven by what's convenient for your application. Ditto if this should 
be split between date &  time.

Depending on the task at hand, you could even require a much more full fledge 
set of entities:

   create table if not exists date
   (
 idinteger not null constraint date_pk primary key,

 year  integer not null,
 month integer not null,
 day   integer not null,

 day_of_year   integer not null,
 day_of_week   integer not null,
 week_of_year  integer not null,

 constraintdate_uk unique( year, month, day )
   )

   create table if not exists time
   (
 id  integer not null constraint time_pk primary key,

 hourinteger not null,
 minute  integer not null,
 second  integer not null,

 constraint  time_uk unique( hour, minute, second )
   )

And then there are timezones, etc…



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


-
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.3408 / Virus Database: 3222/6667 - Release Date: 09/15/13





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

Re: [sqlite] Question about date & time

[William Drago]

Hi Tim,

Thanks for the reply. Seconds since the epoch does make a 
good timestamp. Is that what is normally used to extract 
data between time periods?


Say for example, I want to know for the past month what my 
failure rate was between 11PM and 1AM every day. I'd figure 
out what 11PM and 1AM is in seconds since the epoch for the 
1st of the month and then for each of the next 30 days, then 
figure out some SELECT statement to use that set of numbers. 
It seems very convoluted.


Anyway, I have a feeling I'm asking this question in the 
wrong place. I'm not sure this is a SQLite specific question 
as the answer is probably going to be the same regardless of 
the DBMS.


Thanks,
-Bill

On 9/15/2013 1:16 PM, Tim Streater wrote:

On 15 Sep 2013 at 18:13, William Drago  wrote:


All,

Should I put date and time in separate columns if I want to
select by time?

For example:

SELECT * FROM testresults WHERE (status != "Pass") AND
(23:00 <= testtime) AND (testtime <= 01:00).

I have been reading the documentation, but it just isn't
clear to me how I should handle this.

I convert everything to seconds since the epoch and have a column with that. 
All comparisons are done against that value. This is not too difficult in PHP.



--
Cheers  --  Tim


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


-
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2013.0.3408 / Virus Database: 3222/6667 - Release Date: 09/15/13


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

[sqlite] Question about begin/commit

[William Drago]

All,

In the following bit of code found in the help file 
SQLite.NET.chm, I see that BeginTransaction() encloses 
everything. I always thought that just the for-loop needed 
to be enclosed by begin/commit.


What are the reasons for enclosing the other commands?

Thanks,
-Bill



using (SQLiteTransaction mytransaction = 
myconnection.BeginTransaction())

  {
using (SQLiteCommand mycommand = new 
SQLiteCommand(myconnection))

{
  SQLiteParameter myparam = new SQLiteParameter();
  int n;

  mycommand.CommandText = "INSERT INTO [MyTable] 
([MyId]) VALUES(?)";

  mycommand.Parameters.Add(myparam);

  for (n = 0; n < 10; n ++)
  {
myparam.Value = n + 1;
mycommand.ExecuteNonQuery();
  }
}
mytransaction.Commit();
  }
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Question about date & time

[William Drago]

All,

Should I put date and time in separate columns if I want to 
select by time?


For example:

SELECT * FROM testresults WHERE (status != "Pass") AND 
(23:00 <= testtime) AND (testtime <= 01:00).


I have been reading the documentation, but it just isn't 
clear to me how I should handle this.


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

[sqlite] String not valid DateTime

[William Drago]

All,

I am using SQLite with C# and am having trouble with SQLite 
DATETIME types. The following error occurs when trying to 
read rows from a table that contains dates (e.g. "12/09/2012 
22:51:24"). (I am using a SQLiteDataReader to put query 
results into a C# DataTable.)


"String was not recognized as a valid DateTime."

Apparently SQLite stores dates as strings while C# is 
expecting an object of some sort. Any thoughts or solutions? 
As a work-around I changed all my DATETIME types to VARCHARs.


Thanks,
Bill


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

Re: [sqlite] Newbie issues with SQLite & C#

[William Drago]

Hi Joe,

Thanks for the excellent reply. Putting SQLite.Interop.dll 
in my bin\Debug folder solved the problem.


I did read the download page and the faq page, but most of 
that stuff is gibberish to me and not beginner friendly at 
all. But all's well that ends well. I created some tables 
with the Firefox SQLite Manager plug-in and was able to read 
from them with my C# program.


One thing I'm still curious about are these Visual Studio 
design components. What are they and how do I access them 
from VS?


Thanks again,
Bill

On 12/3/2012 2:50 AM, Joe Mistachkin wrote:

William Drago wrote:

1) I'm not really sure how to install SQLite.  I downloaded
sqlite-netFx40-setup-x86-2010-1.0.82.0.exe and ran setup.

Installing System.Data.SQLite is only necessary if you need the
Visual Studio design-time components.


Is that all it takes?


That depends on how the final application is to be deployed.


There's an FAQ that says not to run setup, just copy all files
to a folder called "Externals."


Did you read the information on the download page?  It provides
some helpful details on how the System.Data.SQLite assemblies are
intended to be used in the development environment and deployed
to end-user environments.


What's setup for then?


It's primarily designed to install the Visual Studio design-time
components; however, there are certain other very rare special
cases where it's useful as well.


Did I botch the install?


Unlikely.  And you should be able to uninstall it later if you
find it is not necessary.


BTW, test.exe works fine.


That's good.


2) My program compiles successfully, but does not run; the
following error message is displayed: "Unable to load DLL
'SQLite.Interop.dll': The specified module could not be
found." The FAQ's regarding this issue make no sense to me.


The "SQLite.Interop.dll" file contains the native code for
SQLite.  The "System.Data.SQLite.dll" contains the managed
code for the ADO.NET provider and loads the other file at
runtime.  In order for this to work successfully, it must
be able to locate that file at runtime, which means the
"SQLite.Interop.dll" file must reside in the binary
directory itself or in the architecture-specific
sub-directory therein.  The download page also covers this
setup in more detail.


I'm exhausted, frustrated, and now wondering if SQLite is as
easy to setup and use as the website implies.


SQLite itself has no setup whatsoever; however, given the
nature of the .NET Framework, some effort is required to get
all the System.Data.SQLite components talking together.


What's going to happen when I try to distribute this
application?


This is also discussed (in great detail) on the download page:

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

--
Joe Mistachkin

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



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

[sqlite] Newbie issues with SQLite & C#

[William Drago]

All,

This is my first post to this group... I'm hoping someone 
can help me.


I've just wasted nearly 8 hours trying to get SQLite to work 
with a C# program in Visual Studio 10 (32bit WinXP, .NET 4.0).


Here are the issues:

1) I'm not really sure how to install SQLite. I downloaded 
sqlite-netFx40-setup-x86-2010-1.0.82.0.exe and ran setup. Is 
that all it takes? There's an FAQ that says not to run 
setup, just copy all files to a folder called "Externals." 
What's setup for then? Did I botch the install? BTW, 
test.exe works fine.


2) My program compiles successfully, but does not run; the 
following error message is displayed: "Unable to load DLL 
'SQLite.Interop.dll': The specified module could not be 
found." The FAQ's regarding this issue make no sense to me.


I'm exhausted, frustrated, and now wondering if SQLite is as 
easy to setup and use as the website implies. What's going 
to happen when I try to distribute this application?


You help is very much appreciated...

-Bill Drago







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