Re: [sqlite] how can i speed up inserts ?

[ohadp]

what about the differences between the FULL and PARTIAL modes of pragma
synchronous ?

-- 
View this message in context: 
http://www.nabble.com/how-can-i-speed-up-inserts---tf3186848.html#a8860860
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] how can i speed up inserts ?

[DragonK]

On 2/8/07, Dennis Cote <[EMAIL PROTECTED]> wrote:


DragonK wrote:
>
>
> Well, using transactions would be a little difficult, because I have a
> library which does something like logging to a database. Using
> transaction
> on a single insert would be useless in my opinion, and on the other
> hand I
> can't use transactions on the all inserts, since logging is a continuous
> process, it doesn't end.
>
You can basically have a transaction open all the time. Begin a
transaction on startup. Insert your log entries as they happen. Execute
a commit and another begin after every 100 records, and commit when your
program ends. You can also get more sophisticated adding a timer that
will also do a commit and begin say one second after the last log entry
if you need to. You will get a speedup by nearly a factor of 100, and if
you have a power failure or OS crash you will only lose  the uncommitted
records, not your entire database.

HTH
Dennis Cote



Thanks for your reply, Dennis -  I'll consider this alternative.




--
...it's only a matter of time...

Re: [sqlite] Select and deletion

[DragonK]

On 2/8/07, Jay Sprenkle <[EMAIL PROTECTED]> wrote:


On 2/7/07, DragonK <[EMAIL PROTECTED]> wrote:
>
>
> This seems to work... but I'm not sure if it should, that's why I'm
> asking.
> I hope the code will show more exactly what I intend to do. Sorry for
the
> lack of proper error checking, it's just a test program. :) Also, I'm
not
> using any transactions, just the PRAGMA synchronous= off;



If it works that's great. I would double check that it did work correctly.
I'll have to poke around now and see why what I did, which was pretty
similar, didn't work.




Please inform me if you find anything wrong with this scenario, because I
want to use something similar in production code.

Also, I'd appreciate any other opinions if some of you tried something
similar and had good/bad results.

I don't know the internals of SQLite or of databases in general, but in this
case i think the database knows the next row to be selected and deleting the
currently selected row would not interfere with the select operation i
think :)

Thanks!



--

The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com





--
...it's only a matter of time...

RE: [sqlite] ATTACH and :memory: databases

[Dan Kennedy]

On Wed, 2007-02-07 at 11:12 -0800, Dave Gierok wrote:
> I have one :memory: database open and I want to make a copy of it into 
> another :memory: database.

Ah, I get it now. Misunderstood earlier.

A new in-memory database is created each time ':memory:' is 
attached (using ATTACH) or opened (via sqlite3_open). You
cannot currently open a :memory: database via sqlite3_open() and 
then attach it to another handle.



>   When I open the 2nd :memory: database and call attach on the first one 
> specifying ':memory:' as the file name, it allows me to do this.  However 
> when I loop through the tables copying them into the 2nd one, it is still 
> empty after this has been done.  I think the first :memory: database might be 
> attaching to, uh, itself.
> 
> Is there a way to specify the name of a memory database uniquely?  Something 
> like ':memory:2'?  Nothing that I've tried works.

> -Dave Gierok
> 
> -Original Message-
> From: Dan Kennedy [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, February 07, 2007 9:56 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] ATTACH and :memory: databases
> 
> On Wed, 2007-02-07 at 09:37 -0800, Dave Gierok wrote:
> > I would like to attach a :memory: database to another :memory: database.  
> > How is this possible since the ATTACH command requires the database's file 
> > name?
> >
> > ATTACH [DATABASE] database-filename AS database-name
> >
> > -Dave Gierok
> 
> I don't think there are any special issues. Use ':memory:' as the
> filename.
> 
> [EMAIL PROTECTED]:~> sqlite3 :memory:
> SQLite version 3.2.8
> Enter ".help" for instructions
> sqlite> attach ':memory:' as db2;
> sqlite> attach ':memory:' as db3;
> sqlite> create table db2.abc(a, b, c);
> sqlite> select * from sqlite_master;
> sqlite> select * from db3.sqlite_master;
> sqlite> select * from db2.sqlite_master;
> table|abc|abc|2|CREATE TABLE abc(a, b, c)
> sqlite>
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Select and deletion

[Jay Sprenkle]

On 2/7/07, DragonK <[EMAIL PROTECTED]> wrote:



This seems to work... but I'm not sure if it should, that's why I'm
asking.
I hope the code will show more exactly what I intend to do. Sorry for the
lack of proper error checking, it's just a test program. :) Also, I'm not
using any transactions, just the PRAGMA synchronous= off;




If it works that's great. I would double check that it did work correctly.
I'll have to poke around now and see why what I did, which was pretty
similar, didn't work.

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

Re: [sqlite] how can i speed up inserts ?

[Jay Sprenkle]

On 2/7/07, ohadp <[EMAIL PROTECTED]> wrote:



luckily this isn't mission critical failure, i'll take the risk that one
out
of ten thousand users experiences database corruption.

i can only batch the inserts into maybe 4-5 a time, don't know how much of
an improvement that will be...




If you're building the database with a mass load and not doing an
interactive update it's much faster
to drop all the indexes, insert all the data, then create the indexes.


--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

Re: [sqlite] What does this mean???

[Clark Christensen]

Jim,

Line 398 in dbdimp.c appears to be in DBD-SQLite's $sth->execute code.

I agree with Puneet.  If you wrap your DBI calls in eval blocks and test $@, 
you might get more info about the error (or maybe not).  Also, setting 
RaiseError, and ShowErrorStatement in $dbh wouldn't hurt.

$dbh = DBI->connect("dbi:SQLite:dbname=$dbname","","", {RaiseError=>1, 
ShowErrorStatement=>1});
$sql = "insert or replace into...";
eval { $sth = $dbh->prepare( $sql ) };
die $@ if ($@);
eval { $sth->execute };
die $@ if ($@);


Not sure if any of that helps at all.  "not an error" on $sth->execute is bound 
to be nasty to diagnose.  FWIW, it looks like "not an error" is the result of a 
call to sqlite3_errmsg().

Unrelated to your issue is a DBI presentation you may be interested to look 
through at http://search.cpan.org/src/TIMB/DBI_AdvancedTalk_2004/index.htm

 -Clark

- Original Message 
From: "Anderson, James H (IT)" <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, February 7, 2007 3:28:00 PM
Subject: RE: [sqlite] What does this mean???

Yes, I printed out the errstr. That was what I sent in the original
mail.

$sth = $dbh->prepare( $sql ) or die $dbh->errstr;
$sth->executeor die $dbh->errstr;

which produced:

not an error(21) at dbdimp.c line 398

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
Kishor
Sent: Wednesday, February 07, 2007 6:16 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] What does this mean???

On 2/7/07, Anderson, James H (IT) <[EMAIL PROTECTED]>
wrote:
> Context :)
>
> insert or replace into TMP_credDerivOrig
> select
> A.date,
..
> A.CDRefId
>   from TMP_credDerivOrig   A,
>C1_credDerivComment B
>  where A.CDId = B.CDId;


yes, but you want to know why an error is appearing, so you have to
show the code that is causing the error. The above is just the SQL
statement. How on earth can one decipher whether or not you have some
error in your code from that.

Did you print out the DBI err str? Did you wrap it in an eval and then
print out the reason it died? That would really help find the cause of
the error.

Else, you can always open up dbdimp.c and look at line 398. That
should set you in the right direction.

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
> Kishor
> Sent: Wednesday, February 07, 2007 5:39 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] What does this mean???
>
> On 2/7/07, Anderson, James H (IT) <[EMAIL PROTECTED]>
> wrote:
> > not an error(21) at dbdimp.c line 398
> >
> > I'm using DBD::SQLite and got this error. What does it mean and how
> best
> > to hanle it?
> >
>
>
> well, how about some context? What is the structure of the table(s)
> you are querying, what is the query, the relevant code snippet,
> something to go on?
>
> The error string itself means that you did something via Perl that
> violated whatever it was at line 398 of the c program dbdimp.c.
>
> --
> Puneet Kishor http://punkish.eidesis.org/
> Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation http://www.osgeo.org/education/
> -
> collaborate, communicate, compete
> =
>
>

> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>

> -
> 
>
> NOTICE: If received in error, please destroy and notify sender. Sender
does not intend to waive confidentiality or privilege. Use of this email
is prohibited when received in error.
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-





-
To unsubscribe, send email to [EMAIL PROTECTED]

Re: [sqlite] how can i speed up inserts ?

[Gerry Snyder]

ohadp wrote:

when you say much less do you mean one in 1,000 or 1 in 1,000,000 ?



Gerry Snyder-3 wrote:
If you really can't get more than 4-5 in a batch, it's your call whether 
the time saving is worth the risk. BTW, the problem rate should be much 
less than one in ten thousand.


Sorry. I meant much less frequent than 1 in 10,000, so more like 1 in 
100,000 or 1 in 1,000,000.


But please note, I was thinking of hardware or OS problems only. As was 
pointed out elsewhere, stupid users can change the odds a lot.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] What does this mean???

[Anderson, James H \(IT\)]

Yes, I printed out the errstr. That was what I sent in the original
mail.

$sth = $dbh->prepare( $sql ) or die $dbh->errstr;
$sth->executeor die $dbh->errstr;

which produced:

not an error(21) at dbdimp.c line 398

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
Kishor
Sent: Wednesday, February 07, 2007 6:16 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] What does this mean???

On 2/7/07, Anderson, James H (IT) <[EMAIL PROTECTED]>
wrote:
> Context :)
>
> insert or replace into TMP_credDerivOrig
> select
> A.date,
..
> A.CDRefId
>   from TMP_credDerivOrig   A,
>C1_credDerivComment B
>  where A.CDId = B.CDId;


yes, but you want to know why an error is appearing, so you have to
show the code that is causing the error. The above is just the SQL
statement. How on earth can one decipher whether or not you have some
error in your code from that.

Did you print out the DBI err str? Did you wrap it in an eval and then
print out the reason it died? That would really help find the cause of
the error.

Else, you can always open up dbdimp.c and look at line 398. That
should set you in the right direction.

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
> Kishor
> Sent: Wednesday, February 07, 2007 5:39 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] What does this mean???
>
> On 2/7/07, Anderson, James H (IT) <[EMAIL PROTECTED]>
> wrote:
> > not an error(21) at dbdimp.c line 398
> >
> > I'm using DBD::SQLite and got this error. What does it mean and how
> best
> > to hanle it?
> >
>
>
> well, how about some context? What is the structure of the table(s)
> you are querying, what is the query, the relevant code snippet,
> something to go on?
>
> The error string itself means that you did something via Perl that
> violated whatever it was at line 398 of the c program dbdimp.c.
>
> --
> Puneet Kishor http://punkish.eidesis.org/
> Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation http://www.osgeo.org/education/
> -
> collaborate, communicate, compete
> =
>
>

> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>

> -
> 
>
> NOTICE: If received in error, please destroy and notify sender. Sender
does not intend to waive confidentiality or privilege. Use of this email
is prohibited when received in error.
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] how can i speed up inserts ?

[ohadp]

when you say much less do you mean one in 1,000 or 1 in 1,000,000 ?



Gerry Snyder-3 wrote:
> 
> If you really can't get more than 4-5 in a batch, it's your call whether 
> the time saving is worth the risk. BTW, the problem rate should be much 
> less than one in ten thousand.
> 
> Gerry
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/how-can-i-speed-up-inserts---tf3186848.html#a8856637
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] What does this mean???

[Anderson, James H \(IT\)]

sqlite-version: 3.3.5
DBD-SQLite: 1.12

-Original Message-
From: Clark Christensen [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, February 07, 2007 6:15 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] What does this mean???

What version of SQLite, and what version of DBD-SQLite?

I saw this predictably with DBD-SQLite 1.09 and SQLite 3.2.7 where I did
something like:

$sth = $dbh->prepare("select foo, bar from mytable where rowid =
?");
for $i (1..5)  {
($myfoo, $mybar) = $dbh->selectrow_array($sth, undef, $i);
}

It would work for the first iteration, then raise a "not an error" error
on the second.  It was annoying, but I usually just worked around it
using $sth->execute/bind_columns/fetch inside the loop.

I updated to DBD-SQLite 1.13 with SQLite 3.3.12, and it worked like it's
documented for DBI (fixed the problem).

 -Clark

- Original Message 
From: "Anderson, James H (IT)" <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, February 7, 2007 2:33:21 PM
Subject: [sqlite] What does this mean???

not an error(21) at dbdimp.c line 398

I'm using DBD::SQLite and got this error. What does it mean and how best
to hanle it?

Thanks,

jim


NOTICE: If received in error, please destroy and notify sender. Sender
does not intend to waive confidentiality or privilege. Use of this email
is prohibited when received in error.





-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] What does this mean???

[P Kishor]

On 2/7/07, Anderson, James H (IT) <[EMAIL PROTECTED]> wrote:

Context :)

insert or replace into TMP_credDerivOrig
select
A.date,

..

A.CDRefId
  from TMP_credDerivOrig   A,
   C1_credDerivComment B
 where A.CDId = B.CDId;



yes, but you want to know why an error is appearing, so you have to
show the code that is causing the error. The above is just the SQL
statement. How on earth can one decipher whether or not you have some
error in your code from that.

Did you print out the DBI err str? Did you wrap it in an eval and then
print out the reason it died? That would really help find the cause of
the error.

Else, you can always open up dbdimp.c and look at line 398. That
should set you in the right direction.


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
Kishor
Sent: Wednesday, February 07, 2007 5:39 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] What does this mean???

On 2/7/07, Anderson, James H (IT) <[EMAIL PROTECTED]>
wrote:
> not an error(21) at dbdimp.c line 398
>
> I'm using DBD::SQLite and got this error. What does it mean and how
best
> to hanle it?
>


well, how about some context? What is the structure of the table(s)
you are querying, what is the query, the relevant code snippet,
something to go on?

The error string itself means that you did something via Perl that
violated whatever it was at line 398 of the c program dbdimp.c.

--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.




--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] What does this mean???

[Clark Christensen]

What version of SQLite, and what version of DBD-SQLite?

I saw this predictably with DBD-SQLite 1.09 and SQLite 3.2.7 where I did 
something like:

$sth = $dbh->prepare("select foo, bar from mytable where rowid = ?");
for $i (1..5)  {
($myfoo, $mybar) = $dbh->selectrow_array($sth, undef, $i);
}

It would work for the first iteration, then raise a "not an error" error on the 
second.  It was annoying, but I usually just worked around it using 
$sth->execute/bind_columns/fetch inside the loop.

I updated to DBD-SQLite 1.13 with SQLite 3.3.12, and it worked like it's 
documented for DBI (fixed the problem).

 -Clark

- Original Message 
From: "Anderson, James H (IT)" <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, February 7, 2007 2:33:21 PM
Subject: [sqlite] What does this mean???

not an error(21) at dbdimp.c line 398

I'm using DBD::SQLite and got this error. What does it mean and how best
to hanle it?

Thanks,

jim


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] What does this mean???

[Anderson, James H \(IT\)]

Context :)

insert or replace into TMP_credDerivOrig
select
A.date,
A.CDId,
A.CDName,
A.CDTicket,
A.tradeId,
A.tapsAccount,
A.CDBook,
coalesce(A.CDFid,'') CDFid,
A.CDStatus,
A.CDTradeDate,
A.CDExpDate,
A.CDNotional,
A.CDCurr,
A.CDSellBuy,
A.CDType,
A.CDExerType,
A.CDEntity,
A.CDCusip,
A.CDSetlType,
A.CDCredInit,
A.CDSingleEntry,
A.CDMaterialType,
A.CDEffDate,
A.CDPremFreq,
A.CDPaymentType,
A.CDUpfrontSetl,
A.CDPublicInfo,
A.CDCollReq,
A.CDSpreadCurve,
A.CDPremium,
A.CDOptType,
A.CDAccrue,
A.CDRefPrice,
A.CDPremiumAmnt,
A.CDLastCoupLength,
A.CDWhoDelivers,
A.CDCollateralText,
A.CDFactorReason,
A.CDDefStartProt,
A.CDDefEndProt,
A.CDDefProtType,
B.CDComment,
A.CDEvent,
A.CDCurveType,
A.CrvShName,
A.CDRefEntity,
A.CDRefIndustry,
A.CDRefCountry,
A.CDRefSNP,
A.CDRefMoody,
A.CDRefMSRating,
A.CDRefId
  from TMP_credDerivOrig   A,
   C1_credDerivComment B
 where A.CDId = B.CDId;
rows affected: 566606 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
Kishor
Sent: Wednesday, February 07, 2007 5:39 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] What does this mean???

On 2/7/07, Anderson, James H (IT) <[EMAIL PROTECTED]>
wrote:
> not an error(21) at dbdimp.c line 398
>
> I'm using DBD::SQLite and got this error. What does it mean and how
best
> to hanle it?
>


well, how about some context? What is the structure of the table(s)
you are querying, what is the query, the relevant code snippet,
something to go on?

The error string itself means that you did something via Perl that
violated whatever it was at line 398 of the c program dbdimp.c.

-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

[sqlite] sqlite via jdbc on Mac OS X Tiger

[P Kishor]

volks,

I need to do some prototyping, heck, maybe even final-typing, using
(*shudder*) Java. Is there a jdbc driver for SQLite that works on Mac
OS X? Essentially, I need to pull in the data from the database into a
Java program which will then do its thang with it.


--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] What does this mean???

[P Kishor]

On 2/7/07, Anderson, James H (IT) <[EMAIL PROTECTED]> wrote:

not an error(21) at dbdimp.c line 398

I'm using DBD::SQLite and got this error. What does it mean and how best
to hanle it?




well, how about some context? What is the structure of the table(s)
you are querying, what is the query, the relevant code snippet,
something to go on?

The error string itself means that you did something via Perl that
violated whatever it was at line 398 of the c program dbdimp.c.

--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] What does this mean???

[Anderson, James H \(IT\)]

not an error(21) at dbdimp.c line 398

I'm using DBD::SQLite and got this error. What does it mean and how best
to hanle it?

Thanks,

jim


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

Re: [sqlite] ATTACH and :memory: databases

[Dennis Cote]

Dave Gierok wrote:
When I open the 2nd :memory: database and call attach on the first one specifying ':memory:' as the file name, 
  

Dave,

You can't do that. You need to attach the second memory database to the 
memory database you already have open. Each time you open or attach to 
:memory: you get a new completely independent memory database.


HTH
Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] how can i speed up inserts ?

[Dennis Cote]

DragonK wrote:



Well, using transactions would be a little difficult, because I have a
library which does something like logging to a database. Using 
transaction
on a single insert would be useless in my opinion, and on the other 
hand I

can't use transactions on the all inserts, since logging is a continuous
process, it doesn't end.


You can basically have a transaction open all the time. Begin a
transaction on startup. Insert your log entries as they happen. Execute
a commit and another begin after every 100 records, and commit when your
program ends. You can also get more sophisticated adding a timer that
will also do a commit and begin say one second after the last log entry
if you need to. You will get a speedup by nearly a factor of 100, and if
you have a power failure or OS crash you will only lose  the uncommitted
records, not your entire database.

HTH
Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Still Excel crash when running sqlite_get_table

[RB Smissaert]

When searching for this problem the first one I found was one from myself of
a few years back:
http://shorterlink.co.uk/6813
So, I think this is what the trouble is and nil to do with SQLite.

RBS


-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: 07 February 2007 21:37
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Still Excel crash when running sqlite_get_table

It looks this crash only happens if the data in one one array element
exceeds about 4000 characters or more precisely when the data in one
element (row and column) of the database exceeds about 4000 characters.

Maybe after all this is not a SQLite problem, but a VB problem and maybe
VB arrays can't handle this, making Excel crash. As SQLite can handle large
blob data it must be very unlikely indeed that SQLite is to blame.

Will see if there is anything known about this in VB/VBA.

RBS


-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: 07 February 2007 16:16
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Still Excel crash when running sqlite_get_table

The trouble is that after dropping some tables and doing a VACUUM to make
the file smaller the crash doesn't happen, despite that table still being
the same. I will attach the zipped db in any case although this particular
table (table called SQL) didn't actually cause a problem.

RBS

-Original Message-
From: Guy Hachlili [mailto:[EMAIL PROTECTED] 
Sent: 07 February 2007 15:40
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Still Excel crash when running sqlite_get_table

Hmmm...

At 13:38 2/7/2007 +, you wrote:
>Still having a problem when selecting data from one particular table with
>the VB wrapper dll SQLite3VB.dll.
>It is only a small table and I just can't see why there would be a problem.
>Would anybody be willing to have a look at this table?
>The zipped database file is only 15 Kb.

Nothing attached... can you post it somewhere for download?

Guy




-
To unsubscribe, send email to [EMAIL PROTECTED]

-





-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Still Excel crash when running sqlite_get_table

[RB Smissaert]

It looks this crash only happens if the data in one one array element
exceeds about 4000 characters or more precisely when the data in one
element (row and column) of the database exceeds about 4000 characters.

Maybe after all this is not a SQLite problem, but a VB problem and maybe
VB arrays can't handle this, making Excel crash. As SQLite can handle large
blob data it must be very unlikely indeed that SQLite is to blame.

Will see if there is anything known about this in VB/VBA.

RBS


-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: 07 February 2007 16:16
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Still Excel crash when running sqlite_get_table

The trouble is that after dropping some tables and doing a VACUUM to make
the file smaller the crash doesn't happen, despite that table still being
the same. I will attach the zipped db in any case although this particular
table (table called SQL) didn't actually cause a problem.

RBS

-Original Message-
From: Guy Hachlili [mailto:[EMAIL PROTECTED] 
Sent: 07 February 2007 15:40
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Still Excel crash when running sqlite_get_table

Hmmm...

At 13:38 2/7/2007 +, you wrote:
>Still having a problem when selecting data from one particular table with
>the VB wrapper dll SQLite3VB.dll.
>It is only a small table and I just can't see why there would be a problem.
>Would anybody be willing to have a look at this table?
>The zipped database file is only 15 Kb.

Nothing attached... can you post it somewhere for download?

Guy




-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Extension functions for SQLite in C for free

[Mikey C]

No problem, attached is the raw source code, no binaries.



Ralf Junker wrote:
> 
> Hello Mikey C,
> 
>>If anyone is having problems downloading the file (which is large as it
>>contains debug & release binaries and all the obj files), please email me
at
>>[EMAIL PROTECTED] and I'll email just the raw source code only.
> 
> Would it be possible to upload just the raw source code as a separate
> archive?
> 
> Ralf 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 
http://www.nabble.com/file/6303/SQLite.zip SQLite.zip 
-- 
View this message in context: 
http://www.nabble.com/Extension-functions-for-SQLite-in-C-for-free-tf3182921.html#a8854150
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[epankoke]

1. When the application first starts up, click on the Servers icon in the 
middle of the window.  Alternatively, select Tools | Server | Server Manager.

2. For Server Engine, select SQLite (of course).  If you're still using 2.x, go 
to step 4.

3. Select the options tab, put 3 in the first box for Version, then switch back 
to the Parameters tab.

4. Put some sort of description in for Server Name.  This is simply for you to 
identify the database when looking through the treeview list on the left hand 
side.

5. Click the ... button next to Database Filename and browse for your database. 
 Note, if the version you specified and the version of the database that you 
selected do not match (ex: you put 3 in the options tab and are only using a 
2.x database), DBTools will "create" the connection, but you'll never see 
anything listed in the server explorer.

6. Click OK.  The server explorer should be refreshed, and under Default 
Servers | Default Group, you should see a new node with the Server Name you 
specified.  When you expand that you will see a Databases folder, and expanding 
that will reveal the a node with the name of your database, assuming all went 
correctly.
--
Eric Pankoke
Founder / Lead Developer
Point Of Light Software
http://www.polsoftware.com/

 -- Original message --
From: "Rob Richardson" <[EMAIL PROTECTED]>
> For the life of me, I can't figure out how to open a database in
> DBManager.  I looked at it before, tried for an hour, and erased it.  I
> just tried again, with the same result.  If I can't figure out how to do
> such an easy thing, I can't trust the program.
> 
> RobR
> 
> 
> 
> -Original Message-
> From: COS [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, February 07, 2007 12:26 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Is there a SQLiteSpy-like thing that will let me
> change data from a grid?
> 
> Hi Rob,
> 
> You can try DBManager Standard or Enterprise Editions. The first one is
> free
> for personal use, the second is a commercial product. The complete list
> of
> features can be found at http://www.dbtools.com.br/EN/dbmanagerpro. You
> can
> edit your tables data and also manipulate images in blob fields.
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Extension functions for SQLite in C for free

[Ralf Junker]

Hello Mikey C,

>If anyone is having problems downloading the file (which is large as it
>contains debug & release binaries and all the obj files), please email me at
>[EMAIL PROTECTED] and I'll email just the raw source code only.

Would it be possible to upload just the raw source code as a separate archive?

Ralf 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[Ralf Junker]

Hello Nemanja Corlija,

>>PS: For anyone interested: The new SQLiteSpy with table cell editing is now 
>>available at http://www.yunqa.de/delphi/sqlitespy/
>Ralf, thanks SQLiteSpy. Download link is not working though.

Thanks for reporting the problem. The link is now fixed and working all right.

Regards,

Ralf 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[Kees Nuyt]

On Wed, 7 Feb 2007 13:03:09 -0500, you wrote:

>I can't get SQLiteExplorer to work with my databases.  It always gives
>me an "unknown file format" error.  I believe it's been quite a while
>since it was updated.  
>
>RobR

Did you download sqlite3explorer or sqliteexplorer? The latter
is meant for sqlite v2.x.x databases, and requires v2.x.x of
sqlite.dll and has not been maintained for a long time.

Make sure you use sqlite3explorer.exe v2.0 (which is built for
sqlite 3.x.x) and put your sqlite3.dll in the same directory.
If it fails to work on an existing database, try to create a new
one from sqlite3explorer.

I use it in combination with sqlite3.dll, version 3.3.12,
without any problem. I downloaded it 2006-03-13 and it still
works well.
-- 
  (  Kees Nuyt
  )
c[_]

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Extension functions for SQLite in C for free

[Mikey C]

No problem.

If anyone is having problems downloading the file (which is large as it
contains debug & release binaries and all the obj files), please email me at
[EMAIL PROTECTED] and I'll email just the raw source code only.

Mikey


Jay Sprenkle wrote:
> 
> Thanks for sharing MIkey.
> 
> On 2/6/07, Mikey C <[EMAIL PROTECTED]> wrote:
>>
>>
>> Hi,
>>
>> I've had these functions hanging around for some time.  They are not
>> fully
>> tested and come with no warranty of fitness, but if anyone wants the
>> code,
>> please take it.
>>
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Extension-functions-for-SQLite-in-C-for-free-tf3182921.html#a8853730
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[Nemanja Corlija]

On 2/7/07, Ralf Junker <[EMAIL PROTECTED]> wrote:

PS: For anyone interested: The new SQLiteSpy with table cell editing is now 
available at http://www.yunqa.de/delphi/sqlitespy/


Ralf, thanks SQLiteSpy. Download link is not working though.

--
Nemanja Corlija <[EMAIL PROTECTED]>

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[Ralf Junker]

Hello Robert Simpson,

>For the SQLite ADO.NET wrapper, I actually wrote two functions into the core
>engine to help me retrieve key information.  My requirements were a little
>more complex than just getting rowid, but here's what I did to get me that
>far at least:
>
>Given a sqlite_stmt pointer consisting of a SELECT on one or more tables,
>  1.  For each table, fetch the internal cursor for that table in the
>statement
>  2.  For each cursor, fetch the rowid
>
>That was the API modification part.  Caveats are that this information is
>not always available.  So if its not, I just return a failure indicator.

Thanks for backing me up on my problem. However, unlike you I am very reluctant 
to hack the SQLite because of the concerns you mentioned. I would love to see 
something similar to what you have done officially supported by SQLite.

>The next part involved querying the schema of each table, looking up indexes 
>and finding the most logical primary key definitions for the table(s) involved 
>in the select, and compiling a side-by-side SELECT clause returning the user's 
>defined primary keys using a WHERE clause with the above fetched rowid's.

For cell updates, SQLiteSpy would be happy to use the RowID index, which is 
auto-generated and always available. Hence a function like 
sqlite3_column_rowid() would just fill the gap perfectly.

Let's see what the future brings ...

Regards,

Ralf

PS: For anyone interested: The new SQLiteSpy with table cell editing is now 
available at http://www.yunqa.de/delphi/sqlitespy/ 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] how can i speed up inserts ?

[DragonK]

On 2/7/07, John Stanton <[EMAIL PROTECTED]> wrote:


DragonK wrote:
> On 2/7/07, Ken <[EMAIL PROTECTED]> wrote:
>
>>
>> Try without the pragma and wrap the inserts with a begin transaction
>> and a
>> commit...
>>
>>   The performance will be almost as good as with the pragma, with the
>> added benefit of consistent data and no corruption in the event of a
>> crash
>> or power failure.
>
>
>
> I know, but in my case, I can't use transactions due to the architecture
of
> the product.

Why not?  Can't you just execute SQL?



Well, using transactions would be a little difficult, because I have a
library which does something like logging to a database. Using transaction
on a single insert would be useless in my opinion, and on the other hand I
can't use transactions on the all inserts, since logging is a continuous
process, it doesn't end.





>
>
> DragonK <[EMAIL PROTECTED]> wrote:
>
>>   On 2/7/07, Teg wrote:
>> >
>> > Hello ohadp,
>> >
>> > Well, my experience is, particularly when it's users using it and not
>> > a personal project, that corruption happens fairly frequently when
you
>> > use this pragma. That's why I don't use it any more in my production
>> > code.
>> >
>> > Transactions are far safer and fast too.
>> >
>> > Indeed, transactions are safer.
>>
>> But I wonder, why did you experienced corruption with this pragma? Was
it
>> because of crashes of the OS or the application? Or are there other
>> factors
>> which can corrupt the data if not syncing ? As I understood from the
>> documentation, the only thing that can corrupt data when using this
>> pragma
>> are crashes and power failures.
>>
>>
>>
>> --
>> ...it's only a matter of time...
>>
>>
>
>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-





--
...it's only a matter of time...

Re[4]: [sqlite] how can i speed up inserts ?

[Teg]

Hello DragonK,

Essentially, when you have 1000's of uses banging on your software,
many with poorly maintained/infected machines things that seldom
happen on well maintained PC's will happen all the time. Some of my
users have daily Windows crashes and assume that it's a normal
experience for windows users (it's always a hardware or driver problem
any more though). Weekly, I get emails from people telling me that my
software is crashing their PC's when you all know that's impossible.
Typically, it ends up being bad RAM.

Losing data was one problem, the other was, once the DB becomes
corrupt, it needs to be deleted before you can use it again. Vacuum
may be better these days but, back when I ran into this, it wouldn't
always fix the file.

All it takes is for a user to kill the software with task manager and
you may end up with a bad DB. On my personal PC, which doesn't crash
and has a UPS, corruption wasn't an issue.



C


Wednesday, February 7, 2007, 12:36:10 PM, you wrote:

D> On 2/7/07, Teg <[EMAIL PROTECTED]> wrote:
>>
>> Hello ohadp,
>>
>> Well, my experience is, particularly when it's users using it and not
>> a personal project, that corruption happens fairly frequently when you
>> use this pragma. That's why I don't use it any more in my production
>> code.
>>
>> Transactions are far safer and fast too.
>>
>> Indeed, transactions are safer.

D> But I wonder, why did you experienced corruption with this pragma? Was it
D> because of crashes of the OS or the application? Or are there other factors
D> which can corrupt the data if not syncing ? As I understood from the
D> documentation, the only thing that can corrupt data when using this pragma
D> are crashes and power failures.






-- 
Best regards,
 Tegmailto:[EMAIL PROTECTED]


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] how can i speed up inserts ?

[John Stanton]

DragonK wrote:

On 2/7/07, Ken <[EMAIL PROTECTED]> wrote:



Try without the pragma and wrap the inserts with a begin transaction 
and a

commit...

  The performance will be almost as good as with the pragma, with the
added benefit of consistent data and no corruption in the event of a 
crash

or power failure.




I know, but in my case, I can't use transactions due to the architecture of
the product.


Why not?  Can't you just execute SQL?




DragonK <[EMAIL PROTECTED]> wrote:


  On 2/7/07, Teg wrote:
>
> Hello ohadp,
>
> Well, my experience is, particularly when it's users using it and not
> a personal project, that corruption happens fairly frequently when you
> use this pragma. That's why I don't use it any more in my production
> code.
>
> Transactions are far safer and fast too.
>
> Indeed, transactions are safer.

But I wonder, why did you experienced corruption with this pragma? Was it
because of crashes of the OS or the application? Or are there other
factors
which can corrupt the data if not syncing ? As I understood from the
documentation, the only thing that can corrupt data when using this 
pragma

are crashes and power failures.



--
...it's only a matter of time...








-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] ATTACH and :memory: databases

[Dave Gierok]

I have one :memory: database open and I want to make a copy of it into another 
:memory: database.  When I open the 2nd :memory: database and call attach on 
the first one specifying ':memory:' as the file name, it allows me to do this.  
However when I loop through the tables copying them into the 2nd one, it is 
still empty after this has been done.  I think the first :memory: database 
might be attaching to, uh, itself.

Is there a way to specify the name of a memory database uniquely?  Something 
like ':memory:2'?  Nothing that I've tried works.

-Dave Gierok

-Original Message-
From: Dan Kennedy [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 07, 2007 9:56 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] ATTACH and :memory: databases

On Wed, 2007-02-07 at 09:37 -0800, Dave Gierok wrote:
> I would like to attach a :memory: database to another :memory: database.  How 
> is this possible since the ATTACH command requires the database's file name?
>
> ATTACH [DATABASE] database-filename AS database-name
>
> -Dave Gierok

I don't think there are any special issues. Use ':memory:' as the
filename.

[EMAIL PROTECTED]:~> sqlite3 :memory:
SQLite version 3.2.8
Enter ".help" for instructions
sqlite> attach ':memory:' as db2;
sqlite> attach ':memory:' as db3;
sqlite> create table db2.abc(a, b, c);
sqlite> select * from sqlite_master;
sqlite> select * from db3.sqlite_master;
sqlite> select * from db2.sqlite_master;
table|abc|abc|2|CREATE TABLE abc(a, b, c)
sqlite>



-
To unsubscribe, send email to [EMAIL PROTECTED]
-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[Rob Richardson]

For the life of me, I can't figure out how to open a database in
DBManager.  I looked at it before, tried for an hour, and erased it.  I
just tried again, with the same result.  If I can't figure out how to do
such an easy thing, I can't trust the program.

RobR



-Original Message-
From: COS [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, February 07, 2007 12:26 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is there a SQLiteSpy-like thing that will let me
change data from a grid?

Hi Rob,

You can try DBManager Standard or Enterprise Editions. The first one is
free
for personal use, the second is a commercial product. The complete list
of
features can be found at http://www.dbtools.com.br/EN/dbmanagerpro. You
can
edit your tables data and also manipulate images in blob fields.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: ATTACH and :memory: databases

[Igor Tandetnik]

Dave Gierok <[EMAIL PROTECTED]> wrote:

I would like to attach a :memory: database to another :memory:
database.  How is this possible


As far as I can tell, this is not possible. Why would you want to? What 
are you trying to achieve?


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: Re[2]: [sqlite] how can i speed up inserts ?

[Guillaume Fougnies]

Wed, Feb 07, 2007 at 07:36:10PM +0200: DragonK wrote:
> On 2/7/07, Teg <[EMAIL PROTECTED]> wrote:
> >
> >Hello ohadp,
> >
> >Well, my experience is, particularly when it's users using it and not
> >a personal project, that corruption happens fairly frequently when you
> >use this pragma. That's why I don't use it any more in my production
> >code.
> >
> >Transactions are far safer and fast too.
> >
> >Indeed, transactions are safer.
> 
> But I wonder, why did you experienced corruption with this pragma? Was it
> because of crashes of the OS or the application? Or are there other factors
> which can corrupt the data if not syncing ? As I understood from the
> documentation, the only thing that can corrupt data when using this pragma
> are crashes and power failures.

Hello,

If you don't care of corruption on crashes and power failures,
you can modify sqlite to write your temporary '-journal' files
in a ramfs mount directory.

DB file: /opt/sqlite/mydb
Journal in smtg like: /ramfs/opt#sqlite#mydb-journal

--
Guillaume FOUGNIES
Eulerian Technologies

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] ATTACH and :memory: databases

[Dan Kennedy]

On Wed, 2007-02-07 at 09:37 -0800, Dave Gierok wrote:
> I would like to attach a :memory: database to another :memory: database.  How 
> is this possible since the ATTACH command requires the database's file name?
> 
> ATTACH [DATABASE] database-filename AS database-name
> 
> -Dave Gierok

I don't think there are any special issues. Use ':memory:' as the
filename.

[EMAIL PROTECTED]:~> sqlite3 :memory:
SQLite version 3.2.8
Enter ".help" for instructions
sqlite> attach ':memory:' as db2;
sqlite> attach ':memory:' as db3;
sqlite> create table db2.abc(a, b, c);
sqlite> select * from sqlite_master;
sqlite> select * from db3.sqlite_master;
sqlite> select * from db2.sqlite_master;
table|abc|abc|2|CREATE TABLE abc(a, b, c)
sqlite> 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[Rob Richardson]

I can't get SQLiteExplorer to work with my databases.  It always gives
me an "unknown file format" error.  I believe it's been quite a while
since it was updated.  

RobR


-Original Message-
From: Griggs, Donald [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 06, 2007 3:14 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Is there a SQLiteSpy-like thing that will let me
change data from a grid?

Regarding:  "If I want to change data ."

Sqlite3Explorer is free software, and works as you describe.  I imagine
there are several others.

http://www.singular.gr/sqlite/  (Not sure if there will be future
releases of this or not) 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Busy timeout and prepare statements

[Igor Tandetnik]

McDermott, Andrew
<[EMAIL PROTECTED]> wrote:

I'm seeking some clarification regarding set_busy_timeout().  If I set
this to some positive value does this work when using prepared
statements (prepare, step, reset) or only when using sqlite3_exec().


Busy timeout does apply to sqlite3_step (prepare and reset cannot 
encounter busy state).


Note that sqlite3_exec is implemented in terms of sqlite3_step et al.

Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Still Excel crash when running sqlite_get_table

[RB Smissaert]

Thanks for that.
I am not familiar with compiling C code and haven't compiled SQLite yet.
Could you send me your dll so I could try that?
Database seems fine to me as well, but I definitely have trouble with only
that particular table.

RBS

-Original Message-
From: Guy Hachlili [mailto:[EMAIL PROTECTED] 
Sent: 07 February 2007 17:22
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Still Excel crash when running sqlite_get_table


At 17:04 2/7/2007 +, you wrote:
>Not sure now the zip file has come through to this forum.
>I can see it, but I also got a message that it wasn't allowed.

Didn't get through the first time, did get through the second time.

I tried my version of the VBSqlite3 DLL and it works, although I didn't try 
it with Excel but with VB6.
I did make some changes to my DLL code - if you want to compile that, I can 
post them.

The database itself seems OK.


Guy




-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: Re[2]: [sqlite] how can i speed up inserts ?

[DragonK]

On 2/7/07, Ken <[EMAIL PROTECTED]> wrote:


Try without the pragma and wrap the inserts with a begin transaction and a
commit...

  The performance will be almost as good as with the pragma, with the
added benefit of consistent data and no corruption in the event of a crash
or power failure.



I know, but in my case, I can't use transactions due to the architecture of
the product.



DragonK <[EMAIL PROTECTED]> wrote:

  On 2/7/07, Teg wrote:
>
> Hello ohadp,
>
> Well, my experience is, particularly when it's users using it and not
> a personal project, that corruption happens fairly frequently when you
> use this pragma. That's why I don't use it any more in my production
> code.
>
> Transactions are far safer and fast too.
>
> Indeed, transactions are safer.

But I wonder, why did you experienced corruption with this pragma? Was it
because of crashes of the OS or the application? Or are there other
factors
which can corrupt the data if not syncing ? As I understood from the
documentation, the only thing that can corrupt data when using this pragma
are crashes and power failures.



--
...it's only a matter of time...





--
...it's only a matter of time...

Re: Re[2]: [sqlite] how can i speed up inserts ?

[Ken]

Try without the pragma and wrap the inserts with a begin transaction and a 
commit...
   
  The performance will be almost as good as with the pragma, with the added 
benefit of consistent data and no corruption in the event of a crash or power 
failure.
   
  

DragonK <[EMAIL PROTECTED]> wrote:
  On 2/7/07, Teg wrote:
>
> Hello ohadp,
>
> Well, my experience is, particularly when it's users using it and not
> a personal project, that corruption happens fairly frequently when you
> use this pragma. That's why I don't use it any more in my production
> code.
>
> Transactions are far safer and fast too.
>
> Indeed, transactions are safer.

But I wonder, why did you experienced corruption with this pragma? Was it
because of crashes of the OS or the application? Or are there other factors
which can corrupt the data if not syncing ? As I understood from the
documentation, the only thing that can corrupt data when using this pragma
are crashes and power failures.



-- 
...it's only a matter of time...

[sqlite] Busy timeout and prepare statements

[McDermott, Andrew]

Hi,

I'm seeking some clarification regarding set_busy_timeout().  If I set
this to some positive value does this work when using prepared
statements (prepare, step, reset) or only when using sqlite3_exec().

Thanks.

[sqlite] ATTACH and :memory: databases

[Dave Gierok]

I would like to attach a :memory: database to another :memory: database.  How 
is this possible since the ATTACH command requires the database's file name?

ATTACH [DATABASE] database-filename AS database-name

-Dave Gierok

Re: Re[2]: [sqlite] how can i speed up inserts ?

[DragonK]

On 2/7/07, Teg <[EMAIL PROTECTED]> wrote:


Hello ohadp,

Well, my experience is, particularly when it's users using it and not
a personal project, that corruption happens fairly frequently when you
use this pragma. That's why I don't use it any more in my production
code.

Transactions are far safer and fast too.

Indeed, transactions are safer.


But I wonder, why did you experienced corruption with this pragma? Was it
because of crashes of the OS or the application? Or are there other factors
which can corrupt the data if not syncing ? As I understood from the
documentation, the only thing that can corrupt data when using this pragma
are crashes and power failures.



--
...it's only a matter of time...

Re: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[COS]

Hi Rob,

You can try DBManager Standard or Enterprise Editions. The first one is free
for personal use, the second is a commercial product. The complete list of
features can be found at http://www.dbtools.com.br/EN/dbmanagerpro. You can
edit your tables data and also manipulate images in blob fields.

Another application from the same company is QueryIT. THis one is intended
to help people navigate through table records, which also includes master
detail records, generate charts, work with blob fields (show images).
Although this is a commercial product is very cheap in its category. For
more details see http://www.dbtools.com.br/EN/queryit.

Best Regards,

COS


- Original Message - 
From: "Rob Richardson" <[EMAIL PROTECTED]>
To: 
Sent: Tuesday, February 06, 2007 4:49 PM
Subject: [sqlite] Is there a SQLiteSpy-like thing that will let me change
data from a grid?


Greetings!

The only tool I have for examining and manipulating SQLite databases is
SQLiteSpy.  If I want to change data in that program, I have to write an
SQL statement.  Every other database editor I've seen lets a use do
simple things from a grid.  Open a table and data is loaded into a grid,
click on a cell and type in a value and it gets written to the table,
select a row and click a Delete button and the row is deleted from the
table, and so on.  There must be a tool somewhere that will do that for
a SQLite database, or there's some feature of SQLiteSpy I don't know
about.  Can somebody please point me to one or the other?

Thank you very much.

Rob Richardson


-
To unsubscribe, send email to [EMAIL PROTECTED]

-



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Still Excel crash when running sqlite_get_table

[Guy Hachlili]

At 17:04 2/7/2007 +, you wrote:

Not sure now the zip file has come through to this forum.
I can see it, but I also got a message that it wasn't allowed.


Didn't get through the first time, did get through the second time.

I tried my version of the VBSqlite3 DLL and it works, although I didn't try 
it with Excel but with VB6.
I did make some changes to my DLL code - if you want to compile that, I can 
post them.


The database itself seems OK.


Guy



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] how can i speed up inserts ?

[Michael Schlenker]

Dennis Cote schrieb:

ohadp wrote:

holy smokes that pragma just made my several hundred inserts take half a
second compared to 30 seconds earlier.

thanks!

  

Yes, but now your database can be corrupted by a power failure or OS crash.

Wrapping several hundred inserts in a transaction will be nearly as fast 
and completely safe.


What happens if one uses the pragma and transactions to do bulk inserts, 
would that be efficient or simple useless premature optimization?


I my scenario i create test databases from scratch by bulk inserts, so 
if things blow up there it doesn't hurt because i have to scrap 
everything anyway in that case.


Michael

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] how can i speed up inserts ?

[Gerry Snyder]

ohadp wrote:

luckily this isn't mission critical failure, i'll take the risk that one out
of ten thousand users experiences database corruption.


Only you can judge how important your data are.


i can only batch the inserts into maybe 4-5 a time, don't know how much of
an improvement that will be...


That should speed things up by a factor of about 4 to 5. If you do not 
use explicit transactions, SQLite (at least in effect) does one 
implicitly per insert. The time per transaction will not go up a whole 
lot unless you do _many_ inserts in each one.


If you really can't get more than 4-5 in a batch, it's your call whether 
the time saving is worth the risk. BTW, the problem rate should be much 
less than one in ten thousand.


Gerry

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Still Excel crash when running sqlite_get_table

[RB Smissaert]

Not sure now the zip file has come through to this forum.
I can see it, but I also got a message that it wasn't allowed.

RBS

-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED] 
Sent: 07 February 2007 16:19
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Still Excel crash when running sqlite_get_table

Ah, now after closing Excel and reopening it and doing the select again on
that same table it now does crash Excel. This is the same table I sent in
the zipped db.

RBS

-Original Message-
From: Guy Hachlili [mailto:[EMAIL PROTECTED] 
Sent: 07 February 2007 15:40
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Still Excel crash when running sqlite_get_table

Hmmm...

At 13:38 2/7/2007 +, you wrote:
>Still having a problem when selecting data from one particular table with
>the VB wrapper dll SQLite3VB.dll.
>It is only a small table and I just can't see why there would be a problem.
>Would anybody be willing to have a look at this table?
>The zipped database file is only 15 Kb.

Nothing attached... can you post it somewhere for download?

Guy




-
To unsubscribe, send email to [EMAIL PROTECTED]

-





-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re[2]: [sqlite] how can i speed up inserts ?

[Teg]

Hello ohadp,

Well, my experience is, particularly when it's users using it and not
a personal project, that corruption happens fairly frequently when you
use this pragma. That's why I don't use it any more in my production
code.

Transactions are far safer and fast too.

C

Wednesday, February 7, 2007, 11:39:26 AM, you wrote:

o> luckily this isn't mission critical failure, i'll take the risk that one out
o> of ten thousand users experiences database corruption.

o> i can only batch the inserts into maybe 4-5 a time, don't know how much of
o> an improvement that will be...


o> Dennis Cote wrote:
>> 
>> ohadp wrote:
>>> holy smokes that pragma just made my several hundred inserts take half a
>>> second compared to 30 seconds earlier.
>>>
>>> thanks!
>>>
>>>   
>> Yes, but now your database can be corrupted by a power failure or OS
>> crash.
>> 
>> Wrapping several hundred inserts in a transaction will be nearly as fast 
>> and completely safe.
>> 
>> HTH
>> Dennis Cote
>> 
>> -
>> To unsubscribe, send email to [EMAIL PROTECTED]
>> -
>> 
>> 
>> 




-- 
Best regards,
 Tegmailto:[EMAIL PROTECTED]


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] how can i speed up inserts ?

[John Stanton]

Try it.  You might find that it executes in a similar time and it will 
still be ACID and safe.


ohadp wrote:

luckily this isn't mission critical failure, i'll take the risk that one out
of ten thousand users experiences database corruption.

i can only batch the inserts into maybe 4-5 a time, don't know how much of
an improvement that will be...


Dennis Cote wrote:


ohadp wrote:


holy smokes that pragma just made my several hundred inserts take half a
second compared to 30 seconds earlier.

thanks!

 


Yes, but now your database can be corrupted by a power failure or OS
crash.

Wrapping several hundred inserts in a transaction will be nearly as fast 
and completely safe.


HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-









-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] how can i speed up inserts ?

[ohadp]

luckily this isn't mission critical failure, i'll take the risk that one out
of ten thousand users experiences database corruption.

i can only batch the inserts into maybe 4-5 a time, don't know how much of
an improvement that will be...


Dennis Cote wrote:
> 
> ohadp wrote:
>> holy smokes that pragma just made my several hundred inserts take half a
>> second compared to 30 seconds earlier.
>>
>> thanks!
>>
>>   
> Yes, but now your database can be corrupted by a power failure or OS
> crash.
> 
> Wrapping several hundred inserts in a transaction will be nearly as fast 
> and completely safe.
> 
> HTH
> Dennis Cote
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 

-- 
View this message in context: 
http://www.nabble.com/how-can-i-speed-up-inserts---tf3186848.html#a8849056
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Still Excel crash when running sqlite_get_table

[RB Smissaert]

Ah, now after closing Excel and reopening it and doing the select again on
that same table it now does crash Excel. This is the same table I sent in
the zipped db.

RBS

-Original Message-
From: Guy Hachlili [mailto:[EMAIL PROTECTED] 
Sent: 07 February 2007 15:40
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Still Excel crash when running sqlite_get_table

Hmmm...

At 13:38 2/7/2007 +, you wrote:
>Still having a problem when selecting data from one particular table with
>the VB wrapper dll SQLite3VB.dll.
>It is only a small table and I just can't see why there would be a problem.
>Would anybody be willing to have a look at this table?
>The zipped database file is only 15 Kb.

Nothing attached... can you post it somewhere for download?

Guy




-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Still Excel crash when running sqlite_get_table

[RB Smissaert]

Wasn't sure if it was allowed to post attachments to this forum.
I will see if I can reproduce this and send the zipped db to here.

RBS

-Original Message-
From: Guy Hachlili [mailto:[EMAIL PROTECTED] 
Sent: 07 February 2007 15:40
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Still Excel crash when running sqlite_get_table

Hmmm...

At 13:38 2/7/2007 +, you wrote:
>Still having a problem when selecting data from one particular table with
>the VB wrapper dll SQLite3VB.dll.
>It is only a small table and I just can't see why there would be a problem.
>Would anybody be willing to have a look at this table?
>The zipped database file is only 15 Kb.

Nothing attached... can you post it somewhere for download?

Guy




-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Still Excel crash when running sqlite_get_table

[Guy Hachlili]

Hmmm...

At 13:38 2/7/2007 +, you wrote:

Still having a problem when selecting data from one particular table with
the VB wrapper dll SQLite3VB.dll.
It is only a small table and I just can't see why there would be a problem.
Would anybody be willing to have a look at this table?
The zipped database file is only 15 Kb.


Nothing attached... can you post it somewhere for download?

Guy



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[Robert Simpson]

> -Original Message-
> From: Robert Simpson [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, February 07, 2007 8:25 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Is there a SQLiteSpy-like thing that will let me
> change data from a grid?
> 

> __declspec(dllexport) int WINAPI sqlite3_cursor_rowid(sqlite3_stmt
> *pstmt,
> int cursor, sqlite_int64 *prowid)

Oops I forgot to de-decorate this call ... just pretend.

And I also neglected to mention that the input statement must have already
been started with a previous call to sqlite3_step() -- as these functions
will return the rowid information for the row you've just stepped into.

Robert




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[Robert Simpson]

For the SQLite ADO.NET wrapper, I actually wrote two functions into the core
engine to help me retrieve key information.  My requirements were a little
more complex than just getting rowid, but here's what I did to get me that
far at least:

Given a sqlite_stmt pointer consisting of a SELECT on one or more tables,
  1.  For each table, fetch the internal cursor for that table in the
statement
  2.  For each cursor, fetch the rowid

That was the API modification part.  Caveats are that this information is
not always available.  So if its not, I just return a failure indicator.

The next part involved querying the schema of each table, looking up indexes
and finding the most logical primary key definitions for the table(s)
involved in the select, and compiling a side-by-side SELECT clause returning
the user's defined primary keys using a WHERE clause with the above fetched
rowid's.

So given these table definitions:
CREATE TABLE Foo (MyID INTEGER, BarID INTEGER, MyValue, CONSTRAINT pk
PRIMARY KEY(MyId, MyKey));
CREATE TABLE Bar (BarID INTEGER PRIMARY KEY, MyBarValue);

And this SELECT clause:
SELECT MyValue, MyBarValue FROM Foo INNER JOIN Bar ON Foo.BarID = Bar.BarID;

I could then construct a hidden side-by-side query, looking like:
SELECT MyID, BarID FROM Foo WHERE ROWID = ?
(I already had the rowid from the Bar table without a sub-query using the
API calls I made earlier)

All this is wrapped up inside the SQLiteDataReader when you specify a
KeyInfo flag to open the reader on the command.

As for the API extensions ...

The only reason I never posted it and wasn't sure of its suitability is that
I've only ever tested it the way I use it and dunno how stable it is for
general purpose usage.  Also, the initial function call to
sqlite_table_cursor requires the database index and root page number which
can only be obtained by previous fetches into the database schema.

Here's the code at any rate:

/*
[in] pstmt -- The statement to fetch a cursor on
[in] iDb   -- The database index (id from a previous call to
PRAMGA database_list)
[in] tableRootPage -- Root page of the table, (fetched from
sqlite_master table)

   For a given row-returning statement which may contain multiple cursors,
return the index of
   the cursor responsible for iterating the specified table.  You must
specify the root page of that
   table and the database it's in to get a match.

   RETURNS
   -1 on failure, or a 0-based index of the cursor for the given table
*/
int sqlite3_table_cursor(sqlite3_stmt *pstmt, int iDb, Pgno tableRootPage)
{
  Vdbe *p = (Vdbe *)pstmt;
  int n;

  for (n = 0; n < p->nCursor && p->apCsr[n] != NULL; n++)
  {
if (p->apCsr[n]->isTable == FALSE) continue;
if (p->apCsr[n]->iDb != iDb) continue;
if (p->apCsr[n]->pCursor->pgnoRoot == tableRootPage)
  return n;
  }
  return -1;
}

/*
[in] pstmt   -- A row-returning SQLite statement
[in] cursor  -- A cursor previously returned from sqlite3_table_cursor()
[out] prowid -- The rowid for the specified cursor

Fetches (if possible) a rowid for a given cursor in a row-returning
statement.

RETURNS
0 on success, or one of the SQLite error codes otherwise
*/
__declspec(dllexport) int WINAPI sqlite3_cursor_rowid(sqlite3_stmt *pstmt,
int cursor, sqlite_int64 *prowid)
{
  Vdbe *p = (Vdbe *)pstmt;
  int rc = 0;
  Cursor *pC;

  if (cursor < 0 || cursor >= p->nCursor) return SQLITE_ERROR;
  if (p->apCsr[cursor] == NULL) return SQLITE_ERROR;
  pC = p->apCsr[cursor];

  rc = sqlite3VdbeCursorMoveto(pC);
  if( rc ) return rc;

  if( pC->rowidIsValid )
  {
*prowid = pC->lastRowid;
  }
  else if(pC->pseudoTable )
  {
*prowid = keyToInt(pC->iKey);
  }
  else if(pC->nullRow || pC->pCursor==0)
  {
return SQLITE_ERROR;
  }
  else
  {
if (pC->pCursor == NULL) return SQLITE_ERROR;
sqlite3BtreeKeySize(pC->pCursor, prowid);
*prowid = keyToInt(*prowid);
  }
  return 0;
}





> -Original Message-
> From: Ralf Junker [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, February 07, 2007 4:23 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Is there a SQLiteSpy-like thing that will let me
> change data from a grid?
> 
> 
> >> The SQLite library lacks certain API required to implement cell
> >> editing: Most notably, there is no functionality to retrieve
> >> the exact origin (table, row and column) of a cell, which must
> >> be known to store back the modified value.
> >
> >See:
> >
> >   http://www.sqlite.org/capi3ref.html#sqlite3_column_database_name
> >   http://www.sqlite.org/capi3ref.html#sqlite3_column_origin_name
> >   http://www.sqlite.org/capi3ref.html#sqlite3_column_table_name
> 
> I know, and these functions were much appreciated when they entered
> SQLite. However, they do not cover the table row (RowID). Here is the
> discussion about what's still missing. The thread also points out a few
> inconsistencies in the implementation of the above functions, IIRC:
> 
>   

Re: [sqlite] Select and deletion

[DragonK]

On 2/7/07, anis chaaba <[EMAIL PROTECTED]> wrote:


with which language are you trying to do this?
to execute the delete statement you can use transactions to ensure
atomicity.
regards,



I'm using a C++ wrapper over sqlite.

The code i'm running is the following:

   SQLiteWrapper w;
   w.Open("test.db");
   w.DirectStatement("CREATE TABLE asd(ID INTEGER, A INTEGER, B
TEXT);");
   w.DirectStatement("PRAGMA synchronous = OFF;");

   char buf[1024];

   for (int i = 0 ; i < 1000; i++)
   {
   sprintf(buf,"INSERT INTO asd VALUES(%d, %d, 'asd')", i,
rand() % 6 );
   w.DirectStatement(buf);
   }

   // See how many rows with int == 2 we have
   SQLiteStatement* s = w.Statement("SELECT COUNT(*) FROM asd WHERE
A=2;");

   s->NextRow();
   int cnt = s->ValueInt(0);
   fprintf(stdout,"\nCount=%d", cnt);
   delete s;

   int cnt_real = 0;

   s = w.Statement("SELECT ID FROM asd WHERE A=2;");

   while (s->NextRow())
   {
   int id = s->ValueInt(0);
   char buff[256];
   sprintf(buff,"DELETE FROM asd WHERE ID=%d", id);

   if ( w.DirectStatement(buff) )
   {
   cnt_real++;
   }
   else
   {
   fprintf(stderr,"\nError...");
   }
   }

   delete s;

   fprintf(stdout,"\nDeleted count=%d\n", cnt_real);

   w.Close();

This seems to work... but I'm not sure if it should, that's why I'm asking.
I hope the code will show more exactly what I intend to do. Sorry for the
lack of proper error checking, it's just a test program. :) Also, I'm not
using any transactions, just the PRAGMA synchronous= off;




--
...it's only a matter of time...

[sqlite] Still Excel crash when running sqlite_get_table

[RB Smissaert]

Still having a problem when selecting data from one particular table with
the VB wrapper dll SQLite3VB.dll.
It is only a small table and I just can't see why there would be a problem.
Would anybody be willing to have a look at this table?
The zipped database file is only 15 Kb.
Thanks.

RBS



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Select and deletion

[Jay Sprenkle]

On 2/7/07, DragonK <[EMAIL PROTECTED]> wrote:


Hello everybody,

I'm wondering if the following scenario would work properly:

Run a SELECT query on the database and in a loop retrieve each result;
for each retrieved result, inside the same loop, execute a DELETE
statement
for the previously extracted row.

I need this in order to retrieve a list of filenames from the database and
delete
each file from the hard disk and from the database also and I'm trying to
avoid
creating a temporary list for storing the file names.



I think it won't. Try selecting the list into memory or a temp table, then
using that to delete in a separate step.

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

Re: [sqlite] Select and deletion

[anis chaaba]

with which language are you trying to do this?
to execute the delete statement you can use transactions to ensure
atomicity.
regards,

Re: [sqlite] how can i speed up inserts ?

[ohadp]

holy smokes that pragma just made my several hundred inserts take half a
second compared to 30 seconds earlier.

thanks!



DragonK wrote:
> 
> On 2/7/07, Ohad Eder-Pressman <[EMAIL PROTECTED]> wrote:
>>
>> i've got sqlite compiled with visual-studio, with all the default
>> options,
>> didn't touch anything.
>> inserts are just a bit too slow for me, is there a good way to speed this
>> up
>> using some flags ?
>> any other flags that it would be smart to turn on for some better
>> performance ?
> 
> 
> 
> Try using transactions for an insert and turning off synchronization via
> Pragma synchronize.
> 
> See here: http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html
> 
> 
> thanks
>>
> 
> 
> 
> -- 
> ...it's only a matter of time...
> 
> 

-- 
View this message in context: 
http://www.nabble.com/how-can-i-speed-up-inserts---tf3186848.html#a8845264
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] how can i speed up inserts ?

[DragonK]

On 2/7/07, Ohad Eder-Pressman <[EMAIL PROTECTED]> wrote:


i've got sqlite compiled with visual-studio, with all the default options,
didn't touch anything.
inserts are just a bit too slow for me, is there a good way to speed this
up
using some flags ?
any other flags that it would be smart to turn on for some better
performance ?




Try using transactions for an insert and turning off synchronization via
Pragma synchronize.

See here: http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html


thanks






--
...it's only a matter of time...

[sqlite] how can i speed up inserts ?

[Ohad Eder-Pressman]

i've got sqlite compiled with visual-studio, with all the default options,
didn't touch anything.
inserts are just a bit too slow for me, is there a good way to speed this up
using some flags ?
any other flags that it would be smart to turn on for some better
performance ?

thanks

Re: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[Ralf Junker]

>> The SQLite library lacks certain API required to implement cell
>> editing: Most notably, there is no functionality to retrieve 
>> the exact origin (table, row and column) of a cell, which must
>> be known to store back the modified value.
>
>See:
>
>   http://www.sqlite.org/capi3ref.html#sqlite3_column_database_name
>   http://www.sqlite.org/capi3ref.html#sqlite3_column_origin_name
>   http://www.sqlite.org/capi3ref.html#sqlite3_column_table_name

I know, and these functions were much appreciated when they entered SQLite. 
However, they do not cover the table row (RowID). Here is the discussion about 
what's still missing. The thread also points out a few inconsistencies in the 
implementation of the above functions, IIRC:

  http://thread.gmane.org/gmane.comp.db.sqlite.general/19323

Ralf 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[drh]

Ralf Junker <[EMAIL PROTECTED]> wrote:
> 
> The SQLite library lacks certain API required to implement cell
> editing: Most notably, there is no functionality to retrieve 
> the exact origin (table, row and column) of a cell, which must
> be known to store back the modified value.

See:

   http://www.sqlite.org/capi3ref.html#sqlite3_column_database_name
   http://www.sqlite.org/capi3ref.html#sqlite3_column_origin_name
   http://www.sqlite.org/capi3ref.html#sqlite3_column_table_name

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[Ralf Junker]

Hello Rob Richardson,

>The only tool I have for examining and manipulating SQLite databases is
>SQLiteSpy.  If I want to change data in that program, I have to write an
>SQL statement.  Every other database editor I've seen lets a use do
>simple things from a grid.

As the author of SQLiteSpy: There are indeed DB managers which _allow_ cell 
editing for all SQL SELECTs. Some are able to commit changes to single table 
SELECTs. However, I have experienced frequent failure updating multiple table, 
nested, and aggregate SELECTs.

There are reason why such updates must indeed fail: The SQLite library lacks 
certain API required to implement cell editing: Most notably, there is no 
functionality to retrieve the exact origin (table, row and column) of a cell, 
which must be known to store back the modified value. These issues have been 
discussed and acknowledged on this list. I am hopeful that we will eventually 
see them implemented.

Until then, however, there are theoretical limits to grid editing. They could 
be worked around by parsing the SQL, but this would be overkill for a simple DB 
editor like SQLiteSpy. I therefore decided to disable cell editing altogether 
in the first version of SQLiteSpy.

>  Open a table and data is loaded into a grid,
>click on a cell and type in a value and it gets written to the table,
>select a row and click a Delete button and the row is deleted from the
>table, and so on.  There must be a tool somewhere that will do that for
>a SQLite database, or there's some feature of SQLiteSpy I don't know
>about.  Can somebody please point me to one or the other?

The upcoming version of SQLiteSpy will see support for table cell editing: The 
grid will internally work in table mode for tables selected from the schema 
treeview. Such table SELECTs will be editable, use an improved buffer 
mechanism, execute faster, and use less memory. Result sets from queries 
executed from the SQL editor will be read-only, even if they are just simple 
table selects. I hope to release within the next few days.

Regards,

Ralf 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-