[sqlite] SQLITE_MISUE returned from sqlite3_step with inconsistent errMsg

[Daniel Dawson]

Hey Guys,

I am calling sqlite3_step and checking the return value,

Usually I get an SQLITE_DONE, or an SQLITE_BUSY which I handle.

However, sometimes I get an SQLITE_MISUSE return code. If I call sqlite3_errmsg 
straight after receiving the code then I get "Database is Locked" - The 
documentation states that SQLITE_MISUSE only occurs if I call the library 
routines incorrectly.

I don't understand why this would happen occasionally. I am not using any 
custom compiler options, everything is just set up by default.

Any help on the matter would be great, thanks.

~ Dan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] about sqlite3_create_function

[Kevin Benson]

Thgat should have been "...the file func.c."
--
   --
  --
 --Ô¿Ô--
K e V i N


On Thu, Aug 30, 2012 at 11:10 PM, Kevin Benson wrote:

> the file*func.c*."
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] about sqlite3_create_function

[Kevin Benson]

On Thu, Aug 30, 2012 at 10:25 PM, YAN HONG YE  wrote:

> who give me a sample about function sqlite3_create_function,I can't search
> by google.
> thank you!
> sqlite3_create_function(db, "hello_newman", 0, SQLITE_UTF8, 0,
> hello_newman, 0, 0);
> ___
>

http://www.sqlite.org/c_interface.html

" For additional information and examples on how to create new SQL
functions, review the SQLite source code in the file*func.c*."
--
   --
  --
 --Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] about sqlite3_create_function

[YAN HONG YE]

   who give me a sample about function sqlite3_create_function,I  can't search 
by google.
thank you!
 sqlite3_create_function(db, "hello_newman", 0, SQLITE_UTF8, 0,
  hello_newman, 0, 0);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ONLY phrase in FTS4

[E. Timothy Uy]

Hi, if I am using the Porter tokenizer in FTS4 and want an exact "stemmed"
match, how would I proceed?  For example, I am if I am looking for
"completed missions" -> "complet mission", I don't want "American completed
missions".  Is it possible?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] why no such column in sqlite3 ?

[Black, Michael (IS)]

As for sprintf what they didn't tell you is that you don't want to use that due 
to security considerations.

If you are getting ANY data from user input they can craft sql injection 
attacks which sprintf is very susceptible to.

Binding the values helps to ensure they can't do that.

Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Vaclav Peroutka [vacla...@seznam.cz]
Sent: Thursday, August 30, 2012 3:51 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] why no such column in sqlite3 ?

> Rob Richardson wrote:
>> Put single quotes around Testitem:
>>
>> sprintf( sqlquery, "INSERT INTO tblTest ( CINDEX, CDATE, CDESCR, CAMOUNT
) VALUES ( 5, 2012-08-29, 'Testitem', 300 )");
>
> And around cdate too. There are no dedicated date type in sqlite, 2012-08-
29 is
> treated as expression ((2012 - 08) - 29). Result will be 1975, not what
you
> might have expected.
>
> sprintf( sqlquery, "INSERT INTO tblTest ( CINDEX, CDATE, CDESCR, CAMOUNT )
> VALUES ( 5, '2012-08-29', 'Testitem', 300 )");
>
> And you likely should use sqlite3_prepare_v2, placeholders, sqlite3_bind_
int and
> sqlite3_bind_text instead of sprintf.

Thank you for answers, single quotes helped.

Regarding other functions, is there any example for them ? I used "5
minutes" example and there is nothing like that. sprintf formatting works
well for me so far.
___
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

Re: [sqlite] Choosing the best query plan

[Richard Hipp]

On Thu, Aug 30, 2012 at 4:02 AM, Navaneeth.K.N wrote:

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

Which one runs faster on your system with actual data?


>
> Also, is there way to get rid of temporary B-TREE for order by?
>

Changing the UNIQUE constraint on the WORDS table to be
UNIQUE(word,confidence) will probably do the trick.


>
> --
> Thanks
> Navaneeth
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] why no such column in sqlite3 ?

[Vaclav Peroutka]

> Rob Richardson wrote:
>> Put single quotes around Testitem:
>>
>> sprintf( sqlquery, "INSERT INTO tblTest ( CINDEX, CDATE, CDESCR, CAMOUNT 
) VALUES ( 5, 2012-08-29, 'Testitem', 300 )");
> 
> And around cdate too. There are no dedicated date type in sqlite, 2012-08-
29 is
> treated as expression ((2012 - 08) - 29). Result will be 1975, not what 
you
> might have expected.
> 
> sprintf( sqlquery, "INSERT INTO tblTest ( CINDEX, CDATE, CDESCR, CAMOUNT )
> VALUES ( 5, '2012-08-29', 'Testitem', 300 )");
> 
> And you likely should use sqlite3_prepare_v2, placeholders, sqlite3_bind_
int and
> sqlite3_bind_text instead of sprintf.

Thank you for answers, single quotes helped.

Regarding other functions, is there any example for them ? I used "5 
minutes" example and there is nothing like that. sprintf formatting works 
well for me so far.
___
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