Re: [sqlite] Data structure

2007-04-11 Thread John Stanton
I used an approach similar to the Bloom Filter for data retrieval. It could be very fast at retrieving substrings from large data sets but was fairly complex to implement. I would not go with that approach unless you had some very broad retrieval requirements and a very large data set.

Re: [sqlite] Data structure

2007-04-11 Thread John Stanton
Lloyd wrote: On Wed, 2007-04-11 at 10:00 -0500, P Kishor wrote: I think, looking from Lloyd's email address, (s)he might be limited to what CDAC, Trivandrum might be providing its users. Lloyd, you already know what size your data sets are. Esp. if it doesn't change, putting the entire

Re: [sqlite] Data structure

2007-04-12 Thread John Stanton
suggest a good tool for performance measurement (on Linux) ? On Wed, 2007-04-11 at 10:35 -0500, John Stanton wrote: You might discover that you can craft a very effective memory resident storage system using a compression system like Huffman Encoding and an index method appropriate to t

Re: [sqlite] Performance analysis of SQLite statements

2007-04-12 Thread John Stanton
What do you want to measure? Jonas Sandman wrote: Anyone know a good bench-marking (preferably free or cheap) which can be used to benchmark C/C++ code in Windows? Best regards, Jonas On 4/12/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote: Are there any tools to help analyze the performance

Re: [sqlite] Still getting "Insertion failed because database is full." errors

2007-04-13 Thread John Stanton
Are you sure that you are not exceeding the capacity of the flash memory to handle writes? Joel Cochran wrote: Hi folks, I had sent this message out a couple of weeks ago, and I am still searching for a solution. I looked at the application design and made a modest change: I now create a

Re: AW: [sqlite] Still getting "Insertion failed because database is full." errors

2007-04-13 Thread John Stanton
If something passes all tests but fails later then it is very likely failing where testing was not performed, such as the hardware under other conditions. Michael Ruck wrote: Hi, Is this the only device seeing this error or are *all* devices seeing this error? Have you checked the CF card?

Re: [sqlite] Still getting "Insertion failed because database is full." errors

2007-04-13 Thread John Stanton
Regular flash memory has a limited number of write cycles before it fails. Are you hitting this problem by using it for general processing? Joel Cochran wrote: First, to answer John's question: the CF Card is a 1GB card, and the only thing on the card is the SQLite Database (currently 509KB),

Re: [sqlite] Supporting databases larger than 2GB on FAT filesystems?

2007-04-13 Thread John Stanton
It is limited by the maximum file size on your OS. You can make a multiple file database by ATTACHing more than one database. Ludvig Strigeus wrote: Does Sqlite support databases larger than 2GB on FAT filesystems? If not, how hard would it be to add so it uses additional files for the pages

Re: [sqlite] Still getting "Insertion failed because database is full." errors

2007-04-13 Thread John Stanton
can only be used so much? That might apply to this scenario, these cards have been written over continuously for the last 6 months. Joel On 4/13/07, John Stanton <[EMAIL PROTECTED]> wrote: Regular flash memory has a limited number of write cycles before it fails. Are you hitting this probl

Re: [sqlite] Supporting databases larger than 2GB on FAT filesystems?

2007-04-13 Thread John Stanton
Use a file system with 64 bit addressing. Ludvig Strigeus wrote: I would like to have a single table larger than 2GB...though. /Ludvig On 4/13/07, John Stanton <[EMAIL PROTECTED]> wrote: It is limited by the maximum file size on your OS. You can make a multiple file database by ATT

Re: [sqlite] Still getting "Insertion failed because database is full." errors

2007-04-13 Thread John Stanton
You might find some joy in the baby disk drives such as installed in the original ipods. Can you substitute RAM with a battery backup if the memory card is always in the device? Joel Cochran wrote: Thanks John and Dennis, At least now I have something to look at. I will look into the CF

Re: [sqlite] Non-amalgamized version

2007-04-15 Thread John Stanton
Just pick up the regular distribution. The link is http://www.sqlite.org/sqlite-3.3.15.tar.gz Everything you need to build Sqlite on a variety of platforms is there. Well commented open source makes it simple to extend or modify. Ludvig Strigeus wrote: Hi, I want a non-amalgamized

Re: [sqlite] Still getting "Insertion failed because database is full." errors

2007-04-17 Thread John Stanton
gt; card, > > when you receive the first write error. This is (approximately) the > number > > of bytes > > the card can store (at that point in time) and falling. > > > > It seems some cards even return "read errors", when they hit a defective > > sector &

Re: [sqlite] linux and java

2007-04-18 Thread John Stanton
Try Ubuntu. It is gaining raving fans. Alex McFerron wrote: I need to get a laptop up and running with linux, java, and sql lite any suggestions on the fastest way, the best linux distribution, the code to connect java to sql lite? I think I can find the code to connect java to sql lite on

Re: [sqlite] DB design questions

2007-04-20 Thread John Stanton
We do something like that by storing the data in TEXT format and using RCS to handle versioning by its reverse delta method. It works well for storing notes and may be useful in your application. A function can return the appropriate version. Michael Ruck wrote: Hello, I'm currently

Re: [sqlite] Data structure

2007-04-20 Thread John Stanton
Lloyd, If you want some code examples contact me and I shall send you some. [EMAIL PROTECTED] Lloyd wrote: Thank you John Stanton. This has opened new doors for me, and think it would be helpful for others in the list too.. Thanks and Regards Lloyd On Thu, 2007-04-12 at 12:34 -0500, John

Re: [sqlite] SQL query, finding out which row a result is in

2007-04-22 Thread John Stanton
Why not use a "where member_id = '4567373'? Gilles Roy wrote: Given a arbitrary statement, I need to find out which row a specific result is in, as efficiently as possible. The arbitrary statement can order the results any way it wants. For example, imagine statement X which returns a column

Re: [sqlite] SQL query, finding out which row a result is in

2007-04-23 Thread John Stanton
You don't have to read into a memory array. How about just running through your selection with an sqlite3_step and counting the rows? Gilles Roy wrote: On Sun, Apr 22, 2007 at 05:33:43PM -0500, P Kishor wrote: On 4/22/07, Gilles Roy <[EMAIL PROTECTED]> wrote: Given a arbitrary statement,

[sqlite] TRANSACTION

2007-04-23 Thread John Stanton
I am not sure how to proceed with handling multiple SQL statements. Perhaps someone has some experiences they would be kind enough to share. I want to store multi-statement SQL to implement an entire transaction in the form - BEGIN statement statement ... COMMIT I can

Re: [sqlite] Re: TRANSACTION

2007-04-23 Thread John Stanton
Thanks Igor, much obliged. That fits my application quite elegantly. Igor Tandetnik wrote: John Stanton <[EMAIL PROTECTED]> wrote: I want to store multi-statement SQL to implement an entire transaction in the form - BEGIN statement statement ... COMMIT I c

Re: [sqlite] TRANSACTION

2007-04-23 Thread John Stanton
Thanks for the input and confirming my analysis. I am implementing a remote procedure call capability and keep a library of SQL transactions to be executed by a remote client. Dennis Cote wrote: John Stanton wrote: I am not sure how to proceed with handling multiple SQL statements. Perhaps

Re: [sqlite] Odd performance issue under Windows - correction

2007-04-27 Thread John Stanton
John Elrick wrote: John Elrick wrote: John Elrick wrote: Griggs, Donald wrote: John Elrick wrote: "what the heck is happening that is creating a better than order of magnitude difference in execution time on five out of seven Windows machines?". John, If the database is

Re: [sqlite] Odd performance issue under Windows - correction

2007-04-27 Thread John Stanton
John Elrick wrote: John Stanton wrote: The real time with the pragma off is 1.78 seconds. The real time on the "faster" machine is 8.4 seconds. When I set the synchronous pragma to off on the "faster" machine, the time drops to 1.64. John Do your various machines us

Re: [sqlite] Odd performance issue under Windows - correction

2007-04-27 Thread John Stanton
John Elrick wrote: John Stanton wrote: John Elrick wrote: John Stanton wrote: I would look at the disk controller/disk drive hardware and the software driver to see if they are reporting correctly to the OS. Some of your numbers are too fast for regular disk technology and suggest

Re: [sqlite] sqlite and borland c++ builder

2007-04-29 Thread John Stanton
Why not use gcc to compile your library, or use a precompiled DLL? Jonathan Kahn wrote: Hi Ulrik, Thank you for responding. I'll try anything! The frustration that all this has brought me I am determined to solve it. If I built SQLite with a C compiler what would be the result? What

Re: AW: [sqlite] sqlite and borland c++ builder

2007-04-29 Thread John Stanton
again to solve that problem, but it remains there too. I would recommed patching up sqlite3.h to conform to BC++ requirements - changing those structs to something the compiler understands. Mike -Urspr├╝ngliche Nachricht- Von: John Stanton [mailto:[EMAIL PROTECTED] Gesendet: Sonntag, 29

Re: [sqlite] Makefile

2007-05-02 Thread John Stanton
You can run strip on the file. Ken wrote: Is there a way to disable the -g flag for the library? I've found that the version compiled without the -g flags is about 3 times smaller (right around the 500k mark) but the default compile is about 1.7 meg! Is there a way to tell the Make to

Re: [sqlite] Makefile

2007-05-02 Thread John Stanton
Just removing the -g will not get rid of all the debugging information so strip will still give you a smaller executbale file. Danilo wrote: ...or you can find and delete " -g" from the Makefile! John Stanton ha scritto: You can run strip on the file. Ken wrote: Is there a way

Re: [sqlite] stmt question

2007-05-02 Thread John Stanton
Variables are bound until you issue a reset or finalize. Jonathan Kahn wrote: Hi, I have a couple questions about using prepared statements with sqlite. If I prepare a statement can I bind variables as my value and then set the variables in a loop and execute? Or in my loop would I

Re: [sqlite] May I ask why the source distribution mechanism was changed starting with 3.3.14?

2007-05-04 Thread John Stanton
I did a comparison some time back between gcc and IBM's Xlc. The IBM compiler was a bit slower to compile but the fully optimized executables were quite different in performance. Xlc's executable ran 40% faster. A look at the generated code showed that the IBM optimizer was carefully matched

Re: [sqlite] I'm Starving for New User Information

2007-05-08 Thread John Stanton
km4hr wrote: Is there a sqlite introduction for programmers wanting to use the sqlite C API? The info on the web site is pretty sparse. There seems to be plenty of info regarding the use of sqlite3 all over the web. But not much on getting set up to write programs that use sqlite. I have

Re: [sqlite] Limiting the size of a database?

2007-05-08 Thread John Stanton
Try the traditional way and use disk partitions/filesystems. Joe Wilson wrote: --- Ron Stevens <[EMAIL PROTECTED]> wrote: Is it possible to tell SQLite to limit the size that a database may grow to? It would be useful for storage constrained applications. This is a tricky problem. What

Re: [sqlite] A suggestion

2007-05-09 Thread John Stanton
That program does have the capability, but may not be implemented that way on Windows. Why not make the change yourself? A.J.Millan wrote: As a suggestion, and even in the risk to abuse of Mr Hipp's patience. Would it be possible to include in the command-line program (sqlite3.exe) the

Re: [sqlite] A suggestion

2007-05-09 Thread John Stanton
Rich Shepard wrote: On Wed, 9 May 2007, John Stanton wrote: That program does have the capability, but may not be implemented that way on Windows. Why not make the change yourself? A.J.Millan wrote: As a suggestion, and even in the risk to abuse of Mr Hipp's patience. Would

Re: [sqlite] My HPUX Notes

2007-05-10 Thread John Stanton
Markus Hoenicka wrote: Quoting km4hr <[EMAIL PROTECTED]>: I just happened to notice that I may not be executing the sqlite installation process (configure/make/make install) in a full "bash" environment. My usual environment is "ksh". Typing in the command "/bin/OpenSource/bin/bash" I get a

Re: [sqlite] Odd results return by SELECT query WHERE word = "word"

2007-05-11 Thread John Stanton
'word' is correct SQL, "word" is not. Matteo Vescovi wrote: Hi, I am getting weird results when executing a query that has this WHERE clause: WHERE word = "word". The query works fine if I use WHERE word = 'word'. The following illustrates the problem: [EMAIL PROTECTED]:~$ sqlite -version

Re: [sqlite] One more SQLite threading question

2007-05-17 Thread John Stanton
Ed Pasma wrote: However, it would be too time consuming to serialize every call to sqlite3_step(), so I wonder whether it can be called in another thread. This almost immediately raises "library routine called out of sequence". It occurs as soon as the processing of A and B overlap, that

Re: [sqlite] One more SQLite threading question

2007-05-19 Thread John Stanton
You can get what you want right now. It is called PostgreSQL. Ken wrote: I would be interested in a version of SQLITE that handled threading in a much cleaner way. I have a need for a single process version that is threaded. But, where SQLITE locking is concerned each thread is really like

Re: [sqlite] One more SQLite threading question

2007-05-19 Thread John Stanton
Doug Nebeker wrote: Yes I did the same experiment with a lock that made thread A wait until B was finished. So actually only one thread can be active at the time. I don't see how the outcome of this experiment can be of any interest, as there is no time reduction any longer. But your

Re: [sqlite] Sqlite Server

2007-05-27 Thread John Stanton
There is no Sqlite Server unless you use a third party product. Sqlite is a library which links into your application. noname wrote: I am using SQL Server as a back end in my vb6 application i want to switch over to sqlite server but terrainformatica.com site has not provided rates for

Re: [sqlite] Embedded SQL in C

2007-05-27 Thread John Stanton
The Sqlite BIND capability makes implementing an embedded SQl interface quite a simple operation. We use embedded SQL with Sqlite but do it within a proprietary language. It was almost trivial to implement. Leif Jensen wrote: Hi, In a larger project we are using PostgreSQL database and

Re: [sqlite] Embedded SQL in C

2007-05-27 Thread John Stanton
. Leif Jensen wrote: That sounds very interesting. Could you please elaborate a little more on that ? Leif John Stanton wrote: The Sqlite BIND capability makes implementing an embedded SQl interface quite a simple operation. We use embedded SQL with Sqlite but do it within a proprietary

Re: [sqlite] Re: CAST

2007-05-28 Thread John Stanton
Robert Simpson wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, May 28, 2007 9:11 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Re: CAST SQLite does not have a dedicated DATE type. I know that, but why it does't create appropriate

Re: [sqlite] Re: CAST

2007-05-28 Thread John Stanton
Read about manifest typing and it will become clear. [EMAIL PROTECTED] wrote: SQLite does not have a dedicated DATE type. I know that, but why it does't create appropriate column definition ? create table tab(col date); creates a table with "date" type. create table tab2 as select * from

Re: [sqlite] Re: CAST

2007-05-29 Thread John Stanton
Robert Simpson wrote: -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Monday, May 28, 2007 4:21 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Re: CAST We actually do that with our Sqlite interfaces. We use the declared type to specify the type and perform

Re: [sqlite] Join

2007-05-29 Thread John Stanton
See the ATTACH statement. Shilpa Sheoran wrote: Does sqlite allow joining tables in different database files using triggers or any other mechanism? Does it affect the performance? Thanks Shilpa - To unsubscribe,

Re: [sqlite] How to restrict the peak heap usage during multiple inserts and updates?

2007-05-29 Thread John Stanton
Since you are only using part of Sqlite have you considered using a much smaller footprint storage system which only implements the functions you are using? Kalyani Tummala wrote: Hi joe, Thanks for your response. In order to reduce the footprint size, I have bypassed parser completely

Re: [sqlite] Re: CAST

2007-05-29 Thread John Stanton
Robert Simpson wrote: -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 29, 2007 6:18 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Re: CAST Your comments endorse the approach we took which was to avoid the wrapper concept entirely with its

Re: [sqlite] How to restrict the peak heap usage during multiple inserts and updates?

2007-05-29 Thread John Stanton
in advance Kalyani -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 29, 2007 6:51 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to restrict the peak heap usage during multiple inserts and updates? Since you are only using part

Re: [sqlite] Re: CAST

2007-05-29 Thread John Stanton
Robert Simpson wrote: -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 29, 2007 8:40 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Re: CAST You have just given an excellent explanation of why the wrapper approach is flawed. Think about

Re: [sqlite] Re: CAST

2007-05-31 Thread John Stanton
a particular concept like .NET would be a tragedy. You might consider developing an SQL engine ideally adapted to .NET. Robert Simpson wrote: -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 29, 2007 3:56 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Re

Re: [sqlite] How to restrict the peak heap usage during multiple inserts and updates?

2007-05-31 Thread John Stanton
modifications? Regards Kalyani -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 29, 2007 9:25 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to restrict the peak heap usage during multiple inserts and updates? In your case we would not use

Re: [sqlite] Re: CAST

2007-05-31 Thread John Stanton
John Elrick wrote: Michael Schlenker wrote: A. Pagaltzis schrieb: * Samuel R. Neff <[EMAIL PROTECTED]> [2007-05-30 14:55]: SQLite's typelessness is an asset if you work only with SQLite but in any application that uses multiple database engines of which SQLite is only one supported engine,

Re: [sqlite] Re: CAST

2007-05-31 Thread John Stanton
John Elrick wrote: John Stanton wrote: John Elrick wrote: SNIP Introspection would occur via this mechanism and would even move all introspection for any given system behind a common interface. Just a thought. John Elrick CREATE TABLE already stores the type as its declared type

Re: [sqlite] Re: CAST

2007-05-31 Thread John Stanton
Robert Simpson wrote: -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Thursday, May 31, 2007 4:08 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Re: CAST You have explained the problem, which is .NET not Sqlite. You have apparently done a fine job marrying

Re: [sqlite] Concurrency

2007-06-01 Thread John Stanton
We find that synchronizing access to database writes using a mutex works well. You could think of implementing read and write locks using the thread primitives and achieve a better result. If you do poll to resolve busy checks a spinlock is certainly a bad idea. When we use that approach we

Re: [sqlite] Concurrency

2007-06-01 Thread John Stanton
Tom Briggs wrote: I don't want to use other database, because I think Sqlite is great for an embedded system that I am using. I think that your own questions about concurrency prove this incorrect. If you need high concurrency and you don't like retries, SQLite is not the database

Re: [sqlite] Concurrency

2007-06-01 Thread John Stanton
Samuel R. Neff wrote: If option (b), using a single thread for writing and a multi-threaded write queue works in your situation, then that would probably provide best concurrency and performance. The only downside to this is the delayed writes mean you don't as easily get feedback to the

Re: [sqlite] Concurrency

2007-06-01 Thread John Stanton
uch a beast. Can anyone see any pitfalls to such an approach? Ian On 6/1/07, John Stanton <[EMAIL PROTECTED]> wrote: > > Tom Briggs wrote: > > > > > >>I don't want to use > >>other database, because I think Sqlite is great for an > >>embedd

Re: [sqlite] extracting and comparing dates

2007-06-04 Thread John Stanton
Chris Fonnesbeck wrote: I'm at a complete loss about how to work with dates in SQLite. The documentation doesnt seem to be helping me. I have a table with some date fields, in the proper -mm-dd format: sqlite> select First_Capture from PIT_manatees limit 5; 1997-6-17 1998-5-6 1997-6-24

Re: [sqlite] baffled by dates

2007-06-04 Thread John Stanton
Sqlite does have a date format, it is physically a 64 bit floating point number. There are functions to transform in and out of that format to present dates as required by the user. The Sqlite date format uses a magib epoch which matches all of the major internaional date systems. P Kishor

Re: [sqlite] extracting and comparing dates

2007-06-04 Thread John Stanton
We use declared types of DATE, TIMESTAMP and DATETIME and store dates as floating point using the Sqlite date conversion functions. The applications get dates formatted as ISO8601 or according to the declared locale. Functions do date artithmetic. Samuel R. Neff wrote: SQLite doesn't have

Re: [sqlite] baffled by dates

2007-06-04 Thread John Stanton
Chris Fonnesbeck wrote: On 6/4/07, P Kishor <[EMAIL PROTECTED]> wrote: There is no "DATE" format in SQLite. Dates are stored as strings. The only formats SQLite knows and understands are TEXT, REAL, INTEGER, BLOB, and NULL (see the link on datatypes). On the other hand, there are built-in

Re: [sqlite] sqlite function list?

2007-06-04 Thread John Stanton
Scott Baker wrote: Is there a list somewhere (I can't find it on the wiki) of all the functions (specifically math) functions that sqlite understands? I'm thinking things like: int, round, floor, ceil, sqrt etc. You have the source. They are all presented there and you can add more if you

Re: [sqlite] baffled by dates

2007-06-04 Thread John Stanton
Look at the date functions, the file date.c is self explanatory and lists the reference for the date type. The underlying type for a date is a float, so that may be how you missed the date details. P Kishor wrote: On 6/4/07, John Stanton <[EMAIL PROTECTED]> wrote: Sqlite does have

Re: [sqlite] baffled by dates

2007-06-04 Thread John Stanton
into business applications were it supports a fixed point decimal type with defined precision and scale. Joe Wilson wrote: --- John Stanton <[EMAIL PROTECTED]> wrote: Sqlite does have a date format, it is physically a 64 bit floating point number. There are functions to transform in a

Re: [sqlite] Sorted index

2007-06-05 Thread John Stanton
B-Tree indices are in sorted sequence. Just raise an index on the column. [EMAIL PROTECTED] wrote: I would like to maintain a sorted list of ~3000 entries. I know that I can create a table and the SELECT from it with the ORDER BY clause in order to sort it. However I do not want the overhead

Re: [sqlite] disk I/O error

2007-06-06 Thread John Stanton
Arun Bhalla wrote: Hello, This message may be off-topic in that I don't think the following issue corresponds to a bug with SQLite, but it's something I've discovered while using SQLite. Perhaps someone on the list has had a similar experience or could make a suggestion. A Linux 2.6/x86_64

Re: [sqlite] Re: Re: Re: Does sqlite3_step searches for a row in the table / or some results buffer?

2007-06-07 Thread John Stanton
Igor Tandetnik wrote: B V, Phanisekhar <[EMAIL PROTECTED]> wrote: Why it's unpredictable? Why can't the unpredictable be made predictable? Please feel free to submit a patch, if you believe it's that easy. Assume I update the column of a row that meets the criteria of some select stmt

Re: [sqlite] Re: Re: Does sqlite3_step searches for a row in the table / or some results buffer?

2007-06-07 Thread John Stanton
Predictability is ensured by using transactions. By using BEGIN and COMMIT to make transactions atomic you enforce a predictable state. B V, Phanisekhar wrote: Thanks Igor, Why it's unpredictable? Why can't the unpredictable be made predictable? Assume I update the column of a row that

[sqlite] FTS-2

2007-06-07 Thread John Stanton
I have just started to use FTS2 and it is working well but I would like to ask any other users if they have had good or bad experiences and why they would use FTS2 rather than FTS1. The software is new and I have not seen any feedback at this stage and we are yet to apply large data sets and

Re: [sqlite] FTS-2

2007-06-07 Thread John Stanton
Scott Hess wrote: On 6/7/07, John Stanton <[EMAIL PROTECTED]> wrote: I have just started to use FTS2 and it is working well but I would like to ask any other users if they have had good or bad experiences and why they would use FTS2 rather than FTS1. The software is new and I have no

Re: [sqlite] Re: Re: Re: Does sqlite3_step searches for a row in the table / or some results buffer?

2007-06-07 Thread John Stanton
Igor Tandetnik wrote: John Stanton <[EMAIL PROTECTED]> wrote: Predictability is ensured by using transactions. By using BEGIN and COMMIT to make transactions atomic you enforce a predictable state. Not if you modify the same data you are iterating over, on the same DB connection an

Re: [sqlite] Syntax help with UPDATE in SQLite Database Browser

2007-06-07 Thread John Stanton
You don't seem to be positioning on a row in the Parameter table with a WHERE clause. Ellis Robin (Bundaberg) wrote: Could I please get some help on the syntax required to perform my UPDATE based on a selection from multiple tables? I've been through the archives but can't seem to make much

Re: [sqlite] Truncate Issue

2007-06-07 Thread John Stanton
If you don't truncate a file then you may have untruncated files. Why can't you truncate a file? It is implemented one way or another on pretty much every OS. Sqlite uses truncate in it b-tree logic and probably elsewhere so you would very likely encounter problems with no truncate. Jimmy

Re: [sqlite] Re: Why is there no sqlite3_exec16() method?

2007-06-11 Thread John Stanton
You can keep the prepared SQl and re-use it by using sqlite3_reset. Rob Richardson wrote: Igor, Thank you very much for your reply. My naïve impression was that sqlite3_prepare/step/finalize are used for SELECT statements, where there would be a result set one would want to step through, and

Re: [sqlite] Database replication question

2007-06-12 Thread John Stanton
[EMAIL PROTECTED] wrote: - Original Message From: Joe Wilson <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Monday, June 11, 2007 8:36:32 PM Subject: Re: [sqlite] Database replication question Large bulk inserts with more than one index (implicit or explicit) is not SQLite's

Re: [sqlite] MMap On Solaris

2007-06-12 Thread John Stanton
Mitchell Vincent wrote: Working with some data conversion here (that will eventually go into an SQLite database). I'm hoping you IO wizards can offer some help on a question that I've been trying to get answered. I'm using Solaris 10 for this. If I mmap a large file and use madvise with

Re: [sqlite] MMap On Solaris

2007-06-13 Thread John Stanton
a good point that the vm page fault is probably faster than the overhead of copying the data to a local buffer. So, page fault or not, I think that's the way I'm going to do it. Again, thanks very much for your input! On 6/12/07, John Stanton <[EMAIL PROTECTED]> wrote: Mitchell Vincent

Re: [sqlite] How can I convert from Julian time to a tm structure?

2007-06-14 Thread John Stanton
We just lifted the routines out of Sqlite to do that. They are in date.c. By making an Sqlite-style date type and a small library of date manipulation routines we move date conversion to the application. It is handy when handling ISO8601 and HTTP date formats plus integrating with file

Re: [sqlite] MMap On Solaris

2007-06-14 Thread John Stanton
;read" but I thought that maybe MMAP might give better performance especially if the OS would just provide the written buffers performed by Process A to Process B's address space that is MMAPed. Thanks for any guidance. Ken John Stanton <[EMAIL PROTECTED]> wrote: MMAP just lets you

Re: [sqlite] Trigger update of multiple columns

2007-06-18 Thread John Stanton
A traditional goal in database design is to place data in "Third Normal Form" which means in essence that each data element is only stored in one place. Acesses join the rows to deliver data. A normalized database does not hold redundant data and changing the value of one element changes its

Re: [sqlite] Journal File Optimization

2007-06-18 Thread John Stanton
Andre du Plessis wrote: How can one optimize the creation of the journal file. The problem is this, for our system which is an event based one each message needs to be insterted and committed to the database (guaranteed), this results in a commit per insert, this was obviously unacceptably slow

Re: [sqlite] Trigger update of multiple columns

2007-06-18 Thread John Stanton
A general rule of database design is to seperate reference and transactional data. Then you can have a normalized database in a dynamic environment. T wrote: Hi Puneet and John, You each respectively said: Why are you repeating the Code, Buy, Sell, and Description columns in the

Re: [sqlite] SQLITE_CORRUPT recover

2007-06-18 Thread John Stanton
The problem is fairly straight forward. Sqlite is a single resource being shared by multiple thyreads so you just use fundamental synchronization logic as you would when sharing any resource between competing threads. Sabyasachi Ruj wrote: Hi, I am using sqlite in a multithreaded

Re: [sqlite] SQLITE_CORRUPT recover

2007-06-18 Thread John Stanton
. On 6/18/07, John Stanton <[EMAIL PROTECTED]> wrote: The problem is fairly straight forward. Sqlite is a single resource being shared by multiple thyreads so you just use fundamental synchronization logic as you would when sharing any resource between competing threads. Sabyasachi Ruj wrote

Re: [sqlite] Trigger update of multiple columns

2007-06-18 Thread John Stanton
I mean something else. You have a reference data set which is accessed to get the current value of reference elements and store transactions to record events. The transaction trails provide event history. A price is in the reference data, its value transferred to a transaction is no longer a

Re: [sqlite] SQLITE_CORRUPT recover

2007-06-18 Thread John Stanton
does if it is compiled with THREADSAFE=1. > > On 6/18/07, John Stanton <[EMAIL PROTECTED]> wrote: > >> >> The problem is fairly straight forward. Sqlite is a single resource >> being shared by multiple thyreads so you just use fundamental >> synchro

Re: [sqlite] Trigger update of multiple columns

2007-06-18 Thread John Stanton
If you can automatically enter data then you are violating the normalization rules. Maybe you should get a book on database design and become familiar with some of the fundamentals. T wrote: Hi John, You have a reference data set which is accessed to get the current value of reference

Re: [sqlite] SQLITE_CORRUPT recover

2007-06-18 Thread John Stanton
It is fundamental computer science, CS101 you might say. Pick up a textbook on basic computing. Sabyasachi Ruj wrote: But can you tell me where is this documented please? On 6/18/07, John Stanton <[EMAIL PROTECTED]> wrote: If you knew the answer then why did you ask the question? Y

Re: [sqlite] SQLITE_CORRUPT recover

2007-06-18 Thread John Stanton
with mutexes. The logic is very simple. Sabyasachi Ruj wrote: I still fail to understand what should I synchronize on. I am *not* sharing sqlite* across multiple threads. On 6/18/07, John Stanton <[EMAIL PROTECTED]> wrote: It is fundamental computer science, CS101 you might say. Pick up a te

Re: [sqlite] Proper way to transfer a live sqlite database

2007-06-18 Thread John Stanton
One of the most endearing features of Sqlite is that it is a single file. You can copy it with impunity. If it is in use while you are copying you can launch an exclusive transaction to block other users and copy it and be assured of its state. Rich Rattanni wrote: The databases will be

Re: [sqlite] FTS2 Experiences?

2007-06-18 Thread John Stanton
We have just started to use it. So far it is performing well, but we have not subjected it to high volume and large data sets yet. I have written a simple function which helps in our application. The function concanenates columns to produce a block of text then strips out punctuation and

Re: [sqlite] Custom Aggregation Not Working

2007-06-19 Thread John Stanton
We have implemented a decimal arithmetic module to handle money in Sqlite. It uses the regular SQL definitions and maintains precison and scale. The data are stored as TEXT and in "display format", right aligned with leading spaces so that they display without editing or radix transformation

Re: [sqlite] Recommend server for Windows?

2007-06-19 Thread John Stanton
Gilles Ganault wrote: At 11:20 19/06/2007 -0400, Clay Dowling wrote: I'm going to recommend PostgreSQL. Thanks for the idea, but if possible, we'd rather something really basic, typically a single EXE. Besides, using eg. PostgreSQL would require rewriting our application. I went

Re: [sqlite] Recommend server for Windows?

2007-06-20 Thread John Stanton
Gilles Ganault wrote: At 20:47 19/06/2007 -0500, John Stanton wrote: Such a server can be made simpler then mine by making it single threaded. Is it publicly available from http://www.viacognis.com? Thanks G. No, but I can give you some code which might help your project. The components

Re: [sqlite] Data structure for versioned data

2007-06-20 Thread John Stanton
We perform some versioning by holding column material in XML and using RCS to maintain reverse deltas and versions. Samuel R. Neff wrote: Not specific to SQLite, but we're working on an app that needs to keep versioned data (i.e., the current values plus all previous values). The versioning

Re: [sqlite] Alternative index methods (hash key)

2007-06-20 Thread John Stanton
Andrew Finkenstadt wrote: On 6/20/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: "Scott Hess" <[EMAIL PROTECTED]> wrote: > On 6/20/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote: > > How difficult do you think it would be to support an alternative method of > > indexing within SQLite

Re: [sqlite] querying number of open connections

2007-06-27 Thread John Stanton
Nate Constant wrote: Hello, is there a way to query the number of open database connections? An open file monitor like lsof will do it. - To unsubscribe, send email to [EMAIL PROTECTED]

Re: [sqlite] FW: BLOB data retrieval

2007-06-27 Thread John Stanton
Krishnamoorthy, Priya (IE10) wrote: Hi, I have a database which has a table that contains BLOB data. The table has two columns - one is "Row_Num" which is of type AUTO_INCREMENT (INTERGER_PRIMARY_KEY) and the other column "Data" contains BLOB data. I am writing a program (in MS VC++)

  1   2   3   4   5   6   7   8   9   10   >