RE: [sqlite] Step Query

> My question here is do I need to do sqlite3_finalize(pStmt); after > every sqlite3_step() to free all memory allocated by > sqlite3_step().Does calling finalize at end will free all memory > allocated by all steps statements? No you don't need to call sqlite3_finalize after every sqlite3_step.

Re: [sqlite] Step Query

On Tue, 2007-06-19 at 10:58 +0530, anand chugh wrote: > Hi > > I am having code like this: > >rc = sqlite3_prepare(db, zSql, -1, , 0); >if( rc!=SQLITE_OK ){ > return rc; >} >sqlite3_bind_text(pStmt, 1, zKey, -1, SQLITE_STATIC); >sqlite3_bind_blob(pStmt, 2, zBlob, nBlob,

[sqlite] Step Query

Hi I am having code like this: rc = sqlite3_prepare(db, zSql, -1, , 0); if( rc!=SQLITE_OK ){ return rc; } sqlite3_bind_text(pStmt, 1, zKey, -1, SQLITE_STATIC); sqlite3_bind_blob(pStmt, 2, zBlob, nBlob, SQLITE_STATIC); while( sqlite3_step(pStmt)==SQLITE_ROW ) { *pnBlob =

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

On Tue, 2007-06-19 at 00:46 +0100, Michael Hooker wrote: > Christian wrote: > > >>Best way of doing this is to execute a 'BEGIN IMMEDIATE', copying the > database file, then executing a 'ROLLBACK' to end the transaction.<< > > >>and can be safely copied at the OS level<< > > I also have a need

[sqlite] Custom Aggregation Not Working

Hey, Folks: I'm writing a financial application and MUST have exact math decimals (no floats). So, I'm using Python's decimal module. My database is Sqlite (and my language is Python with Pysqlite); Sqlite doesn't offer a non-floating point decimal type. But, it does have adapters and

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

Christian wrote: Best way of doing this is to execute a 'BEGIN IMMEDIATE', copying the database file, then executing a 'ROLLBACK' to end the transaction.<< and can be safely copied at the OS level<< I also have a need to copy a live database which is constantly being updated 24/7 by

Re: [sqlite] Version 3.4.0

[EMAIL PROTECTED] wrote: yes. I messed up the build again. Please try one more time. -- Thanks for the quick responses. More better now. ;-) Gerry - To unsubscribe, send email to [EMAIL PROTECTED]

Re: [sqlite] FTS2 Experiences?

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] Proper way to transfer a live sqlite database

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] Trigger update of multiple columns

On Mon, 18 Jun 2007 23:54:10 +1000, you wrote: >So, my question remains, is it possible to update multiple columns >from a single related row in another table, without having to perform >multiple redundant WHERE clauses? You may want to introduce a sold_products table, one row per

Re: [sqlite] Lemon Parser - Modular & Extensible ?

Hey, There's no need to be offensive. I did not mean to be critical. Far from it, it does a great a job (far more than I'm capable of producing). What I was trying to find out was, if it is possible for a .y files to be broken such that it can be built on top on other .y files. Not sure

Re: [sqlite] SQL query help

- Original Message - From: "P Kishor" <[EMAIL PROTECTED]> To: Sent: Monday, June 18, 2007 2:55 PM Subject: Re: [sqlite] SQL query help On 6/18/07, Jeff Godfrey <[EMAIL PROTECTED]> wrote: I have a table which contains (among other things), a "name" column

[sqlite] Re: 3.4.0 make

DRH Maybe the following suggestion for the Makefile... Allow two targets, 1. the default "make all" which generates the .a and .so from individual .o 2. a seperate target: make amalgamated which would build the tsrc and sqlite3.c Then create the .a, .so

Re: [sqlite] Lemon Parser - Modular & Extensible ?

Uma Krishnan uttered: Hello: Is lemon parser modular and extensible? Extensible to do what? It generates parsers, and is self contained. It does a single job, and does it well. What more could you ask for? Thanks Uma Asif Lodhi <[EMAIL PROTECTED]> wrote: Hi Everybody, I have

Re: [sqlite] Version 3.4.0

Gerry Snyder <[EMAIL PROTECTED]> wrote: > > (bin) 1 % load /sqlite/tclsqlite3.dll > couldn't find procedure Tclsqlite_Init > (bin) 2 % > yes. I messed up the build again. Please try one more time. -- D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] FTS2 Experiences?

2007/6/18, Russell Leighton <[EMAIL PROTECTED]>: Could folks that have used fts2 in production apps/systems relate their experiences to the group? I would very much be interested in how folks are using it, how well it performs with large data and general impressions. I had a look at it for an

Re: [sqlite] Recovery After Crash

Asif Lodhi uttered: Hi Kees, Thanks for replying. On 6/17/07, Kees Nuyt <[EMAIL PROTECTED]> wrote: >... thankful if you experts would give me an "accurate" and fair >picture of the crash-recovery aspects of SQLite - without any hype. I'm not sure if you would qualify this as hype, but

Re: [sqlite] Re: SQL query help

- Original Message - From: "Igor Tandetnik" <[EMAIL PROTECTED]> To: "SQLite" Sent: Monday, June 18, 2007 1:17 PM Subject: [sqlite] Re: SQL query help Jeff Godfrey <[EMAIL PROTECTED]> wrote: I have a table which contains (among other things), a "name"

Re: [sqlite] Journal File Optimization

Andre du Plessis uttered: 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

Re: [sqlite] SQL query help

On 6/18/07, Jeff Godfrey <[EMAIL PROTECTED]> wrote: Not seeing this on the list 1.5 hrs after posting, I thought I'd try again. Sorry if this is a duplicate... Jeff = Hi All, I have a table which contains (among other things), a "name" column and a "version" column (a software asset

[sqlite] SQL query help

Not seeing this on the list 1.5 hrs after posting, I thought I'd try again. Sorry if this is a duplicate... Jeff = Hi All, I have a table which contains (among other things), a "name" column and a "version" column (a software asset table). I need a query that will group all like

[sqlite] 3.4.0 make

make distclean does not seem to clear off the sqlite3.c no tsrc directories. My current build script: configure make distclean configure --enable-threadsafe --disable-tcl --enable-tempstore make sqlite3.c make How does one now build libraries static and shared based

[sqlite] Need help linking into Delphi Application

I've been using the Delphi ASGSqlite components with static linking for some time with version 3.3.13. I'd like to move on up to 3.4.0, however, no one seems to have documented how to do this yet. I tried compiling the Amalgamation with Borland C++ 5.0 and it generates the obj file nicely.

[sqlite] Re: SQL query help

Jeff Godfrey <[EMAIL PROTECTED]> wrote: I have a table which contains (among other things), a "name" column and a "version" column (a software asset table). I need a query that will group all like "names" together in a single record, and return the latest "version" (the largest value) for each

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

Thank you Igor and Christian. I appreciate your help. On 6/18/07, Christian Smith <[EMAIL PROTECTED]> wrote: Rich Rattanni uttered: > The databases will be in flux, and I didnt necessairly want to suspend > the application that is performs reads and writes into the database. > A simple copy

[sqlite] SQL query help

Hi All, I have a table which contains (among other things), a "name" column and a "version" column (a software asset table). I need a query that will group all like "names" together in a single record, and return the latest "version" (the largest value) for each group. What I have so far is

RE: [sqlite] Version 3.4.0

> > Is the incremental blob I/O available at the level of the Tcl binding ? > > From the online information this seems to be currently available only at the > > C level. > > Yes. There is a new (undocumented!) "incrblob" method on the > database object that opens a channel to BLOB. The syntax

Re: [sqlite] Optimization of equality comparison when NULL involved

--- Sean Cunningham <[EMAIL PROTECTED]> wrote: > I have very large datasets and have found that the built in union, > intersect, and except operations do not seem to use indices > (would be happy to be proven wrong here). As such, they > are not very speedy with large large data sets. A

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

Rich Rattanni uttered: The databases will be in flux, and I didnt necessairly want to suspend the application that is performs reads and writes into the database. A simple copy worries me because it seems like messing with SQLITE on the file level is dangerous since you circumvent all the

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

Rich Rattanni <[EMAIL PROTECTED]> wrote: The databases will be in flux, and I didnt necessairly want to suspend the application that is performs reads and writes into the database. A simple copy worries me because it seems like messing with SQLITE on the file level is dangerous since you

Re: [sqlite] Version 3.4.0

"Andreas Kupries" <[EMAIL PROTECTED]> wrote: > > SQLite version 3.4.0 is now available for download from the SQLite > > website and from the back-up site: > > > > Version 3.4.0 also includes support for new features such as: > > > >* Incremental BLOB I/O > > > > Additional information about

[sqlite] FTS2 Experiences?

Could folks that have used fts2 in production apps/systems relate their experiences to the group? I would very much be interested in how folks are using it, how well it performs with large data and general impressions. Thanks in advance. Russ

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

The databases will be in flux, and I didnt necessairly want to suspend the application that is performs reads and writes into the database. A simple copy worries me because it seems like messing with SQLITE on the file level is dangerous since you circumvent all the protection mechanisms that

RE: [sqlite] Version 3.4.0

> SQLite version 3.4.0 is now available for download from the SQLite > website and from the back-up site: > > Version 3.4.0 also includes support for new features such as: > >* Incremental BLOB I/O > > Additional information about these new features is available online. Is the incremental

Re: [sqlite] Version 3.4.0

--- [EMAIL PROTECTED] wrote: > Gerry Snyder <[EMAIL PROTECTED]> wrote: > > [EMAIL PROTECTED] wrote: > > > SQLite version 3.4.0 is now available for download > > > > > > > The tcl bindings for windows appear to be missing. Is this deliberate? > > > > Build-script bug. Now fixed. Try

Re: [sqlite] Version 3.4.0

[EMAIL PROTECTED] wrote: Gerry Snyder <[EMAIL PROTECTED]> wrote: The tcl bindings for windows appear to be missing. Is this deliberate? Build-script bug. Now fixed. Try again, please. (Note that I have observed that the DLL is now generated in my cross-compiler environment, but I

Re: [sqlite] Update of multiple columns

T wrote: In case the original question has been clouded by discussions of running a general store and the arson habits of the Hand Crafted Guild, allow me to crystalize my purely SQLite question: I know I can update via: update Table1 set c1 = (select d1 from Table2 where

Re: [sqlite] Optimization of equality comparison when NULL involved

On 6/18/07, Sean Cunningham <[EMAIL PROTECTED]> wrote: There was talk in the mailing list a while back about creating a new operator that would act as a superset of '==' which would treat NULL==NULL as True. I have seen this in some other database. Anybody know if this is on the roadmap? It

Re: [sqlite] Version 3.4.0

On 6/18/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: SQLite version 3.4.0 is now available for download from the SQLite and it's in production use here as of this morning. Version 3.4.0 also includes support for new features such as: * Incremental BLOB I/O * The zeroblob() SQL

Re: [sqlite] Optimization of equality comparison when NULL involved

On Jun 15, 2007, at 5:27 PM, Scott Hess wrote: select tableA.path, tableA.value from tableA,tableB where tableA.path=tableB.path and tableA.value=tableB.value union select tableA.path, tableA.value from tableA,tableB where tableA.path=tableB.path and tableA.value IS NULL AND tableB.value IS

Re: [sqlite] Version 3.4.0

Gerry Snyder <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > > SQLite version 3.4.0 is now available for download > > > > The tcl bindings for windows appear to be missing. Is this deliberate? > Build-script bug. Now fixed. Try again, please. (Note that I have observed that

[sqlite] Capturing data at a point in time

Subject was: [sqlite] Trigger update of multiple columns I've change the subject since this thread is discussing a tangent to my original query. Hi Fred, Therefore, the data items contained in each row of this table should be a permanent reflection of the master tables' data content at an

[sqlite] Update of multiple columns

In case the original question has been clouded by discussions of running a general store and the arson habits of the Hand Crafted Guild, allow me to crystalize my purely SQLite question: I know I can update via: update Table1 set c1 = (select d1 from Table2 where Table2.id =

Re: [sqlite] Trigger update of multiple columns

Hi Ed, I tried to update a list of columns: UPDATE t SET (c1, c2, c3) = (SELECT c1, c2, c3) FROM t2 WHERE .. but this syntax is not accepted as you probably already know. Thanks for your very thoughtful reply. That is exactly the type of syntax I'm after, with only one executed WHERE clause

Re: [sqlite] Trigger update of multiple columns

I want to insert the transactions data (product_id, buy, sell, desc) into the sale_products table. But I want a mechanism whereby if I enter the product_id, then the buy, sell, desc columns are auto entered (copied) from their corresponding row in the products table. Given: CREATE TABLE

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

It would most likely be much quicker (and simpler) just to utilize the OS's file coping feature to copy the table. What would be gained with the attaching databases approach over just a straight file copy? Fred > -Original Message- > From: Rich Rattanni [mailto:[EMAIL PROTECTED] > Sent:

RE: [sqlite] Trigger update of multiple columns

I think maybe "Normalization" could be one of the best underutilized features of modern database design by most practicing "Database Consultants." Never have I seen such a rule with so many exceptions! Each normalization decision must be prefaced with "It Depends" in every instance. Ah the

Re: [sqlite] Trigger update of multiple columns

Hi Gerry, keep each version of each product's description in the products table, along with the date that description became valid. Then the product ID and date in each transaction would reference the appropriate product table data. In certain circumstances, I can see how that would be

Re: [sqlite] SQLITE_CORRUPT recover

"Sabyasachi Ruj" <[EMAIL PROTECTED]> wrote: > I still fail to understand what should I synchronize on. I am *not* sharing > sqlite* across multiple threads. > If you compile SQLite so that it is threadsafe (-DTHREADSAFE=1) and if you do not share sqlite3* pointers across threads, then you should

Re: [sqlite] Version 3.4.0

[EMAIL PROTECTED] wrote: SQLite version 3.4.0 is now available for download The tcl bindings for windows appear to be missing. Is this deliberate? Thanking you for a wonderful product, Gerry - To unsubscribe,

Re: [sqlite] SQLITE_CORRUPT recover

You said that you were sharing Sqlite between threads by opening a connection in each thread. Sqlite is a single resource and must be sync'd somehow if it has multiple users. Complaining about it is like complaining that the sky is blue. It is blue because of Rayleigh scattering, a

[sqlite] Proper way to transfer a live sqlite database

I was writing to ask some opinions on how to perform a download of a live sqlite database. Basically I have a device, which stores all manner of data in a sqlite database. Periodically I want to download the data to a central server for viewing. I discussed it with my colleagues, and they felt

Re: [sqlite] SQLITE_CORRUPT recover

- Make sure you're compiling SQLite with *-DTHREADSAFE=1*. - Make sure that each thread opens the database file and keeps its own sqlite structure. - Make sure you handle the likely possibility that one or more threads collide when they access the db file at the same time: handle *

Re: [sqlite] Trigger update of multiple columns

T wrote: Yes, that's what I'm doing. I just want to make it more efficient. Technically it's the sale_products table (since each sale has many products etc), but yes, I want to insert the transactions data (product_id, buy, sell, desc) into the sale_products table. But I want a mechanism

Re: [sqlite] SQLITE_CORRUPT recover

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 textbook on basic computing. Sabyasachi Ruj wrote: > But can you

Re: [sqlite] SQLITE_CORRUPT recover

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? You can

Re: [sqlite] Trigger update of multiple columns

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

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? You can apply regular CS or look for miracles. You might be better off using a server based RDBMS like Oracle or PostgreSQL where

Re: [sqlite] SQLITE_CORRUPT recover

If you knew the answer then why did you ask the question? You can apply regular CS or look for miracles. You might be better off using a server based RDBMS like Oracle or PostgreSQL where the application programmer is insulated from synchronization issues. Sqlite has the "lite" in its name

Re: [sqlite] Trigger update of multiple columns

Hi John, 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. Yes, agreed. A price is in the reference data, its value transferred to a transaction is no

Re: [sqlite] SQLITE_CORRUPT recover

But the following link http://www.sqlite.org/cvstrac/wiki/wiki?p=MultiThreading says nothing that I have to synchronize at the application level to create multiple connections, until the same database connection is being shared! Ref: The four points in 'Short Answer' section. BTW: for a DBMS it

Re: [sqlite] Trigger update of multiple columns

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

Threadsafe only means that threads do not access global data elements or that they synchronize (serialize) access to global data. It does nothing to synchronize threads. That is up to the application programmer. Sqlite uses POSIX file locks for synchronixation but if you are in a totally

Re: [sqlite] Trigger update of multiple columns

Hi John, A general rule of database design is to seperate reference and transactional data. Then you can have a normalized database in a dynamic environment. Yes, I think that's what I am designing. The reference data is the products table (and potentially customer table etc) The

Re: [sqlite] SQLITE_CORRUPT recover

But I think we do not have to take care of synchronizing sqlite access. sqlite internally 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

Re: [sqlite] Trigger update of multiple columns

Hi Puneet, ok, your problem makes a bit more sense Great :-) but still, much in it doesn't make sense. OK, I'll see if I can clarify further. even if the products table is changing, the buy, sell, and description of a given item shouldn't change. The buy and sell price of products

Re: [sqlite] SQLITE_CORRUPT recover

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

[sqlite] Version 3.4.0

SQLite version 3.4.0 is now available for download from the SQLite website and from the back-up site: http://www.sqlite.org/ http://www.hwaci.com/sw/sqlite/ Version 3.4.0 fixes two bugs which can cause database corruption. Upgrading is recommended for all users. If you are compelled to

Re: [sqlite] Trigger update of multiple columns

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] Journal File Optimization

On Mon, 2007-06-18 at 06:04 -0500, John Stanton wrote: > 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

Re: [sqlite] SQLITE_CORRUPT recover

Hi, I am using sqlite in a multithreaded environment. I have take reasonable like not sharing sqlite* handles. I am creating a new sqlite* for every thread. Where can we get more info on working with SQLite in a multithreaded environment? The application is working as a service in windows.

Re: [sqlite] Trigger update of multiple columns

ok, your problem makes a bit more sense, but still, much in it doesn't make sense. On 6/18/07, T <[EMAIL PROTECTED]> wrote: Hi Puneet and John, You each respectively said: > Why are you repeating the Code, Buy, Sell, and Description columns > in the Sale_Products table when they already

Re: [sqlite] Trigger update of multiple columns

Hi Puneet and John, You each respectively said: Why are you repeating the Code, Buy, Sell, and Description columns in the Sale_Products table when they already exists in the Products table? A traditional goal in database design is to place data in "Third Normal Form" which means in

Re: [sqlite] Tomcat crashes with SQLite

Hi all. Lucy thanks for your answer :+) http://lucy.ysalaya.org My issue is not closed because the site crashes these days. I do not think I use the same connection in different threads at the same time. I am still using the javasqlite ODBC driver. But I did not but the jdbc into the Tomcat

Re: [sqlite] Journal File Optimization

On 6/18/07, John Stanton <[EMAIL PROTECTED]> wrote: 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

Re: [sqlite] Journal File Optimization

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

On 6/17/07, T <[EMAIL PROTECTED]> wrote: Hi All, I have a pretty standard sales tracking database consisting of tables: Products - Each row is a product available for sale. Includes fields: Code, Buy, Sell, Description Sales - Each row is a sale made to a

Re: [sqlite] Help with compiling 3.3.17 version for WinCE

On 6/16/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: I've download the amalgamation (single .c file) version of SQLite 3.3.17 and I'm trying to compile it using Embedded Visual C++ 3.0, but I'm getting some compiling errors such as,fatal error C1083: Cannot open include file:

Re: [sqlite] Trigger update of multiple columns

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

[sqlite] Journal File Optimization

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 and according to the lists