[sqlite] Does sqlite3_exec work with sqlite3_bind ?

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

Re: [sqlite] Testing the ODBC link

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.

Re: [sqlite] Difference between 3.7.11 and 3.7.13

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

Re: [sqlite] Testing the ODBC link

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

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

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

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

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

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

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

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

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 ___

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

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

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)

[sqlite] Counting entries returned from a compound SELECT.

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

Re: [sqlite] Difference between 3.7.11 and 3.7.13

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

Re: [sqlite] Difference between 3.7.11 and 3.7.13

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

[sqlite] Windows sqlite3.dll without Debug info

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

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

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

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

>  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

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

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

Re: [sqlite] Difference between 3.7.11 and 3.7.13

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

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

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

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

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

[sqlite] Difference between 3.7.11 and 3.7.13

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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,

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

> 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

Re: [sqlite] sqlite compound keys

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

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

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

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

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

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

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

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

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

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

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

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

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

Re: [sqlite] sqlite compound keys

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

Re: [sqlite] sqlite compound keys

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

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

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

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

[sqlite] sqlite compound keys

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

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

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

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