[sqlite] Are there distillate posts in the sqlite3 board?

[liubin liu]

are there any distillate posts in the sqlite3 board ?


-- 
View this message in context: 
http://www.nabble.com/Are-there-distillate-posts-in-the-sqlite3-board--tp22368215p22368215.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] shared cache and SQLITE_MISUSE on sqlite3_step()

[Marcus Grimm]

Dan wrote:
> On Mar 6, 2009, at 1:48 PM, Marcus Grimm wrote:
> 
>> I just want to add another hint that I learned yesterday:
>>
>> when sqlite3_step() returns SQLITE_LOCKED be aware
>> that sqlite3_reset() will most likely also return
>> SQLITE_LOCKED and thus it is necessary to repeat calling
>> it until it returns SQLITE_OK.
> 
> This is incorrect. If an error is encountered by sqlite3_step() and
> an error code returned, the same error code is returned by the  
> subsequent
> call to sqlite3_reset() or sqlite3_finalize(). sqlite3_reset() returning
> SQLITE_LOCKED does not mean you have to call it again.

Uhh... you are right. Sorry for that.

However: I've checked what sqlite3_reset() returns in this case:
The first attempt returns SQLITE_LOCKED, the 2nd try returns SQLITE_OK.

But it works, as you say, to just call sqlite3_reset() one time and proceed
with sqlite3_step().

> 
>> Maybe it would be agood idea to have these kind of
>> info somewhere bundled in the sqlite docs?
>> I found it sometimes difficult to get such issues
>> extracted or collected from the sqlite pages, resulting
>> in a lot of try and error loops and generating traffic
>> on the mailing list... :-)
> 
> The website could be much improved by including examples. Feel free
> to create a wiki page or two if you have any ideas for them.

Yes, but I don't consider my selve as an expert -- which is true as one can see
from my wrong hint above... :-)

The website is allready excellent, I'm not at all complaining...

Anyway, I've placed an updated version of the thread test program on the
web:

http://www.exomio.de/sqlitethreadtest.c

If somebody found it useful or good enough: Feel free to use it, change it,
or put on wiki pages.

Thanks Dan, once more

Marcus

> 
> Dan.
> 
> ___
> 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] which func could get the number of row

[liubin liu]

which func could get the number of row?
-- 
View this message in context: 
http://www.nabble.com/which-func-could-get-the-number-of-row-tp22369246p22369246.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] which func could get the number of rows

[John Machin]

On 6/03/2009 9:15 PM, liubin liu wrote:
> which func could get the number of rows?

select count(*) from your_table_name;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLLite support for Silverlight

[Kosenko Max]

Michael Sync wrote:
> 
> Is there any way to support SQLLite in Silverlight? Let's say I have a
> SQLLite database in Isolated Storage and want to connect that database
> from
> Silverlight without using any service.
> 
> Do we need to create our own database driver? I'm also a developer but I'm
> not familiar with that field so that I'm looking for some helps from you
> guys. It would be really great if you guys can share me some concepts or
> links or code or etc.
> 

There is a trick to do that - install google gears to the user -
http://gears.google.com/
Gears has SQLite embedded which you can use through JavaScript calls from
Silverlight.
-- 
View this message in context: 
http://www.nabble.com/SQLLite-support-for-Silverlight-tp22356980p22369473.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] Performance impact for heavy accessing the database file

[Kosenko Max]

Ribeiro, Glauber wrote:
> If it's all reads, you're fine, but if anyone is writing, all others are
> blocked until that transaction is finished.
And actually SQLite can't read at the exactly same time in several threads
in case it's compiled as a thread safe.

-- 
View this message in context: 
http://www.nabble.com/Performance-impact-for-heavy-accessing-the-database-file-tp22301864p22369477.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] which func could get the number of rows

[Kees Nuyt]

On Fri, 6 Mar 2009 02:15:10 -0800 (PST), liubin liu
<7101...@sina.com> wrote:

>which func could get the number of rows?

There is no function to retrieve the number of rows in a
result set. SQLite doesn't know the number in advance, but
returns row by row while iterating through the tables. The
application can increment a row counter as needed at every
successful sqlite3_step() .

Some wrappers are able to collect all rows in a resultset in
a in-memory table, so they can return the number of rows.

You can always get the number of rows that a certain SELECT
statement would return at the cost of some performance:

   BEGIN IMMEDIATE TRANSACTION;
   SELECT COUNT(*) FROM x WHERE y;
   SELECT a,b,c FROM x WHERE y;
   ROLLBACK TRANSACTION;

You have to wrap this in a transaction to prevent other
connections from inserting / deleting rows between the two
SELECT statements.
 
http://www.sqlite.org/lang_transaction.html

I hope this helps and I added it to the wiki FAQ:

http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] import a CSV-File

[Hans-Martin Bundeshund]

Hello to the List!

I started using SQLite (3.6.11) under WindowsXP and get confused using the 
import-function. The import runs well with most lines, but i get an error when 
it comes to following cases:


Case 1, the ',' between a string:

"Text of field1","Text of field2, not for field1"

after trying to import this, i get an error saying the CSV-File has 3 Fields. 
So does strings containing a ',' are handled wrong via SQLite, or do i miss 
something?




Case 2, maybe a mistake of IBM's DB2 running on an iSeries:

"Text of field1","Text of field2:Not for field2!"

well, you guess what will happens here. The field2 has a  what is 
exported as it is, not converted to something like "\n" or so. And for SQLite 
the line ends here. (I wonder that the iSeries does that, because the  
is a special character for the CSV-file, not to be used as value in a field, 
but thats what i get...)


Both errors can be solved by watching the leading and ending '"'-characters. If 
',' or  is between two quotes, its not a CSV-code, its only text to 
write to a field.


So any ideas for solving this out? I wrote a litle C++ .Net-application 
scanning a CSV in the right way and fill a SQLite-DB via ODBC. But that need a 
very long time (about 1 hour) to insert 4 rows to it  :(

regards,

HMB


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

Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()

[Kees Nuyt]

On Fri, 06 Mar 2009 10:26:38 +0100, Marcus Grimm
 wrote:

>The website is allready excellent, I'm not at all complaining...
>
>Anyway, I've placed an updated version of the thread test program on the
>web:
>
>http://www.exomio.de/sqlitethreadtest.c
>
>If somebody found it useful or good enough: Feel free to use it, change it,
>or put on wiki pages.

I added it to the Wiki :
http://www.sqlite.org/cvstrac/wiki?p=SampleCode 
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()

[Hynes, Tom]

Thanks Dan!

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Dan
Sent: Thursday, March 05, 2009 10:08 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()


On Mar 6, 2009, at 2:53 AM, Hynes, Tom wrote:

> Dan,
>
> I am a little confused now about how to treat a SQLITE_LOCKED
> error.  Is this to be treated the same as SQLITE_BUSY (i.e. can
> retry, unless an explicit transaction is in effect and the statement
> is not a COMMIT, in which case should roll back), or should it be
> treated differently?  If the latter, should it generally be handled
> like most other db errors (e.g. SQLITE_CONSTRAINT, SQLITE_CANTOPEN,
> etc.), or is there some specific error handling for SQLITE_LOCKED
> that is recommended?

Usually when sqlite3_step() returns something other than SQLITE_ROW, the
statement needs to be reset (sqlite3_reset()) before execution
can be re-attempted. However, SQLITE_BUSY errors are an exception.
After SQLITE_BUSY is returned, you can call sqlite3_step() again
immediately.
This I either didn't know, or forgot.

An SQLITE_LOCKED error means there is something preventing execution
within the same process. For example a lock on a shared-cache table.
For some applications, for example if another thread may release the
lock shortly, it may be appropriate to retry the query. In other apps,
for example single-threaded apps, it may not.

To retry a query that has failed with an SQLITE_LOCKED error, you need
to reset the statement before trying sqlite3_step() again.

Dan.




> Thanks,
>
> Tom
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org
> ] On Behalf Of Dan
> Sent: Thursday, March 05, 2009 12:38 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
>
>
> On Mar 6, 2009, at 12:22 AM, Marcus Grimm wrote:
>
>> Dan,
>>
>> I'm not sure what you mean by technically the SQLITE_BUSY is
>> also wrong. In the test program I get the SQLITE_BUSY quite
>> often and by retrying the sqlite3_step() it will sooner or
>> later succeed.
>
> Huh. My mistake.
>
>
>
>> Thanks for confirming that it may happend only for the
>> first call to sqlite3_step(). This is an important point and
>> it makes it in fact easy to workaround.
>> I've changed my test application accordingly and now it can
>> run endless, I even increased now to 10 reader threads and 2 writer
>> threads in
>> parallel without making sqlite particular nervous. Wonderful... :-)
>>
>> Thanks again
>>
>> Marcus
>>
>> Dan wrote:
>>> On Mar 5, 2009, at 11:14 PM, Marcus Grimm wrote:
>>>
 Hi Dan,

 thank you!

 Yes! I was not aware that it is wrong to redo a step
 when it previously returned the LOCKED state.
 That was the reason for the (correct) MISSUSE return on
 the 2nd attempt.

 I think this is the main difference in my case between
 shared cache on/off: Without shared cache I never
 get this lock state and it just does the BUSY handling correctly.
>>>
>>> Technically the SQLITE_BUSY handling is also wrong, for the
>>> same reasons. But it's harder to hit a real SQLITE_BUSY than it
>>> is to hit an SQLITE_LOCKED in shared-cache mode.
>>>
 I'll change my locking handling accordingly, seems to be a problem
 of the very first step after a prepare, so that's should to be easy
 to reset...
>>>
>>> That's correct. An SQLITE_LOCKED or SQLITE_BUSY may only occur
>>> on the first call to sqlite3_step().
>>>
>>> Dan.
>>>
>>>
>>>
 Thanks again for your feedback

 Marcus

 Dan wrote:
> On Mar 5, 2009, at 9:57 PM, Marcus Grimm wrote:
>
>> Hi again,
>>
>> I did a little test program that simulates the multi threading
>> issue to understand better why I get a MISSUSE error even
>> when sqlite_prepare returns no error.
>> The test program will printout some errors on the console
>> and exits in that case. If I don't use the shared cache it
>> runs endless without any problem.
>>
>> The code is to long to be placed here, but I would be very happy
>> if somebody could take a look. I've put it on a web server to
>> download:
>>
>> http://www.exomio.de/sqlitethreadtest.c
>>
>> I'm using MS Visual Studio 2008, the program is started within a
>> dosbox to see the error output. Using it on my dual core PC
>> it allmost immediately stops with the missuse error, but why?
>   do
>   {
>   rc = sqlite3_step(hs);
>
>   if( (rc == SQLITE_BUSY) || (rc == SQLITE_LOCKED) )
>   {
>   Sleep(SQLTM_TIME);
>
>   n++;
>   }
>   }while( (n < SQLTM_COUNT) && ((rc == SQLITE_BUSY) || (rc ==
> SQLITE_LOCKED)));
>
> If sqlite3_step() returns SQLITE_BUSY or SQLITE_LOCKED, you need
> to
> res

Re: [sqlite] Are there distillate posts in the sqlite3 board?

[Ribeiro, Glauber]

Are you looking perhaps for digest mode? Yes, there is a digest mode for
the SQLite email lists, it's one of the options you can set up.

Or are you thinking of posts that were made under the influence of
distillates? :)

g 

-Original Message-
From: liubin liu [mailto:7101...@sina.com] 
Sent: Friday, March 06, 2009 2:39 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Are there distillate posts in the sqlite3 board?


are there any distillate posts in the sqlite3 board ?


-- 
View this message in context:
http://www.nabble.com/Are-there-distillate-posts-in-the-sqlite3-board--t
p22368215p22368215.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-dev] SQLLite support for Silverlight

[Chris Morgan]

On Thu, Mar 5, 2009 at 12:57 PM, Michael Sync  wrote:
> Hello,
>
> Is there any way to support SQLLite in Silverlight? Let's say I have a
> SQLLite database in Isolated Storage and want to connect that database from
> Silverlight without using any service.
>
> Do we need to create our own database driver? I'm also a developer but I'm
> not familiar with that field so that I'm looking for some helps from you
> guys. It would be really great if you guys can share me some concepts or
> links or code or etc.
>

You can use System.Data.SQLite, an open source interface assembly, to
interface with libsqlite or sqlite.dll.

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

Re: [sqlite] [sqlite-dev] SQLLite support for Silverlight

[Jeffrey . a . Becker]

1) Please ask this type of question on the sqlite-users mailing list.
2) No there isn't. If you'd like a more in-depth explanation of why start a  
thread on http://sqlite.phxsoftware.com/forums/ and I will answer there.

On Mar 5, 2009 1:09pm, Chris Morgan  wrote:
> On Thu, Mar 5, 2009 at 12:57 PM, Michael Sync mchls...@gmail.com> wrote:

> > Hello,

> >

> > Is there any way to support SQLLite in Silverlight? Let's say I have a

> > SQLLite database in Isolated Storage and want to connect that database  
> from

> > Silverlight without using any service.

> >

> > Do we need to create our own database driver? I'm also a developer but  
> I'm

> > not familiar with that field so that I'm looking for some helps from you

> > guys. It would be really great if you guys can share me some concepts or

> > links or code or etc.

> >



> You can use System.Data.SQLite, an open source interface assembly, to

> interface with libsqlite or sqlite.dll.



> Chris

> ___

> sqlite-dev mailing list

> sqlite-...@sqlite.org

> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev

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

Re: [sqlite] sqlite3 datbase disk image malformed

[Peter van Dijk]

On Thu, Mar 05, 2009 at 04:30:50AM -0800, donnied wrote:
> 
> rsync was corrupting the database.  I'll have to exclude the database from
> rsync backup. 

I feel a need to point out that it is not, technically, rsync that was 
corrupting
the database. The issue is that rsync does not take -snapshots- of files; for
that matter, very few backup tools do. One fix/workaround is to have a cronjob
for making textual dumps.

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

Re: [sqlite] SQLLite support for Silverlight

[Michael Sync]

thanks, Kosenko.

I don;t think that everyone will have Google Gear installed on their
browsers. Actually, if they are using IE then i can probably create an
ActiveX object of ADODB in JS. But I'm looking for platform-independent way
as Silverlight works on non-Windows platform as well.

On Fri, Mar 6, 2009 at 7:10 PM, Kosenko Max  wrote:

>
>
> Michael Sync wrote:
> >
> > Is there any way to support SQLLite in Silverlight? Let's say I have a
> > SQLLite database in Isolated Storage and want to connect that database
> > from
> > Silverlight without using any service.
> >
> > Do we need to create our own database driver? I'm also a developer but
> I'm
> > not familiar with that field so that I'm looking for some helps from you
> > guys. It would be really great if you guys can share me some concepts or
> > links or code or etc.
> >
>
> There is a trick to do that - install google gears to the user -
> http://gears.google.com/
> Gears has SQLite embedded which you can use through JavaScript calls from
> Silverlight.
> --
> View this message in context:
> http://www.nabble.com/SQLLite-support-for-Silverlight-tp22356980p22369473.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
>



-- 
Don't go the way life takes you.
Take life the way you go

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

Re: [sqlite] import a CSV-File

[Griggs, Donald]

 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Hans-Martin
Bundeshund
Sent: Friday, March 06, 2009 6:23 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] import a CSV-File


Hello to the List!

I started using SQLite (3.6.11) under WindowsXP and get confused using
the import-function. The import runs well with most lines, but i get an
error when it comes to following cases:


Case 1, the ',' between a string:

"Text of field1","Text of field2, not for field1"

after trying to import this, i get an error saying the CSV-File has 3
Fields. So does strings containing a ',' are handled wrong via SQLite,
or do i miss something?




Case 2, maybe a mistake of IBM's DB2 running on an iSeries:

"Text of field1","Text of field2:Not for field2!"

well, you guess what will happens here. The field2 has a  what
is exported as it is, not converted to something like "\n" or so. And
for SQLite the line ends here. (I wonder that the iSeries does that,
because the  is a special character for the CSV-file, not to be
used as value in a field, but thats what i get...)


Both errors can be solved by watching the leading and ending
'"'-characters. If ',' or  is between two quotes, its not a
CSV-code, its only text to write to a field.


So any ideas for solving this out? I wrote a litle C++ .Net-application
scanning a CSV in the right way and fill a SQLite-DB via ODBC. But that
need a very long time (about 1 hour) to insert 4 rows to it  :(

regards,

HMB
=
Hi Hans,

The sqlite command-line utility program does not have code in it to
handle quoted strings containing the field separator (comma, in your
case), nor does it expect the multi-line data.

If you can have your data written as tab-separated (and without embedded
CR/LF) you may have better luck.

Alternatively -- for the new utility you wrote, did you surround your
import within a single transaction?   If not, then every row becomes a
separate transaction, and one would expect performance to suffer
drastically.

Regards,
   Donald

  
___
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] sqlite3 datbase disk image malformed

[Derrell Lipman]

On Thu, Mar 5, 2009 at 7:39 AM, Peter van Dijk  wrote:

> On Thu, Mar 05, 2009 at 04:30:50AM -0800, donnied wrote:
> >
> > rsync was corrupting the database.  I'll have to exclude the database
> from
> > rsync backup.
>
> I feel a need to point out that it is not, technically, rsync that was
> corrupting
> the database. The issue is that rsync does not take -snapshots- of files;
> for
> that matter, very few backup tools do. One fix/workaround is to have a
> cronjob
> for making textual dumps.
>

If it's possible that some other application is modifying the database while
you're backing it up, you'll likely end up with a corrupted database when
you rsync it. rsync only copies changed regions of files to save bandwidth.
If you make it a standard practice to always issue a "BEGIN EXCLUSIVE"
transaction on the database before you do your rsync, then the database will
be in a consistent state on both the destination side where rsync compares
and the source side where rsync is looking for changes. This way, the
database is effectively a static (unchanging) file and rsync should have no
problem backing it up without corruption. After you complete the rsync then
ROLLBACK the transaction.

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

Re: [sqlite] import a CSV-File

[Dave Toll]

Hi Hans-Martin

Try changing the double-quotes to single-quotes in your string data. Bear in 
mind that if you want a single-quote to appear in a string, it must be escaped 
with another single-quote i.e. ''. Excel should be able to automate this for 
you.

Cheers,
Dave.


-Original Message-
From: Hans-Martin Bundeshund [mailto:bundesh...@yahoo.de] 
Sent: 06 March 2009 03:23
To: sqlite-users@sqlite.org
Subject: [sqlite] import a CSV-File


Hello to the List!

I started using SQLite (3.6.11) under WindowsXP and get confused using the 
import-function. The import runs well with most lines, but i get an error when 
it comes to following cases:


Case 1, the ',' between a string:

"Text of field1","Text of field2, not for field1"

after trying to import this, i get an error saying the CSV-File has 3 Fields. 
So does strings containing a ',' are handled wrong via SQLite, or do i miss 
something?




Case 2, maybe a mistake of IBM's DB2 running on an iSeries:

"Text of field1","Text of field2:Not for field2!"

well, you guess what will happens here. The field2 has a  what is 
exported as it is, not converted to something like "\n" or so. And for SQLite 
the line ends here. (I wonder that the iSeries does that, because the  
is a special character for the CSV-file, not to be used as value in a field, 
but thats what i get...)


Both errors can be solved by watching the leading and ending '"'-characters. If 
',' or  is between two quotes, its not a CSV-code, its only text to 
write to a field.


So any ideas for solving this out? I wrote a litle C++ .Net-application 
scanning a CSV in the right way and fill a SQLite-DB via ODBC. But that need a 
very long time (about 1 hour) to insert 4 rows to it  :(

regards,

HMB


  

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

Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()

[Dave Toll]

Without wishing to complicate the subject, I have found that retrying a
sqlite3_step() after receiving SQLITE_LOCKED works fine for me without
needing sqlite3_reset(). I do this in a multi-threaded test app (based
on some old SQLite test code) that I have left running with 10 threads
for a long time and did not encounter any errors. I was using
shared-cache.

Are there perhaps some cases where a reset is required on SQLITE_LOCKED,
and other cases where it can be interpreted the same as SQLITE_BUSY?

Cheers,
Dave.


-Original Message-
From: Hynes, Tom [mailto:tom.hy...@inin.com] 
Sent: 06 March 2009 05:10
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()

Thanks Dan!

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan
Sent: Thursday, March 05, 2009 10:08 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()


On Mar 6, 2009, at 2:53 AM, Hynes, Tom wrote:

> Dan,
>
> I am a little confused now about how to treat a SQLITE_LOCKED
> error.  Is this to be treated the same as SQLITE_BUSY (i.e. can
> retry, unless an explicit transaction is in effect and the statement
> is not a COMMIT, in which case should roll back), or should it be
> treated differently?  If the latter, should it generally be handled
> like most other db errors (e.g. SQLITE_CONSTRAINT, SQLITE_CANTOPEN,
> etc.), or is there some specific error handling for SQLITE_LOCKED
> that is recommended?

Usually when sqlite3_step() returns something other than SQLITE_ROW, the
statement needs to be reset (sqlite3_reset()) before execution
can be re-attempted. However, SQLITE_BUSY errors are an exception.
After SQLITE_BUSY is returned, you can call sqlite3_step() again
immediately.
This I either didn't know, or forgot.

An SQLITE_LOCKED error means there is something preventing execution
within the same process. For example a lock on a shared-cache table.
For some applications, for example if another thread may release the
lock shortly, it may be appropriate to retry the query. In other apps,
for example single-threaded apps, it may not.

To retry a query that has failed with an SQLITE_LOCKED error, you need
to reset the statement before trying sqlite3_step() again.

Dan.




> Thanks,
>
> Tom
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org
> ] On Behalf Of Dan
> Sent: Thursday, March 05, 2009 12:38 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
>
>
> On Mar 6, 2009, at 12:22 AM, Marcus Grimm wrote:
>
>> Dan,
>>
>> I'm not sure what you mean by technically the SQLITE_BUSY is
>> also wrong. In the test program I get the SQLITE_BUSY quite
>> often and by retrying the sqlite3_step() it will sooner or
>> later succeed.
>
> Huh. My mistake.
>
>
>
>> Thanks for confirming that it may happend only for the
>> first call to sqlite3_step(). This is an important point and
>> it makes it in fact easy to workaround.
>> I've changed my test application accordingly and now it can
>> run endless, I even increased now to 10 reader threads and 2 writer
>> threads in
>> parallel without making sqlite particular nervous. Wonderful... :-)
>>
>> Thanks again
>>
>> Marcus
>>
>> Dan wrote:
>>> On Mar 5, 2009, at 11:14 PM, Marcus Grimm wrote:
>>>
 Hi Dan,

 thank you!

 Yes! I was not aware that it is wrong to redo a step
 when it previously returned the LOCKED state.
 That was the reason for the (correct) MISSUSE return on
 the 2nd attempt.

 I think this is the main difference in my case between
 shared cache on/off: Without shared cache I never
 get this lock state and it just does the BUSY handling correctly.
>>>
>>> Technically the SQLITE_BUSY handling is also wrong, for the
>>> same reasons. But it's harder to hit a real SQLITE_BUSY than it
>>> is to hit an SQLITE_LOCKED in shared-cache mode.
>>>
 I'll change my locking handling accordingly, seems to be a problem
 of the very first step after a prepare, so that's should to be easy
 to reset...
>>>
>>> That's correct. An SQLITE_LOCKED or SQLITE_BUSY may only occur
>>> on the first call to sqlite3_step().
>>>
>>> Dan.
>>>
>>>
>>>
 Thanks again for your feedback

 Marcus

 Dan wrote:
> On Mar 5, 2009, at 9:57 PM, Marcus Grimm wrote:
>
>> Hi again,
>>
>> I did a little test program that simulates the multi threading
>> issue to understand better why I get a MISSUSE error even
>> when sqlite_prepare returns no error.
>> The test program will printout some errors on the console
>> and exits in that case. If I don't use the shared cache it
>> runs endless without any problem.
>>
>> The code is to long to be placed here, but I would be very happy
>> if somebody could tak

[sqlite] Thread-safety and sqlite3_stmt objects

[Shawn Wilsher]

Hey all,

I've been looking online for a bit trying to establish what protections, if
any, are associated with sqlite3_stmt objects.  It's clearly documented that
sqlite3 objects' access is serialized across threads, but I cannot find
anything about sqlite3_stmt.  I don't actually care either way, but if
SQLite protects it internally, I don't want to add additional overhead by
protecting it myself.

Could someone please clarify this (and maybe add some documentation)?

Cheers,

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

Re: [sqlite] import a CSV-File

[Hans-Martin]

Am Freitag, 6. März 2009 18:29:58 schrieb Griggs, Donald:
...
> Hi Hans,



Hi Donald!

>
> The sqlite command-line utility program does not have code in it to
> handle quoted strings containing the field separator (comma, in your
> case), nor does it expect the multi-line data.
>
> If you can have your data written as tab-separated (and without embedded
> CR/LF) you may have better luck.

It seems that there is no way to get rid of the embedded CR/LF without parse 
the complete output.

>
> Alternatively -- for the new utility you wrote, did you surround your
> import within a single transaction?   If not, then every row becomes a
> separate transaction, and one would expect performance to suffer
> drastically.

Stupid me, i opened a connection and execute insert after insert... 

Better to read the FAQ before start coding:  
http://www.sqlite.org/faq.html#q19 says it. I have to test this at monday.

Btw, how big can a SQL-statment grow in SQLite? 
http://www.sqlite.org/limits.html says: default set to 100, but can set 
up to SQLITE_MAX_LENGTH = 1073741824 - 1.  So can i just execute a 'set 
SQLITE_MAX_LENGTH = 1073741824 - 1'? This point is not clear for me now.
i found http://www.sqlite.org/c3ref/c_limit_attached.html and see about the 
run_time using sqlite3_limit(), but how can i pass this using pure SQL?

Well, looking at the samples it doesn't seems to be too tricky to use the 
C++API. Can i expect better performance using it?


>
> Regards,
>Donald

Hans-Martin

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

Re: [sqlite] import a CSV-File

[Rich Shepard]

On Fri, 6 Mar 2009, Hans-Martin wrote:

> It seems that there is no way to get rid of the embedded CR/LF without parse
> the complete output.

   Use sed. That's what it's for.

Rich

-- 
Richard B. Shepard, Ph.D.   |  IntegrityCredibility
Applied Ecosystem Services, Inc.|Innovation
 Voice: 503-667-4517  Fax: 503-667-8863
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite Transaction Rate and speed...

[Nuzzi]

I have a project where I have to be determining if a row exists, if so get
the data, change it, and then write it back, if not, then just writing the
data.  I have to be able to do millions of these per minute.  Is that pretty
much impossible with SQLite or any other DB?

Thanks,

John
-- 
View this message in context: 
http://www.nabble.com/SQLite-Transaction-Rate-and-speed...-tp22379931p22379931.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] Looking for results from one table based partially onresults from another

[Mike Yenco]

Thanks Igor.  It works great.

-Mike


On Mar 5, 2009, at 3:19 PM, Igor Tandetnik wrote:

> and ItemizeID in (
>select ItemizeID from Items where Description LIKE
> %[[searchstring]]%
> )
>
> Igor Tandetnik

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

Re: [sqlite] SQLite Transaction Rate and speed...

[Ken]

look at the sql syntax for insert or replace for sqlite. 

Also you goal to handle 1 million per minute is probably going to be dependant 
upon your hardware. 

For instance throughput greatly increases with disk striping. 

Also the faster the RPM of the drive the more transactions can be processed. 
Code it up and find out!



--- On Fri, 3/6/09, Nuzzi  wrote:

> From: Nuzzi 
> Subject: [sqlite]  SQLite Transaction Rate and speed...
> To: sqlite-users@sqlite.org
> Date: Friday, March 6, 2009, 2:27 PM
> I have a project where I have to be determining if a row
> exists, if so get
> the data, change it, and then write it back, if not, then
> just writing the
> data.  I have to be able to do millions of these per
> minute.  Is that pretty
> much impossible with SQLite or any other DB?
> 
> Thanks,
> 
> John
> -- 
> View this message in context:
> http://www.nabble.com/SQLite-Transaction-Rate-and-speed...-tp22379931p22379931.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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Transaction Rate and speed...

[Nuzzi]

ken-33 wrote:
> 
> 
> 
> look at the sql syntax for insert or replace for sqlite. 
> 
> Also you goal to handle 1 million per minute is probably going to be
> dependant upon your hardware. 
> 
> For instance throughput greatly increases with disk striping. 
> 
> Also the faster the RPM of the drive the more transactions can be
> processed. Code it up and find out!
> 
> 

I have actually coded it up.  The way I am currently doing it is sending the
data to a function (the data is 3 doubles) and in that function doing a
SELECT to get the data currently in the DB, then updating the data, then
UPDATE or INSERT.  The SQL calls are compiled statements with binds, etc. 
It is woefully slow.  I was kind of hoping that maybe I was missing a step
or just unfamiliar with the best techniques. I know that when adding a lot
of data with the BEGIN and END TRANSACTION things speed up considerably, but
I can't use it in my case (I don't believe) because the UPDATE depends upon
the data in the SELECT.

John

-- 
View this message in context: 
http://www.nabble.com/SQLite-Transaction-Rate-and-speed...-tp22379931p22380539.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 Transaction Rate and speed...

[Nuzzi]

ken-33 wrote:
> 
> 
> 
> look at the sql syntax for insert or replace for sqlite. 
> 
> Also you goal to handle 1 million per minute is probably going to be
> dependant upon your hardware. 
> 
> For instance throughput greatly increases with disk striping. 
> 
> Also the faster the RPM of the drive the more transactions can be
> processed. Code it up and find out!
> 
> 

I have actually coded it up.  The way I am currently doing it is sending the
data to a function (the data is 3 doubles) and in that function doing a
SELECT to get the data currently in the DB, then updating the data, then
UPDATE or INSERT.  The SQL calls are compiled statements with binds, etc. 
It is woefully slow.  I was kind of hoping that maybe I was missing a step
or just unfamiliar with the best techniques. I know that when adding a lot
of data with the BEGIN and END TRANSACTION things speed up considerably, but
I can't use it in my case (I don't believe) because the UPDATE depends upon
the data in the SELECT.

John

-- 
View this message in context: 
http://www.nabble.com/SQLite-Transaction-Rate-and-speed...-tp22379931p22380540.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 Transaction Rate and speed...

[Marcus Grimm]

you should in any case try to encapsulate your
inserting loop with a transaction. you should still
be able to do select statements inside, in particular
if all is done within one DB connection.
transactions do not speed up considerably, they do it dramatically... ;)

also you may give a code example how you do this, since
there are enough sql gurus around here that may help to
improve further.

hth
Marcus


>
>
>
> ken-33 wrote:
>>
>>
>>
>> look at the sql syntax for insert or replace for sqlite.
>>
>> Also you goal to handle 1 million per minute is probably going to be
>> dependant upon your hardware.
>>
>> For instance throughput greatly increases with disk striping.
>>
>> Also the faster the RPM of the drive the more transactions can be
>> processed. Code it up and find out!
>>
>>
>
> I have actually coded it up.  The way I am currently doing it is sending
> the
> data to a function (the data is 3 doubles) and in that function doing a
> SELECT to get the data currently in the DB, then updating the data, then
> UPDATE or INSERT.  The SQL calls are compiled statements with binds, etc.
> It is woefully slow.  I was kind of hoping that maybe I was missing a step
> or just unfamiliar with the best techniques. I know that when adding a lot
> of data with the BEGIN and END TRANSACTION things speed up considerably,
> but
> I can't use it in my case (I don't believe) because the UPDATE depends
> upon
> the data in the SELECT.
>
> John
>
> --
> View this message in context:
> http://www.nabble.com/SQLite-Transaction-Rate-and-speed...-tp22379931p22380540.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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()

[Marcus Grimm]

strange... I can hardly believe that...

did your test app probably reading only ?

Marcus

> Without wishing to complicate the subject, I have found that retrying a
> sqlite3_step() after receiving SQLITE_LOCKED works fine for me without
> needing sqlite3_reset(). I do this in a multi-threaded test app (based
> on some old SQLite test code) that I have left running with 10 threads
> for a long time and did not encounter any errors. I was using
> shared-cache.
>
> Are there perhaps some cases where a reset is required on SQLITE_LOCKED,
> and other cases where it can be interpreted the same as SQLITE_BUSY?
>
> Cheers,
> Dave.
>
>
> -Original Message-
> From: Hynes, Tom [mailto:tom.hy...@inin.com]
> Sent: 06 March 2009 05:10
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
>
> Thanks Dan!
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan
> Sent: Thursday, March 05, 2009 10:08 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
>
>
> On Mar 6, 2009, at 2:53 AM, Hynes, Tom wrote:
>
>> Dan,
>>
>> I am a little confused now about how to treat a SQLITE_LOCKED
>> error.  Is this to be treated the same as SQLITE_BUSY (i.e. can
>> retry, unless an explicit transaction is in effect and the statement
>> is not a COMMIT, in which case should roll back), or should it be
>> treated differently?  If the latter, should it generally be handled
>> like most other db errors (e.g. SQLITE_CONSTRAINT, SQLITE_CANTOPEN,
>> etc.), or is there some specific error handling for SQLITE_LOCKED
>> that is recommended?
>
> Usually when sqlite3_step() returns something other than SQLITE_ROW, the
> statement needs to be reset (sqlite3_reset()) before execution
> can be re-attempted. However, SQLITE_BUSY errors are an exception.
> After SQLITE_BUSY is returned, you can call sqlite3_step() again
> immediately.
> This I either didn't know, or forgot.
>
> An SQLITE_LOCKED error means there is something preventing execution
> within the same process. For example a lock on a shared-cache table.
> For some applications, for example if another thread may release the
> lock shortly, it may be appropriate to retry the query. In other apps,
> for example single-threaded apps, it may not.
>
> To retry a query that has failed with an SQLITE_LOCKED error, you need
> to reset the statement before trying sqlite3_step() again.
>
> Dan.
>
>
>
>
>> Thanks,
>>
>> Tom
>>
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org
>> ] On Behalf Of Dan
>> Sent: Thursday, March 05, 2009 12:38 PM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] shared cache and SQLITE_MISUSE on sqlite3_step()
>>
>>
>> On Mar 6, 2009, at 12:22 AM, Marcus Grimm wrote:
>>
>>> Dan,
>>>
>>> I'm not sure what you mean by technically the SQLITE_BUSY is
>>> also wrong. In the test program I get the SQLITE_BUSY quite
>>> often and by retrying the sqlite3_step() it will sooner or
>>> later succeed.
>>
>> Huh. My mistake.
>>
>>
>>
>>> Thanks for confirming that it may happend only for the
>>> first call to sqlite3_step(). This is an important point and
>>> it makes it in fact easy to workaround.
>>> I've changed my test application accordingly and now it can
>>> run endless, I even increased now to 10 reader threads and 2 writer
>>> threads in
>>> parallel without making sqlite particular nervous. Wonderful... :-)
>>>
>>> Thanks again
>>>
>>> Marcus
>>>
>>> Dan wrote:
 On Mar 5, 2009, at 11:14 PM, Marcus Grimm wrote:

> Hi Dan,
>
> thank you!
>
> Yes! I was not aware that it is wrong to redo a step
> when it previously returned the LOCKED state.
> That was the reason for the (correct) MISSUSE return on
> the 2nd attempt.
>
> I think this is the main difference in my case between
> shared cache on/off: Without shared cache I never
> get this lock state and it just does the BUSY handling correctly.

 Technically the SQLITE_BUSY handling is also wrong, for the
 same reasons. But it's harder to hit a real SQLITE_BUSY than it
 is to hit an SQLITE_LOCKED in shared-cache mode.

> I'll change my locking handling accordingly, seems to be a problem
> of the very first step after a prepare, so that's should to be easy
> to reset...

 That's correct. An SQLITE_LOCKED or SQLITE_BUSY may only occur
 on the first call to sqlite3_step().

 Dan.



> Thanks again for your feedback
>
> Marcus
>
> Dan wrote:
>> On Mar 5, 2009, at 9:57 PM, Marcus Grimm wrote:
>>
>>> Hi again,
>>>
>>> I did a little test program that simulates the multi threading
>>> issue to understand better why I get a MISSUSE error even
>>> when sqlite_prepare returns no error.
>

Re: [sqlite] SQLite Transaction Rate and speed...

[Nuzzi]

Marcus Grimm wrote:
> 
> you should in any case try to encapsulate your
> inserting loop with a transaction. you should still
> be able to do select statements inside, in particular
> if all is done within one DB connection.
> transactions do not speed up considerably, they do it dramatically... ;)
> 
> also you may give a code example how you do this, since
> there are enough sql gurus around here that may help to
> improve further.
> 
> hth
> Marcus
> 
> 

I have attached a .cpp and .h file that show what I am doing.  A couple of
notes: I am using a C++ wrapper to the sqlite API.  The bottleneck is
obviously in AddData.  For testing I am just looping through 1 randoms
entries.  My last run took about 23 minutes for those 1 and I have a
brand spanking new kick-ass Dell 2.93 Ghz laptop running Vista 64-bit.  The
helper functions in the AddData take no time at all, they were in use when I
was doing everything in memory.  Let me know what you think about SQLite end
of it, I am trying to learn and hoping it can be a useful solution.  I have
already incorporated it for other file formats.  Ones that don't need speed
intensive, consistent use, of course. I do appreciate the comments (I was
trying not to say that because the posting rules mention staying away from
the little talk :-))

John
-- 
View this message in context: 
http://www.nabble.com/SQLite-Transaction-Rate-and-speed...-tp22379931p22381160.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 Transaction Rate and speed...

[Marcus Grimm]

I'm afraid you can't post an attachment here...
try it differently.


>
>
>
> Marcus Grimm wrote:
>>
>> you should in any case try to encapsulate your
>> inserting loop with a transaction. you should still
>> be able to do select statements inside, in particular
>> if all is done within one DB connection.
>> transactions do not speed up considerably, they do it dramatically... ;)
>>
>> also you may give a code example how you do this, since
>> there are enough sql gurus around here that may help to
>> improve further.
>>
>> hth
>> Marcus
>>
>>
>
> I have attached a .cpp and .h file that show what I am doing.  A couple of
> notes: I am using a C++ wrapper to the sqlite API.  The bottleneck is
> obviously in AddData.  For testing I am just looping through 1 randoms
> entries.  My last run took about 23 minutes for those 1 and I have a
> brand spanking new kick-ass Dell 2.93 Ghz laptop running Vista 64-bit.
> The
> helper functions in the AddData take no time at all, they were in use when
> I
> was doing everything in memory.  Let me know what you think about SQLite
> end
> of it, I am trying to learn and hoping it can be a useful solution.  I
> have
> already incorporated it for other file formats.  Ones that don't need
> speed
> intensive, consistent use, of course. I do appreciate the comments (I was
> trying not to say that because the posting rules mention staying away from
> the little talk :-))
>
> John
> --
> View this message in context:
> http://www.nabble.com/SQLite-Transaction-Rate-and-speed...-tp22379931p22381160.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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] problem with sqlite storing TCL scripts

[anjela patnaik]

Hello all,
 
I've used sqlite3 .read command to read in an SQL file from (exported via TOAD) 
Oracle that contains TCL scripts.
 
The .read worked fine, but now I'm noticing that my multi-line scripts are not 
running properly on the shell when I retrieve them from the sqlite table.
 
I notice that somehow extra newlines were added or some other control chars. 
 
The original scripts in the exported SQL file are fine and can be run directly 
on the shell.
 
Do I need to do something to properly import/store TCL scripts with backslashes 
and newlines into SQLite??
 
still new to sqlite...
 
 
Thank you for any advice!
 
Below is the script from the SQL file:
 
set ver 7.1
set managedElementName \
[NamingAttributes_T $ver \
[NVSList_T $ver \
[NameAndStringValue_T $ver \
"EMS" \
"" \
] \
[NameAndStringValue_T $ver \
"ManagedElement" \
"IN_CN4200_22" \
] \
] \
] 
set tpLayerRateList \
[LayerRateList_T $ver \
] 
set connectionLayerRateList \
[LayerRateList_T $ver \
] 
set how_many \
"1000" 
if [catch {getAllPTPs $thread $managedElementName $tpLayerRateList 
$connectionLayerRateList $how_many tpList tpIt} err] {
puts stderr "getAllPTPs failed, err = $err"
}
 
Below is the return from the select off the sqlite db:
 
set ver 7.1
 
  set managedElementName \
    [NamingAttributes_T $ver \
    [NVSList_T $ver \
    [NameAndStringValue_T $ver \
    "EMS" \
    "" \
    ] \
    [NameAndStringValue_T $ver \
    "ManagedElement" \
    "IN_CN4200_22" \
    ] \
    ] \
    ] 
 
  set tpLayerRateList \
    [LayerRateList_T $ver \
    ] 
 
  set connectionLayerRateList \
    [LayerRateList_T $ver \
    ] 
 
  set how_many \
    "1000" 
 
  if [catch {getAllPTPs $thread $managedElementName $tpLayerRateList 
$connectionLayerRateList $how_many tpList tpIt} err] {
  puts stderr "getAllPTPs failed, err = $err"
  }
 
 


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

Re: [sqlite] import a CSV-File

[John Machin]

On 7/03/2009 6:16 AM, Rich Shepard wrote:
> On Fri, 6 Mar 2009, Hans-Martin wrote:
> 
>> It seems that there is no way to get rid of the embedded CR/LF without parse
>> the complete output.
> 
>Use sed. That's what it's for.

Has anyone considered that getting rid of the embedded CR/LF is 
destroying information?

Has anyone considered the possibility that SQLite .import could be made 
to support the CSV format?

Alternative: read the CSV file and update the SQLite3 database using a 
scripting language with the appropriate capabilities (Python definitely, 
  Perl surely, others ...)

Cheers,
John

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

Re: [sqlite] SQLite Transaction Rate and speed...

[Nuzzi]

Marcus Grimm wrote:
> 
> I'm afraid you can't post an attachment here...
> try it differently.
> 
> 

Hmm... Bummer.  Here are some links (hopefully):

ftp://ftp.smokemytool.com/pub/matrix_file_db.cpp matrix_file_db.cpp 
ftp://ftp.smokemytool.com/pub/matrix_file_db.h matrix_file_db.h 

John

-- 
View this message in context: 
http://www.nabble.com/SQLite-Transaction-Rate-and-speed...-tp22379931p22381669.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 Transaction Rate and speed...

[Marcus Grimm]

sorry.. no luck either... or I have a personal problem
with your ftp server -- he doesn't like my anonymous login... ;)

>
>
> Marcus Grimm wrote:
>>
>> I'm afraid you can't post an attachment here...
>> try it differently.
>>
>>
>
> Hmm... Bummer.  Here are some links (hopefully):
>
> ftp://ftp.smokemytool.com/pub/matrix_file_db.cpp matrix_file_db.cpp
> ftp://ftp.smokemytool.com/pub/matrix_file_db.h matrix_file_db.h
>
> John
>
> --
> View this message in context:
> http://www.nabble.com/SQLite-Transaction-Rate-and-speed...-tp22379931p22381669.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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Transaction Rate and speed...

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Nuzzi wrote:
> It is woefully slow.  

A transaction requires two syncs (ie requesting the drive write the data
to the metal and not return until it does).  On average each sync will
take a disk rotation so a 7200rpm drive maxes out at 60 transactions a
second.  If you require each data update to be physically on the disk
then this is your limit no matter what library or storage mechanism you use.

You can increase performance by increasing the number of updates per
transaction.  You can also do things like have an attached memory
database and then flush its contents to the disk database every 5
seconds or whatever your tolerance for data loss is.

You can also look at alternate storage mechanisms.  For example rotating
drives can be paired with a battery backed controller, or solid state
could be used if the wear leveling and write-erase performance meet your
needs.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkmxqr0ACgkQmOOfHg372QR36ACgzApBSzqB3fW5lxgyAQaQjjn1
CaoAoNJ3nXrflO/QVZtNYlDFIfzgbLsO
=n2sS
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Transaction Rate and speed...

[Griggs, Donald]

Regarding:
sorry.. no luck either... or I have a personal problem with your ftp
server -- he doesn't like my anonymous login... ;)

==

I get an error of "421 Too many users logged in for this account."

I guess we smoked your tool. ;-)


Pretty much echoing Roger Binn's recent comment, this posting by D. Hipp
may be of interest:
http://www.nabble.com/Behavior-of-SELECT-queries-during-transaction-to13
249797.html#a13257203

Just curious -- are you at liberty to say what this application is with
this firehose of data?

Also, I know you required multiple-millions of actions per minute -- but
since you'll be updating some rows rather than inserting them all, about
how big do you anticpate the database being?   That can make a big
difference because:
   a) Building and consulting the table's index will get longer the
greater the number of rows, and
   b) If it's small enough, an in-ram database (":MEMORY:") Roger
mentions might be feasible.

FTP error below:

N:\>ftp ftp.smokemytool.com
Connected to ftp.smokemytool.com.
220-FileZilla Server version 0.9.24 beta
220-written by Tim Kosse (tim.ko...@gmx.de)
220 Please visit http://sourceforge.net/projects/filezilla/
User (ftp.smokemytool.com:(none)): anonymous
331 Password required for anonymous
Password:
421 Too many users logged in for this account. Try again later.
Login failed.
ftp> 

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

Re: [sqlite] SQLite Transaction Rate and speed...

[Jim Dodgen]

if you need to always replace or create an new row you might just do a
"INSERT OR REPLACE" which will replace the record if it exists or
insert a new one if it does not.


On Fri, Mar 6, 2009 at 3:10 PM, Griggs, Donald
 wrote:
> Regarding:
>    sorry.. no luck either... or I have a personal problem with your ftp
> server -- he doesn't like my anonymous login... ;)
>
> ==
>
> I get an error of "421 Too many users logged in for this account."
>
> I guess we smoked your tool.     ;-)
>
>
> Pretty much echoing Roger Binn's recent comment, this posting by D. Hipp
> may be of interest:
> http://www.nabble.com/Behavior-of-SELECT-queries-during-transaction-to13
> 249797.html#a13257203
>
> Just curious -- are you at liberty to say what this application is with
> this firehose of data?
>
> Also, I know you required multiple-millions of actions per minute -- but
> since you'll be updating some rows rather than inserting them all, about
> how big do you anticpate the database being?   That can make a big
> difference because:
>   a) Building and consulting the table's index will get longer the
> greater the number of rows, and
>   b) If it's small enough, an in-ram database (":MEMORY:") Roger
> mentions might be feasible.
>
> FTP error below:
>
> N:\>ftp ftp.smokemytool.com
> Connected to ftp.smokemytool.com.
> 220-FileZilla Server version 0.9.24 beta
> 220-written by Tim Kosse (tim.ko...@gmx.de)
> 220 Please visit http://sourceforge.net/projects/filezilla/
> User (ftp.smokemytool.com:(none)): anonymous
> 331 Password required for anonymous
> Password:
> 421 Too many users logged in for this account. Try again later.
> Login failed.
> ftp>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Jim Dodgen
j...@dodgen.us
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Transaction Rate and speed...

[Nuzzi]

Griggs, Donald-3 wrote:
> 
> Regarding:
> sorry.. no luck either... or I have a personal problem with your ftp
> server -- he doesn't like my anonymous login... ;)
> 
> ==
> 
> I get an error of "421 Too many users logged in for this account."
> 
> I guess we smoked your tool. ;-)
> 
> 
> Pretty much echoing Roger Binn's recent comment, this posting by D. Hipp
> may be of interest:
> http://www.nabble.com/Behavior-of-SELECT-queries-during-transaction-to13
> 249797.html#a13257203
> 
> Just curious -- are you at liberty to say what this application is with
> this firehose of data?
> 
> Also, I know you required multiple-millions of actions per minute -- but
> since you'll be updating some rows rather than inserting them all, about
> how big do you anticpate the database being?   That can make a big
> difference because:
>a) Building and consulting the table's index will get longer the
> greater the number of rows, and
>b) If it's small enough, an in-ram database (":MEMORY:") Roger
> mentions might be feasible.
> 
> FTP error below:
> 
> N:\>ftp ftp.smokemytool.com
> Connected to ftp.smokemytool.com.
> 220-FileZilla Server version 0.9.24 beta
> 220-written by Tim Kosse (tim.ko...@gmx.de)
> 220 Please visit http://sourceforge.net/projects/filezilla/
> User (ftp.smokemytool.com:(none)): anonymous
> 331 Password required for anonymous
> Password:
> 421 Too many users logged in for this account. Try again later.
> Login failed.
> ftp> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

Sorry All, it is a very unreliable home web server.  I upped the anonymous
connections, so, theoretically, it should now work.

Anyway, to answer one question, the DB can get pretty big.  It is an
application that has to keep track of soundings in cells that cover a
certain area. The data comes fast and we have to record every bit of it, but
it could get crazy big to keep all the individual soundings, so we are try
keep the relevant data for each cell (min, max, sum, sum squares, hits,
etc.).  We used to do this in memory but were exploring ways to not do that
anymore.  So, this was my first try at some kind of file based option.  I am
not optimistic, though, but was just hoping it was more my ignorance rather
than capabilities.  Also, there will be many clients updating and reading
from the "server."  I think it will have to be a very complex combined file
and memory approach.

Thanks for suggestions, and I will look at the links, but I am pretty sure I
will be starting to look at other options very soon.

John
-- 
View this message in context: 
http://www.nabble.com/SQLite-Transaction-Rate-and-speed...-tp22379931p22383529.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] Thread-safety and sqlite3_stmt objects

[Dan]

On Mar 7, 2009, at 1:31 AM, Shawn Wilsher wrote:

> Hey all,
>
> I've been looking online for a bit trying to establish what  
> protections, if
> any, are associated with sqlite3_stmt objects.  It's clearly  
> documented that
> sqlite3 objects' access is serialized across threads, but I cannot  
> find
> anything about sqlite3_stmt.  I don't actually care either way, but if
> SQLite protects it internally, I don't want to add additional  
> overhead by
> protecting it myself.

Each database connection (sqlite3) has associated with it a single
mutex. Many of the operations on an sqlite3_step* object are protected
internally by this mutex (i.e. sqlite3_step()). But some are not,
especially the data access functions like sqlite3_data_count() etc.

Accessing values using the sqlite3_column_XXX() API is nearly  
threadsafe.
If some other thread calls sqlite3_step() to advance the statement while
the first thread is calling sqlite3_column_XXX(), then you could  
conceivably
get a problem. But multiple threads using the sqlite3_column_XXX()  
functions
is safe.

So I suppose the only correct answer is "no, they are not threadsafe
objects".

Why do you want to use a single sqlite3_stmt* from multiple threads
at the same time?

Dan.



> Could someone please clarify this (and maybe add some documentation)?
>
> Cheers,
>
> Shawn Wilsher
> Mozilla Developer
> ___
> 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] Thread-safety and sqlite3_stmt objects

[Shawn Wilsher]

On Fri, Mar 6, 2009 at 9:26 PM, Dan  wrote:

> Why do you want to use a single sqlite3_stmt* from multiple threads
> at the same time?

Really the only thing I need to access is sqlite3_sql to copy the statement
to another thread.

We have an asynchronous API that we expose to add-ons and core code in
Mozilla.  Right now, when a consumer calls executeAsync on a statement, we
make a new copy of the statement, transfer the bindings, and pass the new
one to the thread that executes the statement.  However, profiling shows
that creating a statement can often be an expensive operation.  I'm looking
at just giving the second thread the statement, and if the original thread
needs the statement again, I want to clone it off of the original.

Note: I'm leaving out some details here that probably aren't important.

Cheers,

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

[sqlite] Import table in SQLite

[kin foo]

Hi I'm wondering if it is possible to import a table from an SQLite database
to another SQLite database.
Thanks!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] import a CSV-File

[Sylvain Pointeau]

from my experience,
sed will read line by line, which is not good for the CSV file.(one field
can be on multiple lines).

why sqlite is not enhanced to handle fully the csv file format?

Cheers,
Sylvain


On Fri, Mar 6, 2009 at 11:33 PM, John Machin  wrote:

> On 7/03/2009 6:16 AM, Rich Shepard wrote:
> > On Fri, 6 Mar 2009, Hans-Martin wrote:
> >
> >> It seems that there is no way to get rid of the embedded CR/LF without
> parse
> >> the complete output.
> >
> >Use sed. That's what it's for.
>
> Has anyone considered that getting rid of the embedded CR/LF is
> destroying information?
>
> Has anyone considered the possibility that SQLite .import could be made
> to support the CSV format?
>
> Alternative: read the CSV file and update the SQLite3 database using a
> scripting language with the appropriate capabilities (Python definitely,
>  Perl surely, others ...)
>
> Cheers,
> John
>
> ___
> 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] import a CSV-File

[Marek Paul]

I use an excellent piece of software called AutoIT
(http://www.autoitscript.com). AutoIt is a very flexible easy to use
programming language which supports SQLite. I used it to write a csv
import program for SQLite. Took me 10 minutes. If you would like to
see the source as an example just say.

Cheers,

Marek

2009/3/7 Sylvain Pointeau :
> from my experience,
> sed will read line by line, which is not good for the CSV file.(one field
> can be on multiple lines).
>
> why sqlite is not enhanced to handle fully the csv file format?
>
> Cheers,
> Sylvain
>
>
> On Fri, Mar 6, 2009 at 11:33 PM, John Machin  wrote:
>
>> On 7/03/2009 6:16 AM, Rich Shepard wrote:
>> > On Fri, 6 Mar 2009, Hans-Martin wrote:
>> >
>> >> It seems that there is no way to get rid of the embedded CR/LF without
>> parse
>> >> the complete output.
>> >
>> >    Use sed. That's what it's for.
>>
>> Has anyone considered that getting rid of the embedded CR/LF is
>> destroying information?
>>
>> Has anyone considered the possibility that SQLite .import could be made
>> to support the CSV format?
>>
>> Alternative: read the CSV file and update the SQLite3 database using a
>> scripting language with the appropriate capabilities (Python definitely,
>>  Perl surely, others ...)
>>
>> Cheers,
>> John
>>
>> ___
>> 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
>



-- 
Don't argue with an idiot; people watching may not be able to tell the
difference.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users