Re: [sqlite] why is the data file locked after after using transaction?

[liubin liu]

You are right

I have mend the fault, ^_^

Thank You a lot!


Igor Tandetnik wrote:
> 
> liubin liu wrote:
>> why is the data file locked after after using transaction?
>>
>> the last result of printf() is:
>> # IN END, ret = 5
>>
>> It means to the database file is locked?
>> why does it happen after using transaction although using
>> sqlite3_finalize()?
> 
> You call sqlite3_prepare_v2 n+2 times, but sqlite3_finalize only once. 
> You leak statement handles like there's no tomorrow.
> 
> Igor Tandetnik 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/why-is-the-data-file-locked-after-after-using-transaction--tp24085034p24085861.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] why is the data file locked after after using transaction?

[Igor Tandetnik]

liubin liu wrote:
> why is the data file locked after after using transaction?
>
> the last result of printf() is:
> # IN END, ret = 5
>
> It means to the database file is locked?
> why does it happen after using transaction although using
> sqlite3_finalize()?

You call sqlite3_prepare_v2 n+2 times, but sqlite3_finalize only once. 
You leak statement handles like there's no tomorrow.

Igor Tandetnik 



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

[sqlite] why is the data file locked after after using transaction?

[liubin liu]

why is the data file locked after after using transaction?

the last result of printf() is:
# IN END, ret = 5

It means to the database file is locked?
why does it happen after using transaction although using
sqlite3_finalize()?


PS:
if shielding the codes relating with the transaction, all the result of
printf() is correct.


___

// basic9.c

#include  // for sleep();
#include  // for system();
#include   // for printf();

#include 

int main ( void )
{
int ret = 0;
int i = 0;
int n = 10;

sqlite3 *db = NULL;
sqlite3_stmt *p_stmt = NULL;

char *sql_ct = "CREATE TABLE table1 (id INTEGER, m INTEGER, con
VARCHAR(512))";
char *sql_in = "INSERT INTO table1 VALUES (%d, %d, %Q)";
char *sql = NULL;

ret = sqlite3_open ( "test.db",  );
printf ( "# AFTER sqlite3_open, ret = %d\n", ret );

ret = sqlite3_exec ( db, sql_ct, NULL, NULL, NULL );
printf ( "# AFTER sqlite3_exec, create table,   ret = %d\n", ret );

// system ( "free" );
// 采用事务;
ret = sqlite3_prepare_v2 ( db, "BEGIN", -1, _stmt, 0 );
ret = sqlite3_step ( p_stmt );

// 创建事务;
for ( i=0; i

Re: [sqlite] Asynchronous I/O Module For SQLite

[D. Richard Hipp]

On Jun 17, 2009, at 7:18 PM, Rizzuto, Raymond wrote:

> I am interested in using sqlite3 for an application where I don't  
> have a requirement that data persists after the using process ends.   
> The db is used as a backing store for infrequently used records.

So use an in-memory database.  Open with sqlite3_open(":memory:",  
).  Or if your content is too big for memory, use a temporary  
database whose name is the empty string:  sqlite3_open("", ).

D. Richard Hipp
d...@hwaci.com



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

[sqlite] Asynchronous I/O Module For SQLite

[Rizzuto, Raymond]

I am interested in using sqlite3 for an application where I don't have a 
requirement that data persists after the using process ends.  The db is used as 
a backing store for infrequently used records.  Performance requirements are 
~1500 "transactions" per second.

I would like to use the asynch i/o module since it sounds like it would meet my 
requirements.  However, I have a few questions:

-  The files for asynch i/o aren't in the amalgamations, but only in 
the tarball of the complete source  tree.  Can I just pull out the 2 files I 
need from that, and use it with the installed sqlite3?
-  We are currently using sqlite 3.5.9 in house.  Can I use the 
asynchronous i/o module with that version?

Thanks for any assistance,

Ray



Ray Rizzuto
raymond.rizz...@sig.com
Susquehanna International Group
(610)747-2336 (W)
(215)776-3780 (C)




IMPORTANT: The information contained in this email and/or its attachments is 
confidential. If you are not the intended recipient, please notify the sender 
immediately by reply and immediately delete this message and all its 
attachments. Any review, use, reproduction, disclosure or dissemination of this 
message or any attachment by an unintended recipient is strictly prohibited. 
Neither this message nor any attachment is intended as or should be construed 
as an offer, solicitation or recommendation to buy or sell any security or 
other financial instrument. Neither the sender, his or her employer nor any of 
their respective affiliates makes any warranties as to the completeness or 
accuracy of any of the information contained herein or that this message or any 
of its attachments is free of viruses.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] minimum cache_size for a database connection?

[Dave Toll]

Both "PRAGMA cache_size=10" and "PRAGMA cache_size=1" result in minimal
caching, which is great. What is the expected behaviour of "PRAGMA
cache_size=0" for the default page cache? I'd like to know whether
setting cache_size=0 has some special significance, maybe it's a case I
need to prevent.

Many thanks for the replies,
Dave.


-Original Message-
From: D. Richard Hipp [mailto:d...@hwaci.com] 
Sent: 17 June 2009 10:08
To: General Discussion of SQLite Database
Subject: Re: [sqlite] minimum cache_size for a database connection?


On Jun 17, 2009, at 12:56 PM, Dave Toll wrote:

> What is the correct way to specify that SQLite should perform no  
> caching
> (or at least minimal caching)? PRAGMA cache_size=0 seems to have no
> effect - either it continues to use the default, or tries to caches
> everything.


Did you try "PRAGMA cache_size=10"?

You can use sqlite3_config() with SQLITE_CONFIG_PCACHE to install your  
very own cache manager that does anything you like.  See

 http://www.sqlite.org/c3ref/config.html
 http://www.sqlite.org/c3ref/c_config_getmalloc.html
 http://www.sqlite.org/c3ref/pcache_methods.html

Other resources:

 http://www.sqlite.org/malloc.html#pagecache
 http://www.sqlite.org/c3ref/soft_heap_limit.html

D. Richard Hipp
d...@hwaci.com




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

Re: [sqlite] [noob] merge statement equivalent?

[James Gregurich]

oops. sorry for errant message, folks. I had the wrong email selected  
when I hit the button and didn't pay attention to what I was doing.

On Jun 17, 2009, at 3:19 PM, James Gregurich wrote:

>
> So what are you going to do? we need to get your plans pinned down.
>
> On Jun 17, 2009, at 11:46 AM, James Gregurich wrote:
>
>>
>> Dennis,
>>
>> question on an old post of yours below...
>>
>>
>> on that update statement, is the SQL optimizer smart enough to not
>> rerun that select statement for each column in the update's set
>> clause? Is it going to run a single select statement to get ,
>> , etc.  or is it going to run one for each column in the
>> update statement?
>>
>> -James
>>
>>> Petite Abeille wrote:

 How does one emulate a DML MERGE statement in SQLite [1]?

 INSERT OR REPLACE sounds promising but the REPLACE documentation
>>> under
 the ON CONFLICT clause seems to imply that in the case of a
>>> constraint
 violation the existing row will be deleted entirely and then
>>> replaced
 by a brand new row instead of being merely updated [2].

 Apologies if this is a FAQ, but my google-fu is eluding me on this
>>> one.

 Thanks in advance.

>>>
>>> I haven't tested this so take it with a grain of salt, but I think
>>> this
>>> should do the same thing as the merge statement.
>>>
>>> Given two tables, table1 and table2.
>>>
>>> merge into table1 using table2 on 
>>>when matched then update
>>>set  = ,
>>> =  ...
>>>when not matched then insert ,  ...
>>>values (,  ...)
>>>
>>> Should be the same as the following series of SQL statements.
>>>
>>> create temp table matches as
>>>select t1.rowid as row1, t2.rowid as row2
>>>from table1
>>>join table2
>>>where 
>>>
>>> insert into table1 (,  ...)
>>>select ,  ... from table2
>>>where rowid not in (select row2 from matches);
>>>
>>> update table1
>>>set  = (select  from table2
>>>where table2.rowid =
>>>(select row2 from matches
>>>where row1 = rowid)),
>>> = (select  from table2
>>>where table2.rowid =
>>>(select row2 from matches
>>>where row1 = rowid))
>>>...
>>>where rowid in (select row1 from matches);
>>>
>>> drop table matches;
>>>
>>>
>>> HTH
>>> Dennis Cote
>>> ___
>>> 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

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

Re: [sqlite] [noob] merge statement equivalent?

[James Gregurich]

So what are you going to do? we need to get your plans pinned down.

On Jun 17, 2009, at 11:46 AM, James Gregurich wrote:

>
> Dennis,
>
> question on an old post of yours below...
>
>
> on that update statement, is the SQL optimizer smart enough to not
> rerun that select statement for each column in the update's set
> clause? Is it going to run a single select statement to get ,
> , etc.  or is it going to run one for each column in the
> update statement?
>
> -James
>
>> Petite Abeille wrote:
>>>
>>> How does one emulate a DML MERGE statement in SQLite [1]?
>>>
>>> INSERT OR REPLACE sounds promising but the REPLACE documentation
>> under
>>> the ON CONFLICT clause seems to imply that in the case of a
>> constraint
>>> violation the existing row will be deleted entirely and then
>> replaced
>>> by a brand new row instead of being merely updated [2].
>>>
>>> Apologies if this is a FAQ, but my google-fu is eluding me on this
>> one.
>>>
>>> Thanks in advance.
>>>
>>
>> I haven't tested this so take it with a grain of salt, but I think
>> this
>> should do the same thing as the merge statement.
>>
>> Given two tables, table1 and table2.
>>
>> merge into table1 using table2 on 
>> when matched then update
>> set  = ,
>>  =  ...
>> when not matched then insert ,  ...
>> values (,  ...)
>>
>> Should be the same as the following series of SQL statements.
>>
>> create temp table matches as
>> select t1.rowid as row1, t2.rowid as row2
>> from table1
>> join table2
>> where 
>>
>> insert into table1 (,  ...)
>> select ,  ... from table2
>> where rowid not in (select row2 from matches);
>>
>> update table1
>> set  = (select  from table2
>> where table2.rowid =
>> (select row2 from matches
>> where row1 = rowid)),
>>  = (select  from table2
>> where table2.rowid =
>> (select row2 from matches
>> where row1 = rowid))
>> ...
>> where rowid in (select row1 from matches);
>>
>> drop table matches;
>>
>>
>> HTH
>> Dennis Cote
>> ___
>> 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] [noob] merge statement equivalent?

[James Gregurich]

Dennis,

question on an old post of yours below...


on that update statement, is the SQL optimizer smart enough to not  
rerun that select statement for each column in the update's set  
clause? Is it going to run a single select statement to get ,  
, etc.  or is it going to run one for each column in the  
update statement?

-James

> Petite Abeille wrote:
> >
> > How does one emulate a DML MERGE statement in SQLite [1]?
> >
> > INSERT OR REPLACE sounds promising but the REPLACE documentation  
> under
> > the ON CONFLICT clause seems to imply that in the case of a  
> constraint
> > violation the existing row will be deleted entirely and then  
> replaced
> > by a brand new row instead of being merely updated [2].
> >
> > Apologies if this is a FAQ, but my google-fu is eluding me on this  
> one.
> >
> > Thanks in advance.
> >
>
> I haven't tested this so take it with a grain of salt, but I think  
> this
> should do the same thing as the merge statement.
>
> Given two tables, table1 and table2.
>
> merge into table1 using table2 on 
>  when matched then update
>  set  = ,
>   =  ...
>  when not matched then insert ,  ...
>  values (,  ...)
>
> Should be the same as the following series of SQL statements.
>
> create temp table matches as
>  select t1.rowid as row1, t2.rowid as row2
>  from table1
>  join table2
>  where 
>
> insert into table1 (,  ...)
>  select ,  ... from table2
>  where rowid not in (select row2 from matches);
>
> update table1
>  set  = (select  from table2
>  where table2.rowid =
>  (select row2 from matches
>  where row1 = rowid)),
>   = (select  from table2
>  where table2.rowid =
>  (select row2 from matches
>  where row1 = rowid))
>  ...
>  where rowid in (select row1 from matches);
>
> drop table matches;
>
>
> HTH
> Dennis Cote
> ___
> 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 Exception System.InvalidCastException: Specified cast is not valid.

[Robert Simpson]

The best place to get answers to .NET-related issues with the
System.Data.SQLite provider is:
http://sqlite.phxsoftware.com

I've got plenty of forums over there so we don't clutter up the list with
wrapper issues unrelated to the engine itself.

Robert Simpson


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joel Lucsy
Sent: Wednesday, June 17, 2009 5:53 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] sqlite Exception System.InvalidCastException:
Specified cast is not valid.

On Wed, Jun 17, 2009 at 2:18 AM, Seysan wrote:
> I get the Exception there!  sqlite Exception
> System.InvalidCastException: Specified cast is not valid.
>
> The GetFieldType Shows: Systes.Byte[]

Couldn't find my code where I've done it at. Tho I did find that I
used a ExecuteScalar() that returns byte[].
Could it be that the field is null?
Also, what version of System.Data.Sqlite.dll are you running? I use
1.0.60 but haven't tested 1.0.61 yet.
-- 
Joel Lucsy
"The dinosaurs became extinct because they didn't have a space
program." -- Larry Niven
___
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] minimum cache_size for a database connection?

[Dave Toll]

What is the correct way to specify that SQLite should perform no caching
(or at least minimal caching)? PRAGMA cache_size=0 seems to have no
effect - either it continues to use the default, or tries to caches
everything.

Cheers,
Dave.


-Original Message-
From: D. Richard Hipp [mailto:d...@hwaci.com] 
Sent: 16 June 2009 17:33
To: General Discussion of SQLite Database
Subject: Re: [sqlite] minimum cache_size for a database connection?


On Jun 16, 2009, at 8:28 PM, Dave Toll wrote:

> Hello list
>
>
>
> I've noticed that if I call "PRAGMA cache_size=0", my database rows  
> are
> still cached (very little disk access observed). If I call "PRAGMA
> cache_size=1" I can see that there is very little caching (disk access
> observed). Is there a minimum allowed cache_size setting? Does
> cache_size=0 mean "cache everything"? I am using SQLite 3.6.13 with  
> the
> default page cache, and shared-cache enabled.


I think any  cache size suggestion less than 10 is ignored.  That used  
to be the case.  I don't know if it still is.

Note that this does *not* control your operating systems file cache.   
This is SQLite's user-space cache only.

If more pages than the cache allotment are required, the the cache  
size limit is automatically increased.


D. Richard Hipp
d...@hwaci.com




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

Re: [sqlite] Sqlite-3.5.9: getting sqlite_autoindex error

[hiral]

Hi Simon,

 I looked into it but couldn't find the exact reason for my bug.

 If you have any idea please let me know.

 Also I would appreciate, if you can let me know what does this error mean
in terms of btree/pager module of sqlite

>> sqlite> PRAGMA integrity_check;
>> *** in database main ***
>> Page 5275 is never used
>> wrong # of entries in index sqlite_autoindex__1
Thank you.
-Hiral
On Wed, Jun 17, 2009 at 9:42 PM, Simon Davies <
simon.james.dav...@googlemail.com> wrote:

> 2009/6/17 hiral :
> > Any suggestion on this !!!
>
> Your database has been corrupted.
> http://www.sqlite.org/lockingv3.html#how_to_corrupt
>
> Rgds,
> Simon
>
> >
> > On Tue, Jun 16, 2009 at 4:34 PM, h o  wrote:
> >
> >> Hi,
> >>
> >> I am using sqlite-3.5.9 and observing a 'disk image malformed' error
> >> nfs, on doing 'PRAGMA integrity_check' I got following messages...
> >>
> >> SQLite version 3.5.9
> >> Enter ".help" for instructions
> >> sqlite> PRAGMA integrity_check;
> >> *** in database main ***
> >> Page 5275 is never used
> >> wrong # of entries in index sqlite_autoindex__1
> >> sqlite>
> >>
> >> Can you please let me know what is the problem here.
> >>
> >> Thank you.
> >> -Hiral
> >>
> > ___
> > 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] NULL data .dump command

[Joanne Pham]

Hi All,
Any hints!
I have no problem with executing the followng sql : select * from signature
but if I run this : select * from sig order by peerid; then I got the error 
message:
    SQL error: database disk image is malformed
I have index on peerid and I don't know why the second select has problem?
Any idea!
Thanks,
JP





From: Joanne Pham 
To: General Discussion of SQLite Database 
Sent: Tuesday, June 16, 2009 3:41:17 PM
Subject: Re: [sqlite] NULL data .dump command

Hi All,
I ran two queries:
    1) select * from signature;
        I didn't see the "SQL error: database disk image is malformed"

    2) But if I ran the "select * from sig order by peerid;" then I have seen 
the malformed
        ...
        11020876449360377856|345049073990|1276|368|230383|1857|1245183730|2|0
        SQL error: database disk image is malformed
   
Is the index corruped some where?
Your help is greatly appreciated.
Thanks,
JP





From: Joanne Pham 
To: General Discussion of SQLite Database 
Sent: Tuesday, June 16, 2009 3:26:37 PM
Subject: [sqlite] NULL data .dump command

Hi All,
I have the table is defined as below:
CREATE TABLE `signature` (
  `sig` char(50) NOT NULL, 
  `id' bigint(20) default '0',

But I have ran the folowing command:
    .output mySelect
    select * from signature;
    then I didn't see NULL values in the mySelect file at all
But I ran the following command:
    .output myDump
    .dump signature
    then I viewed the file it has the following NULL values
       INSERT INTO "signature" 
VALUES('573535428650752000',345049073990,1294,365,230296,414,1245183707,2,'0');
            INSERT INTO "signature" 
VALUES(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
           even though sig is defined as NOT NULL but why I have NULL for some 
of these insert statement in my dump but not in select.
Thanks,
JP


      
___
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] Sqlite-3.5.9: getting sqlite_autoindex error

[Simon Davies]

2009/6/17 hiral :
> Any suggestion on this !!!

Your database has been corrupted.
http://www.sqlite.org/lockingv3.html#how_to_corrupt

Rgds,
Simon

>
> On Tue, Jun 16, 2009 at 4:34 PM, h o  wrote:
>
>> Hi,
>>
>> I am using sqlite-3.5.9 and observing a 'disk image malformed' error
>> nfs, on doing 'PRAGMA integrity_check' I got following messages...
>>
>> SQLite version 3.5.9
>> Enter ".help" for instructions
>> sqlite> PRAGMA integrity_check;
>> *** in database main ***
>> Page 5275 is never used
>> wrong # of entries in index sqlite_autoindex__1
>> sqlite>
>>
>> Can you please let me know what is the problem here.
>>
>> Thank you.
>> -Hiral
>>
> ___
> 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 (SQ3) Interfacing with SQL Server 2005 Standard Edition.

[Roosevelt Anderson]

I made a simple command line app that exports a MSSQL query to a sqlite
table. This might not be exactly what you need but it should be in the
ballpark. The app and the source can be found here:

http://www.proghack.net/?p=40


On Wed, Jun 17, 2009 at 5:25 AM, David Cawley
wrote:

> Users,
>
>
>
> I have a requirement to have two separate databases, MS SQL Server 2005
> Standard Edition and SQLite (SQ3) interface to avoid duplication of
> certain data.
>
>
>
> The preference of the project is to have the SQL Server 2005 database
> populated and then duplicate and insert data directly into SQLite
> Database. I am thinking the SLQ Server can duplicate data easily using a
> stored procedure.
>
>
>
> Each database is behind its own GUI where the information would be
> viewed.
>
>
>
> Has anybody done anything like this before?
>
>
>
> Please let me know, as I am currently not sure that this is at all
> feasible, whether the story is good or bad.
>
>
>
> Thanks.
>
>
>
> David.
>
>
>
>
> Reliance High-Tech Limited (registered number 2025063)
> is registered in England and Wales and has its registered office at
> Boundary House, Cricketfield Road, Uxbridge, Middlesex UB8 1QG.
>
> This e-mail is confidential and is intended solely for use by the
> individual to whom it is addressed.
> Access to this e-mail by anyone else is unauthorised. If you are not the
> intended recipient, please
> notify us immediately by returning this e-mail to sender or calling +44 (0)
> 20 8391 2200 and delete
> this e-mail and any copies of it from your computer system. Please be
> advised that if you have
> received this mail in error, any use, dissemination, forwarding, printing
> or copying of it is
> strictly prohibited.
>
> Any views or opinions expressed in this e-mail are solely those of the
> author and do not necessarily
> represent those of the company.
>
> Although we operate an active anti-virus policy, the company accepts no
> liability for any damage
> caused by any virus transmitted by this e-mail, including any attachments.
> ___
> 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] Is the omit_readlock pragma still working?

[Joerg Hoinkis]

> Hi!
> 
> I'm trying to access a readonly database without locks, using the 
> amalgamation of SQLite 3.6.14.2
> 
> After I opened the database with flags = SQLITE_OPEN_READONLY I perform
> 
> sqlite3_exec (db, "PRAGMA omit_readlock=ON", 0, 0, 0);
> 
> I traced that down into the amalgamation to the point where the database 

> flag is set in flagPragma...
> 
> db->flags |= p-mask;
> 
> So I can be sure the pragma syntax is correct.
> 
> 
> Now when I execute a SELECT statement, it is parsed and I run into 
> pagerSharedLock, but
> the pager->noReadlock variable is 0. As a result the database file gets 
> locked.
> 
> 
> It look like the initialization of the pager->noReadlock flag, but that 
> happens when the database is
> opened.

I messed up the last sentence, better:

It looks like the initialization of the pager->noReadlock flag only 
happens
when the database is opened, but by that time the omit_readlock pragma is
not active.

> 
> Am I doing something wrong or did this ("Very experimental") feature got 

> lost in past refactorings?
> 
> 
> Thanks for listening & in advance to any hints

Joerg

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- - - - - - - - - - - - - - - - - - - - - - - - - - -
Steinberg Media Technologies GmbH, Neuer Hoeltigbaum 22-32, D-22143 Hamburg, 
Germany
Phone: +49 (40) 21035-0 | Fax: +49 (40) 21035-300 | www.steinberg.net 
Managing Director: Andreas Stelling, Kazunori Kobayashi, Takuya Nakata
Registration Court: Hamburg HRB 86534
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- - - - - - - - - - - - - - - - - - - - - - - - - - -

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

[sqlite] Is the omit_readlock pragma still working?

[Joerg Hoinkis]

Hi!

I'm trying to access a readonly database without locks, using the 
amalgamation of SQLite 3.6.14.2

After I opened the database with flags = SQLITE_OPEN_READONLY I perform

sqlite3_exec (db, "PRAGMA omit_readlock=ON", 0, 0, 0);

I traced that down into the amalgamation to the point where the database 
flag is set in flagPragma...

db->flags |= p-mask;

So I can be sure the pragma syntax is correct.


Now when I execute a SELECT statement, it is parsed and I run into 
pagerSharedLock, but
the pager->noReadlock variable is 0. As a result the database file gets 
locked.


It look like the initialization of the pager->noReadlock flag, but that 
happens when the database is
opened.

Am I doing something wrong or did this ("Very experimental") feature got 
lost in past refactorings?


Thanks for listening & in advance to any hints
Joerg



- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- - - - - - - - - - - - - - - - - - - - - - - - - - -
Steinberg Media Technologies GmbH, Neuer Hoeltigbaum 22-32, D-22143 Hamburg, 
Germany
Phone: +49 (40) 21035-0 | Fax: +49 (40) 21035-300 | www.steinberg.net 
Managing Director: Andreas Stelling, Kazunori Kobayashi, Takuya Nakata
Registration Court: Hamburg HRB 86534
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- - - - - - - - - - - - - - - - - - - - - - - - - - -

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

Re: [sqlite] Question about searches

[Olaf Schmidt]

"Christophe Leske"  schrieb im
Newsbeitrag news:4a37b811.4030...@multimedial.de...

>> You write your own comparison function that would consider
>> these two strings equal. See sqlite3_create_function,
>> sqlite3_create_collation.
>
> this problem pertains not only to Zürich, but to 24000 other
> entries, so
> ...
> How do you educate SQlite to return me
> "Sào Paulo" if only "Sao Paulo" is being entered?

As already advised above, the best thing is, to write a small
collation-extension for sqlite, which can be registered then
dynamically, before you start working with your data.

An implementation of such a small collation-routine is
pretty easy, if you use the right system-api-call, to compare
your strings - and under windows that is CompareStringW.
http://msdn.microsoft.com/en-us/library/ms647476.aspx

Before passing WStringPointers to that function, you will
have to convert your UTF8 into UTF16 beforehand -
either *after* entering your Collation-Callback with UTF8-
Data using the "officiall" MultiByteToWideChar-API,
using codepage 65001.

Alternatively simply define the correct SQLite-constant
whilst registering your new collation within the engine,
what your string-parameters (passed into your Callback)
are expected to be ... - for Win-WChars use:
SQLITE_UTF16
or
SQLITE_UTF16LE

After that you should be able, to implement this collation-
callback with only a few lines of code.

CompareStringW offers some nice Flags, which will be
useful for you as I see it:
E.g. with a combination of:
NORM_IGNORENONSPACE | NORM_IGNORESYMBOLS |
NORM_IGNORECASE

...and an LCID of 1033 (us-en, which should be available on each system)

...the following comparisons are all evaluated to be identical:
"a" = "Ä"
"Sao Paulo" = "Sào Paulo"
"Cote d azur" = "Côte d'azur"

and with LCID 1031 you will additionally get:
"SS" = "ß"
"ae" = "Ä"

Not that much control as with a selfdefined mapping
of course, but a whole lot more "tolerant" than what
you currently have.

Olaf




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

Re: [sqlite] Database inserts gradually slowing down

[Jens Páll Hafsteinsson]

Here's the code I'm using to run the test (it includes the schema). I've been 
running this code for the last few days and am a bit baffled about my recent 
results, since I'm not seeing the gradual slowing anymore. This happened after 
I changed to use version 3.6.15 of sqlite and even if I change back to 3.6.14 
it still behaves very consistently, that is, doing the insert and delete in 
constant time.

I don't think I changed anything in my original 3.6.14 install but I can't be 
sure since after going into 'silly mode' I copied the 3.6.15 sqlite code over 
my origianl 3.6.14 sqlite code and I now have no way of checking if I made some 
changes there. At most, I may have changed some defines but if the sqlite 
documentation does not suggest such a thing I doubt that I have done so on my 
own accord. I have always compiled the code defining SQLITE_THREADSAFE=1.

The only thing I can think of that's different from what I did when I saw the 
gradual slowing was that I am now always creating a new database when running 
the test (I delete the file) and used 3.6.15 for a while. I had been hammering 
away on the same database using the test code below for a while before I 
noticed the increase in execution time. Could that be a factor? Could it also 
be that 3.6.15 did some cleanup somewhere which could have been confusing my 
previous 3.6.14, since now when I use 3.6.14 it seems quite stable?

Anyway, I will continue to try and reproduce the slowing (by not creating a new 
database and re-use the table) but I'm getting a bit pessimistic on being able 
to do so, which is a good thing if this was a fluke, but also a bad thing since 
it leaves behind a nagging feeling that something might still be wrong. Maybe 
it should be a best practice to vacuum the database regularly to keep it in 
shape?


// Code begins

#include 
#include 
#include 
#include 

int
callback(void* arg, int argc, char** argv, char** column_names)
{
/*
for (int i = 0; i < argc; i++)
{
std::cout << argv[i] << " ";
}
std::cout << std::endl;
*/

return 0;
}

int
main()
{
sqlite3* db;
int res;
char* error_msg = 0;

/*
std::cout << "Opening time file...";
std::fstream file("time.txt", std::ios::app);
if (!file.is_open())
{
std::cerr << "failed\n";
exit(1);
}
std::cout << "done\n";
*/

std::cout << "Opening database...";
res = sqlite3_open("my1.db", );
if (SQLITE_OK != res)
{
std::cerr << "failed\n";
std::cerr << sqlite3_errmsg(db) << std::endl;
sqlite3_close(db);
exit(1);
}
std::cout << "done\n";

std::string sql;

/**/
std::cout << "Dropping table t1...";
sql = "drop table t1";
res = sqlite3_exec(db, sql.c_str(), callback, 0, _msg);
if (SQLITE_OK != res)
{
std::cerr << "failed\n";
std::cerr << error_msg << std::endl;
//exit(1);
}
else
{
std::cout << "done" << std::endl;
}

std::cout << "Creating table t1...";
sql = "create table t1(a integer, b integer, c varchar(100))";
res = sqlite3_exec(db, sql.c_str(), callback, 0, _msg);
if (SQLITE_OK != res)
{
std::cerr << "failed\n";
std::cerr << error_msg << std::endl;
exit(1);
}
std::cout << "done" << std::endl;

std::cout << "Creating index i1 on t1...";
sql = "create index i1 on t1(a)";
res = sqlite3_exec(db, sql.c_str(), callback, 0, _msg);
if (SQLITE_OK != res)
{
std::cerr << "failed\n";
std::cerr << error_msg << std::endl;
exit(1);
}
std::cout << "done" << std::endl;
/**/

while (true)
{
//std::cout << "Deleting from table t1...";
sql = "delete from t1";
res = sqlite3_exec(db, sql.c_str(), callback, 0, _msg);
if (SQLITE_OK != res)
{
std::cerr << "failed\n";
std::cerr << error_msg << std::endl;
exit(1);
}
//std::cout << "done" << std::endl;

SYSTEMTIME start;
SYSTEMTIME end;

double start_seconds;
double end_seconds;
int record_count = 1000;
int batches = 100;

//std::cout << "Inserting and selecting " << record_count * batches << 
" records in batches of " << record_count << std::endl;

GetSystemTime();

char sql_c[1024];
for (int b = 0; b < batches; b++)
{
int row;

//std::cout << "Beginning transaction...";
sql = "begin transaction";
res = sqlite3_exec(db, sql.c_str(), callback, 0, _msg);
if (SQLITE_OK != res)
{
std::cerr << "failed\n";
std::cerr << error_msg << std::endl;
exit(1);
}
//std::cout << "done" << std::endl;

for (int i = 0; i < record_count; i++)
{
row = b * record_count + i;
 

Re: [sqlite] Sqlite-3.5.9: getting sqlite_autoindex error

[hiral]

Any suggestion on this !!!

On Tue, Jun 16, 2009 at 4:34 PM, h o  wrote:

> Hi,
>
> I am using sqlite-3.5.9 and observing a 'disk image malformed' error
> nfs, on doing 'PRAGMA integrity_check' I got following messages...
>
> SQLite version 3.5.9
> Enter ".help" for instructions
> sqlite> PRAGMA integrity_check;
> *** in database main ***
> Page 5275 is never used
> wrong # of entries in index sqlite_autoindex__1
> sqlite>
>
> Can you please let me know what is the problem here.
>
> Thank you.
> -Hiral
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to how many memory is used by sqlite

[pierr]

pierr wrote:
> 
> 
> D. Richard Hipp wrote:
>> 
>> 
>> On Jun 15, 2009, at 9:04 AM, pierr wrote:
>> 
>>>
>>> Hi all,
>>>   I am using an memory type sqlite database in our embedded
>>> application. After the applicate run a while ,the system will run  
>>> ourt of
>>> out of memory. So, is there a way to know how many memory is used by  
>>> sqlite?
>>> That would include the memory for page cache , any other other  
>>> internal
>>> memory used the sqlite ,as well as the memory for database itself.  
>>> Thanks
>>> for your help.
>> 
>> http://www.sqlite.org/c3ref/memory_highwater.html
>> http://www.sqlite.org/malloc.html
>> 
>> 
> 
> By using the memory_highwater api above , I found that most of the memory
> is used for page cache. By limiting the cache size , I can have a good
> control of how many heap memory will be used.
> 
> INSERT Record NumbersPAGE SiZE   CACHE SIZE HIGH WATER
> MARK
> (110bytes per record)
> 
> 10001024   20
> 98,256
> 10001024   2000
> 271,040
> 80001024   2000
> 1,562,144
> 80001024   20
> 99,200
> 
> And as Simon suggest ,when I close the connection, the memory used by
> sqlite will be Zero.
> 
> I am more clear about how sqlite use memory now , however ,as I am using
> memory database ,I also cares about the database size. That is what is
> proportion of database size to the raw data size.
> 
> I insert a record of 112 bytes 10,1000,and 1 times repectively and
> here list the output database size. For The last case ,it has a propotion
> about 1.45. What cost the extra 0.45 here beside the btree? 
> 
>  10240  2009-06-17 14:38 /etc/eit.flash.sqlite.10
>  171008 2009-06-17 14:32 /etc/eit.flash.sqlite.1000
>  1634304   2009-06-17 14:42 /etc/eit.flash.sqlite.1
> 
> (I am using the Flash database instead of the memory data here as I think
> the result should be the same.)
> 
> 


I was wrong here.

In my application (Not the test enviroment mentiond above), after the sqlite
eating up more than 8M bytes in the memory , I dumpded the database to the
Flash but it is only 360K which is very reasonable for my application. The
database was configured as cache_szie = 20 ,page_size=1024 ,so the page
cache would not take too much memory ,it should be less than 100K according
to above measurement. 

So, for the non-memory based data base ,the total storage needed is :
generated database size + memory used by sqlite3 internal.

But for the memory database, the total storage needed will be much bigger
than that. What will eat the extra buck of memory here?  I can think of the
journey file ,but it should be K level memory..

Thanks for help me out.
-- 
View this message in context: 
http://www.nabble.com/How-to-how-many-memory-is-used-by-sqlite-tp24034261p24073060.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] sqlite Exception System.InvalidCastException: Specified cast is not valid.

[Joel Lucsy]

On Wed, Jun 17, 2009 at 2:18 AM, Seysan wrote:
> I get the Exception there!  sqlite Exception
> System.InvalidCastException: Specified cast is not valid.
>
> The GetFieldType Shows: Systes.Byte[]

Couldn't find my code where I've done it at. Tho I did find that I
used a ExecuteScalar() that returns byte[].
Could it be that the field is null?
Also, what version of System.Data.Sqlite.dll are you running? I use
1.0.60 but haven't tested 1.0.61 yet.
-- 
Joel Lucsy
"The dinosaurs became extinct because they didn't have a space
program." -- Larry Niven
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] why is the data file locked after writing?

[Igor Tandetnik]

liubin liu wrote:
> why is the data file locked after writing?
>
> the last printf() result is:
> # IN END, ret = 5

Because you still have a statement in progress. Finalize it with 
sqlite3_finalize.

Igor Tandetnik 



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

[sqlite] why is the data file locked after writing?

[liubin liu]

why is the data file locked after writing?

the last printf() result is:
# IN END, ret = 5

according to the "Result Codes":
#define SQLITE_BUSY 5   /* The database file is locked */

It means to the database file is locked? why does it happen after writing?


_

// basic10.c

#include 
#include 

#include 


int main ( void )
{
int ret = 0;

sqlite3 *db = NULL;
sqlite3_stmt *p_stmt = NULL;

char *sql_ct = "CREATE TABLE table1 (id INTEGER, m INTEGER, n
VARCHAR(32), t CHAR(1), con VARCHAR(512))";
char *sql_in = "INSERT INTO table1 VALUES (%d, %d, %Q, %d, %Q)";
char *sql = NULL;

ret = sqlite3_open ( "test.db",  );
printf ( "# AFTER sqlite3_open, ret = %d\n", ret );

ret = sqlite3_exec ( db, sql_ct, NULL, NULL, NULL );
printf ( "# AFTER sqlite3_exec, create table,   ret = %d\n", ret );

sql = sqlite3_mprintf ( sql_in, 0, 0, "goodc", 0, "test -
varcharvarcharvarchar" );
ret = sqlite3_prepare_v2 ( db, sql, -1, _stmt, NULL );
printf ( "# AFTER sqlite3_prepare_v2,   ret = %d\n", ret );
ret = sqlite3_step ( p_stmt );
printf ( "# AFTER sqlite3_step, ret = %d\n", ret );
sqlite3_free ( sql );

ret = sqlite3_close ( db );
printf ( "# IN END, ret = %d\n", ret );
system ( "rm test.db" );

return 0;
}

_

[...@lb basic]$ make basic10
[...@lb basic]$ ./basic10 
# AFTER sqlite3_open, ret = 0
# AFTER sqlite3_exec, create table,   ret = 0
# AFTER sqlite3_prepare_v2,   ret = 0
# AFTER sqlite3_step, ret = 101
# IN END, ret = 5

-- 
View this message in context: 
http://www.nabble.com/why-is-the-data-file-locked-after-writing--tp24071633p24071633.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] sendfile from the DB

[Philipp Marek]

Hello everybody,

I already saw this discussed in 2006 (see
http://www.mail-archive.com/sqlite-users@sqlite.org/msg14703.html), but there 
was no
final answer.

I'm thinking about storing some bigger data blocks in a database, or, if that's 
not
efficiently doable, address files in the filesystem (eg. via the primary key).
Using the DB directly has the advantage that the file is already open, so 
directory
lookups and secondary/tertiary block pointers loading can be avoided.


I'd like a way to specify "send N bytes from position P from field F of record 
R", where
the record would be specified via some WHERE clause.
The simplest case would be addressing via the primary key; in this case sqlite 
could
(depending on the internal structure?) maybe just do a sendfile() (see
http://linux.die.net/man/2/sendfile) without ever reading the data (if the 
primary key
can be converted to a position in the database file).

Apart from using some copies (which would be the major benefit over using some 
other DB
that has to be accessed via some socket) this could also provide cache 
benefits, and
reduces memory usage (as no buffers are needed, which might be several MB big).


Regards,

Phil

-- 
Versioning your /etc, /home or even your whole installation?
 Try fsvs (fsvs.tigris.org)!

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

[sqlite] Reading Blob/Writing to File in C# 2008

[AFShin Abdollahi]

When I used your code I get:  No current row

If I use: rdr.Read(); before your code, I get the same exception as before.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Reading Blob/Writing to File in C# 2008

[AFShin Abdollahi]

Hello,
I'm Using  "System.Data.SQLite" in VS 2008 C#.

I'm using DataReader to read the data.
Everything works find except the BLOB.
I want to get that Blob and Write it to a File.

Blob data is mostly Picture and Music.

Here a bit of Code:
did-> is the document id.

String cmd = String.Format("SELECT filename,content FROM documents
WHERE did={0}",did);

contentCommand = sqlconn.CreateCommand();
contentCommand.CommandText = cmd;
rdr = contentCommand.ExecuteReader();

I tried rdr.GetBytes but it give me this Error:

System.InvalidCastException was unhandled
  Message="Specified cast is not valid."
  Source="System.Data.SQLite"
  StackTrace:
   at System.Data.SQLite.SQLiteDataReader.VerifyType(Int32 i, DbType typ)
   at System.Data.SQLite.SQLiteDataReader.GetBytes(Int32 i, Int64
fieldOffset, Byte[] buffer, Int32 bufferoffset, Int32 length)
   at SDA_Viewer.Form1.btnShow_Click(Object sender, EventArgs e)
in Form1.cs:line 139
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message& m,
MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at System.Windows.Forms.ButtonBase.WndProc(Message& m)
   at System.Windows.Forms.Button.WndProc(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
   at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr
hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
   at 
System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32
dwComponentID, Int32 reason, Int32 pvLoopData)
   at 
System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32
reason, ApplicationContext context)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32
reason, ApplicationContext context)


I've read most of the mailing list archive, and any other resource on
Web, So far nothing !

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

Re: [sqlite] Sqlite3 database upgrade from 3.5.4 to 3.6.15 issue

[Kalyani Phadke]

if I create dump using sqlite3.5.4 and recreate DB using that dump data
using sqlite3.5.4,everything works fine. if I create dump using
sqlite3.5.4 and recreate DB using that dump data using sqlite3.6.15,gets
error. Pls find attached file.
 
The text generated from .dump command of sqlite3 version 3.5.4.
CREATE TABLE Users
(
ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,
name VARCHAR(50) NOT NULL COLLATE NOCASE,   
Page VARCHAR(255) NULL DEFAULT ("../xyz/main.asp")

);

Changed text to

CREATE TABLE Users
(
ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,
name VARCHAR(50) NOT NULL COLLATE NOCASE,   
 Page VARCHAR(255) NULL DEFAULT ('../xyz/main.asp')
);

After changing everything seems to be fine. 

Thanks,
-Kalyani
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of D. Richard Hipp
Sent: Tuesday, June 16, 2009 12:23 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Sqlite3 database upgrade from 3.5.4 to 3.6.15
issue


On Jun 16, 2009, at 3:10 PM, Kalyani Phadke wrote:

> But before entering the bug , I would like to find out how this 
> happened. Whats the cause of it. Because I am not able to duplicate it

> all the time. IS there any way to find out what went wrong?
> Thanks,
>> I have to go manually and edit sql ,make changes so that everything 
>> works fine.
>>

What do you have to manually edit to make it work.  Please show us the
text before and after you edit.


D. Richard Hipp
d...@hwaci.com



___
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] SQLite (SQ3) Interfacing with SQL Server 2005 Standard Edition.

[David Cawley]

Users,

 

I have a requirement to have two separate databases, MS SQL Server 2005
Standard Edition and SQLite (SQ3) interface to avoid duplication of
certain data.

 

The preference of the project is to have the SQL Server 2005 database
populated and then duplicate and insert data directly into SQLite
Database. I am thinking the SLQ Server can duplicate data easily using a
stored procedure.

 

Each database is behind its own GUI where the information would be
viewed.

 

Has anybody done anything like this before?

 

Please let me know, as I am currently not sure that this is at all
feasible, whether the story is good or bad.

 

Thanks.

 

David.

 


Reliance High-Tech Limited (registered number 2025063) 
is registered in England and Wales and has its registered office at 
Boundary House, Cricketfield Road, Uxbridge, Middlesex UB8 1QG.  

This e-mail is confidential and is intended solely for use by the individual to 
whom it is addressed. 
Access to this e-mail by anyone else is unauthorised. If you are not the 
intended recipient, please 
notify us immediately by returning this e-mail to sender or calling +44 (0) 20 
8391 2200 and delete 
this e-mail and any copies of it from your computer system. Please be advised 
that if you have 
received this mail in error, any use, dissemination, forwarding, printing or 
copying of it is 
strictly prohibited. 

Any views or opinions expressed in this e-mail are solely those of the author 
and do not necessarily 
represent those of the company. 

Although we operate an active anti-virus policy, the company accepts no 
liability for any damage 
caused by any virus transmitted by this e-mail, including any attachments.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to how many memory is used by sqlite

[pierr]

D. Richard Hipp wrote:
> 
> 
> On Jun 15, 2009, at 9:04 AM, pierr wrote:
> 
>>
>> Hi all,
>>   I am using an memory type sqlite database in our embedded
>> application. After the applicate run a while ,the system will run  
>> ourt of
>> out of memory. So, is there a way to know how many memory is used by  
>> sqlite?
>> That would include the memory for page cache , any other other  
>> internal
>> memory used the sqlite ,as well as the memory for database itself.  
>> Thanks
>> for your help.
> 
> http://www.sqlite.org/c3ref/memory_highwater.html
> http://www.sqlite.org/malloc.html
> 
> 

By using the memory_highwater api above , I found that most of the memory is
used for page cache. By limiting the cache size , I can have a good control
of how many heap memory will be used.

INSERT Record NumbersPAGE SiZE   CACHE SIZE HIGH WATER MARK
(110bytes per record)

10001024   20
98,256
10001024   2000
271,040
80001024   2000
1,562,144
80001024   20
99,200

And as Simon suggest ,when I close the connection, the memory used by sqlite
will be Zero.

I am more clear about how sqlite use memory now , however ,as I am using
memory database ,I also cares about the database size. That is what is
proportion of database size to the raw data size.

I insert a record of 112 bytes 10,1000,and 1 times repectively and here
list the output database size. For The last case ,it has a propotion about
1.45. What cost the extra 0.45 here beside the btree? 

 10240  2009-06-17 14:38 /etc/eit.flash.sqlite.10
 171008 2009-06-17 14:32 /etc/eit.flash.sqlite.1000
 1634304   2009-06-17 14:42 /etc/eit.flash.sqlite.1

(I am using the Flash database instead of the memory data here as I think
the result should be the same.)

Here are the table structure. For the 112 bytes in each record , 100 bytes
goes to the event_name field.
CREATE TABLE IF NOT EXISTS tblEvent_basic(
sguid  INT,
service_id  INT,
event_id  INT,
start_time  VARCHAR(5),
duration  VARCHAR(3),
running_status  INT,
free_ca_mode  INT,
event_name  VARCHAR(256),
text  VARCHAR(256))

Thanks for your comment.
-- 
View this message in context: 
http://www.nabble.com/How-to-how-many-memory-is-used-by-sqlite-tp24034261p24068533.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] sqlite Exception System.InvalidCastException: Specified cast is not valid.

[Seysan]

This is the Code in C#:

SQLiteDataReader rdr;
SQLiteCommand contentCommand;
String cmd = String.Format("SELECT
filename,content,filesize FROM documents WHERE did={0}",did);

contentCommand = sqlconn.CreateCommand();
contentCommand.CommandText = cmd;
rdr = contentCommand.ExecuteReader();
rdr.Read();
MessageBox.Show(rdr.GetFieldType(1).ToString());
MessageBox.Show(rdr.FieldCount.ToString());

-->long len = rdr.GetBytes(1, 0, null, 0, 0);
Byte[] buf = new Byte[len];
rdr.GetBytes(1, 0, buf, 0,(int) len);

I get the Exception there!  sqlite Exception
System.InvalidCastException: Specified cast is not valid.

The GetFieldType Shows: Systes.Byte[]

What is it that I'm doing wrong?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users