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

[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, mobile search 
that gives answers, not web links. 
http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC

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

Re: [sqlite] Making BEGIN IMMEDIATE the default.

[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
> procedures are nested.

Wow, I didn't know [transaction] nests!  Thanks.  I had written some
untrustworthy code to only invoke [transaction] on the outermost stack frame;
it's great to know that I can get rid of it.

>From the documentation:

"Also, BEGIN does not nest, so you have to make sure no other transactions are
active before starting a new one. The 'transaction' method takes care of all
of these details automatically."

You might want to be a little bit more clear about the fact that [transaction]
nests even though BEGIN does not.

-- 
Andy Goth
<[EMAIL PROTECTED]>


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

Re: [sqlite] Making BEGIN IMMEDIATE the default.

[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 own
> > built-in methods which in turn runs BEGIN, COMMIT, and
> > ROLLBACK on the user's behalf.  If you used this approach,
> > then you could easily revise your method to call BEGIN IMMEDIATE
> > instead of just BEGIN.  You could also do the BUSY retry
> > handling that Ken suggests.
> 
> Indeed, there has been past discussion about whether we should do some
> sort of transaction API which integrated with the language (for
> instance, automating ROLLBACK on uncaught exceptions).  It may be that
> this is the time for that to come back to the fore.
> 

Exactly.  Notice how the TCL bindings do this.  If "db" is
the object that is your database connection then you do:

db transaction {
# lots of other code.
db eval {--SQL} ...
# more code
}

And if an exception gets thrown inside the {...} and doesn't
get caught before leaving the {...} the transaction is 
automatically rolled back.  Furthermore, you can nest the
"db transaction" implementations:

db transaction {
db transaction {
   db transaction {
   #...
   }
}
}

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
procedures are nested.

This is all relatively easy to implement for TCL where every
string is also a lambda procedure.  It isn't clear to me if
or how you could do the same in javascript.  But if you have
a javascript guru who can pull it off, it would be neat.

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


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

Re: [sqlite] Making BEGIN IMMEDIATE the default.

[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 you're
> > multi-threaded you can hit some hard-to-understand cases.
> >
> > One suggestion was to use BEGIN IMMEDIATE for explicit transactions,
> > rather than BEGIN.  And it seemed to us like that might be a
> > reasonable default, given that Gears encourages multiple threads
> > hitting the same database.
> >
> > It looks pretty easy to make this happen (one-line mod to parse.y),
> > and BEGIN DEFERRED is supported syntax for those who really do mean
> > that.  Does anyone have a strong argument for why we're descending
> > into a pit of despair by considering this?
>
> 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 own
> built-in methods which in turn runs BEGIN, COMMIT, and
> ROLLBACK on the user's behalf.  If you used this approach,
> then you could easily revise your method to call BEGIN IMMEDIATE
> instead of just BEGIN.  You could also do the BUSY retry
> handling that Ken suggests.

Indeed, there has been past discussion about whether we should do some
sort of transaction API which integrated with the language (for
instance, automating ROLLBACK on uncaught exceptions).  It may be that
this is the time for that to come back to the fore.

> If you really want to use SQL instead of a separate method,
> I would suggest a compile-time switch to make IMMEDIATE the
> default in place of DEFERRED - not a pragma.  We already have
> way too many pragmas.  I will be happy to add a compile-time
> option to make IMMEDATE the default behavior.  I will require
> rather more convincing to add another pragma.

That's a reasonable position to take.  For Gears, it would be super
easy to just make the change directly to parse.y, and I _think_ that I
understand things well enough to implement
SQLITE_TRANSACTION_DEFAULT_IMMEDIATE or something along those lines.

Thinking out loud, it looks to me like the change would be something like:

  cmd ::= BEGIN transtype(Y) trans_opt.  {sqlite3BeginTransaction(pParse, Y);}
  trans_opt ::= .
  trans_opt ::= TRANSACTION.
  trans_opt ::= TRANSACTION nm.
  %type transtype {int}
+ %ifdef SQLITE_TRANSACTION_DEFAULT_IMMEDIATE
+ transtype(A) ::= . {A = TK_IMMEDIATE;}
+ %endif
+ %ifndef SQLITE_TRANSACTION_DEFAULT_IMMEDIATE
  transtype(A) ::= . {A = 0;}
+ %endif
  transtype(A) ::= DEFERRED(X).  {A = @X;}
  transtype(A) ::= IMMEDIATE(X). {A = @X;}
  transtype(A) ::= EXCLUSIVE(X). {A = @X;}
  cmd ::= COMMIT trans_opt.  {sqlite3CommitTransaction(pParse);}
  cmd ::= END trans_opt. {sqlite3CommitTransaction(pParse);}
  cmd ::= ROLLBACK trans_opt.{sqlite3RollbackTransaction(pParse);}

I can wrap that up better tomorrow.  And maybe even think of a better
define than  SQLITE_TRANSACTION_DEFAULT_IMMEDIATE :-).

-scott

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

Re: [sqlite] Making BEGIN IMMEDIATE the default.

[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.
> 
> One suggestion was to use BEGIN IMMEDIATE for explicit transactions,
> rather than BEGIN.  And it seemed to us like that might be a
> reasonable default, given that Gears encourages multiple threads
> hitting the same database.
> 
> It looks pretty easy to make this happen (one-line mod to parse.y),
> and BEGIN DEFERRED is supported syntax for those who really do mean
> that.  Does anyone have a strong argument for why we're descending
> into a pit of despair by considering this?
> 

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 own
built-in methods which in turn runs BEGIN, COMMIT, and
ROLLBACK on the user's behalf.  If you used this approach,
then you could easily revise your method to call BEGIN IMMEDIATE
instead of just BEGIN.  You could also do the BUSY retry
handling that Ken suggests.

If you really want to use SQL instead of a separate method,
I would suggest a compile-time switch to make IMMEDIATE the
default in place of DEFERRED - not a pragma.  We already have
way too many pragmas.  I will be happy to add a compile-time
option to make IMMEDATE the default behavior.  I will require
rather more convincing to add another pragma.

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



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

Re: [sqlite] Making BEGIN IMMEDIATE the default.

[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,
>
> 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 default  begin 
> "deffered/immediate/exclusive" would be nice :)
>
> Ken
>
>
> Scott Hess <[EMAIL PROTECTED]> wrote: 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 appropriate places.  This is a little bit of a
> departure from using SQLite in an embedded environment.
>
> -scott
>
>
> On 10/10/07, John Stanton  wrote:
> > 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.  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 explicit transactions,
> > > rather than BEGIN.  And it seemed to us like that might be a
> > > reasonable default, given that Gears encourages multiple threads
> > > hitting the same database.
> > >
> > > It looks pretty easy to make this happen (one-line mod to parse.y),
> > > and BEGIN DEFERRED is supported syntax for those who really do mean
> > > that.  Does anyone have a strong argument for why we're descending
> > > into a pit of despair by considering this?
> > >
> > > Thanks,
> > > scott
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
Index: src/build.c
===
RCS file: /sqlite/sqlite/src/build.c,v
retrieving revision 1.445
diff -u -r1.445 build.c
--- src/build.c 4 Oct 2007 18:11:16 -   1.445
+++ src/build.c 11 Oct 2007 00:02:21 -
@@ -3092,6 +3092,7 @@
 
   v = sqlite3GetVdbe(pParse);
   if( !v ) return;
+  if( !type ) type = db->dfltTransMode;
   if( type!=TK_DEFERRED ){
 for(i=0; inDb; i++){
   sqlite3VdbeAddOp(v, OP_Transaction, i, (type==TK_EXCLUSIVE)+1);
Index: src/parse.y
===
RCS file: /sqlite/sqlite/src/parse.y,v
retrieving revision 1.234
diff -u -r1.234 parse.y
--- src/parse.y 21 Aug 2007 10:44:16 -  1.234
+++ src/parse.y 11 Oct 2007 00:02:21 -
@@ -113,7 +113,7 @@
 trans_opt ::= TRANSACTION.
 trans_opt ::= TRANSACTION nm.
 %type transtype {int}
-transtype(A) ::= . {A = TK_DEFERRED;}
+transtype(A) ::= . {A = 0;}
 transtype(A) ::= DEFERRED(X).  {A = @X;}
 transtype(A) ::= IMMEDIATE(X). {A = @X;}
 transtype(A) ::= EXCLUSIVE(X). {A = @X;}
Index: src/pragma.c
===
RCS file: /sqlite/sqlite/src/pragma.c,v
retrieving revision 1.149
diff -u -r1.149 pragma.c
--- src/pragma.c31 Aug 2007 18:34:59 -  1.149
+++ src/pragma.c11 Oct 2007 00:02:21 -
@@ -84,6 +84,17 @@
 }
 #endif /* ifndef SQLITE_OMIT_AUTOVACUUM */
 
+static int getTransactionMode(const char *z){
+  int i;
+  if( 0==sqlite3StrICmp(z, "deferred") ) return TK_DEFERRED;
+  if( 0==sqlite3StrICmp(z, "immediate") ) return TK_IMMEDIATE;
+  if( 0==sqlite3StrICmp(z, "exclusive") ) return TK_EXCLUSIVE;
+  i = atoi(z);
+  if( i==1 ) return TK_IMMEDIATE;
+  if( i==2 ) return TK_EXCLUSIVE;
+  return TK_DEFERRED;
+}
+
 #ifndef SQLITE_OMIT_PAGER_PRAGMAS
 /*
 ** Interpret the given string as a temp db location. Return 1 for file
@@ -492,6 +503,24 @@
   }else
 #endif
 
+  /*
+  **  PRAGMA [database.]transaction_mode
+  **  PRAGMA [database.]transaction_mode = 0 | deferred | 1 | immediate | 2 | 
exclusive
+  **
+  */
+  if( sqlite3StrICmp(zLeft,"transaction_mode")==0 ){
+if( sqlite3ReadSchema(pParse) ) goto pragma_out;
+if( !zRight ){
+  int mode = 0;
+  if( db->dfltTransMode==TK_IMMEDIATE ) mode = 1;
+  if( db->dfltTransMode==TK_EXCLUSIVE ) mode = 2;
+  returnSingleInt(pParse, "transaction_mode", mode );
+}else{
+  db->dfltTransMode = getTransactionMode(zRight);
+}
+  }else
+
+
 #ifndef SQLITE_OMIT_PAGER_PRAGMAS
   /*
   **  PRAGMA [database.]cache_size
Index: 

Re: [sqlite] Making BEGIN IMMEDIATE the default.

[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
> 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 appropriate places.  This is a little bit of a
> departure from using SQLite in an embedded environment.
>
> -scott
>
>
> On 10/10/07, John Stanton <[EMAIL PROTECTED]> wrote:
> > 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.  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 explicit transactions,
> > > rather than BEGIN.  And it seemed to us like that might be a
> > > reasonable default, given that Gears encourages multiple threads
> > > hitting the same database.
> > >
> > > It looks pretty easy to make this happen (one-line mod to parse.y),
> > > and BEGIN DEFERRED is supported syntax for those who really do mean
> > > that.  Does anyone have a strong argument for why we're descending
> > > into a pit of despair by considering this?
> > >
> > > Thanks,
> > > scott
>

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

Re: [sqlite] auto library function loading

[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 -I../sqliteSrc/sqlite-3.4.1/src -shared user_ext.c -o 
user_ext.so


Thanks,
Ken



Ken <[EMAIL PROTECTED]> wrote: John very eloquently stated. I completely agree 
with you regarding the ant-bloatware philosophy.

OK, I found the answer:

http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions


Which I'll be trying shortly!




John Stanton  wrote: 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 components unused by most applications.

Sqlite's anti-bloatware philosophy is one of its most endearing 
qualities.  It is not easy to retain simplicity.  It reminds me of a 
quotation from ones of Pascal's famous letters "I apologize for making 
this letter so long.  I have not had time to make it shorter".

If you really are concerned with making custom functions available 
without wrappers you can compile them into the Sqlite library alongside 
the predefined functions.  Sqlite lets you do that very simply.

We use a fairly extensive library of custom functions, including ones 
which implement Javascript as an embedded scripting language and another 
set which implements a fixed point decimal number type.  A wrapper 
around sqlite3_open loads them and performs other activities necessary 
to cleanly integrate Sqlite into the applications.  Sqlite upgrades are 
consequently straightforward.

Ken wrote:
> John, 
> 
> Kind of defeats the point of "automatic". 
>  
> Using a wrapper for application code is perfectly legitimate. However it does 
> not address the sqlite3 command line tool.
> 
> The ability to automatically load a library of functions via a dll would be a 
> really nice feature and could be used to promote user contributions for 
> library development for sqlite. 
> 
> For example math function library, or string function library, even the VFS 
> i/o routines could be loaded in this manner. 
> 
> Ken
> 
> John Stanton  wrote: Ken wrote:
> 
>>Is there a way for SQLITE to automatically load user defined functions at 
>>database instantiation or upon database connection? 
>>   
>>Can it test for a .so/.dll and perform a call to load user functions? If 
>> the .dll does not exist maybe issue a warning? 
>>
>>Just a thought as a way to allow users to create their own  presentation 
>>library to implement things like math functions/ presentation functions etc 
>>without having to make and maintain code changes to the core software.
>>
> 
> 
> Build your own wrapper for the sqlite3_open API function.  You can have 
> some form of library of user defined functions which you bind just after 
> you have made a DB connection.
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 


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

Re: [sqlite] Making BEGIN IMMEDIATE the default.

[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 default  begin "deffered/immediate/exclusive" 
would be nice :)

Ken


Scott Hess <[EMAIL PROTECTED]> wrote: 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 appropriate places.  This is a little bit of a
departure from using SQLite in an embedded environment.

-scott


On 10/10/07, John Stanton  wrote:
> 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.  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 explicit transactions,
> > rather than BEGIN.  And it seemed to us like that might be a
> > reasonable default, given that Gears encourages multiple threads
> > hitting the same database.
> >
> > It looks pretty easy to make this happen (one-line mod to parse.y),
> > and BEGIN DEFERRED is supported syntax for those who really do mean
> > that.  Does anyone have a strong argument for why we're descending
> > into a pit of despair by considering this?
> >
> > Thanks,
> > scott

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

Re: [sqlite] Making BEGIN IMMEDIATE the default.

[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 appropriate places.  This is a little bit of a
departure from using SQLite in an embedded environment.

-scott


On 10/10/07, John Stanton <[EMAIL PROTECTED]> wrote:
> 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.  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 explicit transactions,
> > rather than BEGIN.  And it seemed to us like that might be a
> > reasonable default, given that Gears encourages multiple threads
> > hitting the same database.
> >
> > It looks pretty easy to make this happen (one-line mod to parse.y),
> > and BEGIN DEFERRED is supported syntax for those who really do mean
> > that.  Does anyone have a strong argument for why we're descending
> > into a pit of despair by considering this?
> >
> > Thanks,
> > scott

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

Re: [sqlite] Making BEGIN IMMEDIATE the default.

[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.  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 explicit transactions,
rather than BEGIN.  And it seemed to us like that might be a
reasonable default, given that Gears encourages multiple threads
hitting the same database.

It looks pretty easy to make this happen (one-line mod to parse.y),
and BEGIN DEFERRED is supported syntax for those who really do mean
that.  Does anyone have a strong argument for why we're descending
into a pit of despair by considering this?

Thanks,
scott

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




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

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

[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 there some
detail about the behavior of LIKE that I have missed?



See http://www.sqlite.org/optoverview.html#like_opt


Ah, I had read that but obviously not close enough. So basically,  
since the SQL standard requires LIKE to be case-insensitive and the  
default collating sequence is BINARY, the LIKE optimization gets  
disabled. The only way to allow for LIKE to be optimized *and*  
provide a case-insensitive match is to use the NOCASE collation.


Many thanks,

Peter



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

[sqlite] Making BEGIN IMMEDIATE the default.

[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 explicit transactions,
rather than BEGIN.  And it seemed to us like that might be a
reasonable default, given that Gears encourages multiple threads
hitting the same database.

It looks pretty easy to make this happen (one-line mod to parse.y),
and BEGIN DEFERRED is supported syntax for those who really do mean
that.  Does anyone have a strong argument for why we're descending
into a pit of despair by considering this?

Thanks,
scott

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

[sqlite] Sqlite 3.5.1 stability

[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 that 3.5.1
will be as stable as 3.4.0.

 

The reason I am moving over is that the sources for fts are based 3.5.1.
How can I get the sources of fts based on 3.4.0 if I have to use 3.4.0?

 

Regards,

 

Prakash Reddy Bande

Altair Engg. Inc,

Troy, MI

 

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

[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" are the same.
> Attached databases are known by the name you've attached them as.
> 

Then, yes, it would be the "main" database, since we do not attach
any other databases.

> > The functions use the pager to access the file descriptors:
> ...
> > int sqlite3pager_get_database_file_fd(Pager *pPager)
> > {
> > return sqlite3OsFileHandle(pPager->fd);
> 
> Okay, I see. sqlite3OsFileHandle no longer exists.
> But then again, for your purposes it's moot, since FD_CLOEXEC 
> is done for you.

Thanks for the information.
Can you tell me the first sqlite3 version to include this change?


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

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

[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 name you've attached them as.

> The functions use the pager to access the file descriptors:
...
> int sqlite3pager_get_database_file_fd(Pager *pPager)
> {
> return sqlite3OsFileHandle(pPager->fd);

Okay, I see. sqlite3OsFileHandle no longer exists.
But then again, for your purposes it's moot, since FD_CLOEXEC 
is done for you.



  

Don't let your dream ride pass you by. Make it a reality with Yahoo! Autos.
http://autos.yahoo.com/index.html
 



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

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

[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* sqlite3_db_ptr );
> 
> Do your functions always just return the fd of the first database "main"?
> 
Hi Joe.

I'm not sure what you mean by database "main", and I did not
write the functions.

The functions use the pager to access the file descriptors:

In pager.c:

int sqlite3pager_get_database_file_fd(Pager *pPager)
{
return sqlite3OsFileHandle(pPager->fd);
}

int sqlite3pager_get_journal_file_fd(Pager *pPager)
{
if ( !pPager->journalOpen )
return -1;
   
return sqlite3OsFileHandle(pPager->jfd);
}

In fds.c (file added by Schrodinger):

int sqlite3_get_database_file_fd( sqlite3* sqlite3_db_p )
{
Pager *pPager;
if ( !sqlite3_db_p || !sqlite3_db_p->aDb || !sqlite3_db_p->aDb[0].pBt)
return -1;

pPager = sqlite3BtreePager(sqlite3_db_p->aDb[0].pBt);

if ( !pPager )
return -1;

return sqlite3pager_get_database_file_fd(pPager);
}

int sqlite3_get_journal_file_fd( sqlite3* sqlite3_db_p )
{
Pager *pPager;
if ( !sqlite3_db_p || !sqlite3_db_p->aDb || !sqlite3_db_p->aDb[0].pBt)
return -1;

pPager = sqlite3BtreePager(sqlite3_db_p->aDb[0].pBt);

if ( !pPager )
return -1;

return sqlite3pager_get_journal_file_fd(pPager);
}


> SQLite3 now has this code in os_unix.c:
> 
> #ifdef FD_CLOEXEC
>   fcntl(h, F_SETFD, fcntl(h, F_GETFD, 0) | FD_CLOEXEC);
> #endif
> 
That's good to know. Our version (3.3.4, and later 3.4.0) did not
have this.

> Is that your only use for requiring the file descriptor?

Yes.
We set FD_CLOEXEC for the database fd when the database is opened,
and for the journal fd in the pthread_atfork handler.


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

[sqlite] sqlite3_prepare vs. sqlite3_get_table

[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?

[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 function exists, but it would be a useful addition to the API.
Copying the database during an exclusive lock without spawning a process 
for instance...



I agree.

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* sqlite3_db_ptr );

This was done to prevent the open files from being inherited
by forked child processes, using fcntl(fd, F_SETFD, FD_CLOEXEC),
since otherwise those child processes could keep the files open,
thereby preventing deletion of the database files (and the directory
that contains them) in the parent process.

It would be better for us to have similar functions included
as part of the standard sqlite interface.

---

The database files are kept inside project directories, created by
the same client application, to store data about project entries and
their properties. In this application, there is expected to be only a
single client process accessing the database, and the project is locked
by the client to ensure this.

Before adding these functions (in order to set the FD_CLOEXEC flag),
we had problems with the deletion of scratch (temporary) projects
or user deletion of projects, because child processes launched in
the background (such as calculation job processes or browsers for
online help) would by default inherit all open files. The database
files are open essentially the entire time that the project is
open, because we maintain an open transaction at all times
(except for commit or rollback of changes to the project data)
to improve performance and to handle single-level undo of
data modifications (in the project, and in the application state).

---

Andy Spencer  <[EMAIL PROTECTED]>


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




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

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

[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 function exists, but it would be a useful addition to the API.
> > Copying the database during an exclusive lock without spawning a process 
> > for instance...
> 
> I agree.
> 
> 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* sqlite3_db_ptr );

Do your functions always just return the fd of the first database "main"?

> This was done to prevent the open files from being inherited
> by forked child processes, using fcntl(fd, F_SETFD, FD_CLOEXEC),
> since otherwise those child processes could keep the files open,
> thereby preventing deletion of the database files (and the directory
> that contains them) in the parent process.

SQLite3 now has this code in os_unix.c:

#ifdef FD_CLOEXEC
  fcntl(h, F_SETFD, fcntl(h, F_GETFD, 0) | FD_CLOEXEC);
#endif

Is that your only use for requiring the file descriptor?

Another way is to pre-fork() one or more worker processes
before any sockets, files or database connections are made.
These pre-forked processes could take instructions from their 
parent and return information via a pipe(). Of course each 
worker process would have to re-open() all its own resources.



  

Check out the hottest 2008 models today at Yahoo! Autos.
http://autos.yahoo.com/new_cars.html

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

RE: [sqlite] Most basic of questions

[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 found
if I simply

undef $sth;

before disconnecting, it eliminates the message about closing $dbh with
active statement handles.

 -Clark


- Original Message 
From: Brian Rowlands (Greymouth High School)
<[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, October 10, 2007 1:48:49 AM
Subject: [sqlite] Most basic of questions


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 = $dbh->prepare($sql);

if (defined($sth)) {
$sth-> execute();
my @row;
while (@row = $sth->fetchrow_array()) {
print "$row[0]\n";
}
}

sth->finish();

$dbh->disconnect(); 

Can someone kindly tell me why I get displayed: "closing dbh with
active statement handles at H:\Testing sql.pl line 25? By that I mean
which it is necessary to have it displayed? Does it have to be so or can
I somehow cause it not to appear?

I'm awaiting a book "The definitive guide to SQLite" to study but in
the meantime I'd appreciate the help from some kind soul.

Thanks
Brian Rowlands
We must accept finite disappointment, but we must never lose infinite
hope.
Martin Luther King Jr.   








-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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

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

[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.
> Copying the database during an exclusive lock without spawning a process 
> for instance...

I agree.

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* sqlite3_db_ptr );

This was done to prevent the open files from being inherited
by forked child processes, using fcntl(fd, F_SETFD, FD_CLOEXEC),
since otherwise those child processes could keep the files open,
thereby preventing deletion of the database files (and the directory
that contains them) in the parent process.

It would be better for us to have similar functions included
as part of the standard sqlite interface.

---

The database files are kept inside project directories, created by
the same client application, to store data about project entries and
their properties. In this application, there is expected to be only a
single client process accessing the database, and the project is locked
by the client to ensure this.

Before adding these functions (in order to set the FD_CLOEXEC flag),
we had problems with the deletion of scratch (temporary) projects
or user deletion of projects, because child processes launched in
the background (such as calculation job processes or browsers for
online help) would by default inherit all open files. The database
files are open essentially the entire time that the project is
open, because we maintain an open transaction at all times
(except for commit or rollback of changes to the project data)
to improve performance and to handle single-level undo of
data modifications (in the project, and in the application state).

---

Andy Spencer  <[EMAIL PROTECTED]>


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

Re: [sqlite] Reading error outside the while

[[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 "one", 
"two", "three".



#include 
#include 

int main(void)
{
   sqlite3 *db;
   sqlite3_stmt *pStat;

   const char *dbname = "test.db";
   const char *sql = "SELECT * FROM table1";
   const unsigned char *my_array[3];
   int i=0;;
 
   sqlite3_open(dbname, );

   sqlite3_prepare_v2(db, sql, -1, , 0);
  while(sqlite3_step(pStat) == SQLITE_ROW)
   {
my_array[i] = sqlite3_column_text(pStat, 0);
   printf ("%s\n",my_array[i]); // ok

i++;
   }

   for (i = 0; i<3; i++);{
printf ("%s\n", my_array[i]); // error
   }
   sqlite3_finalize(pStat);
   sqlite3_close(db);
   return 0;
}


Fred.



Your program is wrongly structured.  Try this layout:

sqlite3_prepare_V2

while (TRUE) {
  rc = sqlite3_step
  switch (rc) {
case SQLITE_ROW:
  /*Get each column*/
  for (count = 0; count > sqlite3_column_count; count++) {
switch (sqlite3_column_type) {
  case SQLITE_TEXT:
pt = sqlite3_column_text
/*Move text into your output*/
sprintf(outval, "%s", pt);  /*Or some other move.*/
break;
  case SQLITE_INTEGER:
outnbr = sqlite3_column_int;
or
sprintf(outval, "%d", sqlite_column_int(..));
break;
  
  add other types
 }
}
  break;
case SQLITE_DONE:
  sqlite3_finalize
  return from function
case SQLITE_BUSY:
  /*Handle BUSY condition.*/
  break;
default:
  /*Handle error condition.*/
  break;
   } /*switch*/
  }  /*while*/

Now you can handle errors, busy conditions and the return of differring
types from Sqlite.  When you get a pointer to a text value from 
Sqlite it is the programmer's responsibility to move data from that 
pointer into data in your program.


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






Hello John,

Thanks for reply,

I just tested with your code that seem to be more useful, but 
something is wrong :


#include 
#include 


int main(void) {
   sqlite3 *db;
   sqlite3_stmt *pStat;
   const gchar  *dbname = "test.db";
   int  colcnt;


 int  rc;
 int  finished = 0;


  rc = sqlite3_open(dbname, );


 if (rc != SQLITE_OK) {
   printf("error\n");
   exit(1);
 }


   rc = sqlite3_prepare_v2(db, "SELECT * FROM TABLE1", -1, , 0);

  if (rc != SQLITE_OK) {
printf("error\n");
exit(1);
  }


   while(!finished) {

 rc = sqlite3_step(pstat);

   switch (rc) {

   case SQLITE_ROW:
 /*Get each column*/
 for (colcnt = 0; colcnt < sqlite3_column_count(pStat); 
colcnt++) {


 /*Handle returned data according to type.*/


   switch (sqlite3_column_type(pStat, 0)) {
 case SQLITE_TEXT:
   printf("%s %s ", sqlite3_colimn_name(pstat, colcnt),


sqlite3_column_text(pStat,0));


   break;


   case SQLITE_INTEGER:
 printf("%s %d ", sqlite3_column_name(pstat, colcnt),
 sqlite3_column_int(pstat);
 break;
/*!!!There are more types like SQLITE_NULL ...*/
 }  /*switch*/


 }/*for*/


   printf("\n");
   break;
 case SQLITE_BUSY:
   /*Busy logic*/
   break;
 case SQLITE_DONE:
   sqlite3_finalize(pstat);
   finished = 1;
   break;
 default:
   printf("Error\n");
   break;


   }  /*while*/
 sqlite3_close(db);
   exit(0);
}



You left out quite a bit of the example.  I have corrected it, but not 
tested the code.  This should print out a table of what you read.  
Take note that Sqlite does not have fixed types, so the programmer 
must be aware of that.


You also need to be aware of error conditions and busy states.



Whoops, I left out the sqlite3_step.  Corrected.

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






Works perfectly, thanks you very much.

Fred.


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

Re: [sqlite] Reading error outside the while

[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".



#include 
#include 

int main(void)
{
   sqlite3 *db;
   sqlite3_stmt *pStat;

   const char *dbname = "test.db";
   const char *sql = "SELECT * FROM table1";
   const unsigned char *my_array[3];
   int i=0;;
 
   sqlite3_open(dbname, );

   sqlite3_prepare_v2(db, sql, -1, , 0);
  while(sqlite3_step(pStat) == SQLITE_ROW)
   {
my_array[i] = sqlite3_column_text(pStat, 0);
   printf ("%s\n",my_array[i]); // ok

i++;
   }

   for (i = 0; i<3; i++);{
printf ("%s\n", my_array[i]); // error
   }
   sqlite3_finalize(pStat);
   sqlite3_close(db);
   return 0;
}


Fred.



Your program is wrongly structured.  Try this layout:

sqlite3_prepare_V2

while (TRUE) {
  rc = sqlite3_step
  switch (rc) {
case SQLITE_ROW:
  /*Get each column*/
  for (count = 0; count > sqlite3_column_count; count++) {
switch (sqlite3_column_type) {
  case SQLITE_TEXT:
pt = sqlite3_column_text
/*Move text into your output*/
sprintf(outval, "%s", pt);  /*Or some other move.*/
break;
  case SQLITE_INTEGER:
outnbr = sqlite3_column_int;
or
sprintf(outval, "%d", sqlite_column_int(..));
break;
  
  add other types
 }
}
  break;
case SQLITE_DONE:
  sqlite3_finalize
  return from function
case SQLITE_BUSY:
  /*Handle BUSY condition.*/
  break;
default:
  /*Handle error condition.*/
  break;
   } /*switch*/
  }  /*while*/

Now you can handle errors, busy conditions and the return of differring
types from Sqlite.  When you get a pointer to a text value from 
Sqlite it is the programmer's responsibility to move data from that 
pointer into data in your program.


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






Hello John,

Thanks for reply,

I just tested with your code that seem to be more useful, but 
something is wrong :


#include 
#include 


int main(void) {
   sqlite3 *db;
   sqlite3_stmt *pStat;
   const gchar  *dbname = "test.db";
   int  colcnt;


 int  rc;
 int  finished = 0;


  rc = sqlite3_open(dbname, );


 if (rc != SQLITE_OK) {
   printf("error\n");
   exit(1);
 }


   rc = sqlite3_prepare_v2(db, "SELECT * FROM TABLE1", -1, , 0);

  if (rc != SQLITE_OK) {
printf("error\n");
exit(1);
  }


   while(!finished) {

 rc = sqlite3_step(pstat);

   switch (rc) {

   case SQLITE_ROW:
 /*Get each column*/
 for (colcnt = 0; colcnt < sqlite3_column_count(pStat); 
colcnt++) {


 /*Handle returned data according to type.*/


   switch (sqlite3_column_type(pStat, 0)) {
 case SQLITE_TEXT:
   printf("%s %s ", sqlite3_colimn_name(pstat, colcnt),


sqlite3_column_text(pStat,0));


   break;


   case SQLITE_INTEGER:
 printf("%s %d ", sqlite3_column_name(pstat, colcnt),
 sqlite3_column_int(pstat);
 break;
/*!!!There are more types like SQLITE_NULL ...*/
 }  /*switch*/


 }/*for*/


   printf("\n");
   break;
 case SQLITE_BUSY:
   /*Busy logic*/
   break;
 case SQLITE_DONE:
   sqlite3_finalize(pstat);
   finished = 1;
   break;
 default:
   printf("Error\n");
   break;


   }  /*while*/
 sqlite3_close(db);
   exit(0);
}



You left out quite a bit of the example.  I have corrected it, but not 
tested the code.  This should print out a table of what you read.  Take 
note that Sqlite does not have fixed types, so the programmer must be 
aware of that.


You also need to be aware of error conditions and busy states.



Whoops, I left out the sqlite3_step.  Corrected.

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

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

[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 database file; simply opening
the file again from the OS isn't guaranteed to open the same file that's
locked (if the file is unlinked while sqlite has it open, and then recreated
before the call to fopen() or open() or whatever, for instance).  I'm not
too worried about this race condition under normal circumstances but for my
purposes consistency is important.

On 10/10/07, Ronny Dierckx <[EMAIL PROTECTED]> wrote:



I think a possible solution is to lock the database with a "BEGIN
EXCLUSIVE"
statement and then copy the database file.

- Original Message -
From: "Cyrus Durgin" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, October 10, 2007 6:41 PM
Subject: Re: [sqlite] how to get file handle from sqlite3 object?




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 10/10/07, John Stanton <[EMAIL PROTECTED]> wrote:


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 Durgin wrote:


Maybe it would help to state my use case: without this functionality,


what


is the proper way to copy a database using the C API without
introducing


a


race condition?

On 10/9/07, Robert Simpson <[EMAIL PROTECTED]> wrote:





-
To unsubscribe, send email to [EMAIL PROTECTED]

-









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

Re: [sqlite] Reading error outside the while

[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 

int main(void)
{
   sqlite3 *db;
   sqlite3_stmt *pStat;

   const char *dbname = "test.db";
   const char *sql = "SELECT * FROM table1";
   const unsigned char *my_array[3];
   int i=0;;
 
   sqlite3_open(dbname, );

   sqlite3_prepare_v2(db, sql, -1, , 0);
  while(sqlite3_step(pStat) == SQLITE_ROW)
   {
my_array[i] = sqlite3_column_text(pStat, 0);
   printf ("%s\n",my_array[i]); // ok

i++;
   }

   for (i = 0; i<3; i++);{
printf ("%s\n", my_array[i]); // error
   }
   sqlite3_finalize(pStat);
   sqlite3_close(db);
   return 0;
}


Fred.



Your program is wrongly structured.  Try this layout:

sqlite3_prepare_V2

while (TRUE) {
  rc = sqlite3_step
  switch (rc) {
case SQLITE_ROW:
  /*Get each column*/
  for (count = 0; count > sqlite3_column_count; count++) {
switch (sqlite3_column_type) {
  case SQLITE_TEXT:
pt = sqlite3_column_text
/*Move text into your output*/
sprintf(outval, "%s", pt);  /*Or some other move.*/
break;
  case SQLITE_INTEGER:
outnbr = sqlite3_column_int;
or
sprintf(outval, "%d", sqlite_column_int(..));
break;
  
  add other types
 }
}
  break;
case SQLITE_DONE:
  sqlite3_finalize
  return from function
case SQLITE_BUSY:
  /*Handle BUSY condition.*/
  break;
default:
  /*Handle error condition.*/
  break;
   } /*switch*/
  }  /*while*/

Now you can handle errors, busy conditions and the return of differring
types from Sqlite.  When you get a pointer to a text value from Sqlite 
it is the programmer's responsibility to move data from that pointer 
into data in your program.


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






Hello John,

Thanks for reply,

I just tested with your code that seem to be more useful, but something 
is wrong :


#include 
#include 


int main(void) {
   sqlite3 *db;
   sqlite3_stmt *pStat;
   const gchar  *dbname = "test.db";
   int  colcnt;

 int  rc;
 int  finished = 0;
   
   rc = sqlite3_open(dbname, );

 if (rc != SQLITE_OK) {
   printf("error\n");
   exit(1);
 }

   rc = sqlite3_prepare_v2(db, "SELECT * FROM TABLE1", -1, , 0);

   while(!finished) {

   switch (rc) {

   case SQLITE_ROW:
 /*Get each column*/
 for (colcnt = 0; colcnt < sqlite3_column_count(pStat); colcnt++) {

 /*Handle returned data according to type.*/

   switch (sqlite3_column_type(pStat, 0)) {
 case SQLITE_TEXT:
   printf("%s %s ", sqlite3_colimn_name(pstat, colcnt),

sqlite3_column_text(pStat,0));

   break;

   case SQLITE_INTEGER:
 printf("%s %d ", sqlite3_column_name(pstat, colcnt),
 sqlite3_column_int(pstat);
 break;
/*!!!There are more types like SQLITE_NULL ...*/
 }  /*switch*/

 }/*for*/

   printf("\n");
   break;
 case SQLITE_BUSY:
   /*Busy logic*/
   break;
 case SQLITE_DONE:
   sqlite3_finalize(pstat);
   finished = 1;
   break;
 default:
   printf("Error\n");
   break;

   }  /*while*/
  
   sqlite3_close(db);

   exit(0);

}



You left out quite a bit of the example.  I have corrected it, but not 
tested the code.  This should print out a table of what you read.  Take 
note that Sqlite does not have fixed types, so the programmer must be 
aware of that.


You also need to be aware of error conditions and busy states.

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

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

[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 the rows.


Igor Tandetnik 



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

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

[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 and is safe, but it's very slow on large databases 
with tables with many indexes. You also lose page_size and other pragma 
settings on your database.

> I think a possible solution is to lock the database with a "BEGIN EXCLUSIVE"
> statement and then copy the database file.

This will also work on UNIX - but only if the file copy is done via a 
different(!!) UNIX process. 

If you try to make the copy via the same process - even by open()ing a new
file descriptor while the sqlite3 database is in use, you can end up with 
a corrupt database.

The only safe way to perform an exclusive-lock-copy-db-file-in-same-process
is to actually use the same file descriptor of an already open database, 
perform read()s on that fd (and obviously not close() it).
So if you wish to do such a thing, you'd either need to expose the file
descriptor of the database, or have sqlite provide a new API function to 
safely copy a database that has active sqlite3 connection(s).

See:

Ticket 2665: Dissapearing exclusive lock when DB is copied...
http://www.sqlite.org/cvstrac/tktview?tn=2665

"2007-Sep-25 18:32:06 by drh:
On unix, when you close a file, all locks on that file held by the same process 
are cleared - even
locks that were created by separate file descriptors. You are probably closing 
the file at the
conclusion of your copy, which is clearing the locks."



  

Don't let your dream ride pass you by. Make it a reality with Yahoo! Autos.
http://autos.yahoo.com/index.html
 



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

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

[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: Ronny Dierckx [mailto:[EMAIL PROTECTED] 
Sent: quarta-feira, 10 de outubro de 2007 13:55
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] how to get file handle from sqlite3 object?


I think a possible solution is to lock the database with a "BEGIN EXCLUSIVE"
statement and then copy the database file.

- Original Message - 
From: "Cyrus Durgin" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, October 10, 2007 6:41 PM
Subject: Re: [sqlite] how to get file handle from sqlite3 object?


> 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 10/10/07, John Stanton <[EMAIL PROTECTED]> wrote:
>>
>> 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 Durgin wrote:
>> > Maybe it would help to state my use case: without this functionality,
>> what
>> > is the proper way to copy a database using the C API without 
>> > introducing
>> a
>> > race condition?
>> >
>> > On 10/9/07, Robert Simpson <[EMAIL PROTECTED]> wrote:
>> >



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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

[sqlite] Re: Reading error outside the while

[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 each column*/
 for (i = 0; i < sqlite3_column_count(pStat); i++)
 {
  switch (sqlite3_column_type(pStat,0))
   {
 case SQLITE_TEXT:
   printf("%s ", sqlite3_column_text(pStat,0));
 break;
   }
 break;
   }


Why do you run the 'for' loop, if you unconditionally break out of it on 
the very first iteration anyway?


Igor Tandetnik 



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

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

[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 10/10/07, John Stanton <[EMAIL PROTECTED]> wrote:
>
> 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 Durgin wrote:
> > Maybe it would help to state my use case: without this functionality,
> what
> > is the proper way to copy a database using the C API without introducing
> a
> > race condition?
> >
> > On 10/9/07, Robert Simpson <[EMAIL PROTECTED]> wrote:
> >
> >>>-Original Message-
> >>>From: Cyrus Durgin [mailto:[EMAIL PROTECTED]
> >>>Sent: Tuesday, October 09, 2007 5:02 PM
> >>>To: sqlite-users@sqlite.org
> >>>Subject: [sqlite] how to get file handle from sqlite3 object?
> >>>
> >>>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?
> >>>
> >>
> >>There's no public way to get this, nor should there be.  The internal
> >>implementation of the database should be kept separate from the logical
> >>API
> >>to access it.  Such a function would muddy the water between
> >>implementation
> >>and interface and hamper the ability to change the implementation
> without
> >>changing the interface.
> >>
> >>Not all filesystems would be able to return one, nor could it guarantee
> >>that
> >>the database is in fit state for someone to fiddle with its internal
> >>handle.
> >>Furthermore, it could not be guaranteed that once a caller obtained the
> >>handle that the caller might then do something damaging to it or alter
> its
> >>state.
> >>
> >>Such a function definitely falls into the BAD IDEA category, IMO.
> >>
> >>Robert
> >>
> >>
> >>
> >>
>
> >>-
> >>To unsubscribe, send email to [EMAIL PROTECTED]
> >>
>
> >>-
> >>
> >>
> >
> >
> >
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>


-- 
Cyrus.
<[EMAIL PROTECTED]>

Re: [sqlite] Most basic of questions

[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 Fields ORDER BY name";

my $sth = $dbh->prepare($sql);

if (defined($sth)) {
$sth-> execute();
my @row;
while (@row = $sth->fetchrow_array()) {
print "$row[0]\n";
}
}

sth->finish();

$dbh->disconnect(); 


Can someone kindly tell me why I get displayed: "closing dbh with active
statement handles at H:\Testing sql.pl line 25? By that I mean which it
is necessary to have it displayed? Does it have to be so or can I
somehow cause it not to appear?

I'm awaiting a book "The definitive guide to SQLite" to study but in the
meantime I'd appreciate the help from some kind soul.

Thanks
Brian Rowlands
We must accept finite disappointment, but we must never lose infinite
hope.
Martin Luther King Jr.   



It looks like you need to execute a finalize.  Could it be 
sth->finalize()?  (I am not a Perl user).


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

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

[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 Durgin wrote:

Maybe it would help to state my use case: without this functionality, what
is the proper way to copy a database using the C API without introducing a
race condition?

On 10/9/07, Robert Simpson <[EMAIL PROTECTED]> wrote:


-Original Message-
From: Cyrus Durgin [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 09, 2007 5:02 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] how to get file handle from sqlite3 object?

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?



There's no public way to get this, nor should there be.  The internal
implementation of the database should be kept separate from the logical
API
to access it.  Such a function would muddy the water between
implementation
and interface and hamper the ability to change the implementation without
changing the interface.

Not all filesystems would be able to return one, nor could it guarantee
that
the database is in fit state for someone to fiddle with its internal
handle.
Furthermore, it could not be guaranteed that once a caller obtained the
handle that the caller might then do something damaging to it or alter its
state.

Such a function definitely falls into the BAD IDEA category, IMO.

Robert




-
To unsubscribe, send email to [EMAIL PROTECTED]

-









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

Re: [sqlite] auto library function loading

[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 components unused by most applications.


Sqlite's anti-bloatware philosophy is one of its most endearing 
qualities.  It is not easy to retain simplicity.  It reminds me of a 
quotation from ones of Pascal's famous letters "I apologize for making 
this letter so long.  I have not had time to make it shorter".


If you really are concerned with making custom functions available 
without wrappers you can compile them into the Sqlite library alongside 
the predefined functions.  Sqlite lets you do that very simply.


We use a fairly extensive library of custom functions, including ones 
which implement Javascript as an embedded scripting language and another 
set which implements a fixed point decimal number type.  A wrapper 
around sqlite3_open loads them and performs other activities necessary 
to cleanly integrate Sqlite into the applications.  Sqlite upgrades are 
consequently straightforward.


Ken wrote:
John, 

Kind of defeats the point of "automatic". 
 
Using a wrapper for application code is perfectly legitimate. However it does not address the sqlite3 command line tool.


The ability to automatically load a library of functions via a dll would be a really nice feature and could be used to promote user contributions for library development for sqlite. 

For example math function library, or string function library, even the VFS i/o routines could be loaded in this manner. 


Ken

John Stanton <[EMAIL PROTECTED]> wrote: Ken wrote:

Is there a way for SQLITE to automatically load user defined functions at database instantiation or upon database connection? 
  
   Can it test for a .so/.dll and perform a call to load user functions? If the .dll does not exist maybe issue a warning? 


Just a thought as a way to allow users to create their own  presentation 
library to implement things like math functions/ presentation functions etc 
without having to make and maintain code changes to the core software.




Build your own wrapper for the sqlite3_open API function.  You can have 
some form of library of user defined functions which you bind just after 
you have made a DB connection.



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






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

[sqlite] Re: Re: user defined function

[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 < s2 then 1 else 0 end) 
rank

from (
select t1.customer c,
   t1.product p1, sum(t1.sales)/count(distinct t2.rowid) s1,
   t2.product p2, sum(t2.sales)/count(distinct t1.rowid) s2
from Test1MX1000Multi  t1 join Test1MX1000Multi  t2
on (t1.customer = t2.customer)
group by t1.customer, t1.product, t2.product
)
group by customer, product;

Igor Tandetnik 



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

Re: [sqlite] user defined function

[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
   http://www.sqlite.org/cvstrac/chngview?cn=4435

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


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

Re: [sqlite] Re: user defined function

[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,

 [SUM_SALES],

 (SELECT COUNT(T2.SUM_SALES)

  FROM   (SELECT   CUSTOMER,

   PRODUCT,

   (SUM(SALES)) AS [SUM_SALES]

  FROM Test1MX1000Multi AS T1

  GROUP BY CUSTOMER,

   PRODUCT) AS [T2]

  WHERE  T1.CUSTOMER = T2.CUSTOMER

 AND T2.SUM_SALES > T1.SUM_SALES) AS [RANK]

  FROM   (SELECT   CUSTOMER,

   PRODUCT,

   (SUM(SALES)) AS [SUM_SALES]

  FROM Test1MX1000Multi AS T1

  GROUP BY CUSTOMER,

   PRODUCT) AS [T1]) AS [TEMP]

WHERERANK <= 4

ORDER BY CUSTOMER,

 RANK


On 10/8/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
>
> Aviad Harell <[EMAIL PROTECTED]>
> wrote:
> > I tried to create user defined function called Rank. which gets
> > partition columns and order by column. this function should create
> > rank according to its parameters. i.e.:
> >
> > select customer, product, sales, Rank(customer,  sales)
> > from table
> >
> > should return:
> >
> > customerA productA 100 0
> > customerA productB 300 2
> > customerA productC 200 1
> > customerA productD 400 3
> > customerB productB 300 1
> > customerB productA 400 2
> > customerB productC 100 0
> >
> > how can i do it? should i use FunctionType scalar or aggregate?
>
> Scalar. Aggregate function looks at multiple rows and produces a single
> value (see SUM, MAX). A scalar function produces one value per row,
> which is what you want here.
>
> Note also that you don't really need a custom function here. This query
> should produce the same result:
>
> select customer, product, sales,
> (select count(*) from tableName t2
>   where t2.customer = t1.customer and t2.sales < t1.sales)
> from tableName t1;
>
> Igor Tandetnik
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>

[sqlite] Most basic of questions

[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 = $dbh->prepare($sql);

if (defined($sth)) {
$sth-> execute();
my @row;
while (@row = $sth->fetchrow_array()) {
print "$row[0]\n";
}
}

sth->finish();

$dbh->disconnect(); 

Can someone kindly tell me why I get displayed: "closing dbh with active
statement handles at H:\Testing sql.pl line 25? By that I mean which it
is necessary to have it displayed? Does it have to be so or can I
somehow cause it not to appear?

I'm awaiting a book "The definitive guide to SQLite" to study but in the
meantime I'd appreciate the help from some kind soul.

Thanks
Brian Rowlands
We must accept finite disappointment, but we must never lose infinite
hope.
Martin Luther King Jr.   

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

[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 create a new database with the
same schema, attach it to the existing db instance, and copy over the
data using transactions, then detach it.  This way, you've got access to
the original database and the database copy has a consistent,
thread-safe view of the data in the original.  Additionally, my guess
would be the copy would be vacuumed, which if you're using the copy for
backup purposes is great since it saves on disk space.

Cyrus Durgin wrote:

Maybe it would help to state my use case: without this functionality, what
is the proper way to copy a database using the C API without introducing a
race condition?

On 10/9/07, Robert Simpson <[EMAIL PROTECTED]> wrote:
  

-Original Message-
From: Cyrus Durgin [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 09, 2007 5:02 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] how to get file handle from sqlite3 object?

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?

  

There's no public way to get this, nor should there be.  The internal
implementation of the database should be kept separate from the logical
API
to access it.  Such a function would muddy the water between
implementation
and interface and hamper the ability to change the implementation without
changing the interface.

Not all filesystems would be able to return one, nor could it guarantee
that
the database is in fit state for someone to fiddle with its internal
handle.
Furthermore, it could not be guaranteed that once a caller obtained the
handle that the caller might then do something damaging to it or alter its
state.

Such a function definitely falls into the BAD IDEA category, IMO.

Robert




-
To unsubscribe, send email to [EMAIL PROTECTED]

-






  



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