[sqlite] Getting random data from grouped data in one table

Here is the situation. I have one database table "TB_Patient" with the columns below: PATIENT_ID PATIENT_GROUP FIRSTNAME LASTNAME ADDRESS I want to get PATIENT_ID randomly from certain PATIENT_GROUP. There are 5 PATIENT_GROUP only with total row in the table is about 1 (10K). The number of

Re: [sqlite] Storing data with SQLite

Igor Tandetnik wrote: > > It's possible with an UPDATE statement, but it's expensive. You can't just > append to an existing blob, you have to provide all of the blob's data, > old and new. > > You might be better off storing the data in separate files, and just using > SQLite to maintain

Re: [sqlite] Storing data with SQLite

On 9 March 2011 08:54, liran ritkop wrote: > . . . > > I upload a file which contain the important lines in my code. > The result in the blob data, as i said, is 32 bytes, which means 32 chars: > 0123456789:;<=>?@ABCDEFGHIJKLMNO > http://old.nabble.com/file/p31104442/toUpload.c

Re: [sqlite] Storing data with SQLite

Thanks.. dumb error, but i did it.. :-) Simon Davies wrote: > > On 9 March 2011 08:54, liran ritkop wrote: >> > . > . > . >> >> I upload a file which contain the important lines in my code. >> The result in the blob data, as i said, is 32 bytes, which means 32 >> chars: >>

[sqlite] Failure during Rollback statement

What would happen if the following situation occurs: I begin a transaction and then do multiple sqlite execute statements. One of the execute statements fails and I try doing a rollback. Now when I try the rollback the rollback also fails. Does it mean database is corrupted? Or will it be rolled

Re: [sqlite] Failure during Rollback statement

On Wed, Mar 9, 2011 at 5:01 AM, pkcpkc wrote: > > What would happen if the following situation occurs: > I begin a transaction and then do multiple sqlite execute statements. One > of > the execute statements fails and I try doing a rollback. Now when I try the >

Re: [sqlite] Storing data with SQLite

liran ritkop wrote: > Ok, I'll do it that way, it's a smart idea. > The question is, if i use files and not blob data, when i want to update the > data (this time the file, and not the blob field) it can lead to some kind > of fragmentation. You are using Flash storage - why

Re: [sqlite] Failure during Rollback statement

On Wed, Mar 09, 2011 at 02:01:14AM -0800, pkcpkc scratched on the wall: > > What would happen if the following situation occurs: > I begin a transaction and then do multiple sqlite execute statements. One of > the execute statements fails and I try doing a rollback. Now when I try the > rollback

Re: [sqlite] Failure during Rollback statement

On 9 Mar 2011, at 1:34pm, Jay A. Kreibich wrote: > On Wed, Mar 09, 2011 at 02:01:14AM -0800, pkcpkc scratched on the wall: >> > >> What would happen if the following situation occurs: >> I begin a transaction and then do multiple sqlite execute statements. One of >> the execute statements

Re: [sqlite] Storing data with SQLite

On 9 Mar 2011, at 13:05, Igor Tandetnik wrote: > liran ritkop wrote: >> Ok, I'll do it that way, it's a smart idea. >> The question is, if i use files and not blob data, when i want to update the >> data (this time the file, and not the blob field) it can lead to some kind >>

Re: [sqlite] Getting random data from grouped data in one table

On Wed, Mar 9, 2011 at 11:53 AM, eric wong wrote: > But now, I must get the data from certain PATIENT_GROUP. > > What's your best approach to solve this? The objective is the fastest > possible query. > > Thanks. > > There's an approach with "order by random" mentioned by

[sqlite] memory usage after VACUUM

I'm using sqlite in an iOS app, via the popular FMDB wrapper. My profiling tool is showing me that the app is using 2.5 MB of memory before a VACUUM, and nearly 6MB after. The tool shows that the extra memory was allocated by sqlite3MemMalloc(). If I close and re-open the database then the

Re: [sqlite] memory usage after VACUUM

On 09.03.2011, at 16:23, Nick Hodapp wrote: > I'm using sqlite in an iOS app, via the popular FMDB wrapper. > > My profiling tool is showing me that the app is using 2.5 MB of memory > before a VACUUM, and nearly 6MB after. The tool shows that the extra memory > was allocated by

Re: [sqlite] memory usage after VACUUM

> Is there any sqlite function I can call, or some other technique, to reduce > the memory allocated and hung-onto by sqlite, particularly during a VACUUM? Yes, execute "pragma cache_size = 100" for example, or put other number of your liking into there. If closing and re-opening of the database

[sqlite] duplicate a prepared statement?

Is it possible to duplicate a prepared statement? I have a need to run the same query multiple times with different parameters. I can't re-use the prepared statement because multiple copies of the same query, but with different parameters, are active at the same time. That is, I need to step

[sqlite] Creation and modification timestamps with "insert or replace" possible?

Hi there, I'm trying this for quite some time... and I'm totally stuck. I have the following table: CREATE TABLE example(  date integer primary key not null,  text text,  ctime TIMESTAMP,  mtime TIMESTAMP); ctime=creation time (should be set only once) mtime=modification time (should be set

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

On Mar 9, 2011, at 5:19 PM, Armin Kunaschik wrote: > Hi there, > > I'm trying this for quite some time... and I'm totally stuck. > > I have the following table: > > CREATE TABLE example( > date integer primary key not null, > text text, > ctime TIMESTAMP, > mtime TIMESTAMP); > >

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

On Wed, Mar 9, 2011 at 11:21 PM, Mr. Puneet Kishor wrote: > > On Mar 9, 2011, at 5:19 PM, Armin Kunaschik wrote: > >> Hi there, >> >> I'm trying this for quite some time... and I'm totally stuck. >> >> I have the following table: >> >> CREATE TABLE example( >>  date integer

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

On 3/9/2011 5:19 PM, Armin Kunaschik wrote: > I'm trying this for quite some time... and I'm totally stuck. > > I have the following table: > > CREATE TABLE example( > date integer primary key not null, > text text, > ctime TIMESTAMP, > mtime TIMESTAMP); > > ctime=creation time (should be

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

On Wed, Mar 9, 2011 at 11:28 PM, Igor Tandetnik wrote: > On 3/9/2011 5:19 PM, Armin Kunaschik wrote: >> I'm trying this for quite some time... and I'm totally stuck. >> >> I have the following table: >> >> CREATE TABLE example( >>   date integer primary key not null, >>  

[sqlite] Possible bug in FTS3 "NOT" operator

With ICU extension does not work "NOT ..." construction. Below the example from FTS3 documentation: sqlite> CREATE VIRTUAL TABLE docs USING fts3(); sqlite> INSERT INTO docs(docid, content) VALUES(1, 'a database is a software system'); sqlite> INSERT INTO docs(docid, content) VALUES(2, 'sqlite is

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

On Wed, Mar 09, 2011 at 11:19:51PM +0100, Armin Kunaschik scratched on the wall: > Important: I also want to be able to "insert or replace" rows and keep ctime. > The update trigger works fine, but the insert trigger ALWAYS updates ctime. As Igor pointed out, "INSERT OR REPLACE" is called

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

On 9 Mar 2011, at 10:37pm, Armin Kunaschik wrote: > Is it really DELETE and INSERT internally? As far as you can tell when how SQLite behaves, yes it is. > This would explain, why ctime is "invisible" inside the trigger... > it's simply not there > when replace is used. > > Maybe I should

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

On Wed, Mar 9, 2011 at 11:44 PM, Simon Slavin wrote: > > On 9 Mar 2011, at 10:37pm, Armin Kunaschik wrote: > >> Is it really DELETE and INSERT internally? > > As far as you can tell when how SQLite behaves, yes it is. > >> This would explain, why ctime is "invisible" inside

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

On Wed, Mar 09, 2011 at 11:37:46PM +0100, Armin Kunaschik scratched on the wall: > On Wed, Mar 9, 2011 at 11:28 PM, Igor Tandetnik wrote: > > INSERT OR REPLACE is indistinguishable from a DELETE followed by INSERT. > Is it really DELETE and INSERT internally? Yes and no.

[sqlite] Query within user defined funtion / UDF in sqlite

Hello Experts, How can i execute a query from user defined function. the user defined function is defined inside sqlite itself (in shell.c opendb function, there is user defined function shellstatic but it doenst query the DB) for example: an user defined function is intended to get

Re: [sqlite] Query within user defined funtion / UDF in sqlite

RAKESH HEMRAJANI wrote: > for example: > > an user defined function is intended to get the rowcount of the input table. > > user can type the below command > > select rowcount('employee') from sqlite_master; What's the role of sqlite_master in this query?

Re: [sqlite] Creation and modification timestamps with "insert or replace" possible?

On 10-Mar-2011, at 6:52 AM, Jay A. Kreibich wrote: > On Wed, Mar 09, 2011 at 11:37:46PM +0100, Armin Kunaschik scratched on the > wall: >> On Wed, Mar 9, 2011 at 11:28 PM, Igor Tandetnik wrote: > >>> INSERT OR REPLACE is indistinguishable from a DELETE followed by INSERT.