Re: [sqlite] SQL Syntax

On 9/07/2009 2:21 PM, Rick Ratchford wrote: > > Okay, this worked, but I have NO IDEA why. > > SQLString = "SELECT min(Year) FROM TmpTable " & _ > "WHERE Month=1 UNION " & _ > "SELECT max(Year) FROM TmpTable " & _ > "WHERE Month = 12 LIMIT 2"

Re: [sqlite] SQL Syntax

Okay, this worked, but I have NO IDEA why. SQLString = "SELECT min(Year) FROM TmpTable " & _ "WHERE Month=1 UNION " & _ "SELECT max(Year) FROM TmpTable " & _ "WHERE Month = 12 LIMIT 2" While this returned the correct answers: 1988 2008

[sqlite] SQL Syntax

I've been trying all kinds of different ways to accomplish the following, and my head is spinning. Problem: How do you return from the DB just the YEAR of the first and last YEAR that had dates from 1st week of January to last week of December? Clarification: Suppose you had a database that

Re: [sqlite] general question

For searching text files grep can be very handy. A regular expression is powerful. Jay A. Kreibich wrote: > On Wed, Jul 08, 2009 at 11:53:13AM +0100, Simon Slavin scratched on the wall: > >> On 8 Jul 2009, at 11:40am, _h_ wrote: >> >> >>> Does any mechanism is available via which I can

Re: [sqlite] Repost: Accessing a DB while copying it causes Windows to eat virtual memory

A valid backup needs to be a snapshot, It is of no value if other processes were able to modify the file as it was being backed up. An exclusive (write) lock of some type is required or else the file needs to be organized differently. Neville Franks wrote: > A proper backup program that

Re: [sqlite] Getting Complete Years Only

On 9/07/2009 9:33 AM, Rick Ratchford wrote: > Lucky nothing. You're just brilliant. Such humility. :-) > > Yes, it worked wonderfully. The dataset only contains data that does not > fall on weekends. Stock data to be exact. > > It is likely to start sometime during the year of the first year

Re: [sqlite] SQLITE_OMIT_AUTOVACUUM seems to need small change in btree.c

Is this related to your problem? http://www.sqlite.org/cvstrac/tktview?tn=3940 Pavel On Wed, Jul 8, 2009 at 4:06 AM, Yan Bertrand wrote: >                Hello, > > > > I am compiling SQLite with options to reduce the size, and with hopes to > reduce the duration of

Re: [sqlite] unable to backup using command line

Did you try sqlite3 accounts.db ".backup main a.db" (note the quotes)? Pavel On Tue, Jul 7, 2009 at 2:28 PM, Eric Peterson wrote: > > > > C:\InWork\rsm\weekly_status>sqlite3 accounts.db .database > seq  name             file > ---  ---  

Re: [sqlite] Repost: Accessing a DB while copying it causes Windows to eat virtual memory

Eureka! I tried the latest version of sqlite3.exe, and I verified that both the VACUUM and the access when the file is being copied no longer monopolize physical memory. I dug up your "Vista frustrations" thread, and I agree that this seems to be bad behavior on part of the cache manager. If a

Re: [sqlite] Getting Complete Years Only

Lucky nothing. You're just brilliant. Such humility. :-) Yes, it worked wonderfully. The dataset only contains data that does not fall on weekends. Stock data to be exact. It is likely to start sometime during the year of the first year available and end sometime during the year of the last

Re: [sqlite] there is problem when getting data by sqlite3's c apis in signal-handling func

> there is problem when getting data by sqlite3's c apis in signal-handling > func. What problem? And as a hint: are you sure that your SQLite is in correct thread-safety mode and you're not trying to dead-lock your application? Pavel On Mon, Jul 6, 2009 at 9:58 PM, liubin liu<7101...@sina.com>

Re: [sqlite] Getting Complete Years Only

John Machin wrote: > On 9/07/2009 3:39 AM, Igor Tandetnik wrote: >> Rick Ratchford >> wrote: >>> Can someone help me with a SQL request? >>> >>> The Table contains Date, as well as Year, Month and Day columns. >>> >>> I would like to return a

Re: [sqlite] multi-thread access to a db

Ray, Using multiple threads you will have locking contention on the database. Only one thread is allowed to write at a time. If you need concurrent writing then create multiple databases or maybe look into a different DB platform like mysql, postgress or oracle. --- On Wed, 7/8/09,

Re: [sqlite] Getting Complete Years Only

On 9/07/2009 3:39 AM, Igor Tandetnik wrote: > Rick Ratchford > wrote: >> Can someone help me with a SQL request? >> >> The Table contains Date, as well as Year, Month and Day columns. >> >> I would like to return a recordset that is made up of only COMPLETE >> YEARS,

Re: [sqlite] Repost: Accessing a DB while copying it causes Windows to eat virtual memory

I believe the issue was resolved in this ticket: http://www.sqlite.org/cvstrac/tktview?tn=3387 You're being bitten by Vista and Win2008's aggressive cacheing of the database. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of

Re: [sqlite] Repost: Accessing a DB while copying it causes Windows to eat virtual memory

Stan Bielski wrote: > Hello again, > > Copying the database with Explorer and accessing it via sqlite is just > a contrived example that exhibits the same problem I'm having in my > application. The app does make a copy of the DB, but it has app-layer > locking that will prevent modifications

Re: [sqlite] multi-thread access to a db

If I remove the locking_mode=exclusive, I don't get those errors. I'd appreciate any advice on how I can get the best performance using multiple threads in my application, given that: 1. I need maximum performance. That is also why I need multiple threads 2. All threads need to write to

Re: [sqlite] Low-Cost data migration and ETL tools

Rstat wrote: > Hi, Im building a database for my company. We are a rather small size book > company with a lot of references and still growing. > > We have a Mysql database here and are trying to find some good tools to use > it at its best. Basically we are just starting up the database after

Re: [sqlite] Repost: Accessing a DB while copying it causes Windows to eat virtual memory

Hello again, Copying the database with Explorer and accessing it via sqlite is just a contrived example that exhibits the same problem I'm having in my application. The app does make a copy of the DB, but it has app-layer locking that will prevent modifications unless someone decides to start

[sqlite] multi-thread access to a db

I have an application where I have 7 threads. Each thread opens its own db connection object, but the connections are to the same db. I am seeing sporadic insert failures when a thread attempts to insert into the db. sqlite3_errmsg returns this message: database is locked I am using

Re: [sqlite] Getting Complete Years Only

You're a genius Igor. Thank you very much! Much easier than what I was thinking. Rick -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Wednesday, July 08, 2009 12:40 PM To: sqlite-users@sqlite.org

Re: [sqlite] Getting Complete Years Only

Rick Ratchford wrote: > Can someone help me with a SQL request? > > The Table contains Date, as well as Year, Month and Day columns. > > I would like to return a recordset that is made up of only COMPLETE > YEARS, from January to December. > > Say my data starts on

[sqlite] Getting Complete Years Only

Can someone help me with a SQL request? The Table contains Date, as well as Year, Month and Day columns. I would like to return a recordset that is made up of only COMPLETE YEARS, from January to December. Say my data starts on August 14, 1975 and ends with May 4, 2009. How do I get just

Re: [sqlite] Repost: Accessing a DB while copying it causes Windowsto eat virtual memory

On 8 Jul 2009, at 1:44pm, Hoover, Jeffrey wrote: > Just to be clear, I think Windows is really the one that is at fault > here; the behavior seems very broken to me. Unfortunately, I need the > app to work on Windows without this happening, so I need to figure out > some kind of workaround in

[sqlite] SQLite 3.6.16 Crashed on windows XP (with FTS3 table search)

Hello, I got crash with SQLite FTS3 table search version 3.6.16. I am using SQLite fts3 table to INDEX the data. Crash occurred while being the executing the sqllite3_step() API. I have noticed that this crash is happening with special search string "1-2.3" (which contains hypen and

Re: [sqlite] general question

On Wed, Jul 08, 2009 at 07:19:02PM +0530, _h_ scratched on the wall: > Hi Jay, > Its nice to hear that you already did that. > > > If you're willing to write a bit of code, you can do whatever you want. > I can do that, can share some idea how it can be done. Read up on how build a virtual

Re: [sqlite] Repost: Accessing a DB while copying it causes Windows to eat virtual memory

> Just to be clear, I think Windows is really the one that is at fault > here; the behavior seems very broken to me. Unfortunately, I need the > app to work on Windows without this happening, so I need to figure out > some kind of workaround in a.) Windows settings b.) the sqlite source > or c.)

Re: [sqlite] general question

Hi Jay, Its nice to hear that you already did that. > If you're willing to write a bit of code, you can do whatever you want. I can do that, can share some idea how it can be done. Thank you. On Wed, Jul 8, 2009 at 7:12 PM, Jay A. Kreibich wrote: > On Wed, Jul 08, 2009 at

Re: [sqlite] general question

On Wed, Jul 08, 2009 at 11:53:13AM +0100, Simon Slavin scratched on the wall: > > On 8 Jul 2009, at 11:40am, _h_ wrote: > > > Does any mechanism is available via which I can bind the db with > > text file > > and can use the db apis to access that text file, and can perform > > the i/o. > >

Re: [sqlite] general question

There are Free and Commercial ODBC Drivers for Text / CSV files out there. -- kjh On 07/08/2009 07:06 AM, P Kishor wrote: > On Wed, Jul 8, 2009 at 2:00 PM, _h_ wrote: >> Hi Kishor, >> Thank your pointing out perl db module. >> Is the same things are avaiable as C/C++

Re: [sqlite] Repost: Accessing a DB while copying it causes Windowsto eat virtual memory

Why not just make the file read-only before copying it, then restoring write-access when the copy finishes? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Stan Bielski Sent: Tuesday, July 07, 2009 6:39 PM To: General

Re: [sqlite] general question

On Wed, Jul 8, 2009 at 2:00 PM, _h_ wrote: > Hi Kishor, > Thank your pointing out perl db module. > Is the same things are avaiable as C/C++ apis. I have no idea. Google is your friend. Although, if you are messing around with text files, Perl is probably way more

Re: [sqlite] general question

Hi Kishor, Thank your pointing out perl db module. Is the same things are avaiable as C/C++ apis. Thank you. On Wed, Jul 8, 2009 at 5:25 PM, P Kishor wrote: > On Wed, Jul 8, 2009 at 1:48 PM, _h_ wrote: > > I am looking for some thing where via db

Re: [sqlite] general question

On Wed, Jul 8, 2009 at 1:48 PM, _h_ wrote: > I am looking for some thing where via db apis I inform the format of my text > file and then I can do open/close, read/write via db apis to interact with > the underlaying text file. > I require to access the text files, which

Re: [sqlite] general question

I am looking for some thing where via db apis I inform the format of my text file and then I can do open/close, read/write via db apis to interact with the underlaying text file. I require to access the text files, which has different formats, via unified apis. Thank you. On Wed, Jul 8, 2009 at

Re: [sqlite] Repost: Accessing a DB while copying it causes Windows to eat virtual memory

On 8 Jul 2009, at 9:48am, Neville Franks wrote: > A proper backup program that opened the file for r/o, non-exclusive > use may be able to copy the file. But under many operating systems it runs the risk of copying the first half of the file, then allowing a change to be made, then copying

Re: [sqlite] general question

On 8 Jul 2009, at 11:40am, _h_ wrote: > Does any mechanism is available via which I can bind the db with > text file > and can use the db apis to access that text file, and can perform > the i/o. For SQLite to be useful for you, the data must be in a SQLite database file. You cannot

Re: [sqlite] Low-Cost data migration and ETL tools

On 8 Jul 2009, at 10:05am, Rstat wrote: > We have a Mysql database here This is a SQLite mailing list. We talk about MySQL only in relation to SQLite. > and are trying to find some good tools to use > it at its best. Basically we are just starting up the database after > dealing > with

[sqlite] SQLITE_OMIT_AUTOVACUUM seems to need small change in btree.c

Hello, I am compiling SQLite with options to reduce the size, and with hopes to reduce the duration of some operations on portable devices. This includes the use of the compilation switch SQLITE_OMIT_AUTOVACUUM. I noticed that btree.c will not compile withmy current

[sqlite] unable to backup using command line

C:\InWork\rsm\weekly_status>sqlite3 accounts.db .database seq name file --- --- --- 0main C:\InWork\rsm\weekly_status\accounts.db C:\InWork\rsm\weekly_status>sqlite3 accounts.db .help But I can not do the

[sqlite] general question

Hi, Does any mechanism is available via which I can bind the db with text file and can use the db apis to access that text file, and can perform the i/o. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] vitual tables

Hi, Can you give one simple example, about how to use "virtual tables" in sqlite3? Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance issue in using 'LIKE' with Indexes.

On 8/07/2009 7:11 PM, aalap shah wrote: > Hi, > > I am a new user to sqlite3, I have a program that searches through a > database. I have a table with 1 column as varchar and I want to > perform a search on it. > I have created an index over that column. And I use a select query > with

Re: [sqlite] Performance issue in using 'LIKE' with Indexes.

On Jul 8, 2009, at 4:11 PM, aalap shah wrote: > Hi, > > I am a new user to sqlite3, I have a program that searches through a > database. I have a table with 1 column as varchar and I want to > perform a search on it. > I have created an index over that column. And I use a select query > with

[sqlite] Performance issue in using 'LIKE' with Indexes.

Hi, I am a new user to sqlite3, I have a program that searches through a database. I have a table with 1 column as varchar and I want to perform a search on it. I have created an index over that column. And I use a select query with "column_name LIKE 'a%' ". So my first question is will this

[sqlite] Low-Cost data migration and ETL tools

Hi, Im building a database for my company. We are a rather small size book company with a lot of references and still growing. We have a Mysql database here and are trying to find some good tools to use it at its best. Basically we are just starting up the database after dealing with Excel: we

Re: [sqlite] Repost: Accessing a DB while copying it causes Windows to eat virtual memory

A proper backup program that opened the file for r/o, non-exclusive use may be able to copy the file. I thought SQLite has a live backup capability now. Surely that is the best way to handle this. Wednesday, July 8, 2009, 6:16:41 PM, you wrote: JS> It looks like you are trying to copy from a

Re: [sqlite] Repost: Accessing a DB while copying it causes Windows to eat virtual memory

It looks like you are trying to copy from a process other than the one which holds the lock. Think abnout it - the lock gives exlusive access to the file to the process which sets it and blocks all other processes. Stan Bielski wrote: > Just to make sure I understood you correctly, is this

[sqlite] Comparision of string to integer

Hello, I am using sqlite database for android app development. In that I am trying to fetch records which have value 0 in read field. For that I am using following function. public static final String[] KEY_STATUS_FOR_UNREAD = {"read"}; Cursor mCursor = mDb.query(DATABASE_TABLE ,

Re: [sqlite] Repost: Accessing a DB while copying it causes Windows to eat virtual memory

I might missed that but: what are you trying to acomplish by using explorer to copy the database file ? I guess you are doing it for backup purpose. For this, be aware that it might be dangerous to do a simple file copy on a running database since you might forget some temporary files used by