Dennis Cote wrote:
I though you wanted the minute with the most events. I added the outer
select to show that that step can be done easily in the same query,
rather than relying on PHP to extract this info from the full results
for the hour.
But of course that portion of the query doesn't work
ily in the same query,
rather than relying on PHP to extract this info from the full results
for the hour.
Dennis Cote
<= end_minute then
begin_minute <= minutes.i and minutes.i <= end_minute
else
begin_minute <= minutes.i or minutes.i <= end_minute
end
group by minute)
HTH
Dennis Cote
have switched to gmail and Thunderbird as a result. Hopefully these
problems are behind me.
Dennis Cote
Asko Kauppi wrote:
Please help me refine the following SQL. I have pretty much all the C
code in place, table creation & detection works, then.. brick wall.
I cannot add a single small text string into the table. What am I
doing wrong? Trying with the
"UPDATE '%q' SET val=?2 WHERE key=?1;"
ey, value)
INSERT INTO ini VALUES('my key', 'some value')
UPDATE ini SET value = 'another value' WHERE key = 'my key'
SELECT * FROM ini
HTH
Dennis Cote
will return all the rows in table after skipping the first 10.
HTH
Dennis Cote
is example:
SELECT DISTICNT groupId FROM LinkTable
WHERE personId IN (SELECT personId FROM People WHERE city = ?)
I hope this helps.
Dennis Cote
Steve Frierdich wrote:
Does anyone know what I need to add to a select statement so only
certain number of rows will return?
What would I add to a select statement query so only 10 rows from a
table would return and not all the records in the table.
Thanks
Steve
SELECT * FROM table LIMIT 10
it should have a manual that explains its function calling
conventions (i.e. which registers or stack areas are used for arguments and
return values, which registers are preserved across function calls etc...).
If you can tell us what CPU and compiler you are using, we may be able to
help.
Dennis
---
1 2
sqlite> .quit
Does this feature still work? Is there something else I need to do to get
the output?
Any help will be greatly appriciated.
Dennis Cote
ticket #1080.
Dennis Cote
t; select * from t;
a|1
b|3
sqlite>
This works as long as the column containing the string you want to search
for is the primary key of the table (like user above). If the primary key
doesn't exist, it does an insert. If it does, it replaces that row with the
new values.
HTH
Dennis Cote
ed columns
and their order. The primary key is the one that contains all the columns
marked as members of the primary key by the table_info pragma, and no other
columns. The order of the columns in the primary key is given by the seqno
column in the result of the index_info pragma.
HTH
Dennis Cote
Daniele Nicolucci (Jollino) wrote:
Ok, I guess I'll have to live without having an auto-increment field
or find a way to fill it in the csv file with explicit values... my C
skills are almost null so I don't even attempt to do that.
Or I could write a simple script to generate INSERTs from csv
Makefile currently containss commands to generate lib files for the
Borland and Microsoft compilers. You can generate these with a command 'make
implib'. If you can determine the command required for metrowerks, I'm sure
it can be added to the make file as well.
HTH
Dennis Cote
the link line. I again modified the sqlite Makefile to remove
these dlls from the LIBTCL line. Everything builds, and all the tests run
(with the exception of bind-4.1 due to a trivial formatting issue).
HTH
Dennis Cote
Lloyd Thomas wrote:
Thanks Dennis.
As long as I know where I stand. I can probably use PHP or Delphi
to update each row manually.
Lloyd
- Original Message -
From: "Dennis Cote" <[EMAIL PROTECTED]>
To: "sqlite-users" <sqlite-users@sqlite.org>
Sent: Wed
Dennis Cote wrote:
For what it' worth now, here is the first message I posted yesterday.
It might to make my last post make a little more sense.
Hanging head in shame... That didn't clarify anything did it.
It should have said:
"For what it's worth now, here is the first message I posted yest
For what it' worth now, here is the first message I posted yesterday. It
might to make my last post make a little more sense.
Dennis Cote wrote:
Ulrik Petersen wrote:
I guess the next question is: Can someone verify that this is part of
the SQL standard?
Yes, it looks like this is part
Dennis Cote wrote:
Lloyd Thomas wrote:
Thanks. That is going OK but I am having a problem with updating the
new column with the info I need. It seems to update with the same
entry from my users table to all rows.
UPDATE call_data SET caller_name = (SELECT firstname || surname AS
'caller_name' FROM
Hi,
Yesterday I posted three messages to the mailing list. Today when I look at
the list only the last of those three messages has been distributed back to
my email account.
Has anyone else noticed the mailing list failing to distribute messages?
Dennis Cote
index.
I think index is still the best term, it is just that you need to be clear
that it is an index into the array of bound values, not into the text of the
SQL statement.
Dennis Cote
Eli Burke wrote:
I can't tell you how all those variations behave, but I think that by
far the clearest/simplest functional form is plain :N. I'm sure there
are reasons for the other forms to exist (legacy style? oracle/mysql
compatability?), but from a programmatic standpoint:
INSERT INTO
olumn name in an AS clause is
using double quotes. So SQLite's previous acceptance of the single quoted
literals can also be viewed as a standard non-conformance bug.
Finally, SQLite generates an error, as it should, if you try using an
un-quoted column name literal with a space.
Dennis Cote
D. Richard Hipp wrote:
> Before doing the "DELETE TABLE t1" you can run this query:
>
> SELECT sql FROM sqlite_master WHERE tbl_name!=name AND
> tbl_name='t1';
>
> That query will give you the complete text of all CREATE INDEX
> and CREATE TRIGGER statements associated with table t1. Save
>
Bob Dankert wrote:
> I am trying to union two queries with each query having a sub-select
> defining the table, but I consistenly get no results if I try to limit
> the sub-query. Here is my query, assuming the table a and table
> contain one column filled with integers.
>
> Select * from (select
Bob Dankert wrote:
> I am trying to union two queries with each query having a sub-select
> defining the table, but I consistenly get no results if I try to limit
> the sub-query. Here is my query, assuming the table a and table
> contain one column filled with integers.
>
> Select * from (select
Taj Morton wrote:
> http://www.torry.net/db/direct/db_directsql/sqlitedb.zip should work.
> Sorry about that.
>
I will check this out.
> No go :(. I used breakpoints and checked right after the first
> DB.ExecSQL, then looked at .tables from sqlite.exe...it didn't show up
> their either.
It
Taj Morton wrote:
> Dennis,
>
> Indeed. Actually, I'm using an OO wrapper around that library,
> TSQLiteDB
> (http://homepages.borland.com/torry/db/direct/db_directsql/tsqlite.zip).
This link gives me a 404 error so I can't look at the code myself.
>
> Here's my TEMPORARY TABLE code:
> // DB is
Taj Morton wrote:
> Dennis Cote wrote:
>
>> Taj,
>>
>> I'm not sure what wrapper you are using, but the sqlite.dll provided
>> by SQLite has two API functions that are used to open and close the
>> database connection. You use sqlite3_open() to open the connect
Jeff Flowers wrote:
> On Tue, 7 Dec 2004 18:57:18 -0800, "Scott Leighton"
> <[EMAIL PROTECTED]> said:
>> On Tuesday 07 December 2004 10:28 am, Jeff Flowers wrote:
>>> Is it possible to set the .width option when calling the sqlite
>>> frontend? I was surprised that there wasn't a options to do
Dennis Cote wrote:
>
> The case you mentioned as a motivation can be handled using the
> following expression.
>
> case
> when :param is null then x is null
> else x = :param
> end
>
I though I should also mention a somewhat simpler equivalent
many
queries as you want, either using sqlite3_exec() or the
sqlite3_prepare()/sqlite3_step()/sqlite3_finalize() APIs.
If you can provide more details about the wrapper you are using, I may be
able to confirm that the Open and Close functions you mention do map
directly to the native sqlite functions.
Dennis Cote
d along with non-null values. If they do, this
would be a good way to do it.
At the end of the day I don't see it as being a very expensive extension to
the SQL supported by SQLite, but it should be clearly documented as such.
You should also document these equivalent standard expressions so users who
want to avoid SQLite specific extensions can easily get the same effect with
standard compatible SQL.
Just my two cents.
Dennis Cote
lects 1000 records in the middle, the index will be used to locate the
first matching record, and then it will scan the 1000 records of interest
and stop when it finds the first record greater than the maximum value.
I don't know why BETWEEN doesn't use an index, but it doesn't, so stick to
comparisons. The VDBE code produced is larger, but it executes much faster.
Dennis Cote
Lloyd Thomas wrote:
> There are no indexes in may tables. Please find the following schemas
> for my tables. Would it make more sense to convert my datetime
> columns to microtime?. What other recommendations would you make for
> these tables? CREATE TABLE users (
> user_id INTEGER PRIMARY KEY,
D. Richard Hipp wrote:
> Dennis Cote wrote:
>> Hi All,
>>
>> I have a situation where populating data in a temp table takes a
>> long time, about 8 minutes, and I am wondering if anyone can offer
>> any suggestions for ways to speed it up.
>>
>> I
Hi All,
I have a situation where populating data in a temp table takes a long time,
about 8 minutes, and I am wondering if anyone can offer any suggestions for
ways to speed it up.
I have the permanent table and associated index, shown below, which holds
tree structured component data. Each node
CARIOTOGLOU MIKE wrote:
> I have uploaded the new version of sqlite3Explorer, which fixes a bug
> with the schema treeview.
Richard,
I think there is a problem with the SQLite web site. The contrib link
(http://www.hwaci.com/sw/sqlite/contrib) on the main page is giving me a
file not found error
mike cariotoglou wrote:
> I have uploaded the current version of sqlite3Explorer (a GUI to
> manage sqlite3 databases) to http://www.sqlite.org/contrib. also, I
Hi Mike,
I was trying to use your sqlite3Explorer program to look at my database, but
it won't open it. I get a dialog with the
Pigott, Paul wrote:
> Is there a pre-compiled binary for that? Or do you just convert the
> .DLL to a .LIB with a utility?
You need to generate an Borland import library for the DLL using Borland's
IMPLIB utility. The command to use depends upon which version of sqlite you
are using.
For SQLite
Tim McDaniel wrote:
> Given a table T like this:
>
> A B
> -
> 1 5
> 2 5
> 3 5
>
> I need a query to give me just the row with the largest A value,
> within a group defined by B.
Tim,
The statement proposed by Lawrence (copied below) will work if your table
has only
Dennis Cote wrote:
>
> I have attached that comment to this message.
>
Oops... my attachement didn't make it through. (Are they blocked?)
Here is the relevent comment:
/*
** 2004 April 6
**
** The author disclaims copyright to this source code. In place of
** a legal notice, here is a
mment to this message.
I hope this helps.
Dennis Cote
Paolo Vernazza wrote:
> If I try to do all from the command line (sqlite3.exe mydb.db .dump
> mytable), it will dump all the tables that are int the DB
>
> I think I'm doing something wrong. someone can help me?
Paolo,
Try quoting your command so it gets passed to sqlite3 as a single
EzTools Support wrote:
> I'm just wondering why I have not received any comment regarding this
> issue...?
>
> EzTools Support wrote:
>
>> Hello DRH. This is further to the previous query regarding no type
>> information being returned with Views. I had previously asked if
>> this could be
Kurt Welgehausen wrote:
>> ... only the first index is opened ...
>
> That's right. SQLite uses only one index, and it doesn't keep
> statistics, so it may pick the wrong index if it has to choose.
> Try forcing it to use the other index by dropping the one that
> it's using now.
>
> Changing
misc3-3.12 misc3-3.13
misc3-3.14 pager-2.3.6 pager2-2.3.6 printf-8.3 printf-8.4 printf-8.5
rowid-7.7 rowid-12.2 types-2.1.8 types-2.1.9
Does anyone know of any gotcha's I should expect?
TIA
Dennis Cote
From: Michael Roth <[EMAIL PROTECTED]>
D. Richard Hipp wrote:
| The ":N:" style variables were implemented briefly, but never in a
| released version. The latest in CVS supports ":AAA" (alphanumeric AAA
| with no closing colon) because that is what (I am told) is the
| SQL standard.
And what's
Darren Duncan wrote:
> In answer to both Michael Roth and Dennis Cote, I will try to explain
> the logic in what D. Richard Hipp presented as the way things
> currently are, as I understand the explanation.
>
> I interpret what DRH said in that SQLite 3 is still using plain
>
D. Richard Hipp wrote:
> Parameters can be in any of three forms:
>
> *
> *
> *
I repeat the other calls for continued (or added) support of the ?n style of
numbered parameters. They can of course be replaced by named parameters
using names like :Pn but that involves extra
Michael Roth wrote:
> We already have ?, ?nnn and :nnn: IIRC. Adding $xyz, %xyz, @xyz and
> possible other ones in parallel isn't a good thing, I think.
>
> Maybe :xyz: is good enought and binding language neutral. Maybe @xyz.
>
> How this is handled in other engines? Maybe there is a
>
Keith Herold wrote:
> The short question: do (temporary) VIEW's have rowid's in SQLITE?
The short answer: no.
You don't say how you are accessing the database, but if you are using C,
then you can simply use the precompiled query interface to perform your
lookup and return your result in small
Michael Roth wrote:
> Exists the same error in the sqlite3_prepare call? Anybody validated
> this?
I just tested this with SQLite V3.0 and it does not display this quirk. I
get the same performance using a statement that is prepared inside a
transaction and one that is prepared outside a
Hi All,
I have discovered a quirk in the SQLite API (both 2.8 and 3.0) when using
precompiled SQL statements with transactions.
My goal was to perform a series of inserts into a database as quickly as
possible. To this end I first used a single explicit transaction around the
series of inserts.
Christoph Gassmann | aquantum GmbH wrote:
> I have a log-Table of a webpage-logger and for faster selects i need
> the seconds between to clicks.
>
> I found out a select-statement that produces the seconds. Here it is:
>
> SELECT round(julianday(t2.dTimestamp)*86400 -
>
D. Richard Hipp wrote:
> In MSVC and in Borland, a signed 64-bit integer is __int64, right?
> What's an unsigned 64-bit integer on those compilers?
>
> Do MSVC and Borland support the "long double" type?
The Borland C++ compiler in C++Builder 6 supports the following types with
the given sizes
have no idea why. My work around is simply to close the
database file after it is initialized, and then immediately reopen it. Now
all the queries run at full speed.
I hope this helps.
Dennis Cote
-
To unsubscribe, e-mail: [EMAIL
- Original Message -
From: "Detlef Groth" <[EMAIL PROTECTED]>
> After consulting the php-sources I could hack the tclsqlite.c file
> myself to add aggregation capabilities to tcl, this should go into the
> next release:
This looks good. I'm surprised it didn't exist already.
> proc
sqlite>
P.S. Note that SQL doesn't have a boolean data type with values true and
false. These are normally replaced with a single character code as I have
done above. The entire words 'True' and 'False' could be used just as well,
but they will simply take up more space in the database file.
> - Original Message -
> From: "Kurt Welgehausen" <[EMAIL PROTECTED]>
> > No one can really evaluate this without knowing your view
> > definition.
>
> Here you are. I'm still not sure that it is relavent though.
>
> CREATE VIEW Device_Property_List AS
> SELECT
> D.device_id
- Original Message -
From: "Kurt Welgehausen" <[EMAIL PROTECTED]>
> No one can really evaluate this without knowing your view
> definition.
Here you are. I'm still not sure that it is relavent though.
CREATE VIEW Device_Property_List AS
SELECT
D.device_id AS device_id,
> From: "Chris Sebrell" <[EMAIL PROTECTED]>
>
> I'm trying to figure out if it's possible, using SQLite 2.8.13 (latest
version), to do
> case-insensitive ordering on a column.
Chris,
You can do what you want with an auxillary table and an index on that table.
In the example below your table
D. Richard Hipp wrote:
> The 1st and 3rd APIs above will work, but not the second. Remember,
> SQLite 3.0 will have manifest typing, which means that type of the
> data can change from one row to the next. Type is not associated
> with a column, as in standard SQL. So there is no way to know
D. Richard Hipp wrote:
> A design proposal for SQLite version 3.0 can be found at:
>
> http://www.sqlite.org/prop2.html
>
Richard,
I read your proposal and it all look very promising to me.
I would like to propose some additions to the API to support named
parameters in the SQL statement.
From: "Tito Ciuro" <[EMAIL PROTECTED]>
> Now that 2.8.13 is out, is 'PRAGMA show_datatypes = ON' by default? I
> wonder because I'm still not getting the data types in the result set.
>
> I posted a message yesterday and after upgrading to 2.8.13 I'm still
> getting the same results. Can someone
w database.
Are you suggesting that SQLite is not enabling these optimizations, or that
my code needs to do something different to enable them?
Dennis Cote
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
- Original Message -
From: "Michal Pasternak" <[EMAIL PROTECTED]>
>
> Do you use transactions?
>
Yes. Transactions are used around both scripts and around the the large
block of SQL with the different execution times.
seconds.
I didn't expect this 2 to 1 speed difference, and have no explanation for
why it occurs. Does anyone else have an idea what might be happening?
Dennis Cote
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional
> Question to all: If I modified SQLite to use the
> same column naming rules as (say) PostgreSQL, how
> much existing code would it break? Is this something
> that should be done, even though it is a (slightly)
> incompatible change?
>
>
> --
> D. Richard Hipp -- [EMAIL PROTECTED] --
Hi All,
I am looking at making some changes to SQLite to correct the behavior of
the last_insert_rowid() function within triggers. While investigating
these changes I came upon some points where there are several different
ways to handle the semantics. I discussed these points briefly with
From: "Bronislav Klučka" <[EMAIL PROTECTED]>
> Hi, I've got two same databases (one is older then the second one) and I
> wanted to attach them, so I've done:
>
> attach database './database/produkty.sdb' as commodities2;
> select * from commodities left join commodities2.commodities on
>
> So after I write & compile the code, how can I tell SQLite to load it? Or
> do I need to compile it into the sqlite libraries?
No, you install your user defined function using the
sqlite_create_function() or sqlite_create_aggreate() calls at runtime.
These calls require function pointer
> Please excuse the silly question, but how is that done?
It can't be done in SQL code.
You can do it quite easily using C language API calls. These are documented
in section 4 of the API documentation at
http://www.hwaci.com/sw/sqlite/c_interface.html
All of the built in functions in SQLite
to
understand. If not, let the questions fly, and I will try to answer anything
I can.
Dennis Cote
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
> I have a view that left-joins two tables, where the first table has got
unique
> columns. I have created an insert-trigger and an update-trigger on the
view.
> The insert trigger works fine.
> If I do an "UPDATE testview SET xyz='test' WHERE id=1;" the update-trigger
> throws an error 'SQL
1101 - 1177 of 1177 matches
Mail list logo
