On Thu, 8 Oct 2009, Rich Shepard wrote:
> Oh, rats! I messed up the table when converting the dates. Guess I need
> to start the process over. Will report results when I'm done.
Amazing! When the dates are correct, and not all the same, the delete
statement works as intended on the table.
T
Excellent! You were on the mark; I am trying to fill the holes as
records are deleted.
Thanks Igor!
On Thu, 2009-10-08 at 19:45 -0400, Igor Tandetnik wrote:
> Bob Lauria wrote:
> > I have a table keyed by an integer column. Each time I insert a row
> > into the table I would like to use the lo
On Thu, 8 Oct 2009, Igor Tandetnik wrote:
> Well, do you actually have rows that you believe should satisfy the
> condition? Show one of those.
Oh, rats! I messed up the table when converting the dates. Guess I need to
start the process over. Will report results when I'm done.
Rich
__
Rich Shepard wrote:
> On Thu, 8 Oct 2009, Igor Tandetnik wrote:
>
>> Show what the data looks like now, show the statement you are
>> running, and define "not working".
>
> Igor,
>
> Here are two records:
>
> sqlite> select * from Penalties limit 2;
> 2009-071|Water Quality, Storm Water|NWR|
Bob Lauria wrote:
> I have a table keyed by an integer column. Each time I insert a row
> into the table I would like to use the lowest value that does not
> currently exist in the table as a key. Is there a query that will
> provide me with the lowest numeric value (for a column) that does not
On Thu, 8 Oct 2009, Igor Tandetnik wrote:
> Show what the data looks like now, show the statement you are running, and
> define "not working".
Igor,
Here are two records:
sqlite> select * from Penalties limit 2;
2009-071|Water Quality, Storm Water|NWR|205 Auto Salvage,
Inc.|Portland|Belete|s
Rich Shepard wrote:
> I changed the data type in the schema to VARCHAR and the format is
> now -MM-DD. However, it's still not working. What am I still doing
> incorrectly now?
Show what the data looks like now, show the statement you are running, and
define "not working".
Igor Tandetnik
On Thu, 8 Oct 2009, Pavel Ivanov wrote:
> Your dates are compared as simple strings. Thus with your statement you're
> trying to delete all rows where DateIssued is January, 1 of any year
> earlier than 2005. Bottom line: change the way you store your dates if you
> really want to compare them in
I have a table keyed by an integer column. Each time I insert a row
into the table I would like to use the lowest value that does not
currently exist in the table as a key. Is there a query that will
provide me with the lowest numeric value (for a column) that does not
exist in the table?
--
Bob
Simon Slavin
wrote:
> On 8 Oct 2009, at 9:35pm, Rich Shepard wrote:
>
>> A closer look tells me that the string format is incorrect for
>> SQL. It
>> needs to be -MM-DD rather than D/M/. That incorrect format
>> seems to
>> be the problem.
>
> It can be anything which sorts into the corr
On Thu, 8 Oct 2009, Igor Tandetnik wrote:
> Actually, columns with these declared types will have NUMERIC affinity.
Thanks, Igor. I missed that.
> Realize that -MM-DD format works not because SQLite treats it somehow
> specially, but because for strings in this format, alphabetical order
Rich Shepard wrote:
> On Thu, 8 Oct 2009, Pavel Ivanov wrote:
>
>> And the main problem: SQLite doesn't have such type as date. All
>> types it supports are listed here:
>> http://www.sqlite.org/datatype3.html.
>
> We can use DATE, TIME, and DATETIME column types; they all have TEXT
> storage cl
On 8 Oct 2009, at 9:35pm, Rich Shepard wrote:
> A closer look tells me that the string format is incorrect for
> SQL. It
> needs to be -MM-DD rather than D/M/. That incorrect format
> seems to
> be the problem.
It can be anything which sorts into the correct order when seen as a
On Thu, 8 Oct 2009, Pavel Ivanov wrote:
> And the main problem: SQLite doesn't have such type as date. All types
> it supports are listed here: http://www.sqlite.org/datatype3.html.
Pavel,
We can use DATE, TIME, and DATETIME column types; they all have TEXT
storage class.
> Bottom line: chan
> To delete all rows with dates earlier than 1/1/2005 I used the statement:
>
> DELETE FROM Penalties WHERE DateIssued < '1/1/2005';
>
> but it did not delete the records. Doesn't matter if I use single quotes,
> double quotes, or no quotes.
Just a note: when you tried it without quotes you trie
This must be my error, but I am not seeing it. Your input is requested.
I have a table named Penalties with a column named DateIssued and a
datatype of DATE. A select operation shows dates such as 4/6/1992 and
12/15/1993.
To delete all rows with dates earlier than 1/1/2005 I used the sta
Hello!
The problem can be solved by using Tcl_ConvertToType() function for
wideint and double values.
$ diff -u tclsqlite.c.old tclsqlite.c
===
--- tclsqlite.c.old 2009-09-05 00:37:43.0 +0400
+++
Hello!
But why do you not compress big text strings? And index
size can be reduced by using md5 hash of text key field.
See the extensions
http://mobigroup.ru/files/sqlite-ext/
http://mobigroup.ru/files/sqlite-ext/md5/
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
__
Jason Freshwater wrote:
> If this comes out garbled again the answer is java 1.6 on 1.8Ghz windows xp
>
> i get about 500,000 rows/second reading single character values via xerial
> jdbc
>
Thanks, I did some additional experiments with only integers and got
similar results. I had always bee
I think I may now understand the problem we're seeing. I left out a
very important piece of information: On the dbs we're seeing the
increase in insert time, the average row size is large - really large.
Basically we can see averages as high as 45k. This was causing overflow
chains to be as high
If this comes out garbled again the answer is java 1.6 on 1.8Ghz windows xp
i get about 500,000 rows/second reading single character values via xerial jdbc
> From: john.elr...@fenestra.com
> Jason Freshwater wrote:
> > 2. Read every record into my application and use a hashtable to
> > do my own
<4acdf22b.2080...@fenestra.com>
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
> From: john.elr...@fenestra.com
>> 2. Read every record into my application and use a hashtable to
>> do my own select distinct. This is pretty good=2C a
On Thu, Oct 08, 2009 at 08:29:10AM +0200, Fredrik Karlsson scratched on the
wall:
> Yes, that would have been my guess too, but I am on CET, which I
> understand is UTC+1.
The timestamp on your own email tells a different story:
Date: Thu, 8 Oct 2009 08:29:10 +0200
From: Fredrik Karlsson
Jason Freshwater wrote:
> 2. Read every record into my application and use a hashtable to
> do my own select distinct. This is pretty good, about 0.6s to
> achieve the distinct list (I can count each distinct value
> along the way which is a useful side effect).
Would you mind my asking what langu
> From: itandet...@mvps.org
> Jason Freshwater wrote:
> > For a 1 million row table with 8 distinct values of c the query
> > time on my system (pretty typical modern laptop using precompiled
> > sqlite 3.6.18) is about 2.8 seconds. For my requirements this is
> > a bit too slow...
>
> I believe
Jean-Denis Muys wrote:
> On 10/7/09 21:35 , "Adam DeVita" wrote:
>
>
>> One can also get a mess if Mr. Red and Mr Black both get new customers, and
>> enter them and they both get the same ID because the auto-generated int
>> happens to be the same. Both copies get updated with the other guy's
Jason Freshwater wrote:
> The problem I keep running into is with expressions of the form
>
> select distinct c from t1
>
> For a 1 million row table with 8 distinct values of c the query
> time on my system (pretty typical modern laptop using precompiled
> sqlite 3.6.18) is about 2.8 seconds
On Oct 8, 2009, at 6:59 AM, Thomas Henlich wrote:
> Hi,
>
> I found a bug in the .genfkey functionality:
Thank you for the bug report.
Did you know that the next release of SQLite will contain native
support for foreign key constraints? The ".genfkey" functionality
will become obsolete.
S
Hi (Apologies for the long post),
I am using sqlite for storing application data and settings.
A typical simple scenario is to import a CSV file into sqlite and
then present the user with various summaries of the data
that it contains.
Generally everything works really well; I can insert data
Hi,
I found a bug in the .genfkey functionality:
If a foreign key constraint has or references columns which need to be quoted,
the .genfkey command will generate invalid triggers which fail to enforce that
particular constraint. Example:
CREATE TABLE parent("a.1", PRIMARY KEY("a.1"));
CREATE
Hello!
These work fine:
set i 1
string is wideint -strict $i ;# for correct typing
test 1.1 {select * from test where a=$i}
test 1.2 {select * from test where 1=$i}
But it's badly to manually call [string is wideint -strict]
before all queries.
Best regards, Alexey Pechnikov.
http://pechnikov.t
Hi,
Yes! That's it! Sorry about the stupid question then..
select datetime('now','localtime'); seems to do what I want.
/Fredrik
On Thu, Oct 8, 2009 at 9:39 AM, Simon Davies
wrote:
> 2009/10/8 Fredrik Karlsson :
>> Hi,
>>
>>
>>
>> On Thu, Oct 8, 2009 at 12:04 AM, P Kishor wrote:
>>> On Wed, O
Hello!
On Thursday 08 October 2009 11:31:14 Dan Kennedy wrote:
> The behaviour is actually correct, assuming that SQLite is
> using sqlite3_bind_text() to bind the value of $i to the SQL
> statement executed by [db eval].
>
Why integer or real is binded as text? It's very strange and
produced r
On 10/7/09 21:35 , "Adam DeVita" wrote:
> One can also get a mess if Mr. Red and Mr Black both get new customers, and
> enter them and they both get the same ID because the auto-generated int
> happens to be the same. Both copies get updated with the other guy's data,
> they then get annoyed and
On Oct 7, 2009, at 11:21 PM, Pavel Ivanov wrote:
> Hi, Dan!
>
> I've found another bug in async io module. It happens only
> occasionally in my application but I've found how to perfectly
> reproduce it. You need to:
> - not call sqlite3async_run();
> - open new not yet existing database;
> - exe
2009/10/8 Fredrik Karlsson :
> Hi,
>
>
>
> On Thu, Oct 8, 2009 at 12:04 AM, P Kishor wrote:
>> On Wed, Oct 7, 2009 at 5:02 PM, Fredrik Karlsson wrote:
>>> Dear list,
>>>
>>> I am sorry if I am asking a FAQ, but what is differnent with
>>> datetime() and time()?
>>>
date # This is the correct
On Oct 7, 2009, at 10:41 PM, Alexey Pechnikov wrote:
> Hello!
>
> I find some incorrect types casting for constants in all SQLite
> versions.
The behaviour is actually correct, assuming that SQLite is
using sqlite3_bind_text() to bind the value of $i to the SQL
statement executed by [db eval].
37 matches
Mail list logo
