Re: [sqlite] fetching rows

2012-06-20 Thread Pavel Ivanov
On Wed, Jun 20, 2012 at 11:33 PM, Durga D  wrote:
> Hi All,
>
>    I have to develop a sqlite application. Within the process, multiple
> threads are trying to access in write/read mode. Will sqlite supports read
> and write at a time?
>
>    scenario:  1. x number of records (x related data) are going to insert
> in a transaction in ThreadA. still, not committed.
>                   2. In ThreadB (parallel thread), trying to read records
> (x related data), which are there in transaction.
>
>    By default, sqlite supports this scenario? or Do I need to enable any
> flags/macros?

Yes, SQLite supports that. You can see some "problems" only if ThreadA
inserts a lot in one transaction, so that it doesn't fit into memory
cache.


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


[sqlite] fetching rows

2012-06-20 Thread Durga D
Hi All,

I have to develop a sqlite application. Within the process, multiple
threads are trying to access in write/read mode. Will sqlite supports read
and write at a time?

scenario:  1. x number of records (x related data) are going to insert
in a transaction in ThreadA. still, not committed.
   2. In ThreadB (parallel thread), trying to read records
(x related data), which are there in transaction.

By default, sqlite supports this scenario? or Do I need to enable any
flags/macros?

Thanks in advance.

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


Re: [sqlite] Bug report - compilation fails in VS2010 with SQLITE_ZERO_MALLOC defined (patch included)

2012-06-20 Thread Duncan Smith
Oh dear, that got mangled quite horribly.  I've attached the
patch instead.

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


[sqlite] Bug report - compilation fails in VS2010 with SQLITE_ZERO_MALLOC defined (patch included)

2012-06-20 Thread Duncan Smith
Hi,

I'm using the Amalgamation file currently available from
 (named
sqlite-amalgamation-3071300.zip).
When I compile it in Visual Studio 2010 with SQLITE_ZERO_MALLOC defined,
I get
a double-definition of the default memory allocator functions:

1>sqlite3.c(15385): error C2084: function 'void *sqlite3MemMalloc(int)'
already has a body
1>  sqlite3.c(15245) : see previous definition of
'sqlite3MemMalloc'
1>sqlite3.c(15417): error C2084: function 'void sqlite3MemFree(void *)'
already has a body
1>  sqlite3.c(15246) : see previous definition of
'sqlite3MemFree'
1>sqlite3.c(15432): error C2084: function 'int sqlite3MemSize(void *)'
already has a body
1>  sqlite3.c(15248) : see previous definition of
'sqlite3MemSize'
1>sqlite3.c(15454): error C2084: function 'void *sqlite3MemRealloc(void
*,int)' already has a body
1>  sqlite3.c(15247) : see previous definition of
'sqlite3MemRealloc'
1>sqlite3.c(15486): error C2084: function 'int sqlite3MemRoundup(int)'
already has a body
1>  sqlite3.c(15249) : see previous definition of
'sqlite3MemRoundup'
1>sqlite3.c(15493): error C2084: function 'int sqlite3MemInit(void *)'
already has a body
1>  sqlite3.c(15250) : see previous definition of
'sqlite3MemInit'
1>sqlite3.c(15529): error C2084: function 'void sqlite3MemShutdown(void
*)' already has a body
1>  sqlite3.c(15251) : see previous definition of
'sqlite3MemShutdown'
1>sqlite3.c(15540): error C2084: function 'void
sqlite3MemSetDefault(void)' already has a body
1>  sqlite3.c(11581) : see previous definition of
'sqlite3MemSetDefault'

The following patch will remedy this:

--- a/src/sqlite3/sqlite3.c
+++ b/src/sqlite3/sqlite3.c
@@ -402,11 +402,11 @@
 ** If none of the above are defined, then set SQLITE_SYSTEM_MALLOC as
 ** the default.
 */
-#if
defined(SQLITE_SYSTEM_MALLOC)+defined(SQLITE_WIN32_MALLOC)+defined(SQLITE_MEMDEBUG)>1
+#if
defined(SQLITE_SYSTEM_MALLOC)+defined(SQLITE_WIN32_MALLOC)+defined(SQLITE_MEMDEBUG)+defined(SQLITE_ZERO_MALLOC)>1
 # error "At most one of the following compile-time configuration
 options\
- is allows: SQLITE_SYSTEM_MALLOC, SQLITE_WIN32_MALLOC, SQLITE_MEMDEBUG"
+ is allowed: SQLITE_SYSTEM_MALLOC, SQLITE_WIN32_MALLOC,
SQLITE_MEMDEBUG, SQLITE_ZERO_MALLOC"
 #endif
-#if
defined(SQLITE_SYSTEM_MALLOC)+defined(SQLITE_WIN32_MALLOC)+defined(SQLITE_MEMDEBUG)==0
+#if
defined(SQLITE_SYSTEM_MALLOC)+defined(SQLITE_WIN32_MALLOC)+defined(SQLITE_MEMDEBUG)+defined(SQLITE_ZERO_MALLOC)==0
 # define SQLITE_SYSTEM_MALLOC 1
 #endif

Thanks!

-- 
Duncan Smith
___
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-20 Thread Maury Markowitz
I swear I did nothing... and it's working perfectly. Ahhh...

However, the ODBC driver does not properly return the "current catalog" from 
its connection info, which I use to drive the schema download/unfold. Nothing 
major, it's a minor annoyance only, but I'll keep poking at it and maybe I can 
get that working too.
___
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-20 Thread Simon Slavin

On 20 Jun 2012, at 5:12pm, Maury Markowitz  wrote:

> On 2012-06-20, at 12:01 PM, Pavel Ivanov wrote:
>>> Well that looks like your library is loading, the connection with ODBC is 
>>> working, but it isn't accessing your database file.  I agree with your 
>>> other post.
>>> 
>>> It may not be finding your database in the folder where you think it's 
>>> looking.  When you tell it which database to open try specifying the full 
>>> path, from 'C:\' onwards, just for testing purposes.
> 
> Ok, is there a way to do *this* via a PRAGMA or such, through the ODBC 
> connection? IE, is there an analog of "USE"?

I'm not familiar with ODBC, I'm afraid.  I don't know how you're meant to 
specify which database you're working with or whether it expects them all to be 
in a specific place.  I do note that



does not mention SQLite support.  I hope someone else can help.

Simon.
___
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-20 Thread Maury Markowitz
On 2012-06-20, at 12:01 PM, Pavel Ivanov wrote:
>> Well that looks like your library is loading, the connection with ODBC is 
>> working, but it isn't accessing your database file.  I agree with your other 
>> post.
>> 
>> It may not be finding your database in the folder where you think it's 
>> looking.  When you tell it which database to open try specifying the full 
>> path, from 'C:\' onwards, just for testing purposes.

Ok, is there a way to do *this* via a PRAGMA or such, through the ODBC 
connection? IE, is there an analog of "USE"?

___
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-20 Thread Maury Markowitz
On 2012-06-20, at 12:01 PM, Pavel Ivanov wrote:
> Meaning "from '/' onwards" as OP works on OS X. ;-)

Indeed! :-)
___
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-20 Thread Simon Slavin

On 20 Jun 2012, at 5:01pm, Pavel Ivanov  wrote:

> On Wed, Jun 20, 2012 at 11:57 AM, Simon Slavin  wrote:
>> 
>> It may not be finding your database in the folder where you think it's 
>> looking.  When you tell it which database to open try specifying the full 
>> path, from 'C:\' onwards, just for testing purposes.
> 
> Meaning "from '/' onwards" as OP works on OS X. ;-)

Heh.  Especially ironic since I work on OS X and not Windows.  I'm just used to 
questions from Windows people.

Simon.
___
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-20 Thread Pavel Ivanov
On Wed, Jun 20, 2012 at 11:57 AM, Simon Slavin  wrote:
>
> On 20 Jun 2012, at 4:53pm, Maury Markowitz  wrote:
>
>> On 2012-06-20, at 11:50 AM, Simon Slavin wrote:
>>> SELECT sqlite_version()
>>> PRAGMA database_list
>>
>> Ahhh, thanks Simon, this is precisely the sort of thing I was looking for. 
>> And the results are...
>>
>>> SELECT sqlite_version()
>>
>> 3.7.7
>>
>>> PRAGMA database_list
>>
>> 
>
> Well that looks like your library is loading, the connection with ODBC is 
> working, but it isn't accessing your database file.  I agree with your other 
> post.
>
> It may not be finding your database in the folder where you think it's 
> looking.  When you tell it which database to open try specifying the full 
> path, from 'C:\' onwards, just for testing purposes.


Meaning "from '/' onwards" as OP works on OS X. ;-)


Pavel
___
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-20 Thread Maury Markowitz
On 2012-06-20, at 11:50 AM, Simon Slavin wrote:
> SELECT sqlite_version()
> PRAGMA database_list

Ahhh, thanks Simon, this is precisely the sort of thing I was looking for. And 
the results are...

> SELECT sqlite_version()

3.7.7

> PRAGMA database_list




Onward!
___
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-20 Thread Maury Markowitz
My apologies, I failed to mention this potentially important point:

select * from sqlite_master

returns columns in the result set, but no rows of data.

So I *am* connected, but it just doesn't seem to see any data.
___
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-20 Thread Simon Slavin

On 20 Jun 2012, at 4:43pm, Maury Markowitz  wrote:

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

Your interface may do its own checking for a DB and refuse to process a SQLite 
command without it.  But you should be able to do either or both of the 
following commands

SELECT sqlite_version()
PRAGMA database_list

which will both return tables, like a SELECT command would.

By the way, if you want to check out that you really do have a working database 
file, there's a SQLite utility called the 'sqlite command line shell' which is 
fully supported by the SQLite team.  Download a version from here



and the documentation is here



.  If that shell tool can open your database and you can do a '.tables' on it 
and see the right tables, then the database file is definitely there, and in 
the folder you think it's in.  If the shell tool can't see the tables in your 
database then there's no point in trying anything else until you've figured out 
what's wrong.

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-20 Thread Pavel Ivanov
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 pre-determined number of reads.

  1   while( true )
  2  {
  3 // Buffer data, blocks until new data is available
  4 buffer.append( readData() )
  5 ++numberOfReads;
  6

  7 if ( numberOfReads == maxReads )
  8 {
  9    globalTransactionMutex.request()
 10
 11    begin transaction
 12    insert bufferered data to "main" in GlobalDbConnection

 13    commit transaction
 14

 15    globalTransactionMutex.release()
 16    numberOfReads = 0;
 17 }

 18   }


 Thread 2: Upon user action, transfer data within a specified time window 
 from the in-memory database to a database file

 19  while( true )

 20  {
 21 waitForUserAction()
 22

 23 DestDbConnection = Create destination database file Dest.db

 24 close DestDbConnection
 25

 26     attach Dest.db with GlobalDbConnection as "Dest"

 27     transfer log data from "main" to "Dest" in GlobalDbConnection using 
 INSERT INTO Dest SELECT FROM main

 28

 29     globalTransactionMutex.request()
 30     detach "Dest" from GlobalDbConnection

 31     

[sqlite] Testing the ODBC link

2012-06-20 Thread Maury Markowitz
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?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] How to select from table

2012-06-20 Thread Black, Michael (IS)
setup() must be some other function in the book.  Get rid of it.  Probably runs 
some pragmas.



You can force the name error to go away by casting to char *.



Or you can strdup the sqlite3_column_text value and free it when you're done.



The error is warning you that you need to be aware that this value is not 
permanent.



You'll also need to rename the function to int main() -- otherwise it won't 
link.







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 deltagam...@gmx.net [deltagam...@gmx.net]
Sent: Wednesday, June 20, 2012 10:04 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] How to select from table

Am 20.06.2012 14:55, schrieb Igor Tandetnik:
> deltagam...@gmx.net wrote:
>> how can i select from sqlite3 db ?
> By executing a SELECT statement, of course. See this example:
>
> http://books.google.com/books?id=VsZ5bUh0XAkC=PA222
>
>> How do I retrieve the number of records in a table ?
> By running this statement: select count(*) from MyTable;

thx for the hint, it is an interesting book, but in the example on page 222
I get 2 errors

  select_all_from_db.cpp(23): error C3861: 'setup': identifier not found
  select_all_from_db.cpp(41): error C2440: '=' : cannot convert from
'const unsigned char *' to 'char *'
line 41 is :   name = sqlite3_column_text(stmt, 2 );

I checked on the documentation side http://sqlite.org/capi3ref.html ,
there is nothing about setup ...
And what is the problem with name ?


==
#include 
#include 
#include "sqlite3.h"

#include 
#include 
#include 
#include 

using namespace std;


void select_all_rows_db() {

 int rc, i, ncols, id, cid;
 char *name, *sql;
 sqlite3 *db;
 sqlite3_stmt *stmt;

 sql = "Select ID, EVENTTYPE FROM eventlog";
 sqlite3_open("ah.db", );

 setup(db);

 sqlite3_prepare(db, sql, strlen(sql), , NULL);
 rc = sqlite3_step(stmt);

/*Print column information */
for (i=0; i< ncols; i++ ) {
 fprintf(stdout, "Column: name=%s, storage class=%i, declared=%s\n",
 sqlite3_column_name(stmt, i ),
 sqlite3_column_type(stmt, i ),
 sqlite3_column_decltype(stmt, i ));
} // for

fprintf(stdout, "\n");

while(rc == SQLITE_ROW) {
 id = sqlite3_column_int(stmt, 0 );
 cid = sqlite3_column_int(stmt, 1 );
 name = sqlite3_column_text(stmt, 2 );

 if (name != NULL ) {
 fprintf(stderr, "Row: id=%i, cid=%i, name='%s'\n", id, cid, name);
 } else {
 /* Field is NULL */
 fprintf(stderr, "Row: id=%i, cid=%i, name=NULL\n", id, cid);
 }
 rc = sqlite3_step(stmt);
} // while

sqlite3_finalize(stmt);
sqlite3_close(db);


} // select_all_rows_db


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


[sqlite] icu_load_collation: How do I load a case-insensitive collation?

2012-06-20 Thread Grace Batumbya
Hi there,
How do I load a case-insensitive collation using the icu_load_collation method 
that is provided by the ICU extension.

Regards,

Grace Batumbya
Research Assistant | Seneca CDOT
Phone: 416-491-5050 x3548
cdot.senecac.on.ca



___
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-20 Thread Pontus Bergsten
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 pre-determined number of reads.
>>>
>>>  1   while( true )
>>>  2  {
>>>  3 // Buffer data, blocks until new data is available
>>>  4 buffer.append( readData() )
>>>  5 ++numberOfReads;
>>>  6
>>>
>>>  7 if ( numberOfReads == maxReads )
>>>  8 {
>>>  9    globalTransactionMutex.request()
>>> 10
>>> 11    begin transaction
>>> 12    insert bufferered data to "main" in GlobalDbConnection
>>>
>>> 13    commit transaction
>>> 14
>>>
>>> 15    globalTransactionMutex.release()
>>> 16    numberOfReads = 0;
>>> 17 }
>>>
>>> 18   }
>>>
>>>
>>> Thread 2: Upon user action, transfer data within a specified time window 
>>> from the in-memory database to a database file
>>>
>>> 19  while( true )
>>>
>>> 20  {
>>> 21 waitForUserAction()
>>> 22
>>>
>>> 23 DestDbConnection = Create destination database file Dest.db
>>>
>>> 24 close DestDbConnection
>>> 25
>>>
>>> 26     attach Dest.db with GlobalDbConnection as "Dest"
>>>
>>> 27     transfer log data from "main" to "Dest" in GlobalDbConnection using 
>>> INSERT INTO Dest SELECT FROM main
>>>
>>> 28
>>>
>>> 29     globalTransactionMutex.request()
>>> 30     detach "Dest" from GlobalDbConnection
>>>
>>> 31     globalTransactionMutex.release()
>>> 32  }
>>>
>>> Attaching the destination database Dest.db and transferring data (23)-(27) 
>>> work without any hassle.
>>>
>>> The problem we're facing is that the detach statement (30) fails with error 
>>> message "SQL Logic error or missing database".
>>>
>>> The transaction is protected with a mutex, so it should not be possible to 
>>> execute the detach statement (30) during an ongoing transaction. Moreover, 
>>> both threads have the same priority, so we don't have any dead-lock issues.
>>>
>>> The code in Thread 2 (19) -(32) seems to 

Re: [sqlite] How to select from table

2012-06-20 Thread deltagam...@gmx.net

Am 20.06.2012 14:55, schrieb Igor Tandetnik:

deltagam...@gmx.net wrote:

how can i select from sqlite3 db ?

By executing a SELECT statement, of course. See this example:

http://books.google.com/books?id=VsZ5bUh0XAkC=PA222


How do I retrieve the number of records in a table ?

By running this statement: select count(*) from MyTable;


thx for the hint, it is an interesting book, but in the example on page 222
I get 2 errors

  select_all_from_db.cpp(23): error C3861: 'setup': identifier not found
  select_all_from_db.cpp(41): error C2440: '=' : cannot convert from 
'const unsigned char *' to 'char *'

line 41 is :   name = sqlite3_column_text(stmt, 2 );

I checked on the documentation side http://sqlite.org/capi3ref.html , 
there is nothing about setup ...

And what is the problem with name ?


==
#include 
#include 
#include "sqlite3.h"

#include 
#include 
#include 
#include 

using namespace std;


void select_all_rows_db() {

int rc, i, ncols, id, cid;
char *name, *sql;
sqlite3 *db;
sqlite3_stmt *stmt;

sql = "Select ID, EVENTTYPE FROM eventlog";
sqlite3_open("ah.db", );

setup(db);

sqlite3_prepare(db, sql, strlen(sql), , NULL);
rc = sqlite3_step(stmt);

/*Print column information */
for (i=0; i< ncols; i++ ) {
fprintf(stdout, "Column: name=%s, storage class=%i, declared=%s\n",
sqlite3_column_name(stmt, i ),
sqlite3_column_type(stmt, i ),
sqlite3_column_decltype(stmt, i ));
} // for

fprintf(stdout, "\n");

while(rc == SQLITE_ROW) {
id = sqlite3_column_int(stmt, 0 );
cid = sqlite3_column_int(stmt, 1 );
name = sqlite3_column_text(stmt, 2 );

if (name != NULL ) {
fprintf(stderr, "Row: id=%i, cid=%i, name='%s'\n", id, cid, name);
} else {
/* Field is NULL */
fprintf(stderr, "Row: id=%i, cid=%i, name=NULL\n", id, cid);
}
rc = sqlite3_step(stmt);
} // while

sqlite3_finalize(stmt);
sqlite3_close(db);


} // select_all_rows_db


___
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-20 Thread Pavel Ivanov
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 pre-determined number of reads.
>>>
>>>  1   while( true )
>>>  2  {
>>>  3 // Buffer data, blocks until new data is available
>>>  4 buffer.append( readData() )
>>>  5 ++numberOfReads;
>>>  6
>>>
>>>  7 if ( numberOfReads == maxReads )
>>>  8 {
>>>  9    globalTransactionMutex.request()
>>> 10
>>> 11    begin transaction
>>> 12    insert bufferered data to "main" in GlobalDbConnection
>>>
>>> 13    commit transaction
>>> 14
>>>
>>> 15    globalTransactionMutex.release()
>>> 16    numberOfReads = 0;
>>> 17 }
>>>
>>> 18   }
>>>
>>>
>>> Thread 2: Upon user action, transfer data within a specified time window 
>>> from the in-memory database to a database file
>>>
>>> 19  while( true )
>>>
>>> 20  {
>>> 21 waitForUserAction()
>>> 22
>>>
>>> 23 DestDbConnection = Create destination database file Dest.db
>>>
>>> 24 close DestDbConnection
>>> 25
>>>
>>> 26     attach Dest.db with GlobalDbConnection as "Dest"
>>>
>>> 27     transfer log data from "main" to "Dest" in GlobalDbConnection using 
>>> INSERT INTO Dest SELECT FROM main
>>>
>>> 28
>>>
>>> 29     globalTransactionMutex.request()
>>> 30     detach "Dest" from GlobalDbConnection
>>>
>>> 31     globalTransactionMutex.release()
>>> 32  }
>>>
>>> Attaching the destination database Dest.db and transferring data (23)-(27) 
>>> work without any hassle.
>>>
>>> The problem we're facing is that the detach statement (30) fails with error 
>>> message "SQL Logic error or missing database".
>>>
>>> The transaction is protected with a mutex, so it should not be possible to 
>>> execute the detach statement (30) during an ongoing transaction. Moreover, 
>>> both threads have the same priority, so we don't have any dead-lock issues.
>>>
>>> The code in Thread 2 (19) -(32) seems to work fine in a single threaded 
>>> unit test. We're using SQLite v. 3.6.21 on Windows XP.
>>>
>>>
>>> Does anyone have any idea about the source of this problem? Have we missed 
>>> something regarding SQLite and multi-threading?
>>>
>>>
>>> Regards,
>>>
>>> Pontus Bergsten
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> 

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

2012-06-20 Thread Pontus Bergsten


> 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 pre-determined number of reads.
>>
>>  1   while( true )
>>  2  {
>>  3 // Buffer data, blocks until new data is available
>>  4 buffer.append( readData() )
>>  5 ++numberOfReads;
>>  6
>>
>>  7 if ( numberOfReads == maxReads )
>>  8 {
>>  9    globalTransactionMutex.request()
>> 10
>> 11    begin transaction
>> 12    insert bufferered data to "main" in GlobalDbConnection
>>
>> 13    commit transaction
>> 14
>>
>> 15    globalTransactionMutex.release()
>> 16    numberOfReads = 0;
>> 17 }
>>
>> 18   }
>>
>>
>> Thread 2: Upon user action, transfer data within a specified time window 
>> from the in-memory database to a database file
>>
>> 19  while( true )
>>
>> 20  {
>> 21 waitForUserAction()
>> 22
>>
>> 23 DestDbConnection = Create destination database file Dest.db
>>
>> 24 close DestDbConnection
>> 25
>>
>> 26     attach Dest.db with GlobalDbConnection as "Dest"
>>
>> 27     transfer log data from "main" to "Dest" in GlobalDbConnection using 
>> INSERT INTO Dest SELECT FROM main
>>
>> 28
>>
>> 29     globalTransactionMutex.request()
>> 30     detach "Dest" from GlobalDbConnection
>>
>> 31     globalTransactionMutex.release()
>> 32  }
>>
>> Attaching the destination database Dest.db and transferring data (23)-(27) 
>> work without any hassle.
>>
>> The problem we're facing is that the detach statement (30) fails with error 
>> message "SQL Logic error or missing database".
>>
>> The transaction is protected with a mutex, so it should not be possible to 
>> execute the detach statement (30) during an ongoing transaction. Moreover, 
>> both threads have the same priority, so we don't have any dead-lock issues.
>>
>> The code in Thread 2 (19) -(32) seems to work fine in a single threaded unit 
>> test. We're using SQLite v. 3.6.21 on Windows XP.
>>
>>
>> Does anyone have any idea about the source of this problem? Have we missed 
>> something regarding SQLite and multi-threading?
>>
>>
>> Regards,
>>
>> Pontus Bergsten
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-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-20 Thread Pavel Ivanov
> 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.

I guess you meant Thread 1 in the last sentence. And how do you know
that? Do you check the return code?


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 pre-determined number of reads.
>>
>>  1   while( true )
>>  2  {
>>  3 // Buffer data, blocks until new data is available
>>  4 buffer.append( readData() )
>>  5 ++numberOfReads;
>>  6
>>
>>  7 if ( numberOfReads == maxReads )
>>  8 {
>>  9    globalTransactionMutex.request()
>> 10
>> 11    begin transaction
>> 12    insert bufferered data to "main" in GlobalDbConnection
>>
>> 13    commit transaction
>> 14
>>
>> 15    globalTransactionMutex.release()
>> 16    numberOfReads = 0;
>> 17 }
>>
>> 18   }
>>
>>
>> Thread 2: Upon user action, transfer data within a specified time window 
>> from the in-memory database to a database file
>>
>> 19  while( true )
>>
>> 20  {
>> 21 waitForUserAction()
>> 22
>>
>> 23 DestDbConnection = Create destination database file Dest.db
>>
>> 24 close DestDbConnection
>> 25
>>
>> 26     attach Dest.db with GlobalDbConnection as "Dest"
>>
>> 27     transfer log data from "main" to "Dest" in GlobalDbConnection using 
>> INSERT INTO Dest SELECT FROM main
>>
>> 28
>>
>> 29     globalTransactionMutex.request()
>> 30     detach "Dest" from GlobalDbConnection
>>
>> 31     globalTransactionMutex.release()
>> 32  }
>>
>> Attaching the destination database Dest.db and transferring data (23)-(27) 
>> work without any hassle.
>>
>> The problem we're facing is that the detach statement (30) fails with error 
>> message "SQL Logic error or missing database".
>>
>> The transaction is protected with a mutex, so it should not be possible to 
>> execute the detach statement (30) during an ongoing transaction. Moreover, 
>> both threads have the same priority, so we don't have any dead-lock issues.
>>
>> The code in Thread 2 (19) -(32) seems to work fine in a single threaded unit 
>> test. We're using SQLite v. 3.6.21 on Windows XP.
>>
>>
>> Does anyone have any idea about the source of this problem? Have we missed 
>> something regarding SQLite and multi-threading?
>>
>>
>> Regards,
>>
>> Pontus Bergsten
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Odd insertion error FTS4 + ICU (E. Timothy Uy)

2012-06-20 Thread Klaas Van Be
>> >> inserting the following into my virtual table:
>> >>
>> >> 一日耶羅波安出
>>
>> Can you post the list of codepoints in this text? Or the hex
>> of the utf-16 or utf-8 encoding of the same?

00 4E E5 65 36 80 85 7F E2 6C 89 5B FA 51


Here no problem inserting this string (Mac OSX 10.6.8)

sqlite> create table u8_t (u8c1 varchar(32));
sqlite> insert into u8_t values ('一日耶羅波安出');
sqlite> .mode list
sqlite> select * from u8_t;
u8c1
一日耶羅波安出
sqlite> .quit
[[bash SQLite]]
sqlite Club.sl3
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> 

 

Cordiali saluti/Vriendelijke groeten/Kind regards,
Klaas V
http://innocentisart.net
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Problem with select

2012-06-20 Thread Black, Michael (IS)
You missed the prepare for your select_sql:



 sqlite3_stmt *select_stmt = NULL;
 sqlite3_prepare_v2(db,select_sql,strlen(select_sql),_stmt,NULL);

Add that one line and you get:



Successfully bound string for insert: 'zweiter Anlauf/Versuch'
Successfully bound real for insert: 22
INSERT completed

Found row
Column eventtype(0): 'zweiter Anlauf/Versuch'
Column counter(1): '22'

SELECT successfully completed
[mblack@melb0113 sqlite]$



If you close and re-open the database last_row_id won't know anything.  That 
only lasts for your session.





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 deltagam...@gmx.net [deltagam...@gmx.net]
Sent: Wednesday, June 20, 2012 8:03 AM
To: sqlite-users
Subject: EXT :[sqlite] Problem with select

Hello,

I have a problem with the selection of the row, errorcode 21 s

Indeed I would like to retrieve all rows, but as far as I understand it,
this construction will retrieve only the last row/insert.

What happens if  the db is closed after the insert and reopened then for
the select-statement ?



===

#include 
#include 
#include "sqlite3.h"

#include 
#include 
#include 
#include 

using namespace std;


int main() {



 string filename = "errorlog.txt";
 std::ofstream outfile(filename);

 char eventtype[]="zweiter Anlauf/Versuch";
 int zaehler = strlen(eventtype);


 int rc;
 char *exec_errmsg;

 const char dbname[] = "ef.db";
 sqlite3 *db = NULL;

 rc = sqlite3_open(dbname, );
 if(SQLITE_OK != rc) {
 outfile << "Can't open database "<< dbname << " (" << rc << "):
" << sqlite3_errmsg(db) << std::endl;
 //fprintf(stderr, "Can't open database %s (%i): %s\n", dbname,
rc, sqlite3_errmsg(db));
 sqlite3_close(db);
 exit(1);
 }

/maybe discard this part
///
 const char create_sql[] = "CREATE TABLE eventlog ("
 "id INTEGER PRIMARY KEY,"
 "eventdate DATETIME default current_timestamp,"
 "eventtype TEXT,"
 "counter INTEGER"
 ")";

 // Use exec to run simple statements that can only fail/succeed
 rc = sqlite3_exec(db, create_sql, NULL, NULL, _errmsg);
 if(SQLITE_OK != rc) {
 outfile << "Error creating table (" << rc << "): " <<
sqlite3_errmsg(db) << std::endl;
 //fprintf(stderr, "Error creating table (%i): %s\n", rc,
exec_errmsg);
 sqlite3_free(exec_errmsg);
 //sqlite3_close(db);
 //exit(1);
 }

/maybe discard this part
///


 const char insert_sql[] = "INSERT INTO eventlog (eventtype,
counter) VALUES (?,?)";
 sqlite3_stmt *insert_stmt = NULL;



 rc = sqlite3_prepare_v2(db, insert_sql, -1, _stmt, NULL);
 if(SQLITE_OK != rc) {
 outfile << "Can't prepare insert statment " << insert_sql << "
(" << rc << "): " << sqlite3_errmsg(db) << std::endl;
 //fprintf(stderr, "Can't prepare insert statment %s (%i):
%s\n", insert_sql, rc, sqlite3_errmsg(db));
 sqlite3_close(db);
 exit(1);
 }



 rc = sqlite3_bind_text(insert_stmt, 1, eventtype,
strlen(eventtype), NULL);
 if(SQLITE_OK != rc) {
 outfile << "Error binding value in insert (%i): %s\n", rc,
sqlite3_errmsg(db);
 //fprintf(stderr, "Error binding value in insert (%i): %s\n",
rc, sqlite3_errmsg(db));
 sqlite3_close(db);
 exit(1);
 } else {
 printf("Successfully bound string for insert: '%s'\n", eventtype);
 }

 rc = sqlite3_bind_int(insert_stmt, 2, zaehler);
 if(SQLITE_OK != rc) {
 outfile << "Error binding value in insert (%i): %s\n", rc,
sqlite3_errmsg(db);
 //fprintf(stderr, "Error binding value in insert (%i): %s\n",
rc, sqlite3_errmsg(db));
 sqlite3_close(db);
 exit(1);
 } else {
 printf("Successfully bound real for insert: %d\n", zaehler);
 }


 rc = sqlite3_step(insert_stmt);
 if(SQLITE_DONE != rc) {
 outfile << "insert statement didn't return DONE (%i): %s\n",
rc, sqlite3_errmsg(db);
 //fprintf(stderr, "insert statement didn't return DONE (%i):
%s\n", rc, sqlite3_errmsg(db));
 } else {
 printf("INSERT completed\n\n");
 }


 // start selecting ///
 const char select_sql[] = "SELECT eventtype, counter FROM eventlog
WHERE id=?";
 //const char insert_sql[] = "INSERT INTO eventlog (eventtype,
counter) VALUES (?,?)";

 sqlite3_stmt *select_stmt = NULL;

 // Now attempt to get that row out
 sqlite3_int64 id = sqlite3_last_insert_rowid(db);
 sqlite3_bind_int64(select_stmt, 1, id);


 // This 

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

2012-06-20 Thread Pontus Bergsten
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 pre-determined number of reads.
>
>  1   while( true )
>  2  {
>  3 // Buffer data, blocks until new data is available
>  4 buffer.append( readData() )
>  5 ++numberOfReads;
>  6
>
>  7 if ( numberOfReads == maxReads )
>  8 {
>  9    globalTransactionMutex.request()
> 10
> 11    begin transaction
> 12    insert bufferered data to "main" in GlobalDbConnection
>
> 13    commit transaction
> 14
>
> 15    globalTransactionMutex.release()
> 16    numberOfReads = 0;
> 17 }
>
> 18   }
>
>
> Thread 2: Upon user action, transfer data within a specified time window from 
> the in-memory database to a database file
>
> 19  while( true )
>
> 20  {
> 21 waitForUserAction()
> 22
>
> 23 DestDbConnection = Create destination database file Dest.db
>
> 24 close DestDbConnection
> 25
>
> 26     attach Dest.db with GlobalDbConnection as "Dest"
>
> 27     transfer log data from "main" to "Dest" in GlobalDbConnection using 
> INSERT INTO Dest SELECT FROM main
>
> 28
>
> 29     globalTransactionMutex.request()
> 30     detach "Dest" from GlobalDbConnection
>
> 31     globalTransactionMutex.release()
> 32  }
>
> Attaching the destination database Dest.db and transferring data (23)-(27) 
> work without any hassle.
>
> The problem we're facing is that the detach statement (30) fails with error 
> message "SQL Logic error or missing database".
>
> The transaction is protected with a mutex, so it should not be possible to 
> execute the detach statement (30) during an ongoing transaction. Moreover, 
> both threads have the same priority, so we don't have any dead-lock issues.
>
> The code in Thread 2 (19) -(32) seems to work fine in a single threaded unit 
> test. We're using SQLite v. 3.6.21 on Windows XP.
>
>
> Does anyone have any idea about the source of this problem? Have we missed 
> something regarding SQLite and multi-threading?
>
>
> Regards,
>
> Pontus Bergsten
> ___
> 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] Problem with select

2012-06-20 Thread Simon Slavin

On 20 Jun 2012, at 2:03pm, deltagam...@gmx.net wrote:

> I have a problem with the selection of the row, errorcode 21 s

Look up the error code in the list, or make SQLite print out the text message 
which does with it.

On this page



you see that error code 21 means that you have called the wrong SQLite 
functions in the wrong order.  Looking at your code, I see that you are doing 
some binding and stepping for the SELECT command, but I do not see you have 
called _prepare() for it.  So perhaps you should take a look at this page



and make sure that you always follow all those five steps for every statement.

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


Re: [sqlite] Problem with select

2012-06-20 Thread Marcus Grimm

On 20.06.2012 15:03, deltagam...@gmx.net wrote:

Hello,

I have a problem with the selection of the row, errorcode 21 s


You are missing a sqlite3_prepare_v2 for your select.



Indeed I would like to retrieve all rows, but as far as I understand it, this 
construction will retrieve only the last
row/insert.


Well.. if it would work then yes because you ask for a specific ID, don't you ?



What happens if the db is closed after the insert and reopened then for the 
select-statement ?



What do you expect what will happen ?





===

#include 
#include 
#include "sqlite3.h"

#include 
#include 
#include 
#include 

using namespace std;


int main() {



string filename = "errorlog.txt";
std::ofstream outfile(filename);

char eventtype[]="zweiter Anlauf/Versuch";
int zaehler = strlen(eventtype);


int rc;
char *exec_errmsg;

const char dbname[] = "ef.db";
sqlite3 *db = NULL;

rc = sqlite3_open(dbname, );
if(SQLITE_OK != rc) {
outfile << "Can't open database "<< dbname << " (" << rc << "): " << 
sqlite3_errmsg(db) << std::endl;
//fprintf(stderr, "Can't open database %s (%i): %s\n", dbname, rc, 
sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}

/ maybe discard this part 
///
const char create_sql[] = "CREATE TABLE eventlog ("
"id INTEGER PRIMARY KEY,"
"eventdate DATETIME default current_timestamp,"
"eventtype TEXT,"
"counter INTEGER"
")";

// Use exec to run simple statements that can only fail/succeed
rc = sqlite3_exec(db, create_sql, NULL, NULL, _errmsg);
if(SQLITE_OK != rc) {
outfile << "Error creating table (" << rc << "): " << sqlite3_errmsg(db) << 
std::endl;
//fprintf(stderr, "Error creating table (%i): %s\n", rc, exec_errmsg);
sqlite3_free(exec_errmsg);
//sqlite3_close(db);
//exit(1);
}

/ maybe discard this part 
///


const char insert_sql[] = "INSERT INTO eventlog (eventtype, counter) VALUES 
(?,?)";
sqlite3_stmt *insert_stmt = NULL;



rc = sqlite3_prepare_v2(db, insert_sql, -1, _stmt, NULL);
if(SQLITE_OK != rc) {
outfile << "Can't prepare insert statment " << insert_sql << " (" << rc << "): " << 
sqlite3_errmsg(db) << std::endl;
//fprintf(stderr, "Can't prepare insert statment %s (%i): %s\n", insert_sql, 
rc, sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}



rc = sqlite3_bind_text(insert_stmt, 1, eventtype, strlen(eventtype), NULL);
if(SQLITE_OK != rc) {
outfile << "Error binding value in insert (%i): %s\n", rc, sqlite3_errmsg(db);
//fprintf(stderr, "Error binding value in insert (%i): %s\n", rc, 
sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
} else {
printf("Successfully bound string for insert: '%s'\n", eventtype);
}

rc = sqlite3_bind_int(insert_stmt, 2, zaehler);
if(SQLITE_OK != rc) {
outfile << "Error binding value in insert (%i): %s\n", rc, sqlite3_errmsg(db);
//fprintf(stderr, "Error binding value in insert (%i): %s\n", rc, 
sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
} else {
printf("Successfully bound real for insert: %d\n", zaehler);
}


rc = sqlite3_step(insert_stmt);
if(SQLITE_DONE != rc) {
outfile << "insert statement didn't return DONE (%i): %s\n", rc, 
sqlite3_errmsg(db);
//fprintf(stderr, "insert statement didn't return DONE (%i): %s\n", rc, 
sqlite3_errmsg(db));
} else {
printf("INSERT completed\n\n");
}


// start selecting ///
const char select_sql[] = "SELECT eventtype, counter FROM eventlog WHERE id=?";
//const char insert_sql[] = "INSERT INTO eventlog (eventtype, counter) VALUES 
(?,?)";

sqlite3_stmt *select_stmt = NULL;

// Now attempt to get that row out
sqlite3_int64 id = sqlite3_last_insert_rowid(db);
sqlite3_bind_int64(select_stmt, 1, id);


// This is your standard pattern
while(SQLITE_ROW == (rc = sqlite3_step(select_stmt))) {
int col;
printf("Found row\n");
for(col=0; col

[sqlite] Problem with select

2012-06-20 Thread deltagam...@gmx.net

Hello,

I have a problem with the selection of the row, errorcode 21 s

Indeed I would like to retrieve all rows, but as far as I understand it, 
this construction will retrieve only the last row/insert.


What happens if  the db is closed after the insert and reopened then for 
the select-statement ?




===

#include 
#include 
#include "sqlite3.h"

#include 
#include 
#include 
#include 

using namespace std;


int main() {



string filename = "errorlog.txt";
std::ofstream outfile(filename);

char eventtype[]="zweiter Anlauf/Versuch";
int zaehler = strlen(eventtype);


int rc;
char *exec_errmsg;

const char dbname[] = "ef.db";
sqlite3 *db = NULL;

rc = sqlite3_open(dbname, );
if(SQLITE_OK != rc) {
outfile << "Can't open database "<< dbname << " (" << rc << "): 
" << sqlite3_errmsg(db) << std::endl;
//fprintf(stderr, "Can't open database %s (%i): %s\n", dbname, 
rc, sqlite3_errmsg(db));

sqlite3_close(db);
exit(1);
}

/maybe discard this part 
///

const char create_sql[] = "CREATE TABLE eventlog ("
"id INTEGER PRIMARY KEY,"
"eventdate DATETIME default current_timestamp,"
"eventtype TEXT,"
"counter INTEGER"
")";

// Use exec to run simple statements that can only fail/succeed
rc = sqlite3_exec(db, create_sql, NULL, NULL, _errmsg);
if(SQLITE_OK != rc) {
outfile << "Error creating table (" << rc << "): " << 
sqlite3_errmsg(db) << std::endl;
//fprintf(stderr, "Error creating table (%i): %s\n", rc, 
exec_errmsg);

sqlite3_free(exec_errmsg);
//sqlite3_close(db);
//exit(1);
}

/maybe discard this part 
///



const char insert_sql[] = "INSERT INTO eventlog (eventtype, 
counter) VALUES (?,?)";

sqlite3_stmt *insert_stmt = NULL;



rc = sqlite3_prepare_v2(db, insert_sql, -1, _stmt, NULL);
if(SQLITE_OK != rc) {
outfile << "Can't prepare insert statment " << insert_sql << " 
(" << rc << "): " << sqlite3_errmsg(db) << std::endl;
//fprintf(stderr, "Can't prepare insert statment %s (%i): 
%s\n", insert_sql, rc, sqlite3_errmsg(db));

sqlite3_close(db);
exit(1);
}



rc = sqlite3_bind_text(insert_stmt, 1, eventtype, 
strlen(eventtype), NULL);

if(SQLITE_OK != rc) {
outfile << "Error binding value in insert (%i): %s\n", rc, 
sqlite3_errmsg(db);
//fprintf(stderr, "Error binding value in insert (%i): %s\n", 
rc, sqlite3_errmsg(db));

sqlite3_close(db);
exit(1);
} else {
printf("Successfully bound string for insert: '%s'\n", eventtype);
}

rc = sqlite3_bind_int(insert_stmt, 2, zaehler);
if(SQLITE_OK != rc) {
outfile << "Error binding value in insert (%i): %s\n", rc, 
sqlite3_errmsg(db);
//fprintf(stderr, "Error binding value in insert (%i): %s\n", 
rc, sqlite3_errmsg(db));

sqlite3_close(db);
exit(1);
} else {
printf("Successfully bound real for insert: %d\n", zaehler);
}


rc = sqlite3_step(insert_stmt);
if(SQLITE_DONE != rc) {
outfile << "insert statement didn't return DONE (%i): %s\n", 
rc, sqlite3_errmsg(db);
//fprintf(stderr, "insert statement didn't return DONE (%i): 
%s\n", rc, sqlite3_errmsg(db));

} else {
printf("INSERT completed\n\n");
}


// start selecting ///
const char select_sql[] = "SELECT eventtype, counter FROM eventlog 
WHERE id=?";
//const char insert_sql[] = "INSERT INTO eventlog (eventtype, 
counter) VALUES (?,?)";


sqlite3_stmt *select_stmt = NULL;

// Now attempt to get that row out
sqlite3_int64 id = sqlite3_last_insert_rowid(db);
sqlite3_bind_int64(select_stmt, 1, id);


// This is your standard pattern
while(SQLITE_ROW == (rc = sqlite3_step(select_stmt))) {
int col;
printf("Found row\n");
for(col=0; col

Re: [sqlite] How to select from table

2012-06-20 Thread Igor Tandetnik
deltagam...@gmx.net wrote:
> how can i select from sqlite3 db ?

By executing a SELECT statement, of course. See this example:

http://books.google.com/books?id=VsZ5bUh0XAkC=PA222

> How do I retrieve the number of records in a table ?

By running this statement: select count(*) from MyTable;
-- 
Igor Tandetnik

___
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-20 Thread Pavel Ivanov
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 pre-determined number of reads.
>
>  1   while( true )
>  2  {
>  3 // Buffer data, blocks until new data is available
>  4 buffer.append( readData() )
>  5 ++numberOfReads;
>  6
>
>  7 if ( numberOfReads == maxReads )
>  8 {
>  9    globalTransactionMutex.request()
> 10
> 11    begin transaction
> 12    insert bufferered data to "main" in GlobalDbConnection
>
> 13    commit transaction
> 14
>
> 15    globalTransactionMutex.release()
> 16    numberOfReads = 0;
> 17 }
>
> 18   }
>
>
> Thread 2: Upon user action, transfer data within a specified time window from 
> the in-memory database to a database file
>
> 19  while( true )
>
> 20  {
> 21 waitForUserAction()
> 22
>
> 23 DestDbConnection = Create destination database file Dest.db
>
> 24 close DestDbConnection
> 25
>
> 26     attach Dest.db with GlobalDbConnection as "Dest"
>
> 27     transfer log data from "main" to "Dest" in GlobalDbConnection using 
> INSERT INTO Dest SELECT FROM main
>
> 28
>
> 29     globalTransactionMutex.request()
> 30     detach "Dest" from GlobalDbConnection
>
> 31     globalTransactionMutex.release()
> 32  }
>
> Attaching the destination database Dest.db and transferring data (23)-(27) 
> work without any hassle.
>
> The problem we're facing is that the detach statement (30) fails with error 
> message "SQL Logic error or missing database".
>
> The transaction is protected with a mutex, so it should not be possible to 
> execute the detach statement (30) during an ongoing transaction. Moreover, 
> both threads have the same priority, so we don't have any dead-lock issues.
>
> The code in Thread 2 (19) -(32) seems to work fine in a single threaded unit 
> test. We're using SQLite v. 3.6.21 on Windows XP.
>
>
> Does anyone have any idea about the source of this problem? Have we missed 
> something regarding SQLite and multi-threading?
>
>
> Regards,
>
> Pontus Bergsten
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Problem with concurrent transaction and DETACH DATABASE

2012-06-20 Thread Pontus Bergsten
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 pre-determined number of reads.

 1   while( true )
 2  {
 3 // Buffer data, blocks until new data is available
 4 buffer.append( readData() )
 5 ++numberOfReads;
 6

 7 if ( numberOfReads == maxReads )
 8 {
 9    globalTransactionMutex.request()
10
11    begin transaction
12    insert bufferered data to "main" in GlobalDbConnection

13    commit transaction
14

15    globalTransactionMutex.release()
16    numberOfReads = 0;
17 }

18   }


Thread 2: Upon user action, transfer data within a specified time window from 
the in-memory database to a database file

19  while( true )

20  {
21 waitForUserAction()
22

23 DestDbConnection = Create destination database file Dest.db

24 close DestDbConnection
25

26     attach Dest.db with GlobalDbConnection as "Dest"

27     transfer log data from "main" to "Dest" in GlobalDbConnection using 
INSERT INTO Dest SELECT FROM main

28

29     globalTransactionMutex.request()
30     detach "Dest" from GlobalDbConnection 

31     globalTransactionMutex.release()
32  }

Attaching the destination database Dest.db and transferring data (23)-(27) work 
without any hassle.

The problem we're facing is that the detach statement (30) fails with error 
message "SQL Logic error or missing database".

The transaction is protected with a mutex, so it should not be possible to 
execute the detach statement (30) during an ongoing transaction. Moreover, both 
threads have the same priority, so we don't have any dead-lock issues.

The code in Thread 2 (19) -(32) seems to work fine in a single threaded unit 
test. We're using SQLite v. 3.6.21 on Windows XP.


Does anyone have any idea about the source of this problem? Have we missed 
something regarding SQLite and multi-threading?


Regards,

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


Re: [sqlite] How to select from table

2012-06-20 Thread deltagam...@gmx.net

Am 20.06.2012 12:02, schrieb deltagam...@gmx.net:

Hello,

how can i select from sqlite3 db  ?

How do I retrieve the number of records in a table ?

Thx in advance


Sorry forgot to tell, I'm using the c++ API
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] How to select from table

2012-06-20 Thread deltagam...@gmx.net

Hello,

how can i select from sqlite3 db ?

How do I retrieve the number of records in a table ?

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