Re: [sqlite] I don't understand dates

[Simon Slavin]

On 26 May 2010, at 1:56am, Radcon Entec wrote:

> I have an application that writes data into a simple SQLite table with three 
> fields.  Two are integers and the third is a date.

If you still want us to think about this, can we see the table definition, 
please ?  Whatever you used for CREATE TABLE.

> The next things on my things I don't understand about SQLite are that columns 
> are not restricted to one data type, and that there is no specific date 
> format.  I have seen a customer A database in which the column containing the 
> date happily contained a Julian date and then a human-readable string.  But, 
> accepting all that without understanding it, I should be able to write a 
> floating-point number into my field.  But when I tried, I got an exception 
> with this message:
> 
> Invalid cast from 'Double' to 'DateTime'.Couldn't store <2455342.48371528> in 
> value_timestamp Column. Expected type is DateTime.

That is definitely not a message generated by SQLite.  SQlite wouldn't know a 
Double, and doesn't expect a DateType type.

> Huh???  I didn't think there was a DateTime type,

Correct.  See



> and I thought I could write anything into any column!

Correct.  Columns have 'affinities' not 'types'.  Although individual fields do 
have types.

>  Is this something that the ADO .Net provider is enforcing that doesn't 
> really correspond to the database?

That is my guess, though I don't know anything about your .NET framework.  That 
is not an official part of SQLite, it's something someone else whipped up.

>  What is the recommended way to write a datetime value as a Julian date?

It's popular to store dates as text, and use the functions in



for access and conversion.  But if you have SQLite handle your dates as Julian 
dates (which are numbers, and should be handled and stored as numbers) that's 
okay too.

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

[sqlite] How to compile RTREE using eclipse on windows

[D. Richard Hipp]

The following message is forwarded from private email.  (I know  
nothing about eclipse or windows so I can't help.)  Please reply to  
this list with CC to k52...@hanmail.net if you have any suggestions.

> Hello
> I am a student studying in south Korea about sqlite.
> I have sent the e-mail to you before to ask something about rtree.
> But I have not solve the problem yet, so I send the e-mail one more  
> time.
>
> I downloaded sqlite-3.6.23.1.tar.gz file and then I execute  
> CONFIGURE and MAKE in linux.
> After that, I compiled with config.h, parse.h, parse.c, opcode.h,  
> opcode.c, sqlite3.h, keywordhash.h file and Sqlite-3.6.3.23.1’s  
> other source file in windows eclipse environment.
>
>
>
> I except a few files like fts1…fts3 which make some error.
> In this situation, compile is ok. Works well.
> However..
>
>
>
> It doesn’t make error to compile giving option ,– 
> DSQLITE_ENABLE_RTREE=1, like this one,
> But it brings this problem.
>
>
>
> Sq.exe’s operation is stopped
> you can find the way to solve this problem though on-line.
> Close the program after checking solving method though on-line
> Close the program
>
> The program is closed by making table.
> When I make Rtree table also I can see the same problem.(the program  
> is closed)
>
> I can not use the amalgamation vision to make Sqlite program.
>
> Please let me know how to compile in window’s eclipse environment  
> with Sqlite-3.6.23.1.tar.gz file
> Thank you for giving many information. Thank you.
>

D. Richard Hipp
d...@hwaci.com



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

[sqlite] I don't understand dates

[Radcon Entec]

There are several aspects of SQLite that I don't understand, and at the top of 
this list is dates.

I have an application that writes data into a simple SQLite table with three 
fields.  Two are integers and the third is a date.  At one customer's site, 
that application was happily writing Julian dates (floating-point numbers) into 
the date field, until I did something (maybe updating my sqlite library?), and 
it suddenly started writing dates as human-readable strings.  At another 
customer's site, the same application is still writing dates as Julian dates.  
There is an ActiveX control with versions at both sites that read the databases 
and generate graphs from them.

I have a C# application using the most popular ADO .Net provider for SQLite.  
Using this application, I write dates into my databases as human-readable 
strings.  At customer A, the trend files are happily graphed.  At customer B, 
they aren't. 

So, since customer B uses Julian dates, I modified my C# application to read an 
.ini file to determine what date format to use.  If the .ini file says to use 
Julian dates, then I translate the human-readable string into a floating-point 
number using the julianday() function.  Then I try to write that value into my 
table.

The next things on my things I don't understand about SQLite are that columns 
are not restricted to one data type, and that there is no specific date format. 
 I have seen a customer A database in which the column containing the date 
happily contained a Julian date and then a human-readable string.  But, 
accepting all that without understanding it, I should be able to write a 
floating-point number into my field.  But when I tried, I got an exception with 
this message:

Invalid cast from 'Double' to 'DateTime'.Couldn't store <2455342.48371528> in 
value_timestamp Column.  Expected type is DateTime.

Huh???  I didn't think there was a DateTime type, and I thought I could write 
anything into any column!  Is this something that the ADO .Net provider is 
enforcing that doesn't really correspond to the database?  What is the 
recommended way to write a datetime value as a Julian date?

Thanks very much!

RobR



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

[sqlite] how to use tclsqlite3.dll on windows xp 64bits

[cesar rivas]

hello, sqlite users...

Does anyone have problems using tclsqlite3.dll on windows xp 64? I
have no problems using the sqlite tcl brige with no problems with
windows xp 32bits. I use the tcl distro loaded with in Altair
Hyperworks (http://www.altairhyperworks.com/).

In a win64 box, when trying to load the sqlite pacakge, an error is
thrown, which follows: "couldn't load library
"c:/Altairwin64/MAT_MGR/tclsqlite3.dll": invalid argument". the comand
executed is "load c:/Altairwin64/MAT_MGR/tclsqlite3.dll Sqlite3"

 If I use a win32 box, I get NO errors.

Does anybody know to build the tclsqlite3 in 64 bits?


thanks ,

best regads



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

Re: [sqlite] How accept sqlite3 commands from stdin

[Black, Michael (IS)]

sqlite3 main.db < main.txt
 
or
cat main.txt | sqlite3 main.db
 
or
 
echo "create table tbl1(one varchar(10), two smallint);" | sqlite3 main.db
 
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Peng Yu
Sent: Tue 5/25/2010 9:20 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] How accept sqlite3 commands from stdin



I got the following error when I try to read the commands from the
command line. Would you please let me know how to let sqlite3 read
from stdin?

$ cat main.txt
create table tbl1(one varchar(10), two smallint);

$ echo main.txt |sqlite3 main.db
Incomplete SQL: main.txt


--
Regards,
Peng
___
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] Looking for 64-bit pre-built LIB and DLL

[Carl Buhrke]

Hi,

 

I am unsure if I should contact you or look elsewhere.

 

I'm looking for a pre-built, downloadable version of SQLITE3.DLL and
SQLITE3.LIB built for 64-bit windows 7.

 

Can you help me or direct me to where I can find this.

 

Carl Buhrke

 

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

[sqlite] Looking for 64-bit pre-built LIB and DLL

[Carl Buhrke]

Hi,

 

I am unsure if I should contact you or look elsewhere.

 

I'm looking for a pre-built, downloadable version of SQLITE3.DLL and
SQLITE3.LIB built for 64-bit windows 7.

 

Can you help me or direct me to where I can find this.

 

Carl Buhrke

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

[sqlite] How accept sqlite3 commands from stdin

[Peng Yu]

I got the following error when I try to read the commands from the
command line. Would you please let me know how to let sqlite3 read
from stdin?

$ cat main.txt
create table tbl1(one varchar(10), two smallint);

$ echo main.txt |sqlite3 main.db
Incomplete SQL: main.txt


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

Re: [sqlite] sqlite-users Digest, Vol 29, Issue 25

[Art]

Thanks for the suggestions, here's the results:

Sqlite Manager also fails.  
This is a permission issue with the sql open api.  This is a "Shared Folder" 
under VirtualBox with "read only permission".  There should be no reason that 
the openv2 statement with the read_only specified should fail, unless sqlite is 
not setting its permissions correctly when it does the file open.

If the folder is tagged as full access then sql open works.  

Art.




Running in sqlite application in Virtual Box, attempt to open a database with 
sqlite from a shared network folder \\Vboxsvr\testdata  however the open16 and 
openv2 (with read only)  both fail --- rc = 14.  App opens file if moved 
locally to hard drive.

using latest version of sqlite3 3.6.23.1 

this seems to be a bug in the open code of sqlite when accessing a file across 
a network.

Thank you,
Art Zerger
azer...@yahoo.com
===

Hi Art,

I just succeeded in opening a db with a UNC path, under Windows XP Pro, using 
the same version (3.6.23.1) with the command-line utility -- but I do NOT use 
virtual box.

Does the command-line utility fail for you under virtual box?  

Might you attempt it without virtualbox?  

Donald

--

A VBox shared folder is not exactly the best example of
"sharing over a network". The purpose of shared folders is
to copy files to and from virtual machines in an easy way.
I think the locking primitives of VBox shared folders are
not 100%.  You could try again with NFSv4.

-- 
  (  Kees Nuyt
  )
c[_]

--

I don't believe so: I use several bases which I open with MS short UNC 
syntax 200 times a day without any problem ever (using vanilla 3.6.23.1).

Your issue is elsewhere.  Make sure the account you use has enough 
rights to the destination folder and check you can access it with, for 
instance, a innocent application like a hex editor or a third-party 
SQLite manager.


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

Re: [sqlite] File locking with BEGIN/END

[Nick Shaw]

> You need to add your records in smaller batches and sleep just a
little between iterations.
>  
> Your batches will have to be small enough to make the user response
time reasonable.
>  
> You'll find that your loop of inserting records won't take long.  It's
when you do the "COMMIT" that it locks everything and takes all the time
to write to disk.
>  
> Something like this to  COMMIT in batches:
>  
> while(records) {
>   insert records
>   if (recnum%1)==0 COMMIT;BEGIN DEFERRED } COMMIT
>  

Thanks Michael, that's what I thought.  I have tried this and it did
improve things considerably.  What I've got now is a mutex check between
each record insert, and if the second process is waiting on the mutex I
then in the first process I do a commit, then wait on the mutex in the
1st process until the second process has finished then carry on with the
inserts.  Seems to be faster doing it this way (though not by much).
I've got the delay in the second process down to around 200ms which is
much improved from the original delay of over a minute. :)

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

Re: [sqlite] File locking with BEGIN/END

[Nick Shaw]

> Is it possible you have a busy-handler installed? Are you using SQLite
directly or via some wrapper API?

Yes and no.  I set a busy handler of 1 minute, but it takes longer than
this and doesn't timeout, plus I tried without the busy timeout; made no
difference.

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

Re: [sqlite] File locking with BEGIN/END

[Dan Kennedy]

On May 25, 2010, at 10:24 PM, Nick Shaw wrote:

> Hi all,
>
>
>
> I've got a database that is accessed by two processes on the same PC.
> When I add a large number of records to a table from one process (all
> records wrapped in a BEGIN DEFERRED / END so it's committed to disk  
> all
> at once; and we're talking about adding 500,000 recs to a DB  
> containing
> around 3 million recs, so it takes some time), the other process gets
> stuck in the sqlite3_step() call when doing a SELECT call.  It doesn't
> return SQLITE_BUSY, it just sits there waiting.

Is it possible you have a busy-handler installed? Are you
using SQLite directly or via some wrapper API?

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

Re: [sqlite] File locking with BEGIN/END

[Pavel Ivanov]

> According to http://www.sqlite.org/lang_transaction.html, in deferred
> mode, sqlite creates a RESERVED lock on the DB when it starts a write
> operation, but this should allow other SHARE locks at the same time,
> implying other processes should still be able to query the database
> during the write transation.

Also see here http://www.sqlite.org/lockingv3.html - if cache is full
deferred transaction takes EXCLUSIVE lock and spills some changes to
disk even before actual commit.

> Does this sound like correct behaviour, or not?  If correct, should my
> second process be getting SQLITE_BUSY returned from sqlite3_step()?

Yes, everything sounds like correct behavior. And if you have set
busy_timeout to some very big value then sqlite3_step() won't return
SQLITE_BUSY to you until this very big time has passed.

> (Note that the first process is adding data to the same table that the
> second process wants to do a SELECT query on).

This actually doesn't matter if you access to the database from
different processes.

> If this is all correct... what would be the best way to allow me to add
> (or delete!) lots of records from a table in one process whilst still
> allowing the other process to query that table's data during the insert
> time?

You can split your insert transactions - e.g. 1000 records per
transaction, maybe 1 will work well too. This will make the whole
inserting time bigger but will make things better for readers.


Pavel

On Tue, May 25, 2010 at 11:24 AM, Nick Shaw  wrote:
> Hi all,
>
>
>
> I've got a database that is accessed by two processes on the same PC.
> When I add a large number of records to a table from one process (all
> records wrapped in a BEGIN DEFERRED / END so it's committed to disk all
> at once; and we're talking about adding 500,000 recs to a DB containing
> around 3 million recs, so it takes some time), the other process gets
> stuck in the sqlite3_step() call when doing a SELECT call.  It doesn't
> return SQLITE_BUSY, it just sits there waiting.  It only continues after
> the first process calls END to complete the transaction.
>
>
>
> According to http://www.sqlite.org/lang_transaction.html, in deferred
> mode, sqlite creates a RESERVED lock on the DB when it starts a write
> operation, but this should allow other SHARE locks at the same time,
> implying other processes should still be able to query the database
> during the write transation.
>
>
>
> Does this sound like correct behaviour, or not?  If correct, should my
> second process be getting SQLITE_BUSY returned from sqlite3_step()?
>
> (Note that the first process is adding data to the same table that the
> second process wants to do a SELECT query on).
>
>
>
> If this is all correct... what would be the best way to allow me to add
> (or delete!) lots of records from a table in one process whilst still
> allowing the other process to query that table's data during the insert
> time?  An in-memory database is obviously an option for the second
> process, but that has an impact on RAM usage and requiring the second
> process to know when the database has changed on disk.
>
>
>
> Thanks,
>
> Nick.
>
> ___
> 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

Re: [sqlite] File locking with BEGIN/END

[Black, Michael (IS)]

You need to add your records in smaller batches and sleep just a little between 
iterations.
 
Your batches will have to be small enough to make the user response time 
reasonable.
 
You'll find that your loop of inserting records won't take long.  It's when you 
do the "COMMIT" that it locks everything and takes all the time to write to 
disk.
 
Something like this to  COMMIT in batches:
 
while(records) {
  insert records
  if (recnum%1)==0 COMMIT;BEGIN DEFERRED
}
COMMIT
 
 
Michael D. Black
Senior Scientist
Northrop Grumman Mission Systems
 



From: sqlite-users-boun...@sqlite.org on behalf of Nick Shaw
Sent: Tue 5/25/2010 10:24 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] File locking with BEGIN/END



Hi all,



I've got a database that is accessed by two processes on the same PC.
When I add a large number of records to a table from one process (all
records wrapped in a BEGIN DEFERRED / END so it's committed to disk all
at once; and we're talking about adding 500,000 recs to a DB containing
around 3 million recs, so it takes some time), the other process gets
stuck in the sqlite3_step() call when doing a SELECT call.  It doesn't
return SQLITE_BUSY, it just sits there waiting.  It only continues after
the first process calls END to complete the transaction.



According to http://www.sqlite.org/lang_transaction.html, in deferred
mode, sqlite creates a RESERVED lock on the DB when it starts a write
operation, but this should allow other SHARE locks at the same time,
implying other processes should still be able to query the database
during the write transation.



Does this sound like correct behaviour, or not?  If correct, should my
second process be getting SQLITE_BUSY returned from sqlite3_step()?

(Note that the first process is adding data to the same table that the
second process wants to do a SELECT query on).



If this is all correct... what would be the best way to allow me to add
(or delete!) lots of records from a table in one process whilst still
allowing the other process to query that table's data during the insert
time?  An in-memory database is obviously an option for the second
process, but that has an impact on RAM usage and requiring the second
process to know when the database has changed on disk.



Thanks,

Nick.

___
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] File locking with BEGIN/END

[Nick Shaw]

Hi all,

 

I've got a database that is accessed by two processes on the same PC.
When I add a large number of records to a table from one process (all
records wrapped in a BEGIN DEFERRED / END so it's committed to disk all
at once; and we're talking about adding 500,000 recs to a DB containing
around 3 million recs, so it takes some time), the other process gets
stuck in the sqlite3_step() call when doing a SELECT call.  It doesn't
return SQLITE_BUSY, it just sits there waiting.  It only continues after
the first process calls END to complete the transaction.

 

According to http://www.sqlite.org/lang_transaction.html, in deferred
mode, sqlite creates a RESERVED lock on the DB when it starts a write
operation, but this should allow other SHARE locks at the same time,
implying other processes should still be able to query the database
during the write transation.

 

Does this sound like correct behaviour, or not?  If correct, should my
second process be getting SQLITE_BUSY returned from sqlite3_step()?

(Note that the first process is adding data to the same table that the
second process wants to do a SELECT query on).

 

If this is all correct... what would be the best way to allow me to add
(or delete!) lots of records from a table in one process whilst still
allowing the other process to query that table's data during the insert
time?  An in-memory database is obviously an option for the second
process, but that has an impact on RAM usage and requiring the second
process to know when the database has changed on disk.

 

Thanks,

Nick.

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

[sqlite] Bug

[Wiktor Adamski]

SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t(a int);
sqlite> insert into t values(1);
sqlite> select 0 where 0;
sqlite> update t set a = (select 0 where 0);
sqlite> select * from t;

sqlite> select 0 limit 0;
sqlite> update t set a = (select 2 limit 0);
sqlite> select * from t;
2
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Doesn't update return wrong code while there isn't record in table?

[Kees Nuyt]

On Mon, 24 May 2010 18:19:23 -0700 (PDT), liubin liu
<7101...@sina.com> wrote:

>Thank you for you reply, :)
>
>But I see the return errmsg is "unknown error" on sqlite3.6.23-1. 
>What does it mean?

Oops, I didn't read your original post carefully enough and
I defaulted to the standard answer ;)

Anyway, it returns result code 101.
http://www.sqlite.org/c3ref/c_abort.html says:
#define SQLITE_DONE101  /* sqlite3_step() has
finished executing */

Which just means you don't have to execute another
sqlite3_step().
I don't know why sqlite3_errmsg() returns 'unknown error'.
All I can say it is not very useful to call sqlite3_errmsg()
on a correct result.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users