- Original Message -
From: "Rúben Lício" <[EMAIL PROTECTED]>
To:
Sent: Wednesday, November 08, 2006 1:03 PM
Subject: [sqlite] PHP5 with SQLite3
Are you connecting correctly to the database. Which version of sqlite3 was
the database created in. I beleive php5.1 is version 3.2.8
Hi
I wish to create a query where I do a number of counts on the same table but
with different filters.
ie:
count(id) as numrows
count(id) as inrows where direction = 'In'
count(id) as outrows where direction = 'Out'
Could I do the above in a single query?
| id | date | direction | dur
Would not adding verification cause a problem for those include the
downloading of sqlite in bash/make files of certain apps.
Lloydie T
- Original Message -
From: "Rich Shepard" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, January 30, 2007 2:50 PM
Subject: Re: [sqlite] Abuse of the SQLite
a question about sqlite3.exe
reading some documentation aboit it I see you could have memory database.
how do I create them?
or attach them?
thanks all!
by the way, on the wiki page there was a link to an article which seems to
confirm that ':memory:' is used as special filename to declare in memory
database, indeed :)
I have 2 related table:
CREATE TABLE Ingredients(
ID INTEGER PRIMARY KEY,
name TEXT,
description BLOB,
property_ID INTEGER
);
CREATE TABLE Properties(
ID INTEGER PRIMARY KEY,
price double
);
When I create a new Ingredient I would like to create a new property for this
ing
BTW, one more question / precision.
in INSERT INTO Properties() I didn't pass the value for ID.
because basically I want an auto-incremented value which I don't have to
worry about.
maybe that's not the way to use such value ?!
- Original Message -
From: "L
thanks Martin it worked!
although I replaced your (SELECT MAX(ID) FROM Properties) by ROWID.
is it sound?
like that:
CREATE TRIGGER create_ingredient_property AFTER INSERT ON Ingredients
BEGIN
INSERT INTO Properties (price) VALUES (NULL);
UPDATE Ingredients
SET property_ID = ROWID
Thanks for that!
last_insert_rowid() function:
anyway, I hadn't tested the code. I mean the CREATE TRIGGER succeed.
But I didn't check if the trigger itself works well.
Now I did and have a problem...
It don't work!
I get: "SQLite Error 1 - no such column: OLD.ID"
this is my setting:
CREATE T
Sorry, stupit mistak, I have to use NEW and not OLD in case of an INSERT
trigger!
Thanks all it works like a breeze!
- Original Message -
From: "Lloyd Dupont" <[EMAIL PROTECTED]>
To:
Sent: Thursday, June 09, 2005 12:26 AM
Subject: Re: [sqlite] SQL question
http://www.sqlite.org/lang.html
follow the link for
CREATE TRIGGER
- Original Message -
From: "Puneet Kishor" <[EMAIL PROTECTED]>
To:
Sent: Wednesday, June 15, 2005 12:18 AM
Subject: [sqlite] stored procedures
searching on the 'net reveals that SQLite3 doesn't support storedprocs,
BTW I wonder why SQLite doesn't support Stored Procedure.
Through Trigger it does already support some similar functionality.
Certainly, while writing trigger code it won't have been that much code to
write stored procedure code as well.
That kind of puzzle me.. is there any rationale for the
I'm not sure it's a real justification
I believe that stored procedure are more than convenience to do avoid
multiple client-serveur call
For exemple lately I wanted to created to related table (kind of
MASTER_TABLE, PROPERTY_TABLE)
property should be destroyed/created with master record.
I u
in the application I'm writting I would make intensive use of BLOB
in fact I have a very simple DB tables.
However most of them have a BLOB column where I would put application defined
data.
The data could easily be over 50K (in fact it would be an in memory .tgz
archive with user text and (opti
let's say I have a table
like that
CREATE TABLE Infos
{
id INTEGER,
text TEXT
}
and I want to search a with the word... 'apple', 'cinamon', 'cake'
I could write
SELECT FROM infos WHERE text LIKE '*apple*' AND text LIKE '*cinamon*' AND text
LIKE '*cake*'
Now, isn't there a way to improv
thanks all!
- Original Message -
From: "Puneet Kishor" <[EMAIL PROTECTED]>
To:
Sent: Sunday, June 26, 2005 9:21 AM
Subject: Re: [sqlite] about text search
On Jun 25, 2005, at 6:05 PM, Lloyd Dupont wrote:
let's say I have a table
like that
CREATE TABLE I
I have read that SQLite doesn't suport well multithreading...
I have the following problem and wonder if anyone could provide me some
guidance.
I have a GUI application using SQLite to store its data.
I have a 'Search' panel. Performing a search (scan of the database) in the
background.
And whil
I look into the build in function of SQLite and saw there is a function to know
the length of a string (in a record).
Great!
But to my disbelief there is (apparently) no way to get the size of a blob
(other than loading it :-()
And no, length() doesn't work on Blob.
I think it would be a worthy
Well, I guess I was not clear in my explanations.
I'm already doing my own locking, that's the problem!
But I'm not sure how to improve it.
My problem here is the following: I am doing a search through all record,
could be long. Do some string matching, cutting, etc with all text of all
records
Isn't this what you are looking for?
http://www.sqlite.org/capi3ref.html#sqlite3_column_bytes
" If the result is a BLOB then the sqlite3_column_bytes() routine returns
the number of bytes in that BLOB. "
Or do you really need it inside an SQL statement?
that's right!
I'm not using SQLite C A
How about putting the update into in it's own thread and just wait
till any locks are released?
I though of that but think it's annoying, what if the user want to Quit the
application?
Should he wait for all the thread to perform...
But someone give me a good idea, I could make a copy of the da
I am having a problem building sqlite on my redhat 9 box. There seems to be
a problem with TCL. I am no linux guru, so it some one can poinjt me in the
right direction that would be great.
here is as far as I get
[EMAIL PROTECTED] sqlite-3.2
I have compilted tcl, but had a problem with tk. having compiled sqlite3 I
get a new error trying to run sqlite3 =
'sqlite3: error while loading shared libraries: libsqlite3.so.0: cannot open
shared object file: No such file or diretory'
Any Ideas?
Lloyd
- Original Message -
There does not seem to be a library file in /usr/lib/ called
libsqlite3.so.0. would that be the problem. Please bear with me I am a linux
newbie.
Lloyd
- Original Message -
From: "Arjen Markus" <[EMAIL PROTECTED]>
To:
Sent: Monday, January 02, 2006 12:13 PM
Subj
have installed
sqlite 3.2.8 correctly for me. It would be good if I could type sqlite3 at
the prompt and it would just start.
Lloyd
- Original Message -
From: "Arjen Markus" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, January 03, 2006 7:51 AM
Sub
The precompiled version did work. I was just trying to compile it myself to
get experience building a linux box.
Lloyd
- Original Message -
From: <[EMAIL PROTECTED]>
To:
Sent: Tuesday, January 03, 2006 1:29 PM
Subject: Re: [sqlite] Building sqlite 3.2.8 on redhat 9 (of
As you can tell I am no sql guru. Is there anyway I could do the following
without 'group by'.
query:
SELECT C.call_id, C.extn_no, C.dest, U.group_name
FROM call_data AS C, grp_user AS G, user_grp AS U
LEFT JOIN user_grp ON G.group_id = U.group_id
LEFT JOIN grp_user ON C.extn_no = G.extn_no
WHER
sqlite3 is available in 5.1 using the pdo extensions.
have a look at www.php.net/pdo
I had the same problem with an app I wrote in delphi to insert records and
used php front end to select records
Lloyd
- Original Message -
From: "jack wu" <[EMAIL PROTECTED]>
T
I am not sure if I am being crazy, but I seem to be getting a wierd result
when using 'BETWEEN'.
if use
SELECT count(call_id) as num_rows WHERE ring_time BETWEEN 6 and 10;
I get 0 results
but if I do
SELECT count(call_id) as num_rows WHERE ring_time = 7;
I get 39 results
SELECT count(call_id) a
If i do
SELECT count(call_id) as num_rows WHERE ring_time BETWEEN 10 and 6;
I get 633 results.
Is that normal?
- Original Message -
From: "Lloyd Thomas" <[EMAIL PROTECTED]>
To:
Sent: Saturday, March 25, 2006 3:48 PM
Subject: [sqlite] Wierd between results
I am not su
Go back to my original problem if I change 'between 6 and 10' to 'between 6
and 9', I get the expected results.
SELECT count(call_id) as num_rows WHERE ring_time BETWEEN 6 and 9;
232
Am I finding a bug or is my syntax incorrect?
- Original Message -
From: &q
AIL PROTECTED]>
To:
Sent: Saturday, March 25, 2006 5:39 PM
Subject: Re: [sqlite] Wierd between results
"Lloyd Thomas" <[EMAIL PROTECTED]> wrote:
if use
SELECT count(call_id) as num_rows WHERE ring_time BETWEEN 6 and 10;
I get 0 results
You DID specify a table in your act
I heard a rumor that there is some dtae/time functions. If so what is implemented?
I was hoping for YEAR / MONTH functions you get in MySQL.
Lloydie T
Thanks for that. I note that the date/time functions have been available since ver
2.8.7 . What version is the windows DLL binary on the downlaod page.
Lloyd
-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
Sent: 21 February 2004 03:11
To: [EMAIL PROTECTED]
Subject
SELECT TIME_TO_SEC(duration)as duration
FROM call_data where call_time <= '2003-12-04 16:41'
AND DATE_SUB('2003-12-04 16:41', INTERVAL duration HOUR_SECOND) <= call_time
---
Lloyd
- Original Message -
From: "Lloyd thomas" <[EMAIL PROTECTED]>
To: "Kurt Welgehausen" <[EMAIL PROTECTED]>
Sent: Saturday, February 28, 2004 8:34 PM
Subject: Re: [sqlite] time calculations
> Thanks Kurt,
> Nearly there, but not quit
I have a table that shows a list of connections showing the time the
connection was finished and the duration.
I wish to show concurrent connections during a particular minute.
for instance the following would show that there was two connections during
2003-12-04 09:27:00
--
2')
AND (julianday('2003-12-03 18:42') - julianday(duration)) <=
julianday(call_time)
---
Lloyd
- Original Message -
From: "Lloyd thomas" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Satur
Thank you DRH,
I did manage to sort out my problem with the help
from another member. The functions you are adding I'm sure will be
appreciated. Will there be a simple way to update SQLite in PHP on a windows
platform so that I can use the new date/time functions?
Hi All,
I am still not quite getting my head round the time functions. I have a MySQL
query which checks minutes within a period for a match for a match.
Can I achieve the same thing in sqlite?
MySQL code--
SELECT TIME_TO_SEC(duration)as durati
Please ignore this question. I have already work it out ages ago, but had
forgotten.
- Original Message -
From: "Lloyd thomas" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, April 03, 2004 12:59 PM
Subject: [sqlite] DATE_SUB
Hi All,
I am stil
I am using delphi to campare telephone numbers against a table of telephone number
area codes. These arecodes can be upto ten digits, so using a for loop, I am comparing
the the first ten digits of the number. If there are no matches I then will delete one
digit from the right of the telephone n
I tried the follwing with no results:
select areacode from stdcodes where '01865323260' like areacode || '%'
if I do:
select areacode from stdcodes where '01865' like areacode
that works OK, but that is more or less the same as what I do already.
Am I missing something?
- Original Message
Thanks Kurt,
The GUI I was using to run the query seems to be using
an old version of the SQLite DLL. I haved tried another one and the query
has got me closer to what I want.
The problem I now have is that I am getting too many results.
ie.
SELECT areacode FROM stdcodes WHERE '
I think I have sussed it. If you can see something wrong with the follwing
let me know.
code-
SELECT MAX(areacode) AS areacode FROM stdcodes WHERE '0018764582' LIKE
areacode || '%';
---
> Thanks Kurt,
>
>I think I have sussed it.<
Well not quite!!
If I extend the 'select' parameter to include other columns, there are
instances where the results in those columns may not be related to what is
in the left columns (as I would expect) and if you group the results, you
end up with more than one result(
Thanks.
Lloyd
- Original Message -
From: "Kurt Welgehausen" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sunday, April 11, 2004 4:58 PM
Subject: Re: [sqlite] Matching telephone strings
> > ..., but is it correct?
>
> No, very dangerous. You&
I have a table with user id's and another with user id's linked to a group.
users table
|user_id| username |
| 1 | Lloyd Thomas|
| 2| John Smith |
Group table
|group id| user_id|
| 1 | 1 |
I am trying to create a query which selects a use
x27;1'or G.group_id is null;
- Original Message -
From: "Christian Smith" <[EMAIL PROTECTED]>
To: "Lloyd thomas" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Sunday, April 11, 2004 9:3
Thanks Ken,
You make it look so simple.
Lloyd
- Original Message -
From: "Williams, Kenneth (Ken) (TLR Corp)" <[EMAIL PROTECTED]>
To: "Lloyd thomas" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Monday, April 12, 2004 3:50 P
I have a query which successfully runs on PHP5, which I beleive has sqlite 2.8.11
embedded, but run it in sqliteplus (windows GUI using 2.8.12) it does not return any
results. I have check with eZtools the provider of Sqliteplus and they can not find
fault with their program.
Is there any differ
Ok,
I just tried the query using the sqlite command tool 2.8.12 and I
get no results, whereas if I use PHP5 with sqlite 2.8.11 I get the expected
results.
Can some one tell me why this is or what I am doing wrong?
- Original Message -
I have a query which successfully runs on PH
Which is the best way to backup rows which meet a certain date criteria?
ie WHERE data is <= '2003-11-20'.
Would I need to select and save the data to a temporary table and then DUMP the temp
table.
Forgive my ignorance, I have yet to use a transaction and therefore can you
give me an example.
Lloyd
- Original Message -
From: "Christian Smith" <[EMAIL PROTECTED]>
To: "Lloyd thomas" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesd
Thanks Christian,
Although that makes sense I can find nowhere on the
net which explains how to dump the data within a transaction.
Do you have an example.
Lloyd
- Original Message -
From: "Christian Smith" <[EMAIL PROTECTED]>
To: "Lloyd thom
May try something like
WHERE julianday(DBTimeStamp) < julianday('2004/4/4')
but I might be wrong.
Lloyd
- Original Message -
From: "Shawn Anderson" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, May 11, 2004 3:33 PM
Subject: RE: [sqlite]
I am have a problem with a query which may well have over 200,000 records. I
have building a website using PHP and PHP is timing out after 30secs due the
the size of the call_data table (I think). Is there anyway I can improve the
following query so that it is faster. I think I am using sqlite 2
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,
extn_no varchar(16) default N
If I change the call_time to an integer column, storing unix time and make
it hte index, would this help?
- Original Message -
From: "Ulrik Petersen" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, November 16, 2004 11:10 PM
Subject: Re: [sqlite] Speeding up quer
Hi again,
Th
I have been using SQLite successfully for quite some time. I recently
installed it on a friends server in order to use it there.
While I am able to access the db file just fine when I run my perl script from
the shell prompt I get the following error when trying to run it from a cgi-bin.
unab
M
Subject: Re: [sqlite] unable to open database
Steven Lloyd said:
I have been using SQLite successfully for quite some time. I recently
installed it on a friends server in order to use it there.
While I am able to access the db file just fine when I run my perl script
from the shell prompt I get th
t: Monday, November 22, 2004 11:49 AM
Subject: Re: [sqlite] unable to open database
--- Steven Lloyd <[EMAIL PROTECTED]> wrote:
I have been using SQLite successfully for quite some
time. I recently installed it on a friends server in
order to use it there.
While I am able to access the db file
Hi, I am having a problem with the following query. It seems to force php to
timeout after 30secs. The query goes through 150K records. Is there anything I
can do to speed it up?
code-
SELECT call_id, C.extn_no AS extn_no, dest, dest_name, call_time, durat
Hi, I am having a problem with the following query. It seems to force php to
timeout after 30secs. The query goes through 150K records. Is there anything I
can do to speed it up?
code-
SELECT call_id, C.extn_no AS extn_no, dest, dest_name, call_time, durat
Hou can try libsql as a component for Delphi (more of a wrapper). I have
used it so far, but only for sqlite2.8.x. I don't have much programming
experience, but to date I have not had any real problems using sqlite as a
database.
Lloyd
- Original Message -
From: "Ahmet Akso
I wish to create a new column in a table and add data, which is queried from
another table.What is the best way?
Lloyd
another table.
Lloyd
- Original Message -
From: "Paul Dixon" <[EMAIL PROTECTED]>
To:
Sent: Monday, January 10, 2005 5:50 PM
Subject: Re: [sqlite] add new column to table
Lloyd Thomas wrote:
I wish to create a new column in a table and add data, which is queried
from an
= (SELECT extn_no FROM call_data));
I have missed something?
- Original Message -
From: "Lloyd Thomas" <[EMAIL PROTECTED]>
To:
Sent: Monday, January 10, 2005 9:08 PM
Subject: Re: [sqlite] add new column to table
Thanks Paul,
I have used that example before
UPDATES with SUB SELECTS. What is
the URL?Lloyd- Original Message -
From: "Bert Verhees" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, January 11, 2005 7:55 AM
Subject: Re: [sqlite] add new column to table
Op maandag 10 januari 2005 23:56, schreef Lloyd Thomas:
Thanks. That is g
regarding UPDATES with SUB SELECTS. What is
the URL?
Lloyd
- Original Message -
From: "Bert Verhees" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, January 11, 2005 7:55 AM
Subject: Re: [sqlite] add new column to table
Op maandag 10 januari 2005 23:56, schreef Lloyd Thomas:
Thank
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"
Sent: Wednesday, January 12, 2005 4:21 PM
Subject: Fw: [sqlit
Hi Dennis,
I tried something similar to your recommendation, but there was
a problem with the following
INSERT INTO call_data
SELECT old_call_data.*, firstname || ' ' || surname
FROM old_call_data JOIN users USING extn_no;
there is a syntax error near extn_no.
Lloyd
;, '+ i
minutes') AND datetime('2004-04-07 10:00:00', '+ i minutes', '+ intdur) <=
event_time)
Can some one help?
Lloyd
me between
datetime('2004-04-07 10:00:00','+1 minutes') and datetime ('2004-04-07
10:59:59','+1 minutes');
returns nothing.
Am I using the right datetime modifiers?
- Original Message -
From: "Lloyd Thomas" <[EMAIL PROTECTED]>
To:
thanks for your reply. I think writing a new function is beyond me at the
moment as I have no knowledge of C. I have found the problem with my
datetime modifiers.
I am trying various queries but still getting no results.
I think the best way to attack this is using the my minute integer table
-
might try that.
|
---
How would you do that in C?
- Original Message -
From: "Jay" <[EMAIL PROTECTED]>
To: ; <[EMAIL PROTECTED]>
Sent: Tuesday, February 22, 2005 2:00 AM
Subject: Re: [sqlite] tricky da
ur_start+duration)%60'
finally
'GROUP' by seconds'
Would this be the best way to do this query?
I apologise for my bad SQL syntax in advance as I am not great at it, a
symptom of knowing a little of a lot and lot of little.
Lloyd
- Original Message -
From: "Jay&
ion <= 1081335540
The result I get is
0|15
I was expecting 60 result rows
any Ideas? Is this a version 3 operator only?
- Original Message -
From: "Lloyd Thomas" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, February 22, 2005 11:41 PM
Subject: Re: [sqlite] tricky date time pr
sults 'i'
as a datatime modifier in the event_data table. Not much luck though.
- Original Message -
From: "D. Richard Hipp" <[EMAIL PROTECTED]>
To:
Sent: Wednesday, February 23, 2005 12:26 PM
Subject: Re: [sqlite] tricky date time problem
On Mon, 2005-02-21 at 21
indicate where I am going wrong.
Lloud
- Original Message -
From: "Jay" <[EMAIL PROTECTED]>
To: ; <[EMAIL PROTECTED]>
Sent: Wednesday, February 23, 2005 5:39 PM
Subject: Re: [sqlite] tricky date time problem
--- Lloyd Thomas <[EMAIL PROTECTE
ECTED]>
To: "sqlite-users"
Sent: Wednesday, February 23, 2005 5:41 PM
Subject: Re: [sqlite] tricky date time problem
Lloyd,
I messed up the math for the end minute calculation. :-[
The correct query is given below. Adding 59 was intended to round the
result of an integer division (w
Dennis,
Thanks for you help so far. I think it is easier for PHP to select
the MAX event. The problem I now have is if there is no records for an hour,
PHP will through up an error because MAX must have at least one record to
process, even if it is 0.
Thanks again
- Original Message
Dennis, syntax error somewhere.
'group by minute union select 0, 0 where not exists (select * from
event_data)'
Lloyd
- Original Message -
From: "Dennis Cote" <[EMAIL PROTECTED]>
To:
Sent: Thursday, February 24, 2005 1:31 AM
Subject: Re: [sqlite] tricky date
Dennis
Might be someting to do with version 2.8.15. I have tried as is and I get an
error "sql error near 'Select': syntax error"
Lloyd
- Original Message -
From: "Dennis Cote" <[EMAIL PROTECTED]>
To:
Sent: Thursday, February 24, 2005 1:31 AM
Su
Hi,
My table contains a text field called Name. Let the data be
Name
---
Abc
abcd
AB
cab
def
I want to selcct all the rows which contains the term ab (not case
sensitive). How can I do this?
Thanks in advance,
Lloyd
-
This email was sent using
SQLite database is stored as a normal file in the disk. Why dont you use
Operating System calls to back up the file?
- Original Message -
From: <[EMAIL PROTECTED]>
To:
Sent: Friday, November 03, 2006 2:18 PM
Subject: [sqlite] sqlite backup from the program.
Hi All,
How t
Whether your database creation error solved? what was the problem?
- Original Message -
From: <[EMAIL PROTECTED]>
To:
Sent: Friday, November 03, 2006 2:18 PM
Subject: [sqlite] sqlite backup from the program.
Hi All,
How to take a backup of the sqlite database (dump) from
101 - 187 of 187 matches
Mail list logo
