Re: [sqlite] how to get file handle from sqlite3 object?

2007-10-10 Thread Vitali Lovich
Two approaches - use sqlite, or use OS code. Use proper filesystem synchronization as appropriate for the given OS & filesystem, where you guarantee that your db copy is the only one that holds an exclusive lock. Then do the file copy and release the lock. The better approach, IMHO would be to

[sqlite] Most basic of questions

2007-10-10 Thread Brian Rowlands (Greymouth High School)
Hi I'm absolutely new to sqlite which I'm using with a perl project. I did a test script: use strict; use DBI; my $dbfile = 'H:\trythis.s3db'; my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","",{RaiseError => 1}); my $sql = "SELECT name FROM Fields ORDER BY name"; my $sth =

Re: [sqlite] Re: user defined function

2007-10-10 Thread Aviad Harell
Hey Igor, Thanks for your replay. I tried not to use user defined function to do it. This code is working on SqlServer but on SQLite I get misuse of aggregate: (SUM(SALES)). SELECT CUSTOMER, PRODUCT, [RANK] FROM (SELECT CUSTOMER, PRODUCT,

Re: [sqlite] user defined function

2007-10-10 Thread drh
"Aviad Harell" <[EMAIL PROTECTED]> wrote: > Hey Igor, > > Thanks for your replay. I tried not to use user defined function to do it. > This code is working on SqlServer but on SQLite I get misuse of aggregate: > (SUM(SALES)). > See http://www.sqlite.org/cvstrac/tktview?tn=2652

[sqlite] Re: Re: user defined function

2007-10-10 Thread Igor Tandetnik
Aviad Harell <[EMAIL PROTECTED]> wrote: Thanks for your replay. I tried not to use user defined function to do it. This code is working on SqlServer but on SQLite I get misuse of aggregate: (SUM(SALES)). Try this - it should do the same thing: select c customer, p1 product, sum(case when s1 <

Re: [sqlite] auto library function loading

2007-10-10 Thread John Stanton
The Sqlite API is set at a low level for very sound reasons. It is suitable for incorporating Sqlite into other software such as TCL, Perl, Javascript etc or to build an API for specific embedded applications. If higher level features are embedded in the core Sqlite it becomes bloated with

Re: [sqlite] how to get file handle from sqlite3 object?

2007-10-10 Thread John Stanton
There is a good case to have an Sqlite API call to take a snapshot of a database. It would integrate with the locking logic and secure an exclusive lock before taking the snapshot. That is a safer and handier approach than extracting a file descriptor and perhaps creating mayhem. Cyrus

Re: [sqlite] Most basic of questions

2007-10-10 Thread John Stanton
Brian Rowlands (Greymouth High School) wrote: Hi I'm absolutely new to sqlite which I'm using with a perl project. I did a test script: use strict; use DBI; my $dbfile = 'H:\trythis.s3db'; my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","",{RaiseError => 1}); my $sql = "SELECT name FROM

Re: [sqlite] how to get file handle from sqlite3 object?

2007-10-10 Thread Cyrus Durgin
Agreed - it seems like this would be useful enough functionality that I'm not sure everyone who needs it should be reinventing the wheel... So is it fair to say that the sqlite3_file API methods are not useful for this purpose? The docs are a bit sparse regarding their intended purposes. On

[sqlite] Re: Reading error outside the while

2007-10-10 Thread Igor Tandetnik
[EMAIL PROTECTED] wrote: while(sqlite3_step(pStat) != SQLITE_DONE) { switch (sqlite3_step(pStat)) { You call sqlite3_step twice on every iteration, which means you are only looking at every other row. That's probably not what you wanted. case SQLITE_ROW: /*Get

RE: [sqlite] how to get file handle from sqlite3 object?

2007-10-10 Thread Virgilio Alexandre Fornazin
There is a better workaround: get the code from the .dump command of sqlite3 utility and use it... it creates a fresh copy of your database like using sqlite3 dbold .dump | sqlite3 newdb (not sure about the syntax, there´s a example of this case on internet) -Original Message- From:

RE: [sqlite] how to get file handle from sqlite3 object?

2007-10-10 Thread Joe Wilson
> There is a better workaround: get the code from the .dump command of sqlite3 > utility and use it... > it creates a fresh copy of your database like using > > sqlite3 dbold .dump | sqlite3 newdb (not sure about the syntax, there´s a > example of this > case on internet) This will always work

[sqlite] Re: Re: Reading error outside the while

2007-10-10 Thread Igor Tandetnik
[EMAIL PROTECTED] wrote: ok thanks i understand my errors, is there a function to count the number of rows ? Not really. You just call sqlite3_step repeatedly, and count how many times you had to do that before it returned SQLITE_DONE. Usually you would combine this with other processing of

Re: [sqlite] Reading error outside the while

2007-10-10 Thread John Stanton
[EMAIL PROTECTED] wrote: John Stanton a écrit : [EMAIL PROTECTED] wrote: Hello, I got an error when I try to read some data outside the while{}, inside the while{} it's ok, an idea ? test.db have just one "table1" and a "field1" with values "one", "two", "three". #include #include

Re: [sqlite] how to get file handle from sqlite3 object?

2007-10-10 Thread John Stanton
You obviously understand the problem. The clean and tidy way to make a copy would be to incorporate it into Sqlite as an API call and use the locking primitives and the open file handle. It would be very fast and safe. Cyrus Durgin wrote: Right, my question was around how to copy the

Re: [sqlite] Reading error outside the while

2007-10-10 Thread John Stanton
John Stanton wrote: [EMAIL PROTECTED] wrote: John Stanton a écrit : [EMAIL PROTECTED] wrote: Hello, I got an error when I try to read some data outside the while{}, inside the while{} it's ok, an idea ? test.db have just one "table1" and a "field1" with values "one", "two", "three".

Re: [sqlite] Reading error outside the while

2007-10-10 Thread [EMAIL PROTECTED]
John Stanton a écrit : John Stanton wrote: [EMAIL PROTECTED] wrote: John Stanton a écrit : [EMAIL PROTECTED] wrote: Hello, I got an error when I try to read some data outside the while{}, inside the while{} it's ok, an idea ? test.db have just one "table1" and a "field1" with values

Re: [sqlite] how to get file handle from sqlite3 object?

2007-10-10 Thread Andy Spencer
On Tue, 9 Oct 2007, Joe Wilson wrote: > --- Cyrus Durgin <[EMAIL PROTECTED]> wrote: > > i'm wondering if there's a "standard" way to get an open file handle from an > > sqlite3 pointer using the C API. anyone know? > > No such function exists, but it would be a useful addition to the API. >

RE: [sqlite] Most basic of questions

2007-10-10 Thread Brian Rowlands (Greymouth High School)
Nice one Clark. Worked a treat. -Original Message- From: Clark Christensen [mailto:[EMAIL PROTECTED] Sent: Thursday, 11 October 2007 5:47 a.m. To: sqlite-users@sqlite.org Subject: Re: [sqlite] Most basic of questions As you've discovered, $sth->finish doesn't quite do the job. I've

Re: [sqlite] how to get file handle from sqlite3 object?

2007-10-10 Thread Joe Wilson
--- Andy Spencer <[EMAIL PROTECTED]> wrote: > On Tue, 9 Oct 2007, Joe Wilson wrote: > > > --- Cyrus Durgin <[EMAIL PROTECTED]> wrote: > > > i'm wondering if there's a "standard" way to get an open file handle from > > > an > > > sqlite3 pointer using the C API. anyone know? > > > > No such

Re: [sqlite] how to get file handle from sqlite3 object?

2007-10-10 Thread John Stanton
Andy, Thank you for your note. It is very useful. Andy Spencer wrote: On Tue, 9 Oct 2007, Joe Wilson wrote: --- Cyrus Durgin <[EMAIL PROTECTED]> wrote: i'm wondering if there's a "standard" way to get an open file handle from an sqlite3 pointer using the C API. anyone know? No such

[sqlite] sqlite3_prepare vs. sqlite3_get_table

2007-10-10 Thread Uma Krishnan
Hello, Is sqlite3_get_table a legitimate call when you want to get a bunch of rows? what are the pros and cons as against using sqlite3_prepare/step. Thanks in advance. Uma

Re: [sqlite] how to get file handle from sqlite3 object?

2007-10-10 Thread Andy Spencer
On Wed, 10 Oct 2007, Joe Wilson wrote: > > At Schrodinger, we added two functions to our version of sqlite3: > > > > /* The sqlite3 APIs to get file descriptors fo the open files */ > > int sqlite3_get_database_file_fd( sqlite3* sqlite3_db_ptr ); > > int sqlite3_get_journal_file_fd( sqlite3*

Re: [sqlite] how to get file handle from sqlite3 object?

2007-10-10 Thread Joe Wilson
--- Andy Spencer <[EMAIL PROTECTED]> wrote: > I'm not sure what you mean by database "main", and I did not > write the functions. The default database name (really more of an alias) is "main". So "SELECT * from foo" and "SELECT * from main.foo" are the same. Attached databases are known by the

Re: [sqlite] how to get file handle from sqlite3 object?

2007-10-10 Thread Andy Spencer
On Wed, 10 Oct 2007, Joe Wilson wrote: > --- Andy Spencer <[EMAIL PROTECTED]> wrote: > > I'm not sure what you mean by database "main", and I did not > > write the functions. > > The default database name (really more of an alias) is "main". > So "SELECT * from foo" and "SELECT * from main.foo"

[sqlite] Sqlite 3.5.1 stability

2007-10-10 Thread Prakash Reddy Bande
Hi, Is Sqlite 3.5.1 a stable release? Should I move from 3.4.0 to 3.5.1. The reason I am asking this is that we get a linker error _TryEnterCriticalSection and to fix it we must get http://www.sqlite.org/cvstrac/chngview?cn=4399 If I get the files in this check-in can I rely on the fact

[sqlite] Making BEGIN IMMEDIATE the default.

2007-10-10 Thread Scott Hess
We've just had a bit of discussion on the Google Gears team about some cases where failure of an UPDATE/DELETE/INSERT while within a transaction is unexpected. Well, that and that when you're multi-threaded you can hit some hard-to-understand cases. One suggestion was to use BEGIN IMMEDIATE for

Re: [sqlite] odd behavior difference between LIKE and GLOB

2007-10-10 Thread Peter A. Friend
On Oct 10, 2007, at 2:12 PM, [EMAIL PROTECTED] wrote: "Peter A. Friend" <[EMAIL PROTECTED]> wrote: As near as I can tell from the documentation, the only difference between LIKE and GLOB is the wildcard character and that LIKE is case- insensitive (unless configuration overrides that). Is

Re: [sqlite] Making BEGIN IMMEDIATE the default.

2007-10-10 Thread John Stanton
If you are going to use BEGIN IMMEDIATE why not just enclose the transaction in some form of lock like a mutex? Scott Hess wrote: We've just had a bit of discussion on the Google Gears team about some cases where failure of an UPDATE/DELETE/INSERT while within a transaction is unexpected.

Re: [sqlite] Making BEGIN IMMEDIATE the default.

2007-10-10 Thread Scott Hess
To clarify, this is for Google Gears, a JavaScript library which includes a Database component which is implemented using SQLite. If we were simply building an app on top of SQLite, then the distinction between BEGIN and BEGIN IMMEDIATE would be no problem - we'd just use the right thing in the

Re: [sqlite] auto library function loading

2007-10-10 Thread Ken
.load /home/users/LIB/sqlite3/user_ext.so unable to open shared library [/home/users/LIB/sqlite3/user_ext.so] sqlite> Any ideas as to why the .load pragma is failing? The file exists, built as follows: gcc -rdynamic -fPIC

Re: [sqlite] Making BEGIN IMMEDIATE the default.

2007-10-10 Thread Scott Hess
Clarify^2: I'm suggesting for our use of SQLite in Google Gears. NOT for SQLite itself. Though Ken's suggestion of a PRAGMA might be interesting for SQLite core... -scott On 10/10/07, Scott Hess <[EMAIL PROTECTED]> wrote: > To clarify, this is for Google Gears, a JavaScript library which >

Re: [sqlite] Making BEGIN IMMEDIATE the default.

2007-10-10 Thread Ken
Scott, I found that using begin imediate was very helpful. But it didn't quite fix everything. I ended up wrapping the begin immediate with some retry logic when a sqlite busy is encounted. Once you get the transaction you shouldn't have any isuses with DML. A pragma that could configure the

Re: [sqlite] Making BEGIN IMMEDIATE the default.

2007-10-10 Thread Scott Hess
Such a thing might look something like the attached patch. I'm not enthusiastic about using an extra byte in the sqlite3 structure. If it could be reduced to a flag (default deferred, default immediate), that might be worthwhile. -scott On 10/10/07, Ken <[EMAIL PROTECTED]> wrote: > Scott, > >

Re: [sqlite] Making BEGIN IMMEDIATE the default.

2007-10-10 Thread drh
"Scott Hess" <[EMAIL PROTECTED]> wrote: > We've just had a bit of discussion on the Google Gears team about some > cases where failure of an UPDATE/DELETE/INSERT while within a > transaction is unexpected. Well, that and that when you're > multi-threaded you can hit some hard-to-understand cases.

Re: [sqlite] Making BEGIN IMMEDIATE the default.

2007-10-10 Thread Scott Hess
On 10/10/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > "Scott Hess" <[EMAIL PROTECTED]> wrote: > > We've just had a bit of discussion on the Google Gears team about some > > cases where failure of an UPDATE/DELETE/INSERT while within a > > transaction is unexpected. Well, that and that when

Re: [sqlite] how to get file handle from sqlite3 object?

2007-10-10 Thread Joe Wilson
> > sqlite3OsFileHandle no longer exists. > Can you tell me the first sqlite3 version to include this change? I'm guessing that it's 3.5.0 when most of the code was revised. Yahoo! oneSearch: Finally,

Re: [sqlite] Making BEGIN IMMEDIATE the default.

2007-10-10 Thread Andy Goth
On Thu, 11 Oct 2007 02:40:22 +, drh wrote > The BEGIN, ROLLBACK, and/or COMMIT only happen on the outermost > "transaction". Of course, it is kind of silly to nest > as shown above. But this is useful, for example, when each > "db transaction" is really in a separate procedure and the >

Re: [sqlite] Making BEGIN IMMEDIATE the default.

2007-10-10 Thread drh
"Scott Hess" <[EMAIL PROTECTED]> wrote: > > > > Many (most?) of the other teams using SQLite in situations > > similar to Gears have their own separate methods for starting, > > committing, and rolling back transactions. They don't run > > BEGIN, COMMIT, or ROLLBACK statements - they call their