On Tue, Oct 18, 2011 at 8:04 AM, Igor Tandetnik itandet...@mvps.org wrote:
Frank Missel i...@missel.sg wrote:
I think that the sqlite-users e-mail list has enough traffic to warrant a
proper forum.
For what it's worth, I'm using GMane (http://gmane.org/), which is a mailing
list-to-NNTP
• TEXT as ISO8601 strings (-MM-DD HH:MM:SS.SSS).
I personally have had the best luck with this storage mechanism. I'm
lazy, and my platforms all understand this format readily, and it has
the advantage of being human readable as well.
___
Mmmm. Looks like there's no elegant way to do it. I looked into this a couple
of years ago when designing the setup. So:
1) Leave things as they are. Downside is the unexplained error every few
months and it's a slightly clumsy method. Upside is if the schema changes
there's no extra work
You could always create a table that stores the pragma values in which
you're interested, then have code that checks on start up to set those
pragmas to those values.
On Tue, Aug 23, 2011 at 1:24 PM, Erik Lechak e...@lechak.info wrote:
Hello all,
Is there a way to save pragma states to the
What happens when you do:
select * from t1 where rowid = (select max( rowid ) from t1);
or
select * from t1 where rowid in (select max( rowid ) from t1);
On Mon, Aug 22, 2011 at 10:01 AM, Cousin Stanley
cousinstan...@gmail.com wrote:
Black, Michael (IS) wrote:
select * from table where
On Mon, Aug 22, 2011 at 10:08 AM, Black, Michael (IS)
michael.bla...@ngc.com wrote:
Brad got it:
select * from t1 where rowid = max( rowid ) ;
Error: misuse of aggregate function max()
sqlite select * from t1 where rowid = (select max(rowid) from t1);
3|three
Why is max(rowid) a misuse.
'Create the SQL command.
strSQLCommand = INSERT INTO + strDBTable + VALUES (null +
strUpdate + );
http://www.sqlite.org/lang_corefunc.html
Use last_insert_rowid() as a second statement in your query:
'Create the SQL command.
strSQLCommand = INSERT INTO + strDBTable + VALUES
Cool! That worked. The VB6 wrapper has a LastInsertAutoID that I never paid
attention / saw before.
Be sure that it's doing what you think it is. If it's a general
purpose wrapper, then it might not. If it's a SQLite specific
wrapper, it might, but even if it's wired up correctly, you have to
Among the whole paramitration there are some parameters which are
actually reads to specific hardware addresses.
If these values are to be read directly from the hardware, why involve
the database at all? Why not simply have your application code read
them directly, instead of trying to figure
I am trying to do an UPDATE of one table based on the aggregate
results of the different table. How can I do it in SQlite please?
What have you tried that didn't work?
/bs
___
sqlite-users mailing list
sqlite-users@sqlite.org
For my own edification, why the order by 1 clause?
To sort them in ascending order of table name, which might make
old-fashioned capers like visual scrutiny a little easier.
OK then, why would one not use the column name?
/bs
___
sqlite-users
On Tue, Feb 3, 2009 at 10:28 AM, Jay A. Kreibich j...@kreibi.ch wrote:
On Tue, Feb 03, 2009 at 08:37:10AM -0500, Brad Stiles scratched on the wall:
For my own edification, why the order by 1 clause?
To sort them in ascending order of table name, which might make
old-fashioned capers like
For my own edification, why the order by 1 clause?
To sort them in ascending order of table name, which might make
old-fashioned capers like visual scrutiny a little easier.
OK then, why would one not use the column name?
It does.
No, I meant why not use the column name, instead of
sqlite select 'drop table ' || name || ';' from sqlite_master where
type = 'table' and name glob 'X[0-9][0-9][0-9][0-9]' order by 1;
For my own edification, why the order by 1 clause?
/bs
___
sqlite-users mailing list
sqlite-users@sqlite.org
Can someone tell me how many tables a given database can hold.
Try here: http://www.sqlite.org/limits.html
I'm looking at an initial design of an application that could have a table
of data for each city in a state. This could be possibly more than a
thousand tables.
Is the data stored for
CREATE TABLE dup_killer (member_id INTEGER, date DATE); INSERT INTO
dup_killer (member_id, date) SELECT * FROM talks GROUP BY member_id,
date HAVING count(*)1;
But, now that I have the copies in the dup_killer table, I have not
been able to discover an efficient way to go back to the
This will be a point release: 3.6.8. There are no
incompatibilities. An important aspect of our social contract is
that SQLite continues to be compatible moving forward. There are
hundreds of millions of SQLite3 databases in the world, and we do
not want to abandon them.
Software
That, unfortunately, leads directly to the follow-up question of
can BIGINT PRIMARY KEY AUTOINCREMENT be made to work the same as
INTEGER PRIMARY KEY AUTOINCREMENT. I believe the answer is yes, but
I wouldn't bet my life on it.
If I knew anything at all about SQLite, I'd probably say the
I'd like ideas / recommendations on implementing and auditing to track delta
changes to tables.
Here's one possibility for auditing.
http://www.sqlite.org/cvstrac/wiki?p=UndoRedo
Another possibility is to have audit tables are identical copies of
the tables being tracked, except for some
I'd like ideas / recommendations on implementing and auditing to track delta
changes to tables.
Here's one possibility for auditing:
http://www.sqlite.org/cvstrac/wiki?p=UndoRedo
Another possibility is to have audit tables are identical copies of
the tables being tracked, except for some
SELECT *
FROM MyTableWithDates
WHERE datetime(now) MAX(dtEndDate)
What is it that you are actually trying to do with this query? As
formulated (even if it were syntactically correct, which I don't think
it is), you are either going to get every row in the table, or no rows
at all. Since the
select max(rowid) from sometable;
Looks good and is instantaneous. Thank you very much.
And will only work if you never delete any rows from the table.
___
sqlite-users mailing list
sqlite-users@sqlite.org
Out of interest why must it completely READ each entire row? Is it
because '*' has been used?
The database reads a page at a time. A page is, by default, 1024
bytes. A single page might contain multiple rows, or a single large
row might be spread across multiple pages.
When rows are
I am *sure* that I am overlooking the obvious...
I have a need to identify blank columns in my tables, and have been unable
to find a suitable query, eg
SELECT * from table WHERE column = '';
What am I doing wrong here?
It depends on what blank means? Does it mean an empty string, a
PS Managed (ADO.NET) providers do not satisfy requirements for
performance reasons.
That's a pretty blanket statement. I've found Robert Simpson's
ADO.NET provider to be very performant. He has a benchmarking suite
he wrote to compare various DBs. It might still be available for
download.
The problem with performance is not caused directly by SQLite provider, but
by different
way of reading data from managed (ADO.NET) providers by Analysis Services.
Ah. Analysis Services. 'Nuff said. Missed that the first time around. :)
___
I found that the ado.net provider for sqlite support some types that do
not really match the substrings here
http://sqlite.phxsoftware.com/forums/t/31.aspx
That wrapper does some mapping between a type that's declared in the
table definition, and the types used in .NET programs. So, if you
The problem is with bulk-updating:
# time sqlite3 kfzdb 'update kfz set musttrans=3'
What happens when you run the update inside a transaction?
___
sqlite-users mailing list
sqlite-users@sqlite.org
What happens when you run the update inside a transaction?
I tried it like this:
time sqlite3 kfzdb 'begin ; update kfz set musttrans=5 ; end'
No significant change in runtime either.
Are you able to benchmark it using an actual PC's local hard drive?
Just for comparison. To be fair,
Are you able to benchmark it using an actual PC's local hard drive?
Just for comparison. To be fair, you'd have to use the same build of
sqlite, or at one that was built the same way.
That would be quite an effort.
Just a thought. Since the build for your device is likely to be
different
My stab at an SQL statement to pull this change off is as follows:
UPDATE
Events SET Return = date(d, n + ' days') WHERE pkIndex IN (SELECT
pkIndex, Nights AS n, Departure AS d FROM Events WHERE Return = '' AND Nights
!= '')
Why do you think you need the sub-select? Assuming that they
access ... dll from PowerShell ... PowerShell docs
the procedure given there is to register the dll with installutil.
The use of the word register implies to me that they might be
assuming a COM dll or .NET com visible assembly. I don't believe the
SQLITE3.DLL qualifies as either. :) As
btw: if I rerun InstallUtil I now get a message:
No public installers with the RunInstallerAttribute.Yes attribute could be
found in the ...\System.Data.SQLite.dll assembly.
You should really be asking these questions on the forum/list
dedicated to the support of the product you are working
The original problem is given in the topic.
The System.Data.SQLite provider (which I currently can't get running) is
hopefully only one solution.
You mentioned the System.Data.SQLite provider in every message you
posted, and presented it in such a way that I interpreted you to have
asked
D. Richard Hipp [EMAIL PROTECTED] wrote:
CREATE TABLE t1(a INTEGER, b INTEGER);
INSERT INTO t1 VALUES(1,2);
INSERT INTO t1 VALUES(9,8);
MSSQL Server 2000
SELECT a AS b, b AS a FROM t1 ORDER BY a;
b a
--- ---
1 2
9 8
SELECT
jonwood wrote:
I've done very little programming related to currency (dollars,
etc.) and I'm wondering if I need to worry about rounding errors.
Since SQLite doesn't appear to have a currency type, I had planned
on using REAL instead. But I have a lot of reports to print out and
I could
Is this how you expect the RTree tables to be used in a case like the OP
is interested in?
create table City (
id integer primary key,
nametext,
lat real,
longreal,
class integer
);
create virtual table
Taking into consideration a declared close relativity between SQLite and
TCL, I would to suggest an improvement in boolean-type fields treatment.
In my opinion, field of that type should be treated equally, when it does
contain a values: f, false, 0, no - and, respectively: t, true,
1, yes.
I wanted to know the algorithm used by sqlite to generate the new
rowid. Assume there can be N distinct rowid's possible, now insert N
records, followed by random deletion of some records. Now what rowid
will be assigned to a new row that is added?
I also had some rather complex piece of SQL from The Art of SQL, which
took a string and dissected it in place with subqueries and a join to a
pivot table, but wondered if there was an easier way to do it?
If you already have that information in an array in your application code, it
might
Can somebody give any explain to this please.
sqlite select date(2006-03-31);
2006-03-31
-- correct
sqlite select date(2006-03-31, -1 month);
2006-03-03
-- not correct
Can anyone confirm? Any suggestions / workarounds greatfully
received!
I've seen other replies that show why
I got a BOOLEAN field. It's defined: NOT NULL.
when a new record is inserted, how to put a Boolean Default Value
as FALSE?
By reading the documentation.
http://www.sqlite.org/lang_createtable.html
http://www.sqlite.org/lang_createtrigger.html
Brad
Could someone please post the results of these queries on Oracle,
DB2 or SQL Server?
On MS SQL Server 2000, your queries result in the following:
a c
--- ---
2-998
1 3
(2 row(s) affected)
foo
---
There is a physical constraint here. If you want to verify that your
data is safely written to non-volatile storage you have to live with the
latency. If that is unimportant to you you can relax the ACID
requirements and get faster writes, but when you do that there is no
crying over
SELECT number, name, (SELECT COUNT(*) FROM pet WHERE employee.number =
pet.number) AS pets FROM employee
That seems like it should work, provided the tables have the definitions
implied by the query. What error are you getting?
Or you could try something like this completely
I'm trying to improve SELECT queries on a db I created.
Here's the part I think is relevant:
SELECT fid, man_fsetid, pmfeature.allele, pmfeature.strand FROM
featureSet, pmfeature WHERE man_fsetid IN (LONG LIST HERE) AND
pmfeature.fsetid = featureSet.fsetid ORDER BY fid
That list
The SELECT I have is:
SELECTT1.COLUMN, T2.COLUMN
FROM TABLE1 T1, TABLE2 T2
WHERE T1.COLUMN *= T2.COLUMN
In SQL Server, the *= indicates a forced inner join which would cause
a record to be generated regardless if it existed in the T1 table or
not.
If I understand what
SELECTT1.COLUMN, T2.COLUMN
FROM TABLE1 T1, TABLE2 T2
WHERE T1.COLUMN *= T2.COLUMN
Sorry about the fist one. New web mail client...
Anyway, if I understand what you're saying, I believe what you want is an OUTER
JOIN.
SELECTT1.COLUMN, T2.COLUMN
FROM TABLE2 T2
cat your.db /dev/null
Using Windows XP. :-0
Anyone know a simple Windows command line equivalent of the cat
to dev null command above to put a file into OS cache?
Well, 'type your.db nul' will do the same thing, though whether or not it
will remain in the cache is another
On 4/4/07, Nathan Biggs [EMAIL PROTECTED] wrote:
Does anyone know if there is a floor function in sqlite, or of a way to
implement it.
Well, the suggestions about extending SQLite are probably quicker, but if a SQL
solution is required for portability, you can try something like this:
Gerry Snyder [EMAIL PROTECTED] wrote:
Chris Jones wrote:
Hi all,
I have a very simple schema. I need to assign a unique identifier
to a large collection of strings, each at most 80-bytes, although
typically shorter.
Would it help to hash the strings, then save them in the DB, checking the
SELECT TotalInvoice, (SELECT SUM(PaymentValue) FROM Payments
WHERE Payments.IDInvoice = IDInvoice) AS TotalPaid, TotalPaid = TotalInvoice
AS FullyPaid FROM Invoices;
Here, I select:
- TotalInvoice the total amount of the invoice
- TotalPaid the total amount paid till now
- FullyPaid a
52 matches
Mail list logo