Re: [sqlite] Zip file with prebuilt DLL incomplete
Cory Nelson wrote: > > Indeed it should probably include sqlite3.h, but you are supposed to > generate an import .lib for your compiler using the .def file. VC++ comes > with lib.exe, I'm not sure how you do it with others. > > I know that the GNU compilers have a similar utility - I just have never used either (being lazy, I mostly rely on the compiler to provide the .lib file - that does require the use of non-standard keywords, but you can hide those). Anyway, the header file is needed, as you say. Regards, Arjen
RE: [sqlite] On VACUUM I get "constraint failed"
Hard to tell from the schema that you attached where the problem might be... but try doing a .dump from the command line util and re-importing into a clean db, it should where expose the issue is. sqlite> .output db.sql sqlite> .dump sqlite> .exit sqlite3 -init db.sql test.db hope this helps --preston From: John Duprey <[EMAIL PROTECTED]> Reply-To: sqlite-users@sqlite.org To: sqlite-users@sqlite.org Subject: [sqlite] On VACUUM I get "constraint failed" Date: Thu, 20 Oct 2005 14:01:10 -0400 We have an SQLite database with a table that has about a million rows. We do a lot of (thousands) of deletes and inserts in this table. Recently, deleting rows has become slow. I assumed the slow down was a result of fragmentation or wasted space due to the deletes. Therefore, I thought vacuuming would fix it. While trying to VACUUM the database, I got this error from sqlite3: "SQL error: constraint failed" Does anyone have any ideas what constraint could be failing and how to fix it? I've attached the schema of the db.. if anyone thinks its relevant. Thanks, -John << schema.sql >>
[sqlite] Re: Re: in memory SQLite in C/C++
Rajan, Vivek K wrote:
One question: So at any given point in time, one can only have _ONE_
SQLite database in-memory?
If I understand it correctly, every sqlite3_open(":memory:") call
creates a separate, independent in-memory database. So you can have many
such databases open at the same time, but you can't have two handles
open on the same in-memory database (the way you can on a file).
Igor Tandetnik
Re: [sqlite] Re: in memory SQLite in C/C++
Rajan, Vivek K wrote: Thanks Igor. One question: So at any given point in time, one can only have _ONE_ SQLite database in-memory? Rajan, No, you get a new independent in-memory database for each call to sqlite3_open() with a filename of ":memory:" Each call returns a separate sqlite3* database pointer. Dennis Cote
RE: [sqlite] Re: in memory SQLite in C/C++
Thanks Igor. One question: So at any given point in time, one can only have _ONE_ SQLite database in-memory? Rajan >-Original Message- >From: Igor Tandetnik [mailto:[EMAIL PROTECTED] >Sent: Thursday, October 20, 2005 1:57 PM >To: SQLite >Subject: [sqlite] Re: in memory SQLite in C/C++ > >Rajan, Vivek K wrote: >> As I understand it is possible to us in-memory SQLite database. Does >> someone have example C/C++ code to demonstrate techniques for using >> in-memory SQLite database? > >You use sqlite3_open to open a database with a special name ":memory:". >Then just use it as you would a regular file-based database. > >Igor Tandetnik
[sqlite] Re: in memory SQLite in C/C++
Rajan, Vivek K wrote: As I understand it is possible to us in-memory SQLite database. Does someone have example C/C++ code to demonstrate techniques for using in-memory SQLite database? You use sqlite3_open to open a database with a special name ":memory:". Then just use it as you would a regular file-based database. Igor Tandetnik
[sqlite] in memory SQLite in C/C++
Hello- As I understand it is possible to us in-memory SQLite database. Does someone have example C/C++ code to demonstrate techniques for using in-memory SQLite database? Thanks in advance. Rajan
Re: [sqlite] "or" in SQL Query converted to "IN"
R S <[EMAIL PROTECTED]> wrote: > by default? I am using 3.2.2 > > I ran across this doc > > http://www.sqlite.org/optoverview.html The OR optimization was added in 3.2.3. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] "or" in SQL Query converted to "IN"
by default? I am using 3.2.2 I ran across this doc http://www.sqlite.org/optoverview.html
Re: [sqlite] What does SQLITE_FULL - "database is full" mean? How to fix?
Stephen, Thank you. That makes sense. I later confirmed that the file system had temporarily ran out of disk space. -John On 10/17/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Hi John, > > I believe that SQLite will return with an SQLITE_FULL error if your run > out of disk space, OR if the value of the inserted row's rowid or INTEGER > PRIMARY KEY AUTOINCREMENT column exceeds a maximum value (2147483647 on a > 32 bit, 9223372036854775807 on a 64 bit). > > The first condition should be easy to check - just see if the drive or > volume you store the database on has any free space. > > To check the second condition you could "SELECT max(rowid) from category;" > and see if you are at or near the maximum value for your platform. > > Cheers, > Stephen > > > Hi all, > > > > I just recently got the SQLITE_FULL error while trying to insert a row > > into a table. What does it mean when the database is full? Out of > > disk space perhaps or did I hit some internal constraint? > > > > Here's a except from my log: > > Oct 14 17:49:19 [8048/3899832] ERROR: SQL statement failure executing: > > "INSERT INTO category (taxonomy_id, category_uid, version, > > category_state_id) > > Oct 14 17:49:19 [8048/3899832] ERROR: VALUES > > Oct 14 17:49:19 [8048/3899832] ERROR: ( > > Oct 14 17:49:19 [8048/3899832] ERROR: (SELECT rowid FROM > > taxonomy_description WHERE name='TRG-CACIVP'), > > Oct 14 17:49:19 [8048/3899832] ERROR: '294790203', > > Oct 14 17:49:19 [8048/3899832] ERROR: '_NO_VERSION_', > > Oct 14 17:49:19 [8048/3899832] ERROR: (SELECT rowid FROM > > category_state WHERE state='TRAINING' ) > > Oct 14 17:49:19 [8048/3899832] ERROR: ); > > Oct 14 17:49:19 [8048/3899832] ERROR: > > Oct 14 17:49:19 [8048/3899832] ERROR: " because "database is full". > > > > I'd appreciate any feedback you have on this error. > > > > Thank you, > > > > -John > > > >
[sqlite] On VACUUM I get "constraint failed"
We have an SQLite database with a table that has about a million rows.
We do a lot of (thousands) of deletes and inserts in this table.
Recently, deleting rows has become slow. I assumed the slow down was
a result of fragmentation or wasted space due to the deletes.
Therefore, I thought vacuuming would fix it. While trying to VACUUM
the database, I got this error from sqlite3:
"SQL error: constraint failed"
Does anyone have any ideas what constraint could be failing and how to fix it?
I've attached the schema of the db.. if anyone thinks its relevant.
Thanks,
-John
CREATE TABLE category (taxonomy_id INTEGER NOT NULL,
category_uid VARCHAR NOT NULL UNIQUE,
version VARCHAR NOT NULL,
category_state_id INTEGER NOT NULL,
modification_date DATE DEFAULT NULL,
PRIMARY KEY (taxonomy_id, category_uid, version));
CREATE TABLE category_meta_information (taxonomy_id INTEGER NOT NULL,
category_id INTEGER NOT NULL,
name VARCHAR NOT NULL,
value VARCHAR,
data_type_id INTEGER NOT NULL,
PRIMARY KEY (taxonomy_id, category_id, name));
CREATE TABLE category_state (state VARCHAR NOT NULL UNIQUE DEFAULT 'training');
CREATE TABLE data_type (type VARCHAR UNIQUE DEFAULT 'string');
CREATE TABLE feature_type (feature_type VARCHAR NOT NULL UNIQUE);
CREATE TABLE taxonomy_description (name VARCHAR UNIQUE,
version VARCHAR DEFAULT 'current',
featuresets VARCHAR DEFAULT 'CWordPairFeatureSet_Object',
modification_date DATE DEFAULT NULL,
PRIMARY KEY (name, version));
CREATE TABLE taxonomy_meta_information (name VARCHAR NOT NULL,
value VARCHAR,
data_type_id INTEGER NOT NULL,
taxonomy_id INTEGER NOT NULL);
CREATE UNIQUE INDEX tempindex ON taxonomy_meta_information ( taxonomy_id,name );
CREATE TRIGGER delete_category BEFORE DELETE ON category
BEGIN
DELETE FROM category_meta_information WHERE category_id=old.rowid;
END;
CREATE TRIGGER delete_category_meta_moddate AFTER DELETE ON
category_meta_information
BEGIN
UPDATE category SET modification_date = DATETIME('NOW') WHERE rowid =
old.category_id;
END;
CREATE TRIGGER delete_category_moddate AFTER DELETE ON category
BEGIN
UPDATE taxonomy_description SET modification_date = DATETIME('NOW') WHERE
rowid = old.taxonomy_id;
END;
CREATE TRIGGER delete_taxonomy BEFORE DELETE ON taxonomy_description
BEGIN
DELETE FROM category WHERE taxonomy_id=old.rowid;
DELETE FROM taxonomy_meta_information WHERE taxonomy_id=old.rowid;
END;
CREATE TRIGGER insert_category_meta_moddate AFTER INSERT ON
category_meta_information
BEGIN
UPDATE category SET modification_date = DATETIME('NOW') WHERE rowid =
new.category_id;
END;
CREATE TRIGGER insert_category_moddate AFTER INSERT ON category
BEGIN
UPDATE category SET modification_date = DATETIME('NOW') WHERE rowid =
new.rowid;
UPDATE taxonomy_description SET modification_date = DATETIME('NOW') WHERE
rowid = new.taxonomy_id;
END;
CREATE TRIGGER insert_taxonomy_moddate AFTER INSERT ON taxonomy_description
BEGIN
UPDATE taxonomy_description SET modification_date = DATETIME('NOW') WHERE
rowid = new.rowid;
END;
CREATE TRIGGER update_category AFTER UPDATE OF rowid ON category
BEGIN
UPDATE category_meta_information SET category_id=new.rowid WHERE
category_id=old.rowid;
END;
CREATE TRIGGER update_category_meta_moddate AFTER UPDATE ON
category_meta_information
BEGIN
UPDATE category SET modification_date = DATETIME('NOW') WHERE rowid =
new.category_id;
END;
CREATE TRIGGER update_category_moddate AFTER UPDATE ON category
BEGIN
UPDATE category SET modification_date = DATETIME('NOW') WHERE rowid =
new.rowid;
UPDATE taxonomy_description SET modification_date = DATETIME('NOW') WHERE
rowid = new.taxonomy_id;
END;
CREATE TRIGGER update_taxonomy_description AFTER UPDATE OF rowid ON
taxonomy_description
BEGIN
UPDATE category SET taxonomy_id=new.rowid WHERE taxonomy_id=old.rowid;
UPDATE taxonomy_meta_information SET taxonomy_id=new.rowid WHERE
taxonomy_id=old.rowid;
END;
CREATE TRIGGER update_taxonomy_moddate AFTER UPDATE ON taxonomy_description
BEGIN
UPDATE taxonomy_description SET modification_date = DATETIME('NOW') WHERE
rowid = new.rowid;
END;
Re: [sqlite] Zip file with prebuilt DLL incomplete
Indeed it should probably include sqlite3.h, but you are supposed to generate an import .lib for your compiler using the .def file. VC++ comes with lib.exe, I'm not sure how you do it with others. On 10/20/05, Arjen Markus <[EMAIL PROTECTED]> wrote: > > Hello, > > I downloaded the sqlitedll-3_2_7.zip file the other day > to do some initial experiments with SQLite, but it turns > out that this zip-file only contains the DLL itself and > the definitions file. > > If I understand it correctly, it is possible to link > against the DLL using that definition file, but it is > more common to use the import library. > > But even so, the sqlite3.h header file is missing. > So the zip-file as such is not useable. > > Kind regards, > > Arjen Markus > > -- Cory Nelson http://www.int64.org
Re: Re: [sqlite] Instr, Locate or Splite
Unfortunately, it's not always the same length (nor the same chars) and the case statement could be slightly unwieldy. I may have to resort to that anyway. Thanks Get your own "800" number Voicemail, fax, email, and a lot more http://www.ureach.com/reg/tag On Mon, 17 Oct 2005, Kurt Welgehausen ([EMAIL PROTECTED]) wrote: > If you're sure all the range strings are formatted > correctly and if StartAge <= EndAge, you can do it > with a case expression. The length of the range > string will be >= 3 and <= 7; from the length you > can deduce and hard code the indices in the substr > calls. > > Regards > >
RE: [sqlite] sqlite3_open16 with unicode string
Hi all, I just wanted to let you know that when updating the sources, the problem wad solved! ++ Benoît Gantaume R&D Manager. DMAILER 15 av. Frederic Mistral 13008 Marseille, France Bureau: +33 4 91 29 32 80 Fax : +33 4 91 76 39 27 Email : [EMAIL PROTECTED] Web: http://www.dmailer.com -Original Message- From: Benoit Gantaume [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 19, 2005 5:42 PM To: sqlite-users@sqlite.org Subject: [sqlite] sqlite3_open16 with unicode string Hi all, I have a little problem when trying to open database that contain Unicode caracters: Here is a small sample: wstring lPath = L"c:\\\x4ECA\x6708 database.db"; DWORD lReturn = sqlite3_open16((void *)lPath.c_str (), &this->cdb); This works correctly, but the problem is that the database name created is not correct: I get something like: [今月 database.db] instead of [今月database.db] I guess that I have a problem a string encoding, but I can not find any solution. I have also tried to use sqlite3_open with encoding the name UTF8, but I get exactly the same result. Can anyone help? Thanks a lot Benoît Gantaume R&D Manager. DMAILER
Re: AW: [sqlite] and , or
Martin Engelschalk wrote: The problem seems to be that sqlite makes a difference between an empty string and a null value. s/sqlite/SQL/ The SQL standards all say that nulls never compare equal to anything, not even other nulls. SQLite's behavior here is the correct one.
[sqlite] Zip file with prebuilt DLL incomplete
Hello, I downloaded the sqlitedll-3_2_7.zip file the other day to do some initial experiments with SQLite, but it turns out that this zip-file only contains the DLL itself and the definitions file. If I understand it correctly, it is possible to link against the DLL using that definition file, but it is more common to use the import library. But even so, the sqlite3.h header file is missing. So the zip-file as such is not useable. Kind regards, Arjen Markus
Re: [sqlite] threading and win32
Hi, There was a recent discussion about this, with the subject "Problems with threadsafe opt correction #2623" On 5/9 2005 Richard wrote: "The restriction that a DB handle can only be used from a single thread is currently only enforced on Unix, because Unix boxes are the only place where using DB handles in multiple threads is a problem. -- D. Richard Hipp <[EMAIL PROTECTED]> " From personal experience it seems sqlite is completely safe in multi-threaded environments when shared the same database handle on windows platforms, provided that you use an adequate locking mechanism. The drawback is portability to other platforms that implement threading different and are not safe on concurrent file access, even if well-sequenced. On unix sqlite enforces a database handle to be used by the same thread as that opened it. Some people claimed having no problems, but this seems os/kernel dependent. rene Wilson Yeung wrote: I wasn't doing a very good job paying attention to the FAQ about threading and SQLite when I wrote a bunch of code on win32. I'm sharing sqlite3 database handles between threads, although I am ensuring that no two threads are executing against the sqlite3 database handle at the same time. The threads are in fact interleaving, and the sqlite3 database handle is indeed being shared between the two threads, and everything is working as I'd like it to work... Is the warning about threads in the FAQ specifically apply to Linux systems? Or can we pretty much say that my code is going to eventually break on win32 and I'm lucky that it hasn't already? Wilson
