Re: [sqlite] read only databases and in-memory databases

it in read-only, fore it plays nicer with other threads that are also doing read-only operations.* Sam On Mon, May 17, 2010 at 10:48 AM, Black, Michael (IS) michael.bla...@ngc.com wrote: By breakdown in communications you must mean lost data or incomplete results. That's probably

Re: [sqlite] Bus error crash in SQLite

This has been fixed http://www.sqlite.org/src/info/f3162063fd Michael D. Black Senior Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of Ben Sent: Sat 5/15/2010 7:27 AM To: General Discussion of SQLite Database

Re: [sqlite] Bus error crash in SQLite

I tested your code with the latest fossil checkout It now gets this: sqlite INSERT INTO stuff DEFAULT VALUES; Error: stuff.name may not be NULL Which makes sense as you didn't specify a default value for it. So adding this: name TEXT NOT NULL DEFAULT 'unk'; produces this: sqlite INSERT INTO

Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

sqlite3_reset on the statement that returned SQLITE_BUSY. And this call to sqlite3_reset will return SQLITE_BUSY again. Pavel On Thu, May 13, 2010 at 7:20 AM, Black, Michael (IS) michael.bla...@ngc.com wrote: It also means you're not handling the SQLITE_BUSY from the sqlite3_stmt() before

Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

in this case one must call sqlite3_reset on the statement that returned SQLITE_BUSY. And this call to sqlite3_reset will return SQLITE_BUSY again. Pavel On Thu, May 13, 2010 at 7:20 AM, Black, Michael (IS) michael.bla...@ngc.com wrote: It also means you're not handling the SQLITE_BUSY from

Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

in finalizing the sqlite3_stmt pointer? Black, Michael (IS) wrote: SQLITE_BUSY is not an error...just a fact. All your processes cannot work on the database at the same time...at least not when one of them is doing an insert. You could be changing the table while you're scanning

Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

SQLITE_BUSY. But even in this case one must call sqlite3_reset on the statement that returned SQLITE_BUSY. And this call to sqlite3_reset will return SQLITE_BUSY again. Pavel On Thu, May 13, 2010 at 7:20 AM, Black, Michael (IS) michael.bla...@ngc.com wrote: It also means you're not handling

Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

then SQLITE_MISUSE will be returned (although from my experience in some cases repeated sqlite3_step can work, but generally you better call sqlite3_reset). Pavel On Thu, May 13, 2010 at 8:13 AM, Black, Michael (IS) michael.bla...@ngc.com wrote: Hmmm...bad assumption on my partseems so logical

Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

of SQLITE_MISUSE. And I tried to use the BEGIN EXCLUSIVE TRANSACTION. The things are same with them without using it. Black, Michael (IS) wrote: Your reiterating 20 times is not using a usleep so you'll blow by this most every time it's busy. Do this instead in all your proc's

Re: [sqlite] Attach

H...I duplicated this behavior on 3.6.23.1...I can only assume this is by intent as the interpreter isn't designed for mulitiple attached databases. .dump doesn't work on an attached table either Michael D. Black Senior Scientist Northrop Grumman Mission Systems

Re: [sqlite] Insert large data question ??

You haven't said what kind of machine you're running on. I did this test using 9,000,000 records and got 40,000 inserts per second. sqlite3 test.db x1.sql time sqlite3 test.db x2.sql It took 225 seconds on my 8-core 2.5Ghz Xeon machine. It created a 797Mb database. x1.sql: CREATE TABLE

Re: [sqlite] SQLite Database in Shared Memory

Just 'cuz you don't need persitence now of course doesn't mean you can't use it. That solves your shared memory problem even though it's not as elegant. You can even access via file shares that way too which sounds a bit like what you may want do anyways. Michael D. Black Senior Scientist

Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

Your reiterating 20 times is not using a usleep so you'll blow by this most every time it's busy. Do this instead in all your proc's ret = sqlite3_step (p_stmt); if (SQLITE_BUSY == ret) { int n=0; usleep(10); // try one more time

Re: [sqlite] SQLite Database in Shared Memory

I think you may be worrying too much about file speed as it's already pretty fast. But if you want AIX ramdisk check here: http://www.ee.pw.edu.pl/~pileckip/aix/mkramdisk.htm Michael D. Black Senior Scientist Northrop Grumman Mission Systems From:

Re: [sqlite] create virtual table if not exists table_id???

Syntax says they are different...virtual tables don't have the same flexibility apparently...I suppose you're looking for why though? http://www.sqlite.org/lang_createvtab.html Michael D. Black Senior Scientist Northrop Grumman Mission Systems From:

Re: [sqlite] Changing file descriptor of database file

The select() limit has nothing to do with sqlite. You already noted it's a limit on sockets -- it's really an OS limit. Do you have any idea what your max is or what you think you need? I see some solutions and upcoming problems (by the way, you forgot to mention what OS you're on). #1

Re: [sqlite] Doing fine with SQLite

echo .mode csv input.sql echo select * from selected limit 4 input.sql sqlite3 test.db input.sql Michael D. Black Senior Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of Matt Young Sent: Tue 5/4/2010 12:02 PM To:

Re: [sqlite] Optimising usage of LIKE

Simon's answer is probably best -- without any benchmarks it makes the most sense. You've got at least two solutions that don't require changing your data: SELECT x FROM userTable WHERE upper(name) = upper('name'); SELECT x FROM userTable WHERE name = 'name' COLLATE NOCASE. And one

Re: [sqlite] Optimising usage of LIKE

form. I would favor normalizing the data on the INSERT. Michael D. Black Senior Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of Black, Michael (IS) Sent: Mon 5/3/2010 5:58 AM To: General Discussion of SQLite Database

Re: [sqlite] WHERE = does not work

Apparently you must by typeing something wrong. This works for me: create table t(resourceType varchar); insert into t values('PSM'); select * from t where resourceType = 'PSM'; PSM select * from t where resourceType like 'PSM'; PSM Does this work for you? I'm using 3.6.23.1 Michael D.

Re: [sqlite] WHERE = does not work

You are likely getting the case insensitive result with like. sqlite create table t(resourceType varchar); sqlite insert into t values('PSM'); sqlite insert into t values('psm'); sqlite select * from t where resourceType = 'PSM'; PSM sqlite select * from t where resourceType like 'PSM'; PSM psm

Re: [sqlite] WHERE = does not work

|PSM 2010-04-28 17:47:13.845|PSM 2010-04-28 17:47:16.837|PSM 2010-04-28 17:47:18.846|PSM sqlite sqlite sqlite select timeStamp, resourceType From MyTable where resourceType = 'PSM' LIMIT 10; sqlite sqlite sqlite THANKS On Fri, Apr 30, 2010 at 9:35 AM, Black, Michael (IS) michael.bla...@ngc.com

Re: [sqlite] WHERE = does not work

-04-28 17:47:16.837|PSM 2010-04-28 17:47:18.846|PSM sqlite sqlite sqlite select timeStamp, resourceType From MyTable where resourceType = 'PSM' LIMIT 10; sqlite sqlite sqlite THANKS On Fri, Apr 30, 2010 at 9:35 AM, Black, Michael (IS) michael.bla...@ngc.com wrote: You are likely getting

Re: [sqlite] restore doesn't work

Hmmm...works for me on windows and Linux -- I used the default configuration for compiling 3.6.23.1 under Linux. I also removed write permissions to test.db and it still worked. There are a limited number of places where SQLITE_READONLY error can occur. Why don't you set some debug

Re: [sqlite] restore doesn't work

Also..was your database created on the same machine you're restoring on? Page size difference will create this error too. Michael D. Black Senior Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of Alexey Pechnikov

Re: [sqlite] restore doesn't work

/2010 9:25 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] restore doesn't work The problem is really produced by the different page_size. I did have bugreport from my client and it's not easy to reproduce the problem by this error message... 2010/4/30 Black, Michael

Re: [sqlite] select %column% from table

Create a view with your columns that you can easily reference: http://www.1keydata.com/sql/sql-create-view.html Michael D. Black Senior Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of David Lyon Sent: Fri

Re: [sqlite] WHERE = does not work

which I assume separates the columns). On Fri, Apr 30, 2010 at 10:02 AM, Black, Michael (IS) michael.bla...@ngc.com wrote: Also...what do you get from a .dump ?? Any extra chars should show up in the SQL statements. Michael D. Black Senior Scientist Northrop Grumman Mission Systems

Re: [sqlite] SQlite trigger issues

U...write one? As noted -- no DBMS provides this type of interface that I've ever seen...as intuitive as you may think it is. They would end up doing the same thing that was suggested here. What you need is to set up a trigger, then write your own sqlite_execute_select_query(string

Re: [sqlite] SQLite memory leakage

You'll have the growth until the CACHE is full. That stays until you vacuum it. An initial large select could fil the cache entirely in one call. Then you'll have the very temporary memory of storage from the select -- but that should disappear as soon as you finalize your statement.

Re: [sqlite] SQLite memory leakage

on behalf of Black, Michael (IS) Sent: Thu 4/29/2010 6:57 AM To: i...@omroth.com; General Discussion of SQLite Database Subject: Re: [sqlite] SQLite memory leakage You'll have the growth until the CACHE is full. That stays until you vacuum it. An initial large select could fil the cache

Re: [sqlite] CHECK constraints and type affinity

Get rid of the quotes in your values. sqlite create table t2(n integer check(typeof(n)='integer')); sqlite insert into t2 values('5'); Error: constraint failed sqlite insert into t2 values(5); sqlite select n from t2; 5 Michael D. Black Senior Scientist Northrop Grumman Mission Systems

Re: [sqlite] CHECK constraints and type affinity

for dynamically created views, triggers, etc. And modern languages can use string representation of variables in SQLite bindings. 2010/4/29 Black, Michael (IS) michael.bla...@ngc.com: Get rid of the quotes in your values. sqlite create table t2(n integer check(typeof(n)='integer')); sqlite insert

Re: [sqlite] CHECK constraints and type affinity

If you want only positive values: sqlite create table t3(n integer check(abs(round(n)) = n)); sqlite insert into t3 values('-5'); Error: constraint failed Michael D. Black Senior Scientist Northrop Grumman Mission Systems From:

Re: [sqlite] CHECK constraints and type affinity

Hmmm...when I get rid of the +' CREATE TABLE T1 (N INTEGER CHECK(N = 0)); the constraint works Seems to me that +N is the same as abs(N). I'm not even sure of what the intent of +N would be??? Michael D. Black Senior Scientist Northrop Grumman Mission Systems

Re: [sqlite] Hypothetical memory consumption question

We just went through this the other day. You want to change # define SQLITE_DEFAULT_CACHE_SIZE 2000 To something smaller since you don't apparently need the cache space. It will only grow to about 3Meg on a 32-bit system apparently. Try making it 10 or less and you should see your

Re: [sqlite] values containing dash - not evaluated

First off confirm it's not a bug with sqlite2: sqlite create table Groups (name varchar(10)); sqlite insert into Groups values('bob'); sqlite insert into Groups values('jean-baptiste'); sqlite select * from Groups where name='jean-baptiste'; jean-baptiste If you don't get a results this way

Re: [sqlite] Searching with like for a specific start letter

When you say running on the fly do you mean running from an sqlite3 command prompt? Or are you doing this in some other programming language? Why in the world would you use a database to do this? Michael D. Black Senior Scientist Northrop Grumman Mission Systems

Re: [sqlite] Is there any memory leak in the code while being busy?

the leak. Black, Michael (IS) wrote: You need to sqlite3_free(sql) after you use the sql from your sqlite3_mprintf(). sql = sqlite3_mprintf (sql_f, i); ret = sqlite3_prepare_v2 (db1, sql, -1, p_stmt, NULL); sqlite3_free(sql); Michael D. Black Senior

Re: [sqlite] Is there any memory leak in the normal routine?

I confirmed your memory leak. What you're seeing is the page cache growing. Not really a memory leak. Default page cache size is 2000 and indeed if I just let it run it topped out at 5404 RES in top. I added dmalloc to sqlite3 and found that if you let your program loop several times and

Re: [sqlite] Direct access of table data

If insert speed is important are you doing batch inserts? If so, you want to do a BEGIN/COMMIT to speed up your inserts a LOT. Default action is to defer which mean no database locks occur during your inserts. http://www.sqlite.org/lang_transaction.html Michael D. Black Senior Scientist

Re: [sqlite] Direct access of table data

From: sqlite-users-boun...@sqlite.org on behalf of Nathan Biggs Sent: Fri 4/23/2010 8:05 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Direct access of table data Yes, I do use batch inserts. On 4/23/2010 8:56 AM, Black, Michael (IS) wrote

Re: [sqlite] Is there any memory leak in the code while being busy?

You need to sqlite3_free(sql) after you use the sql from your sqlite3_mprintf(). sql = sqlite3_mprintf (sql_f, i); ret = sqlite3_prepare_v2 (db1, sql, -1, p_stmt, NULL); sqlite3_free(sql); Michael D. Black Senior Scientist Northrop Grumman Mission Systems

<    4   5   6   7   8   9