[sqlite] Does sqlite3_exec work with sqlite3_bind ?

2012-06-21 Thread deltagam...@gmx.net
sqlite3_prepare_v2() , 
sqlite3_step() , and 
sqlite3_finalize() ,


From the documentation  I saw, that sqlite3_exec "includes" 
sqlite3_prepare_v2 , sqlite3_step, sqlite3_finalize.
But if the select_statement contains some variables to bind, is it 
possible to use then the wrapper sqlite3_exec ?


If the select_statement would be
select_statement = "SELECT id, eventdate, eventtype, counter FROM 
eventlog WHERE eventdate BETWEEN ? AND ?";


how would I use the wrapper sqlite3_exec ?


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


Re: [sqlite] Testing the ODBC link

2012-06-21 Thread Simon Slavin

On 22 Jun 2012, at 2:17am, Kyle McKay  wrote:

> I have successfully used the SQLite ODBC Driver available from:
> 
> http://www.ch-werner.de/sqliteodbc/

Hey, thanks for that, Kyle.  I always wondered whether I could make ODBC do 
SQLite but had no idea how to make it happen.  Your instructions worked fine 
for me and I successfully made a toy database.  Nice.

I agree that somewhere in that chain is the idea that only SELECT commands 
return results, and that that's the reason PRAGMAs can't return values.  Hmm.  
Can you do

SELECT (PRAGMA user_version)

?  Bah.

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


Re: [sqlite] Difference between 3.7.11 and 3.7.13

2012-06-21 Thread Keith Medcalf

Either both of the following should execute, or neither should.  And they 
should both produce the same error message if they are not executable.

SELECT max((select avg(x) FROM t2)) FROM t1;
SELECT max((SELECT x FROM t2 limit 1)) FROM t1;

In other words, either you can access columns from the outer query inside a 
correlated subquery, or you cannot.  Since the latter is acceptable but not the 
former, there is a bug.

Similarly, these two:

sqlite> SELECT max((SELECT avg(x+y) FROM t2)) FROM t1;
Error: misuse of aggregate: avg()
sqlite> SELECT max((SELECT x+y FROM t2)) FROM t1;

sqlite> insert into t1 values (1), (2), (3);
sqlite> insert into t2 values (1), (2), (3);
sqlite> SELECT max((SELECT x+y FROM t2)) FROM t1;
4
sqlite> SELECT max((SELECT avg(x+y) FROM t2)) FROM t1;
Error: misuse of aggregate: avg()

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: Thursday, 21 June, 2012 14:10
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Difference between 3.7.11 and 3.7.13
> 
> On 6/21/2012 3:52 PM, Pavel Ivanov wrote:
>  CREATE TABLE t1(x);
>  CREATE TABLE t2(y);
>  SELECT max((SELECT avg(x) FROM t2)) FROM t1;
> >
> > So you are saying that behavior of such query should be equivalent to
> > "SELECT max(x) FROM t1"?
> 
> Not quite, but you get the idea. I think "select avg(SomeConstExpr) from
> t2" would return NULL rather than SomeConstExpr if t2 is empty. So it
> should be equivalent to
> 
> select max(case when (select count(*) from t2) == 0 then null else x
> end) from t1;
> --
> Igor Tandetnik
> 
> ___
> 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] Testing the ODBC link

2012-06-21 Thread Kyle McKay

On June 20, 2012 08:43:31 PDT, Maury Markowitz wrote:
I'm working on a OSX10.7 ODBC query interface - type SQL, get  
results. It uses the open-source iODBC library set. I've got this  
working fairly well with MySQL (including major public servers on  
the 'net, cool!) and Firebird.


I'd like to test it against SQLite as well, as this is obviously a  
common DB on the Mac. I downloaded a version of the Northwind DB  
that was converted to SQLite format, and to make sure that worked, I  
also downloaded a wonderful (but SQLite-only) DB viewer known as  
"Base". Everything is working well.


However, when I attempt to connect to the DB using the ODBC driver,  
I connect fine but the database is not selected. This may be nothing  
more than the driver reporting the connection wrong, but I can't be  
sure...


So can anyone suggest any SQLite command I might send in through the  
SQL interface that should return something even if there is no  
active DB connection?


With MySQL I'd use "show databases" or "use mysql", is there an  
equivalent in SQLite?


If you haven't already, install the ODBC Administrator tool.  (It gets  
installed to /Applications/Utilities.)  Apple's been leaving it out  
for some reason since 10.6 but provides it as a separate download at:


http://support.apple.com/kb/DL895

I have successfully used the SQLite ODBC Driver available from:

http://www.ch-werner.de/sqliteodbc/

I have it installed on my system as /usr/local/lib/ 
libsqlite3odbc.dylib but it can really go anywhere.


So I can do (from Terminal):

iodbctest 'DRIVER=/usr/local/lib/libsqlite3odbc.dylib;DATABASE=/tmp/ 
test.sq3'


And use the ODBC interface to manipulate the /tmp/test.sq3 SQLite  
database successfully.  If you set up a DSN using ODBC Adminstrator  
(say named test3) that has the SQLite3 ODBC driver and database name  
already set you can just do this:


iodbctest DSN=test3

I was never able to get SQLite pragmas that return values to work via  
the ODBC driver.  It seems to execute pragmas that set values okay  
though.  For example, you can execute 'pragma user_version=2;'  But if  
you execute 'pragma user_version;' expecting to get a row back from  
ODBC you won't since it's not a select statement (and there's no  
_pragma_value('user_version') or equivalent function that can be used  
in a select statement instead).


If you're using the ODBC Administrator to set this up, first click the  
Drivers tab and add a driver that uses the SQLite3 ODBC Driver, then  
click one of the two DSN tabs and add a DSN that uses that driver and  
then explicitly add a Database keyword with the value being the full  
path to the SQLite3 database.  The SQLite3 ODBC driver mostly only  
cares about the database keyword.


It does, however, support several other keywords to control things  
like foreign key support etc., but they don't seem to be well  
documented other than by looking at the code.  You can view the source  
file at:


http://www.ch-werner.de/sqliteodbc/html/sqlite3odbc_8c-source.html

Then look at the source starting around line 10643.

Finally if you want to get an SQLite3 table list via ODBC, you can  
always do something like:


SELECT name FROM sqlite_master WHERE type = 'table';

Kyle

P.S. As Simon already mentioned, 'SELECT sqlite_version();' will get  
you the version if you're successfully connected to the SQLite driver.

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


Re: [sqlite] An interesting (strange) issue with selects

2012-06-21 Thread Dennis Volodomanov

On 22/06/2012 9:33 AM, Pavel Ivanov wrote:
I believe result of integrity_check won't depend on compilation flags, 
at least not on those you define. Maybe there's some bug surfacing 
when you use those compilation flags... Pavel 


I've also removed all transactions from the latest code. There were no 
transactions surrounding these particulars SQL statements, but perhaps 
they were causing some issue elsewhere which in turn lead to some 
strange state of the db - just guessing, of course. The database is 
accessed from multiple processes and multiple threads, so at any given 
time there are at least 3-4 readers and potentially 2 writers.


I'll see if the new compilation options still make this happen, but it 
takes a couple of hours for each test due to data volume and I'd need to 
run a few tests (unless it occurs right away of course). I'll post back.


Thanks!

   Dennis

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


Re: [sqlite] An interesting (strange) issue with selects

2012-06-21 Thread Pavel Ivanov
On Thu, Jun 21, 2012 at 7:21 PM, Dennis Volodomanov  wrote:
> On 22/06/2012 2:15 AM, Pavel Ivanov wrote:
>>
>> Then it should be okay to do such queries concurrently. So you are saying
>> that two SELECTs you initially showed us give contradicting results from
>> your application and work as expected from sqlite3 shell, right? The only
>> ideas I have left to try are execute "pragma integrity_check" on this
>> database and try to compile without SQLITE_OMIT_DEPRECATED and without
>> SQLITE_ENABLE_STAT3... Pavel
>
>
> Yes, I should've given more details from the start, sorry.
>
> I'll try re-compiling and see if I can reproduce this. The integrity_check
> returns "ok" (from the shell, haven't tried it from the application).

I believe result of integrity_check won't depend on compilation flags,
at least not on those you define. Maybe there's some bug surfacing
when you use those compilation flags...


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


Re: [sqlite] An interesting (strange) issue with selects

2012-06-21 Thread Dennis Volodomanov

On 22/06/2012 2:15 AM, Pavel Ivanov wrote:
Then it should be okay to do such queries concurrently. So you are 
saying that two SELECTs you initially showed us give contradicting 
results from your application and work as expected from sqlite3 shell, 
right? The only ideas I have left to try are execute "pragma 
integrity_check" on this database and try to compile without 
SQLITE_OMIT_DEPRECATED and without SQLITE_ENABLE_STAT3... Pavel 


Yes, I should've given more details from the start, sorry.

I'll try re-compiling and see if I can reproduce this. The 
integrity_check returns "ok" (from the shell, haven't tried it from the 
application).


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


Re: [sqlite] Counting entries returned from a compound SELECT.

2012-06-21 Thread Igor Tandetnik

On 6/21/2012 4:07 PM, Peter Haworth wrote:

Hoping someone can provide a way to return a count of the number of entries
returned by a compound SELECT statement, specifically "SELECT …. EXCEPT
SELECT….".


select count(*) from ();
--
Igor Tandetnik

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


Re: [sqlite] Counting entries returned from a compound SELECT.

2012-06-21 Thread Petite Abeille

On Jun 21, 2012, at 10:07 PM, Peter Haworth wrote:

> returned by a compound SELECT statement, specifically "SELECT …. EXCEPT
> SELECT….".

select count( * ) from ( /* compound */ )  ?

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


Re: [sqlite] Difference between 3.7.11 and 3.7.13

2012-06-21 Thread Igor Tandetnik

On 6/21/2012 3:52 PM, Pavel Ivanov wrote:

CREATE TABLE t1(x);
CREATE TABLE t2(y);
SELECT max((SELECT avg(x) FROM t2)) FROM t1;


So you are saying that behavior of such query should be equivalent to
"SELECT max(x) FROM t1"?


Not quite, but you get the idea. I think "select avg(SomeConstExpr) from 
t2" would return NULL rather than SomeConstExpr if t2 is empty. So it 
should be equivalent to


select max(case when (select count(*) from t2) == 0 then null else x 
end) from t1;

--
Igor Tandetnik

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


[sqlite] Counting entries returned from a compound SELECT.

2012-06-21 Thread Peter Haworth
Hoping someone can provide a way to return a count of the number of entries
returned by a compound SELECT statement, specifically "SELECT …. EXCEPT
SELECT….".
Thanks,
Pete
lcSQL Software 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Difference between 3.7.11 and 3.7.13

2012-06-21 Thread Pavel Ivanov
On Thu, Jun 21, 2012 at 3:41 PM, Igor Tandetnik  wrote:
> On 6/21/2012 12:17 PM, Pavel Ivanov wrote:
>>
>> On Thu, Jun 21, 2012 at 11:47 AM, Marco Bambini  wrote:
>>>
>>> Consider the following example:
>>>
>>> CREATE TABLE t1(x);
>>> CREATE TABLE t2(y);
>>> SELECT max((SELECT avg(x) FROM t2)) FROM t1;
>>>
>>> With sqlite 3.7.11 NULL is returned, while with sqlite 3.7.13 an error
>>> "Misuse of aggregate: avg()" is returned.
>>> Any thought?
>>
>>
>> 3.7.11 had a bug, 3.7.13 fixed it. Your query is invalid, t2 doesn't
>> have column named x.
>
>
> But t1 does, and a nested select should be able to access it. I don't see
> why an aggregate function can't be applied to any expression, even one that
> happens to be constant across all rows of the table.
>
> I don't understand what makes this query invalid. Pointless, yes, but why
> invalid?

So you are saying that behavior of such query should be equivalent to
"SELECT max(x) FROM t1"? I didn't think about it like that... Probably
you are right.

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


Re: [sqlite] Difference between 3.7.11 and 3.7.13

2012-06-21 Thread Igor Tandetnik

On 6/21/2012 12:17 PM, Pavel Ivanov wrote:

On Thu, Jun 21, 2012 at 11:47 AM, Marco Bambini  wrote:

Consider the following example:

CREATE TABLE t1(x);
CREATE TABLE t2(y);
SELECT max((SELECT avg(x) FROM t2)) FROM t1;

With sqlite 3.7.11 NULL is returned, while with sqlite 3.7.13 an error "Misuse of 
aggregate: avg()" is returned.
Any thought?


3.7.11 had a bug, 3.7.13 fixed it. Your query is invalid, t2 doesn't
have column named x.


But t1 does, and a nested select should be able to access it. I don't 
see why an aggregate function can't be applied to any expression, even 
one that happens to be constant across all rows of the table.


I don't understand what makes this query invalid. Pointless, yes, but 
why invalid?

--
Igor Tandetnik

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


[sqlite] Windows sqlite3.dll without Debug info

2012-06-21 Thread BillP
Does anyone know if someone has compiled the recent sqlite3.dll without the 
 debug information?
 
I found the .dll and .def files which work nicely but I noticed that  
sqlite3.dll still contains debug information which I'm sure is making the DLL  
larger than it needs to be.  I have a very small app and would like to cut  
the size of my setup program.
 
I know the source code is available and I could do it myself but I'd be  
more comfortable if someone on the sqlite team has already created a copy.
 
Thanks,
Bill Pytlovany
 
 
 
_BillP Studios_ (http://www.billp.com/)  | This computer  protected by 
WinPatrol |
_http://www.winpatrol.com_ (http://www.winpatrol.com/) _  
http://billpstudios.blogspot.com/_ (http://billpstudios.blogspot.com/) 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] HTML5 database commands as a JavaScript-SQLite bridge

2012-06-21 Thread Simon Slavin

On 13 Jun 2012, at 3:50am, Simon Slavin  wrote:

> So we have an excellent JavaScript-SQLite bridge but it's intentionally 
> prevented from accessing SQLite databases you keep on your computer.  So it's 
> useful only if you only ever need to access the database inside a specific 
> browser. But then I thought … well, since it turns out that everyone 
> implements this using SQLite, what would happen if I did an ATTACH ?

And the results … nope.  The browsers I've tried it in prevent ATTACH and 
similar subterfuges in various different ways.  Strangely, they don't seem to 
explicitly trap it or provide an error code for it, but they've either removed 
it from the built-in version of SQLite they use, or the permissions the process 
runs under bar access to arbitrary folders.  I'm pleased everyone cared about 
security and annoyed that I can't use the slick HTML5 database functions to do 
what I want.

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


Re: [sqlite] C++ programming - int sqlite3_prepare_v2() question

2012-06-21 Thread Pavel Ivanov
>  Also, I have downloaded and installed 'SQLight Designer' and when opening 
> the database file, I have just created, all that I can see is the words: 
> SQLite format 3.

Apparently you open your database file in some plain text editor
instead of in something that understands what SQLite database is.


Pavel


On Thu, Jun 21, 2012 at 12:22 PM, Arbol One  wrote:
> namespace jme {
>  class Runner : public Gtk::Window {
>  private:
>  jme::Exception* e; // Exception handler
>  sqlite3 *db; // Data Base
>  sqlite3_stmt* stmt; // SQL statement
>  Glib::ustring dbName; // db name
>  Glib::ustring sName; // my name
>  Glib::ustring sAddress; // my address
>  Glib::ustring vfs; // MSWIN Identifier
>  int rc; // return code
>  int age; // my age
>  // SQL statement
>  Glib::ustring dbdata;
>  Glib::ustring create_table;
>  public:
>  Runner();
>  ~Runner();
>  void OpenDB();
>  void CreateTable();
>  void AddData();
>  };
>  }
>  void jme::Runner::CreateTable(){
>  rc = sqlite3_prepare_v2(
>  db, /* Database handle */
>  create_table.c_str() , /* SQL statement, UTF-8 encoded */
>  create_table.length(), /* Maximum length of zSql in bytes. */
>  , /* OUT: Statement handle */
>  NULL /* OUT: Pointer to unused portion of zSql */
>  );
>  if(rc != SQLITE_OK) {
>  sqlite3_close(db);
>  std::cout << "error prepare_v2: " << rc << std::endl;
>  exit(-2);
>  }
>  rc = sqlite3_step(stmt);
>  if(rc != SQLITE_DONE) {
>  sqlite3_close(db);
>  std::cout << "error sqlite3_step: " << rc << std::endl;
>  exit(-3);
>  }
>  sqlite3_finalize(stmt);
>  }
>  void jme::Runner::AddData(){
>  rc = sqlite3_prepare_v2(
>  db, /* Database handle */
>  dbdata.c_str() , /* SQL statement, UTF-8 encoded */
>  dbdata.length(), /* Maximum length of zSql in bytes. */
>  , /* OUT: Statement handle */
>  NULL /* OUT: Pointer to unused portion of zSql */
>  );
>  if(rc != SQLITE_OK) {
>  
>  }
>  rc = sqlite3_step(stmt);
>  if(rc != SQLITE_DONE) {
>  
>  }
>  sqlite3_finalize(stmt);
>  }
>  void jme::Runner::OpenDB() {
>  rc = sqlite3_open_v2(dbName.c_str(),
>  , SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
>  NULL);
>  if(rc != SQLITE_OK) {
>  
>  }
>  jme::Runner::Runner() {
>  dbName = "sqliteTest.sql";
>  sName = "ArbolOne";
>  sAddress = "1 Parkway Rd";
>  vfs = "win32";
>  age = 22;
>  dbdata = "INSERT INTO friend VALUES('Jamiil', '49 New Bridge', '49')";
>  create_table = "CREATE TABLE friend (name TEXT, address TEXT, age INT)";
>  try {
>  jme::gnu_io f;
>  f.deleteFile(dbName);
>  } catch(jme::Exception e) {
>  std::cout << e.what() << std::endl;
>  }
>  OpenDB();
>  CreateTable();
>  AddData();
>
>  }
>  jme::Runner::~Runner(){
>  sqlite3_close(db);
>  cout << "Good bye!" << endl;
>  }
>  int main(int argc, char *argv[]) {
>  try {
>  Gtk::Main kit(argc, argv);
>  jme::Runner dbtest;
>  Gtk::Main::run(dbtest);
>  } catch(jme::Exception& x) {
>  x.Display();
>  }
>  return 0;
>  }
>  This is what I have done so far, and following your advice I hade added 
> 'sqlite3_finalize(stmt)' each time I am done with it. Also, I have downloaded 
> and installed 'SQLight Designer' and when opening the database file, I have 
> just created, all that I can see is the words: SQLite format 3.
>  Come on you folk, you have more experience than me using SQLite, pich in!. 
> What else do I need to do?
>
>  TIA
>
>  void jme::Runner::CreateTable(){
>  rc = sqlite3_prepare_v2(
>  db, /* Database handle */
>  create_table.c_str() , /* SQL statement, UTF-8 encoded */
>  create_table.length(), /* Maximum length of zSql in bytes. */
>  , /* OUT: Statement handle */
>  NULL /* OUT: Pointer to unused portion of zSql */
>  );
>  if(rc != SQLITE_OK) {
>  sqlite3_close(db);
>  std::cout << "error prepare_v2: " << rc << std::endl;
>  exit(-2);
>  }
>  rc = sqlite3_step(stmt);
>  if(rc != SQLITE_DONE) {
>  sqlite3_close(db);
>  std::cout << "error sqlite3_step: " << rc << std::endl;
>  exit(-3);
>  }
>  sqlite3_finalize(stmt);
>  }
> ___
> 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] C++ programming - int sqlite3_prepare_v2() question

2012-06-21 Thread Arbol One
namespace jme {
 class Runner : public Gtk::Window {
 private:
 jme::Exception* e; // Exception handler
 sqlite3 *db; // Data Base
 sqlite3_stmt* stmt; // SQL statement
 Glib::ustring dbName; // db name
 Glib::ustring sName; // my name
 Glib::ustring sAddress; // my address
 Glib::ustring vfs; // MSWIN Identifier
 int rc; // return code
 int age; // my age
 // SQL statement
 Glib::ustring dbdata;
 Glib::ustring create_table;
 public:
 Runner();
 ~Runner();
 void OpenDB();
 void CreateTable();
 void AddData();
 };
 }
 void jme::Runner::CreateTable(){
 rc = sqlite3_prepare_v2(
 db, /* Database handle */
 create_table.c_str() , /* SQL statement, UTF-8 encoded */
 create_table.length(), /* Maximum length of zSql in bytes. */
 , /* OUT: Statement handle */
 NULL /* OUT: Pointer to unused portion of zSql */
 );
 if(rc != SQLITE_OK) {
 sqlite3_close(db);
 std::cout << "error prepare_v2: " << rc << std::endl;
 exit(-2);
 }
 rc = sqlite3_step(stmt);
 if(rc != SQLITE_DONE) {
 sqlite3_close(db);
 std::cout << "error sqlite3_step: " << rc << std::endl;
 exit(-3);
 }
 sqlite3_finalize(stmt);
 }
 void jme::Runner::AddData(){
 rc = sqlite3_prepare_v2(
 db, /* Database handle */
 dbdata.c_str() , /* SQL statement, UTF-8 encoded */
 dbdata.length(), /* Maximum length of zSql in bytes. */
 , /* OUT: Statement handle */
 NULL /* OUT: Pointer to unused portion of zSql */
 );
 if(rc != SQLITE_OK) {
 
 }
 rc = sqlite3_step(stmt);
 if(rc != SQLITE_DONE) {
 
 }
 sqlite3_finalize(stmt);
 }
 void jme::Runner::OpenDB() {
 rc = sqlite3_open_v2(dbName.c_str(),
 , SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
 NULL);
 if(rc != SQLITE_OK) {
 
 }
 jme::Runner::Runner() {
 dbName = "sqliteTest.sql";
 sName = "ArbolOne";
 sAddress = "1 Parkway Rd";
 vfs = "win32";
 age = 22;
 dbdata = "INSERT INTO friend VALUES('Jamiil', '49 New Bridge', '49')";
 create_table = "CREATE TABLE friend (name TEXT, address TEXT, age INT)";
 try {
 jme::gnu_io f;
 f.deleteFile(dbName);
 } catch(jme::Exception e) {
 std::cout << e.what() << std::endl;
 }
 OpenDB();
 CreateTable();
 AddData();

 }
 jme::Runner::~Runner(){
 sqlite3_close(db);
 cout << "Good bye!" << endl;
 }
 int main(int argc, char *argv[]) {
 try {
 Gtk::Main kit(argc, argv);
 jme::Runner dbtest;
 Gtk::Main::run(dbtest);
 } catch(jme::Exception& x) {
 x.Display();
 }
 return 0;
 }
 This is what I have done so far, and following your advice I hade added 
'sqlite3_finalize(stmt)' each time I am done with it. Also, I have downloaded 
and installed 'SQLight Designer' and when opening the database file, I have 
just created, all that I can see is the words: SQLite format 3.
 Come on you folk, you have more experience than me using SQLite, pich in!. 
What else do I need to do?

 TIA

 void jme::Runner::CreateTable(){
 rc = sqlite3_prepare_v2(
 db, /* Database handle */
 create_table.c_str() , /* SQL statement, UTF-8 encoded */
 create_table.length(), /* Maximum length of zSql in bytes. */
 , /* OUT: Statement handle */
 NULL /* OUT: Pointer to unused portion of zSql */
 );
 if(rc != SQLITE_OK) {
 sqlite3_close(db);
 std::cout << "error prepare_v2: " << rc << std::endl;
 exit(-2);
 }
 rc = sqlite3_step(stmt);
 if(rc != SQLITE_DONE) {
 sqlite3_close(db);
 std::cout << "error sqlite3_step: " << rc << std::endl;
 exit(-3);
 }
 sqlite3_finalize(stmt);
 }
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Difference between 3.7.11 and 3.7.13

2012-06-21 Thread Pavel Ivanov
On Thu, Jun 21, 2012 at 11:47 AM, Marco Bambini  wrote:
> Consider the following example:
>
> CREATE TABLE t1(x);
> CREATE TABLE t2(y);
> SELECT max((SELECT avg(x) FROM t2)) FROM t1;
>
> With sqlite 3.7.11 NULL is returned, while with sqlite 3.7.13 an error 
> "Misuse of aggregate: avg()" is returned.
> Any thought?

3.7.11 had a bug, 3.7.13 fixed it. Your query is invalid, t2 doesn't
have column named x.


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


Re: [sqlite] An interesting (strange) issue with selects

2012-06-21 Thread Pavel Ivanov
On Thu, Jun 21, 2012 at 11:48 AM, Dennis Volodomanov  wrote:
> On 22/06/2012 1:37 AM, Dennis Volodomanov wrote:
>>
>> On 22/06/2012 1:29 AM, Pavel Ivanov wrote:
>>>
>>> Do you have mutex surrounding statement execution in these threads? You
>>> should use it or compile with SQLITE_THREADSAFE=1. Pavel
>>
>>
>> No, I don't have mutexes for those, as I assumed that THREADSAFE=2 would
>> protect them. I'm not clear about the exact difference between 1 and 2,
>> having read the docs a few times.
>>
>
> Just to add on to this, each thread opens up its own database connection
> (sqlite3_open_v2), so no threads are sharing sqlite3 handles.

Then it should be okay to do such queries concurrently.

So you are saying that two SELECTs you initially showed us give
contradicting results from your application and work as expected from
sqlite3 shell, right? The only ideas I have left to try are execute
"pragma integrity_check" on this database and try to compile without
SQLITE_OMIT_DEPRECATED and without SQLITE_ENABLE_STAT3...

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


Re: [sqlite] An interesting (strange) issue with selects

2012-06-21 Thread Dennis Volodomanov

On 22/06/2012 1:37 AM, Dennis Volodomanov wrote:

On 22/06/2012 1:29 AM, Pavel Ivanov wrote:
Do you have mutex surrounding statement execution in these threads? 
You should use it or compile with SQLITE_THREADSAFE=1. Pavel 


No, I don't have mutexes for those, as I assumed that THREADSAFE=2 
would protect them. I'm not clear about the exact difference between 1 
and 2, having read the docs a few times.




Just to add on to this, each thread opens up its own database connection 
(sqlite3_open_v2), so no threads are sharing sqlite3 handles.


   Dennis

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


[sqlite] Difference between 3.7.11 and 3.7.13

2012-06-21 Thread Marco Bambini
Consider the following example:

CREATE TABLE t1(x);
CREATE TABLE t2(y);
SELECT max((SELECT avg(x) FROM t2)) FROM t1;

With sqlite 3.7.11 NULL is returned, while with sqlite 3.7.13 an error "Misuse 
of aggregate: avg()" is returned.
Any thought?
--
Marco Bambini
http://www.sqlabs.com
http://twitter.com/sqlabs

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


Re: [sqlite] An interesting (strange) issue with selects

2012-06-21 Thread Pavel Ivanov
On Thu, Jun 21, 2012 at 11:37 AM, Dennis Volodomanov  wrote:
> On 22/06/2012 1:29 AM, Pavel Ivanov wrote:
>>
>> Do you have mutex surrounding statement execution in these threads? You
>> should use it or compile with SQLITE_THREADSAFE=1. Pavel
>
>
> No, I don't have mutexes for those, as I assumed that THREADSAFE=2 would
> protect them. I'm not clear about the exact difference between 1 and 2,
> having read the docs a few times.

>From http://www.sqlite.org/compile.html: "SQLITE_THREADSAFE=1 sets the
default threading mode to Serialized. SQLITE_THREADSAFE=2 sets the
default threading mode to Multi-threaded". And from
http://www.sqlite.org/threadsafe.html: "In serialized mode, SQLite can
be safely used by multiple threads with no restriction. In
Multi-thread mode, SQLite can be safely used by multiple threads
provided that no single database connection is used simultaneously in
two or more threads". In other words with SQLITE_THREADSAFE=1 SQLite
itself is responsible for protecting concurrent access to connection
with mutex, with SQLITE_THREADSAFE=2 developer using SQLite is
responsible for that. And you was just lucky not getting application
crash without mutex.


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


Re: [sqlite] An interesting (strange) issue with selects

2012-06-21 Thread Dennis Volodomanov

On 22/06/2012 1:29 AM, Pavel Ivanov wrote:
Do you have mutex surrounding statement execution in these threads? 
You should use it or compile with SQLITE_THREADSAFE=1. Pavel 


No, I don't have mutexes for those, as I assumed that THREADSAFE=2 would 
protect them. I'm not clear about the exact difference between 1 and 2, 
having read the docs a few times.


I think this may be the problem - I'll recompile with 1 and spend a bit 
of time testing. I'll get back with the results in either case.


Thank you all for the help!

   Dennis

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


Re: [sqlite] An interesting (strange) issue with selects

2012-06-21 Thread Pavel Ivanov
On Thu, Jun 21, 2012 at 11:02 AM, Dennis Volodomanov  wrote:
> On 22/06/2012 12:57 AM, Pavel Ivanov wrote:
>>
>> OK. And now when you execute the original statements (SELECT COUNT(...)
>> and SELECT ... WHERE ColC = 0) in SQLiteStudio and sqlite3 shell you still
>> get the same results? 1 in the first statement and no rows in the second
>> one? Pavel
>
>
> No, I get correct results in sqlite3 shell now (haven't tried the SS).
>
> What I also failed to mention and which may have something to do with it, is
> that there's another SELECT COUNT() being executed from a different thread,
> which kicks in from time to time. Could that potentially cause any issues?
> So, I have one thread doing SELECT COUNT() and SELECT ... and another thread
> doing just a SELECT COUNT().

Do you have mutex surrounding statement execution in these threads?
You should use it or compile with SQLITE_THREADSAFE=1.


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


Re: [sqlite] An interesting (strange) issue with selects

2012-06-21 Thread Simon Slavin

On 21 Jun 2012, at 3:22pm, Dennis Volodomanov  wrote:

> Strangely enough (albeit expected), sqlite3 shell returns the expected 
> results (e.g. 1, 1 and "integer|0") for the queries. The "integer|64" could 
> be an SQLiteStudio-specific thing, I don't know.
> 
> I'm opening the DB using sqlite3_open_v2() with these flags: 
> SQLITE_OPEN_CREATE|SQLITE_OPEN_READWRITE|SQLITE_OPEN_SHAREDCACHE - could the 
> shared cache play a role in this, along with read uncommitted?
> 
> Given that the result is ok in the shell, I assume it's the way I've compiled 
> the amalgamation (see the flags I've posted earlier) or the way I open it - 
> is there anything peculiar about either of those?

It should be completely impossible to get the results you describe, no matter 
what compilation options or flags you choose for SQLite, how you misuse 
transactions, or what kind of access modes you're using.  It's possible to make 
SQLite give error messages instead of getting the correct results, probably 
related to file locking.  But it's not meant to be possible to make SQLite give 
the wrong results and no error message, no matter how badly you mess things up.

It's possible that you've discovered a bug in SQLite, but the posts I'm seeing 
here, including my own, suspect you're doing something wrong in your use of the 
API: either overwriting memory, or duplicating handles, or something else which 
violates the conventions for using C APIs.

I think we will only be able to get further if you can produce a minimal 
example which demonstrates your problem.  Try making a little program that 
doesn't use threads.  If you can't duplicate the problem like that, try one 
that does use threads.  If you can't make a small program that duplicates your 
problem, it might be something about your big program that's doing it.

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


Re: [sqlite] An interesting (strange) issue with selects

2012-06-21 Thread Dennis Volodomanov

On 22/06/2012 12:57 AM, Pavel Ivanov wrote:
OK. And now when you execute the original statements (SELECT 
COUNT(...) and SELECT ... WHERE ColC = 0) in SQLiteStudio and sqlite3 
shell you still get the same results? 1 in the first statement and no 
rows in the second one? Pavel 


No, I get correct results in sqlite3 shell now (haven't tried the SS).

What I also failed to mention and which may have something to do with 
it, is that there's another SELECT COUNT() being executed from a 
different thread, which kicks in from time to time. Could that 
potentially cause any issues? So, I have one thread doing SELECT COUNT() 
and SELECT ... and another thread doing just a SELECT COUNT().


   Dennis

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


Re: [sqlite] An interesting (strange) issue with selects

2012-06-21 Thread Pavel Ivanov
On Thu, Jun 21, 2012 at 10:37 AM, Dennis Volodomanov  wrote:
> On 22/06/2012 12:30 AM, Pavel Ivanov wrote:
>>
>> Maybe SQLiteStudio, sqlite3 shell and your app don't use the same database
>> file? I don't think there's any sane reason for SQLiteStudio to convert 0 to
>> 64. Pavel On Thu, Jun 21, 2012 at 10:22 AM, Dennis Volodomanov
>>  wrote:
>
>
> No, it's the same DB - I've got the path double-checked and it's all local
> and both SQLiteStudio and sqlite3 shell are copied into the same folder as
> well. Just to clear things up, I did re-try the statement in SS and this
> time it returns the expected "integer|0", so I must've done something wrong
> on the first run, sorry for the confusion.

OK. And now when you execute the original statements (SELECT
COUNT(...) and SELECT ... WHERE ColC = 0) in SQLiteStudio and sqlite3
shell you still get the same results? 1 in the first statement and no
rows in the second one?

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


Re: [sqlite] An interesting (strange) issue with selects

2012-06-21 Thread Dennis Volodomanov

On 22/06/2012 12:30 AM, Pavel Ivanov wrote:
Maybe SQLiteStudio, sqlite3 shell and your app don't use the same 
database file? I don't think there's any sane reason for SQLiteStudio 
to convert 0 to 64. Pavel On Thu, Jun 21, 2012 at 10:22 AM, Dennis 
Volodomanov  wrote:


No, it's the same DB - I've got the path double-checked and it's all 
local and both SQLiteStudio and sqlite3 shell are copied into the same 
folder as well. Just to clear things up, I did re-try the statement in 
SS and this time it returns the expected "integer|0", so I must've done 
something wrong on the first run, sorry for the confusion.


   Dennis

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


Re: [sqlite] An interesting (strange) issue with selects

2012-06-21 Thread Pavel Ivanov
> Strangely enough (albeit expected), sqlite3 shell returns the expected
> results (e.g. 1, 1 and "integer|0") for the queries. The "integer|64" could
> be an SQLiteStudio-specific thing, I don't know.

Maybe SQLiteStudio, sqlite3 shell and your app don't use the same
database file? I don't think there's any sane reason for SQLiteStudio
to convert 0 to 64.


Pavel


On Thu, Jun 21, 2012 at 10:22 AM, Dennis Volodomanov  wrote:
>
> On 22/06/2012 12:00 AM, Pavel Ivanov wrote:
>>
>> You apparently executed above query on different dataset than you
>> initially posted. 64 is not something quote(ColA) can return when column
>> contains integer value 1 (and I guess Richard meant you should execute that
>> query for ColC, not ColA). So please recheck and report the exact steps to
>> reproduce the problem - show us all statements: CREATE TABLE, INSERT,
>> SELECTs. If you are reproducing it on existing data and can't reproduce it
>> on newly created one then show us a series of SELECTs with their results
>> showing the contradiction. Try to copy exactly what SQLiteStudio returns or
>> better yet to use sqlite3 command line utility and copy data it outputs to
>> terminal. Pavel
>
>
> Yes, I've picked up on ColA/ColC  and did execute the query on ColC.
>
> Strangely enough (albeit expected), sqlite3 shell returns the expected
> results (e.g. 1, 1 and "integer|0") for the queries. The "integer|64" could
> be an SQLiteStudio-specific thing, I don't know.
>
> I'm opening the DB using sqlite3_open_v2() with these flags:
> SQLITE_OPEN_CREATE|SQLITE_OPEN_READWRITE|SQLITE_OPEN_SHAREDCACHE - could the
> shared cache play a role in this, along with read uncommitted?
>
> Given that the result is ok in the shell, I assume it's the way I've
> compiled the amalgamation (see the flags I've posted earlier) or the way I
> open it - is there anything peculiar about either of those?
>
> Thanks!
>
>
>   Dennis
>
> ___
> 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] An interesting (strange) issue with selects

2012-06-21 Thread Dennis Volodomanov


On 22/06/2012 12:00 AM, Pavel Ivanov wrote:
You apparently executed above query on different dataset than you 
initially posted. 64 is not something quote(ColA) can return when 
column contains integer value 1 (and I guess Richard meant you should 
execute that query for ColC, not ColA). So please recheck and report 
the exact steps to reproduce the problem - show us all statements: 
CREATE TABLE, INSERT, SELECTs. If you are reproducing it on existing 
data and can't reproduce it on newly created one then show us a series 
of SELECTs with their results showing the contradiction. Try to copy 
exactly what SQLiteStudio returns or better yet to use sqlite3 command 
line utility and copy data it outputs to terminal. Pavel 


Yes, I've picked up on ColA/ColC  and did execute the query on ColC.

Strangely enough (albeit expected), sqlite3 shell returns the expected 
results (e.g. 1, 1 and "integer|0") for the queries. The "integer|64" 
could be an SQLiteStudio-specific thing, I don't know.


I'm opening the DB using sqlite3_open_v2() with these flags: 
SQLITE_OPEN_CREATE|SQLITE_OPEN_READWRITE|SQLITE_OPEN_SHAREDCACHE - could 
the shared cache play a role in this, along with read uncommitted?


Given that the result is ok in the shell, I assume it's the way I've 
compiled the amalgamation (see the flags I've posted earlier) or the way 
I open it - is there anything peculiar about either of those?


Thanks!

   Dennis

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


Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE

2012-06-21 Thread Jay A. Kreibich
On Thu, Jun 21, 2012 at 09:53:57AM +0100, Pontus Bergsten scratched on the wall:

> It seems that it is very important that every statement evaluation is
> concluded with a sqlite3_reset() (or sqlite3_finalize() if the statement
> is not going to be used anymore). 

  That's true, and as others have pointed out, it is fairly well
  documented.

> "You should be in the habit of calling sqlite3_reset() on each query as
> soon as that query finishes. Otherwise the query *might* leave a
> read-lock on the database file and thus prevent subsequent write
> operations for working correctly."
> 
> I suppose that the "*might* locking part" should be explicitly stated
> in the SQLite API documentation for step/reset.

  I believe the documentation is purposely vague about locking.

  The documentation is clear that you should call _reset() or
  _finalize() reasonably soon after you're done with a statement
  execution.

  As for the locking issue, I had a private conversation with R. Hipp
  about this when working on "Using SQLite"... are locks released
  when _step() returns SQLITE_DONE, or are they released when
  _reset()/_finalize() is called?  He said it varied, both statement
  to statement, as well as version to version.  The documentation made
  it clear which APIs a well-behaved program had to use; the rest was
  just internals (as most of locking is).  How things were handled
  changed from time to time to get better performance, reduce
  deadlocks, etc.

  Mind you, this was several years ago, back in the late 2.6 days.  It
  was before WAL.  I have no idea if locking is now more consistent (or
  less).  I'm sure WAL mixed things up quite a bit.
  
  Regardless, I think the general principal still stands... Locking is
  generally considered an internal function of SQLite that the vast
  majority of programmers never directly deals with.  We have to deal
  with some of the ramifications, but not the process itself.  Allowing
  the SQLite devs to alter the locking scheme depending on journal modes,
  platforms, code-refactoring, or whatever, allows the SQLite dev team
  the freedom to change internal details, and things will work just
  fine-- as long as an application follows the well documented API
  sequence.  
  
  In short, you should never be deciding to call or not call _reset()
  or _finalize() because of locking semantics.  If your program can call
  one of those APIs and maintain correct flow, it should be calling them.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] An interesting (strange) issue with selects

2012-06-21 Thread Black, Michael (IS)
At an absolute minimum you need to show us your code for this section.



Then...when we're stumpedyou need a stand-alone version you can send out.  
It will be worth your time to do so as you may catch your own error.



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 Dennis Volodomanov [i...@psunrise.com]
Sent: Thursday, June 21, 2012 9:08 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] An interesting (strange) issue with selects

On 22/06/2012 12:02 AM, Black, Michael (IS) wrote:
>
> Are you multi-threaded?
>
> It sounds like the database is being changed during your run...how is
> that being done?  Inside your program?
>
> Are any deletes being done?
>
>

Yes, the application is multi-threaded, but at this point, there's only
one thread left running (related to this issue) which is reading the
database. Nothing is writing into the DB at this point and the behavior
is still there after restarting the program, so all inserts/deletes were
already done in the previous session for sure.

Dennis
___
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] An interesting (strange) issue with selects

2012-06-21 Thread Dennis Volodomanov

On 22/06/2012 12:02 AM, Black, Michael (IS) wrote:


Are you multi-threaded?

It sounds like the database is being changed during your run...how is 
that being done?  Inside your program?


Are any deletes being done?




Yes, the application is multi-threaded, but at this point, there's only 
one thread left running (related to this issue) which is reading the 
database. Nothing is writing into the DB at this point and the behavior 
is still there after restarting the program, so all inserts/deletes were 
already done in the previous session for sure.


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


Re: [sqlite] An interesting (strange) issue with selects

2012-06-21 Thread Black, Michael (IS)
Are you multi-threaded?



It sounds like the database is being changed during your run...how is that 
being done?  Inside your program?

Are any deletes being done?



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 Dennis Volodomanov [i...@psunrise.com]
Sent: Thursday, June 21, 2012 8:44 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] EXT : An interesting (strange) issue with selects

Michael,

Thanks for the reply. I know, it's usually the user :)

On 21/06/2012 11:31 PM, Black, Michael (IS) wrote:
>
> You don't show where you inserted your data.
>
> Are you postiive ColC is an integer and you didn't insert it as a string?
>
> You don't show a dump of your table which would be handy.
>
> What does "bomb" mean?  Your program gets a seg fault or such?
>
> What are you programming in, on what OS?
>
> I can tell you now if there isn't a simple answer you need to make a
> complete example so somebody else can reproduce the problem and see
> what you're doing wrong (in all likelihood it's probably you).
>
>

I'm using SQLite in C++ code (Windows, MSVC2008, amalgamation). The data
is inserted using sqlite3_bind_int64()/sqlite3_bind_int(). What I mean
by "bomb out" is that it executes this pair of statements tens of
thousands of times (count, then get one row) and works (e.g. "count"
returns more than one and "select" returns one row) and then, once in a
while, count returns more than one, but select doesn't get anything.

Dennis

___
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] An interesting (strange) issue with selects

2012-06-21 Thread Pavel Ivanov
On Thu, Jun 21, 2012 at 9:38 AM, Dennis Volodomanov  wrote:
> On 21/06/2012 11:23 PM, Richard Hipp wrote:
>>
>> Looks like your data is a string:  ' 0'  -  that is a space followed by
>> ascii '0'.  That is different from a numeric 0, so the second query should
>> return zero rows.  What does this show:
>>
>>    SELECT typeof(ColA), quote(ColA) FROM CriticaltemsToProcess;
>>
>
> Pavel, Richard - thank you for replying.
>
> The data I've provided is just how I typed it, so it's actually written into
> the DB using sqlite3_bind_int64(). The SQL above returns "integer, 64".
> Please note, I'm not using the SQLite shell to execute it, so I'm again
> interpreting the results. I can try the shell as well, if that makes sense.
> I'm using SQLiteStudio v2.0.26 for this testing.

You apparently executed above query on different dataset than you
initially posted. 64 is not something quote(ColA) can return when
column contains integer value 1 (and I guess Richard meant you should
execute that query for ColC, not ColA). So please recheck and report
the exact steps to reproduce the problem - show us all statements:
CREATE TABLE, INSERT, SELECTs. If you are reproducing it on existing
data and can't reproduce it on newly created one then show us a series
of SELECTs with their results showing the contradiction. Try to copy
exactly what SQLiteStudio returns or better yet to use sqlite3 command
line utility and copy data it outputs to terminal.


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


Re: [sqlite] EXT : An interesting (strange) issue with selects

2012-06-21 Thread Simon Slavin

On 21 Jun 2012, at 2:55pm, Dennis Volodomanov  wrote:

> On 21/06/2012 11:47 PM, Simon Slavin wrote:
>> Are you examining the result code returned by the SELECT commands ?  I bet 
>> in the cases where the select gets zero rows, it isn't returning SQLITE_OK 
>> but it's instead returning some sort of error code.
> 
> Yes, I do examine those and the return is SQLITE_DONE in this case. I 
> typically check for SQLITE_ROW to get the results and bail out on anything 
> else. The prepare does return a SQLITE_OK.

Hmm.  Okay, well I'm out of ideas.

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


Re: [sqlite] EXT : An interesting (strange) issue with selects

2012-06-21 Thread Dennis Volodomanov

On 21/06/2012 11:47 PM, Simon Slavin wrote:

Are you examining the result code returned by the SELECT commands ?  I bet in 
the cases where the select gets zero rows, it isn't returning SQLITE_OK but 
it's instead returning some sort of error code.

Simon.


Hi Simon,

Yes, I do examine those and the return is SQLITE_DONE in this case. I 
typically check for SQLITE_ROW to get the results and bail out on 
anything else. The prepare does return a SQLITE_OK.


   Dennis

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


Re: [sqlite] EXT : An interesting (strange) issue with selects

2012-06-21 Thread Simon Slavin

On 21 Jun 2012, at 2:44pm, Dennis Volodomanov  wrote:

> I'm using SQLite in C++ code (Windows, MSVC2008, amalgamation). The data is 
> inserted using sqlite3_bind_int64()/sqlite3_bind_int(). What I mean by "bomb 
> out" is that it executes this pair of statements tens of thousands of times 
> (count, then get one row) and works (e.g. "count" returns more than one and 
> "select" returns one row) and then, once in a while, count returns more than 
> one, but select doesn't get anything.

Are you examining the result code returned by the SELECT commands ?  I bet in 
the cases where the select gets zero rows, it isn't returning SQLITE_OK but 
it's instead returning some sort of error code.

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


Re: [sqlite] EXT : An interesting (strange) issue with selects

2012-06-21 Thread Dennis Volodomanov

Michael,

Thanks for the reply. I know, it's usually the user :)

On 21/06/2012 11:31 PM, Black, Michael (IS) wrote:


You don't show where you inserted your data.

Are you postiive ColC is an integer and you didn't insert it as a string?

You don't show a dump of your table which would be handy.

What does "bomb" mean?  Your program gets a seg fault or such?

What are you programming in, on what OS?

I can tell you now if there isn't a simple answer you need to make a 
complete example so somebody else can reproduce the problem and see 
what you're doing wrong (in all likelihood it's probably you).





I'm using SQLite in C++ code (Windows, MSVC2008, amalgamation). The data 
is inserted using sqlite3_bind_int64()/sqlite3_bind_int(). What I mean 
by "bomb out" is that it executes this pair of statements tens of 
thousands of times (count, then get one row) and works (e.g. "count" 
returns more than one and "select" returns one row) and then, once in a 
while, count returns more than one, but select doesn't get anything.


   Dennis

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


Re: [sqlite] sqlite compound keys

2012-06-21 Thread Jay A. Kreibich
On Thu, Jun 21, 2012 at 08:30:31AM -0400, Richard Hipp scratched on the wall:
> On Thu, Jun 21, 2012 at 1:47 AM, nn6eumtr  wrote:
> 
> >
> > If I have a table:
> >
> > create table tmp (
> >  x integer,
> >  y integer
> > )
> >
> > And a an index with compound key:
> >
> > create unique index idx on tmp (x, y);
> >
> > And I did a query:
> >
> > select * from tmp where x = 9
> >
> > Would sqlite know how to use the index to find records where x = 9? Or
> > would it opt for a scan instead?
> >
> 
> It will use the index.

  I'm likely setting myself up for Richard to tell me I'm wrong, but

  -If the table has more columns (i.e. columns that are not in the index).
  -And you ask for them all (SELECT * ...).
  -And you've run ANALYZE.
  -And the index has poor diversity.

  It might be possible that SQLite will choose to do a full scan (?).

  I think.


  In this case, since the index has all the values you're asking for,
  SQLite should always use the index.  This is because SQLite can extract
  all the required values directly from the index itself, and will
  never touch the main table record.  This is more efficient, even if
  the index is known to have poor diversity.

   -j


-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] An interesting (strange) issue with selects

2012-06-21 Thread Tomas Vasko
tu je cela seria
http://www.jamesgunn.com/pg-porn/

stk je  vlavo druhe zospodu

On Thu, Jun 21, 2012 at 11:38:50PM +1000 Dennis Volodomanov wrote:
> On 21/06/2012 11:23 PM, Richard Hipp wrote:
> >Looks like your data is a string:  ' 0'  -  that is a space
> >followed by ascii '0'.  That is different from a numeric 0, so the
> >second query should return zero rows.  What does this show:
> >
> >SELECT typeof(ColA), quote(ColA) FROM CriticaltemsToProcess;
> >
> 
> Pavel, Richard - thank you for replying.
> 
> The data I've provided is just how I typed it, so it's actually
> written into the DB using sqlite3_bind_int64(). The SQL above
> returns "integer, 64". Please note, I'm not using the SQLite shell
> to execute it, so I'm again interpreting the results. I can try the
> shell as well, if that makes sense. I'm using SQLiteStudio v2.0.26
> for this testing.
> 
>Dennis
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 
   The more I see, The less I believe...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] An interesting (strange) issue with selects

2012-06-21 Thread Dennis Volodomanov

On 21/06/2012 11:23 PM, Richard Hipp wrote:
Looks like your data is a string:  ' 0'  -  that is a space followed 
by ascii '0'.  That is different from a numeric 0, so the second query 
should return zero rows.  What does this show:


SELECT typeof(ColA), quote(ColA) FROM CriticaltemsToProcess;



Pavel, Richard - thank you for replying.

The data I've provided is just how I typed it, so it's actually written 
into the DB using sqlite3_bind_int64(). The SQL above returns "integer, 
64". Please note, I'm not using the SQLite shell to execute it, so I'm 
again interpreting the results. I can try the shell as well, if that 
makes sense. I'm using SQLiteStudio v2.0.26 for this testing.


   Dennis

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


Re: [sqlite] EXT : An interesting (strange) issue with selects

2012-06-21 Thread Black, Michael (IS)
You don't show where you inserted your data.

Are you postiive ColC is an integer and you didn't insert it as a string?



You don't show a dump of your table which would be handy.



What does "bomb" mean?  Your program gets a seg fault or such?



What are you programming in, on what OS?



I can tell you now if there isn't a simple answer you need to make a complete 
example so somebody else can reproduce the problem and see what you're doing 
wrong (in all likelihood it's probably you).





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 Dennis Volodomanov [i...@psunrise.com]
Sent: Thursday, June 21, 2012 8:15 AM
To: General Discussion of SQLite Database
Subject: EXT :[sqlite] An interesting (strange) issue with selects

Hello all,

I've been using SQLite for quite a few years, but have just recently
started exploring WAL mode (may or may not be related to WAL) and I'm
experiencing an interesting issue that perhaps is known to others, so
I've decided to ask for your wisdom. This is the amalgamation 3.7.13.

Let's consider the following setup - pragmas used (there's also a bunch
of indexes, but I believe those shouldn't affect the problem):

"PRAGMA journal_mode = WAL;";
"PRAGMA synchronous = NORMAL;";
"PRAGMA page_size = 4096;";
"PRAGMA read_uncommitted = 1;";

Also, some defines when building:

#define SQLITE_OMIT_DEPRECATED
#define SQLITE_DEFAULT_WAL_AUTOCHECKPOINT 5000
#define SQLITE_THREADSAFE 2
#define SQLITE_ENABLE_STAT3

Table:

"CREATE TABLE TableA (ColA INTEGER, ColB INTEGER, ColC INTEGER)";

Now the problem - the first SQL returns SQLITE_ROW and 1 as
sqlite3_column_int(), while the second doesn't find any items:

SELECT COUNT(ColA) FROM TableA;

SELECT ColA FROM CriteriaItemsToProcess WHERE ColC=0 LIMIT 1;

The data in the table is like this (that's the only one row):

1| 12| 0

Just as a side note, the same SQL/functions above work 99.9% of the
time, but bomb out once in a while. Most likely this doesn't help, but
thought I'd mention it.

I appreciate you reading so far and hopefully you can help me out.

Best regards,

Dennis

___
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] An interesting (strange) issue with selects

2012-06-21 Thread Richard Hipp
On Thu, Jun 21, 2012 at 9:15 AM, Dennis Volodomanov wrote:

> Hello all,
>
> I've been using SQLite for quite a few years, but have just recently
> started exploring WAL mode (may or may not be related to WAL) and I'm
> experiencing an interesting issue that perhaps is known to others, so I've
> decided to ask for your wisdom. This is the amalgamation 3.7.13.
>
> Let's consider the following setup - pragmas used (there's also a bunch of
> indexes, but I believe those shouldn't affect the problem):
>
> "PRAGMA journal_mode = WAL;";
> "PRAGMA synchronous = NORMAL;";
> "PRAGMA page_size = 4096;";
> "PRAGMA read_uncommitted = 1;";
>
> Also, some defines when building:
>
> #define SQLITE_OMIT_DEPRECATED
> #define SQLITE_DEFAULT_WAL_**AUTOCHECKPOINT 5000
> #define SQLITE_THREADSAFE 2
> #define SQLITE_ENABLE_STAT3
>
> Table:
>
> "CREATE TABLE TableA (ColA INTEGER, ColB INTEGER, ColC INTEGER)";
>
> Now the problem - the first SQL returns SQLITE_ROW and 1 as
> sqlite3_column_int(), while the second doesn't find any items:
>
> SELECT COUNT(ColA) FROM TableA;
>
> SELECT ColA FROM CriteriaItemsToProcess WHERE ColC=0 LIMIT 1;
>
> The data in the table is like this (that's the only one row):
>
> 1| 12| 0
>

Looks like your data is a string:  ' 0'  -  that is a space followed by
ascii '0'.  That is different from a numeric 0, so the second query should
return zero rows.  What does this show:

SELECT typeof(ColA), quote(ColA) FROM CriticaltemsToProcess;


>
> Just as a side note, the same SQL/functions above work 99.9% of the time,
> but bomb out once in a while. Most likely this doesn't help, but thought
> I'd mention it.
>
> I appreciate you reading so far and hopefully you can help me out.
>
> Best regards,
>
>   Dennis
>
> __**_
> 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] An interesting (strange) issue with selects

2012-06-21 Thread Pavel Ivanov
> Now the problem - the first SQL returns SQLITE_ROW and 1 as
> sqlite3_column_int(), while the second doesn't find any items:
>
> SELECT COUNT(ColA) FROM TableA;
>
> SELECT ColA FROM CriteriaItemsToProcess WHERE ColC=0 LIMIT 1;
>
> The data in the table is like this (that's the only one row):
>
> 1| 12| 0

It looks like you don't have 0 (as integer) in the ColC, but you have
string " 0" instead (note a space at the beginning). And this string "
0" will never be equal to integer 0. Thus your second query doesn't
return rows while first can count them.


Pavel


On Thu, Jun 21, 2012 at 9:15 AM, Dennis Volodomanov  wrote:
> Hello all,
>
> I've been using SQLite for quite a few years, but have just recently started
> exploring WAL mode (may or may not be related to WAL) and I'm experiencing
> an interesting issue that perhaps is known to others, so I've decided to ask
> for your wisdom. This is the amalgamation 3.7.13.
>
> Let's consider the following setup - pragmas used (there's also a bunch of
> indexes, but I believe those shouldn't affect the problem):
>
> "PRAGMA journal_mode = WAL;";
> "PRAGMA synchronous = NORMAL;";
> "PRAGMA page_size = 4096;";
> "PRAGMA read_uncommitted = 1;";
>
> Also, some defines when building:
>
> #define SQLITE_OMIT_DEPRECATED
> #define SQLITE_DEFAULT_WAL_AUTOCHECKPOINT 5000
> #define SQLITE_THREADSAFE 2
> #define SQLITE_ENABLE_STAT3
>
> Table:
>
> "CREATE TABLE TableA (ColA INTEGER, ColB INTEGER, ColC INTEGER)";
>
> Now the problem - the first SQL returns SQLITE_ROW and 1 as
> sqlite3_column_int(), while the second doesn't find any items:
>
> SELECT COUNT(ColA) FROM TableA;
>
> SELECT ColA FROM CriteriaItemsToProcess WHERE ColC=0 LIMIT 1;
>
> The data in the table is like this (that's the only one row):
>
> 1| 12| 0
>
> Just as a side note, the same SQL/functions above work 99.9% of the time,
> but bomb out once in a while. Most likely this doesn't help, but thought I'd
> mention it.
>
> I appreciate you reading so far and hopefully you can help me out.
>
> Best regards,
>
>   Dennis
>
> ___
> 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] An interesting (strange) issue with selects

2012-06-21 Thread Dennis Volodomanov

On 21/06/2012 11:15 PM, Dennis Volodomanov wrote:

SELECT ColA FROM CriteriaItemsToProcess WHERE ColC=0 LIMIT 1;


Of course the table name here should be TableA (shortened for convenience).

   Dennis

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


[sqlite] An interesting (strange) issue with selects

2012-06-21 Thread Dennis Volodomanov

Hello all,

I've been using SQLite for quite a few years, but have just recently 
started exploring WAL mode (may or may not be related to WAL) and I'm 
experiencing an interesting issue that perhaps is known to others, so 
I've decided to ask for your wisdom. This is the amalgamation 3.7.13.


Let's consider the following setup - pragmas used (there's also a bunch 
of indexes, but I believe those shouldn't affect the problem):


"PRAGMA journal_mode = WAL;";
"PRAGMA synchronous = NORMAL;";
"PRAGMA page_size = 4096;";
"PRAGMA read_uncommitted = 1;";

Also, some defines when building:

#define SQLITE_OMIT_DEPRECATED
#define SQLITE_DEFAULT_WAL_AUTOCHECKPOINT 5000
#define SQLITE_THREADSAFE 2
#define SQLITE_ENABLE_STAT3

Table:

"CREATE TABLE TableA (ColA INTEGER, ColB INTEGER, ColC INTEGER)";

Now the problem - the first SQL returns SQLITE_ROW and 1 as 
sqlite3_column_int(), while the second doesn't find any items:


SELECT COUNT(ColA) FROM TableA;

SELECT ColA FROM CriteriaItemsToProcess WHERE ColC=0 LIMIT 1;

The data in the table is like this (that's the only one row):

1| 12| 0

Just as a side note, the same SQL/functions above work 99.9% of the 
time, but bomb out once in a while. Most likely this doesn't help, but 
thought I'd mention it.


I appreciate you reading so far and hopefully you can help me out.

Best regards,

   Dennis

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


Re: [sqlite] sqlite compound keys

2012-06-21 Thread Igor Tandetnik
nn6eumtr  wrote:
> And I did a query:
> 
> select * from tmp where x = 9
> 
> Would sqlite know how to use the index to find records where x = 9? Or
> would it opt for a scan instead?

Why not ask SQLite itself? In sqlite3 console, or your favorite SQLite 
management tool, run this query:

EXPLAIN QUERY PLAN
select * from tmp where x = 9;

-- 
Igor Tandetnik

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


Re: [sqlite] sqlite compound keys

2012-06-21 Thread Black, Michael (IS)
Only when you query X thoughquerying Y by itself gives a table scan.



SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table tmp(x,y);
sqlite> create unique index idx on tmp(x,y);

sqlite> explain query plan select * from tmp where x=9;
0|0|0|SEARCH TABLE tmp USING COVERING INDEX idx (x=?) (~10 rows)

sqlite> explain query plan select * from tmp where x=9 and y=9;
0|0|0|SEARCH TABLE tmp USING COVERING INDEX idx (x=? AND y=?) (~1 rows)

sqlite> explain query plan select * from tmp where y=9;
0|0|0|SCAN TABLE tmp (~10 rows)



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 Richard Hipp [d...@sqlite.org]
Sent: Thursday, June 21, 2012 7:30 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] sqlite compound keys

On Thu, Jun 21, 2012 at 1:47 AM, nn6eumtr  wrote:

>
> If I have a table:
>
> create table tmp (
>  x integer,
>  y integer
> )
>
> And a an index with compound key:
>
> create unique index idx on tmp (x, y);
>
> And I did a query:
>
> select * from tmp where x = 9
>
> Would sqlite know how to use the index to find records where x = 9? Or
> would it opt for a scan instead?
>

It will use the index.


>
>
> __**_
> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] sqlite compound keys

2012-06-21 Thread Richard Hipp
On Thu, Jun 21, 2012 at 1:47 AM, nn6eumtr  wrote:

>
> If I have a table:
>
> create table tmp (
>  x integer,
>  y integer
> )
>
> And a an index with compound key:
>
> create unique index idx on tmp (x, y);
>
> And I did a query:
>
> select * from tmp where x = 9
>
> Would sqlite know how to use the index to find records where x = 9? Or
> would it opt for a scan instead?
>

It will use the index.


>
>
> __**_
> 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] Problem with concurrent transaction and DETACH DATABASE

2012-06-21 Thread Pontus Bergsten
Aaah...

I only read the documentation in sqlite3.h.

Thanks for the tip.


Pontus




 Från: Simon Slavin 
Till: Pontus Bergsten ; General Discussion of SQLite 
Database  
Skickat: torsdag, 21 juni 2012 13:46
Ämne: Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE
 

On 21 Jun 2012, at 9:53am, Pontus Bergsten  wrote:

> were it was stated that
> 
> "You should be in the habit of calling sqlite3_reset() on each query as soon 
> as that query finishes. Otherwise the query *might* leave a read-lock on the 
> database file and thus prevent subsequent write operations for working 
> correctly."
> 
> I suppose that the "*might* locking part" should be explicitly stated in the 
> SQLite API documentation for step/reset.

The documentation for the use of statements has it:



It doesn't tell you what will go wrong if you don't use _finalize() but it does 
tell you when to use _reset() and that you must use _finalize().

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


[sqlite] sqlite compound keys

2012-06-21 Thread nn6eumtr


If I have a table:

create table tmp (
  x integer,
  y integer
)

And a an index with compound key:

create unique index idx on tmp (x, y);

And I did a query:

select * from tmp where x = 9

Would sqlite know how to use the index to find records where x = 9? Or 
would it opt for a scan instead?



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


Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE

2012-06-21 Thread Simon Slavin

On 21 Jun 2012, at 9:53am, Pontus Bergsten  wrote:

> were it was stated that
> 
> "You should be in the habit of calling sqlite3_reset() on each query as soon 
> as that query finishes. Otherwise the query *might* leave a read-lock on the 
> database file and thus prevent subsequent write operations for working 
> correctly."
> 
> I suppose that the "*might* locking part" should be explicitly stated in the 
> SQLite API documentation for step/reset.

The documentation for the use of statements has it:



It doesn't tell you what will go wrong if you don't use _finalize() but it does 
tell you when to use _reset() and that you must use _finalize().

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


Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE

2012-06-21 Thread Pontus Bergsten
Found it!!!

The problem were some SQL statements in Thread1 that called sqlite3_reset() 
before evaluation using sqlite3_step().

It worked alright in the single threaded unit test, but not in the 
multi-threaded application.

It seems that it is very important that every statement evaluation is concluded 
with a sqlite3_reset() (or sqlite3_finalize() if the statement is not going to 
be used anymore). I found an old discussion

http://sqlite.1065341.n5.nabble.com/Sqlite3-reset-needed-td7927.html

were it was stated that

"You should be in the habit of calling sqlite3_reset() on each query as soon as 
that query finishes. Otherwise the query *might* leave a read-lock on the 
database file and thus prevent subsequent write operations for working 
correctly."

I suppose that the "*might* locking part" should be explicitly stated in the 
SQLite API documentation for step/reset.


However, now it works like a charm :)


Pavel, thanks for your input.

Pontus




 Från: Pavel Ivanov 
Till: Pontus Bergsten ; General Discussion of SQLite 
Database  
Skickat: onsdag, 20 juni 2012 17:45
Ämne: Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE
 
It looks to me that you have corrupted memory or already closed
database at this point. AFAIK, ROLLBACK cannot return SQLITE_ERROR in
any "normal" situation (I exclude such situations as faulty disk or
some similar I/O error), normally it can return only SQLITE_BUSY.
Check your application in valgrind.

Pavel

On Wed, Jun 20, 2012 at 11:27 AM, Pontus Bergsten
 wrote:
> Let's see, the code in Thread 2 then look something like this:
>
>
> {
>    ...
>
>    copy data
>
>    request mutex
>
>    sqlite3_get_autocommit     returns 1 ( Autocommit mode enabled )
>
>    exec("ROLLBACK")   returns 1 ( SQLITE_ERROR )
>
>    exec("DETACH DATABASE Dest")   returns 1( SQLITE_ERROR )
>    release mutex
>    ...
>
> }
>
> as before copying data from "main" to "Dest" before entering the synchronized 
> section is successful, but detach fails.
>
>
> Pontus
>
>
>
> 
>  Från: Pavel Ivanov 
> Till: Pontus Bergsten ; General Discussion of 
> SQLite Database 
> Skickat: onsdag, 20 juni 2012 16:24
> Ämne: Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE
>
> Then something else should happen in your application besides the
> given pseudo code. Could you try to check what
> sqlite3_get_autocommit() returns and execute rollback under the mutex
> but before doing detach?
>
> Pavel
>
>
> On Wed, Jun 20, 2012 at 10:11 AM, Pontus Bergsten
>  wrote:
>>
>>
>>> I guess you meant Thread 1 in the last sentence. And how do you know
>>> that? Do you check the return code?
>>
>> You are absolutely right, I meant Thread 1. We are checking the return value 
>> of the commit statement which indicate success ( 0 ).
>>
>> Pontus
>>
>>
>>>Pavel
>>>
>>>
>>>On Wed, Jun 20, 2012 at 9:27 AM, Pontus Bergsten
>>  wrote:
>>> Thanks for the response,
>>>
>>>
>>> In fact, Thread 2 uses a series of INSERT INTO...SELECT queries to transfer 
>>> data in chunks in a loop, from "main" to "Dest". The queries are prepared 
>>> once in the beginning of the transfer loop. In each iteration the 
>>> statements are bounded with new time interval parameters and reset, before 
>>> the query is executed.
>>>
>>>
>>> Thread 1 is using transaction statements "BEGIN" and "COMMIT" to start and 
>>> commit transactions. As we know the commit statement in Thread 2 does not 
>>> fail.
>>>
>>> Any ideas?
>>>
>>> Pontus
>>>
>>>
>>>
>>> 
>>>  Från: Pavel Ivanov 
>>> Till: Pontus Bergsten ; General Discussion of 
>>> SQLite Database 
>>> Skickat: onsdag, 20 juni 2012 14:27
>>> Ämne: Re: [sqlite] Problem with concurrent transaction and DETACH DATABASE
>>>
>>> Probably the following scenario is possible in your situation:
>>> - thread 1 locks transaction mutex
>>> - thread 1 inserts buffered data
>>> - thread 2 starts transferring data to Dest database
>>> - thread 1 tries to commit, commit fails (do you check return code
>>> from it?), transaction is left open
>>> - thread 1 unlocks mutex
>>> - thread 2 locks mutex
>>> - thread 2 tries to detach and fails
>>>
>>> So do you check return code from commit? And do you really use only
>>> INSERT INTO ... SELECT in thread 2 and no other SELECT queries?
>>>
>>>
>>> Pavel
>>>
>>>
>>> On Wed, Jun 20, 2012 at 7:42 AM, Pontus Bergsten
>>>  wrote:
 In our application we have two threads implementing a signal logger 
 functionality, see pseudo code below.
 Thread 1: Reads signal data, and log to a global in-memory SQLite database 
 after a