[sqlite] How to search words
Hi all, I am quite-learner of SQLite. I want to clear some doubts regarding Can i search a word in the whole of the database.? With advanced thanks and Regards Anantha Narayanan.T.L CELEBRATE FOR A REASON CALLED UNITY. UNITE FOR A REASON CALLED LIFE. -- "This e-mail message may contain confidential, proprietary or legally privileged information. It should not be used by anyone who is not the original intended recipient. If you have erroneously received this message, please delete it immediately and notify the sender. The recipient acknowledges that ICICI Bank or its subsidiaries and associated companies, (collectively "ICICI Group"), are unable to exercise control or ensure or guarantee the integrity of/over the contents of the information contained in e-mail transmissions and further acknowledges that any views expressed in this message are those of the individual sender and no binding nature of the message shall be implied or assumed unless the sender does so expressly with due authority of ICICI Group.Before opening any attachments please check them for viruses and defects."
Re: [sqlite] Whole word searches
Kurt Welgehausen wrote: There are no brackets in the SQL spec; that's a Microsoft extension. SQLite's glob operator has similar syntax; it's an extension also. If it weren't for brackets, in another context, I might still be using Access (and would be much the poorer for it). In some cases, brackets were needed around table names, and in others they were forbidden. I could find no documentation making the distinction clear. A complicated SELECT statement could take a long time to get right, by trial and error. SQLite omits some standard SQL, and also lacks some useful extensions present in Access, but it is MUCH more pleasant to work with. Thank you, Dr. Hipp, and other contributors. Gerry -- Gerry Snyder in warm, winterless Los Angeles -- USDA zone 9b, Sunset 18-19
Re: [sqlite] NULL values with NOT NULL constraint
On Wed, 8 Dec 2004, D. Richard Hipp wrote: Ara.T.Howard wrote: Has anyone seen anything like this and can suggest possible causes? does your code fork? Ooo. That sounds like the voice of experience speaking. :-) I should add forking to the documented list of ways to corrupt your database file. i never __actually__ corrupted anything by forking - but this is because my child process never did anything with the db handle. i did verify that forking causes silly names on NFS though and could imagine many other 'not good' (tm) scenarios. regards. -a -- === | EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov | PHONE :: 303.497.6469 | When you do something, you should burn yourself completely, like a good | bonfire, leaving no trace of yourself. --Shunryu Suzuki ===
Re: [sqlite] Sequences In SQLite
I want to apologize for not thinking of checking the FAQ. I didn't see what I was looking for in the documentation and totally forgot about the FAQ. Thanks, and again, sorry. -- Jeff Flowers
Re: [sqlite] Whole word searches
Thanks Kurt. I think glob looks promising. best regards -brett Quoting Kurt Welgehausen <[EMAIL PROTECTED]>: > > How do I search on whole words ... > > find ... "main" and don't want ... "maintain" > > I tried using brackets as specified in the SQL spec > > There are no brackets in the SQL spec; that's a Microsoft extension. > SQLite's glob operator has similar syntax; it's an extension also. > > If your word separators are always spaces, you can use > > col like '% main %' or col like 'main %' or col like '% main' or > col = 'main' > > If there can be other characters between words, you can use > something like > > col glob '*[ ,.]main[ ,.]*' or col glob 'main[ ,.]*' or > col glob '*[ ,.]main' or col = 'main' > > Regards > This message was sent using IMP, the Internet Messaging Program.
Re: [sqlite] Whole word searches
> How do I search on whole words ... > find ... "main" and don't want ... "maintain" > I tried using brackets as specified in the SQL spec There are no brackets in the SQL spec; that's a Microsoft extension. SQLite's glob operator has similar syntax; it's an extension also. If your word separators are always spaces, you can use col like '% main %' or col like 'main %' or col like '% main' or col = 'main' If there can be other characters between words, you can use something like col glob '*[ ,.]main[ ,.]*' or col glob 'main[ ,.]*' or col glob '*[ ,.]main' or col = 'main' Regards
Re: [sqlite] NULL values with NOT NULL constraint
Ara.T.Howard wrote: Has anyone seen anything like this and can suggest possible causes? does your code fork? Ooo. That sounds like the voice of experience speaking. :-) I should add forking to the documented list of ways to corrupt your database file. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Whole word search
[EMAIL PROTECTED] wrote: How do I search on whole words within a text field. For example, if I want to find the whole word "main" and don't want records with "maintain", how can I do that with Sqlite? Write your own string comparison function that does such things and register it using sqlite3_create_function(). -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
[sqlite] Whole word search
I asked this question yesterday but got no answer, so here goes my second try. How do I search on whole words within a text field. For example, if I want to find the whole word "main" and don't want records with "maintain", how can I do that with Sqlite? I tried using brackets as specified in the SQL spec but it doesn't work - I get errors. Here is some documentation from MS SQL Server: [ ] Any single character within the specified range ([a-f]) or set ([abcdef]). WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and beginning with any single character between C and P, for example Carsen, Larsen, Karsen, and so on. This message was sent using IMP, the Internet Messaging Program.
Re: [sqlite] NULL values with NOT NULL constraint
On Wed, 8 Dec 2004, Michael Robinette wrote: I have table somewhat like this created by sqlite 2.8.14: create table t1( created timestamp not null, n int not null, url text not null, primary key(n) ); somehow i've ended up with a database where .dump t1 produces a bunch of insert into t1 values(NULL,NULL,NULL); a pragma integrity_check produces output like: *** in database main *** On page 578 cell 0: Key is out of order On page 1204 cell 4: Key is out of order On page 1161 cell 2: Key is out of order On page 1210 cell 0: Key is out of order On page 1210 cell 6: Key is out of order rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) wrong # of entries in index (t1 autoindex 1) the number of missing rows == the number of NULL rows from the dump. how does one get into this state where the contraints are ignored? If I were a guessing man, I'd say that space is being reserved and zeroed, but data is never being flushed to disk. Additionally, it seems that a journal file is either not being created, or is being deleted before the database is opened again, so that data is never recovered. Has anyone seen anything like this and can suggest possible causes? does your code fork? -a -- === | EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov | PHONE :: 303.497.6469 | When you do something, you should burn yourself completely, like a good | bonfire, leaving no trace of yourself. --Shunryu Suzuki ===
Re: [sqlite] Sequences In SQLite
> Is there anything like serial sequences in SQLite? What I want to do is > have a primary interger key that auto-increments as records are added to > a table. http://www.sqlite.org/faq.html#q1 http://www.catb.org/~esr/faqs/smart-questions.html Ulrik P.
Re: [sqlite] Sequences In SQLite
On Wed, Dec 08, 2004 at 04:40:02PM -0500, Jeff Flowers wrote: > Is there anything like serial sequences in SQLite? What I want to do is > have a primary interger key that auto-increments as records are added to > a table. does the first entry in the faq on the website not do what you want?
Re: [sqlite] NULL values with NOT NULL constraint
Michael Robinette wrote: I have table somewhat like this created by sqlite 2.8.14: create table t1( created timestamp not null, n int not null, url text not null, primary key(n) ); somehow i've ended up with a database where .dump t1 produces a bunch of insert into t1 values(NULL,NULL,NULL); a pragma integrity_check produces output like: *** in database main *** [error messages omitted...] how does one get into this state where the contraints are ignored? In a corrupt database, all things are possible. If I were a guessing man, I'd say that space is being reserved and zeroed, but data is never being flushed to disk. Additionally, it seems that a journal file is either not being created, or is being deleted before the database is opened again, so that data is never recovered. Has anyone seen anything like this and can suggest possible causes? See http://www.sqlite.org/lockingv3.html section 6.0 "How To Corrupt Your Database Files". This is written for version 3.0 but all the same rules apply to version 2.8.14. If you think the problem is caused by some bug in SQLite and not by one of the external problems described above, please let us know how to reproduce it and we will look into the problem further. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Sql lite new user
Thanks you It works fine now
[sqlite] NULL values with NOT NULL constraint
I have table somewhat like this created by sqlite 2.8.14: create table t1( created timestamp not null, n int not null, url text not null, primary key(n) ); somehow i've ended up with a database where .dump t1 produces a bunch of insert into t1 values(NULL,NULL,NULL); a pragma integrity_check produces output like: *** in database main *** On page 578 cell 0: Key is out of order On page 1204 cell 4: Key is out of order On page 1161 cell 2: Key is out of order On page 1210 cell 0: Key is out of order On page 1210 cell 6: Key is out of order rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) rowid -500152523 missing from index (t1 autoindex 1) wrong # of entries in index (t1 autoindex 1) the number of missing rows == the number of NULL rows from the dump. how does one get into this state where the contraints are ignored? If I were a guessing man, I'd say that space is being reserved and zeroed, but data is never being flushed to disk. Additionally, it seems that a journal file is either not being created, or is being deleted before the database is opened again, so that data is never recovered. Has anyone seen anything like this and can suggest possible causes? Thanks, - michael
Re: [sqlite] Sql lite new user
A connection object should not be copied, they will both hold the same
internal connection to sqlite.
I really should add a copy constructor, thanks for reminding me. For
now just do sql.open("mk.db").
On Wed, 8 Dec 2004 18:45:17 +0100, ibrahim <[EMAIL PROTECTED]> wrote:
> Hello,
>
> I m new to this mailing list ,
>
> I currently try to add sql lite to a program I m working on
>
> It is a quizz program ,
>
> And I want the question to be incorporated into a sql lite database to enable
> search ...etc..
>
> I m download the source code of version 2_8_15
>
> And compiled it under Visual Studio .NET 2003 with problems (only warnings )
>
> As a wrapper , I choose this one : http://dev.int64.org/sqlite.html
>
> I compiled the sqllite-plus.lib with no problems
>
> Run the code samples test-select and test-insert with no problem
>
> So I tried to add the both lib sqlite.lib and sqllite-plus.lib in my real
> project ( windows program )
>
> I get a lot of errors , I found that the error was due to runtime library
> conflit (Multithread DLL / single threaded)
>
> So I recompiled the both lib with Multithread DLL runtime ... no
> problem
>
> run the samples test-select and test-insert ... no problem
>
> So I tried to recompile my real project with the both lib ... no problem
>
> but when i run the code , I get error at runtime :
>
> library routine called out of sequence ( from ex.what() )
>
> Exception de première chance à 0x77e4d756 dans Quizz.exe:Microsoft C++
> exception: std::runtime_error @ 0x0012fe5c.
> HEAP[Quizz.exe]: HEAP: Free Heap block 49d4758 modified at 49d487c after it
> was freed
> Exception non gérée à 0x77f6f570 dans Quizz.exe:Point d'arrêt utilisateur.
>
> Here is the code that causes the problem :
>
> sql = sqlite::connection("mk.db");
>
> try {
>
> sqlite::reader reader=sql.executereader("select * from t_test;");
>
> reader.close();
> sql.close();
> }
>
> catch(exception ) {
> fprintf(i,"%s\n",ex.what() );
> fflush(i);
> }
>
> When I run it in console program no problem
>
> But when I run it with my win32 project -> problem
>
> Any help appreciated
>
> Thanks in advance
>
> Nicolas Raby
> vmgames
> France
>
>
--
Cory Nelson
http://www.int64.org
[sqlite] Sql lite new user
Hello,
I m new to this mailing list ,
I currently try to add sql lite to a program I m working on
It is a quizz program ,
And I want the question to be incorporated into a sql lite database to enable
search ...etc..
I m download the source code of version 2_8_15
And compiled it under Visual Studio .NET 2003 with problems (only warnings )
As a wrapper , I choose this one : http://dev.int64.org/sqlite.html
I compiled the sqllite-plus.lib with no problems
Run the code samples test-select and test-insert with no problem
So I tried to add the both lib sqlite.lib and sqllite-plus.lib in my real
project ( windows program )
I get a lot of errors , I found that the error was due to runtime library
conflit (Multithread DLL / single threaded)
So I recompiled the both lib with Multithread DLL runtime ... no
problem
run the samples test-select and test-insert ... no problem
So I tried to recompile my real project with the both lib ... no problem
but when i run the code , I get error at runtime :
library routine called out of sequence ( from ex.what() )
Exception de première chance à 0x77e4d756 dans Quizz.exe:Microsoft C++
exception: std::runtime_error @ 0x0012fe5c.
HEAP[Quizz.exe]: HEAP: Free Heap block 49d4758 modified at 49d487c after it was
freed
Exception non gérée à 0x77f6f570 dans Quizz.exe:Point d'arrêt utilisateur.
Here is the code that causes the problem :
sql = sqlite::connection("mk.db");
try {
sqlite::reader reader=sql.executereader("select * from t_test;");
reader.close();
sql.close();
}
catch(exception ) {
fprintf(i,"%s\n",ex.what() );
fflush(i);
}
When I run it in console program no problem
But when I run it with my win32 project -> problem
Any help appreciated
Thanks in advance
Nicolas Raby
vmgames
France
Re: [sqlite] Setting The Width Option
Jeff Flowers wrote: > On Tue, 7 Dec 2004 18:57:18 -0800, "Scott Leighton" > <[EMAIL PROTECTED]> said: >> On Tuesday 07 December 2004 10:28 am, Jeff Flowers wrote: >>> Is it possible to set the .width option when calling the sqlite >>> frontend? I was surprised that there wasn't a options to do this, >>> something like: >>> >>> sqlite3 -column -header -width '30 30 5 5' dbname >>> >>> >> >> You didn't mention your OS, but I know on Linux you >> can set whatever defaults you want in .sqliterc >> and the commandline utility uses them. >> >> Not sure if this is also true on the Win platform tho. >> >> Scott > > I'm using Mac OS X, which means I am basically running on Unix. Are > the commands in the .sqliterc the same as I would type them in > interactively? Jeff, Yes, you use the same commands. You should be aware that .sqliterc is executed everytime sqlite is started. You may not always want the columns set to these widths. For your pruposes the -init filename option may be more suitable. You can use this to read and execute the commands in the specified file before executing the SQL on the command line (or entering interactive mode). You could use a command like: sqlite3 -init setup dbname "select * from ..." to execute the commands in the file setup before executing the SQL statement on the command line. Of course, if you always execute the same query (or queries) they could be moved into the command file. You can also execute commands in a file using the .read meta command on the command line, or by redirecting input from a command file. sqlite3 dbname ".read cmdfile" sqlite3 dbname
Re: [sqlite] Setting The Width Option
On Tue, 7 Dec 2004 18:57:18 -0800, "Scott Leighton" <[EMAIL PROTECTED]> said: > On Tuesday 07 December 2004 10:28 am, Jeff Flowers wrote: > > Is it possible to set the .width option when calling the sqlite > > frontend? I was surprised that there wasn't a options to do this, > > something like: > > > > sqlite3 -column -header -width '30 30 5 5' dbname > > > > > > You didn't mention your OS, but I know on Linux you > can set whatever defaults you want in .sqliterc > and the commandline utility uses them. > > Not sure if this is also true on the Win platform tho. > > Scott I'm using Mac OS X, which means I am basically running on Unix. Are the commands in the .sqliterc the same as I would type them in interactively? -- Jeff Flowers
