[sqlite] Query optimization: Checking for existence before performing action
Hello, I am trying to optimize the SQL calls that my application makes. I have a scenario where words are inserted into a table. Now each word will have a column called "confidence". There is a unique primary key on "word". When inserting a word, first I check if the words exists by performing a "select" query. If it exists, I fire an update query to increment the confidence for that word. If word is not available, I fire an insert query to insert the word. In both the cases, I can't skip doing two queries. One for checking existence and second for updating or creating. I am wondering is there an easy way to solve this by just doing one query? I have tried "insert or replace", but I can't use that as it changes the rowid's. Any help would be great! -- Thanks Navaneeth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Should I do analyze?
Hello, I learned about the use of ANALYZE command recently. In my application, SQLIte file is generated once and never modified. Currently my application creates SQLIte database, creates required tables, indexes and inserts records into it. As a last step, it runs VACUUM. I am wondering should I do ANALYZE also as the last command? Will that make the query planner happy and choose faster plans? Will that improve the runtime performance? -- Thanks Navaneeth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query Help
Hello, Thanks for the help. On Sun, Mar 10, 2013 at 10:36 PM, Igor Tandetnik wrote: > On 3/10/2013 11:06 AM, Navaneeth.K.N wrote: >> >> select distinct(lower(pattern)) as pattern, id from symbols where >> value1 = ?1 or value2 = ?1 group by pattern >> >> This returns >> >> "cchu", "20907" >> "chchu", "20879" >> "chu", "20935" >> >> This is distinct set of patterns, but I am not getting the list >> ordered by id. Even if I add a "order by id" to the above query, it >> sorts only the above set. But what I need is to get in the following >> order. >> >> >> "chu", "20851" >> "chchu", "20879" >> "cchu", "20907" > > > Why do you expect 'chu' to be accompanied by an ID of 20851, and not 20935? > These seem to be equally valid choices? More than the id, I care about order. When I use my first query, "chu" comes at the end. But since it has a lower id, it should be first. This order defined how my application behaves. Doing min(id) did the trick. Thanks for the help. Thanks to James also for the additional information. > > If you want, say, the smallest of the two, just say so: > > select lower(pattern) as pattern, min(id) as minid > > from symbols where value1 = ?1 or value2 = ?1 > group by pattern order by minid; > > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Thanks Navaneeth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query Help
Hi Guys, I have a table named "symbols". I am writing the below query. select lower(pattern), id from symbols where value1 = ?1 or value2 = ?1 This returned the following results. "chu", "20851" "chchu", "20879" "cchu", "20907" "chu", "20935" >From this, I need only distinct patterns. So I tried this query. select distinct(lower(pattern)) as pattern, id from symbols where value1 = ?1 or value2 = ?1 group by pattern This returns "cchu", "20907" "chchu", "20879" "chu", "20935" This is distinct set of patterns, but I am not getting the list ordered by id. Even if I add a "order by id" to the above query, it sorts only the above set. But what I need is to get in the following order. "chu", "20851" "chchu", "20879" "cchu", "20907" This is ordered by id and only distinct patterns. I am not able to come up with a query which does the above. Any help would be great. -- Thanks Navaneeth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3.dll no longer operative
On Oct 11, 2012 5:58 PM, "L. Dale Rohl" wrote: > > I am using 64bit Window 7 on my stand alone Toshiba Computer. Recently I > lost a Hard Drive and had it replaced. All is well but during activation of > the computer a pop-up is on the screen that says that "sqlite3.dll" has been > lost and I need to download and replace the file. > > > > I have looked and looked and finally found you and need your assistance > about how to download this file and cure the problem. AFAIK, there is no pre built 64 bit version dll available to dowload. You might have to download amalgamation and compile the dll yourself. > > > > Thanks for your help. > > > > Regards, > > > > Dale > > > > L. Dale Rohl, President > > ROHL MORTGAGE CAPITAL CORPORATION > > 602 SE 131st Court > > Vancouver, WA 98683-4001 > > Telephone: 360-944-1440 > > Mobile: 360-921-6610 > > FAX: 360-892-4632 > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Choosing the best query plan
Hello, I have two tables named "patterns_content" and "words". CREATE TABLE patterns_content (pattern text, word_id integer, primary key(pattern, word_id)) CREATE TABLE words (id integer primary key, word text unique, confidence integer default 1, learned integer default 1, learned_on date) Given a pattern, "abc", I need to get the word for it. For this, I use, select word, confidence from words as w, (SELECT distinct(word_id) as word_id FROM patterns_content as pc where pc.pattern = lower('abc') limit 5) as patterns where w.id = patterns.word_id and w.learned = 1 order by confidence desc I could also use, select word, confidence from words where rowid in (SELECT distinct(word_id) FROM patterns_content as pc where pc.pattern = lower('abc') limit 5) and learned = 1 order by confidence desc Both these queries are fast. The only difference between them is the place where subquery is used. In first one subquery is used as part of the from clause and second one uses as part of where clause. When looking throgh the execution plan, they both uses different plans. Plan for 1st query -- SEARCH TABLE patterns_content AS pc USING COVERING INDEX sqlite_autoindex_patterns_content_1 (pattern=?) (~2 rows) SCAN SUBQUERY 1 AS patterns (~2 rows) SEARCH TABLE words AS w USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) USE TEMP B-TREE FOR ORDER BY Plan for 2nd query - SEARCH TABLE words USING INTEGER PRIMARY KEY (rowid=?) (~2 rows) EXECUTE LIST SUBQUERY 1 SEARCH TABLE patterns_content AS pc USING COVERING INDEX sqlite_autoindex_patterns_content_1 (pattern=?) (~2 rows) USE TEMP B-TREE FOR ORDER BY First one uses a temporary table to store the subquery results. I am wondering which query to choose. Any help would be great! Also, is there way to get rid of temporary B-TREE for order by? -- Thanks Navaneeth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How is this query so fast?
Forgot to add. I got idea for last method from this[1] post. [1] http://sqlite.1065341.n5.nabble.com/fast-string-prefix-matching-td10777.html On Thu, Aug 2, 2012 at 11:36 PM, Navaneeth.K.N wrote: > Hello, > > I have a table which contains a set of words. These are stored in a table > called 'patterns_content' with pattern as unique key. There is a FTS4 table > named 'patterns' which has 'content=patterns_content' set. It will have > only ASCII characters. Something like, > > ab > abd > abcd > . > . > > Given a string, 'abcdef', I'd like to tokenize it based on the above > table. My tokenizer is greedy and always looks for longest prefix match. > Which means, > > Eg1: tokenize('abcdef') = 'abcd' + tokenize(ef) > Eg2: tokenize('xyzabdab') = tokenize(xyx) + 'abd' + 'ab' > > I have tried all these methods to find the longest prefix match. > > Method1 - select * from patterns where pattern match 'abcdef OR abcde OR > abcd OR abc OR ab OR a' order by length(pattern) desc limit 1 > > Method2 - select * from patterns_content where pattern = 'abcdef' OR > pattern = 'abcde' OR pattern = 'abcd' OR pattern = 'abc' OR pattern = 'ab' > OR pattern = 'a' order by length(pattern) desc limit 1 > > Method3: Start reading from left and read one character at a time. Each > run, execute select 1 from patterns_content as c where c.pattern >= 'a' and > c.pattern <= 'a' || 'z' limit 1. Next run, it will test for 'ab', then > 'abc' etc. > > All the above methods works well. Surprisingly, the last method > outperforms all other methods. I am wondering what optimization is making > the last query always execute fast? Is it reliable to assume that the query > will have it performance even with huge number of rows? > > I am also wondering if there are other ways to solve this problem in an > efficient way? Any help would be great. My table will have more than a > million of data. > > -- > -Navaneeth > -- -n ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How is this query so fast?
Hello, I have a table which contains a set of words. These are stored in a table called 'patterns_content' with pattern as unique key. There is a FTS4 table named 'patterns' which has 'content=patterns_content' set. It will have only ASCII characters. Something like, ab abd abcd . . Given a string, 'abcdef', I'd like to tokenize it based on the above table. My tokenizer is greedy and always looks for longest prefix match. Which means, Eg1: tokenize('abcdef') = 'abcd' + tokenize(ef) Eg2: tokenize('xyzabdab') = tokenize(xyx) + 'abd' + 'ab' I have tried all these methods to find the longest prefix match. Method1 - select * from patterns where pattern match 'abcdef OR abcde OR abcd OR abc OR ab OR a' order by length(pattern) desc limit 1 Method2 - select * from patterns_content where pattern = 'abcdef' OR pattern = 'abcde' OR pattern = 'abcd' OR pattern = 'abc' OR pattern = 'ab' OR pattern = 'a' order by length(pattern) desc limit 1 Method3: Start reading from left and read one character at a time. Each run, execute select 1 from patterns_content as c where c.pattern >= 'a' and c.pattern <= 'a' || 'z' limit 1. Next run, it will test for 'ab', then 'abc' etc. All the above methods works well. Surprisingly, the last method outperforms all other methods. I am wondering what optimization is making the last query always execute fast? Is it reliable to assume that the query will have it performance even with huge number of rows? I am also wondering if there are other ways to solve this problem in an efficient way? Any help would be great. My table will have more than a million of data. -- -Navaneeth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXT : Unknown module FTS4
Hello, On Mon, Jul 30, 2012 at 6:43 PM, Black, Michael (IS) wrote: > You've got me totally confusedyou say "shared library" and > "dynamically linked" but then say it's embedded in the GUI. > > Which is it? > > Are you on Unix/Linux? > > Can you show us your Makefile or an example build line? > What are you compiling with? > > Have you duplicated your GUI build process on another program using all > the same settings and succeeded? > After some investigation, it looks like when building shared library using GCC, it exports all the SQLite functions too. And for some reason at runtime, functions gets resolved to the statically linked version in the GUI. I hope using visibility settings in GCC will fix this problem. I will try it and let you guys know. -- -n ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unknown module FTS4
Hello, I have a weird problem. I am working on a shared library, written using C and a GUI application written on C++. GUI application uses the shared library. This shared library uses SQLite amalgamation and links statically. GUI also uses SQLite for some configuration purpose. It is also statically linked. Both of them uses latest SQLite version. My shared library uses FTS4. I have enabled FTS4 by providing the compile time options while compiling the shared library. All works well with the shared library. All my tests in the shared library codebase is passing. Problem happens when I start using this in the GUI program. I am getting error like, "Unknown module FTS4". This is weird because I have it linked statically in my shared library and all this GUI program does is to dynamically link to my library. When I set the FTS compilation options to the GUI program, error goes away and all works well. I am not sure why this is happening. Any help would be great! -- -n ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling concurrent read request to SQLite
On Thu, Jul 26, 2012 at 11:37 AM, Simon Slavin wrote: > > Have you set a timeout ? If you haven't the SQLite functions never back > off and retry when they find the database locked, they just immediately > return an error. > Thanks a lot. I didn't know about this feature. I will give it a try. -- -n ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Handling concurrent read request to SQLite
Hello, I am writing a shared library which uses SQLite as the file format. This library has got a learning subsystem which can learn a word and all possible ways to type that word. Usually the word will be UTF-8 encoded indic text and patterns will be words with latin characters. I am using the SQLite C API and WAL as journal mode. All of this works really well on local machine. Recently, I implemented a web version of my program which internally uses the shared library. There will be REST URLs exposed for the "learn" API call, something like "http://websitename.com/learn"; with the word to learn in the request parameters. Since the web-server allows concurrent requests, there could be a possibility that two requests for learn getting executed in parallel. In this case, SQLite fails with error message "Database is locked" as there would be one writer already in progress. I am looking for the best way to workaround this problem. Currently, I have implemented a queue at the server side which will queue all the requests for learn. Another worker process reads this queue and call my library routine for each word sequentially. This works well. But I am wondering is this the right way to workaround this problem? Any help would be great! -- -n ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to avoid duplicate entries in FTS table?
Hello, On Sun, Jul 1, 2012 at 2:27 PM, Petite Abeille wrote: > > On Jul 1, 2012, at 9:11 AM, Navaneeth.K.N wrote: > > > Now, repeating a "pattern" and "id" combination is an error to me. There > > should be always one "pattern" to "id" combination. If this was not a > > virtual table, I'd have solved the problem by creating a primary key on > > both "pattern" and "id". But this trick is not working on FTS tables. > > Perhaps you could try the following setup: > > (1) Create a regular table to hold your unique patterns, using an unique > constraint > > create table foo > ( > id integer not null constraint foo_pk primary key, > bar text, > constraint foo_uk unique( bar ) > ) > > (2) Create a FTS table with external content [1] to search the above > > create virtual table foo_bar using fts4 > ( > content = "foo", > bar text > ) > > I knew this. I was more worried about the performance. Will there be a performance difference comparing to data stored directly on the FTS table? -- -n ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to avoid duplicate entries in FTS table?
Hello, I have a table wth the following schema. create virtual table patterns using fts4 (pattern text, id integer) Now, repeating a "pattern" and "id" combination is an error to me. There should be always one "pattern" to "id" combination. If this was not a virtual table, I'd have solved the problem by creating a primary key on both "pattern" and "id". But this trick is not working on FTS tables. So to ensure the unique "pattern" to "id" combinations, I have to do something like, insert into patterns (pattern, id) select ?1, ?2 where not exists (select 1 from patterns where pattern match ?1 and id = ?2); This is not efficient because this does a linear table scan on patterns table. I couldn't find a way to use multiple match on a single statement. Something like, pattern match ?1 and id match ?2. This was failing with error " unable to use function MATCH in the requested context". I am also concerned about the thread safety of this approach. is there a possibility of getting two threads/processes execute the inner select at the same time which will yield to duplicate rows? I am using latest sqlite and all my queries are inside a transaction which was started by executing "BEGIN". Each process/thread will be using separate connection to the database. I am confused about how to solve this problem. Any help would be appreciated. -- -n ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cache all pages
Hello, On Wed, May 9, 2012 at 5:15 PM, Simon Slavin wrote: > > Sure. That would cache the data. And then the next thing that needs to > be cached might overwrite it all again. You're messing with something that > your OS thinks it has sole control over. > > > Are there any API functions that will tell how many pages are currently > > cached, cache misses etc..? > > The problem with this is it will change from run to run. Sometimes your > program will be the only thing running. Other times other apps will be > open. Sometimes they'll be printing in the background. Sometimes the OS > will be defragging in the background. Sometimes a virus-checker will > spring into action. Sometimes the computer will be left idle and a > screensaver will start up. > I think I am missing something here. I was thinking that caching of pages is SQLite's implementation and nothing to do with the OS. I'd think the cache is associated with each database connection and Sqlite caches all the pages it reads until the maximum limit. Is this the correct understanding? If yes, how will other applications starting or doing some work in background affects SQLite's cache? When documentation says SQLIte caches pages, are we talking about operating system level paging and caching? I thought about in memory databases. But technically, caching pages at SQLite level or using an in-memory database makes no difference here, rigtht? BTW, this database is used only for reads. Writes are performed only one time. Thanks -- -Navaneeth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Cache all pages
Hello, I have a SQLite database which has got 60 pages. For performance reasons, I am thinking of making SQLite cache all of this 60 pages, so for further queries no disk read will be performed. I believe when all pages are cached, SQLIte just has to read the cached pages and would be faster. To do this, when my library is initialized, I will execute a query like, SELECT * FROM symbols; Symbols table contains all my data. I am not sure how SQLite does the page caching. So I am iterating over the results using sqlite3_step() and each iteration will read all of the columns. Is this enough for SQLite to build the cache? Or just executing the above query without iterating over rows would be sufficient? Are there any API functions that will tell how many pages are currently cached, cache misses etc..? Any help would be great -- -Navaneeth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with binding parameters to LIKE
On Sun, Oct 23, 2011 at 2:21 PM, Baruch Burstein wrote: > I have done something similar and it worked for me, but there is an issue > with indexes you should take into account, as discussed here: > http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2011-July/031470.html Thanks a lot for that link. Sqlite was not using index even in my case. So I removed the concatenation in the query and passed a single parameters which will have % appended. I can see Sqlite uses index now. Much better! > . > Out of curiosity (since this query and it's field names seem very similar to > one I am using), what are you using this for? I am developing a text editor for indic languages. It has some amount of artificial inteligence builtin. I use the above said scheme to remeber words entered into the editor. > On Sun, Oct 23, 2011 at 7:36 PM, Igor Tandetnik wrote: > > It should. Check the value of "data" variable - you are probably passing > something other than what you think you are. I don't think anything wrong > with the code you've shown - the problem must lie in the code you haven't. My bad. I was passing an incorrectly encoded string. Corrected the encoding and all started working. > On Sun, Oct 23, 2011 at 4:28 PM, Richard Hipp wrote: > > sqlite3_trace() does, since version 3.6.21 (2009-12-07). What version of > SQLite did you say you were using? I got it working. I was reseting the parameters at a wrong location. This is the reason why trace was not showing the parameter value. All works well. Thanks for the help. -- -n ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem with binding parameters to LIKE
Hello, I am trying to use parameters in a LIKE query. I have the following code which uses Sqlite C/C++ API. const char *sql = "SELECT word FROM words WHERE word LIKE ?1 || '%' ORDER BY freq DESC LIMIT 10;"; int rc = sqlite3_prepare_v2 (db, sql, -1, &stmt, NULL); if ( rc != SQLITE_OK ) return false; sqlite3_bind_text ( stmt, 1, data , -1, NULL ); Unfortunaltly, this won't work. Sqlite is executing the statement successfully, but I am not getting the expected result. When I execute the same statement after removing parameters it works perfectly. Something like, const char *sql = "SELECT word FROM words WHERE word LIKE 'word'%' ORDER BY freq DESC LIMIT 10;"; It looks like concatentation with parameters is not working for some reason. To debug the issue, I hooked up sqlite3_trace and sqlite3_profile and printed the SQL being executed. Unfortunatly, these routines won't give the SQL with values bound to it. I am running out of ideas and any help would be great to address the problem. Thanks -- -n ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Will opening database in ReadOnly mode result in better query time
> > You may have opened the file as read-only, but someone else may open the > same file for writing. Thus, your connection still needs to maintain a > shared lock, just like any other reader. Thanks. I understand this. But my file will be on a read-only medium. So no other connection opening for writing is not possible. -- -n ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Will opening database in ReadOnly mode result in better query time
Hello, I have an application that uses SQLite just for querying. Application will not write anything to the database. So I am wondering will I get a better query time if the database is opened with flag SQLITE_OPEN_READONLY? I am guessing on a read only database, SQLite doesn't have to do any kind of locking which will lead to performance improvement. -- -n ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple threads sharing one DB
On Wed, Jan 26, 2011 at 10:24 PM, Ian Hardingham wrote: > Many thanks Eric. > > Does a write on Table A block a read/write on Table B? AFAIK, it does. The lock is acquired on the whole file and not on tables. -- -n ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select statement not returning any result
> Ah, just read your new post. Seems you've found the error in code. Good :-) > Thanks everyone for the help. I fixed my code and it is working fine. However, I am wondring why the function (sqlite3_bind_text) don't respect NULL character in the string and stop reading when it find one? -- Navaneeth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select statement not returning any result
Hello, On Sun, Jan 16, 2011 at 9:41 PM, Drake Wilson wrote: > Quoth "Navaneeth.K.N" , on 2011-01-16 21:31:42 +0530: >> rc = sqlite3_bind_text(stmt, 2, tok->pattern, VARNAM_SYMBOL_MAX, >> NULL); /* debugged and tok->pattern doesn't have any extra >> characters. strlen(tok->pattern) return 1 */ > > ... and yet you're passing a length of VARNAM_SYMBOL_MAX instead, > which I'm guessing is not 1. Pass the real length of the string (not > the size of the buffer), or -1 to treat it as a NUL-terminated C > string. Otherwise you're grabbing extra bogus bytes. Awesome! It worked. I was expecting the function will stop reading characters when it finds a NULL terminator. Thanks for correcting it. -- Navaneeth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select statement not returning any result
> > Hmmm, OK, try: > > .dump symbols > > and see what you get. I get the same queries and I am not seeing anything unusual with that. I tried creating another database through the Sqlite command line tool and executed the output got from ".dump symbols". In that DB, the selects seems to be working fine. So I am suspecting the way my application creates and inserts DB is wrong. Here is what I am doing. #define VSTGEN_SYMBOLS_STORE "symbols" snprintf(sql, 500, "insert into %s values (?1, ?2, ?3, ?4, ?5);", VSTGEN_SYMBOLS_STORE); rc = sqlite3_prepare_v2( db, sql, 500, &stmt, NULL ); if( rc == SQLITE_OK ) { /* */ rc = sqlite3_bind_text(stmt, 2, tok->pattern, VARNAM_SYMBOL_MAX, NULL); /* debugged and tok->pattern doesn't have any extra characters. strlen(tok->pattern) return 1 */ if(rc != SQLITE_OK) { /* error reporting */ } /* other parameter bindings */ rc = sqlite3_step( stmt ); } Finally the transaction will get committed. > > Id the above dump doesn't help, email me the file directly and I'll have a > look. > Thanks & Done! -- Navaneeth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select statement not returning any result
Thanks for replying > > You've probably used the wrong form of quotes somewhere and either your > database fields or your SELECT has the n with some form of quote marks around > it. Try using the command-line tool with exactly this request: I tried everything through the command line tool. "select * from symbols where pattern = 'n'" return nothing. "select pattern from symbols" returns 2 rows with no quotes or white spaces. Just to ensure white spaces are not there, I tried "select length(pattern) from symbols" and it return two rows with length 1. > Using the sqlite3 command line tool, try: > .mode insert > select * from symbols; > Reply here with one of the lines showing an 'n'. The insert syntax should > make it clear what's going on. That is a nice trick. But it shows the proper values. INSERT INTO table VALUES('co','n','','',0); INSERT INTO table VALUES('vo','a','','',0); > > This mailing list does not allow attachments: most of the people who read it > won't read most of the messages. > I was not aware of this. Thanks for pointing it out. -- Navaneeth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Select statement not returning any result
Hello, I have a database with a table named "symbols". This has the following schema. CREATE TABLE symbols (type TEXT, pattern TEXT, value1 TEXT, value2 TEXT, children INTEGER); I am using the C API of Sqlite and my application inserts records into the above table. Everything is executed inside a transaction and if all looks good, application will commit the transaction. Now after the DB file has been created, "select * from symbols;" shows all the records available. Consider I have patterns like "n" & "a". A select statement like "select * from symbols where pattern = 'n'" returns no records. Same thing happens if I do "select * from symbols where pattern = 'a'". But when I do "select * from symbols where pattern like 'n'" it returns the matching records. I am wondering why this is happening? Please find the attached the database. Sqlite version - 3.7.4 Any help would be appreciated -- Navaneeth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Compilation flags required for amalgamation
Hello, I am trying to understand the compilation flags required to use for compiling the amalgamation copied into my source code directory. I can't use the make files provided with the amalgamation as I am using a different build system using CMake. Currently I am using only "SQLITE_THREADSAFE=1" . But is there any other flags that needs to be set? Or will the amalgamation sets some default values? I will be using GCC on linux and MSVC on windows. When I compile, getting a warning like "sqlite3.c:795: warning: ISO C90 does not support ‘long long’". What can I do to avoid this warning? Any help would be great! -- Navaneeth ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users