Re: [sqlite] Getting SQLITE_BUSY within a transaction and how tohandle it

[Dennis Volodomanov]

> Are you at least reseting the statement?  I hit this once before, and
> reseting the statement fixed the issue for me.

No... I'll try that, thanks!

   Dennis


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

Re: [sqlite] Getting SQLITE_BUSY within a transaction and how to handle it

[Shawn Wilsher]

On Mon, Mar 16, 2009 at 10:02 PM, Dennis Volodomanov <
dennis.volodoma...@conceiva.com> wrote:

> Do I need the finalize the statement and re-prepare it again in the
> second thread before trying to step it?

Are you at least reseting the statement?  I hit this once before, and
reseting the statement fixed the issue for me.

Cheers,

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

[sqlite] Getting SQLITE_BUSY within a transaction and how to handle it

[Dennis Volodomanov]

Hello all,

I'm having this problem... I have one database and two threads opening
it for reading/writing (in a simplistic scenario).

Now, one of the threads issues (successfully) a "BEGIN IMMEDIATE
TRANSACTION" and starts doing what it needs. The second thread also
issues a "BEGIN IMMEDIATE TRANSACTION", but gets an SQLITE_BUSY (which
is what I expected). At this point, I issue a Sleep() in the second
thread a bit and try the sqlite3_step("BEGIN IMMEDIATE TRANSACTION")
again. However, this seems to cause it to enter a non-ending loop - none
of the 2 threads ever get a SQLITE_DONE (or any other) return code.

Do I need the finalize the statement and re-prepare it again in the
second thread before trying to step it?

Thanks in advance for any advice!

   Dennis


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

Re: [sqlite] Re trieve results of a query into a bash script, and use them to iterate

[Ken]

you might want to do a search on named pipes... 

Also you query could then be simplified.

attach database 'db2.sl3' as usr
select u.* from usr.user u, names n
where u.name = n.name




--- On Mon, 3/16/09, urschrei  wrote:

> From: urschrei 
> Subject: [sqlite] Re trieve results of a query into a bash script, and use 
> them to iterate
> To: sqlite-users@sqlite.org
> Date: Monday, March 16, 2009, 9:50 PM
> I'd like to use the results of a simple select query in
> a bash script, and
> iterate through them with a 'for' loop. I have
> something like the following
> in mind:
> 
> names = `sqlite3 db1.sl3 'select * from names;'`
> users = `sqlite3 db2.sl3 'select * from users;'`
> for n in names
> do
>  for u in users
>   do
>if $n == $u echo $u is a valid user!
>fi
>   done
> done
> 
> Am I on the right track, or should I be going about this
> differently?
> -- 
> View this message in context:
> http://www.nabble.com/Retrieve-results-of-a-query-into-a-bash-script%2C-and-use-them-to-iterate-tp22551722p22551722.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] Re trieve results of a query into a bash script, and use them to iterate

[urschrei]

I'd like to use the results of a simple select query in a bash script, and
iterate through them with a 'for' loop. I have something like the following
in mind:

names = `sqlite3 db1.sl3 'select * from names;'`
users = `sqlite3 db2.sl3 'select * from users;'`
for n in names
do
 for u in users
  do
   if $n == $u echo $u is a valid user!
   fi
  done
done

Am I on the right track, or should I be going about this differently?
-- 
View this message in context: 
http://www.nabble.com/Retrieve-results-of-a-query-into-a-bash-script%2C-and-use-them-to-iterate-tp22551722p22551722.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 : Constraint question

[John Machin]

On 17/03/2009 9:02 AM, Wolfgang Enzinger wrote:
>> Date: Sun, 15 Mar 2009 23:17:04 -0400
>> From: "Griggs, Donald" 
>> Subject: Re: [sqlite] SQLITE : Constraint question
> 
>>> BTW, is there a document that explains in more detail what operations
>>> the CHECK constraint is capable of?
> 
> [...]
> 
>> Were you looking for something beyond the documentation under "CREATE
>> TABLE"?
>>
>>  Column constraints:
>>  http://www.sqlite.org/syntaxdiagrams.html#column-constraint
>>
>>  Table constraints:
>>  http://www.sqlite.org/syntaxdiagrams.html#table-constraint
> 
> Well, all that these docs are saying concerning CHECK constraints is:
> "CHECK(expr)". So I looked up "expr" and there is indeed a lot of useful
> information, but I didn't see the typeof() function mentioned, for
> instance. Actually I wonder how to find out about this useful function if
> not by reading (and understanding ;-) the source code.

Don't wonder. It's on the same page as all the other core functions: 
http://www.sqlite.org/lang_corefunc.html

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

Re: [sqlite] SQLITE : Constraint question

[D. Richard Hipp]

On Mar 16, 2009, at 6:02 PM, Wolfgang Enzinger wrote:
>
>> Were you looking for something beyond the documentation under "CREATE
>> TABLE"?
>>
>> Column constraints:
>> http://www.sqlite.org/syntaxdiagrams.html#column-constraint
>>
>> Table constraints:
>> http://www.sqlite.org/syntaxdiagrams.html#table-constraint
>
> Well, all that these docs are saying concerning CHECK constraints is:
> "CHECK(expr)". So I looked up "expr" and there is indeed a lot of  
> useful
> information, but I didn't see the typeof() function mentioned, for
> instance. Actually I wonder how to find out about this useful  
> function

http://www.sqlite.org/lang_corefunc.html#typeof


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



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

Re: [sqlite] SQLITE : Constraint question

[Wolfgang Enzinger]

> Date: Sun, 15 Mar 2009 23:17:04 -0400
> From: "Griggs, Donald" 
> Subject: Re: [sqlite] SQLITE : Constraint question

>> BTW, is there a document that explains in more detail what operations
>> the CHECK constraint is capable of?

[...]

> Were you looking for something beyond the documentation under "CREATE
> TABLE"?
> 
>  Column constraints:
>  http://www.sqlite.org/syntaxdiagrams.html#column-constraint
> 
>  Table constraints:
>  http://www.sqlite.org/syntaxdiagrams.html#table-constraint

Well, all that these docs are saying concerning CHECK constraints is:
"CHECK(expr)". So I looked up "expr" and there is indeed a lot of useful
information, but I didn't see the typeof() function mentioned, for
instance. Actually I wonder how to find out about this useful function if
not by reading (and understanding ;-) the source code.

>  Conflict clause:
>  http://www.sqlite.org/syntaxdiagrams.html#conflict-clause
> 
>  Foreign key clause (enforceable with preprocessing to create triggers):
>  http://www.sqlite.org/syntaxdiagrams.html#foreign-key-clause

Useful info, too, but not with respect to the CHECK statement (at least
right now i can't see it).

I understand that it's up to everybody to help improve the docs. So, as far
as "strict affinity" is concerned, I'm planning on opening a ticket (that's
how it works, right?) requesting a documentation improvement if there are
no objections. Regarding the CHECK statement, however, I think that there
is a gap that should be filled by someone with more insight than I have ...

BTW: My previous post was my first post to this list, and I forgot to say
Hello to everybody, and most of all: Hats off to Mr. Hipp, great work! :-)

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

[sqlite] Using SQLite from Elisp functions

[Florian v. Savigny]

Dear Listmates,

I would like to use SQLite from within Emacs. I have rummaged through
the Website, and it seems that nobody has programmed any bindings for
Elisp so far, and I'm afraid I'm not smart enough to do that.

The only possibility I can currently see would be starting the sqlite
command line interface as a process from within Emacs, and then
sending it SQL statements via stdin. I wouldn't have any problem with
that, but I seem to have heard that such an arrangement isn't
particularly robust (I forgot the precise term; it has something to do
with I/O, in any case).

And if that's basically a safe way, can anyone give recommendations
what pitfalls to avoid? E.g. would it be better to run a new
(synchronous) sqlite process every time I want to do something about
(or query) the database, or is a permanent "daemon" (i.e. starting it
as an asynchronous process and having it run for the whole Emacs
session) the better way?

Basically, of course, I would appreciate any hint on how to use SQLite
from within Emacs (I may be completely on the wrong track already).

Thanks for any help here!

Florian

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

Re: [sqlite] SQLITE : documentation and search topics

[Griggs, Donald]

 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of
REPKA_Maxime_NeufBox
Sent: Monday, March 16, 2009 3:11 PM
To: General Discussion of SQLite Database
Subject: [sqlite] SQLITE : documentation and search topics

Hello,

A main remark to SQLite developers :
  - Does it exist a Help file .chm for SQLite ?
I am quite new user with Database and SQLite and find very hard to get
information and documentation.
My opinion is that it is totaly NOT intuitive on the web to find
information.
For comparison I am quite new on Autohotkey software (autohokey.com) and
I have no problem to find  information and answer on problem (most of
the time I find the answer without posting a question).

-> Did I miss something on SQLite web ? or does other users think like 
-> me
that some improvment should be done ?

MaxMax14

==
Bonjour, Maxime,

I'm *not* an sqlite developer, but maybe I can provide a portion of an
answer.

-- CHM -- 

There's no .chm file for a couple of reasons.   Mainly, .chm is a
Microsoft application help format for Windows, and Windows is just one
of the many operating systems on which sqlite is run.   Secondly, sqlite
is not really an application (not like, say, Microsoft Access) -- it is
instead an embeddable SQL engine intended for programmers.   That being
said, there *is* also provided a command line tool called "sqlite3"
intended mainly for testing, but which is useful in it's own right.  If
you run it, it will let you know that a consise help screen is available
using the ".help" command.

The full source code (very well commented) is available for both the
sqlite engine and the command line  utility.

There is not to say that other folks have not written end-user
applications using Sqlite, of course, and there are a number of
applications that are GUI database administration tools that may or may
not have good online help included -- you'd want to approach those
developers in that regard.


-- Your current problem --

I take it you likely have something in particular you would like to know
about sqlite?  Since you haven't found it on the website, you may want
to post your particular problem here.


-- Documentation in general --

On the one hand, each person would organize a website differently, and
all will never be pleased.   But on the other hand, sometimes someone
unacquainted with the product can provide some of the most useful
feedback, so you may want to post some specific suggestions you may
have.

I find that it's very helpful to visit the wiki -- and of course you may
submit wiki improvements quite directly.

Hope this may help in some way,
   Donald
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Adding a NOT NULL column fails on empty table

[Igor Tandetnik]

"Alex Ousherovitch"
 wrote in
message
news:5ee1928d06817b4788b64caf1a8517b00325e...@sfo-ex-01.ad.opentv.local
> The attempts to add a NOT NULL column on an empty  table
>
> ALTER TABLE tbl_name ADD COLUMN col_name blob NOT NULL
>
> fail with the following error message:
>
> Cannot add a NOT NULL column with default value NULL

Can't you just drop the table and create a new one? As the table is 
empty, you won't be losing any data.

Igor Tandetnik 



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

[sqlite] Adding a NOT NULL column fails on empty table

[Alex Ousherovitch]

Hello list,

 

The attempts to add a NOT NULL column on an empty  table

ALTER TABLE tbl_name ADD COLUMN col_name blob NOT NULL

 fail with the following error message:

Cannot add a NOT NULL column with default value NULL

 

As I understand, many other systems allow it when the table has no
records. 

Is it a bug or a feature in SQLite?

 

Thank you,

Alex

 

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

Re: [sqlite] Query help

[Igor Tandetnik]

"Marco Bambini"  wrote
in message news:3265458b-af7b-434f-83e8-f9448bab0...@sqlabs.net
> Hello all,
>
> I have a table foo (id INTEGER, tid INTEGER, optype INTEGER),
> and I have some data into foo:
> id id2 optype
> -
> 1 2 10
> 2 2 10
> 3 2 10
> 4 2 10
> 5 2 10
> 6 2 20
> 7 2 10
> 8 2 20
> 9 2 20
> 10 2 10
>
> I need a query that returns results like:
> 1,2,3,4,5
> 6
> 7
> 8,9
> 10
>
> (divided by optype and sorted by id)

Try something like this:

SELECT group_concat(id) FROM rsql_mvcc t1 WHERE transactionID=2
GROUP BY (
select min(id) from rsql_mvcc t2
where t2.transactionID=2 and t2.id <= t1.id and t2.optype=t1.optype 
and
not exists (
select 1 from rsql_mvcc t3 where t3.transactionID=2 and
t3.id > t2.id and t3.id < t1.id and t3.optype != t1.optype
)
);

However, this is likely to be excrutiatingly slow ( O(N^3) ) for 
anything but small number of records. The problem doesn't lend itself 
easily to SQL. I submit it would likely be easier, and much faster, to 
run a query like this:

select id, optype from rsql_mvcc WHERE transactionID=2
order by id;

and assemble groups in your application code as you walk the resultset.

Igor Tandetnik 



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

Re: [sqlite] datetime as integer

[MikeW]

John Machin  writes:

>
> > "noon in Greenwich on November 24, 4714 BC" - 
> > presumably that's the beginning of time for Creationists ...
>
> You presume incorrectly; it's the start of Scaliger's 7980-year "Julian" 
> astronomical cycle. (http://en.wikipedia.org/wiki/Julian_day#History)
>
> You are possibly thinking of Archbishop Ussher's creation estimate of 23 
> October 4004 BC. (http://en.wikipedia.org/wiki/Dating_Creation).
>
> Cheers,
> John

Sorry, I meant to say _before_ the beginning of time ...   
   
Cheers,
MikeW

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

[sqlite] Query help

[Marco Bambini]

Hello all,

I have a table foo (id INTEGER, tid INTEGER, optype INTEGER),
and I have some data into foo:
id  id2 optype
-
1   2   10
2   2   10
3   2   10
4   2   10
5   2   10
6   2   20
7   2   10
8   2   20
9   2   20
10  2   10

I need a query that returns results like:
1,2,3,4,5
6
7
8,9
10

(divided by optype and sorted by id)

If I use a simple:
SELECT group_concat(id) FROM rsql_mvcc WHERE transactionID=2 GROUP BY  
OPTYPE;

I obtain:
1,2,3,4,5,7,10
6,8,9

I would really appreciate any help.
Thanks a lot.
-- 
Marco Bambini
http://www.sqlabs.com
http://www.creolabs.com/payshield/





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

Re: [sqlite] Perl DBD Question

[marcos rebelo]

I'm considering...

package KQI::Utils::SQLiteWrapper;
use strict;
use warnings;
use Data::Dumper;
use DBI;
use Carp qw(confess);

sub new {
my ( $pkg, $path, $attr, $count) = @_;
$pkg = ref($pkg) if ref($pkg);

my %attr = (
'RaiseError' => 1,
'AutoCommit' => 1,
%{ $attr or { } }
);

return bless( {
'path'  => $path,
'dbh'   => DBI->connect( "dbi:SQLite:dbname=$path", "", "", \%attr),
'count' => ( $count or 100)
}, $pkg );
}

sub do {
my ($self, @args) = @_;

foreach (0..$self->{'count'}) {
my $result = eval { $self->{'dbh'}->do(@args) };
if ( $@ ) {
if( $self->{'dbh'}->err == 5 ){  # If got a locked code, try again
sleep 1;
next;
}
confess $@;
}
return $result;
}
confess $self->{'dbh'}->errstr;
}
...
1;

But please.

Since I will need to use C++ later, tell me:
Isn't 'busy_handler' supposed to do that?

Thanks
Marcos Rebelo




On Mon, Mar 16, 2009 at 1:54 PM, David Westbrook  wrote:
> At the app level you can do something like this to check for the
> "database is locked(5)" error. Note the sleep and max ~1000 attempts
> functionality as well.
>
> my $ct = 0;
> while( $ct++ < 1000 ){
>  $dbh->do($sql, {}, @bind);
>  if( $dbh->err == 5 ){  # If got a locked code, try again
>    sleep 1;
>    next;
>  }
>  ...
> }
>
>
> On Mon, Mar 16, 2009 at 7:05 AM, marcos rebelo  wrote:
>> Hi all
>>
>> I'm a Perl programmer using SQLite
>>
>> I want to retry to execute every command automatically, until the DB
>> is not locked. In C seems that I need to set the busy_handler.
>>
>> How do I do this with DBD in Perl?
>>
>> Thanks for any help
>>
>> Best Regards
>> Marcos Rebelo
>>
>> --
>> Marcos Rebelo
>> http://oleber.freehostia.com
>> Milan Perl Mongers leader http://milan.pm.org
>> ___
>> 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
>



-- 
Marcos Rebelo
http://oleber.freehostia.com
Milan Perl Mongers leader http://milan.pm.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index optimization

[Igor Tandetnik]

"Andrea Galeazzi"  wrote in
message news:49be74fd.6060...@korg.it
> I red this article on wiki:
> http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
> I've got a similar case but the difference is that I've to use LIKE
> operator instead of =
>  SELECT title FROM tracks
>WHERE title LIKE %Mad% AND ((title=:last_title AND id>:last_id)
> OR ((title>:last_title)) ORDER BY title,id;
> id is the primary key and I created an index for (id,title).
> My question is: will the previous query be actually faster then just
> only using OFFSET and LIMITS even if I also need a LIKE operator on
> title column?

SQLite won't be able to use the index to satisfy LIKE condition. So you 
should concentrate on the other clauses. An index on (title, id) should 
help. For greater effect, change the query to

SELECT title FROM tracks
WHERE title LIKE %Mad% AND
  title >= :last_title AND
  (title>:last_title OR id>:last_id)
ORDER BY title,id;

Igor Tandetnik 



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

Re: [sqlite] Index optimization

[Andrea Galeazzi]

Andrea Galeazzi ha scritto:
> I red this article on wiki: 
> http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
> I've got a similar case but the difference is that I've to use LIKE 
> operator instead of =
>   SELECT title FROM tracks
> WHERE title LIKE %Mad% AND ((title=:last_title AND id>:last_id) 
> OR ((title>:last_title)) ORDER BY title,id;
> id is the primary key and I created an index for (id,title).
> My question is: will the previous query be actually faster then just 
> only using OFFSET and LIMITS even if I also need a LIKE operator on 
> title column?
> Thanks.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> __ NOD32 3939 (20090316) Information __
>
> This message was checked by NOD32 antivirus system.
> http://www.eset.com
>
>
>
>   
ERRATA CORRIGE
...then -> than
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Index optimization

[Andrea Galeazzi]

I red this article on wiki: 
http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
I've got a similar case but the difference is that I've to use LIKE 
operator instead of =
  SELECT title FROM tracks
WHERE title LIKE %Mad% AND ((title=:last_title AND id>:last_id) 
OR ((title>:last_title)) ORDER BY title,id;
id is the primary key and I created an index for (id,title).
My question is: will the previous query be actually faster then just 
only using OFFSET and LIMITS even if I also need a LIKE operator on 
title column?
Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] web page data scraping to sqlite db

[John Machin]

On 17/03/2009 1:55 AM, d...@dommel.be wrote:
> Hello,
> 
> 
> I am working on a SQLite db with equity data in it.
> On http://finance.yahoo.com/q?s=dow you can find the current P/E and Div & 
> Yield
> fields. So I like to store in my db for name=DOW pe=12.25 and div=7.9 in a
> automated way.
> 
> I can use wget to get the page in a file but I still needs some function to 
> get
> those 2 fields.
> The best ofcourse would be doing this with one function call.
> 
> Any ideas how to tackle this problem?

It's a "How do I use language X to extract some data from a web page" 
question.

Here's an idea: ask on a forum related to language X.

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

[sqlite] web page data scraping to sqlite db

[ddk]

Hello,


I am working on a SQLite db with equity data in it.
On http://finance.yahoo.com/q?s=dow you can find the current P/E and Div & Yield
fields. So I like to store in my db for name=DOW pe=12.25 and div=7.9 in a
automated way.

I can use wget to get the page in a file but I still needs some function to get
those 2 fields.
The best ofcourse would be doing this with one function call.

Any ideas how to tackle this problem?

tx,

Danny

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

Re: [sqlite] In-memory databases from Perl

[Jean-Denis Muys]

On 3/16/09 3:24 PM, "P Kishor"  wrote:

> Jean-Denis, do consider correcting the wiki so others after you are
> not similarly misled.

Done.

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

Re: [sqlite] datetime as integer

[John Machin]

On 17/03/2009 1:00 AM, MikeW wrote:
> Timothy A. Sawyer  writes:
> 
[snip]

>> For date calculations, SQLite prefers real values containing
>> number of days since noon in Greenwich on November 24, 4714
>> B.C., using the Proleptic Gregorian calendar:
> SNIP
> 
> "noon in Greenwich on November 24, 4714 BC" - 
> presumably that's the beginning of time for Creationists ...
> 

You presume incorrectly; it's the start of Scaliger's 7980-year "Julian" 
astronomical cycle. (http://en.wikipedia.org/wiki/Julian_day#History)

You are possibly thinking of Archbishop Ussher's creation estimate of 23 
October 4004 BC. (http://en.wikipedia.org/wiki/Dating_Creation).

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

Re: [sqlite] In-memory databases from Perl

[P Kishor]

On Mon, Mar 16, 2009 at 9:16 AM, Jean-Denis Muys  wrote:
>
> On 3/16/09 3:10 PM, "P Kishor"  wrote:
>
>> On Mon, Mar 16, 2009 at 9:03 AM, Jean-Denis Muys  
>> wrote:
>>> Now I want to use an in-memory database, so I replaced my original connect
>>> statement with the one suggested at
>>> http://www.sqlite.org/cvstrac/wiki?p=PerlNotes :
>>>
>>>     my $dbh = DBI->Connect("dbi:SQLite:dbname=:memory:");
>>>
>>> However, this doesn't work, and I crash on that connect statement  with  the
>>> following error message:
>>>
>>
>> DBI->connect()
>>
>> note the lowercase connect()
>>
>
> Precisely. It may be worth noting that I was misled by the wiki entry which
> has the same mistake.
>
>
> See http://www.sqlite.org/cvstrac/wiki?p=PerlNotes
>
> Thank you very much.
>
>

Jean-Denis, do consider correcting the wiki so others after you are
not similarly misled.

-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Carbon Model http://carbonmodel.org/
Open Source Geospatial Foundation http://www.osgeo.org/
Sent from: Madison WI United States.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] designing a db to hold repeating data

[P Kishor]

On Sun, Mar 15, 2009 at 8:57 PM, Rich Shepard  wrote:
> On Sun, 15 Mar 2009, P Kishor wrote:
>
>> CREATE TABLE met (
>>        met_id INTEGER PRIMARY KEY,
>>        other met attributes,
>>        met_grid_id INTEGER
>> );
>
>   Given the quantity of data you have I urge you to read Joe Celko's "SQL
> Programming Style" before you dig yourself into a really deep hole. For
> example, your met_grid covers a defined land area. It would be much better
> to use the geographic coordinates (lat/lon, UTM, State Plane) of the center
> of each grid as the primary key. On the other hand, if all your information
> is associated with the met_grid, then its center should be the unique
> identifier. Your *_id are artificial and convey no useful information.
>
>> Imagine a million square kms area divided into 1 sq. kms. cells. Those are
>> the land cells. Now imagine another overlay, the met grid, on top of the
>> land grid. The met grid is also a million square kms., but is tessellated
>> into 2500 sq. kms. cells, that is, a 20 x 20 grid.
>
>   What you could do is have a Grid table whose primary key is the center of
> the central met_grid, and which references all the other met_grids it
> contains.
>
>   Given your schema above, how can you tell that a specific 1 km**2 met cell
> belongs to a specific 2500 km**2 grid cell? The numbers are meaning less.
> Geographic coordinates use real, meaningful data to associate all 2500 met
> cells in each grid cell.
>
>> I have 7 met attributes for every day of 20 years (that is, 7300 rows)
>> for each "met_cell". So, imagine a stack of 7300 rows sitting on each
>> met cell. In other words, each set of 7300 rows is related to a block
>> of 2500 land cells.
>
>   Then your Met table contains 7 columns (one for each attribute), plus the
> geographic center columns (Northing/Easting, Lat/Lon), and the date. That's
> 10 columns and 7300 rows.
>
>   Now you have a start toward a normalized table.
>
>   I suspect that your thinking has been shaped by trying to use spreadsheets
> as databases, and thinking of SQL as a procedural language rather than a
> declarative language that works on sets of tables (rows, columns) instead of
> files (records, fields).
>
> Rich
>


I appreciate your help Rich, but your suspicion is unfounded and
wrong. I don't use spreadsheets other than when doing my taxes, and
while I am only about 1/100th as good as Igor at SQL, that probably
places me in the upper 90s percentile.

That said, my original question was about designing a db for ease of
use and speed of query retrieval, not for size or memory constraints.

I also have a fairly long history with GIS, so I have considered fully
the benefits and drawbacks of using lat/lon as the the key.
Geographically unique keys are useless for my problem even though the
problem space is geographic.

I don't really need to know which cell_id or what location I am trying
to query... I will build a map front end that will allow the users to
choose an area of interest and send the bounding box back to the
application. The application will take that bounding box and locate
the cells. Hence, the R*Tree index. The cell_ids will then be used to
return the spatial and weather attributes which will be passed to the
model. Hence, the cell_ids and met_ids can be as artificial as
Sucralose.

In any case, I built the db last night. Weighs in at about 450 MB. I
basically loaded all the weather data in a single table, and linked
the spatial cells to the weather cells using met_id. Built an index on
met_id, and, of course, the R*Tree index. Did a few cursory tests. The
performance is outstanding.

Now I need to do a few load tests, and I think I am well on my way to
the next step.

I think I have converted a procedure that took many several hours
doing data prep to a process that returns data in a few milliseconds.

Simple schema, simple db, simply great results.

-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Carbon Model http://carbonmodel.org/
Open Source Geospatial Foundation http://www.osgeo.org/
Sent from: Madison WI United States.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In-memory databases from Perl

[Jean-Denis Muys]

On 3/16/09 3:10 PM, "P Kishor"  wrote:

> On Mon, Mar 16, 2009 at 9:03 AM, Jean-Denis Muys  wrote:
>> Now I want to use an in-memory database, so I replaced my original connect
>> statement with the one suggested at
>> http://www.sqlite.org/cvstrac/wiki?p=PerlNotes :
>> 
>>     my $dbh = DBI->Connect("dbi:SQLite:dbname=:memory:");
>> 
>> However, this doesn't work, and I crash on that connect statement  with  the
>> following error message:
>> 
> 
> DBI->connect()
> 
> note the lowercase connect()
>

Precisely. It may be worth noting that I was misled by the wiki entry which
has the same mistake.


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

Thank you very much.

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

Re: [sqlite] datetime as integer

[John Elrick]

MikeW wrote:
> Timothy A. Sawyer  writes:
>
>   
>> I stand corrected on the math
>>
>> Bottom line is that different applications use different baseline values for
>> 
> epoch (beginning of time)
>   
>> and you must know that baseline value
>> --Original Message--
>> From: Kees Nuyt
>> Sender: sqlite-users-boun...@...
>> To: sqlite-us...@...
>> ReplyTo: sqlite-us...@...
>> Sent: Mar 13, 2009 14:58
>> Subject: Re: [sqlite] datetime as integer
>>
>> Just a few corrections.
>>
>> 
> SNIP
>   
>> For date calculations, SQLite prefers real values containing
>> number of days since noon in Greenwich on November 24, 4714
>> B.C., using the Proleptic Gregorian calendar:
>> 
> SNIP
>
> "noon in Greenwich on November 24, 4714 BC" - 
> presumably that's the beginning of time for Creationists ...
>   

That would be October 23, 4004 BCE (according the the Bishop of 
Ussher).  Noon, IIRC.  I would assume using Radiometric dating as a 
starting point would be a tad unwieldy for day to day usage.

http://en.wikipedia.org/wiki/Dating_Creation


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

Re: [sqlite] In-memory databases from Perl

[P Kishor]

On Mon, Mar 16, 2009 at 9:03 AM, Jean-Denis Muys  wrote:
> Hello,
>
> I am quite a newbie but I already spent a significant amount of time on
> Google, to no avail.
>
> I have a very small Perl program which exercises the basic SQLite3 features,
> and it works very well.
>
> I connect from Perl to SQLite3 through DBI, because it's a no brainer. I
> don't know whether there are alternatives. Here is the connect statement:
>
>    my $dbh = DBI->connect("dbi:SQLite:dbname=DBItest.db","", "");
>
> Now I want to use an in-memory database, so I replaced my original connect
> statement with the one suggested at
> http://www.sqlite.org/cvstrac/wiki?p=PerlNotes :
>
>     my $dbh = DBI->Connect("dbi:SQLite:dbname=:memory:");
>
> However, this doesn't work, and I crash on that connect statement  with  the
> following error message:
>

DBI->connect()

note the lowercase connect()

> Can't locate auto/DBI/Connect.al in @INC (@INC contains:
> /Volumes/BLeopard/Applications/TextMate.app/Contents/SharedSupport/Bundles/P
> erl.tmbundle/Support /System/Library/Perl/5.8.8/darwin-thread-multi-2level
> /System/Library/Perl/5.8.8 /Library/Perl/5.8.8/darwin-thread-multi-2level
> /Library/Perl/5.8.8 /Library/Perl
> /Network/Library/Perl/5.8.8/darwin-thread-multi-2level
> /Network/Library/Perl/5.8.8 /Network/Library/Perl
> /System/Library/Perl/Extras/5.8.8/darwin-thread-multi-2level
> /System/Library/Perl/Extras/5.8.8 /Library/Perl/5.8.6 /Library/Perl/5.8.1 .)
> at /Volumes/Prune/DBItest.pl line 26
>
> To me the error message is rather puzzling as the exact same code works in
> the case of a filename with no colons. Maybe the colons are screwing the
> connect statement, since the connect statement itself uses colons as a
> separator?
>
> Either way, everything I tried failed, including silly things like varying
> additional connect parameters or string delimitors. Does DBI support
> in-memory SQLite3 databases? If so, how? Otherwise, are there any
> alternative?
>
> I use SQLite3 version 3.4.0. Please note that using ":memory:" from the
> command line sqlite3 tool works fine.
>
> My configuration: MacOS X 10.5.6, Perl 5.8.8, DBI 1.6.7.
>
> Please note that I also posted a similar request to comp.lang.perl.misc, as
> I am quite unsure of where best to go for help.
>
> If anybody out there had any clue, I'd be very grateful.
>
> Many many thanks,
>
> Jean-Denis
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Carbon Model http://carbonmodel.org/
Open Source Geospatial Foundation http://www.osgeo.org/
Sent from: Madison WI United States.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime as integer

[P Kishor]

On Mon, Mar 16, 2009 at 9:00 AM, MikeW  wrote:
> Timothy A. Sawyer  writes:
>
>>
>> I stand corrected on the math
>>
>> Bottom line is that different applications use different baseline values for
> epoch (beginning of time)
>> and you must know that baseline value
>> --Original Message--
>> From: Kees Nuyt
>> Sender: sqlite-users-boun...@...
>> To: sqlite-us...@...
>> ReplyTo: sqlite-us...@...
>> Sent: Mar 13, 2009 14:58
>> Subject: Re: [sqlite] datetime as integer
>>
>> Just a few corrections.
>>
> SNIP
>> For date calculations, SQLite prefers real values containing
>> number of days since noon in Greenwich on November 24, 4714
>> B.C., using the Proleptic Gregorian calendar:
> SNIP
>
> "noon in Greenwich on November 24, 4714 BC" -
> presumably that's the beginning of time for Creationists ...
>
> MikeW
> ;-)
>


guess it is better than using the apoplectic calendar.



-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Carbon Model http://carbonmodel.org/
Open Source Geospatial Foundation http://www.osgeo.org/
Sent from: Madison WI United States.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] In-memory databases from Perl

[Jean-Denis Muys]

On 3/16/09 3:03 PM, "Jean-Denis Muys"  wrote:


> Now I want to use an in-memory database, so I replaced my original connect
> statement with the one suggested at
> http://www.sqlite.org/cvstrac/wiki?p=PerlNotes :
> 
>  my $dbh = DBI->Connect("dbi:SQLite:dbname=:memory:");
> 
> However, this doesn't work, and I crash on that connect statement  with  the
> following error message:
> 


Silly me. I just realized that I wrote "Connect" instead of "connect"

I apologize for the waste of bandwidth and of your attention.

Sigh...

Jean-Denis

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

Re: [sqlite] starting INTEGER PRIMARY KEY at 0

[P Kishor]

On Mon, Mar 16, 2009 at 8:59 AM, John Machin  wrote:
> On 17/03/2009 12:33 AM, P Kishor wrote:
>> On Mon, Mar 16, 2009 at 8:31 AM, P Kishor  wrote:
>>> is there a way to have a table start the INTEGER PRIMARY KEY sequence
>>> at 0 (or some other arbitrary number)?
>>>
>>> --
>>> Puneet Kishor
>>>
>>
>> I should have added.. yes, I can do the following
>>
>> CREATE TABLE foo (a INTEGER PRIMARY KEY, b TEXT);
>> INSERT INTO foo VALUES (0, 'blah');
>>
>> but, I want to do
>>
>> INSERT INTO foo (b) VALUES ('blah');
>>
>> additionally, are their any gotchas with forcing INTEGER PRIMARY KEY
>> (hence, the ROWID) to start from 0?
>>
>>
> Hi Puneet,
>
> Have you read this: http://www.sqlite.org/autoinc.html ? The first part
> appears to cover non-autoincrement as a background to explaining the
> subtle differences with auto increment.

right... read that.

>
> It appears that you will need to write the first ROWID explicitly.
> There may be a gotcha with zero, otherwise why pick 1 for the default?

dunno... only DRH can tell, but it just may be convention. My modeling
program uses 0 as the first index, and arrays in C and Perl start at 0
as well. But SQLite implements 'stuff' starting at base 1. For
example, consider

sqlite> SELECT substr('blah', 1, 1);
b
sqlite> SELECT substr('blah', 0, 1);

sqlite>






> What are you trying to achieve? If you are going to let the software
> choose your PK for you, why do you care what the starting value is?

compatibility. And, as 'they' say, 0 is a perfectly fine number. Why
let it go waste.


>
> Cheers,
> John





-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Carbon Model http://carbonmodel.org/
Open Source Geospatial Foundation http://www.osgeo.org/
Sent from: Madison WI United States.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] In-memory databases from Perl

[Jean-Denis Muys]

Hello,

I am quite a newbie but I already spent a significant amount of time on
Google, to no avail.

I have a very small Perl program which exercises the basic SQLite3 features,
and it works very well.

I connect from Perl to SQLite3 through DBI, because it's a no brainer. I
don't know whether there are alternatives. Here is the connect statement:

my $dbh = DBI->connect("dbi:SQLite:dbname=DBItest.db","", "");

Now I want to use an in-memory database, so I replaced my original connect
statement with the one suggested at
http://www.sqlite.org/cvstrac/wiki?p=PerlNotes :

 my $dbh = DBI->Connect("dbi:SQLite:dbname=:memory:");

However, this doesn't work, and I crash on that connect statement  with  the
following error message:

Can't locate auto/DBI/Connect.al in @INC (@INC contains:
/Volumes/BLeopard/Applications/TextMate.app/Contents/SharedSupport/Bundles/P
erl.tmbundle/Support /System/Library/Perl/5.8.8/darwin-thread-multi-2level
/System/Library/Perl/5.8.8 /Library/Perl/5.8.8/darwin-thread-multi-2level
/Library/Perl/5.8.8 /Library/Perl
/Network/Library/Perl/5.8.8/darwin-thread-multi-2level
/Network/Library/Perl/5.8.8 /Network/Library/Perl
/System/Library/Perl/Extras/5.8.8/darwin-thread-multi-2level
/System/Library/Perl/Extras/5.8.8 /Library/Perl/5.8.6 /Library/Perl/5.8.1 .)
at /Volumes/Prune/DBItest.pl line 26

To me the error message is rather puzzling as the exact same code works in
the case of a filename with no colons. Maybe the colons are screwing the
connect statement, since the connect statement itself uses colons as a
separator?

Either way, everything I tried failed, including silly things like varying
additional connect parameters or string delimitors. Does DBI support
in-memory SQLite3 databases? If so, how? Otherwise, are there any
alternative?

I use SQLite3 version 3.4.0. Please note that using ":memory:" from the
command line sqlite3 tool works fine.

My configuration: MacOS X 10.5.6, Perl 5.8.8, DBI 1.6.7.

Please note that I also posted a similar request to comp.lang.perl.misc, as
I am quite unsure of where best to go for help.

If anybody out there had any clue, I'd be very grateful.

Many many thanks,

Jean-Denis 

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

Re: [sqlite] datetime as integer

[MikeW]

Timothy A. Sawyer  writes:

> 
> I stand corrected on the math
> 
> Bottom line is that different applications use different baseline values for
epoch (beginning of time)
> and you must know that baseline value
> --Original Message--
> From: Kees Nuyt
> Sender: sqlite-users-boun...@...
> To: sqlite-us...@...
> ReplyTo: sqlite-us...@...
> Sent: Mar 13, 2009 14:58
> Subject: Re: [sqlite] datetime as integer
> 
> Just a few corrections.
>
SNIP
> For date calculations, SQLite prefers real values containing
> number of days since noon in Greenwich on November 24, 4714
> B.C., using the Proleptic Gregorian calendar:
SNIP

"noon in Greenwich on November 24, 4714 BC" - 
presumably that's the beginning of time for Creationists ...

MikeW
;-)

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

Re: [sqlite] starting INTEGER PRIMARY KEY at 0

[John Machin]

On 17/03/2009 12:33 AM, P Kishor wrote:
> On Mon, Mar 16, 2009 at 8:31 AM, P Kishor  wrote:
>> is there a way to have a table start the INTEGER PRIMARY KEY sequence
>> at 0 (or some other arbitrary number)?
>>
>> --
>> Puneet Kishor
>>
> 
> I should have added.. yes, I can do the following
> 
> CREATE TABLE foo (a INTEGER PRIMARY KEY, b TEXT);
> INSERT INTO foo VALUES (0, 'blah');
> 
> but, I want to do
> 
> INSERT INTO foo (b) VALUES ('blah');
> 
> additionally, are their any gotchas with forcing INTEGER PRIMARY KEY
> (hence, the ROWID) to start from 0?
> 
> 
Hi Puneet,

Have you read this: http://www.sqlite.org/autoinc.html ? The first part 
appears to cover non-autoincrement as a background to explaining the 
subtle differences with auto increment.

It appears that you will need to write the first ROWID explicitly.
There may be a gotcha with zero, otherwise why pick 1 for the default?
What are you trying to achieve? If you are going to let the software 
choose your PK for you, why do you care what the starting value is?

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

Re: [sqlite] starting INTEGER PRIMARY KEY at 0

[P Kishor]

On Mon, Mar 16, 2009 at 8:31 AM, P Kishor  wrote:
> is there a way to have a table start the INTEGER PRIMARY KEY sequence
> at 0 (or some other arbitrary number)?
>
> --
> Puneet Kishor
>

I should have added.. yes, I can do the following

CREATE TABLE foo (a INTEGER PRIMARY KEY, b TEXT);
INSERT INTO foo VALUES (0, 'blah');

but, I want to do

INSERT INTO foo (b) VALUES ('blah');

additionally, are their any gotchas with forcing INTEGER PRIMARY KEY
(hence, the ROWID) to start from 0?


-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Carbon Model http://carbonmodel.org/
Open Source Geospatial Foundation http://www.osgeo.org/
Sent from: Madison WI United States.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] starting INTEGER PRIMARY KEY at 0

[P Kishor]

is there a way to have a table start the INTEGER PRIMARY KEY sequence
at 0 (or some other arbitrary number)?

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

Re: [sqlite] Perl DBD Question

[David Westbrook]

At the app level you can do something like this to check for the
"database is locked(5)" error. Note the sleep and max ~1000 attempts
functionality as well.

my $ct = 0;
while( $ct++ < 1000 ){
  $dbh->do($sql, {}, @bind);
  if( $dbh->err == 5 ){  # If got a locked code, try again
sleep 1;
next;
  }
  ...
}


On Mon, Mar 16, 2009 at 7:05 AM, marcos rebelo  wrote:
> Hi all
>
> I'm a Perl programmer using SQLite
>
> I want to retry to execute every command automatically, until the DB
> is not locked. In C seems that I need to set the busy_handler.
>
> How do I do this with DBD in Perl?
>
> Thanks for any help
>
> Best Regards
> Marcos Rebelo
>
> --
> Marcos Rebelo
> http://oleber.freehostia.com
> Milan Perl Mongers leader http://milan.pm.org
> ___
> 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] Perl DBD Question

[marcos rebelo]

Hi all

I'm a Perl programmer using SQLite

I want to retry to execute every command automatically, until the DB
is not locked. In C seems that I need to set the busy_handler.

How do I do this with DBD in Perl?

Thanks for any help

Best Regards
Marcos Rebelo

-- 
Marcos Rebelo
http://oleber.freehostia.com
Milan Perl Mongers leader http://milan.pm.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting field data back from SQLite db

[Simon Davies]

2009/3/16  :
> Hello,
>
> I am new on SQLite so bear with me :-)
>
> Can someone give me a simple c solution on following:
>
> I execute select telnr from contacts where name="David"
>
> I just want to get from the found record the content of field telnr back to 
> my c
> program in variable c_telnr.
>
> Thanks in advance,
>
> Danny
> Belgium
>

Hi Danny,

See http://www.sqlite.org/c3ref/prepare.html
   http://www.sqlite.org/c3ref/bind_blob.html,
   http://www.sqlite.org/c3ref/step.html
   http://www.sqlite.org/c3ref/column_blob.html

Experiment with

int get_telnr( char** c_telnr, sqlite3* db, char* name )
{
char* sql = "SELECT telnr FROM contacts WHERE name=?;";
char* tail;
const char* data;
sqlite3_stmt* stmt;
int rc = sqlite3_prepare_v2( db,
sql,
strlen( sql ),
,
 );
if( SQLITE_OK == rc )
{
rc = sqlite3_bind_text( stmt, 1, name, strlen( name ), SQLITE_STATIC );
if( SQLITE_OK == rc )
{
rc = sqlite3_step( stmt );
if( SQLITE_ROW == rc )
{
data = sqlite3_column_text( stmt, 0 );
if( data )
{
*c_telnr = (char*)malloc( strlen( data ) + 1 );
strcpy( *c_telnr, data );
}
}
}
}
return( rc );
}

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

Re: [sqlite] How to Use Dot Commands from the Library

[Mihai Limbasan]

You cannot do that. The dot commands are not SQL, and SQLite only 
understands SQL. The dot commands are provided by the shell executable. 
You could simply look at its source code, see how each specific command 
is implemented, and reimplement that functionality in your app.

HG wrote:
> I want to get a dump of an sqlite3 database using the sqlite3 library without 
> adding a dependency on the sqlite3 executable.
>
> Does the sqlite3 library support execution of dot commands. The following 
> fails for me :-
>
> --
> import sqlite3
>
> conn = sqlite3.connect('media_db.db')
> c = conn.cursor()
> c.execute('.dump\n')
> c.execute('.dump')
> conn.commit()
> c.close()
>
> Traceback (most recent call last):
>   File "test.py", line 5, in 
> c.execute('.dump\n')
> sqlite3.OperationalError: near ".": syntax error
> --
>
> Kindly suggest.
>
> Thank You,
> Himanshu
>
>
>   Add more friends to your messenger and enjoy! Go to 
> http://messenger.yahoo.com/invite/
> ___
> 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] How to Use Dot Commands from the Library

[HG]

I want to get a dump of an sqlite3 database using the sqlite3 library without 
adding a dependency on the sqlite3 executable.

Does the sqlite3 library support execution of dot commands. The following fails 
for me :-

--
import sqlite3

conn = sqlite3.connect('media_db.db')
c = conn.cursor()
c.execute('.dump\n')
c.execute('.dump')
conn.commit()
c.close()

Traceback (most recent call last):
  File "test.py", line 5, in 
c.execute('.dump\n')
sqlite3.OperationalError: near ".": syntax error
--

Kindly suggest.

Thank You,
Himanshu


  Add more friends to your messenger and enjoy! Go to 
http://messenger.yahoo.com/invite/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Mozilla Firefox 3.0.7 using SQLITE 3.5.9 crash on AIX

[Shailendra Jain]

Hi,

 While running Mozilla Firefox 3 on AIX platform, the application crashed with 
below stacktrace information. 

sqlite3_result_value() at 0x900077bb084
minMaxFinalize() 
sqlite3VdbememFinalize() 
sqlite3VdbeExec()
sqlite3Step()
ExecuteStep_19mozStorageStatementFPi()
Init_12nsNavHistoryEv()
getSingleton_12nsNavHitoryFv()
nsnavhistoryconstructor_fp11nsisupportsrc4nsid...@af15_1()
nsGenericFactory.CreateInstance()
GetService_22nsComponentManagerImpl()
NS_InvokedByIndex_P()
CallMethod_16XPCWrappedNative()
js_Invoke()
js_Interpret()
js_Invoke()
PrepareAndDispatch()
XRE_main()
main()

Note : The .mozconfig had the line -> ac_add_options --disable-debug

How to go about debugging this further ?  

I did see some debug options for sqlite3 such as SQLITE_DEBUG and 
SQLITE_MEMDEBUG. I tried setting these ( -DSQLITE_DEBUG=2 -DSQLITE_MEMDEBUG=1) 
in the makefile of mozilla/db/sqlite3/src with below line in .mozconfig.

ac_add_options --disable-debug

But I was not able to get any debug statements or tracefile generated.

Could you please advise how to go about the same ?

Thanks,
Shailendra

   

   


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

[sqlite] Getting field data back from SQLite db

[ddk]

Hello,

I am new on SQLite so bear with me :-)

Can someone give me a simple c solution on following:

I execute select telnr from contacts where name="David"

I just want to get from the found record the content of field telnr back to my c
program in variable c_telnr.

Thanks in advance,

Danny
Belgium


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

Re: [sqlite] Advices to get max performance with SQLITE and BLOBS

[John Machin]

On 16/03/2009 5:48 PM, Pierre Chatelier wrote:
>> A few hundred blocks of raw data? Blocksize approx 300K bytes?  
>> Database
>> created and dropped by the same process? 500 blocks is approx 150M
>> bytes; why not keep it in a hash table in memory? If you keep it in a
>> database or the file system, it's going to be washed through your real
>> memory and pagefile-aka-swap-partition anyway, so just cut out the
>> middlemen :-)
> 
> You're right, but who said I have only 1 DB at a time :-) ?

You didn't say anything at all about how many DBs you have, so it wasn't 
you.


> In fact, I have several DBs and I do not known in advance what size it  
> will represent.

What is "it"?

> Perhaps 500MB. And I need RAM for other stuff, so the  
> simplest thing is to use "normal" DBs.

You've lost me now. You need RAM for your working set of whatever you 
are acccessing at the time, doesn't matter whether it came from a file 
or a DB (which is just a structured file, probably not optimised for 
300KB BLOBs) or you built it in memory, and what's not being used at the 
time will be in your filesystem or in your swap partition.

Please re-read what I wrote, to which your response was "You're right", 
then consider that the total amount of data is not very relevant, what 
matters is the size of your working set, mostly irrespective of its source.

However the overhead of packing/unpacking 300KB blobs into/out of a 
database can't be overlooked.

I would suggest giving serious thought to a variant of an earlier 
poster's suggestion: have the BLOBs each in its own file in the file 
system, but mmap them.


> Using memory DBs and swapping  
> them aftwerwards would not be smooth.
> 
> But we are not answering my initial question !
> 
> Can I expect some gain in
> -recompiling SQLite (which options/DEFINEs would help ?)
> -using custom memory allocators (I am on Win32, in a multi-threaded  
> environment, and yes, "it's bad")
> -using compression

Compression? You tell us. What percentage compression do you get with 
these 300KB BLOBs with (say) bz2? How long does it take to read in a 
bz2-compressed BLOB and uncompress it compared to reading in an 
uncompressed BLOB?

Cheers,
John


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

Re: [sqlite] Advices to get max performance with SQLITE and BLOBS

[Pierre Chatelier]

> A few hundred blocks of raw data? Blocksize approx 300K bytes?  
> Database
> created and dropped by the same process? 500 blocks is approx 150M
> bytes; why not keep it in a hash table in memory? If you keep it in a
> database or the file system, it's going to be washed through your real
> memory and pagefile-aka-swap-partition anyway, so just cut out the
> middlemen :-)

You're right, but who said I have only 1 DB at a time :-) ?
In fact, I have several DBs and I do not known in advance what size it  
will represent. Perhaps 500MB. And I need RAM for other stuff, so the  
simplest thing is to use "normal" DBs. Using memory DBs and swapping  
them aftwerwards would not be smooth.

But we are not answering my initial question !

Can I expect some gain in
-recompiling SQLite (which options/DEFINEs would help ?)
-using custom memory allocators (I am on Win32, in a multi-threaded  
environment, and yes, "it's bad")
-using compression

Regards,

Pierre Chatelier

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

Re: [sqlite] IP from number with SQL

[Roger Andersson]

Thanks John, appreciated!


-Ursprungligt meddelande-
Från: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] För John Machin
Skickat: den 16 mars 2009 00:51
Till: sqlite-users@sqlite.org
Ämne: Re: [sqlite] IP from number with SQL

On 16/03/2009 8:48 AM, Kees Nuyt wrote:
> On Sun, 15 Mar 2009 21:10:02 +0100, "Roger Andersson"
>  wrote:
> 
>> Hi!
>>
>> The SQL below might be out there but I didn't find it and since there 
>> might be other that need to get 32-bit integer IP in a sqlite3 
>> database to the a.b.c.d format using SQL
>>
>> I did get started from
>> http://acidlab.sourceforge.net/acid_faq.html#faq_e1
>> and for me what's below does the trick in sqlite3 :-)
>>
>> SELECT
>>  CAST((intIP & 4278190080) >> 24 AS text)||'.'||  CAST((intIP & 
>> 16711680) >> 16 AS text)||'.'||  CAST((intIP & 65280) >> 8 AS 
>> text)||'.'||  CAST((intIP & 255) AS text) AS strIP FROM IP_table;
> 
> Cute code, thanks.

We appear to have differing meanings for "cute" :-) Following are two
iterations of make-over:

SQLite version 3.6.10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE IP_table (intIP integer);
sqlite>
sqlite> INSERT INTO IP_table VALUES(12345678); INSERT INTO IP_table 
sqlite> VALUES(9876543210123);
sqlite>
sqlite> SELECT rowid, intIP,
...>   CAST((intIP & 4278190080) >> 24 AS text)||'.'||
...>   CAST((intIP & 16711680) >> 16 AS text)||'.'||
...>   CAST((intIP & 65280) >> 8 AS text)||'.'||
...>   CAST((intIP & 255) AS text) AS strIP
...> FROM IP_table;
1|12345678|0.188.97.78
2|9876543210123|143.217.130.139
sqlite>
sqlite> SELECT rowid, intIP,
...>   CAST((intIP >> 24) & 255 AS text)||'.'||
...>   CAST((intIP >> 16) & 255 AS text)||'.'||
...>   CAST((intIP >>  8) & 255 AS text)||'.'||
...>   CAST((intIP  ) & 255 AS text) AS strIP
...> FROM IP_table;
1|12345678|0.188.97.78
2|9876543210123|143.217.130.139
sqlite>
sqlite> SELECT rowid, intIP,
...>   ((intIP >> 24) & 255) ||'.'||
...>   ((intIP >> 16) & 255) ||'.'||
...>   ((intIP >>  8) & 255) ||'.'||
...>   ((intIP  ) & 255) AS strIP
...> FROM IP_table;
1|12345678|0.188.97.78
2|9876543210123|143.217.130.139
sqlite>

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