[sqlite] Problem with conflict resolution in triggers

I'm trying to implement a string pool using views and triggers: CREATE TABLE StringPool ( ID INTEGER PRIMARY KEY AUTOINCREMENT, Val TEXT UNIQUE ); CREATE TABLE T ( KeyTEXT PRIMARY KEY, ValRef INTEGER REFERENCES StringPool(ID) ); CREATE VIEW V

Re: [sqlite] Bug Report - Documentation

Hello All: Sorry for the misdirection. The correct link is http://sqlite.org/lang_transaction.html Mark Benningfield ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug Report - Documentation

Hello All: I was surfing the docs today and fell over a contradiction on http://sqlite.org/lockingv3.html#reserved_lock To wit: "The rollback hook is invoked on a rollback that results from a commit hook returning non-zero, just as it would be with any other rollback." And "The rollback

Re: [sqlite] FTS3 stop words/chars

On Fri, Apr 30, 2010 at 2:18 PM, Richard Hipp wrote: > > > On Fri, Apr 30, 2010 at 4:43 PM, Paul Rigor (uci) wrote: > >> Hi, >> >> What are the default stop words and characters for the FTS3 simple >> tokenizer? >> >> > The tokenizers built into FTS3 do not

Re: [sqlite] FTS3 stop words/chars

On Fri, Apr 30, 2010 at 4:43 PM, Paul Rigor (uci) wrote: > Hi, > > What are the default stop words and characters for the FTS3 simple > tokenizer? > > The tokenizers built into FTS3 do not use any stop words. > Thanks, > Paul > > -- > Paul Rigor > Pre-doctoral BIT Fellow and

Re: [sqlite] Occasional "cannot commit - no transaction is active"

We're seeing exactly the same thing on .22. We were previously on .17 and never had this issue. Our usage is exactly as you describe as well. Multiple threads with the shared cache enabled but no single thread is using the same connection more than once. -- View this message in context:

[sqlite] FTS3 stop words/chars

Hi, What are the default stop words and characters for the FTS3 simple tokenizer? Thanks, Paul -- Paul Rigor Pre-doctoral BIT Fellow and Graduate Student Institute for Genomics and Bioinformatics Donald Bren School of Information and Computer Sciences University of California, Irvine

Re: [sqlite] sqlite update with joins

That works... Thank you very much. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite update with joins

I know sqlite update with joins is not supported but I have heard work arounds without the need for scripting. eg: 2 tables H and F both join on FILENAME I want to update h.FILENAME so its the same as the rowid of table F sqlite3 F "update H h , F f set h.FILENAME=f.rowid where

Re: [sqlite] cannot import blank columns in a Sqlite table

Updating to Sqlite Manager 0.5.15 solved completely the problem. Gary_Gabriel wrote: > > Hi, > am65 wrote: >> What is strange is that it used to do the import correctly. But from a >> certain point of time it started to ignore the blank columns. >> > If you check the issues for SQLite

[sqlite] FTS3 Japanese Tokenization

Hi All, Does anyone know if ICU supports tokenization of Japanese text? Thanks Ray This e-mail message, including any attachments, is for the sole use of the person to whom it has been sent, and may contain information that is confidential or legally protected. If you are not the intended

Re: [sqlite] Book, Tutorial, Code Sample Request

On 30 Apr 2010, at 6:33pm, Hal Faulkner wrote: > I want to program using Cocoa/Objective-C with Mac OSX 10.6 Xcode & Interface > Builder to create and use persistent SQLite3 database. > > I have failed in my search for code examples or tutorials which show the use > of objects to use

[sqlite] Book, Tutorial, Code Sample Request

I want to program using Cocoa/Objective-C with Mac OSX 10.6 Xcode & Interface Builder to create and use persistent SQLite3 database. I have failed in my search for code examples or tutorials which show the use of objects to use SQLite. I have run out of web search word combinations. All I

Re: [sqlite] WHERE = does not work

Is is possible the character encoding is different? On 4/30/2010 6:59 AM, Adam DeVita wrote: > Is it possible there is a null, tab, newline or other invisible character? > Try > > select timeStamp, '' || resourceType || 'xx' From MyTable where > resourceType like 'PSM' LIMIT 10; > > On

Re: [sqlite] WHERE = does not work

I am not sure if this is your issue exactly but I had similar problems when I started on SQLite a while ago. It turned out that my table definition was case-sensitive and therefore = was not working for me and I had to use LIKE. I changed my table definition with COLLATE NO CASE and the problem

Re: [sqlite] WHERE = does not work

Would this query help determine if any extraneous characters present? SELECT * FROM MyTable WHERE LENGTH(resourceType) <> 3 AND resourceType LIKE 'PSM' ; ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] WHERE = does not work

On 30 April 2010 16:56, ecforu wrote: > this was my first thought so I did a dump to a file and looked at in hex - > there were no extra characters.  I even tried looking at the db file with a > hex editor and I could see the PSM text and no extra characters around it >

Re: [sqlite] WHERE = does not work

Show us your dump output -- there should be no nulls. Michael D. Black Senior Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of ecforu Sent: Fri 4/30/2010 10:56 AM To: General Discussion of SQLite Database Subject:

Re: [sqlite] WHERE = does not work

this was my first thought so I did a dump to a file and looked at in hex - there were no extra characters. I even tried looking at the db file with a hex editor and I could see the PSM text and no extra characters around it (except the NULLs on either side which I assume separates the columns).

Re: [sqlite] select %column% from table

Thank you all, I am amazed by both the generousity and the intelligent solutions by everyone. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select %column% from table

On Fri, Apr 30, 2010 at 07:57:05AM -0700, David Lyon wrote: > if I had many many files like this: > http://research.stowers-institute.org/efg/ScientificSoftware/Utility/FCSExtract/CC4_067_BM.txt > > you see 2 columns keyword and value, the keywords would be the fields > (1st column in the html

Re: [sqlite] select %column% from table

On 30 Apr 2010, at 3:57pm, David Lyon wrote: > if I had many many files like this: > http://research.stowers-institute.org/efg/ScientificSoftware/Utility/FCSExtract/CC4_067_BM.txt > > you see 2 columns keyword and value, the keywords would be the fields (1st > column in the html link above)

Re: [sqlite] select %column% from table

On Fri, Apr 30, 2010 at 10:17 AM, Gerry Snyder wrote: > So why not columns keyword and value? > because, each row is a conceptual "text file" with many key-value combos. Putting them in separate rows would mean that each key-val belongs in a separate text file, whatever

Re: [sqlite] select %column% from table

On Fri, Apr 30, 2010 at 9:57 AM, David Lyon wrote: > Thanks for everyones efforts let me expand: > > > if I had many many files like this: > http://research.stowers-institute.org/efg/ScientificSoftware/Utility/FCSExtract/CC4_067_BM.txt > > you see 2 columns keyword and

Re: [sqlite] select %column% from table

So why not columns keyword and value? Gerry On 4/30/10, David Lyon wrote: > Thanks for everyones efforts let me expand: > > > if I had many many files like this: > http://research.stowers-institute.org/efg/ScientificSoftware/Utility/FCSExtract/CC4_067_BM.txt > > you see 2

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] Reusing stmt and erroneous constraint failure

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/29/2010 11:10 PM, Dan Kennedy wrote: > For those playing at home, this is an example of how to write > a great bug report. Clear, concise explanation and a > self-contained minimal example. The credit all goes to Nikolaus Rath who initially

Re: [sqlite] select %column% from table

Thanks for everyones efforts let me expand: if I had many many files like this: http://research.stowers-institute.org/efg/ScientificSoftware/Utility/FCSExtract/CC4_067_BM.txt you see 2 columns keyword and value, the keywords would be the fields (1st column in the html link above) in the table

Re: [sqlite] select %column% from table

crap! I completely misunderstood your question... be confused, and then ignore my reply. On Fri, Apr 30, 2010 at 9:43 AM, P Kishor wrote: > On Fri, Apr 30, 2010 at 9:28 AM, David Lyon wrote: >> If I had a table called TABLE with fields P1N..P50N

Re: [sqlite] select %column% from table

On Fri, Apr 30, 2010 at 07:28:52AM -0700, David Lyon scratched on the wall: > If I had a table called TABLE with fields P1N..P50N is there a way > to select something like: > > "select P%N from TABLE" SELECT * FROM... > to return all the results from columns P1N..P50N or do I have >

Re: [sqlite] "restore" doesn't work

Thanks, it's best solution! There are a lot of situations when are useful databases with different page sizes. 2010/4/30 Black, Michael (IS) : > Add a debug statement to show the page sizes being used here so you know what > to set the default size to. > >  /* Catch the

Re: [sqlite] select %column% from table

On Fri, Apr 30, 2010 at 9:28 AM, David Lyon wrote: > If I had a table called TABLE with fields P1N..P50N is there a way to > select something like: > > "select P%N from TABLE" > >  to return all the results from columns P1N..P50N or do I have to do it > manually:

Re: [sqlite] select %column% from table

You have to do it manually. Or reconsider your database schema towards normalization. Pavel On Fri, Apr 30, 2010 at 10:28 AM, David Lyon wrote: > If I had a table called TABLE with fields P1N..P50N is there a way to > select something like: > > "select P%N from

Re: [sqlite] "restore" doesn't work

I think you should be able to change this: #ifndef SQLITE_MAX_DEFAULT_PAGE_SIZE # define SQLITE_MAX_DEFAULT_PAGE_SIZE 8192 #endif #if SQLITE_MAX_DEFAULT_PAGE_SIZE>SQLITE_MAX_PAGE_SIZE # undef SQLITE_MAX_DEFAULT_PAGE_SIZE # define SQLITE_MAX_DEFAULT_PAGE_SIZE SQLITE_MAX_PAGE_SIZE #endif Add a

[sqlite] select %column% from table

If I had a table called TABLE with fields P1N..P50N is there a way to select something like: "select P%N from TABLE" to return all the results from columns P1N..P50N or do I have to do it manually: "select P1N, P2N, P3N, P$nN from TABLE" I can obviously do it via scripting but

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 (IS) : > Also..was your database created on the same machine you're restoring on?   >

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] WHERE = does not work

On 30 April 2010 14:59, Adam DeVita wrote: > Is it possible there is a null, tab, newline or other invisible character? > Try > > select timeStamp, '' || resourceType || 'xx'  From MyTable where > resourceType like 'PSM' LIMIT 10; > Following from Adam's suggestion:

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] WHERE = does not work

On Fri, Apr 30, 2010 at 9:53 AM, ecforu wrote: > I don't think it is a case issue. See below from sqlite3 command line. > Also one thing to note - I build the database from c API. I don't know if > that makes a difference. > > Please email me your database file by private

Re: [sqlite] WHERE = does not work

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 From: sqlite-users-boun...@sqlite.org on behalf of ecforu Sent: Fri 4/30/2010 8:53 AM To: General

Re: [sqlite] WHERE = does not work

Care to show us the function in C where you're doing the insert? At least the code where you prepare the string and insert it. Michael D. Black Senior Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of ecforu Sent:

Re: [sqlite] WHERE = does not work

Is it possible there is a null, tab, newline or other invisible character? Try select timeStamp, '' || resourceType || 'xx' From MyTable where resourceType like 'PSM' LIMIT 10; On Fri, Apr 30, 2010 at 9:53 AM, ecforu wrote: > I don't think it is a case issue.

Re: [sqlite] WHERE = does not work

I don't think it is a case issue. See below from sqlite3 command line. Also one thing to note - I build the database from c API. I don't know if that makes a difference. sqlite> sqlite> select timeStamp, resourceType From MyTable where resourceType like 'PSM' LIMIT 10; timeStamp|resourceType

Re: [sqlite] WHERE = does not work

On Fri, Apr 30, 2010 at 9:22 AM, ecforu wrote: > I have an sqlite3 database which I can't query with WHERE =. I have to use > WHERE like. > > Any ideas why this is? > > For example I have a resourceType column that has as some of its entries > (over 50) 'PSM'. > > SELECT *

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';

Re: [sqlite] WHERE = does not work

But the like WHERE clause works the way it is. Its the = that isn't working. I would rather use = than like. I'm just using like for now because it works. Thanks On Fri, Apr 30, 2010 at 9:29 AM, Timothy A. Sawyer < tsaw...@mybowlingdiary.com> wrote: > With the like clause you have to use the

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

Ecforu, Re: What's the diff? In sqlite, LIKE without a "%" (percent-sign ) would be a case-insensitive search, whereas == would be case-sensitive. sqlite> select 'cat' like 'CAT'; 1 sqlite> select 'cat' == 'CAT'; 0 ___ sqlite-users mailing list

Re: [sqlite] WHERE = does not work

With the like clause you have to use the % sign as a wildcard. So resourceType LIKE %'PSM' returns anything ending in PSM. The SQLite website has excellent docs on standard SQL. -Original Message- From: ecforu Sent: Friday, April 30, 2010 09:22 To:

[sqlite] WHERE = does not work

I have an sqlite3 database which I can't query with WHERE =. I have to use WHERE like. Any ideas why this is? For example I have a resourceType column that has as some of its entries (over 50) 'PSM'. SELECT * FROM MyTable WHERE resourceType = 'PSM' --> returns nothing. SELECT * FROM MyTable

[sqlite] "restore" doesn't work

$ sqlite3 :memory: SQLite version 3.6.23 sqlite> .restore './work.db' Error: attempt to write a readonly database sqlite> .q $ sqlite3 SQLite version 3.6.23 sqlite> .restore './work.db' Error: attempt to write a readonly database sqlite> .q $ sqlite3 test.db SQLite version 3.6.23 sqlite>

Re: [sqlite] cannot import blank columns in a Sqlite table

On 30 Apr 2010, at 8:23am, am65 wrote: > I tested using a blank space between the commas, like this (", ,") instead of > this (",,") and this way the value of the next column is not put in the null > column. But then the column is a blank space instead of a null. Try putting quotes around all

Re: [sqlite] cannot import blank columns in a Sqlite table

Hi, am65 wrote: > What is strange is that it used to do the import correctly. But from a > certain point of time it started to ignore the blank columns. > If you check the issues for SQLite Manager you will determine that ver. 0.5.12- 0.5.14 did not insert properly. According to the change log

Re: [sqlite] cannot import blank columns in a Sqlite table

I tested using a blank space between the commas, like this (", ,") instead of this (",,") and this way the value of the next column is not put in the null column. But then the column is a blank space instead of a null. am65 wrote: > > I'm using the Mozilla add-on SQLite Manager 0.5.14 (SQLite

Re: [sqlite] Reusing stmt and erroneous constraint failure

Now fixed here: http://www.sqlite.org/src/ci/f660be615a For those playing at home, this is an example of how to write a great bug report. Clear, concise explanation and a self-contained minimal example. Dan. On Apr 30, 2010, at 8:51 AM, Roger Binns wrote: > -BEGIN PGP SIGNED