Re: [sqlite] Widen output of file field for .databases CLI command

2011-08-25 Thread Clark Christensen
Hi Keith,

How about

pragma database_list;

 -Clark




From: Keith Christian 
To: sqlite-users@sqlite.org
Sent: Thursday, August 25, 2011 8:55 AM
Subject: [sqlite] Widen output of file field for .databases CLI command

Is there a parameter that will widen the "file" column when
".databases" is typed at the sqlite> prompt?  Occasionally the
database is several directories deep and the filename is lost or
truncated if the complete path is more than 58 characters wide.

If no parameter exists, could the "file" field be widened?

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


Re: [sqlite] SQLite version 3.7.5

2011-02-01 Thread Clark Christensen
- Original Message 
From: Richard Hipp 
To: General Discussion of SQLite Database ; 
sqlite-annou...@sqlite.org
Sent: Mon, January 31, 2011 5:32:15 PM
Subject: [sqlite] SQLite version 3.7.5

>As of this release, the popular ADO.NET provider for SQLite by Robert
>Simpson, System.Data.SQLite, is hosted on the SQLite website.  See
>http://System.Data.SQLite.org/ for additional information.  Release builds
>of System.Data.SQLite will appears on the SQLite download page over the
>course of the next week.

Very impressive.  Is there a story to tell about how the SQLite team came to 
choose this path?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] EXTERNAL:Re: Can't delete a row

2011-01-28 Thread Clark Christensen
- Original Message 
From: Puneet Kishor 
To: General Discussion of SQLite Database 
Sent: Fri, January 28, 2011 9:33:15 AM
Subject: Re: [sqlite] EXTERNAL:Re:  Can't delete a row


>Because I find using bind values easier, clearer, and safer, even for 
>one-off execute statements.

Me too.  And it works great with $dbh->do, too.

my $rows_affected = $dbh->do('DELETE FROM contacts WHERE rowid = ?', undef, $q);


 -Clark

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


Re: [sqlite] using SQLite with mod_perl

2010-10-11 Thread Clark Christensen
>So, I want all the perl modules to be loaded when Apache2 starts, and
>then a $dbh created for each user when the user comes to the web site,
>but not recreated for the same user on every reload. I am assuming
>that would be the correct way to work speedily.

I had the same problem, and it's a huge pain, even when you have root privilege.

I never did completely solve it.  I had some success with explicit 
$dbh->disconnect calls before the script exits, but I could never prove why it 
worked.  Ultimately, I abandoned mod_perl.  Having to restart Apache every time 
I update code in a module was a big obstacle.

I always thought having a single $dbh across all instances (open it in an init 
script that runs when Apache starts, and never close it) might be the correct 
approach.

Near as I can tell, the alternative is to have a per-request connection.  To 
get 
this with mod_perl, you have to define and export $dbh in a module.  Though I 
never retrofotted it back into the app I was running under mod_perl, it got me 
in the habit of writing an app-specific init.pm where I could define truly 
transient request-global variables.  Then I just use the init module in every 
script in the app.

 -Clark



- Original Message 
From: P Kishor 
To: General Discussion of SQLite Database 
Sent: Fri, October 8, 2010 11:49:43 PM
Subject: [sqlite] using SQLite with mod_perl

This is a perl question really, so apologies to the SQLite community.
However, I am stuck, and I am flailing on various forums
(perlmonks/stackoverflow), hoping to strike lucky. My problem is that
I am running into the "database locked" error under mod_perl with
Apache2. I thought I had surmounted this problem, but it is back with
a vengeance.

Here is what is going on in a single web action --

SELECT col1 FROM table WHERE condition;
if (col1 exists) {
UPDATE table SET col2 = 
}

Well, I get the "db is locked" error at the UPDATE statement. Now, in
my httpd.conf I started using

PerlModule Apache::DBI

and I thought my troubles were over. Except, this lock problem has
started rearing up again. I have tried a few other things, such as
starting every db action with `$dbh->begin_work` and ending with
`$dbh->commit`, but I am already using `AutoCommit => 1` so I am not
sure if that even has any effect.

My application is being loaded in Apache conf file with


SetHandler perl-script
PerlHandler Plack::Handler::Apache2
PerlSetVar psgi_app /path/to/application.pl


So, I want all the perl modules to be loaded when Apache2 starts, and
then a $dbh created for each user when the user comes to the web site,
but not recreated for the same user on every reload. I am assuming
that would be the correct way to work speedily.

Perhaps SQLite is the wrong tool to use with mod_perl (or any
persistent web environment), but I want to establish that for sure
before trying some other db.

Any ideas?

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

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


Re: [sqlite] BUG - Documentation

2010-01-15 Thread Clark Christensen
Perhaps "...to whole underlying operating system..." should be "...to the whole 
underlying operating system..."


- Original Message 
From: Dennis Cote 
To: General Discussion of SQLite Database 
Sent: Thu, January 14, 2010 4:38:48 PM
Subject: [sqlite] BUG - Documentation

On the website page at http://www.sqlite.org/custombuild.html

The following sentence appears:

"This object is somewhat misnamed since it is really an interface to 
whole underlying operating system, just the filesystem."

I think it should be changed to:

"This object is somewhat misnamed since it is really an interface to 
whole underlying operating system, *NOT* just the filesystem."

Or something similar. Maybe the NOT doesn't need that much emphasis, but 
it should be there.

HTH
Dennis Cote

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

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


Re: [sqlite] [Windows] Good GUI alternative to sqlite.exe?

2009-10-23 Thread Clark Christensen
>SQLiteSpy (www.yunqa.de) is OK, but unless I missed the option, it
>won't let me copy the output of a SELECT into the clipboard so I can
>paste it elsewhere.

FWIW, I'm pretty happy with SQLiteSpy, even though I don't use it for 
copy/paste of results.  I used SQLite Explorer before, and am happy with the 
switch.

In the (admittedly older) version I have, I can click on any cell in the 
results, press Ctrl-A, Ctrl-C, and it appears to copy the resultset into the 
clipboard.  I just pasted a couple thousand rows of results into both Excel, 
and into a text editor following these steps.


 -Clark


- Original Message 
From: Gilles Ganault 
To: sqlite-users@sqlite.org
Sent: Fri, October 23, 2009 2:45:18 AM
Subject: [sqlite] [Windows] Good GUI alternative to sqlite.exe?

Hello

I'm looking for a Windows alternative to the CLI sqlite.exe to manage
SQLite databases.

SQLiteSpy (www.yunqa.de) is OK, but unless I missed the option, it
won't let me copy the output of a SELECT into the clipboard so I can
paste it elsewhere.

Are there better alternatives?

Thank you.

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

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


Re: [sqlite] suggestions for avoiding "database locked" on ajax

2009-10-19 Thread Clark Christensen
>But, if I enter something in the field, and, instead of tabbing out of
>it, if I click on the submit button, two events fire simultaneously.
>There is the onblur() from the field (this is a SELECT query), and the
>submit from the form (this is an UPDATE/INSERT query). The events
>reach sqlite simultaneously, and it croaks.

I seem to remember Javascript offers no guarantee about which event fires 
first.  It's browser-dependent.  So I think, as you originally indicated, this 
is to be avoided.

Perhaps onBlur isn't the best event to listen for.  Maybe an onFocus handler on 
the second field, would be more appropriate.


>Mind it, I am not talking about Amazon.com here. But, even with a few
>hundred users, someone is likely to hit the db at the same time
>someone else is hitting it. How do you all manage this situation?

I see the more knowledgeable among us have weighed-in.  For my $0.02, I'd say 
make sure the busy_timeout is set appropriately.  Also, I have the impression 
(on Windows, anyway), localhost seems more prone to this than a remote Apache 
box.  ISTR you're a Mac user, so none of this may apply :-))




- Original Message 
From: P Kishor <punk.k...@gmail.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Mon, October 19, 2009 9:44:39 AM
Subject: Re: [sqlite] suggestions for avoiding "database locked" on ajax

Let me shed a bit more light on this (as I have understood). Top
posting follows --

What is happening is that I have an onblur() action on a field which
sends off a request to check the validity of the entered text. Then,
of course, I have the submit button which also sends off the entire
form to the server for more work. If a user (me, when testing the
darned thing) tabs away from the field with onblur(), all is well. The
onblur() fires, the request is sent to the server, sqlite responds,
all is well in the 20 acre woods.

But, if I enter something in the field, and, instead of tabbing out of
it, if I click on the submit button, two events fire simultaneously.
There is the onblur() from the field (this is a SELECT query), and the
submit from the form (this is an UPDATE/INSERT query). The events
reach sqlite simultaneously, and it croaks.

Now, here is the question -- in real life, the web server would
receive many concurrent requests, not just the one described above.
What happens in that case? If there is an UPDATE/INSERT request while
a SELECT is happens to be happening, there is going to be a block, no?
Putting logic in my code to keep retrying till a query (SELECT or
UPDATE/INSERT) succeeds is going to be very messy. Does this make
sqlite unsuitable for a web application?

Mind it, I am not talking about Amazon.com here. But, even with a few
hundred users, someone is likely to hit the db at the same time
someone else is hitting it. How do you all manage this situation?



On Sun, Oct 18, 2009 at 2:28 PM, Clark Christensen <cdcmi...@yahoo.com> wrote:
>>Ajax is always asynchronous. That is what the first "A" in Ajax is.
>
> Well, not quite always.  The open() method of the XMLHttpRequest object lets 
> you specify syncronous, or async.  But, since you're using jQuery's wrappers, 
> it's async.  Good choice.  Love jQuery.
>
>>I am not doing $dbh->disconnect. DBI is supposed to that automatically
>>when the script finishes. Don't have any issues other than during
>>these Ajax calls.
>
> I agree.  It should.  But I wonder if it takes more time for the forked Perl 
> process to clean-up and close than you're expecting.  I never did figure out 
> the exact cause and effect.  I just $dbh->disconnect (before printing the 
> response where appropriate) and the issue usually seems to resolve itself.
>
>
>
>>I don't have any timeout set at all. Roger Binns suggestion a
>>busy_timeout, but I am not sure how to even use that. I am using DBI.
>>Where do I set sqlite busy_timeout? Besides, how would that help me?
>
> If you're currently set to a short value, lengthening it might help 
> narrow-down the real culprit (or prove a timing issue).
>
> DBD-SQLite adds this driver private method
>
>   $dbh->func(  'busy_timeout' );  # getter
>   $dbh->func( $ms, 'busy_timeout' );  # setter
>
> I seem to think it defaults to 30 seconds, but I don't see it documented now.
>
> If you move the second AJAX request from the onBlur event, to an
> explicit user click (button, link, etc), do you still get the locked
> DB?  If not, I think it would help prove it's a timing issue (or not).
>
>
>
> - Original Message 
> From: P Kishor <punk.k...@gmail.com>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Sent: Sat, October 17, 2009 8:07:09 PM
> Subject: Re: [sqlite] suggestions for avoiding "databas

Re: [sqlite] suggestions for avoiding "database locked" on ajax

2009-10-18 Thread Clark Christensen
>Ajax is always asynchronous. That is what the first "A" in Ajax is.

Well, not quite always.  The open() method of the XMLHttpRequest object lets 
you specify syncronous, or async.  But, since you're using jQuery's wrappers, 
it's async.  Good choice.  Love jQuery.

>I am not doing $dbh->disconnect. DBI is supposed to that automatically
>when the script finishes. Don't have any issues other than during
>these Ajax calls.

I agree.  It should.  But I wonder if it takes more time for the forked Perl 
process to clean-up and close than you're expecting.  I never did figure out 
the exact cause and effect.  I just $dbh->disconnect (before printing the 
response where appropriate) and the issue usually seems to resolve itself.



>I don't have any timeout set at all. Roger Binns suggestion a
>busy_timeout, but I am not sure how to even use that. I am using DBI.
>Where do I set sqlite busy_timeout? Besides, how would that help me?

If you're currently set to a short value, lengthening it might help narrow-down 
the real culprit (or prove a timing issue).

DBD-SQLite adds this driver private method

   $dbh->func(  'busy_timeout' );  # getter
   $dbh->func( $ms, 'busy_timeout' );  # setter

I seem to think it defaults to 30 seconds, but I don't see it documented now.

If you move the second AJAX request from the onBlur event, to an
explicit user click (button, link, etc), do you still get the locked
DB?  If not, I think it would help prove it's a timing issue (or not).



- Original Message 
From: P Kishor <punk.k...@gmail.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Sat, October 17, 2009 8:07:09 PM
Subject: Re: [sqlite] suggestions for avoiding "database locked" on ajax

On Sat, Oct 17, 2009 at 7:16 PM, Clark Christensen <cdcmi...@yahoo.com> wrote:
> Sorry for top-posting...
>
> What's running on the the server?  A Perl CGI script?  Apache HTTPD?  
> mod-perl?

Although I have Apache mod_perl installed, I am running a plain
vanilla Perl cgi script for now.

>
> Is the AJAX exchange asyncronous?

Ajax is always asynchronous. That is what the first "A" in Ajax is.

> Are you sure the first AJAX exchange is finished when the second one fires?

Am I sure? Not really. I guess it is not finished when the second one
fires, which is what causes the database lock, no?

> Does the AJAX request wait for a 200 response?

I am using jQuery. It does what it does. I don't do anything special.

>
> Assuming Perl, are you explicitly closing the DB with  $dbh->disconnect (as 
> opposed to $dbh = undef)?  Does the script end with an exit instruction?

I am not doing $dbh->disconnect. DBI is supposed to that automatically
when the script finishes. Don't have any issues other than during
these Ajax calls.

>  I'm guessing you're sure there's no writer or writers that jumped-in.

I am sure no other process is interfering other than what I have
specified. I am sure because I am the only one using my laptop on
which I am doing the development. :-)


> Are you doing anything like modifying the default 30 second busy_timeout?  
> Perhaps to too short a timeout?

I don't have any timeout set at all. Roger Binns suggestion a
busy_timeout, but I am not sure how to even use that. I am using DBI.
Where do I set sqlite busy_timeout? Besides, how would that help me?

>
> Sometimes it's useful to $dbh->disconnect before you print the response.
>
> I've seen this happen from time to time in my own environment.  It's annoying 
> as hell.   It seems to always come down to when and how I disconnect from the 
> DB file.
>
> I seldom see it in regular CGI scripts.  More often, I see it with 
> mod_perl::PerlRun (I don't use mod_perl::Registry) when I open the $dbh in a 
> module where $dbh is exported into the main package.

No, as I described, what I think is happening is that two Ajax events
are hitting the db at the nearly the same time. The first one is a
select (checking the db if the user exists), and the second is an
insert (creating a user because the user didn't exist). Even though
Javascript has gotten the response from the first event, and started
on the second process. sqlite is still busy from the first process, so
throws a lockout.

Yes, it is very annoying. I have to figure out some other shenanigans
to prevent this from happening.

>
>  -Clark
>
>
>
> - Original Message 
> From: P Kishor <punk.k...@gmail.com>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Sent: Fri, October 16, 2009 12:53:28 PM
> Subject: [sqlite] suggestions for avoiding "database locked" on ajax
>
> Yes, I know, if it "hurts when I press here," then I shouldn't "press
> here," but, so it goes...
>
> I have an account creation fo

Re: [sqlite] suggestions for avoiding "database locked" on ajax

2009-10-17 Thread Clark Christensen
Sorry for top-posting...

What's running on the the server?  A Perl CGI script?  Apache HTTPD?  mod-perl?

Is the AJAX exchange asyncronous?  Are you sure the first AJAX exchange is 
finished when the second one fires?  Does the AJAX request wait for a 200 
response?

Assuming Perl, are you explicitly closing the DB with  $dbh->disconnect (as 
opposed to $dbh = undef)?  Does the script end with an exit instruction?  I'm 
guessing you're sure there's no writer or writers that jumped-in.  Are you 
doing anything like modifying the default 30 second busy_timeout?  Perhaps to 
too short a timeout?

Sometimes it's useful to $dbh->disconnect before you print the response.

I've seen this happen from time to time in my own environment.  It's annoying 
as hell.   It seems to always come down to when and how I disconnect from the 
DB file.  

I seldom see it in regular CGI scripts.  More often, I see it with 
mod_perl::PerlRun (I don't use mod_perl::Registry) when I open the $dbh in a 
module where $dbh is exported into the main package.

 -Clark



- Original Message 
From: P Kishor 
To: General Discussion of SQLite Database 
Sent: Fri, October 16, 2009 12:53:28 PM
Subject: [sqlite] suggestions for avoiding "database locked" on ajax

Yes, I know, if it "hurts when I press here," then I shouldn't "press
here," but, so it goes...

I have an account creation form -- users enter their email and their
desired username, and the form --

onblur from the username field, sends off an ajax request to see if
the desired username already exists in the db, and if yes, it sends
back a suggestion;

onblur from the email field, sends off an ajax request to see if the
email already exists in the db, and if yes...

If neither the username nor the email exist in the db, then the
application creates a record and informs the user of success. Except,
the previous ajax request (I am assuming it is the previous ajax
request from onblur event from the email field) has locked the
database, and the app returns an error that "the database is locked."

So, what suggestion might you all have for getting around this?



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] regular expression search

2009-10-15 Thread Clark Christensen
The current dev branch of DBD-SQLite (1.26_05) includes an implementation of 
SQLite's unimplemented REGEX function 
(http://search.cpan.org/~adamk/DBD-SQLite/lib/DBD/SQLite.pm#REGEXP_function).  
Presumably, this will survive to the next production release.

Otherwise, DBD-SQLite offers a custom function interface where you can write 
custom SQL functions in Perl.  Perhaps something like


$dbh->func( 'pattern_match', 2, sub { my ($input, $regex) = @_; return ($input 
=~ m/$regex/); }, 'create_function' );

and your SQL might be

select foo from mytable where pattern_match(foo, 'A[BCD]D[AD]BB') > 0;

Sorry, it's untested, off the top of my head.

 -Clark

- Original Message 
From: "Farkas, Illes" 
To: sqlite-users@sqlite.org
Sent: Thu, October 15, 2009 2:24:16 PM
Subject: [sqlite] regular expression search

Hi,

I have strings in a database and I would like to find all of them
matching a pattern that is 5-10 characters long. In each position of
the pattern up to three different characters may be allowed. This
would be a typical regular expression that I'd like to find:

A (B | C | D ) D ( A | D ) B B

(I use the Perl DBI with sqlite3)

Any help would be greatly appreciated. Thanks!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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


Re: [sqlite] Scalability.

2009-08-14 Thread Clark Christensen
> Your only problem is that you're at Stanford and Dr Hipp was at
> Duke so he hates you.

Not much humor on this list, but you made my day :-)

Very funny.

 -Clark

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


Re: [sqlite] speeding up row by row lookup in a large db

2009-03-23 Thread Clark Christensen

> Matt,

> Just wanted to say publicly that DBD::SQLite is the greatest thing
> since, well, SQLite. Thanks for making our lives easy.

+1

Great stuff.

 -Clark



- Original Message 
From: P Kishor 
To: General Discussion of SQLite Database 
Sent: Monday, March 23, 2009 2:27:17 PM
Subject: Re: [sqlite] speeding up row by row lookup in a large db

On Mon, Mar 23, 2009 at 4:04 PM, Matt Sergeant
 wrote:
> On Sat, 21 Mar 2009 23:42:30 +0100, Stefan Evert wrote:
>> On 21 Mar 2009, at 15:31, P Kishor wrote:
>>
..
>
>> (Darren, any news from the maintainer of DBD::SQLite?  I would be very
>> delighted and grateful to be able to use an up-to-date SQLite version
>> in my Perl scripts.)
>
> I'm here, I'm just very slow. I did try and pass it off to someone else
> as maintainer but they vanished. No idea why.
>


Matt,

Just wanted to say publicly that DBD::SQLite is the greatest thing
since, well, SQLite. Thanks for making our lives easy.


-- 
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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Newbie question

2009-03-18 Thread Clark Christensen

SQLite treats double-quoted strings as column and table identifiers.  Use 
single-quotes for literals.

SELECT * FROM tbl1 WHERE description='someval' AND
foreign_key_id=(select id from tbl2 where name='Joe');

 -Clark



- Original Message 
From: Dermot 
To: sqlite-users@sqlite.org
Sent: Wednesday, March 18, 2009 8:35:52 AM
Subject: [sqlite] Newbie question

Hi,

I am very green with SQL entirely so I apologise in advance for what
might be a simple query.

I want to do a select query a bit like this:

SELECT * FROM tbl1 WHERE description="someval" AND
foreign_key_id=(select id from tbl2 where name="Joe");

This gives me a syntax error and my other efforts are not yielding results.

I am not sure what the term is for such as statement, compound perhaps?

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

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


Re: [sqlite] parameters

2009-03-17 Thread Clark Christensen

Difficult to read, so it's unclear what your intention is.  But you mention 
JavaScript, so I'll take a stab...

Bound parameters can only contain literal values.  You can't use a bound 
parameter as an identifier for a table or column.


Do you really mean this:
...
 WHERE
users.user_id = ?
AND schedule.user_id = users.user_id
AND schedule.schedule_id = ?
AND main_detail.main_detail_id = ?
AND main_detail.property_id = properties.property_id
AND main_detail.order_id = orders.order_id
AND main_detail.activity_id = activities.activity_id
AND properties.organisation_id = organisation.organisation_id
AND activities.trade_id = trades.trade_id
AND users.user_type != ?

with bound param values:

[
this_user_id,
this_schedule_id,
this_main_detail_id,
'Subcontractor'
]


I believe that'll get you the join you're looking for.

 -Clark


- Original Message 
From: meerkat 
To: sqlite-users@sqlite.org
Sent: Tuesday, March 17, 2009 8:00:42 AM
Subject: [sqlite] parameters

Hello,



I am trying to bind some parameters in a query but I can't do it. I have the
following (JavaScript in html page):







var rs = db.execute('SELECT distinct '+

'schedule.schedule_id, ' +

'orders.order_no, '+

'orders.order_no_iteration, '+

'organisation.organisation_name, '+

'strftime(\'%H:%M\',schedule.the_start_time,\'unixepoch\')AS the_start_time,
'+

'strftime(\'%H:%M\',schedule.the_end_time,\'unixepoch\')AS the_end_time, '+

'date(schedule.the_date,\'unixepoch\',\'localtime\') AS the_date, '+

'schedule.user_id, '+

'properties.property_id, '+

'properties.property_name_number, '+

'properties.property_address1, '+

'properties.property_address2, '+

'properties.property_town, '+

'properties.property_postcode, '+

'properties.tenant_name, '+

'properties.tenant_tel, '+

'properties.tenant_mobile, '+

'date(orders.estimated_completion_date,\'unixepoch\',\'localtime\') AS
estimiated_completion_date, '+

'orders.other_contact_tel, '+

'main_detail.quantity, '+

'trades.trade_code, '+

'activities.activity_code, '+

'activities.unit_type, '+

'main_detail.activity_description, '+

'main_detail.main_detail_id, '+

'activities.activity_id, '+

'users.user_id, '+

'users.user_type, '+

'trades.trade_id '+

'FROM schedule,users,main_detail,properties,orders,activities,organisation,
trades '+

'WHERE schedule.user_id = ? '+

'AND users.user_id = ? '+

'AND schedule.schedule_id = ? '+

'AND main_detail.main_detail_id = ? '+

'AND main_detail.property_id = ? '+

'AND main_detail.order_id = ? '+

'AND main_detail.activity_id = ? '+

'AND properties.organisation_id = ? '+

'AND activities.trade_id = ? '+

'AND users.user_type != ?',['users.user_id',this_user_id
,this_schedule_id,this_main_detail_id,'properties.property_id','orders.order
_id','activities.activity_id','organisation.organisation_id','trades.trade_i
d','Subcontractor']);



I have tried $this_schedule_id or :this_schedule_id or ?this_schedule_id or
'$this_schedule_id' or ':this_schedule_id' or '?this_schedule_id'



I get the url variables ok with:

 var this_schedule_id = getValue("my_schedule_id");

  var this_user_id = getValue("user_id");

  var this_main_detail_id = getValue("main_detail_id");

  

  document.write("SID "+ this_schedule_id);

  document.write("\\");

  document.write("uID "+ this_user_id);

  document.write("\\");

  document.write("mdID "+ this_main_detail_id);



I get an empty result set even though I know that the variables produce a
row (tested in external sql program).



If anyone can suggest what I am doing wrong I would really appreciate your
help.



meerkat









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

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


Re: [sqlite] UPDATE Field based on matching value in different Table

2009-02-23 Thread Clark Christensen

I've been meaning to ask this myself.

I've been writing it like

update TableA 
set Field2 = (select Field4 from TableB where Field3 = TableA.Field1)
where Field1 = ?;  --perhaps

But it feels wrong.

 -Clark




- Original Message 
From: Greg Robertson 
To: sqlite-users@sqlite.org
Sent: Monday, February 23, 2009 3:34:15 PM
Subject: [sqlite] UPDATE Field based on matching value in different Table

I have two tables each with two fields:

TableA: Field1, Field2

TableB: Field3, Field4


I would like to set Field2 in TableA to the value in Field4 in TableB
where TableA.Field3=TableB.Field4

Sounds simple enough but I can't figure out how to write the UPDATE
SQLite for this.

Can someone help?

Thanks

Greg
___
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] Attached database atomic-ness

2009-02-20 Thread Clark Christensen

Hello,

In the docs for ATTACH, I see this information:

Transactions involving multiple attached databases are atomic,
assuming that the main database is not ":memory:".  If the main
database is ":memory:" then 
transactions continue to be atomic within each individual
database file. But if the host computer crashes in the middle
of a COMMIT where two or more database files are updated,
some of those files might get the changes where others
might not.

I'm looking for clarification on the last sentence:

"But if the host computer crashes in the middle
of a COMMIT where two or more database files are updated,
some of those files might get the changes where others
might not."

If both the main database, and the attached database(s) are disk-based, is 
there more risk of data loss during a crash in the middle of a COMMIT than with 
a single database file?

Thanks!

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


Re: [sqlite] A list as an SQL paramater

2009-01-14 Thread Clark Christensen

> What I'm comparing is the speed of SELECTing the list from a temporary
> table, vs. building a new query string with printf and calling
> sqlite3_prepare each time I want to execute it with a different $list.

Please forgive me if I'm missing a key point, but it sounds like you already 
have the list in a temp table.  If that's the case, wouldn't something like

SELECT * ... WHERE foreign.id IN (select foreign_id from temptable...)

do the job for you?

FWIW, I often find myself wanting to pass a list (array) of variable length as 
bound parameters.  I understand why it may not be practical, but it would be a 
nice feature.  Is this even part of the SQL standard?

 -Clark



- Original Message 
From: Sam Thursfield 
To: General Discussion of SQLite Database 
Sent: Wednesday, January 14, 2009 1:26:14 PM
Subject: Re: [sqlite] A list as an SQL paramater

On Wed, Jan 14, 2009 at 4:38 PM, Igor Tandetnik  wrote:
> Sam Thursfield  wrote:
 - binding text results in SELECT * ... WHERE foreign.id IN ("4. 3,
 6, 7") ...; which of course doesn't work.
>>
>> Do you know off hand whether this method would be much faster than
>> compiling a new query for each new list of ids?
>
> I'm not sure I understand the question. How can one compare the speed of
> the solution that doesn't work with one that does?
>

What I'm comparing is the speed of SELECTing the list from a temporary
table, vs. building a new query string with printf and calling
sqlite3_prepare each time I want to execute it with a different $list.
This would definitely work but does incur the overhead of
sqlite3_prepare each time I want to run the query.

I had an idea for a third method too, which would use a virtual table
instead of a temporary table, and get the list from the app to to the
query that way. I think I'm going to do some profiling to found out
which method is the fastest.

MikeW, that's an ingenious idea but I have slightly more than 64 rows :)

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

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


Re: [sqlite] request to become co-maintainer of DBD::SQLite

2009-01-14 Thread Clark Christensen

> One of my first code changes will be to require DBI 1.607+

The current DBD-SQLite works fine under older versions of DBI.  So unless 
there's a compelling reason to do it, I would prefer you not make what seems 
like an arbitrary requirement.

Otherwise, it sounds like a good start.  Matt must be really busy with other 
work.

I'll be happy to contribute where I can, but no C-fu here, either :-(

 -Clark



- Original Message 
From: Darren Duncan 
To: m...@sergeant.org; mserge...@cpan.org
Cc: General Discussion of SQLite Database ; DBI Dev 
; DBIx::Class user and developer list 
; rose-db-obj...@googlegroups.com; 
modu...@perl.org; c...@audreyt.org
Sent: Tuesday, January 13, 2009 7:55:30 PM
Subject: [sqlite] request to become co-maintainer of DBD::SQLite

Hello Matt Sergeant,

I would like to request your permission or blessing to become an official 
co-maintainer of the DBD::SQLite module, which is the defacto standard binding 
for SQLite to Perl.

(Also CC'd are some other concerned parties as FYI; my apologies if I've 
written 
too many people.  But this message is initially just for response by Matt, 
though others can write if they feel inclined, but try to keep the recipient 
list smaller than I just did here.  Focus any discussion to dbi-...@perl.org 
and 
modu...@perl.org as appropriate please, the former for what work needs doing 
and 
the latter for matters of module maintainership.)

P.S.  Or if anyone else has the tuits and wants to make a better offer to be a 
co-maintainer now, please do so.

I am interested in the long-term success of SQLite in combination with Perl, 
and 
in the short term I am particularly interested in using the latest SQLite 3.6.8 
(which adds the extremely important feature of nested transactions) with modern 
versions of Perl, and I am interested that it would be easy for the large 
number 
of other DBD::SQLite users to use this combination as well.

I am also concerned with there apparently being a number of significant bugs in 
DBD::SQLite that have been reported on the RT system, some with patches, and 
DBD::SQLite hasn't seen new releases in awhile to either address bugs or update 
the bundled SQLite.  A number of people I trust are seeing that this is a 
serious matter to address, some in the mean-time recommending use of older 
DBD::SQLite versions, which is itself a problem since automatic CPAN install 
tools would select the newest versions, and access to newer SQLite library 
features is missing.

Now I would of course be happiest if you had the time and motivation to bring 
your project up to date and address its bugs.  But otherwise I would like to 
offer you an out, and take on this responsibility myself, either alone or with 
partners such as yourself or other concerned parties that want to help.

If you agree, then please say the word to modu...@perl.org.

My CPAN account ID is DUNCAND.

To summarize, this is my intention in the short term:

1.  Release a new version every time there is a SQLite core library release.

2.  Make only the most minimal changes to DBD::SQLite itself, to ensure that 
reported bugs are fixed and that it compiles on modern systems and passes its 
own test suite on the same.  There won't be any feature additions or 
architectural changes initially, except where such may be highly demanded and 
simple.  The priorities here are stability and correctness plus easy access to 
all the SQLite library's native features, and minimal additional features.

3.  All initial releases will have version numbers ending in _NN that mark them 
as developer releases, so the community can test them before they become what 
the CPAN tools install by default.

4.  Perhaps follow what Audrey Tang started and use the official amalgamated 
pre-compiled source files rather than the original-original source code, so 
users with less capable build environments can handle it.  Though in the short 
term this will depend on which version I can get to work with fewer problems on 
my own machine (Mac OS X Leopard).

5.  I may use an older DBD::SQLite than the current one, such as 1.12, as an 
initial point of departure, if doing so makes for a more trouble-free solution.

6.  I will have this in a public GIT source repository and I will regularly 
seek 
feedback, help, patches, testing, etc from the user community that have a stake 
in this working.

7.  I am assuming until corrected that the primary discussion forum for people 
to discuss actual work to do and patches etc for DBD::SQLite is 
dbi-...@perl.org.

Some caveats:

1.  I have very little C-fu right now and won't be able to do much in the short 
term besides update the SQLite source files, and apply third-party patches to 
the C, and make more involved fixes to the portions written in Perl.

2.  It will probably be several weeks before my first release, partly because I 
am busy with other 

Re: [sqlite] Query Crashing SQLite

2008-11-26 Thread Clark Christensen

Older versions result in an SQL error:

SQLite version 3.3.13
Enter ".help" for instructions
sqlite> create table a (b);
sqlite> select max(b) as q from a where q = 1;
SQL error: misuse of aggregate:

Same on Linux, Solaris, and Win32



- Original Message 
From: Daniel Zingaro <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Wednesday, November 26, 2008 12:18:42 PM
Subject: [sqlite] Query Crashing SQLite

Hi all,

The following session crashes SQLite3.exe 3.6.6.2 on Windows XP:

sqlite> create table a (b);
sqlite> select max(b) as q from a where q = 1;

SQLite prevents me from directly using max(b) in the 'where' clause, but 
I think it gets fooled by the alias.

Thanks,
Dan

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

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


[sqlite] Concatenation question

2008-09-11 Thread Clark Christensen
Hello,

Using SQLite v3.3.13, this query:

select 
oid || '|' || email_addr || '|' || residual_value as RD
from 
gl_claims c
where
--RD is not null and
status = 1
and not exists (select 1 from gl_claim_tickets where ticket_type = 'coupon' 
and claim_id = c.oid);

I expect one row of data.  But what I get is 3 rows, the one I expect, and two 
others where RD is null.  I've tracked it down to null values in residual_value 
for the null result rows.

Since it's test data, and the rows in question are crap anyway, no big deal.  I 
can easily work around it by un-commenting the null test in the where clause.

Long setup for a simple question:  Is null the expected result when one column 
of a concatenation operation is null?

Thanks!

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


Re: [sqlite] Proposed removal of (mis-)feature

2008-08-07 Thread Clark Christensen
+1 in favor of removing non-standard quoting mechanism #3.



- Original Message 
From: D. Richard Hipp <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Thursday, August 7, 2008 10:26:07 AM
Subject: [sqlite] Proposed removal of (mis-)feature

String literals in SQL are suppose to be enclosed in single-quotes -  
Pascal-style.  Double-quotes are used around table and/or column names  
in cases where the name would otherwise be a keyword or when the name  
contains non-standard characters.

But SQLite tries to be flexible and accommodating.  To this end, it  
accepts some non-standard quoting mechanisms:

1.  Names can be enclosed in [...] for compatibility with Access  
and SQLServer.
2.  Names can be enclosed in grave accents for compatibility with  
MySQL.
3.  Double-quoted names fall back to being string literals if  
there is no matching table or column name.

In retrospect, (3) seems to be a bad idea.  It is accident-prone and  
leads to all kinds of confusion.  For example, if double-quotes are  
being used correctly (which is to say to quote table or column names)  
but a misspelling occurs in the name, the token reverts to being a  
string literal rather than throwing an error.  Or if a double-quoted  
string really is being used as a string literal, but later a new  
column is added to a table that has the same name as the string text,  
the string literal will suddenly take on the value of the column.  It  
seems like we have one or two problem reports per month on this  
mailing list that involve people using double-quoted names where they  
should be using single-quoted string literals instead.

So I'm giving some thought to removing feature (3) above and  
disallowing double-quoted string literals.  My concern is, though,  
that this might break many existing applications.

What opinion do you, gentle users, have of this matter?

D. Richard Hipp
[EMAIL PROTECTED]



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

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


Re: [sqlite] Setting where AUTOINCREMENT starts?

2008-06-30 Thread Clark Christensen
Looks like you can insert and delete a row to set whatever you want as the 
starting number:

sqlite> create table t1 (oid integer primary key autoincrement, a);
sqlite> insert into t1 values (100, 'foo');
sqlite> delete from t1;
sqlite> insert into t1 (a) values ('bar');
sqlite> select * from t1;
101|bar


 -Clark


- Original Message 
From: Shawn Wilsher <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Monday, June 30, 2008 2:55:34 PM
Subject: [sqlite] Setting where AUTOINCREMENT starts?

Hey all,

I was wondering if we could set the value that an AUTOINCREMENT starts
at for temporary tables.  Right now we are looking at having to manage
it ourselves, but if we could use sqlite to handle it, as long as it
starts at the right value, that would be ideal.

Cheers,

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

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


Re: [sqlite] Listing duplicate entries

2008-04-28 Thread Clark Christensen
select * from mytable
where last in ( 
select last from mytable group by last_name having count(*) > 1
)

Probably slow on a big table.

 -Clark


- Original Message 
From: flakpit <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, April 28, 2008 8:33:36 AM
Subject: [sqlite]  Listing duplicate entries


Is there a way of querying the database to list all duplicate entries from a
column in the same table?

Something like "SELECT * FROM mytable WHERE last NOT UNIQUE"

fred, johnson
roger, johnson


-- 
View this message in context: 
http://www.nabble.com/Listing-duplicate-entries-tp16941525p16941525.html
Sent from the SQLite mailing list archive at Nabble.com.

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

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


Re: [sqlite] What is the standard way to store dates and do operations with dates please?

2008-04-05 Thread Clark Christensen
Near as I can tell, there's no 'standard' way to store dates.

SQLite's date functions can deal with dates as floating-point julian numbers, 
-mm-dd hh:mm:ss strings (with or without the time portion), or Unix time 
integers.  As arguments to SQLite's date/time functions, Unix times usually 
have to be accompanied by a second argument, 'unixepoch'.  In either format, 
SQLite's date/time functions internally convert -mm-dd... and Unix times 
into julian dates before evaluating.

So, without knowing anything about your specific requirements, the most 
experienced guys here usually recommend storing dates as julian numbers.  It's 
clearly the most efficient in terms of storage, and effificncy.  The downside, 
of course, is julian and Unix numbers are not human-readable as dates.

But, if you need human-readable, -mm-dd hh:mm:ss, with or without the time 
portion works just as well if you're not tight on storage, and are willing to 
accept the negligible overhead of the internal conversions when you need to 
call a date function.  Plus, -mm-dd... sorts, and behaves in boolean 
comparisons appropriately.

 -Clark

- Original Message 
From: sqlfan <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Saturday, April 5, 2008 3:44:37 PM
Subject: Re: [sqlite] What is the standard way to store dates and do operations 
with dates please?


is this just your "hack" or the standard way to do this?  I don't need it to
be floating point, since I'm not interested in "when" during the day.  and,
to be clear, "julian" is the calendar we all use, right? it's completely 1:1
with the ansi format 2008-04-05 that I mentioned, right?

Thank you.


Dennis Cote-2 wrote:
> 
> sqlfan wrote:
>> I'm very new to sqlite but I notice there is no way to mark a column as
>> containing dates... What is the standard way to do operations with dates,
>> please, and to store dates?  Should I try the format 20080405 and do my
>> own
>> calculations using my language's standard library?  (I'm using Python) or
>> is
>> there a better way to store dates?  Thank you for all your help.  I'm
>> very
>> new to all this.
>>  
> See http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions for info 
> on date and time functions.
> 
> I would suggest storing dates as floating point julian day numbers.
> 
> HTH
> Dennis Cote
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/What-is-the-standard-way-to-store-dates-and-do-operations-with-dates-please--tp16514369p16518987.html
Sent from the SQLite mailing list archive at Nabble.com.

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

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


Re: [sqlite] Windows XP, where should I place the Sqlite .DLL's for best Sqlite operation, for usage with other COM components?

2008-04-03 Thread Clark Christensen
Pretty much any folder in the PATH will do.

You can put it \windows\system32\ but that can be a pain to get to and 
remember.  I usually create one or more folders to hold non-Windows misc 
binaries, and add those to the system PATH.  The most recent is C:\usr\bin\ for 
executables, and C:\usr\lib\ for DLLs.  Since I have both Windows, and Linux 
boxes here, those are easier for me to remember.

 -Clark

- Original Message 
From: Paul Newcum <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, April 3, 2008 1:09:09 PM
Subject: [sqlite] Windows XP, where should I place the Sqlite .DLL's for best 
Sqlite operation, for usage with other COM components?

For Windows XP, where should I place the Sqlite .DLL's for best Sqlite 
operation, and for usage with other COM components?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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


Re: [sqlite] When I try to .read I get a "can't open" message

2008-03-31 Thread Clark Christensen
It's the trailing semicolon.  The dot commands don't require them, while SQL 
statements do.

- Original Message 
From: Douglas McCarroll <[EMAIL PROTECTED]>
To: "sqlite-users@sqlite.org" 
Sent: Monday, March 31, 2008 2:11:35 PM
Subject: [sqlite] When I try to .read I get a "can't open" message

I'm sure I'm doing something simple and obvious wrong here. I'm a complete
sqlite n00b.

Help?

C:\_source>sqlite3 test
SQLite version 3.5.7
Enter ".help" for instructions
sqlite> .read test.sql;
can't open "test.sql;"
sqlite> .read C:/_source/test.sql;
can't open "C:/_source/test.sql;"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



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


Re: [sqlite] [C] Getting results doesn't work properly

2008-03-25 Thread Clark Christensen
Well, I'm no C programmer, so I might be full of crap, but it looks like you're 
closing the db connection inside your while block (after you get the first 
row's data).  Not sure about the exact usage for reset() and finalize(), but 
they don't seem proper inside a loop like yours.  Last, it looks like you skip 
every odd row by calling step(oStmt) in the while condition, and then again 
inside the block.

 -Clark

- Original Message 
From: Severin Müller <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, March 25, 2008 2:14:33 PM
Subject: [sqlite] [C] Getting results doesn't work properly

Hi Folks

I have the following C funtion:

void get_acc(char *src,int ac,char **av)
{
   char *buf = "SELECT accmask FROM testtable;");
sqlite3 *db;
struct sqlite3_stmt *oStmt;
int rc;
if(sqlite3_prepare_v2(db,buf,strlen(buf),,NULL)==SQLITE_OK)
{
while(sqlite3_step(oStmt)==SQLITE_ROW)
{
sqlite3_step(oStmt);
txt = (char*)sqlite3_column_text(oStmt,0);
sqlite3_reset(oStmt);
sqlite3_finalize(oStmt);
sqlite3_close(db);
printf("Result: %s\n",txt);
}
}
 }

If i call the function, only one row is returned, but there are definately more 
(i check with SELECT COUNT(*)). Did i oversee something? Or what's wrong here?

Thanks again for your help.

Kind regards

Severin
-- 



Pt! Schon vom neuen GMX MultiMessenger gehört?
Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger
___
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] drh interviewed on FLOSS Weekly podcast

2008-03-07 Thread Clark Christensen
I see drh and SQLite are the subjects this week in the FLOSS Weekly podcast.  
Check it out at http://www.twit.tv/floss26.

 -Clark


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


Re: [sqlite] Question on Queries

2008-03-03 Thread Clark Christensen
I'm sure the real experts will chime-in, but it looks like you might be 
executing the subquery once for every row in main.

Maybe if you use a join, it would go faster

select 
L.data 
from 
list L, main m
where 
m.name='something' 
and L.mid = m.id;

Or, maybe you could just use in() rather than =.

-- Compound Query
SELECT data FROM LIST WHERE mid in (SELECT id FROM MAIN WHERE name =
"something") ORDER BY ord;

 -Clark


- Original Message 
From: Mike McGonagle <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Monday, March 3, 2008 1:32:45 PM
Subject: [sqlite] Question on Queries

Hello all,
I was working with some queries last night, and ran accross something that I
don't quite understand. Basically, this is what I have...

***

CREATE TABLE MAIN (
id integer primary key autoincrement not null,
name varchar(30),
[other fields left out, as they are not used]
);

CREATE TABLE LIST (
mid integer,
ord integer,
data float
);

-- Compound Query
SELECT data FROM LIST WHERE mid = (SELECT id FROM MAIN WHERE name =
"something") ORDER BY ord;

-- Individual Queries
SELECT id FROM MAIN WHERE name = "something";
SELECT data FROM LIST WHERE mid = id_as_returned_above;

***

So, what is happening is when I run the first query, it takes about 45
seconds for the data to be returned. It is correct and everything, just
takes a long time.

But, when I run the queries in two passes, it comes back pretty quickly,
nowhere near the 45 seconds it takes for the first compound query.

Is this something that is unique to SQLITE? Or would any database engine
choke on these sorts of queries? Would this go faster if I create an index
on 'name'?

I believe that the version of SQLITE that I am running is 3.1.3 (I am not on
the machine that I was running this on). Is there something out there (on
the net) that I should read that explains these sorts of things?

Thanks,

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



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


Re: [sqlite] DBD::SQLite 1.14 prepare_cached bug?

2008-02-05 Thread Clark Christensen
> Conclusion: avoid using $dbh->disconnect() for DBD::SQLite, instead
use "undef $dbh".

Good info.  I don't use usually use prepare_cached(), but I'm adding your 
observation to my notes for when I'm trying to resolve the same.

> What kind of SQL injection is possible here?

Good point.  Not sure.  Possibly none because only the first statement in the 
SQL is compiled.

Thanks!

 -Clark

- Original Message 
From: Alexander Batyrshin <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Monday, February 4, 2008 8:30:10 PM
Subject: Re: [sqlite] DBD::SQLite 1.14 prepare_cached bug?

I think i found solution.
The problem is that DBD::SQlite->disconnect() method execute
sqlite3_close() function.
This function return SQLITE_BUSY in case if there are any active statement.
>From API:
"Applications should finalize all prepared statements and close all
BLOBs associated with the sqlite3 object prior to attempting to close
the sqlite3 object."
Currently DBD::SQLite can finalize statements only via DESTROY method.

In simplest case you can always use "undef $sth" or wait untill it
goes out of scope
which will finalize statement.

But if you prepared statement via cache (prepare_cached) it will not
work for you,
because statement is till inside DBI cache. In this case we can call
DESTROY on our cached statement only via DESTROY for database handler.
And we can achieve it by "undef $dbh".
"undef $dbh" - will close all cached statements and close database
without any errors.

Conclusion: avoid using $dbh->disconnect() for DBD::SQLite, instead
use "undef $dbh".

On Feb 5, 2008 3:37 AM, Alexander Batyrshin <[EMAIL PROTECTED]> wrote:
>  Hello,
>
> > What do you expect to see?  From the code, I'm guessing something like:
>
> This is "test-case" program for testing DBD-SQLite behavior. Dumper is
> only for be sure, that data was read correctly from database.
>
> > If you're just trying to silence the "closing dbh with active handles..." 
> > warning, "undef $sth;" usually works for me.  I see you have it commented 
> > in your code?  DBD-SQLite has spewed this warning for as long as I can 
> > remember.  And $dbh->finish; doesn't squash it.
>
> Yes, this is what I want. "undef $sth" doesn't work for statement that
> was prepare_cached. Because statement is still allocated inside $dbh
> buffers for cached statement.
>
>
> > Also, I see you could save the sprintf and $dbh->quote by changing to:
> >
> > my $sql = "select a_session from sessions where id = ?";
> > my $sth = $dbh->prepare_cached($sql);
> > $sth->execute($sid);
>
> Yes, i know, but this is only "test-case" program without any
> optimization and code-beauty refactoring.
>
> > In your example, the value of $sid, after doing the $dbh->quote, is parsed 
> > by the SQL parser.  Doing that has always been unreliable for me, and it's 
> > generally open to SQL injection.  In the example above, $sid isn't 
> > parsed/compiled by SQLite, it's just passed as-is as a bound parameter 
> > after $sth is prepared.
>
> What kind of SQL injection is possible here?
>
> > Are you building a web session manager using SQLite as the data store?  How 
> > is Storable working for you?  I usually just use Data::Dumper, and eval the 
> > stored hash.  But doing the eval has always worried me :-))
>
> It's work without any problems for me handling over 150k hits/day.
>
>
>
> --
> Alexander Batyrshin aka bash
> bash = Biomechanica Artificial Sabotage Humanoid
>



-- 
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid
___
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] DBD::SQLite 1.14 prepare_cached bug?

2008-02-04 Thread Clark Christensen
Sorry, this was mis-addressed.  Should have gone to the list...

- Forwarded Message 
From: Clark Christensen <[EMAIL PROTECTED]>
To: Alexander Batyrshin <[EMAIL PROTECTED]>
Sent: Monday, February 4, 2008 9:46:49 AM
Subject: Re: [sqlite] DBD::SQLite 1.14 prepare_cached bug?

bash,

What do you expect to see?  From the code, I'm guessing something like:

$VAR1=[...]

If you're just trying to silence the "closing dbh with active handles..." 
warning, "undef $sth;" usually works for me.  I see you have it commented in 
your code?  DBD-SQLite has spewed this warning for as long as I can remember.  
And $dbh->finish; doesn't squash it.

Also, I see you could save the sprintf and $dbh->quote by changing to:

my $sql = "select a_session from sessions where id = ?";
my $sth = $dbh->prepare_cached($sql);
$sth->execute($sid);

In your example, the value of $sid, after doing the $dbh->quote, is parsed by 
the SQL parser.  Doing that has always been unreliable for me, and it's 
generally open to SQL injection.  In the example above, $sid isn't 
parsed/compiled by SQLite, it's just passed as-is as a bound parameter after 
$sth is prepared.

Are you building a web session manager using SQLite as the data store?  How is 
Storable working for you?  I usually just use Data::Dumper, and eval the stored 
hash.  But doing the eval has always worried me :-))

Thanks!

 -Clark


- Original Message 
From: Alexander Batyrshin <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Saturday, February 2, 2008 3:27:35 PM
Subject: [sqlite] DBD::SQLite 1.14 prepare_cached bug?

 Hello All,
I don't know is it right place to discuss this or not. Sorry If I am
doing something wrong.
Installed sqlite-3.5.4 and DBD::SQLite-1.14
I get problems with this code:

%<
#!/usr/bin/perl -w

use strict;
use DBI;
use Data::Dumper;
use Storable;
use warnings;

sub get_session {
my ($dbh) = shift;
#$dbh->{TraceLevel} = 2;
my $sid = $ARGV[0];
my $SQL = sprintf("select a_session from sessions where id = %s",
$dbh->quote($sid));
my $sth = $dbh->prepare_cached($SQL, undef, 3);
$sth->execute;
my ($val) = $sth->fetchrow_array;
#my ($val2) = $sth->fetchrow_array;
$sth->finish;
#[3] undef $sth;
my $session = Storable::thaw($val);
}


my $dbh = DBI->connect('dbi:SQLite:dbname=db/sessions.db');
print Dumper(get_session($dbh));
$dbh->disconnect;
%<

If we run program as it looks, result will be:

DBI::db=HASH(0x87a79c)->disconnect invalidates 1 active statement
handle (either destroy statement handles or call finish on them before
disconnecting) at ./decode_sessions.pl line 26.
closing dbh with active statement handles at ./decode_sessions.pl line 26.

Note: that my database does not contain duplicated records.

If I uncomment (1), (2) or (1)+(2) result:

closing dbh with active statement handles at ./decode_sessions.pl line 26.

Inside DBD-SQLite this errors goes from this:
%<
int
sqlite_db_disconnect (SV *dbh, imp_dbh_t *imp_dbh)
{
dTHR;
DBIc_ACTIVE_off(imp_dbh);

if (DBIc_is(imp_dbh, DBIcf_AutoCommit) == FALSE) {
sqlite_db_rollback(dbh, imp_dbh);
}

if (sqlite3_close(imp_dbh->db) == SQLITE_BUSY) {
/* active statements! */
warn("closing dbh with active statement handles");
}
imp_dbh->db = NULL;

av_undef(imp_dbh->functions);
imp_dbh->functions = (AV *)NULL;

av_undef(imp_dbh->aggregates);
imp_dbh->aggregates = (AV *)NULL;

return TRUE;
}
%<


-- 
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users






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


Re: [sqlite] Mailing List Changes

2008-02-04 Thread Clark Christensen
So, I sent a reply this morning to a list message, and it seems to have gone to 
the OP's email address rather than to the list (sorry bash).

I don't remember having that issue with the old software (ezmlm).  To fix, is 
it a client configuration, or is there a reply-to header that should be set in 
Mailman?  I would really like to NOT have to remember to change the recipient 
address when I reply.

Thanks!

 -Clark

- Original Message 
From: Mike Owens <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Saturday, February 2, 2008 10:55:04 AM
Subject: [sqlite] Mailing List Changes

The SQLite mailing list has been moved over to Postfix and GNU Mailman. Please
do not use the ezmlm mail accounts to modify your subscription status from this
point on.

You can now configure your list status and options via the Mailman
interface at:

  http://sqlite.org:8080/cgi-bin/mailman/options/sqlite-users

You will need your password to do so. To get it, fill in your email address and
hit the "Remind" button under the the "Password Reminder" section. You will
receive an email with your auto-generated password. You can change this password
after logging into your admin page.

While it shouldn't be a problem for you, we are using the following blacklists:

   list.dsbl.org
   zen.spamhaus.org
   bl.spamcop.net
   dnsbl.njabl.org

If you have any problems with passwords, settings, or sending/receiving mail, or
anything else related to the mailing list, please email [EMAIL PROTECTED] I
apologize in advance for any problems or inconvenience, and will work
to fix them
as quickly as possible.

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



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


Re: [sqlite] Cache for SQLite

2008-01-24 Thread Clark Christensen
I don't think you're going to get the kind of caching you want using Perl and a 
web server (Apache, right?).  There's just no persistence across processes, no 
shared memory, no database connections.

Now, Apache's mod_perl and some associated modules could get you all that and 
more.  For me, anyway, it requires a big adjustment in the way you build your 
apps if you want to take advantage of the shared $dbh, shared variables, and 
caching.  For me, the investment isn't quite worth the benefit.

 -Clark

- Original Message 
From: Alexander Batyrshin <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, January 24, 2008 7:19:47 AM
Subject: Re: [sqlite] Cache for SQLite


On 
Jan 
24, 
2008 
4:03 
PM, 
Doug 
<[EMAIL PROTECTED]> 
wrote:
> 
I 
don't 
know 
of 
a 
daemon, 
but 
based 
on 
someone 
else's 
post 
where 
they
> 
described 
keeping 
a 
pool 
of 
sqlite3* 
handles 
to 
the 
database, 
and 
always
> 
reusing 
the 
most 
recently 
used 
handle 
first 
(so 
that 
the 
SQLite 
page 
cache
> 
is 
most 
likely 
still 
valid) 
I 
saw 
a 
very 
big 
jump 
in 
performance.
>
> 
Perhaps 
that 
would 
help 
in 
your 
case 
too?

Sounds 
interesting, 
maybe 
it 
help 
me 
a 
little.
I 
am 
using 
Perl 
DBD::SQLite, 
so 
i 
need 
some 
investigation 
how 
this 
library 
work.


-- 
Alexander 
Batyrshin 
aka 
bash
bash 
= 
Biomechanica 
Artificial 
Sabotage 
Humanoid

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





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



Re: [sqlite] How to truncate the hour fraction

2008-01-23 Thread Clark Christensen
sqlite> select datetime('1201561222', 'unixepoch');
2008-01-28 23:00:22

OK, so now it's clear your values are Unix times.

sqlite> select strftime('%s', date('1201561222', 'unixepoch'));
1201478400

Effectively strips the time portion of your time value

sqlite> select datetime('1201478400', 'unixepoch');
2008-01-28 00:00:00

Proves the result is what you asked for.

Good luck!

 -Clark

- Original Message 
From: Joanne Pham <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, January 23, 2008 2:02:09 PM
Subject: Re: [sqlite] How to truncate the hour fraction


Any 
help 
for 
this 
question 
please!
I 
would 
like 
to 
convert 
from 
t1 
to 
t2.
and 
my 
table 
is 
store 
t2.

t1:  
201561222 
-> 
2008-01-28 
15:00:22
and 
I 
want
t2 
?  
  
  
  
  
  
  
  
-> 
2008-01-28 
00:00:00

Thanks
JP


- 
Original 
Message 

From: 
Joanne 
Pham 
<[EMAIL PROTECTED]>
To: 
sqlite-users@sqlite.org
Sent: 
Wednesday, 
January 
23, 
2008 
10:44:07 
AM
Subject: 
Re: 
[sqlite] 
How 
to 
truncate 
the 
hour 
fraction

Thank 
Ken,
But 
this 
is 
not 
what 
I 
want. 
I 
would 
like 
to 
truncate 
the 
hour.
So 
t1:  
201561222 
-> 
2008-01-28 
15:00:22
and 
I 
want
t2 
?  
  
  
  
  
  
  
  
-> 
2008-01-28 
00:00:00
So 
I 
want 
to 
find 
out 
t2? 
which 
has 
no 
hour 
number.
Thanks
JP

- 
Original 
Message 

From: 
Ken 
<[EMAIL PROTECTED]>
To: 
sqlite-users@sqlite.org
Sent: 
Wednesday, 
January 
23, 
2008 
10:35:03 
AM
Subject: 
Re: 
[sqlite] 
How 
to 
truncate 
the 
hour 
fraction

assuming 
your 
time 
is 
in 
seconds.
  
try 

t1:  
201561222 
-> 
2008-01-28 
15:00:22
and 
you 
want 
t2:  
1201561200  
  
  
-> 
2008-01-28 
15:00:00

  
t1 
= 
t2 
- 
(t2 
mod 
3600)

  

Joanne 
Pham 
<[EMAIL PROTECTED]> 
wrote: 
Hi 
All,
I 
have 
a 
hour 
table 
which 
has 
the 
startTime 
are 
stored 
at 
GMT 
time. 
The 
startTime 
of 
this 
hour 
table 
has 
the 
values 
below:
Hour 
table
---
StartTime  
  
  
  
  
  
equivalent 
with 
the 
time 
format
1201561200  
  
  
-> 
2008-01-28 
15:00:00
120159  
  
  
-> 
2008-01-28 
23:00:00
120159  
  
  
-> 
2008-01-28 
23:00:00
120159  
  
  
-> 
2008-01-28 
23:00:00

I 
want 
to 
aggregate 
the 
data 
from 
hour 
table 
and 
store 
them 
in 
weekly 
table 
and 
startTime 
should 
not 
have
any 
hour 
fraction. 
Is 
there 
any 
function 
to 
truncate 
all 
the 
hour 
fraction.
Thanks,
JP


  
  
  

Never 
miss 
a 
thing.  
Make 
Yahoo 
your 
home 
page. 
http://www.yahoo.com/r/hs


  
  
  

Be 
a 
better 
friend, 
newshound, 
and 
know-it-all 
with 
Yahoo! 
Mobile.  
Try 
it 
now.  
 
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ


  
  
  

Never 
miss 
a 
thing.  
Make 
Yahoo 
your 
home 
page. 
http://www.yahoo.com/r/hs



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



Re: [sqlite] free excel-like COLORFUL gui for sqlite

2008-01-08 Thread Clark Christensen
I'm not big on either Excel or ODBC, so I can't help with the details, but...

There's an ODBC driver for SQLite you could install on your customer's system, 
and I'm pretty sure Excel can render data from an ODBC data source.  So, if 
what you really want is to view query result data in Excel, it seems you could 
start here:

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

 -Clark


- Original Message 
From: sqlfan <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, January 8, 2008 2:50:00 AM
Subject: Re: [sqlite] free excel-like COLORFUL gui for sqlite



Let 
me 
be 
more 
clear.  
There's 
very 
little 
data, 
and 
I 
want 
to 
help 
my 
client
be 
able 
to 
expand 
his 
business, 
so 
the 
first 
step 
is 
automating 
what 
he's
been 
doing 
by 
hand.  
I 
can 
get 
the 
excel 
data 
into 
sqlite 
no 
problem 
-- 
and
wish 
to 
do 
so 
in 
order 
to 
START 
doing 
automated 
stuff 
with 
the 
data 
-- 
but
would 
like 
to 
PRESENT 
(and 
only 
present) 
the 
data 
in 
the 
familiar
spreadsheet 
manner.

This 
is 
a 
general 
problem: 
many 
people 
use 
excel 
as 
a 
database 
only 
because
of 
how 
nice 
it 
looks 
once 
they 
add 
coloring, 
and 
because 
it's 
easier 
to,
say, 
have 
the 
three 
address 
fields 
as 
three 
columns, 
even 
though 
logically
it 
should 
be 
a 
separate 
table 
Addresses.  
So, 
I'd 
like 
to 
have 
the 
same
familiar 
input 
view 
without 
having 
the 
client 
worry 
about 
the 
details 
(which
column 
is 
really 
what 
table, 
etc) 
and 
also 
to 
be 
able 
to 
color 
it 
as 
he 
has
done 
to 
date.  
So, 
is 
there 
is 
a 
free 
gui 
frontend 
to 
sqlite 
that 
will 
do
it, 
or 
do 
I 
have 
to 
code 
one 
myself?

Thanks!



bartsmissaert 
wrote:
> 
> 
If 
it 
is 
so 
good 
then 
why 
would 
you 
want 
to 
use
> 
SQLite? 
Holiday 
data 
can't 
be 
that 
much, 
so 
I 
would
> 
think 
Excel 
can 
cope 
with 
that 
fine.
> 
If 
you 
really 
want 
to 
move 
the 
data 
from 
Excel 
to
> 
SQLite 
then 
you 
will 
need 
a 
VB 
wrapper.
> 
> 
RBS
> 
>>
>> 
I 
have 
a 
client 
who's 
using 
a 
colorful 
excel 
sheet 
as 
a 
database.  
It's
>> 
colorful, 
well-structured, 
and 
a 
joy 
to 
use.  
He 
has 
no 
code 
working 
on
>> 
the
>> 
data 
though 
-- 
it's 
just 
used 
for 
holding 
data, 
like 
a 
ledger 
book!
>>
>> 
My 
question 
is 
how 
I 
can 
put 
his 
information 
into 
a 
sqlite 
database 
but
>> 
give
>> 
him 
a 
very 
similar 
interface 
-- 
the 
same, 
well-structured, 
colorful,
>> 
spreadsheet 
view?  
I 
don't 
want 
him 
to 
even 
have 
to 
worry 
about 
which
>> 
column
>> 
is 
actually 
in 
which 
table-- 
just 
have 
it 
look 
like 
an 
excel 
sheet.
>>
>> 
This 
is 
very 
basic 
and 
easy, 
and 
I'd 
hate 
to 
have 
to 
reinvent 
the 
wheel
>> 
coding 
it 
-- 
is 
there 
a 
free 
sqlite 
gui 
that 
can 
present 
such 
a 
colorful
>> 
spreadsheet 
view?
>>
>> 
Thank 
you!
>> 
--
>> 
View 
this 
message 
in 
context:
>> 
http://www.nabble.com/free-excel-like-COLORFUL-gui-for-sqlite-tp14686423p14686423.html
>> 
Sent 
from 
the 
SQLite 
mailing 
list 
archive 
at 
Nabble.com.
>>
>>
>> 
-
>> 
To 
unsubscribe, 
send 
email 
to 
[EMAIL PROTECTED]
>> 
-
>>
>>
>>
> 
> 
> 
> 
> 
-
> 
To 
unsubscribe, 
send 
email 
to 
[EMAIL PROTECTED]
> 
-
> 
> 
> 

-- 
View 
this 
message 
in 
context: 
http://www.nabble.com/free-excel-like-COLORFUL-gui-for-sqlite-tp14686423p14686909.html
Sent 
from 
the 
SQLite 
mailing 
list 
archive 
at 
Nabble.com.


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





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



Re: [sqlite] How to truncate the hour

2007-12-19 Thread Clark Christensen
I think your description of 1198990800 is a little off

sqlite> select datetime(1198990800, 'unixepoch');
2007-12-30 05:00:00

To "truncate the hour", as you say:

sqlite> select strftime('%s', date(1198990800, 'unixepoch'));
1198972800

Which translates to 2007-12-30 00:00:00

 -Clark

- Original Message 
From: Joanne Pham <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, December 19, 2007 10:09:42 AM
Subject: Re: [sqlite] How to truncate the hour


Hi P 
Sorry for the confusion!
Current my hourAppAcclTable is store the following
 1, 1198990800  
 2, 1198998000  
which is 2007-12-29 21:00:00 and 2007-12-29 23:00:00  if I used the
 datetime function to show the time format.
What I want is to store this time stamp in different table but truncate
 all the hour fraction, dayAppAcclTable
   1, 1198915200 
   2, 1198915200 
which is 2007-12-29 00:00:00 which GUI run the datetime function

Thanks a lot,
Joanne
- Original Message 
From: P Kishor <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, December 19, 2007 9:49:43 AM
Subject: Re: [sqlite] How to truncate the hour

On 12/19/07, Joanne Pham <[EMAIL PROTECTED]> wrote:
> Hi,
> strftime doesn't work for me. I appreciate if you can help me on
 this.
>
> My hourlyAppAcclTable
>  row1 -  1, 1198990800  -- (which is  12-29-2007 21:00:00)
>  row 2 -  2, 1198998000  - (which is  12-29-2007 23:00:00)
> I want to truncate all the hour fraction and store these time in
 dayAppAcclTable
>  1, 1198915200 ( 12-29-2007 00:00:00)
>  2, 1198915200 (12-29-2007 00:00:00)
> I still want to store the GMT time in  my dayAppAcclTable as INTEGER
 too.
> Thanks,
> Joanne
>

I am not really sure what you want to do, nor can I figure out what
exactly you have tried to do. Did you try

sqlite> select date(1198990800, 'unixepoch', '-8 hours');
2007-12-29

is that what you want when you mean "truncate the hour"?

Please read the datetime wiki at
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions carefully.
Most of your questions will be answered there.

>
>
>
> - Original Message 
> From: P Kishor <[EMAIL PROTECTED]>
> To: sqlite-users@sqlite.org
> Sent: Wednesday, December 19, 2007 9:28:22 AM
> Subject: Re: [sqlite] How to truncate the hour
>
> On 12/19/07, Joanne Pham <[EMAIL PROTECTED]> wrote:
> > Hi All,
> > I have the table as defined below:
> >  hourlyAppAcclTable ( appid INTEGER,
> >  startTime INTEGER -- is  number of
 seconds in GMT time in the integer
> >.
> >  )
> >
> > the values in this table is :
> >row1 -  1, 1198990800
> >row 2 -  2, 1198998000
> > If I ran the following sql statement:
> >  select datetime(startTime, 'unixepoch','-8 hours');
> > I got the output as below:
> >12-29-2007 21:00:00
> >12-29-2007 23:00:00
> > I want to store this information in different table,
 dayAppAcclTable but the startTime is the day. So I want to truncate all the 
hour.
> >1, 1198915200 ( 12-29-2007 00:00:00)
> >2, 1198915200 (12-29-2007 00:00:00)
> > So I want to have generic the formular/function to truncate all
 hours and only keep the day.
>
>
> did you check out the formatting functions on the datetime wiki?
 Check
> out strftime
>

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



  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.
  http://tools.search.yahoo.com/newsearch/category.php?category=shopping



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



Re: [sqlite] How to truncate the hour

2007-12-19 Thread Clark Christensen
select date(startTime, 'unixepoch','-8 hours');

SQLite's date/time functions are documented at 
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

 -Clark

- Original Message 
From: Joanne Pham <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, December 19, 2007 9:22:39 AM
Subject: [sqlite] How to truncate the hour


Hi All,
I have the table as defined below:
 hourlyAppAcclTable ( appid INTEGER,
 startTime INTEGER -- is  number of seconds
 in GMT time in the integer
.
 )

the values in this table is :
   row1 -   1, 1198990800 
row 2 -  2, 1198998000
If I ran the following sql statement:
  select datetime(startTime, 'unixepoch','-8 hours');
I got the output as below:
   12-29-2007 21:00:00
   12-29-2007 23:00:00
I want to store this information in different table, dayAppAcclTable
 but the startTime is the day. So I want to truncate all the hour.
   1, 1198915200 ( 12-29-2007 00:00:00)
   2, 1198915200 (12-29-2007 00:00:00)
So I want to have generic the formular/function to truncate all hours
 and only keep the day.
Thanks in advance for your help,
Joanne



  

Never miss a thing.  Make Yahoo your home page. 
http://www.yahoo.com/r/hs



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



Re: [sqlite]:Using sqlite3_progress_handler for GUI application

2007-12-18 Thread Clark Christensen
FWIW, I notice the window title on the CVSTrac-generated pages at sqlite.org is 
"Sqlite CVSTrace".  I'm guessing it should be "SQLite CVSTrac".

- Original Message 
From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, December 18, 2007 4:18:52 AM
Subject: Re: [sqlite]:Using sqlite3_progress_handler for GUI application


"Sreedhar.a" <[EMAIL PROTECTED]> wrote:
> Hi,
> 
>  Say,I am having 4 entries in the table ALBUM.
> 
> "CREATE TABLE ALBUM (AlbumtId INTEGER PRIMARY KEY NOT NULL,Album TEXT
 NOT
> NULL COLLATE NOCASE DEFAULT 'Unknown',UNIQUE(Album));"
> 
> Now I want to list the first 100 Album from ALBUM table.The result
 has to be
> in sorting order.So my querry is like.
> 
> "SELECT AlbumId ,Album FROM ALBUM ORDER BY Album LIMIT 100 OFFSET 0;"
> 
> So for the next time I will change the offset to 200 then 300 ...
> But the search Speed is fast initially and slows down later since it
 has
> 4 records.

http://www.sqlite.org/cvstrac/wiki?ScrollingCursor

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


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





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



Re: [sqlite] Detailed notes on compiling full-text search

2007-11-21 Thread Clark Christensen
DBD-SQLite, by default, builds using your installed version of SQLite by 
default (assuming it can find the SQLite headers it needs).

It might be as simple as just configuring/building like any other Perl module.

Maybe I'll fiddle with that over the holiday weekend :-))

 -Clark

- Original Message 
From: P Kishor <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, November 21, 2007 8:53:24 AM
Subject: Re: [sqlite] Detailed notes on compiling full-text search


On 11/21/07, Clark Christensen <[EMAIL PROTECTED]> wrote:
> Were you able to build DBD-SQLite using the resulting library?

No, I haven't ventured that far. I discovered the Matt Sergeant has
already built fts2 into DBD::SQLite 1.14 (with SQLite 3.4.2), so my
problem was solved temporarily.

Yes, I would like to learn how to build DBD::SQLite with a different
version of the SQLite source code, so if someone on this list can give
me pointers, I would be happy to try it out, and if successful, post
the instructions for that as well on the wiki.

I have seen Matt on this list on occasion, so maybe if he reads this
he would be kind enough to provide some guidance.

I will also ask on Perlmonks.


>
> Thanks!
>
>  -Clark
>
> - Original Message 
> From: P Kishor <[EMAIL PROTECTED]>
> To: sqlite-users@sqlite.org
> Sent: Wednesday, November 21, 2007 7:18:20 AM
> Subject: [sqlite] Detailed notes on compiling full-text search
>
>
> http://www.sqlite.org/cvstrac/wiki?p=CompilingFts
>
> Not being very conversant with the ins and outs of compiling
 software,
> I had to do a lot of undue diligence to get this working, so I hope
> this is of help to someone.
>
> By the way, fts is the greatest thing since... since the world's most
> widely used database. My compliments to the chefs.
>
> Please consider making a Makefile for it so compiling or not
 compiling
> full-text search is just a matter of throwing a switch in configure.
>
> Many thanks,
>
> --
> Puneet Kishor
>
>
 -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
 -
>
>
>
>
>
>
 -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
 -
>
>


-- 
Puneet Kishor
http://punkish.eidesis.org/
Nelson Institute for Environmental Studies
http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo)
http://www.osgeo.org/
Summer 2007 S Policy Fellow, The National Academies
http://www.nas.edu/

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





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



Re: [sqlite] Detailed notes on compiling full-text search

2007-11-21 Thread Clark Christensen
Were you able to build DBD-SQLite using the resulting library?

Thanks!

 -Clark

- Original Message 
From: P Kishor <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, November 21, 2007 7:18:20 AM
Subject: [sqlite] Detailed notes on compiling full-text search


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

Not being very conversant with the ins and outs of compiling software,
I had to do a lot of undue diligence to get this working, so I hope
this is of help to someone.

By the way, fts is the greatest thing since... since the world's most
widely used database. My compliments to the chefs.

Please consider making a Makefile for it so compiling or not compiling
full-text search is just a matter of throwing a switch in configure.

Many thanks,

--
Puneet Kishor

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





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



Re: [sqlite] Suggests for improving the SQLite website

2007-11-09 Thread Clark Christensen
Richard,

I just recently discovered that IE supports "conditionl comments", which allow 
you to, among other things, load specific CSS in IE.  For detail, see 
http://msdn2.microsoft.com/en-us/library/ms537512.aspx

I was able to use this feature to my advantage on a project to load the main 
CSS file for all browsers, and maintain a small "delta" CSS file for IE that 
replaces a couple of specific classes that IE handled differently than FF.

If you'll add this as the first block in your page style:


  * {
  font-family: Verdana, sans-serif; /* this is the correct syntax for a CSS 
font list, accordint to TopStyle */
  font-size: 12px;
  }

it'll set the global default font and size.

And if you change to:

  .toolbar a {
color: white;
text-decoration: none;
  }

IE will display the correct color for the toolbar links.  Neither change 
appears to affect the page display in FF.

I may find time over the weekend to see if I can fix the radiused corners, and 
the broken cascading hover menus on the toolbar under IE6.

 -Clark


- Original Message 
From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Friday, November 9, 2007 10:27:21 AM
Subject: Re: [sqlite] Suggests for improving the SQLite website

Joe Wilson <[EMAIL PROTECTED]> wrote:
> 
> You chose to embed the CSS settings on each page to avoid the round
> trip to the web server. You can always put the css info in a separate
 
> file, and define it only once for the entire site. It should reduce
> the number of bytes sent over the wire.
> 

I don't think the extra bandwidth is an issue.  Dan points out that
if you put the CSS in a separate file, then sometimes a browser
will render the page without CSS, then when the CSS arrives a
fraction of a second later, everything shifts.  I'd rather avoid
that.

I am now also told that web pages need to be designed for
three separate browsers:  IE6, IE7, and all others.  This is
madness.

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


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





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



Re: [sqlite] SQLite and html character entities

2007-09-21 Thread Clark Christensen
Wow!  Excellent summary, Trevor.

- Original Message 
From: Trevor Talbot <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, September 20, 2007 11:35:42 PM
Subject: Re: [sqlite] SQLite and html character entities

On 9/20/07, P Kishor <[EMAIL PROTECTED]> wrote:
> On 9/20/07, Trevor Talbot <[EMAIL PROTECTED]> wrote:
> > On 9/20/07, P Kishor <[EMAIL PROTECTED]> wrote:

> > > Lucknow:~/Data/ecoservices punkish$ less foo.csv
> > > "the first record"
> > > "\351 \347 \361 \356"
> > > "more from 3rd row"
> > > "row four"
> > > "these \223volunteered\224 activities"
> > > "<\341 \370 \343 \374 \356 & others>"
> > > foo.csv (END)
> > > -
> >
> > Note that this is *not* UTF-8.  If you're still using this as test
> > data, you need to get rid of it and use UTF-8 encoded data instead.

> this is where I lost you... when you say "this" is not UTF8, what is
> "this"?

The data in the file shown by less, and since sqlite3 exported that
data exactly as it was stored, the data in the db as well.

> All I want is that I want (1) the user to be able to type ç in
> the web form, and (2) I want to be able to save ç in the db. (3)
 Then
> when I look at that data, either on the command line, but definitely
> back on the web, I want it to appear as ç. (4) If I export it, I
> should still be able to see it as ç and not something else.
>
> Seems like I was able to do 1, 2, and 3 with my test case, but not 4
> (I got \347 instead ç).
>
> Also, in my production case, 1,2, and 3 are not very reliable. Are
 you
> saying my data above are not UTF8? If so, I would like to know how
 you
> can tell that, so I can recognize it in the future myself. Also, I
> would like to know how I can do what you are suggesting I should do,
> that is, how can I ensure that I "use UTF8 encoded data"?

Okay, first a quick primer on character sets and encodings.  A byte
can hold one of 256 different values (0-255), and most processing
tends to happen on bytes, so it makes sense that individual characters
should be stored as individual bytes.

First we have US ASCII, the character encoding standard that defines
128 characters, including the basic english alphabet, numbers, and
some punctuation (www.asciitable.com).  However, this obviously
doesn't cover all the symbols in common use, or characters from other
languages, so more definitions are needed.  Given that a byte supports
twice as many values (ASCII takes up only half), that leaves 128
values for other purposes.  Many other character sets keep the bottom
half as ASCII, and assign different characters to the top 128 values.
The ISO-8859 family of standards works this way.

ISO-8859-1 is also known as Latin-1, and is most common for languages
that use characters similar to English, Spanish, etc.  It adds a few
more symbols (copyright, paragraph, etc) and some common characters
with diacritical marks (like é ç ñ î).  The data you posted above
 was
entered into your database using this encoding (or Windows-1252, which
is identical except for adding some characters in places 8859-1 does
not use).

ISO-8859-2 is also known as Latin-2, and covers another set of
European languages (such as Romanian).  It contains a different set of
symbols and characters with diacritical marks needed for these
languages, characters that don't fit in 8859-1.

It keeps going, of course (Wikipedia has info:
http://en.wikipedia.org/wiki/Category:ISO_8859).  There are many other
encodings that work this way, and collectively they're known as
single-byte encodings: they all represent a character as a single
byte, but the actual meaning of that byte depends in the character set
in use.

This situation is ripe for confusion, since interpreting a sequence of
bytes as being in a different encoding than it was stored in will lead
to strange results.  This is exactly what you saw in your Cocoa
editor, since it defaulted to using the classic MacRoman encoding,
which uses those same byte values to store uppercase characters
instead.

It gets worse: there are multi-byte encodings too.  You typically see
these in the East Asian languages, since they don't use the same
alphabetic writing system, and instead have thousands of characters to
encode.  A byte only supports a mere 256 values, so more than one byte
is needed to represent a single character.

By now you can see how this can spiral into an unmaintainable mess:
you have to worry about this encoding and that encoding and you can
store the encoding with the text but what do you do if someone
requests data in another encoding and what if they are using a
specific encoding but that text only contains ASCII characters and
therefore everyone should see it anyway and how do you tell the
difference and *brain asplode*

Enter Unicode, which has the goal of putting all the world's commonly
used language characters and symbols into one single character set.
By using Unicode, you don't have to worry about which character set
your data is in, and 

Re: [sqlite] SQLite and html character entities

2007-09-20 Thread Clark Christensen
It's a nightmare.  I can think of some ugly client-side scripting things to 
investigate, but I think you'd be on the way down a bunny hole :-))

If you submit the data, and do nothing but print it back to the browser, does 
it display correctly?

If you submit, store, retrieve and send it back to the browser, does it display 
correctly?

 -Clark


- Original Message 
From: P Kishor <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, September 20, 2007 9:12:30 AM
Subject: Re: [sqlite] SQLite and html character entities

On 9/20/07, Clark Christensen <[EMAIL PROTECTED]> wrote:
> The META tag you include looks correct to me.
>
> Does perl get the chars right after CGI decodes them?

This is what I am not completely sure of.

>
> The browser, ultimately, will escape the accented characters into
 urlencoded chars based on the utf-8 charset you specify in the HTML META
  tag.  Then Perl (via CGI) is going to decode those back into
 characters, probably using the host's default charset.  Seems like there's a
 chance for perl to mangle the accented chars during this step.
>

1. The following is one scenario I have experienced:

(a) SQLite has accented data
(b) I retrieve that data and display it in web form, and it show
accented and correctly
(c) I update some text in that form (not necessarily the accented
text) and update the entire record
(d) viewing the same data shows that it has gotten clobbered.

2. I have also experienced the following scenario:

(a) My Cocoa-based text editor writes scripts in utf8. So, stuff that
shows up at accented in the text editor, shows up funky in vim
(b) the same text shows up fine in the browser both ways, to and from
the browser, without any special encoding or decoding
(c) However, if I stick some accented text in a scalar and then
display it in the browser, it shows up clobbered in the browser even
though it shows up fine in the editor (see (a) and (b) above... fine
in the editor, screwed up in vim).
(d) I know (a) is true because if I add "use utf8" to the script then
all is well.

I don't care so much about the 2nd scenario except that my application
itself is in at least 3 languages (for now). That is, the interface
text, not just the data from SQLite, is also in Spanish and
Portuguese. Well, I have created separated templates and used properly
escaped entities in the interface text ( for é and so on) so
the interface text is fine and reliable. I just want to make sure that
nothing is clobbered in the data during its roundtrip from and to the
database, and assuming that the users will update the text with
correct accents instead of with escaped entities (that is, the users
will type é instead of ).

Puneet.


>
> - Original Message 
> From: P Kishor <[EMAIL PROTECTED]>
> To: Nuno Lucas <[EMAIL PROTECTED]>
> Cc: sqlite-users@sqlite.org
> Sent: Thursday, September 20, 2007 6:43:37 AM
> Subject: Re: [sqlite] SQLite and html character entities
>
> Thanks Nuno. Since I am raw in this matter, could I ask you for a
> little more hand-holding as specified below --
>
> On 9/20/07, Nuno Lucas <[EMAIL PROTECTED]> wrote:
> > You have to know the encoding of the user input. To do that, all
 your
> > html forms _MUST_ have proper  tags, and as you will be using
> > SQLite, the obvious encoding choice will be UTF-8 (because that way
> > you don't need to do any conversions when feeding/retrieving data
> > to/from SQLite).
>
> So, what is the proper meta tag? Is the following sufficient?
>
>  "http://www.w3.org/TR/html4/loose.dtd;>
> 
>   
> 
> 
>   
>   
>
> And, other than the above, I don't have to do anything else? Just a
> straight ahead INSERT with bind vars is enough?
>
> Many thanks in advance,
>
> Puneet.
>
>
> >
> > Then there is the problem of non-compliant browsers, but that is
> > another history...
> >
> >
> > Best regards,
> > ~Nuno Lucas
> >
> >
> > On 9/20/07, P Kishor <[EMAIL PROTECTED]> wrote:
> > > Folks,
> > >
> > > I come to ask you a question that may be basic for many of you
 but
>  is
> > > leaving me completely bewildered. My work environment is a Mac OS
 X
> > > (Tiger) computer, and I use a Cocoa-based text editor, and am
>  writing
> > > a Perl-based web app. Data are in several different languages,
> > > predominantly English, but with Portuguese, Spanish, and other
> > > languages mixed in... hence, have accent marks (diacritics).
> > >
> > > Goal: To reliably and consistently show the retrieved data in a
 web
> > > page or a web form with the correct diacritics, and when the user
> > > edits and updates that data, reliably and consistently u

Re: [sqlite] SQLite and html character entities

2007-09-20 Thread Clark Christensen
The META tag you include looks correct to me.

Does perl get the chars right after CGI decodes them?

The browser, ultimately, will escape the accented characters into urlencoded 
chars based on the utf-8 charset you specify in the HTML META  tag.  Then Perl 
(via CGI) is going to decode those back into characters, probably using the 
host's default charset.  Seems like there's a chance for perl to mangle the 
accented chars during this step.

 -Clark

- Original Message 
From: P Kishor <[EMAIL PROTECTED]>
To: Nuno Lucas <[EMAIL PROTECTED]>
Cc: sqlite-users@sqlite.org
Sent: Thursday, September 20, 2007 6:43:37 AM
Subject: Re: [sqlite] SQLite and html character entities

Thanks Nuno. Since I am raw in this matter, could I ask you for a
little more hand-holding as specified below --

On 9/20/07, Nuno Lucas <[EMAIL PROTECTED]> wrote:
> You have to know the encoding of the user input. To do that, all your
> html forms _MUST_ have proper  tags, and as you will be using
> SQLite, the obvious encoding choice will be UTF-8 (because that way
> you don't need to do any conversions when feeding/retrieving data
> to/from SQLite).

So, what is the proper meta tag? Is the following sufficient?

http://www.w3.org/TR/html4/loose.dtd;>

  


  
  

And, other than the above, I don't have to do anything else? Just a
straight ahead INSERT with bind vars is enough?

Many thanks in advance,

Puneet.


>
> Then there is the problem of non-compliant browsers, but that is
> another history...
>
>
> Best regards,
> ~Nuno Lucas
>
>
> On 9/20/07, P Kishor <[EMAIL PROTECTED]> wrote:
> > Folks,
> >
> > I come to ask you a question that may be basic for many of you but
 is
> > leaving me completely bewildered. My work environment is a Mac OS X
> > (Tiger) computer, and I use a Cocoa-based text editor, and am
 writing
> > a Perl-based web app. Data are in several different languages,
> > predominantly English, but with Portuguese, Spanish, and other
> > languages mixed in... hence, have accent marks (diacritics).
> >
> > Goal: To reliably and consistently show the retrieved data in a web
> > page or a web form with the correct diacritics, and when the user
> > edits and updates that data, reliably and consistently update the
> > database.
> >
> > Summary of problem: Data with diacritics show up fine in web forms,
> > but on updating, they get clobbered with gibberish and subsequently
> > show up incorrectly.
> >
> > So, I decided to do a little test. I created a small table, wrote a
> > script, and inserted a few records from the web. See the output of
 my
> > investigation below. I ask you, what is it that I have to do to
> > achieve my goal above? (output of test follows; I have separated
> > logical sections with a "---" line, and my comments start with
 #)
> >
> > Lucknow:~/Data/ecoservices punkish$ sqlite3 entities.sqlite
> > SQLite version 3.3.8
> > Enter ".help" for instructions
> > sqlite> .s
> > CREATE TABLE tbl (a text);
> > sqlite> select * from tbl;
> > the first record
> > é ç ñ î
> > more from 3rd row
> > row four
> > these "volunteered" activities
> > <á ø ã ü î & others>
> > -
> > sqlite> .mode csv
> > sqlite> .output foo.csv
> > sqlite> select * from tbl;
> > sqlite> .q
> > Lucknow:~/Data/ecoservices punkish$ less foo.csv
> > "the first record"
> > "\351 \347 \361 \356"
> > "more from 3rd row"
> > "row four"
> > "these \223volunteered\224 activities"
> > "<\341 \370 \343 \374 \356 & others>"
> > foo.csv (END)
> > -
> > sqlite> .mode html
> > sqlite> .output foo.html
> > sqlite> select * from tbl;
> > sqlite> .q
> > Lucknow:~/Data/ecoservices punkish$ less foo.html
> > "foo.html" may be a binary file.  See it anyway?
> > the first record
> > 
> >
> > 
> > more from 3rd row
> > 
> > row four
> > 
> > these <93>volunteered<94> activities
> > 
> >   others>
> > 
> > foo.html (END)
> > -
> > # below foo.html in my Cocoa-based text editor
> > the first record
> > 
> > È Á Ò Ó
> > 
> > more from 3rd row
> > 
> > row four
> > 
> > these ìvolunteeredî activities
> > 
> > · ¯ „ ¸ Ó  others>
> > 
> > -
> > # below foo.html in Safari; I added  tags to format
 correctly
> > the first record
> > é ç ñ î
> > more from 3rd row
> > row four
> > these "volunteered" activities
> > <á ø ã ü î & others>
> >
> >
 -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
 -
> >
> >
>


-- 
Puneet Kishor
http://punkish.eidesis.org/
Nelson Institute for Environmental Studies
http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo)
http://www.osgeo.org/
Summer 2007 S Policy Fellow, The National Academies
http://www.nas.edu/

-
To unsubscribe, send email to [EMAIL 

Re: [sqlite] like operator

2007-08-16 Thread Clark Christensen
You have to test your incoming values, and reject requests that have "%" (and 
other illegal) chars.

I never allow real deletes from a web form, and especially not from trusted 
users.  Consider adding a "deleted" column, and update the affected rows to 
indicate they've been deleted.  It's a little more work, but it sounds like 
it'll save you some pain in the long run.

 -Clark

- Original Message 
From: RaghavendraK 70574 <[EMAIL PROTECTED]>
To: SQLite 
Sent: Thursday, August 16, 2007 6:02:32 PM
Subject: [sqlite] like operator

Hi,

we have given a web interface which receive delete request.
Now in the req we get "%" and in the delete impl we do this
delete from table where itemName like xxx.%;

since the key is % the above statement becomes,
"delete from table where itemName like %.%";And result in fatal problem of 
erasing all records.

Is there any api to deal with like operator for these conditions, pls help. 
Hopefully fix will not 
degrade performance.

regrds
ragha


**
 This email and its attachments contain confidential information from HUAWEI, 
which is intended only for the person or entity whose address is listed above. 
Any use of the information contained herein in any way (including, but not 
limited to, total or partial disclosure, reproduction, or dissemination) by 
persons other than the intended recipient(s) is prohibited. If you receive this 
e-mail in error, please notify the sender by phone or email immediately and 
delete it!
 
*

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





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



Re: [sqlite] Need To Export A Table From a SQLite Database as a TAB Character Delimited Text File

2007-08-13 Thread Clark Christensen
I think 

.mode tabs

does much the same as 

.separator "\t"

but without the ambuguity of single vs double quotes.

 -Clark

- Original Message 
From: "Griggs, Donald" <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, August 13, 2007 2:37:06 PM
Subject: RE: [sqlite] Need To Export A Table From a SQLite Database as a TAB 
Character Delimited Text File

Regarding
  .separator "\t"
Vs
  .separator '\t'


Cool.   BTW, It seems to require double quotes for me, running Windoze
XP, sqlite3.exe version 3.4.0, else the separator becomes literally a
backslash followed by the letter t. I'm guessing you're using *NIX
and guessing it requires single quotes. 


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





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



Re: [sqlite] how to create C functions and refer to them in sql

2007-08-06 Thread Clark Christensen
I think you'd have to actually add your function into the SQLite source, and 
recompile.  My guess, not being a C guy, would be for you to have a look at the 
SQLite source (maybe in func.c?).  Since you already have a C function to do 
what you want, it seems pretty straightforward :-))

 -Clark

- Original Message 
From: Chase <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, August 6, 2007 2:43:13 PM
Subject: Re: [sqlite] how to create C functions and refer to them in sql


okay, wait sorry wrong question.

here's the deal.  i want this trigger to fire -- and insert valid guids 
into a table -- even outside the context of my app.

using sqlite3_create_function(), i can create a sort of temporary 
function that only works from with my app (or other running instances 
of my app), but i want it to fire even if someone opens the database 
file in some other 3rd-party editor and inserts/updates/deletes the 
table with the trigger.

well... it DOES fire, but when it does, it won't know what newuuid() means.

so i guess my question should be how to i **INSTALL/EMBED** my 
custom function **into** the database file.

- chase






On August 6, 2007, Eugene Wee wrote:

> You are probably looking for sqlite3_create_function:
> http://www.sqlite.org/capi3ref.html#sqlite3_create_function
> 
> Regards,
> Eugene Wee
> 
> Chase wrote:
>> 
>> i need a trigger to create and insert a new guid into a table, but 
>> apparently there is no built-in function for creating guids in sqlite.
>> 
>> i can create the guid in C using uuid_generate() and then uuid_unparse() 
>> to get it into a string format.
>> 
>> but how can i call that c code from a trigger?
>> 
>> my understanding was that sqlite allows for user functions written in C 
>> and used from within your sql code, but i forget where i saw that and 
>> i'm so far unable to find in the docs where it shows how this is done.
>> 
>> anyone done this before?
>> 
>> - chase
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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





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



Re: [sqlite] DELETE using a join?

2007-07-12 Thread Clark Christensen
delete from Payments where UserID in (select UserID from Users where UserName = 
'John Smith');

will get the job done.  And I'm sure there's a more elegant method.

 -Clark

- Original Message 
From: Scott Baker <[EMAIL PROTECTED]>
To: SQLITE 
Sent: Thursday, July 12, 2007 3:47:37 PM
Subject: [sqlite] DELETE using a join?

Let's say I have two really simple tables like so

Users
-
UserName
UserID

Payments

UserID
PaymentAmount

Is there an easy way to do something like "delete all entries in the
payment table where the Username is 'John Smith'"

I'm thinking it would be trivial to do it with a subquery, but is
there a way to do it with an inner join (wouldn't that be faster).

Something like (it doesn't work):

DELETE FROM Payments INNER JOIN Users USING (UserID) WHERE UserName
= 'John Smith';

-- 
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253

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





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



Re: [sqlite] Database Level Unique Sequence

2007-07-02 Thread Clark Christensen
http://www.mail-archive.com/sqlite-users@sqlite.org/msg10803.html

Describes a patch that implements a sequence table, and functions to deal with 
it.  You could use something like that to implement a unique-across-all-tables 
ID scheme.  Though I think someone else (Igor?) already suggested something 
similar.

 -Clark

- Original Message 
From: Andre du Plessis <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, July 2, 2007 9:36:02 AM
Subject: [sqlite] Database Level Unique Sequence

Good day everyone.

 

I would like to know how to create an Autoincrement field and insure
that it is unique across the database, I tested this and it does not
seem to work:

 

c:\Temp>sqlite3 temp.db

SQLite version 3.3.17

Enter ".help" for instructions

sqlite> create table temptable (id integer primary key autoincrement,
info text)

;

sqlite> create table temptable2 (id integer primary key autoincrement,
info text

);

sqlite> insert into temptable (info) values ('info1');

sqlite> insert into temptable2 (info) values ('info2');

sqlite> select * from temptable;

1|info1

sqlite> select * from temptable2;

1|info2

sqlite>

 

as you can see both have id = 1

 

I need this because I need a link table that wont know which table the
id comes from, and I cant add all the fields to make a compound key as
some of the values would then be blank.

 

Any suggestions is greatly appreciated.





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



Re: [sqlite] Optimization of equality comparison when NULL involved

2007-06-15 Thread Clark Christensen
In SQLite null is not equal to anything, including null.

I'm not sure what the best solution for your application is.  With help from 
the others here, I have learned to use coalesce() to convert nulls into a 
value, and to not allow null in key fields.

select 
  tableA.path,
  tableA.value 
from
  tableA,
  tableB 
where  
  tableA.path = tableB.path 
  and coalesce(tableA.value, '' ) = coalesce(tableB.value, '' );

yields 

alpha|1
gamma|

And it appears to use the myIndexB index for the join.

 -Clark

- Original Message 
From: Sean Cunningham <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Friday, June 15, 2007 1:26:49 PM
Subject: [sqlite] Optimization of equality comparison when NULL involved

I am hoping there is an obvious answer to this that I've overlooked.

I have two tables:

create table tableA (path TEXT, value TEXT);
create index myIndexA on tableA (path, value);

create table tableB(path TEXT, value TEXT);
create index myIndexB on tableB (path, value);


Now some simple inserts:

insert into tableA(path,value) values('alpha', '1');
insert into tableA(path,value) values('beta', '2');
insert into tableB(path,value) values('alpha', '1');
insert into tableB(path,value) values('beta', '999');

The following select statement gets what you'd expect, and it uses  
the index.

sqlite> select tableA.path, tableA.value from tableA,tableB where  
tableA.path=tableB.path and tableA.value=tableB.value;
alpha|1
gamma|3


However, if your data has NULL's:

insert into tableA(path,value) values('gamma', NULL);
insert into tableB(path,value) values('gamma', NULL);

The same select above neglects to report the tuple ('gamma',NULL) as  
the equal.  This is correct given SQL's treatment of NULL, and is  
easily fixed:

sqlite> select tableA.path, tableA.value from tableA,tableB where  
ifnull(tableA.path,'')=ifnull(tableB.path,'') and ifnull 
(tableA.value,'')=ifnull(tableB.value,'');
alpha|1
gamma|3
gamma|

However, the above statement has the unfortunate side effect of  
bypassing the index:

sqlite> explain select tableA.path, tableA.value from tableA,tableB  
where ifnull(tableA.path,'')=ifnull(tableB.path,'') and ifnull 
(tableA.value,'')=ifnull(tableB.value,'');
0|Goto|0|35|
1|Integer|0|0|
2|OpenRead|0|2|
3|SetNumColumns|0|2|
4|Integer|0|0|
5|OpenRead|1|4|
6|SetNumColumns|1|2|
7|Rewind|0|32|
8|Rewind|1|31|
9|Column|0|0|
10|String8|0|0|
11|CollSeq|0|0|collseq(BINARY)
12|Function|2|2|ifnull(2)
13|Column|1|0|
14|String8|0|0|
15|CollSeq|0|0|collseq(BINARY)
16|Function|2|2|ifnull(2)
17|Ne|28417|30|
18|Column|0|1|
19|String8|0|0|
20|CollSeq|0|0|collseq(BINARY)
21|Function|2|2|ifnull(2)
22|Column|1|1|
23|String8|0|0|
24|CollSeq|0|0|collseq(BINARY)
25|Function|2|2|ifnull(2)
26|Ne|28417|30|
27|Column|0|0|
28|Column|0|1|
29|Callback|2|0|
30|Next|1|9|
31|Next|0|8|
32|Close|0|0|
33|Close|1|0|
34|Halt|0|0|
35|Transaction|0|0|
36|VerifyCookie|0|4|
37|Goto|0|1|
38|Noop|0|0|



My question is:Is there another way to write such a select  
statement which can solve both problems of treating NULL==NULL and  
using the index.

Thanks,
Sean



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





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



Re: [sqlite] Select the top N rows from each group

2007-05-11 Thread Clark Christensen
I'm sure somebody can do better, but I I came up with this:

create table fruits (type text, variety text, price number);
create index fruit_type_price on fruits (type, price);
insert into fruits values ('apple', 'gala', 2.79);
insert into fruits values ('apple', 'fuji', 0.24);
insert into fruits values ('apple', 'limbertwig', 2.87);
insert into fruits values ('orange', 'valencia', 3.59);
insert into fruits values ('orange', 'navel', 9.36);
insert into fruits values ('pear', 'bradford', 6.05);
insert into fruits values ('pear', 'bartlett', 2.14);
insert into fruits values ('cherry', 'bing', 2.55);
insert into fruits values ('cherry', 'chelan', 6.33);

select
  f.type,
  f.variety,
  f.price
from 
  fruits f
where
  rowid in (select rowid from fruits where type = f.type order by price desc 
limit 2)
order by 
  f.type asc,
  f.price desc;

apple|limbertwig|2.87
apple|gala|2.79
cherry|chelan|6.33
cherry|bing|2.55
orange|navel|9.36
orange|valencia|3.59
pear|bradford|6.05
pear|bartlett|2.14

It's slow for a small result set.  100ms on my 2Ghz system under Windows.  It 
was over 300ms without the index.

 -Clark

- Original Message 
From: Yuriy Martsynovskyy <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Friday, May 11, 2007 2:44:30 PM
Subject: [sqlite] Select the top N rows from each group

I need to select top 2 (or N) most expensive fruits of each type from
this table:
+++---+
| type   | variety| price |
+++---+
| apple  | gala   |  2.79 |
| apple  | fuji   |  0.24 |
| apple  | limbertwig |  2.87 |
| orange | valencia   |  3.59 |
| orange | navel  |  9.36 |
| pear   | bradford   |  6.05 |
| pear   | bartlett   |  2.14 |
| cherry | bing   |  2.55 |
| cherry | chelan |  6.33 |
+++---+

The result should be this:
++--+---+
| type   | variety  | price |
++--+---+
| apple  | gala |  2.79 |
| apple  | fuji |  0.24 |
| orange | valencia |  3.59 |
| orange | navel|  9.36 |
| pear   | bradford |  6.05 |
| pear   | bartlett |  2.14 |
| cherry | bing |  2.55 |
| cherry | chelan   |  6.33 |

The actual table is large and may contain millions of records.
This sample is taken from article at
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/.
They offer this solution for MySQL as fastest:

(select * from fruits where type = 'apple' order by price limit 2)
union all
(select * from fruits where type = 'orange' order by price limit 2)
union all
(select * from fruits where type = 'pear' order by price limit 2)
union all
(select * from fruits where type = 'cherry' order by price limit 2)

But this query will not work on SQLite as SQLite supports only one
LIMIT. Can you suggest a good way to accomplish this task on SQLite?
Correlated queries are going to be slow

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





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



Re: [sqlite] May I ask why the source distribution mechanism was changed starting with 3.3.14?

2007-05-02 Thread Clark Christensen
Richard,

For what it's worth, it would be very convenient to have shell.c included in 
the preprocessed source distro.

sqlite3.def would also be convenient, but the

  nm sqlite3.o | grep ... | sed ... >>sqlite3.def

method seems to correctly generate sqlite3.def on my Windows system - EXCEPT, 
with 3.3.15 and 3.3.16, I have to manually add "sqlite3_io_trace" to 
sqlite3.def to get the shell to link into sqlite3.exe.  Otherwise, it fails 
with an "unresolved external..." error.

This is on Windows XP and MS VC6 using the pre-processed C source distro.  
shell.c was generated on my Linux box (Red Hat 7.2) using "make target_source" 
after running the configure script in the "regular" source distro.

The same link error occurs using the sqlite3.def file from the precompiled 
Windows DLL distro file.

Thanks!

 -Clark

- Original Message 
From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, May 2, 2007 7:53:35 AM
Subject: Re: [sqlite] May I ask why the source distribution mechanism was 
changed starting with 3.3.14?

"C.Peachment" <[EMAIL PROTECTED]> wrote:
> 
> After clearing these warnings, I discovered that the Pelles C
> compiler was unable to complete the compilation of sqlite3.c
> and timed out after 300 seconds. So it appears that I need
> to revert to separate source files that were available with
> earlier versions of sqlite3.
> 

I consider this to be a bug in Pelles C.  It refuses to compile
a valid ANSI C program.  On the other hand, this is the first
argument in favor of separate source files that makes sense to
me.  

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


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





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



Re: [sqlite] May I ask why the source distribution mechanism was changed starting with 3.3.14?

2007-04-27 Thread Clark Christensen
In general, I agree.  I miss the zipped set of pre-processed C source.

Since you have the Linux-based build system at your disposal, you can get what 
you're used to having with

make target_source

on the Linux system.  This creates a tsrc directory containing the familiar 
pre-processed C source for use in your Windows build.

For what it's worth, I was able to build the sqlite3.dll on Windows (VC6) from 
the "amalgam" sqlite3.c file without having to have lemon or any of the other 
SQLite-specific build components - except that pesky sqlite3.def file.  I still 
have to download the precompiled Win32 DLL to get that.

Looking at the generated makefile (from the configure script) on Linux, I see 
there's a rule for making sqlite3.def.  The algorithm (grep ... | sed ...) 
basically works on Windows, using the sqlite3.obj, but the resulting def is 
short a few lines compared to the one I downloaded.

Last, I tried to build the shell from the compiled amalgam, but it errors-out 
on the link phase complaining about unresolved externals (sqlite3_iotrace or 
somesuch).  And I don't have time right now to figure it out.  I'm not a C 
programmer, and am unfamiliar with compilers, linkers, compiler options, and 
all that.  I usually just have to do trial and error (lots of error) and 
eventually I figure it out.

 -Clark

- Original Message 
From: "Bennett, Patrick" <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Friday, April 27, 2007 6:57:38 AM
Subject: [sqlite] May I ask why the source distribution mechanism was changed 
starting with 3.3.14?

The last time I downloaded SQLite was version 3.3.12.
For that version (and many prior versions), I could download a
preprocessed archive containing all of the source code, except parse.h,
parse.c and opcode.h(? - this is from memory) were the 'generated'
versions.
The source for the command-line shell was there as well as all other
source code.  This was close to ideal for me, as I was able to use it in
our (custom) build system and build for Windows and Linux with almost no
effort at all.
Now, I have two choices:  
1) Download a tar of everything and have a version of lemon available
(which nothing else here uses), as well awk and other tools which don't
fit well within our windows-side compiles, or..
2) Download a preprocessed archive that contains only two files
(sqlite3.c and sqlite3.h) losing the ability to easily track changes (or
make local patches / fixes if necessary) as well as no longer having the
shell code.  I'll have to download both archives and piece together my
own build.

Hopefully this doesn't come off as too much of a nag, but the way it was
before was quite convenient and the new method seems to have taken the
'preprocessed' notion to the extreme.  
If this is how it's going to be from now on, I'll just have to adjust,
but if there wasn't any specific reason for changing, you can count this
as a vote for the 'old' way.  :)

Cheers...
Patrick Bennett





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





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



Re: [sqlite] Version 3.3.15

2007-04-09 Thread Clark Christensen
The sqlite3.def file is included in the Zip archive with the precompiled 
Windows DLL (http://www.sqlite.org/sqlitedll-3_3_15.zip).

For me, it's a minor annoyance to have to download the precompiled DLL when I'm 
making the DLL from source.  I've been meaning to ask to have sqlite3.def 
included with the pure C source.  I seem to remember some discussion about it 
previously on the list, and haven't had time to look-up the result.

 -Clark

- Original Message 
From: Gunnar Roth <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, April 9, 2007 10:33:11 AM
Subject: Re: [sqlite] Version 3.3.15

[EMAIL PROTECTED] schrieb:
> SQLite version 3.3.15 is now available on the website
>
>http://www.sqlite.org/
>
> This release fixes a problem introduced in 3.3.14 which
> causes the database connection to become wedged if you
> attempt to ROLLBACK a CREATE TEMP TABLE statement.
>
>   

Hello Mr. Hipp.
Is there a reason why the sqlite3.def and the shell.c file is missing 
from the zip distribution file?
And is there an easy way to get the def file when using msvc?

regards,
gunnar



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





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



Re: [sqlite] Re: storing funky text in TEXT field

2007-04-05 Thread Clark Christensen
> Suffice to say there is a terrible degree of annoying niggly details, 
> as ever when both “web” and “charset” show up in a single sentence.

Very true  :-))

If you ever find the energy to correct my errors, I'd be glad to hear it.

 -Clark

- Original Message 
From: A. Pagaltzis <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, April 5, 2007 9:19:19 AM
Subject: [sqlite] Re: storing funky text in TEXT field

* Clark Christensen <[EMAIL PROTECTED]> [2007-04-05 17:25]:
> I hate it when the CGI transaction clobbers characters.  You
> can set the content-encoding in the HTML to UTF-8, and it might
> help, but I think the conversion from the urlencoded value is
> dependent on the web server platform's encoding (OS codepage,
> app platform settings, etc.)

This description of the overall behaviour is grossly wrong in a
number of ways, but I don’t have the stamina right now to drop
over to Google and peel back the layers on this onion. Suffice to
say there is a terrible degree of annoying niggly details, as
ever when both “web” and “charset” show up in a single sentence.

(The first place I’d look is HTML5; the WHATWG is doing a good
job for document actual implemented browser behaviour, so if
they’ve written any spec text about this, that is likely to be
a good summary of what real browsers really do.)

> Plus, you run the risk of a user forcing the browser's encoding
> to something other than what you intended.

You may want to take a look at this:

HEBCI: HTML Entity-Based Codepage Inference
http://www.joshisanerd.com/set/

Regards,
-- 
Aristotle Pagaltzis // <http://plasmasturm.org/>

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





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



Re: [sqlite] storing funky text in TEXT field

2007-04-05 Thread Clark Christensen
I hate it when the CGI transaction clobbers characters.  You can set the 
content-encoding in the HTML to UTF-8, and it might help, but I think the 
conversion from the urlencoded value is dependent on the web server platform's 
encoding (OS codepage, app platform settings, etc.)

Plus, you run the risk of a user forcing the browser's encoding to something 
other than what you intended.

 -Clark

- Original Message 
From: P Kishor <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, April 5, 2007 7:39:33 AM
Subject: [sqlite] storing funky text in TEXT field

Seems like a basic question, but I can't figure out a definitive
answer to this. I want to store UTF-8 characters in a TEXT field,
y'know, things like umlauts and accents and that Norwegian slashed-O
thingy, perhaps even South Asian Devnagari. The documentation says
about text -- "TEXT. The value is a text string, stored using the
database encoding (UTF-8, UTF-16BE or UTF-16-LE)."

So, what do I do? Do I have to declare the database encoding while
creating the database? I find that if I update the database table via
a web-form, these funky characters get clobbered. Is this something
that I have to account for in my web application? How?

Many thanks in advance.

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

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





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



Re: [sqlite] API for backups

2007-04-05 Thread Clark Christensen
I don't know if there are any APIs for backing-up.  I don't think there are.

I use this algorithm:

open database using sqlite3_open() or equivalent in your wrapper.
begin immediate or exclusive transaction to lock the database from all other 
access
copy the file on the file system
rollback transaction to release the lock

 -Clark

- Original Message 
From: Paul Richards <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, April 5, 2007 4:23:18 AM
Subject: [sqlite] API for backups

Hi,
Are there functions in the SQLite API for creating live backups of a database?

I see that the sqlite3 command line tool has a "dump" command, but can
this feature be accessed through the normal C API?



-- 
Paul Richards

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





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



Re: [sqlite] Re: SQL help

2007-04-04 Thread Clark Christensen
Igor,

Amazing.  Thanks very much for your help.  You get credit in my code comments 
:-))

 -Clark

- Original Message 
From: Igor Tandetnik <[EMAIL PROTECTED]>
To: SQLite <sqlite-users@sqlite.org>
Sent: Tuesday, April 3, 2007 12:20:57 PM
Subject: [sqlite] Re: SQL help

Clark Christensen 
wrote:
> I have a table, as described below, where I need to find out if the
> tech_id in question has at least some modules in a particular
> collection (coll_id), and they're all complete.

select coalesce(min(is_complete), 0) from tech_modules
where tech_id=? and coll_id=?;

This returns 0 if there's at least one record with is_complete=0 (min 
will select it), or if there are no matching records at all (min will 
produce NULL, coalesce will convert it to 0).

Igor Tandetnik 


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





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



Re: [sqlite] SQL and SQLite pronounciation?

2007-04-04 Thread Clark Christensen
Personally, I use "sequel" and "sequel-light".  I think I remember from DRH's 
Google presentation video he uses "ess cue el ite" for his product.

 -Clark

- Original Message 
From: Dennis Cote <[EMAIL PROTECTED]>
To: sqlite-users 
Sent: Wednesday, April 4, 2007 1:24:39 PM
Subject: [sqlite] SQL and SQLite pronounciation?

Hi All,

I have a simple question; how do you pronounce SQL and SQLite?

I have heard some people pronounce SQL like "sequel", others like 
"squeal", and others like three words "ess cue el". Which do you prefer?

How about SQLite? Is it "ess cue light" or something else like "sequel 
light"?

I prefer "ess cue el" and "ess cue light" myself.

Dennis Cote



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





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



Re: [sqlite] Novice help

2007-04-03 Thread Clark Christensen
Once you create a table, new.db will appear on disk in the default directory.

Have a look at SQLite Spy (http://www.yunqa.de/delphi/sqlitespy/) and SQLite 
Explorer (http://www.singular.gr/sqlite/).  Both are decent Windows GUI tools 
for SQLite.  Neither provides much of a UI for data entry, but both are good 
for showing the schema in a tree, and presenting a GUI windowed app for testing 
your SQL.

 -Clark


- Original Message 
From: Raju Penmetsa <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, April 3, 2007 4:17:36 PM
Subject: [sqlite] Novice help

Hi,

I am completely new to SQlite. I have a faint idea of SQL from MS Access 
queries. I need to develop some crosstab queries on these huge databases (~14 
million records), which I am not able to, in MS Access.

I downloaded all the Windows binary files of SQlite on the download page of 
sqlite.org. I extracted all of them into separate folders. 
Now, when I open sqlite3.exe in a DOS window and type - "sqlite3 new.db", it 
should create a new db if there is none already existing, right?
It is not creating anything for me Or I am not able to find where it is 
creating one.

Any help on the quick start is highly appreciated.

Thanks

Raju

John Stanton <[EMAIL PROTECTED]> wrote: Vivek Rajan wrote:
> Thanks everyone. Appreciate your help. I am most proficient with PERL and
> was hoping to use PERL. PHP looks interesting.
> 
> John- How do you generate AJAX style dynamic HTML? Can you provide more
> info?
> 
Sure.  I wrote a compiler and a virtual machine for a simple language 
which incorporates basic control structures including procedure calls 
plus an interface to Sqlite such that it binds a callback which can be a 
procedure in HTML or Javascript or both to a row so that the callback 
fires each time a row is delivered.  The VM places the row data into 
local storagei each time the DBMS delivers it a row.

A very common usage of a callback procedure is to form a row in a table.

A special HTML tag is defined to make the embedded instructions look 
like HTML.  The compiler strips out these tags and creates a byte code 
executable format which makes the generation of output pages efficient.

The HTML/Javascript pages is compiled into a byte code format and upon 
execution its variables are dereferenced from the local data store and 
embedded in the generated HTML page.

There is also an RPC capability linked to the XMLHttpRequest Javascript 
object.  A request from the web page in the browser sends data in JSON 
format and that loads Javascript variables which can be displayed.  That 
implements AJAX by allowing a web page to behave dynamcially without the 
need to refresh.  The RPC processor can alternatively deliver its data 
in XML.

The RPC capability is designed to make the interaction between the web 
page and the server as transparent and simple as possible.  These is a 
repository of RPCs and these are accessed by name.

RPC calls can also INSERT and UPDATE rows in the DB.

The application server holds each instance of an Sqlite database open 
exclusively and serves all the threads using it from the one instance. 
Each user connection is a thread in the application server.  The DB can 
be on a different machine without problems because it is accessed 
exclusively and no locking glitches get in the way of secure processing. 
  Synchronization is supplied by rwlocks on the threads.

A graphics processor is built in to allow charts as an alternative to 
tables and the generated images are embedded in the generated pages.

Finally the design of the server is based on the principle that business 
rules etc will be defined as database functions, not embedded in the 
workflow level which generates and supports web pages.

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



 
-
Now that's room service! Choose from over 150,000 hotels 
in 45,000 destinations on Yahoo! Travel to find your fit.



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



Re: [sqlite] sqlite and generate dynamic html

2007-04-03 Thread Clark Christensen
Vivek,

You ask a very broad question.  I wouldn't know where to begin with code 
samples.  If you were to ask for some specific, "how do I do...?" questions, 
I'm sure you'd get some code examples.

You'll need to get the DBI modules installed, and the DBD-SQLite driver module 
installed into Perl.



What you describe is pretty basic variable manipulation, and file I/O with some 
DBI thrown-in for good measure.  IMO, Perl is very good at this kind of thing.  
You'll need some basic understanding of Perl references, too.

If you're not already familiar with the Perl DBI, you should read-up on it.  
You can start at http://search.cpan.org/~timb/ with the DBI-1.54 
(http://search.cpan.org/~timb/DBI-1.54/), where you'll find the module's docs.  
Also on the list page there is a link to "DBI Advanced Talk 2004" 
(http://search.cpan.org/src/TIMB/DBI_AdvancedTalk_2004/index.htm), a PowerPoint 
presentation where DBI's author talks about some best practices, how to get 
best performance, and how to detect and deal with errors.  It's been very 
useful for me.  I didn't quite follow it all in the beginning, but as I learn 
more, I return to it, from time to time, and it makes more sense.

 -Clark

- Original Message 
From: Vivek Rajan <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, April 3, 2007 2:20:31 PM
Subject: Re: [sqlite] sqlite and generate dynamic html

I need to generate dynamic HTML from contents in the SQLite database.
Display some text/tables and related. Upon clicking on the web-page, the
web-page can generate another query and display subsequent queries as HTML
on the web.

On 4/3/07, John Stanton <[EMAIL PROTECTED]> wrote:
>
> What does the HTML have to do?
>
> Vivek Rajan wrote:
> > Hello SQLite Community-
> >
> > For a personal project, I need to dynamically create HTML pages from an
> > on-disk SQLite database. I don't need cross-network capability, I just
> need
> > the ability to query and dynamically generate HTML from an on-disk
> SQLite
> > database on a localhost (stand-alone unix machine). I am using PERL as
> the
> > API for accessing/querying SQLite.
> >
> > Has someone done something like this? Could you please post any code
> > snippets, suggestions and/or pointers for how to do this?
> >
> > Thanks in advance.
> >
> > Rajan
> >
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>


-- 
Vivek Rajan
(503) 646-3985




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



Re: [sqlite] SQL help

2007-04-03 Thread Clark Christensen
>You could try this:

>  select count(*) as mcount, sum(is_complete) as mcomplete
>  from tech_modules
>  where tech_id = ? and coll_id = ?;

Dennis,

Yeah, that's much cleaner.  Just once through the tech's module set instead of 
twice, and it satisfies both requirements (at least some modules, and all 
complete).

Thanks again!

 -Clark

- Original Message 
From: Dennis Cote <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, April 3, 2007 12:27:54 PM
Subject: Re: [sqlite] SQL help

Clark Christensen wrote:
> I have a table, as described below, where I need to find out if the tech_id 
> in question has at least some modules in a particular collection (coll_id), 
> and they're all complete.
>
> At this point, I'm working with variations on:
>
> select
> (select count(*) from tech_modules
>  where tech_id = ? and coll_id = ?) as mcount,
> (select count(*) from tech_modules where tech_id = ? 
>  and coll_id = ? and is_complete = 1) as mcomplete;
>
> Performing the comparisons on mcount and mcomplete in the app ($mcount > 0 && 
> $mcomplete == $mcount) to get a boolean $allComplete.
>
> It performs OK (~50K rows in the table), using the unique index for both 
> subqueries, but the SQL seems crude.  Anybody have a more elegant solution to 
> share?
>
> Thanks!
>
>  -Clark
> 
> CREATE TABLE TECH_MODULES (
> TECH_ID integer,
> MODULE_ID integer,
> COLL_ID integer,
> IS_COMPLETE integer default 0,
> COMPLETION_TIME date,
> COMPLETION_TARGET date,
> DELETED integer
> );
>
> CREATE INDEX tech_modules_by_module_id ON TECH_MODULES (module_id);
>
> CREATE UNIQUE INDEX tech_modules_unique_module_id on TECH_MODULES (tech_id, 
> coll_id, module_id);
>
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>   
Clark,

You could try this:

  select count(*) as mcount, sum(is_complete) as mcomplete
  from tech_modules
  where tech_id = ? and coll_id = ?;

HTH
Dennis Cote

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





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



Re: [sqlite] Version 3.3.14

2007-04-02 Thread Clark Christensen
Excellent.  All tests passed.  Thanks for the quick fix.

 -Clark


- Original Message 
From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, April 2, 2007 3:20:45 PM
Subject: Re: [sqlite] Version 3.3.14

Clark Christensen <[EMAIL PROTECTED]> wrote:
> > Did you use "configure; make" to get this error?
> 
> Yes.  I used the configure script, and make, as you describe.
> 

Apply the patch at

   http://www.sqlite.org/cvstrac/chngview?cn=3805

The library itself builds correctly, just not the test harness.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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





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



Re: [sqlite] Version 3.3.14

2007-04-02 Thread Clark Christensen
> Did you use "configure; make" to get this error?

Yes.  I used the configure script, and make, as you describe.

 -Clark

- Original Message 
From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, April 2, 2007 1:47:26 PM
Subject: Re: [sqlite] Version 3.3.14

Clark Christensen <[EMAIL PROTECTED]> wrote:
> I'm seeing an error in make test for 3.3.14:
> 
> /tmp/ccDdRRCh.o: In function `Sqlitetest1_Init':
> /home/cchriste/sqlite-3.3.14/src/test1.c:4321: undefined reference to 
> `sqlite3_xferopt_count'
> collect2: ld returned 1 exit status
> make: *** [testfixture] Error 1
> $
> 
> Red Hat Linux 7.2 (2.4.7.10);
> gcc 3.0.2
> tcl 8.4.12
> 
> SQLite v3.3.13 and earlier tested OK for me.  Any ideas?  Something too old 
> in my setup?
> 

The sqlite3_xferopt_count global variable only exists if you
compile with -DSQLITE_TEST=1.  It appears that the insert.c
module was not so compiled.  Did you use "configure; make" to
get this error?
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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





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



Re: [sqlite] Version 3.3.14

2007-04-02 Thread Clark Christensen
I'm seeing an error in make test for 3.3.14:

/tmp/ccDdRRCh.o: In function `Sqlitetest1_Init':
/home/cchriste/sqlite-3.3.14/src/test1.c:4321: undefined reference to 
`sqlite3_xferopt_count'
collect2: ld returned 1 exit status
make: *** [testfixture] Error 1
$

Red Hat Linux 7.2 (2.4.7.10);
gcc 3.0.2
tcl 8.4.12

SQLite v3.3.13 and earlier tested OK for me.  Any ideas?  Something too old in 
my setup?

Thanks!

 -Clark

- Original Message 
From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, April 2, 2007 8:46:57 AM
Subject: [sqlite] Version 3.3.14

SQLite version 3.3.14 is now available on the SQLite website

   http://www.sqlite.org/

Version 3.3.14 focuses on performance improvements.  There 
have been several changes to the back-end layers (the pager
and the b-tree subsystems) that reduce the amount of disk
I/O.  A new optimization has been added to the INSERT command
which, as a side effect, makes VACUUM work significantly faster
for large databases and helps VACUUM to greatly reduce the
amount of fragmentation in the database.  We have also added 
the concept of "exclusive access mode".  In exclusive access 
mode, SQLite holds onto locks until you close the connection.
This allows for additional I/O reductions and corresponding
performance improvements, at the expense of concurrency.  The
core SQLite sources are now also available as a single huge 
file of C code (which we call "the amalgamation") rather than 
as a collection of smaller files.  

When all the latest changes are used and the code is
recompiled using the amalgamation with -O3 under gcc 4.1.0,
we are seeing performance improvements on Linux of about 35%
over version 3.3.13.  We are very interesting in hearing 
about performance changes on other compilers and with other 
operating systems.

Version 3.3.14 incorporates many changes over version 3.3.13.
These changes have been well tested on Linux, but as the
no so much on windows and other systems.  If you find problems,
please report them either on this list or at

  http://www.sqlite.org/cvstrac/tktnew

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



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





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



Re: [sqlite] Finding linked peers

2007-03-20 Thread Clark Christensen
>Also note that using "version DESC" will not necessarily give you the
expected results

Gotcha.  Always good to remember :-))

Thanks!

 -Clark

- Original Message 
From: Samuel R. Neff <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, March 20, 2007 12:50:12 PM
Subject: RE: [sqlite] Finding linked peers


It's much easier to load all the data into a in-memory hierarchical
structure and work with it from memory than try to come up with individual
SQL statements.  Some things you could do easily like find the leaf nodes
(i.e., use a sub-select and identify nodes that nobody else uses as a
parent) or find the root nodes (parent id is null) but getting a full
hierarchy requires multiple SQL statements and thus is faster to process in
a custom hierarchical structure rather than through SQL.

Also note that using "version DESC" will not necessarily give you the
expected results because it's using string comparison and '10.0' is less
than '2.0' with string comparison.  You could create a custom collation
routine to do proper version number comparison but it'd be faster to store
the data as separate integers (a field for major and minor version).

HTH,

Sam


---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Clark Christensen [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, March 20, 2007 1:57 PM
To: SQLite List
Subject: [sqlite] Finding linked peers

I'm having trouble wrapping my head around a solution here.  Any advice is
appreciated.

I'm working on a SQLite-based app for keeping track of PC BIOS releases.
One obvious requirement is to be able to track and document change history.
So, using this sample schema/data code:

create table bios_release (
  oid integer primary key, 
  parent_id integer, 
  group_id integer, 
  version text, 
  bios_name text
);

insert into bios_release values (1, null, 1, '1.10', 'PC1_v1.1');
insert into bios_release values (2, null, 2, '1.10', 'PC2_v1.1');
insert into bios_release values (3, 1, 1, '1.20', 'PC1_v1.2');
insert into bios_release values (4, null, 3, '1.10', 'PC3_v1.1');
insert into bios_release values (5, 3, 1, '1.30', 'PC1_v1.3');

/* irrelevant to the discussion, but provided for clarity (?) */
create table bios_group (

  oid integer primary key,

  group_name text

);



insert into bios_group values (1, 'PC1 Group');


insert into bios_group values (2, 'PC2 Group');


insert into bios_group values (3, 'PC3 Group');




I see two ways to go to know which releases.  The obvious way is  to simply
maintain a history group with an ID number that gets stored in each member
BIOS release record.  A simple:

select * from bios_release where group = 1 order by version desc;

will get me all the members of the PC1 Group in descending order.  That's
fine, but, creating, naming, and maintaining the groups is a hassle for the
administrators.

The other option I'm considering is chaining each new bios_release to its
predecessor by storing the predecessor OID in the parent_id column.  Doing
it this way makes some sense from the admin process POV because usually, the
admin has a new BIOS with a set of known properties, and not necessarily any
knowledge of what group it might belong to, or whether or not she needs to
create a new group for this BIOS.

So, finally, the question:  What might the SQL look like to retrieve a list
of predecessors for 'PC1_v1.3'?  Sure, I could prepare a stmt in the app
(Perl), and walk the chain in some fashion. Is this result even possible
using plain SQL?


TIA

 -Clark


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





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



Re: [sqlite] Finding linked peers

2007-03-20 Thread Clark Christensen
Dennis,

Very cool.  Thanks for showing the example.  You always seem to offer 
well-considered solutions.  It might just be a practical tool for the job here.

I could see the materialized path solution working both for finding the change 
history, and for producing a browser-based UI for identifying a release's 
predecessor.

 -Clark

- Original Message 
From: Dennis Cote <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, March 20, 2007 11:27:39 AM
Subject: Re: [sqlite] Finding linked peers

Clark Christensen wrote:
> So, finally, the question:  What might the SQL look like to retrieve a list 
> of predecessors for 'PC1_v1.3'?  Sure, I could prepare a stmt in the app 
> (Perl), and walk the chain in some fashion. Is this result even possible 
> using plain SQL?
>
>   
Clark,

SQLite does not support the recursive SQL queries that could be used to 
do this kind of processing. So there is no way to follow a chain in SQL. 
You can convert the problem into pattern matching by having each record 
store the path along the chain in that record. This is really a 
variation of the SQL tree problem. I have previously posted a sample 
implementation of this materialized (or stored) path method at 
http://www.mail-archive.com/sqlite-users@sqlite.org/msg13225.html

HTH
Dennis Cote

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





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



[sqlite] Finding linked peers

2007-03-20 Thread Clark Christensen
I'm having trouble wrapping my head around a solution here.  Any advice is 
appreciated.

I'm working on a SQLite-based app for keeping track of PC BIOS releases.  One 
obvious requirement is to be able to track and document change history.  So, 
using this sample schema/data code:

create table bios_release (
  oid integer primary key, 
  parent_id integer, 
  group_id integer, 
  version text, 
  bios_name text
);

insert into bios_release values (1, null, 1, '1.10', 'PC1_v1.1');
insert into bios_release values (2, null, 2, '1.10', 'PC2_v1.1');
insert into bios_release values (3, 1, 1, '1.20', 'PC1_v1.2');
insert into bios_release values (4, null, 3, '1.10', 'PC3_v1.1');
insert into bios_release values (5, 3, 1, '1.30', 'PC1_v1.3');

/* irrelevant to the discussion, but provided for clarity (?) */
create table bios_group (

  oid integer primary key,

  group_name text

);



insert into bios_group values (1, 'PC1 Group');


insert into bios_group values (2, 'PC2 Group');


insert into bios_group values (3, 'PC3 Group');




I see two ways to go to know which releases.  The obvious way is  to simply 
maintain a history group with an ID number that gets stored in each member BIOS 
release record.  A simple:

select * from bios_release where group = 1 order by version desc;

will get me all the members of the PC1 Group in descending order.  That's fine, 
but, creating, naming, and maintaining the groups is a hassle for the 
administrators.

The other option I'm considering is chaining each new bios_release to its 
predecessor by storing the predecessor OID in the parent_id column.  Doing it 
this way makes some sense from the admin process POV because usually, the admin 
has a new BIOS with a set of known properties, and not necessarily any 
knowledge of what group it might belong to, or whether or not she needs to 
create a new group for this BIOS.

So, finally, the question:  What might the SQL look like to retrieve a list of 
predecessors for 'PC1_v1.3'?  Sure, I could prepare a stmt in the app (Perl), 
and walk the chain in some fashion. Is this result even possible using plain 
SQL?


TIA

 -Clark



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



Re: [sqlite] Format lf csv file RE: [sqlite] date/time implementation question

2007-03-14 Thread Clark Christensen
Hi Rafi,

If it were mine to do, I would concentrate on getting the data into a table 
where I can work with it using SQL.

It sounds like your best bet is to write some simple code to read through your 
CSV, validate its consistency (ignore the dates), and insert it into a table.  
Then use Dennis's temp table scenario to process the rows as appropriate.

Obviously, it doesn't necessarily need to be a temp table as in "create temp 
table...".  It could easily be a non-temp "scratch", or "pending" table that 
doesn't go away when the connection closes.  That way, you can close and come 
back later to deal with the rows that crossed-over into past-date state.

 -Clark

- Original Message 
From: Rafi Cohen <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, March 14, 2007 1:21:21 PM
Subject: [sqlite] Format lf csv file RE: [sqlite] date/time implementation 
question

Hi Dennis, the first approach is clear now and I may proceed with it.
The second approach is interesting and chalenging, but leaves some
issues to clarify and in case I find solutions to those issues I well
may adopt it.
1. format of csv file: I have no idea how this csv file is created and
which database engine is used. I do know that I receive such a file once
or twice a day.
While reading the file onto the list of structures, I also validate the
consistency of the data in the file.
Before the data lines (starting with "d,") there is a header line
"hdr,". This line contains the sequential number of the file, number of
records in the file and a checksum on a specific field of the data (say
coumn 2).
As I knew nothing up to now about .import, I wonder if there is a way to
include those checings in the second approach?
2. The deletion of the future dates is incorrect. On the contrary, in
the first approach, I re-examine the remaining structures each half a
minute until any of them becomes past date, then I process it just like
any other past date structures and then free it.
In case a new .csv file arrives, I add the new list of structures to the
remaining ones and continue to examine them every half a minute.
I could do the same with the sql3_exec statement in the second approach,
but I need another approach for the case of the future records.
I hope you have satisfying answers for those 2 issues and then I'll be
glad to proceed with the second approach.
Thanks, Rafi.

-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, March 14, 2007 9:15 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] date/time implementation question


Rafi Cohen wrote:
> Dennis, I really appreciate your patience and willingness to help. 
> Unfortunately, this still did not bring me to the expected solution. I

> will give you a small algorithm of what I need to do and I'm sure 
> after this you'll know how to assist me. 1. I read a .csv file into a 
> linked list of structures. 2. I examine eacch structure one after the 
> other: Compare the datetime filed of the structure aginst the current 
> date. If bigger (future), I skup to the next structure.
> If smaller or equal, I check if a row with the same id field already
> exists in the table:
> Select * from tbl where id = id-in-struct.
> If no such row exists, I insert a row according to the structure's
> field, otherwise, based on another criteria I EITHER UPDATE THE row
with
> a new value on the second column or delete the row.
> 3. After this process, I free the structure from the linked list and
> move to the next structure.
> Because of the last section, I thought I need to make the date
> comparison in C, but I may be wrong here.
> However, if the comparison is made thru sqlite, how can I know if
indeed
> an insert, update or delete was processed so that I can free the
> structures?
> All the sql statements I use above are, of course, prepared statements
> which I execute with wqlite3_step for the fields of each structure.
> I hope I'm clear, this time.
>   
Rafi,

I think I have followed your description and I agree that you could do 
this by iterating in C as you have described. If you want to do that and

all you need is an expedient way to get a date and time string for the 
current time for the comparison in step 2 you have a couple of
approaches.

One simple way is to let sqlite do it for you by executing a single SQL 
statement "select datetime('now')". This will return a ISO date and time

string that you can compare with the concatenated strings that are in 
your structure. The other is to use the standard C library routines to 
build an ISO formatted date and time string. These two examples are 
shown below in a mix of C an pseudo code comments (you have to fill in 
the blanks).

Get current date and time from sqlite:

sqlite3_stmt* get_now;
sqlite3_prepare(db, "select datetime'now'", -1, _now, NULL);  

char now_datetime[20];
sqlite3_step(get_now);
strcpy(now_datetime, sqlite3_column_text(get_now, 0));

// read csv into 

Re: [sqlite] sin and similar functions

2007-03-09 Thread Clark Christensen
A poster here, "Mikey C", wrote some math functions and posted them a month or 
so ago.  You might have a look at 
http://www.mail-archive.com/sqlite-users@sqlite.org/msg21791.html

 -Clark

- Original Message 
From: Jakub Ladman <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Friday, March 9, 2007 3:00:22 PM
Subject: Re: [sqlite] sin and similar functions

Thank you very much.
Not only my english is poor, but my coordinated geometry too :-(
But my software will make much more geometrical computations, so probably some 
in queries too.
Please let You (or someone) direct me to the relevant part of documentation 
for defining new functions.

Thank You
Jakub


Dne pátek 09 březen 2007 23:35 Dennis Cote napsal(a):
> Jakub Ladman wrote:
> > I will have table with sequence of coordinates (two dimensional space)
> > and corresponding radiuses, so sequence of circles. And i need to use a
> > sqlite query to detect if a actual coordinates (after their measurement)
> > match some of the circle's square or not. And which circle, if match.
> > And this must be for low CPU consumption optimised, so i am not sure, if
> > separate sin table queries will be enough as fast as i need at needed
> > precission.
> >
> > The whole algorithm is proven on mssql by my colegue, but he is using the
> > native math functions.
>
> Jakub,
>
> I may not understand your problem completely, but it seems to me you can
> solve your problem without using any trigonometric functions.
>
> If you have a table of circles like this
>
> create table circle (
> id  integer primary key,
> cx  real,
> cy  real,
> r   real
> );
>
> You can find all the circles that contain a given point (px,py) using a
> simple query based in the distance between the point and the center of
> the circle.
>
> select id from circle
> where (px-cx)*(px-cx)+(py-cy)*(py-cy) < r*r;
>
> If you want to create a user defined distance function you could
> possibly speed up the calculation somewhat. You could then use a query
> like:
>
> select id from circle
> where distance(cx, cy, px, py) < r;
>
> where
>
> distance(cx, cy, px, py) = sqrt((px-cx)^2 + (py-cy)^2)
>
> HTH
> Dennis Cote
>
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--

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





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



Re: [sqlite] Is there an inverse for .import?

2007-03-09 Thread Clark Christensen
>From the SQLite shell, you can send the output to a file using

.output myfile.txt

So

.output myfile.txt
select * from mytable;
.output stdout

will get you a pipe-delimited myfile.txt.  You can change the delimiter using 
the .separator command, or you can use .mode to use a predefined format.

 -Clark

- Original Message 
From: "Anderson, James H (IT)" <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Friday, March 9, 2007 9:04:29 AM
Subject: [sqlite] Is there an inverse for .import?

I need to "export" a table to a file in the same format as used by
.import, but I don't see any such cmd. Am I missing something, or does
such a cmd just not exist?

jim




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



Re: [sqlite] Installing & Running Sqlite on Linux Webserver?

2007-02-28 Thread Clark Christensen
I've had good results with Vizaweb.  They have PHP, and Perl by default, as 
well as MySQL and Postgres.  Plus, they've expressed their willingness to 
consider installing other stuff.  My guess is your part of the site runs in its 
own VM of some kind, so the impact to the host system, and other subscriber 
sites is limited.

I am able to compile SQLite, but the default tcl version is older than SQLite 
expects, so you'd have to get them to update it, or omit the SQLite tcl 
interface (but then you can't make test).

Anyway, with both PG and MySQL, you have a database option other than SQLite.

 -Clark

- Original Message 
From: Tom VP <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, February 28, 2007 11:23:06 AM
Subject: Re: [sqlite] Installing & Running Sqlite on Linux Webserver?

Thank you, John. The problem with shared servers is the real and extensive 
restrictions on running apps and scripts - I'm considering hosting my own 
web server.

Tom :}
- Original Message - 
From: "John Stanton" <[EMAIL PROTECTED]>
To: 
Sent: Wednesday, February 28, 2007 12:07 PM
Subject: Re: [sqlite] Installing & Running Sqlite on Linux Webserver?


> Sqlite is a library which is linked into your application.  Write your 
> application using the Sqlite API and then load that program onto the web 
> server.
>
> Tom VP wrote:
>> I would like to install and run sqlite on my host web server. Does anyone 
>> know how I can do this? I downloaded:
>> sqlite-2.8.17.bin.gz and gunzipped it to: sqlite-2.8.17.bin.
>> Is this what I would install and how. My website is on a shared server, 
>> so is there a problem with running sqlite in such an environment?
>>
>> Whatever assistance you can provide will be greatly appreciated.
>>
>> Thnx,
>> Dr. Tom
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
> 


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





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



Re: [sqlite] UPDATE base on certain values

2007-02-13 Thread Clark Christensen
Maybe coalesce() would work just as well:

> UPDATE table SET
>  ID = '88',
>  parent = (SELECT CASE WHEN parent IS NULL THEN '1171291314642'
> END FROM table WHERE ProjID = '88'),
> ..
>  WHERE ProjID = '88';

becomes:

UPDATE table SET
 ID = '88',
 parent = coalesce(parent, '1171291314642'),
..
 WHERE ProjID = '88';

Since someone else pointed out the value of coalesce() for me recently, I'll 
share the advice.


 -Clark

- Original Message 
From: P Kishor <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, February 13, 2007 12:29:25 PM
Subject: Re: [sqlite] UPDATE base on certain values

On 2/13/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote:
>
> "P Kishor" wrote,
>
> > On 2/13/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote:
> >>
> >> Greetings!
> >>
> >> I would like to be able to update certain columns based on their value.
> >> I
> >> was looking at "ON CONFLICT", but I could not understand it nor I could
> >> not
> >> find any examples quickly, so I say, "heck, that's what the list is
> >> for..."
> >> :-)
> >>
> >> What I would like is to do something like this,
> >>
> >> UPDATE table SET
> >> ID = '88' if not = '88',
> >> parent = '1171291314642' if null,
> >> children = '',
> >> login = 'blah',
> >> notes = 'blah-blah' if null,
> >> status = 'o'
> >> WHERE ProjID = '88';
> >>
> >
> > Well, if you
> > UPDATE table
> > SET ID = '88'
> > WHERE ProjID = '88'
> >
> > it will set it to 88 whether or not it is 88. Problem solved. Also
> >
> > UPDATE table
> > SET parent = '1171291314642'
> > WHERE ProjID = '88' AND parent IS NULL
> >
> > will do the appropriate thing only if parent is null. Else, it will
> > leave it alone. In any case, WHERE clause is the correct place to put
> > your constraints, not the SET clause, unless you write functions that
> > return the desired value to be SET.
>
> So, there is on way of doing it in one call/instruction as I previously
> exampled, correct?  I will have to do them in many calls, correct?


Not that I know of. You want to update different columns (your SET)
based on different criteria (your WHERE)... remember that your
constraint, no matter how complicated it is, acts on the entire set of
values, not on individual elements of that set. Perhaps you could do
nested SELECT queries, but they would be so much more messy and error
prone than multiple calls. You could try something like

UPDATE table SET
 ID = '88',
 parent = (SELECT CASE WHEN parent IS NULL THEN '1171291314642'
END FROM table WHERE ProjID = '88'),
..
 WHERE ProjID = '88';

(My CASE clause syntax is probably incorrect, but you get the idea...
there are also  IFNULL and NOTNULL operators). But why? It will be so
convoluted and messy.

Others probably have better ideas.

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

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





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



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

2007-02-08 Thread Clark Christensen
WOW.  Good to know.  Thanks Jim (and Matt).

 -Clark

- Original Message 
From: "Anderson, James H (IT)" <[EMAIL PROTECTED]>
To: Matt Sergeant <[EMAIL PROTECTED]>
Cc: sqlite-users@sqlite.org
Sent: Thursday, February 8, 2007 8:36:31 AM
Subject: RE: [sqlite] What does this mean???

Looks like removing the semi-colon did the trick. Thanks very much. 

-Original Message-
From: Matt Sergeant [mailto:[EMAIL PROTECTED] 
Sent: Thursday, February 08, 2007 11:09 AM
To: Anderson, James H (IT)
Cc: sqlite-users@sqlite.org
Subject: Re: [sqlite] What does this mean???

On 8-Feb-07, at 11:04 AM, Anderson, James H ((IT)) wrote:

> The output after setting $dbh->trace(3):

Ah. Please re-try after taking the semi-colon off the end of your SQL.


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

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





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



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

2007-02-07 Thread Clark Christensen
Jim,

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

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

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


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

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

 -Clark

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

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

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

which produced:

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

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

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


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

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

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

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

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

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


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


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


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

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





-
To unsubscribe, send email to [EMAIL PROTECTED]

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

2007-02-07 Thread Clark Christensen
What version of SQLite, and what version of DBD-SQLite?

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

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

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

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

 -Clark

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

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

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

Thanks,

jim


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




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



Re: [sqlite] Appropriate uses for SQLite

2007-02-02 Thread Clark Christensen
FWIW, I'm not convinced Samba has locking working correctly.  Using a very 
recent Samba version, I managed to corrupt a SQLite database last fall by (I 
think) doing simultaneous writes from the Linux host box, and my WinXP client 
box (via a SMB drive map).  I'm guessing the XP writes started first.  It seems 
unlikely it would have happened had the Linux host started first.

Maybe it was something else, but given the explanations I've seen of what's 
known to corrupt a SQLite DB, it seems pretty likely it was a locking issue 
with a network file system.

Fortunately, it was early in the dev process on that project, and there wasn't 
much data in the DB, and I was able to recover it with a text editor.

That I didn't have a copy of the data outside the DB points to a weakness in my 
development habits.  Gotta work on that.

 -Clark

- Original Message 
From: Dennis Cote <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Friday, February 2, 2007 7:49:55 AM
Subject: Re: [sqlite] Appropriate uses for SQLite

[EMAIL PROTECTED] wrote:
> The problem is, not many network filesystems work correctly.  
Hi All,

If my understanding of this is correct, SQLite only requires that the 
network file system has; reliable data transport and working file 
locking. This has primarily been an issue on older implementations of 
NFS where the file locking does not work correctly. I believe that SMB 
(windows shares and Samba) do work correctly. I also seem to remember 
someone saying that current versions of NFS work correctly. It seems 
like overkill to say SQLite won't work an all network file systems, when 
in fact it will work on some, perhaps even most.

I appreciate that there are still network latency issues that will 
impact performance, but that is a separate issue. Many users could 
accept the lower performance as long as they don't have to worry about 
corruption.

I'm sure someone knows which versions of NFS have working file locking, 
at least under Linux. Perhaps we can collect this information about 
network file systems that are known to work and those that are known to 
have problems and post them on the wiki. Right now this  issue seems to 
be handled more as folklore than science.

So, what works and what doesn't? Let me know.

Dennis Cote


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





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



Re: [sqlite] NULL always greater?

2007-01-31 Thread Clark Christensen
Dennis,

Thanks for the timely reply.  

max(coalesce(col1, 0), coalesce(col2, 0))

is a lot cleaner than the 

max(
case when col1 is null then 0 else col1 end, 
case when col2 is null then 0 else col2 end
   )

solution I came up with.  Though the performance seems to be about the same.

The "coalesce" word hasn't been in my vocabulary, so it has no meaning for me.  
I'll have to read-up :-))

 -Clark

- Original Message 
From: Dennis Cote <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, January 31, 2007 11:40:02 AM
Subject: Re: [sqlite] NULL always greater?

Clark Christensen wrote:
> I've read through numerous discussions here about comparing values with null, 
> and how SQLite functions work with null values, and I thought I understood.
>
> Now it seems appropriate to use the max(col1, col2) function to find the 
> latest of two dates (integer Unix times), and some rows will contain null in 
> one column or the other.  But, max() always returns null when one of its args 
> is null.  That just seems backwards :-))
>
> FWIW, I'm on 3.3.12 on both Windows and Linux.
>
> Any help is appreciated.
>
>   
Clark,

You must reassign the value used for the comparison if it is null.

The coalesce function will return the first non null value in its 
arguments, and this may be all you need. If you only want the value from 
col2 if col1 is null then simply use

coalesce(col1, col2)

You will only get a null result if both columns are null.

If you really wan the max of the two columns you can use coalesc to 
convert nulls into zeros for the max function.

max(coalesce(col1, 0), coalesce(col2, 0))

This will give a result of zero if both columns are null.

HTH
Dennis Cote

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





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



Re: [sqlite] NULL always greater?

2007-01-31 Thread Clark Christensen
Aah, perfect.  Thanks for the pointer.

 -Clark

- Original Message 
From: Dan Kennedy <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, January 30, 2007 10:49:34 PM
Subject: Re: [sqlite] NULL always greater?

The basic rule is that the result of any comparison involving a
NULL value (including comparing against another NULL) is NULL.

See the following for the details:

http://www.sqlite.org/nulls.html

Dan.


On Tue, 2007-01-30 at 16:41 -0800, Clark Christensen wrote:
> I've read through numerous discussions here about comparing values with null, 
> and how SQLite functions work with null values, and I thought I understood.
> 
> Now it seems appropriate to use the max(col1, col2) function to find the 
> latest of two dates (integer Unix times), and some rows will contain null in 
> one column or the other.  But, max() always returns null when one of its args 
> is null.  That just seems backwards :-))
> 
> FWIW, I'm on 3.3.12 on both Windows and Linux.
> 
> Any help is appreciated.
> 
> Thanks!
> 
>  -Clark
> 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


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





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



[sqlite] NULL always greater?

2007-01-30 Thread Clark Christensen
I've read through numerous discussions here about comparing values with null, 
and how SQLite functions work with null values, and I thought I understood.

Now it seems appropriate to use the max(col1, col2) function to find the latest 
of two dates (integer Unix times), and some rows will contain null in one 
column or the other.  But, max() always returns null when one of its args is 
null.  That just seems backwards :-))

FWIW, I'm on 3.3.12 on both Windows and Linux.

Any help is appreciated.

Thanks!

 -Clark



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



Re: [sqlite] SQLite documentation

2007-01-29 Thread Clark Christensen
Puneet,

How about "make doc"?  If you have TCL, that seems to generate the HTML output 
in ./doc.  If you don't, I'd be happy to send it to you.

 -Clark

- Original Message 
From: P Kishor <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, January 29, 2007 10:13:05 AM
Subject: [sqlite] SQLite documentation

Isn't there a way to generate html documentation for SQLite from the
source files? I have a www folder with a bunch of Tcl files (which
seem like I need to run them through Tcl to get html out), but no
html. I am missing something obvious, so please point me in the right
direction.

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

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





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



Re: [sqlite] enforcing Foreign Keys

2007-01-29 Thread Clark Christensen
To unsubscribe, send email to [EMAIL PROTECTED]

- Original Message 
From: Mag Gam <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Saturday, January 27, 2007 9:13:11 AM
Subject: Re: [sqlite] enforcing Foreign Keys

So...anyone?


On 1/25/07, Martin Jenkins <[EMAIL PROTECTED]> wrote:
>
> Dan McDaniel wrote:
> > Can someone tell me how to unsubscribe. I have sent
> > two messages to the link and have had no luck thank
> > you.
> > --- Mag Gam <[EMAIL PROTECTED]> wrote:
> >
> Send a mail to [EMAIL PROTECTED] - it has directions on how
> to unsubscribe if the standard method fails. I just tried it and
> (assuming all the timestamps are correct) got a response back in a
> fraction over a minute.
>
> Martin
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>




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



Re: [sqlite] Restricting integer primary key values

2007-01-15 Thread Clark Christensen
Brett,

Have a look at http://www.sqlite.org/cvstrac/tktview?tn=1476  There may still 
be some custom function work to do for the OP's app, but this idea was a great 
start for me in implementing/managing a foreign sequence.  Not sure if his 
wrapper supports custom SQL functions.

 -Clark

- Original Message 
From: w b <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, January 15, 2007 11:41:39 AM
Subject: RE: [sqlite] Restricting integer primary key values


Hi Brett,

I dont think that there is a way within Sqlite to automagically do what 
you are looking for. I know that within Oracle they have the ability 
for you to define a sequence which allows lower and upper bounds to be 
defined as well as if the sequence can loop, which sounds like what you could 
be looking for.

However typically even within Oracle you then use the value obtained from 
the sequence value into your insert statement. 

This however is not automatic and would still require you to 
check the bounds conditions, since a declared sequence can be used for 
anything you really like and is not tied to a specific primary key within a 
specific table. 

In the case of sqlite you would probably need to create a C function to do that 
your self.

Dr H, I was wondering if it would be worth considering exposing the 
ability for a user to define a function that could override the auto 
increment abilities of the primary key, or would that open up a can of worms 
?



Wayne


Brett Keating <[EMAIL PROTECTED]> wrote: Hi,

This does indeed work, but only to prevent primary keys from being
inserted into the database if they fall out of range.

What I was hoping was that the with this command, the sqlite3 would
always automatically choose primary keys that fell within this range.
However with this command, the sqlite3 will eventually automatically
choose a primary key outside of this range (I tested with a range of 100
to 1000), and fail to insert the new item based on the check clause.

I tried to repeat the statement hoping that a new primary key would be
generated and eventually the insertion would succeed, but this ended up
causing an infinite loop (I think the key once chosen by the automated
algorithm remains the same for repeated attempts).

Are there any alternatives? I want automatic key generation that is
restricted to a range, and I don't want failures unless all possible
values are taken. I will continue trying other things in the meantime.

Thanks,
Brett

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Sunday, January 14, 2007 6:09 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Restricting integer primary key values

"Brett Keating"  wrote:
> Hi,
> 
> I don't want to spam the list but basically, if "id INTEGER PRIMARY 
> KEY CHECK (id > 0 and id < 0x)" or something along those lines

> will work, please let me know. It's not clear if sqlite3 supports 
> checks on primary keys from what I can tell.
> 

SQLite does not support hexadecimal constants.  Use
4294967295 instead of 0x and it should work.
--
D. Richard Hipp  



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




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






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



Re: [sqlite] calculate age

2006-12-23 Thread Clark Christensen
I see Microsoft is already offering a patch for Windows XP to handle the new 
U.S. DST rules.

 -Clark

- Original Message 
From: Joe Wilson <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Saturday, December 23, 2006 9:52:02 AM
Subject: Re: [sqlite] calculate age

Holiday determination per country (or even per state/city) via an algorithm
can only get you so far because it is at the whim of constantly changing
local laws. You basically need a database of all the dates for the exceptions. 
Even getting a reliable source of such information for various countries is 
problematic.

On a somewhat related topic, I wonder how many computer systems will be 
affected 
by the new US daylight savings time changes going into effect in 2007.
What a mess. Son of Y2K - Y2K7DST.

--- Jay Sprenkle <[EMAIL PROTECTED]> wrote:
> LOL! You should look at a function to determine if a day is a holiday.
> Talk about ugly! In some places you literally need to know the weather
> and the phase of the moon!


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

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





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



Re: [sqlite] REPLACE INTO Only Executes last SELECT?

2006-11-30 Thread Clark Christensen
DOH!  I stand corrected.  Guess I should RTFM before I speak :-))

Thanks!

 -Clark

- Original Message 
From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, November 30, 2006 4:52:25 PM
Subject: Re: [sqlite] REPLACE INTO Only Executes last SELECT?

Clark Christensen <[EMAIL PROTECTED]> wrote:
> I don't think SQLite supports "REPLACE INTO..."  I'm pretty sure "REPLACE" =
> is a conflict action in SQLite.  
> 
> Perhaps "INSERT OR REPLACE INTO Memb=
> erAccounts (MemberId, Balance) SELECT..." will do what you want?
> 

I went to the extra trouble of making REPLACE INTO an
alias for INSERT OR REPLACE INTO for compatibility with
MySQL.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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





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



Re: [sqlite] REPLACE INTO Only Executes last SELECT?

2006-11-30 Thread Clark Christensen
I don't think SQLite supports "REPLACE INTO..."  I'm pretty sure "REPLACE" is a 
conflict action in SQLite.  

Perhaps "INSERT OR REPLACE INTO MemberAccounts (MemberId, Balance) SELECT..." 
will do what you want?

 -Clark

- Original Message 
From: Cnichols <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, November 30, 2006 2:22:19 PM
Subject: [sqlite] REPLACE INTO Only Executes last SELECT?


I have a complex SQL statement which attempts to update all Member Accounts
balance with a discount if there anniversary falls between last handled date
and the current day.


REPLACE INTO MemberAccounts (MemberId, Balance)

SELECT DA.MemberId, (MA.Balance - D.Amount) AS Amount
FROM DiscountsApplied AS DA
LEFT JOIN Discounts AS D ON DA.DiscountId = D.Id
LEFT JOIN Members AS M ON DA.MemberId = M.Id
LEFT JOIN MemberAccounts AS MA ON DA.MemberId = MA.MemberId
WHERE
DATE(strftime('0001-%m-%d', M.Registered)) > DATE(strftime('0001-%m-%d',
(SELECT RegistrateDate FROM Config)))
AND
DATE(strftime('0001-%m-%d', M.Registered)) <= DATE(strftime('0001-%m-%d',
'now'))
AND
D.Type = 1 AND D.Modifier = 1


The SELECT statement will return multiple rows.  A member may have more than
one discount that can be applied.  When this case occurs the last row for
that member is the only one that actually executes.  The rows before that
are not replacing the value of BALANCE only the last row for that member
will affect the balance.

Is there a way I can accumalate the discounts for a member and apply it to
their balance with an SQL statement?  Or will this have to be done
programmtically with SELECT and then nested in a transaction UPDATES?
-- 
View this message in context: 
http://www.nabble.com/REPLACE-INTO-Only-Executes-last-SELECT--tf2734721.html#a7629298
Sent from the SQLite mailing list archive at Nabble.com.


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





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



Re: [sqlite] Importing text file via .bat file

2006-11-16 Thread Clark Christensen
Aah, I see "Shell" means something different to you than to me.  I'm sorry, I 
was thinking SQLite shell.  I can't help with any specific VB examples.

 -Clark

- Original Message 
From: RB Smissaert <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, November 15, 2006 5:11:38 PM
Subject: RE: [sqlite] Importing text file via .bat file

Not sure if Shell can do something like that.
What would the VB code be?

RBS

-Original Message-----
From: Clark Christensen [mailto:[EMAIL PROTECTED] 
Sent: 15 November 2006 23:53
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Importing text file via .bat file

Shell

- Original Message 
From: RB Smissaert <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, November 15, 2006 2:16:32 PM
Subject: RE: [sqlite] Importing text file via .bat file

>   sqlite3 c:\sqlite\ReadCode.db ".read c:\sqlite\ReadCode.sql"

Not sure how that would work from VBA. Did you mean to run this with Shell
or the Windows API?

RBS


-----Original Message-
From: Clark Christensen [mailto:[EMAIL PROTECTED] 
Sent: 15 November 2006 21:37
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Importing text file via .bat file

RBS,

Sorry to jump in late here.  Others have given good advice, but I'm
wondering, since this is all running from VB, why not do all the work in VB
and skip the batch (or cmd) file.  I'm not a VB guy, but I do know it's
pretty powerful.

Are you having some trouble with a VB wrapper for SQLite?  If no, then what
you propose should be as simple as iterating through the IB recordset and
inserting what you need into your SQLite table.  If you are having trouble
with a wrapper, then it seems to me like VB can (and should) do everything
except the actual import.

If you create your SQL script as:

--ReadCode.sql to build and populate ReadCode.db
drop table if exists ReadCode;
create table ReadCode
  (
SUBJECT_TYPE   varchar(5),
READ_CODE   varchar(5),
TERM30   varchar(30),
TERM60  varchar(60)
  );
.mode csv
.import c:\sqlite\ReadCode.txt ReadCode
--END SQL

Then, from VB, you issue a single command like:

sqlite3 c:\sqlite\ReadCode.db ".read c:\sqlite\ReadCode.sql"

and wait for SQLite to finish (or read the exit code, or read SQLite's
stdout output).  If it's a success, there'll be no output from SQLite.

If what you really want is to have one single SQL file to do the job, you
would have your "Recordset to text" step write out each row as an insert
statement into ReadCode.sql, so ReadCode.sql would then look like:

--ReadCode.sql to build and populate ReadCode.db

drop table if exists ReadCode;

create table ReadCode

  (

SUBJECT_TYPE   varchar(5),

READ_CODE   varchar(5),

TERM30   varchar(30),

TERM60  varchar(60)

  );

begin transaction;
insert into ReadCode values (...);
insert into ReadCode values (...);
insert into ReadCode values (...);
...
commit;
--END SQL

Then issue the same command from VB to start the job:

sqlite3 c:\sqlite\ReadCode.db ".read c:\sqlite\ReadCode.sql"

Either way, you would be able to eliminate the batch file, and handle
everything from within VB.

 -Clark
- Original Message 
From: RB Smissaert <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, November 14, 2006 3:44:12 PM
Subject: [sqlite] Importing text file via .bat file

Have figure out now what the quickest way is to move data from Interbase to
a SQLite db file:
IB to ADO recordset
Recordset to text
Import the text file with the .import command.

Now I am trying to figure out how to automate the last step with a .bat
file.
What I got sofar is:
Have a SQL file with:

create table ReadCode
  (
SUBJECT_TYPE   varchar(5),
READ_CODE   varchar(5),
TERM30   varchar(30),
TERM60  varchar(60)
  );

Run a .bat file with this:

cd C:\SQLite
del ReadCode.db
type ReadCode.sql | sqlite3 ReadCode.db

Then run from the command prompt:

Cd C:\SQLite  (press return)
SQLite3 ReadCode.db  (press return)
.mode csv(press return)
.import ReadCode.txt ReadCode   (press return)

This runs nice and quick, but how would I combine all this in one .bat file
or how could I run this all from VB? I know very little about .bat files,
but I would think that somehow it must be possible.
Thanks for any assistance.


RBS





-
To unsubscribe, send email to [EMAIL PROTECTED]

-






-
To unsubscribe, send email to [EMAIL PROTECTED]

-




--

Re: [sqlite] Importing text file via .bat file

2006-11-15 Thread Clark Christensen
RBS,

Sorry to jump in late here.  Others have given good advice, but I'm wondering, 
since this is all running from VB, why not do all the work in VB and skip the 
batch (or cmd) file.  I'm not a VB guy, but I do know it's pretty powerful.

Are you having some trouble with a VB wrapper for SQLite?  If no, then what you 
propose should be as simple as iterating through the IB recordset and inserting 
what you need into your SQLite table.  If you are having trouble with a 
wrapper, then it seems to me like VB can (and should) do everything except the 
actual import.

If you create your SQL script as:

--ReadCode.sql to build and populate ReadCode.db
drop table if exists ReadCode;
create table ReadCode
  (
SUBJECT_TYPE   varchar(5),
READ_CODE   varchar(5),
TERM30   varchar(30),
TERM60  varchar(60)
  );
.mode csv
.import c:\sqlite\ReadCode.txt ReadCode
--END SQL

Then, from VB, you issue a single command like:

sqlite3 c:\sqlite\ReadCode.db ".read c:\sqlite\ReadCode.sql"

and wait for SQLite to finish (or read the exit code, or read SQLite's stdout 
output).  If it's a success, there'll be no output from SQLite.

If what you really want is to have one single SQL file to do the job, you would 
have your "Recordset to text" step write out each row as an insert statement 
into ReadCode.sql, so ReadCode.sql would then look like:

--ReadCode.sql to build and populate ReadCode.db

drop table if exists ReadCode;

create table ReadCode

  (

SUBJECT_TYPE   varchar(5),

READ_CODE   varchar(5),

TERM30   varchar(30),

TERM60  varchar(60)

  );

begin transaction;
insert into ReadCode values (...);
insert into ReadCode values (...);
insert into ReadCode values (...);
...
commit;
--END SQL

Then issue the same command from VB to start the job:

sqlite3 c:\sqlite\ReadCode.db ".read c:\sqlite\ReadCode.sql"

Either way, you would be able to eliminate the batch file, and handle 
everything from within VB.

 -Clark
- Original Message 
From: RB Smissaert <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, November 14, 2006 3:44:12 PM
Subject: [sqlite] Importing text file via .bat file

Have figure out now what the quickest way is to move data from Interbase to
a SQLite db file:
IB to ADO recordset
Recordset to text
Import the text file with the .import command.

Now I am trying to figure out how to automate the last step with a .bat
file.
What I got sofar is:
Have a SQL file with:

create table ReadCode
  (
SUBJECT_TYPE   varchar(5),
READ_CODE   varchar(5),
TERM30   varchar(30),
TERM60  varchar(60)
  );

Run a .bat file with this:

cd C:\SQLite
del ReadCode.db
type ReadCode.sql | sqlite3 ReadCode.db

Then run from the command prompt:

Cd C:\SQLite  (press return)
SQLite3 ReadCode.db  (press return)
.mode csv(press return)
.import ReadCode.txt ReadCode   (press return)

This runs nice and quick, but how would I combine all this in one .bat file
or how could I run this all from VB? I know very little about .bat files,
but I would think that somehow it must be possible.
Thanks for any assistance.


RBS




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





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



Re: [sqlite] Q about new SQLite API

2006-11-07 Thread Clark Christensen
Q1:  sqlite3_prepare_ex
Q3: SQLITE_SCHEMA

I don't currently use the APIs directly (though I have a project in mind), but 
these seem to make the most sense.

 -Clark

- Original Message 
From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, November 7, 2006 5:17:37 AM
Subject: [sqlite] Q about new SQLite API

I'm working on a new API routine for SQLite and I have
questions for the community.

The working name of the new api is sqlite3_prepare_v2().
sqlite3_prepare_v2() works like sqlite3_prepare() in that
it generates a prepared statement in an sqlite3_stmt
structure.  The differences is in the behavior of the
resulting sqlite3_stmt and in particular a difference in
the way sqlite3_step() responds to the sqlite3_stmt.  The
differences are these:

  * You never get an SQLITE_SCHEMA error.  sqlite3_prepare_v2
retains the original SQL and automatically reprepares and
rebinds it following a schema change.

  * sqlite3_step() returns the correct error code right
away, rather than just returning SQLITE_ERROR and making
you call sqlite3_reset() to find the true reason for the
error.

In this way, I am hoping that sqlite3_prepare_v2() will work
around two of the most visible warts in the current API.

QUESTION 1: sqlite3_prepare_v2 is the merely the working name
for the new function.  What should the official name be?
Some possibilities include:

sqlite3_prepare_ex1
sqlite3_prepare_ng
sqlite3_new_prepare
sqlite3_compile

QUESTION 2: Are there any other API warts that need to be
worked around that can be fixed by this same change?

QUESTION 3: Suppose there is a schema change and the SQL
statement is automatically reprepared. But the schema change
is such that the SQL is no longer valid.  (Perhaps one of the
tables mentioned in a SELECT statement was dropped.) What
error code should sqlite3_step() return in that case?

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


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





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



Re: [sqlite] Date data type

2006-11-07 Thread Clark Christensen
IMO, dates are a pain.  I spent considerable time trying to decide how best to 
store dates in my app(s), and eventually chose to use Unix times (integers).  
It seemed an easy choice as I program in Perl and JavaScript.

Lately, I've begun to regret the choice I made.  Every ad-hoc query I need to 
do (select * from mytable...) becomes an exercise in using SQLite date 
functions.  If I had it to do over, I would probably store my datetimes as 
-MM-DD HH:MM:SS strings.

I thought about storing as julians, too, but it's the same issue when you need 
human-readable dates in ad-hoc queries.

YMMV.  My apps are all web-based, and I frequently need to look at stored data 
using ad-hoc queries.

 -Clark

- Original Message 
From: Lloyd <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, November 7, 2006 2:30:34 AM
Subject: Re: [sqlite] Date data type

Thanks gg and Craig Morrison for your informative reply.

I would like to know one more thing, can I use these date and time
functions with comparison operators? Will they return the correct result
or as per the string comparison rules?

Thanks again,
  Lloyd.

On Tue, 2006-11-07 at 04:25 -0500, Craig Morrison wrote:
> Lloyd wrote:
> > Hi,
> >   How can I manage date and time using sqlite? [Do I have to do it
> > outside sqlite?]
> 
> http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
> 


__
Scanned and protected by Email scanner

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





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



Re: [sqlite] SQLite and McAfee Anti-Virus

2006-10-31 Thread Clark Christensen
With the bigger companies, like McAfee, the phone support people are often not 
employees at the companies they represent.  Phone support these days is largely 
outsourced.

The first level tech you get usually does triage from a script.  If you get to 
a second level (or higher) tech, they are more likely to actually do some work. 
 And it depends a LOT on the individual you get.

I'm certain McAfee outsources their support.

 -Clark


- Original Message 
From: Clay Dowling <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Tuesday, October 31, 2006 1:41:36 PM
Subject: Re: [sqlite] SQLite and McAfee Anti-Virus

Actually, just by following the links on your web site I was able to find
a forum post from a McAfee employee suggesting that they were going to
change the naming of the temp files, or were at least considering it. 
Check the last message in the forum posting that you have linked from the
wiki page about the McAfeeBug.

One of the chief problems that you're running into is that people in
general can't or won't troubleshoot problems to determine a root cause
(I'm certainly guilty of this).  Additionally, the McAfee phone support
people don't seem to be aware that their program is generating these
files, probably because any poor soul stuck on phone support is fairly new
to the company and the product.  Phone support in any company has a high
turnover.

Clay Dowling


[EMAIL PROTECTED] said:
> I need advice from the community.  The problem
> is seen here:
>
>   http://www.sqlite.org/cvstrac/tktview?tn=2049
>   http://www.sqlite.org/cvstrac/tktview?tn=1989
>   http://www.sqlite.org/cvstrac/tktview?tn=1841
>   http://www.sqlite.org/cvstrac/wiki?p=McafeeProblem
>
> It appears that McAfee Anti-Virus uses SQLite internally,
> and it leaves lots of files in C:/TEMP that contain
> SQLite in their names.  This annoys many windows users.
> They get on Google and search around for "sqlite" and
> find me.  Then they send me private email or call me at
> my office or on my cellphone to complain.  Many refuse
> to believe that I have nothing to do with the problem
> and I am accused of spreading a virus or malware.
>
> My efforts to contact Mcafee about this problem have
> been unfruitful.
>
> Does anybody have an suggestions on how I might deal
> with this?  Does anybody know how I can get in touch
> with an engineer at Mcafee so that we can at least
> change the names of the files in future releases?
>
> --
> D. Richard Hipp  <[EMAIL PROTECTED]>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>


-- 
Simple Content Management
http://www.ceamus.com


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





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



Re: [sqlite] Re: Re: Re: sqlite3_prepare16 and LIKE

2006-10-30 Thread Clark Christensen
Possible typo in the first sub condition of your WHERE clause.  I'm sure you 
mean to say, "Data.title LIKE ",  instead of "Data.titleLIKE".

Also, I don't think SQLite lets you write a parameterized query like this.  I 
think you have to concatenate the percents and your input string and pass the 
concatenated string to sqlite3_bind_text16().

So, where query = "madonna", and path = "C:\MP3\Albums" you'd set 
inquery="%madonna%", and inpath = "%C:\MP3\Albums%" then I might write the 
query like:

SELECT 
*
FROM 
Data 
WHERE
( Data.title LIKE ?
OR Data.artist LIKE ?
OR Data.album LIKE ?
OR Data.genre LIKE ?
OR Data.comment LIKE ?
OR Data.path LIKE ? )
AND Data.path LIKE ?;

and bind inquery and inpath.  As others have pointed out, you might need to 
escape the backslashes in path.

 -Clark

- Original Message 
From: Jonas Sandman <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, October 30, 2006 1:31:02 PM
Subject: Re: [sqlite] Re: Re: Re: sqlite3_prepare16 and LIKE

#define SELECT_STATEMENT_TEXTSORT L"SELECT * FROM Data WHERE
(Data.titleLIKE (SELECT '%%' || ? || '%') OR
Data.artist LIKE (SELECT '%' || ? || '%') OR Data.album LIKE (SELECT '%' ||
? || '%') OR Data.genre LIKE (SELECT '%' || ? || '%') OR Data.comment LIKE
(SELECT '%' || ? || '%') OR Data.path LIKE (SELECT '%' || ? '%')) AND
Data.path LIKE (SELECT '%' || ? || '%');"

ERR = sqlite3_prepare16(m_db, SELECT_STATEMENT_TEXTSORT, -1, ,
(const void**));

wchar_t* errmsg = (wchar_t*) sqlite3_errmsg16(m_db);

sqlite3_bind_text16(exc, 1, query, -1, SQLITE_STATIC);
sqlite3_bind_text16(exc, 2, query, -1, SQLITE_STATIC);
sqlite3_bind_text16(exc, 3, query, -1, SQLITE_STATIC);
sqlite3_bind_text16(exc, 4, query, -1, SQLITE_STATIC);
sqlite3_bind_text16(exc, 5, query, -1, SQLITE_STATIC);
sqlite3_bind_text16(exc, 6, path, -1, SQLITE_STATIC);

while ((ERR = sqlite3_step(exc)) == SQLITE_ROW)
{
 // process data here like:
// df->SetFileName((wchar_t*)sqlite3_column_text16(exc, 0));
}


path and query are wchar_t variables.

Jonas

On 10/30/06, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
>
> Jonas Sandman <[EMAIL PROTECTED]>
> wrote:
> > Yes, but path isn't part of the query. I ask for query = "", path =
> > "C:\MP3\Albums" and I get all the results in the entire database. If
> > I set query = "madonna" and same path. I get no results.
> >
> >> Use parentheses to achieve this result.
> >
> > Okay, that could've been it. But when I put that parenthesis in
> > there, I get no results ever. No matter of the query.
>
> Right. A condition on path is mandatory now, whereas before it could be
> false but you would still get results. This tells us that there's
> something wrong with the value you bind to path parameter.
>
> When you say "C:\MP3\Albums", are you literally hardcoding this string?
> If so, are you aware that backslashes should be escaped in C string
> literal?
>
> Show the actual code that calls sqlite3_bind_text[16] . There's
> something wrong there.
>
> Igor Tandetnik
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>




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



Re: [sqlite] The term "flat-file" as applied to sqlite

2006-09-25 Thread Clark Christensen
In a project team I was on recently, the PM, and some other team members seemed 
to think "database" meant either Access, or client-server (Oracle/MS SQL 
Server).  They kept wanting to "access the database directly".  I ended-up 
telling them if they think of this database as if it were a "flat file", they'd 
have a better understanding of why they couldn't just connect to it (it's 
isolated on a Webserver).  Everybody seemed to accept it without further 
challenge.

At least in this case, that over-simplification worked just fine ;-)

 -Clark

- Original Message 
From: Fred Williams <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, September 25, 2006 2:13:10 PM
Subject: RE: [sqlite] The term "flat-file" as applied to sqlite

Unless you have a very funny shaped disk drive all the files are "Flat"
:-)

I tend to agree stating SQLite is a "Flat file" somewhat oversimplifies
things to me as my definition of a "flat file" is a simple sequential
set of data written in no particular order and retrievable only by
opening the file and reading sequentially from start to finish to seek
out a particular data item.

Fred

> -Original Message-
> From: Griggs, Donald [mailto:[EMAIL PROTECTED]
> Sent: Monday, September 25, 2006 3:07 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] The term "flat-file" as applied to sqlite
>
>
>
> I've noticed that more than one contributor to this list has
> referred to
> sqlite as a "flat file database."  I had always thought of a
> flat file as a
> file composed of single table of records, with records
> defined either by
> fixed-width allocations or by some sort of delimiter (e.g.,
> comma-separated
> files).
>
> The article below seems to agree, though a more "broad"
> definition would
> also include simple tables with no relationships.
>
> http://en.wikipedia.org/wiki/Flat-file
>
> By either definition, it would seem that an sqlite database
> file is far, far
> from flat.
>
> My purpose is not to argue terms for their own sake, but instead:
>1) If I'm correct, then it could really confuse those new
> to sqlite into
> thinking it very different from the relational, b-tree
> indexed database that
> it is, or
>2) If instead, I'm unaware of another popular use of the
> term "flat file"
> -- then I'd be glad to learn this.
>
>
>
> [Opinions are my own, not those of my company]
>
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
>


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





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



Re: [sqlite] Re: Re: Cannot Find Syntax Error -- SOLVED!

2006-09-21 Thread Clark Christensen
>From the command line, you can use

sqlite3 foo.db ".read load_hedges.sql"

 -Clark

- Original Message 
From: Rich Shepard <[EMAIL PROTECTED]>
To: SQLite 
Sent: Thursday, September 21, 2006 9:25:02 AM
Subject: Re: [sqlite] Re: Re: Cannot Find Syntax Error -- SOLVED!

On Thu, 21 Sep 2006, Igor Tandetnik wrote:

> Check that this existing table does indeed have the columns you think it has. 
> Try running this from the shell:

Igor,

   I found the problem ... at least, from within the sqlite3 shell. I was
using the incorrect tool. :-(

   Since the file contained valid SQL statements, I need to use
sqlite> .read load_hedges.sql

rather than .import 

   Makes all the difference!

   Now, why it's not working from the command line I don't know. But, within
the sqlite shell, I need the .read command.

Thanks,

Rich

-- 
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.(TM)|Accelerator
 Voice: 503-667-4517  Fax: 503-667-8863

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





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



Re: [sqlite] List of web hosting providers who provide/support SQLite?

2006-08-25 Thread Clark Christensen
I've been thinking about that myself.  Then I started to wonder if the more 
common MySQL or PostgreSQL wouldn't be just as good (or better) for websites - 
particularly remote-hosted ones.

I see there are a lot of hosting companies out there that offer the traditional 
LAMP stuff, as well as CPanel or other admin tools to help you administer your 
site and your DBs.  I signed-up with Vizaweb for my condo association's site.  
They offer Linux, Apache, MySQL, PG, PHP, Perl, and a bunch of other stuff.  
Though they've expressed their openness to installing more stuff, I think if I 
were to do a DB-backed web app there, I'd probably just go with the My or PG db 
platform they offer.

 -Clark

- Original Message 
From: Louis P. Santillan <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Friday, August 25, 2006 12:33:36 PM
Subject: [sqlite] List of web hosting providers who provide/support SQLite?

This is probably a good topic for a wiki page...

Does anybody have a list (actually a matrix would be
even better) of web hosting providers who have SQLite
available to their customers?  I decided to look for
some cheap web hosting service for a personal project
using LASP (Linux, Apache, SQLite, PHP) and realized a
list of providers would probably be ideal.  A matrix
of common features would be good also (Windows, Linux,
Apache, IIS, PHP, Perl, Python, Price, etc.)

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

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





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



Re: [sqlite] running a script?

2006-08-23 Thread Clark Christensen
It should work fine with filenames with semicolons.  My problem is when I 
include the trailing semicolon, and it isn't really part of the filename or 
table name.

You _might_ need to use forward slashes instead of backslashes as the path 
separator inside the SQLite shell on Windows.  Or you might need to use double 
backslashes.  I remember having an issue like this, but I don't remember the 
details.  I've just started putting the files in the default dir.

 -Clark


- Original Message 
From: John Salerno <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
Sent: Wednesday, August 23, 2006 1:22:03 PM
Subject: Re: [sqlite] running a script?

Thanks guys. Does any of this change for Windows though? I don't
believe I can use cat or | in the command prompt. I've also tried some
of those structures already and there seems to be a problem with my
file paths: C:\name\name\file.ext

I don't know if it's the colon or the slashes, but it won't work properly.

And about using .read and .import, does this mean you can't use them
on files that have semicolons in them?



On 8/23/06, Ulrich Schöbel <[EMAIL PROTECTED]> wrote:
> On Wednesday 23 August 2006 21:45, John Salerno wrote:
> > Hi everyone. Can someone tell me the proper syntax for running a sql
> > script when starting up sqlite from the command line interface?
> >
> > Thanks,
> > John
> >
> > ---
> >-- To unsubscribe, send email to [EMAIL PROTECTED]
> > ---
> >--
>
> cat scriptfile | sqlite3 mydb
>
> or
>
> sqlite3 -init scriptfile mydb
>
> or
>
> sqlite3 mydb < scriptfile
>
> or (for a single sql command)
>
> sqlite3 mydb 'sqlcmd'
>
> Kind regards
>
> Ulrich
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

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





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



Re: [sqlite] running a script?

2006-08-23 Thread Clark Christensen
>From the SQLite shell

.read myfile

Or, from the OS command shell

sqlite3 foo.db ".read myfile"

.help in the SQLite shell will give you the available commands

Note.  For me, it's a habit to end lines in the SQLite shell with a semicolon.  
That breaks the .read and .import commands because the SQLite shell includes 
the trailing semicolon as part of the file or table identifier.

 -Clark


- Original Message 
From: John Salerno <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, August 23, 2006 1:01:46 PM
Subject: Re: [sqlite] running a script?

I'm asking about an actual file, though, not just a single query. I've
tried something like what you suggest with the file path, but it
doesn't work.



On 8/23/06, Scott Baker <[EMAIL PROTECTED]> wrote:
> echo "SELECT * FROM Table" | sqlite database.bin
>
> John Salerno wrote:
> > Hi everyone. Can someone tell me the proper syntax for running a sql
> > script when starting up sqlite from the command line interface?
> >
> > Thanks,
> > John
> >
> > -
> >
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> >
> >
> >
> >
>
> --
> Scott Baker - RHCE
> Canby Telcom System Administrator
> 503.266.8253
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>

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





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



Re: [sqlite] Re: Re: Using Wrong Date Format

2006-08-18 Thread Clark Christensen
Duh!  Nice.  Sorry I missed it.

 -Clark


- Original Message 
From: Igor Tandetnik <[EMAIL PROTECTED]>
To: SQLite <sqlite-users@sqlite.org>
Sent: Friday, August 18, 2006 11:22:18 AM
Subject: [sqlite] Re: Re: Using Wrong Date Format

Clark Christensen 
wrote:
>> update tableName set
>> DOB=substr(DOB,7,4)||substr(DOB,3,4)||substr(DOB,1,2);
>
> Am I missing some magic here?  To me, this looks like it'll result in
> MMDD.

substr(DOB,3,4) extracts the day complete with surrounding dashes from 
the original string.

Igor Tandetnik 


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





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



Re: [sqlite] Re: Using Wrong Date Format

2006-08-18 Thread Clark Christensen
> update tableName set DOB=substr(DOB,7,4)||substr(DOB,3,4)||substr(DOB,1,2);

Am I missing some magic here?  To me, this looks like it'll result in MMDD. 
 Does SQLite convert MMDD date strings?



I would've gone with the original -MM-DD date string that Igor posted.

Thanks!

 -Clark


- Original Message 
From: Gerry Snyder <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Friday, August 18, 2006 5:54:22 AM
Subject: Re: [sqlite] Re: Using Wrong Date Format

Igor Tandetnik wrote:
> Eoin Collins <[EMAIL PROTECTED]>
> wrote:
>> The database I'm currently using has a field Date Of Birth, and all
>> enteries in are in dd-mm- format.
>>
>> I need them in -mm-dd format.
>
> update tableName set 
> DOB=substr(DOB,7,4)||'-'||substr(DOB,4,2)||'-'||substr(DOB,1,2);
>
One tiny simplification:  |'-'||substr(DOB,4,2)||'-'|  can be replaced 
by |substr(DOB,3,4)|  making the total command:

update tableName set DOB=substr(DOB,7,4)||substr(DOB,3,4)||substr(DOB,1,2);


Gerry






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



Re: [sqlite] Altering a table when field(column) names are unknown

2006-07-24 Thread Clark Christensen
I have had success using:

create temp table my_temp as select * from my_table;

Of course, if you don't know the column names, it might be a challenge getting 
the data back into the new (altered) table.

If all you need is to add a column, ALTER TABLE does a good job in later 
releases.  I'm on 3.2.7, and it's been very useful.  

ALTER TABLE also supports renaming a table, so when I have a more significant 
change to make, I've found it more useful to rename the original table, create 
the new table, select data from old to new, and drop the old.  But that 
requires knowing both the old and new schemas.

 -Clark


- Original Message 
From: John Newby <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, July 24, 2006 5:29:16 AM
Subject: [sqlite] Altering a table when field(column) names are unknown

http://www.sqlite.org/faq.html#q13

Hi, the above link goes to an FAQ on the SQLite website stating that the
ALTER TABLE command has limited functionality and recommends creating a temp
table and copying everything there, dropping the original table, re-creating
it with the desired changes and copying everything back from the temp table
and then dropping that.

This is all good when the fields(columns) are known in advance, but how
would I attempt doing this without knowing the name of the fields?

Any ideas?

Many thanks

John





  1   2   >