[sqlite] Query optimization: Checking for existence before performing action

[Navaneeth.K.N]

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?

[Navaneeth.K.N]

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

[Navaneeth.K.N]

Hello,

Thanks for the help.

On Sun, Mar 10, 2013 at 10:36 PM, Igor Tandetnik <i...@tandetnik.org> 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

[Navaneeth.K.N]

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

[Navaneeth.K.N]

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

[Navaneeth.K.N]

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?

[Navaneeth.K.N]

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 <navaneet...@gmail.com>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?

[Navaneeth.K.N]

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

[Navaneeth.K.N]

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

[Navaneeth.K.N]

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

[Navaneeth.K.N]

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

[Navaneeth.K.N]

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?

[Navaneeth.K.N]

Hello,

On Sun, Jul 1, 2012 at 2:27 PM, Petite Abeille <petite.abei...@gmail.com>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?

[Navaneeth.K.N]

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

[Navaneeth.K.N]

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

[Navaneeth.K.N]

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

[Navaneeth.K.N]

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

[Navaneeth.K.N]

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, , 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

[Navaneeth.K.N]

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

[Navaneeth.K.N]

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

[Navaneeth.K.N]

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

[Navaneeth.K.N]

> 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

[Navaneeth.K.N]

Hello,

On Sun, Jan 16, 2011 at 9:41 PM, Drake Wilson <dr...@begriffli.ch> wrote:
> Quoth "Navaneeth.K.N" <navaneet...@gmail.com>, 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

[Navaneeth.K.N]

>
> 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, , 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

[Navaneeth.K.N]

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

[Navaneeth.K.N]

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

[Navaneeth.K.N]

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