Re: [sqlite] Abuse of the SQLite website
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: sqlite-users@sqlite.org Sent: Tuesday, January 30, 2007 2:50 PM Subject: Re: [sqlite] Abuse of the SQLite website On Tue, 30 Jan 2007, Mark Richards wrote: Returning a link that expires in an email to the requester is a method that works nicely. It does require some programming and maintenance, but would be a nice gatekeeper. Rather than this, I support the idea of an image with letters and digits in various fonts or colors (so they cannot be readily harvested by OCR methods) that are to be manually copied to a text entry field. I've no idea of the relative amounts of effort required by these different approaches, but I suspect that such images are readily available on the Web and a sufficiently large collection could be used with each presentation being arbitrary. Perhaps once this is done -- and hidden from page source view -- it would not need much maintenance. The inconvenience to us who access the site is the price we need to pay given the abuse of the 'Net and the Web. Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] A little help with count
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 | duration | cost | | 1 |2007-01-01|In| 56 | 0.00 | | 2 |2007-01-01| Out | 60 | 0.10 | | 3 |2007-01-02| Out | 47 | 0.10 | | 4 |2007-01-02|In| 120 | 0.20 | Thx Lloydie T - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PHP5 with SQLite3
- Original Message - From: Rúben Lício [EMAIL PROTECTED] To: sqlite-users@sqlite.org 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'm trying to use PHP5 with SQLite 3, but it's not working. I see then the native PHP only suporte SQLite 2.8, but i can compile last version of php with SQLite 3 suport. O compile last PHP version with this line: make clean ./configure --prefix=/usr/local/php5 --enable-pdo --with-sqlite=shared --with-pdo-sqlite=shared --with-zlib --enable-track-vars --with-apxs2=/usr/local/apache2/bin/apxs --enable-sqlite-utf8 make make install phpinfo tell-me that it is ok with SQLite 3 suport. But when I try to execute query, i have that exception message: 'PDOException' with message 'SQLSTATE[HY000]: General error: 1 SQL logic error or missing database' in ... Anybody know how to correct that problem? ty Ruben -- Linux user #433535 Linux because we are freedon. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] number problem with 3.2.8
I am using sqlite 3.2.8 which is included in PHP5.1. I seem to be having a problem doing queries where with '' to search a number. for instance if I do the following select ring_time fron calls where ring_time '10'; I get the following results 3 6 3 6 3 6 2 3 3 3 2 etc. Why? This row is varchar. Is 3.2.8 not able to work with numbers stored as varchar?
Re: [sqlite] Re: number problem with 3.2.8
I did try number literal 10 but mad no difference. I will rebuild the database row as an integer. Lloyd - Original Message - From: Igor Tandetnik [EMAIL PROTECTED] To: SQLite sqlite-users@sqlite.org Sent: Thursday, October 26, 2006 12:21 AM Subject: [sqlite] Re: number problem with 3.2.8 Lloyd Thomas lloydie-t-d/OCxD/[EMAIL PROTECTED] wrote: I am using sqlite 3.2.8 which is included in PHP5.1. I seem to be having a problem doing queries where with '' to search a number. for instance if I do the following select ring_time fron calls where ring_time '10'; I get the following results 3 6 3 6 3 6 2 3 3 3 2 etc. Why? This row is varchar. Is 3.2.8 not able to work with numbers stored as varchar? I don't see any problem. '3' is indeed greater than '10' in alphabetic order. What did you expect? If you want the values to compare as numbers, why do you store them in a string field, and why do you compare against a string literal '10' rather than numeric literal 10 ? Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite under linux
Hi Lloyd, I had exactly the same problem being new to C++. These are the steps I took. You may find that the library files may be in a folder where RH does not expect them to be. Try 'whereis libsqlite3.so' to locate. I can't remember where the default lib folder is. you will need to add -lsqlite3 in your Makefile or compile your code with -lsqlite3. You don't mention what errors you get when the make files. Some may be able to help you further if they have that information. Lloyd T - Original Message - From: Lloyd [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Tuesday, September 26, 2006 12:45 PM Subject: [sqlite] SQLite under linux Hi all, I have been trying to use SQLite with Redhat EL 4. I dont know how to link the object file. I have downloaded the sqlite-3.3.7.so. but I don't know how to link my source file with this file. As mentioned in the quick start guide I have copied the example source code for C. though I could not find the sqlite3.h file, I downloaded the source code file of the sqlite and executed the ../configure command. it worked file then I tried to make the file, but it failed, any how I got the necessary sqlite3.h file. But still I don't know from where to download the sqlite library and how to install it in my machine. Awaiting for your kind reply. Thanks and regards, Lloyd. __ Scanned and protected by Email scanner - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Compiling PHP5 in order to use SQLite3
I far as i know you compile with php5-pdo-sqlite, but this was only tested on debian. Last time i looked this supports sqlite 3.2.8 and you have to use php's pdo functions and not the sqlite functions which only work with v2. - Original Message - From: Michael Young [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Friday, September 15, 2006 10:42 PM Subject: [sqlite] Compiling PHP5 in order to use SQLite3 I'm not a Unix programmer, but I would like to compile PHP5 so that it will be able to read/write SQLite3 files on my Mac. In particular, I have tried to follow the installation script suggested at www.phpmac.com for Apache 2.2.2 and PHP 5.1.4. However, this only results in a compilation that recognizes SQLite2 files. If anyone can offer advice on compiling on Mac OS Tiger, I would be most appreciative. Thanks in advance. Here's the script (modified in bold from the one suggested at www.phpmac.com) I have been using without success: ./configure --prefix=/apache2/php --with-zlib --enable-pdo=shared -- with-pdo-sqlite=shared --with-sqlite=shared --with-xml --with-ldap=/ usr --enable-cli --with-zlib-dir=/usr --enable-exif --enable-ftp -- enable-mbstring --enable-mbregex --enable-dbx --enable-sockets --with- iodbc=/usr --with-curl=/usr --with-apxs2=/apache2/bin/apxs Regards, Mike - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: Re[2]: [sqlite] A lillte help adding sqlite to a c program
I am still having some problems compiling my code. I suspect it maybe something to do with my app not linking (ld) with sqlite libraries, but I do not know how to do this or it could be something else. Can you advise? - /usr/src/logger-1.2-beta3/src/logger.cpp:731: undefined reference to `sqlite3_open' logger.o(.text+0xc99):/usr/src/logger-1.2-beta3/src/logger.cpp:732: undefined reference to `sqlite3_exec' logger.o(.text+0xcae):/usr/src/logger-1.2-beta3/src/logger.cpp:735: undefined reference to `sqlite3_free' logger.o(.text+0xcbf):/usr/src/logger-1.2-beta3/src/logger.cpp:737: undefined reference to `sqlite3_close' collect2: ld returned 1 exit status make: *** [logger] Error 1 - Original Message - From: [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Sunday, September 10, 2006 1:32 PM Subject: Re: Re[2]: [sqlite] A lillte help adding sqlite to a c program On Sun, 10 Sep 2006 13:02:58 +0100, you wrote: Thanks Teg, I have been trying to do using a basic example but still running into problems. Can you expand a little more on your example below. This is where I am so far. I am getting a bit closer bit still getting some errors. clearly I am still getting something wrong with the format of snprintf. any ideas? Hello Lloydie If you use a MS Compiler like VC++ 6 or VC++ 7 Net you can look at my HP. There is a little Example and a Wrapperclass simple to understand, desgined to handle small Databases. http://www.thlu.de Best Regards Thomas - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: Re[2]: [sqlite] A lillte help adding sqlite to a c program
Thanks Teg,
I have been trying to do using a basic example but still
running into problems. Can you expand a little more on your example below.
This is where I am so far.
I am getting a bit closer bit still getting some errors. clearly I am still
getting something wrong with the format of snprintf. any ideas?
errors:
logger.cpp: In function `void* start_one_channel(void*)':
logger.cpp:607: error: invalid conversion from `const char*' to `size_t'
logger.cpp:607: error: initializing argument 2 of `int snprintf(char*,
size_t, const char*, ...)'
logger.cpp:629: error: jump to case label
logger.cpp:603: error: crosses initialization of `char*zErrMsg'
make: *** [logger.o] Error 1
---
code:-
char *zErrMsg = 0;
char *sql;
int rc;
sqlite3 *db;
snprintf(sql, insert into call_data (direction, call_time, dest,
trunk_no, file_name)values(\'%s\',\'%s\',\'%s\',\'%s\',\'%s\'),
details.inout, details.statime,details.cidn,
details.channel,details.filename);//line 607
rc = sqlite3_open(/var/tmp/logger/database/logger.db, db); //line
608
rc = sqlite3_exec(db, sql, NULL, 0, zErrMsg);
if( rc!=SQLITE_OK ){
//fprintf(stderr, SQL error: %s\n, zErrMsg);
sqlite3_free(zErrMsg);
}
sqlite3_close(db);
---
Lloydie T
- Original Message -
From: Teg [EMAIL PROTECTED]
To: Lloyd Thomas sqlite-users@sqlite.org
Sent: Sunday, September 10, 2006 12:47 AM
Subject: Re[2]: [sqlite] A lillte help adding sqlite to a c program
Hello Lloyd,
You need to escape the quotes. Remember in C and C++ means the
beginning or end of a literal string so, when you want to embed quotes
in a string you have to escape them. Probaby \.
You'd be better off using the paramaterized version of the SQL
std::string sql = insert into call_data (direction, call_time, dest,
trunk_no,
file_name)values(?,?,?,?,?);;
Then bind the actual parameters after the fact.
C
Saturday, September 9, 2006, 7:02:43 PM, you wrote:
LT Jay, Thanks for your reply.
LT I gave it a try with and got a few errors. as follows
LT ---
LT logger.cpp:609: error: invalid operands of types `const char[80]' and
LT `char[4]' to binary `operator+'
LT logger.cpp:615: error: `t' was not declared in this scope
LT logger.cpp:615: warning: unused variable 't'
LT logger.cpp:634: error: jump to case label
LT logger.cpp:631: error: crosses initialization of `std::string test2'
LT logger.cpp:628: error: crosses initialization of `std::string test1'
LT logger.cpp:637: error: jump to case label
LT logger.cpp:631: error: crosses initialization of `std::string test2'
LT logger.cpp:628: error: crosses initialization of `std::string test1'
LT logger.cpp:638: error: jump to case label
LT logger.cpp:631: error: crosses initialization of `std::string test2'
LT logger.cpp:628: error: crosses initialization of `std::string test1'
LT logger.cpp:639: error: jump to case label
LT logger.cpp:631: error: crosses initialization of `std::string test2'
LT logger.cpp:628: error: crosses initialization of `std::string test1'
LT logger.cpp:641: error: `t' was not declared in this scope
LT logger.cpp:641: warning: unused variable 't'
LT logger.cpp:664: error: jump to case label
LT logger.cpp:621: error: crosses initialization of `bool Loop'
LT logger.cpp:634: warning: destructor needed for `test2'
LT logger.cpp:634: warning: where case label appears here
LT logger.cpp:634: warning: (enclose actions of previous case statements
LT requiring destructors in their own scope.)
LT logger.cpp:637: warning: destructor needed for `test2'
LT logger.cpp:637: warning: where case label appears here
LT logger.cpp:638: warning: destructor needed for `test2'
LT logger.cpp:638: warning: where case label appears here
LT logger.cpp:639: warning: destructor needed for `test2'
LT logger.cpp:639: warning: where case label appears here
LT make: *** [logger.o] Error 1
LT --
LT line 609 =
LT sql = insert into call_data (direction, call_time, dest, trunk_no,
LT
file_name)values('+details.inout+','+details.statime+','+details.cidn+'+details.channel+','+details.filename+');
LT - Original Message -
LT From: Jay Sprenkle [EMAIL PROTECTED]
LT To: sqlite-users@sqlite.org
LT Sent: Saturday, September 09, 2006 11:16 PM
LT Subject: Re: [sqlite] A lillte help adding sqlite to a c program
On 9/9/06, Lloyd Thomas [EMAIL PROTECTED] wrote:
I know nothing of C++ and therefore need a lilte help editing a C++ app
to
insert some records into a database.
here's an example to read from a database.
If you build the sql like you're doing and you use it on the web you
leave yourself
open to sql injection attacks. Using the bind
Re: Re[2]: [sqlite] A lillte help adding sqlite to a c program
Nearly there just a couple of outstanding issues any help apreciated
Error:--
make: Warning: File `logger.cpp' has modification time 45 s in the future
g++ -c -Wall -O -g logger.cpp -o logger.o
logger.cpp: In function `void* start_one_channel(void*)':
logger.cpp:607: warning: format argument is not a pointer (arg 6)
logger.cpp:629: error: jump to case label
logger.cpp:603: error: crosses initialization of `char*zErrMsg'
make: *** [logger.o] Error 1
code sample:---
char *zErrMsg = 0;
char sql[200];
int rc;
sqlite3 *db;
sprintf(sql, insert into call_data (direction, call_time, dest,
trunk_no, file_name)values('%s','%s','%s','%s','%s'), details.inout,
details.statime,details.cidn,channel,details.filename);
rc = sqlite3_open(/var/tmp/logger/database/logger.db, db);
rc = sqlite3_exec(db, sql, NULL, 0, zErrMsg);
if( rc!=SQLITE_OK ){
//fprintf(stderr, SQL error: %s\n, zErrMsg);
sqlite3_free(zErrMsg);
}
sqlite3_close(db);
LOGIT([%02d] sql string = %s\n,channel,sql);
// Clear out details ready for next call
details.inout[0] = 0;
details.filename[0] = 0;
details.statime[0] = 0;
details.endtime[0] = 0;
details.cidn[0] = 0;
details.cidt[0] = 0;
strcpy(details.term, );
break;
default: // Any thing else ??
break;
}
break;
-
- Original Message -
From: [EMAIL PROTECTED]
To: sqlite-users@sqlite.org
Sent: Sunday, September 10, 2006 1:32 PM
Subject: Re: Re[2]: [sqlite] A lillte help adding sqlite to a c program
On Sun, 10 Sep 2006 13:02:58 +0100, you wrote:
Thanks Teg,
I have been trying to do using a basic example but still
running into problems. Can you expand a little more on your example below.
This is where I am so far.
I am getting a bit closer bit still getting some errors. clearly I am still
getting something wrong with the format of snprintf. any ideas?
Hello Lloydie
If you use a MS Compiler like VC++ 6 or VC++ 7 Net you can look at
my HP. There is a little Example and a Wrapperclass simple to
understand, desgined to handle small Databases.
http://www.thlu.de
Best Regards
Thomas
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] A lillte help adding sqlite to a c program
I know nothing of C++ and therefore need a lilte help editing a C++ app to insert some records into a database. This is where I am so far #include sqlite.h sqlite *db; //insert record into database db=sqlite_open(/var/tmp/logger/database/logger.db, 0, NULL); query = insert into call_data; query += (direction, call_time, dest, trunk_no, file_name)values(; query += 'details.inout','details.statime','details.cidn','details.channel','details.filename; sqlite_exec(db, query, NULL, NULL, NULL); sqlite_close(db); -- Can someone tell me if I am going in the right direction. Sorry for being so lazy,but I need to get something working quick. Lloydie T - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] A lillte help adding sqlite to a c program
Jay, Thanks for your reply.
I gave it a try with and got a few errors. as follows
---
logger.cpp:609: error: invalid operands of types `const char[80]' and
`char[4]' to binary `operator+'
logger.cpp:615: error: `t' was not declared in this scope
logger.cpp:615: warning: unused variable 't'
logger.cpp:634: error: jump to case label
logger.cpp:631: error: crosses initialization of `std::string test2'
logger.cpp:628: error: crosses initialization of `std::string test1'
logger.cpp:637: error: jump to case label
logger.cpp:631: error: crosses initialization of `std::string test2'
logger.cpp:628: error: crosses initialization of `std::string test1'
logger.cpp:638: error: jump to case label
logger.cpp:631: error: crosses initialization of `std::string test2'
logger.cpp:628: error: crosses initialization of `std::string test1'
logger.cpp:639: error: jump to case label
logger.cpp:631: error: crosses initialization of `std::string test2'
logger.cpp:628: error: crosses initialization of `std::string test1'
logger.cpp:641: error: `t' was not declared in this scope
logger.cpp:641: warning: unused variable 't'
logger.cpp:664: error: jump to case label
logger.cpp:621: error: crosses initialization of `bool Loop'
logger.cpp:634: warning: destructor needed for `test2'
logger.cpp:634: warning: where case label appears here
logger.cpp:634: warning: (enclose actions of previous case statements
requiring destructors in their own scope.)
logger.cpp:637: warning: destructor needed for `test2'
logger.cpp:637: warning: where case label appears here
logger.cpp:638: warning: destructor needed for `test2'
logger.cpp:638: warning: where case label appears here
logger.cpp:639: warning: destructor needed for `test2'
logger.cpp:639: warning: where case label appears here
make: *** [logger.o] Error 1
--
line 609 =
sql = insert into call_data (direction, call_time, dest, trunk_no,
file_name)values('+details.inout+','+details.statime+','+details.cidn+'+details.channel+','+details.filename+');
- Original Message -
From: Jay Sprenkle [EMAIL PROTECTED]
To: sqlite-users@sqlite.org
Sent: Saturday, September 09, 2006 11:16 PM
Subject: Re: [sqlite] A lillte help adding sqlite to a c program
On 9/9/06, Lloyd Thomas [EMAIL PROTECTED] wrote:
I know nothing of C++ and therefore need a lilte help editing a C++ app
to
insert some records into a database.
here's an example to read from a database.
If you build the sql like you're doing and you use it on the web you
leave yourself
open to sql injection attacks. Using the bind() method eliminates that
vulnerability.
Something to consider.
Jay
Here's some example code:
sqlite3*db;
// connect to database
if ( sqlite3_open( test.db, db ) )
throw Can't open database;
char* sql;
sql = SELECT one.test1, two.test2
FROM one
INNER JOIN two ON one.id = two.id
;
sqlite3_stmt* pStmt;
if ( sqlite3_prepare( db, sql, strlen(sql), pStmt, NULL ) != SQLITE_OK )
{
string str = Cannot prepare sql: ;
str += sql[t];
str += , Error: ;
str += sqlite3_errmsg(db);
throw str.c_str();
}
bool Loop = true;
while ( Loop )
switch ( sqlite3_step( pStmt ) )
{
case SQLITE_ROW:
// retrieve the results
char* p = (char *) sqlite3_column_text( pStmt, 0 );
string test1 = string( p ? p : );
p = (char *) sqlite3_column_text( pStmt, 1 );
string test2 = string( p ? p : );
break;
case SQLITE_DONE:
Loop = false;
break;
case SQLITE_BUSY:
case SQLITE_LOCKED:
default:
string str = Cannot execute sql: ;
str += sql[t];
str += , Error: ;
str += sqlite3_errmsg(db);
throw str.c_str();
break;
}
// clean up when finished
sqlite3_finalize( pStmt );
sqlite3_close( db );
--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite
Cthulhu Bucks!
http://www.cthulhubucks.com
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: Re[2]: [sqlite] A lillte help adding sqlite to a c program
if I could somehow create the following string as a char i cpuld probably
get the code to work.
sql = insert into call_data (direction, call_time, dest,
trunk_no, file_name)values(';
sql += details.inout;
sql += ',';
sql += details.statime;
sql += ',';
sql += details.cidn;
sql += ';
sql +=details.channel;
sql += ',';
sql += details.filename;
sql += ');
I am getting stuck adding exist chars to the sql char
defined chars
^^
sql = insert into call_data(direction,
call_time)values('details.inout','details.statime)
Do you have any ideas?
Lloydie T
- Original Message -
From: Teg [EMAIL PROTECTED]
To: Lloyd Thomas sqlite-users@sqlite.org
Sent: Sunday, September 10, 2006 12:47 AM
Subject: Re[2]: [sqlite] A lillte help adding sqlite to a c program
Hello Lloyd,
You need to escape the quotes. Remember in C and C++ means the
beginning or end of a literal string so, when you want to embed quotes
in a string you have to escape them. Probaby \.
You'd be better off using the paramaterized version of the SQL
std::string sql = insert into call_data (direction, call_time, dest,
trunk_no,
file_name)values(?,?,?,?,?);;
Then bind the actual parameters after the fact.
C
Saturday, September 9, 2006, 7:02:43 PM, you wrote:
LT Jay, Thanks for your reply.
LT I gave it a try with and got a few errors. as follows
LT ---
LT logger.cpp:609: error: invalid operands of types `const char[80]' and
LT `char[4]' to binary `operator+'
LT logger.cpp:615: error: `t' was not declared in this scope
LT logger.cpp:615: warning: unused variable 't'
LT logger.cpp:634: error: jump to case label
LT logger.cpp:631: error: crosses initialization of `std::string test2'
LT logger.cpp:628: error: crosses initialization of `std::string test1'
LT logger.cpp:637: error: jump to case label
LT logger.cpp:631: error: crosses initialization of `std::string test2'
LT logger.cpp:628: error: crosses initialization of `std::string test1'
LT logger.cpp:638: error: jump to case label
LT logger.cpp:631: error: crosses initialization of `std::string test2'
LT logger.cpp:628: error: crosses initialization of `std::string test1'
LT logger.cpp:639: error: jump to case label
LT logger.cpp:631: error: crosses initialization of `std::string test2'
LT logger.cpp:628: error: crosses initialization of `std::string test1'
LT logger.cpp:641: error: `t' was not declared in this scope
LT logger.cpp:641: warning: unused variable 't'
LT logger.cpp:664: error: jump to case label
LT logger.cpp:621: error: crosses initialization of `bool Loop'
LT logger.cpp:634: warning: destructor needed for `test2'
LT logger.cpp:634: warning: where case label appears here
LT logger.cpp:634: warning: (enclose actions of previous case statements
LT requiring destructors in their own scope.)
LT logger.cpp:637: warning: destructor needed for `test2'
LT logger.cpp:637: warning: where case label appears here
LT logger.cpp:638: warning: destructor needed for `test2'
LT logger.cpp:638: warning: where case label appears here
LT logger.cpp:639: warning: destructor needed for `test2'
LT logger.cpp:639: warning: where case label appears here
LT make: *** [logger.o] Error 1
LT --
LT line 609 =
LT sql = insert into call_data (direction, call_time, dest, trunk_no,
LT
file_name)values('+details.inout+','+details.statime+','+details.cidn+'+details.channel+','+details.filename+');
LT - Original Message -
LT From: Jay Sprenkle [EMAIL PROTECTED]
LT To: sqlite-users@sqlite.org
LT Sent: Saturday, September 09, 2006 11:16 PM
LT Subject: Re: [sqlite] A lillte help adding sqlite to a c program
On 9/9/06, Lloyd Thomas [EMAIL PROTECTED] wrote:
I know nothing of C++ and therefore need a lilte help editing a C++ app
to
insert some records into a database.
here's an example to read from a database.
If you build the sql like you're doing and you use it on the web you
leave yourself
open to sql injection attacks. Using the bind() method eliminates that
vulnerability.
Something to consider.
Jay
Here's some example code:
sqlite3*db;
// connect to database
if ( sqlite3_open( test.db, db ) )
throw Can't open database;
char* sql;
sql = SELECT one.test1, two.test2
FROM one
INNER JOIN two ON one.id = two.id
;
sqlite3_stmt* pStmt;
if ( sqlite3_prepare( db, sql, strlen(sql), pStmt, NULL ) !=
SQLITE_OK )
{
string str = Cannot prepare sql: ;
str += sql[t];
str += , Error: ;
str += sqlite3_errmsg(db);
throw str.c_str();
}
bool Loop = true;
while ( Loop )
switch ( sqlite3_step( pStmt ) )
{
case
[sqlite] Wierd between results
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) as num_rows WHERE ring_time 6 and ring_time 10; Also gives 0 results. Where am I going wrong? sqlite 3.2.8 windows XP Lloydie-T
Re: [sqlite] Wierd between results
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: sqlite-users@sqlite.org Sent: Saturday, March 25, 2006 3:48 PM Subject: [sqlite] Wierd between results 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) as num_rows WHERE ring_time 6 and ring_time 10; Also gives 0 results. Where am I going wrong? sqlite 3.2.8 windows XP Lloydie-T
Re: [sqlite] Wierd between results
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: Lloyd Thomas [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Saturday, March 25, 2006 4:14 PM Subject: Re: [sqlite] Wierd between results 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: sqlite-users@sqlite.org Sent: Saturday, March 25, 2006 3:48 PM Subject: [sqlite] Wierd between results 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) as num_rows WHERE ring_time 6 and ring_time 10; Also gives 0 results. Where am I going wrong? sqlite 3.2.8 windows XP Lloydie-T
Re: [sqlite] Wierd between results
Yeah I did. sorry about the rushed typing. DRH managed to sort it out for me. I had imported a ver 2 database to version 3 and changed a time values to integers, but forgot to change the column to integer. Lloydie-T - Original Message - From: Kurt Welgehausen [EMAIL PROTECTED] To: sqlite-users@sqlite.org 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 actual queries, didn't you? select * from tbl; t x y -- -- -- 1 100 101 2 100 102 3 100 103 4 100 103 5 200 210 6 200 220 7 200 230 8 300 199 select count(t) from tbl where y=103; count(t) -- 2 select count(t) from tbl where y between 102 and 210; count(t) -- 5 Regards
[sqlite] Help with multiple join
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 WHERE C.direction = 'Out' AND C.extn_no IN (select extn_no FROM grp_user) GROUP BY C.call_id, C.extn_no, C.dest, U.group_name table data call_data table call_id |extn_no|dest | 1 |201 |012087562978| 2 |201 |079087562879| 3 |200 |018657562072| 4 |203 |018693263202| user_grp table group_id|group_name | 1 |ideal CT | grp_user table group_id|extn_no| 1 |200 | 1 |201 | Lloydie T
Re: [sqlite] Building sqlite 3.2.8 on redhat 9 (off list)
Thanks Kimball Your right about having a little experience. I am just running into error after error installing apps, but I'm learnoing slowly. Anyway, I was trying to install the sqlite support for another application I want to use and as a side issue it seems to 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 Subject: Re: [sqlite] Building sqlite 3.2.8 on redhat 9 (off list) Lloyd Thomas wrote: 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. Hello Lloyd, my reply may be a bit too detailed, but I assume you have very little experience with Linux/UNIX. So bare with me if the explanations are too basic. What about /usr/local/lib? That is the usual place for packages/libraries that are not part of the operating system. You can print the current setting of LD_LIBRARY_PATH by typing: echo $LD_LIBRARY_PATH on the prompt. It is a list of directories that the loader/linker will look at in search of shared objects. The sqlite3 library must be contained in any of these. If you find it in a different directory not listed there, you need to set LD_LIBRARY_PATH: export LD_LIBRARY_PATH=/your/new/directory:$LD_LIBRARY_PATH or: setenv LD_LIBRARY_PATH /your/new/directory:$LD_LIBRARY_PATH (Presumably the first, the second form is specific to the C-shell, and you probably have bash.) What directory (the prefix option) did you use for installing SQLite? That is the directory where the library will live in (or in the subdirectory lib of that directory). Hope this helps. Regards, Arjen
Re: [sqlite] Building sqlite 3.2.8 on redhat 9 (off list)
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: sqlite-users@sqlite.org Sent: Tuesday, January 03, 2006 1:29 PM Subject: Re: [sqlite] Building sqlite 3.2.8 on redhat 9 (off list) Lloyd Thomas [EMAIL PROTECTED] wrote: Thanks Kimball Your right about having a little experience. I am just running into error after error installing apps, but I'm learnoing slowly. Anyway, I was trying to install the sqlite support for another application I want to use and as a side issue it seems to 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. Did the precompiled binary on the website not work for you? http://www.sqlite.org/download.html -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] Building sqlite 3.2.8 on redhat 9
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 - From: Dan Kennedy [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Monday, January 02, 2006 5:37 AM Subject: Re: [sqlite] Building sqlite 3.2.8 on redhat 9 If possible, the easiest way around this is to install Active-tcl. Or compile the tcl library yourself. For a long time the stock tcl install in redhat was problematic. http://www.activestate.com/Products/ActiveTcl/ --- Lloyd Thomas [EMAIL PROTECTED] wrote: 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.8]# make ./libtool --mode=compile cc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I./src -DNDEBUG -DTHREADSAFE=0 -DSQLITE_OMIT_CURSOR -c ./src/tclsqlite.c gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I./src -DNDEBUG -DTHREADSAFE=0 -DSQLITE_OMIT_CURSOR -c ./src/tclsqlite.c -fPIC -DPIC -o .libs/tclsqlite.o src/tclsqlite.c: In function `tclSqlFunc': src/tclsqlite.c:372: warning: passing arg 1 of `Tcl_NewByteArrayObj' discards qualifiers from pointer target type src/tclsqlite.c:380: warning: assignment makes pointer from integer without a cast src/tclsqlite.c:438: `Tcl_WideInt' undeclared (first use in this function) src/tclsqlite.c:438: (Each undeclared identifier is reported only once src/tclsqlite.c:438: for each function it appears in.) src/tclsqlite.c:438: parse error before v src/tclsqlite.c:439: `v' undeclared (first use in this function) src/tclsqlite.c: In function `DbObjCmd': src/tclsqlite.c:636: warning: passing arg 3 of `Tcl_GetIndexFromObj' from incompatible pointer type src/tclsqlite.c:1252: warning: passing arg 2 of `Tcl_GetVar2Ex' discards qualifiers from pointer target type src/tclsqlite.c:1274: `Tcl_WideInt' undeclared (first use in this function) src/tclsqlite.c:1274: parse error before v src/tclsqlite.c:1275: `v' undeclared (first use in this function) src/tclsqlite.c:1325: warning: passing arg 1 of `Tcl_NewByteArrayObj' discards qualifiers from pointer target type src/tclsqlite.c:1333: warning: assignment makes pointer from integer without a cast src/tclsqlite.c:1773: warning: passing arg 3 of `Tcl_GetIndexFromObj' from incompatible pointer type src/tclsqlite.c: In function `DbMain': src/tclsqlite.c:1918: warning: passing arg 2 of `Tcl_CreateObjCommand' discards qualifiers from pointer target type make: *** [tclsqlite.lo] Error 1 -- __ Yahoo! for Good - Make a difference this year. http://brand.yahoo.com/cybergivingweek2005/
Re: [sqlite] Building sqlite 3.2.8 on redhat 9
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: sqlite-users@sqlite.org Sent: Monday, January 02, 2006 12:13 PM Subject: Re: [sqlite] Building sqlite 3.2.8 on redhat 9 Lloyd Thomas wrote: 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' That seems a common problem with shared objects/libraries ... they have to be in the path of the dynamic linker/loader. Have you set LD_LIBRARY_PATH properly? Regards, Arjen
[sqlite] Building sqlite 3.2.8 on redhat 9
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.8]# make ./libtool --mode=compile gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I./src -DNDEBUG -DTHREADSAFE=0 -DSQLITE_OMIT_CURSOR -c ./src/tclsqlite.c gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I./src -DNDEBUG -DTHREADSAFE=0 -DSQLITE_OMIT_CURSOR -c ./src/tclsqlite.c -fPIC -DPIC -o .libs/tclsqlite.o src/tclsqlite.c: In function `tclSqlFunc': src/tclsqlite.c:372: warning: passing arg 1 of `Tcl_NewByteArrayObj' discards qualifiers from pointer target type src/tclsqlite.c:380: warning: assignment makes pointer from integer without a cast src/tclsqlite.c:438: `Tcl_WideInt' undeclared (first use in this function) src/tclsqlite.c:438: (Each undeclared identifier is reported only once src/tclsqlite.c:438: for each function it appears in.) src/tclsqlite.c:438: parse error before v src/tclsqlite.c:439: `v' undeclared (first use in this function) src/tclsqlite.c: In function `DbObjCmd': src/tclsqlite.c:636: warning: passing arg 3 of `Tcl_GetIndexFromObj' from incompatible pointer type src/tclsqlite.c:1252: warning: passing arg 2 of `Tcl_GetVar2Ex' discards qualifiers from pointer target type src/tclsqlite.c:1274: `Tcl_WideInt' undeclared (first use in this function) src/tclsqlite.c:1274: parse error before v src/tclsqlite.c:1275: `v' undeclared (first use in this function) src/tclsqlite.c:1325: warning: passing arg 1 of `Tcl_NewByteArrayObj' discards qualifiers from pointer target type src/tclsqlite.c:1333: warning: assignment makes pointer from integer without a cast src/tclsqlite.c:1773: warning: passing arg 3 of `Tcl_GetIndexFromObj' from incompatible pointer type src/tclsqlite.c: In function `DbMain': src/tclsqlite.c:1918: warning: passing arg 2 of `Tcl_CreateObjCommand' discards qualifiers from pointer target type make: *** [tclsqlite.lo] Error 1 --
Re: [sqlite] tricky date time problem
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: sqlite-users@sqlite.org Sent: Thursday, February 24, 2005 1:31 AM Subject: Re: [sqlite] tricky date time problem On Wed, 23 Feb 2005 23:12:41 -, Lloyd Thomas [EMAIL PROTECTED] wrote: 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. Lloyd, You can force a zero record if there no events using a union like this: select minutes.i as minute, count(*) as events from (select (event_time / 60) % 60 as begin_minute, ((event_time + duration) / 60) % 60 as end_minute from event_data) outer join integers60 as minutes where case when begin_minute = 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 union select 0, 0 where not exists (select * from event_data) This will give a single row with a count of zero if there are no events, or the usual set of 60 rows if there are one or more events. Dennis Cote
Re: [sqlite] tricky date time problem
Still no joy. I have tried to use the % operator but I do not get the expected results. Can some one point me in the right direction? I have simplified my query to test Select 3600 %60 as seconds, count (event_id) from event_data WHERE event_time = 1081331940 and event_time-duration = 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: sqlite-users@sqlite.org Sent: Tuesday, February 22, 2005 11:41 PM Subject: Re: [sqlite] tricky date time problem Thanks Jay/DRH, this looks more promising (The % operator gives you remainder after division). Still not sure how I could apply it to start and end unix times. The columns I have are :- hour start = start time of query for event (unix time) hour end = end time of query for event (unix time) event time = start time of event(unix time) duration = duration of event(seconds) So I need to know the amount of simultaneous events durring each minute. I can use 'SELECT(hour_end - hour_start)%60 as seconds, count(event_id) AS event_num' to give me my minute intervals To make sure that the events are within the events 'WHERE event_time = hour_start AND event_time-duration = hour end' then compare wether seconds fall in between the event start and end 'AND seconds BETWEEN (event_time-hour_start)%60 AND (event_time-hour_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 [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Tuesday, February 22, 2005 2:47 PM Subject: Re: [sqlite] tricky date time problem --- D. Richard Hipp [EMAIL PROTECTED] wrote: It's too bad Sqlite doesn't have the modulo operator, The % operator gives you remainder after division, which is very close to being a modulo operator. Thanks! I went looking for the page in the documentation about expressions to see what math sqlite supported. I couldn't find it and assumed it didn't have all the math functions. Lloyd: Since it does have this operator please disregard my example showing how to create a user defined function. You can do what you want by using : select tm % 60 as second, count(*) from your_table Group by second The tm field must contain the time in seconds of your event. You'll get up to 60 result rows with 0 - 59. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] tricky date time problem
I have two tables the event table which holds the data and a table which has an entry for each minute CREATE TABLE event_data ( call_id INTEGER PRIMARY KEY, desc varchar(32) NOT NULL default '', event_time datetime default NULL, stamptime integer default NULL, duration integer default NULL ); 'stamptime' is the unix time (Number of seconds since 1970) representation of 'event_time' 'duration' is the duration of the event in seconds CREATE TABLE integers60 ( i INTEGER ); So I was hoping to us the query the integers table and use the results 'i' as a datatime modifier in the event_data table. Not much luck though. - Original Message - From: D. Richard Hipp [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Wednesday, February 23, 2005 12:26 PM Subject: Re: [sqlite] tricky date time problem On Mon, 2005-02-21 at 21:49 +, Lloyd Thomas wrote: I have a query which calculates the number of events during an hour by the minute. It needs to work out which minute has the most events and the average events during that hour. So it should return an array of 60 results for an hour where I can use the MAX() feature in php to find the peak minute. How are you storing time values? Julian day number/ Number of seconds since 1970? IS09601? And what is the schema for the table you are querying? -- D. Richard Hipp [EMAIL PROTECTED]
Re: [sqlite] tricky date time problem
Now I am confused. Jay, I tried your solution but I still end up with one result 0|14 I need to now the result for each minute(even if null) during the hour. Dennis, I tried yours and ended up with much the same thing 0|4 this is the contents of the table between 2004-04-07 10:00:00 and 2004-04-07 10:59:59 call_id|event_time|duration 7|1081332060|129 8|1081332540|208 10|1081332900|180 11|108180|44 12|1081333500|27 13|108180|229 14|1081334280|0 15|1081334280|0 16|1081335120|11 17|1081335120|0 18|1081335360|40 19|1081335420|46 20|1081334940|719 23|1081334460|1802 Maybe this data may indicate where I am going wrong. Lloud - Original Message - From: Jay [EMAIL PROTECTED] To: sqlite-users@sqlite.org; [EMAIL PROTECTED] Sent: Wednesday, February 23, 2005 5:39 PM Subject: Re: [sqlite] tricky date time problem --- Lloyd Thomas [EMAIL PROTECTED] wrote: Thanks Jay/DRH, this looks more promising (The % operator gives you remainder after division). Still not sure how I could apply it to start and end unix times. The columns I have are :- hour start = start time of query for event (unix time) hour end = end time of query for event (unix time) event time = start time of event(unix time) duration = duration of event(seconds) The modulo concept is simple: unix time = number of seconds (since 1970 if I remember correctly, but that's not important) If you divide the start time, ie. the number of seconds, by 60 and keep the reminder it tells you the n-th second during each minute when the event occurred. Which was exactly what I thought you were looking for. for example: event 1 happens at 100 seconds. event 2 happens at 112 seconds. event 3 happens at 123 seconds. event 4 happens at 183 seconds. 100 % 60 = 40 ( 1 minute 40 seconds ) 112 % 60 = 52 ( 1 minute 52 seconds ) 123 % 60 = 3 ( 2 minutes 3 seconds ) 183 % 60 = 3 ( 3 minutes 3 seconds ) select hour_start % 60 as seconds, count(*) as count from your_table group by seconds gives this: secondscount 3 2 40 1 52 1 I hope that's what you wanted! __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] tricky date time problem
Dennis, Revisted your query. I was being a bit lazy. changed 'select minute, max(events)' to 'select minute, events' and added 'order by minute' to give me what I needed. Thanks - Original Message - From: Dennis Cote [EMAIL PROTECTED] To: sqlite-users sqlite-users@sqlite.org 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 (which drops the remainder), but I'm actually keeping the reminader and throwing away the quotient so it was simply wrong. select minute, max(events) from (select minutes.i as minute, count(*) as events from (select (event_time / 60) % 60 as begin_minute, ((event_time + duration) / 60) % 60 as end_minute from event_data) outer join integers60 as minutes where case when begin_minute = 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
Re: [sqlite] tricky date time problem
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 - From: Dennis Cote [EMAIL PROTECTED] To: sqlite-users sqlite-users@sqlite.org Sent: Wednesday, February 23, 2005 8:51 PM Subject: Re: [sqlite] tricky date time problem 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 the way I wanted it to. This is a common problem with all SQL aggregate functions. They generate tables with their won rows, they don't return rows fromt the table they are summarizing. To do what I want you need to build a temp table or recalculate the event count table twice (because SQLite doesn't support named subqueries). You then need to find all the minutes with counts that match the maximum value because there could be more that one minute with the same count value. Using a temp table: create temp table t as select minutes.i as minute, count(*) as events from (select (event_time / 60) % 60 as begin_minute, ((event_time + duration) / 60) % 60 as end_minute from event_data) outer join integers60 as minutes where case when begin_minute = 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 select minute, events from t where events = (select max(events) from t) order by minute drop table t Or using repeated sub-queries (basically replace each instance of t in the above query with the entire query used to build the event count table): select minute, events from ( select minutes.i as minute, count(*) as events from (select (event_time / 60) % 60 as begin_minute, ((event_time + duration) / 60) % 60 as end_minute from event_data) outer join integers60 as minutes where case when begin_minute = 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 ) where events = (select max(events) from ( select minutes.i as minute, count(*) as events from (select (event_time / 60) % 60 as begin_minute, ((event_time + duration) / 60) % 60 as end_minute from event_data) outer join integers60 as minutes where case when begin_minute = 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 )) order by minute The second has the advantage that it is a single (complex) query, but neither is really pretty, so perhaps using PHP isn't such a bad idea. P.S. Richard, this is another example of where named subqueies and the WITH clause allow more efficient SQL to be written. This could be stated as below if this feature was supported by SQLite. with event_counts as ( select minutes.i as minute, count(*) as events from (select (event_time / 60) % 60 as begin_minute, ((event_time + duration) / 60) % 60 as end_minute from event_data) outer join integers60 as minutes where case when begin_minute = 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 ) select minute, events from event_counts where events = (select max(events) from event_counts) order by minute
Re: [sqlite] tricky date time problem
Jay, --- |You could select and group by the modulo of the seconds of each| |date and get your nice groupings very simply. If you can write a| |user defined function in whatever language you're using | |you might try that. | --- How would you do that in C? - Original Message - From: Jay [EMAIL PROTECTED] To: sqlite-users@sqlite.org; [EMAIL PROTECTED] Sent: Tuesday, February 22, 2005 2:00 AM Subject: Re: [sqlite] tricky date time problem --- Lloyd Thomas [EMAIL PROTECTED] wrote: I have a query which calculates the number of events during an hour by the minute. It needs to work out which minute has the most events and the average events during that hour. So it should return an array of 60 results for an hour where I can use the MAX() feature in php to find the peak It's too bad Sqlite doesn't have the modulo operator, you could select and group by the modulo of the seconds of each date and get your nice groupings very simply. If you can write a user defined function in whatever language you're using you might try that. __ Do you Yahoo!? The all-new My Yahoo! - What will yours do? http://my.yahoo.com
Re: [sqlite] tricky date time problem
Thanks Jay/DRH, this looks more promising (The % operator gives you remainder after division). Still not sure how I could apply it to start and end unix times. The columns I have are :- hour start = start time of query for event (unix time) hour end = end time of query for event (unix time) event time = start time of event(unix time) duration = duration of event(seconds) So I need to know the amount of simultaneous events durring each minute. I can use 'SELECT(hour_end - hour_start)%60 as seconds, count(event_id) AS event_num' to give me my minute intervals To make sure that the events are within the events 'WHERE event_time = hour_start AND event_time-duration = hour end' then compare wether seconds fall in between the event start and end 'AND seconds BETWEEN (event_time-hour_start)%60 AND (event_time-hour_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 [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Tuesday, February 22, 2005 2:47 PM Subject: Re: [sqlite] tricky date time problem --- D. Richard Hipp [EMAIL PROTECTED] wrote: It's too bad Sqlite doesn't have the modulo operator, The % operator gives you remainder after division, which is very close to being a modulo operator. Thanks! I went looking for the page in the documentation about expressions to see what math sqlite supported. I couldn't find it and assumed it didn't have all the math functions. Lloyd: Since it does have this operator please disregard my example showing how to create a user defined function. You can do what you want by using : select tm % 60 as second, count(*) from your_table Group by second The tm field must contain the time in seconds of your event. You'll get up to 60 result rows with 0 - 59. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[sqlite] tricky date time problem
I have a query which calculates the number of events during an hour by the
minute. It needs to work out which minute has the most events and the
average events during that hour. So it should return an array of 60 results
for an hour where I can use the MAX() feature in php to find the peak
minute.
currently the query looks like this:
SELECT event_id, intdur FROM event_data where event_time =
datetime('2004-04-07 00:00:00', '+ i minutes')
AND datetime('2004-04-07 00:00:00', '+ i minutes', '- intdur seconds') =
event_time;
but it takes an age to work as I have to run the query 60 times for each
hour
I was thinking of creating a seperate table with a row for each minute and
then run a query against that table using the minute integer as part of a
sub-select against the event table. I have tried but can't get it to work.
so far I get an error: only a single result allowed for a SELECT that is
part of an expression
--
SELECT count(i) from integers60 WHERE (SELECT event_id, intdur from
event_data where event_time = datetime('2004-04-07 10:00:00', '+ i
minutes') AND datetime('2004-04-07 10:00:00', '+ i minutes', '+ intdur) =
event_time)
Can some one help?
Lloyd
Re: [sqlite] tricky date time problem
I am trying to simplify my query but seem to be failing at the first hurdle.
While if I do
select * from event_id from eveny_data where event_time between '2004-04-07
10:00:00' and '2004-04-07 10:59:59';
returns 15 rows
select * from event_id from eveny_data where event_time 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: sqlite-users@sqlite.org
Sent: Monday, February 21, 2005 9:49 PM
Subject: [sqlite] tricky date time problem
I have a query which calculates the number of events during an hour by the
minute. It needs to work out which minute has the most events and the
average events during that hour. So it should return an array of 60
results for an hour where I can use the MAX() feature in php to find the
peak minute.
currently the query looks like this:
SELECT event_id, intdur FROM event_data where event_time =
datetime('2004-04-07 00:00:00', '+ i minutes')
AND datetime('2004-04-07 00:00:00', '+ i minutes', '- intdur seconds') =
event_time;
but it takes an age to work as I have to run the query 60 times for each
hour
I was thinking of creating a seperate table with a row for each minute and
then run a query against that table using the minute integer as part of a
sub-select against the event table. I have tried but can't get it to work.
so far I get an error: only a single result allowed for a SELECT that is
part of an expression
--
SELECT count(i) from integers60 WHERE (SELECT event_id, intdur from
event_data where event_time = datetime('2004-04-07 10:00:00', '+ i
minutes') AND datetime('2004-04-07 10:00:00', '+ i minutes', '+ intdur) =
event_time)
Can some one help?
Lloyd
Re: [sqlite] add new column to table
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: Wednesday, January 12, 2005 4:21 PM Subject: Fw: [sqlite] add new column to table 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 users WHERE extn_no = (SELECT extn_no FROM call_data)); I have missed something? Lloyd, You can't use an UPDATE statement to fill in your new column unless you want all rows to have the same value. You need to populate your new table using the INSERT ... SELECT ... statement. You use the select statement to combine the data from your old table and your new values using joins to pull values from other tables or calculating the new values based on existing columns. I hope this helps. Dennis Cote
Re: [sqlite] add new column to table
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 - Original Message - From: Dennis Cote [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Wednesday, January 12, 2005 5:21 PM Subject: Re: [sqlite] add new column to table 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: Wednesday, January 12, 2005 4:21 PM Subject: Fw: [sqlite] add new column to table 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 users WHERE extn_no = (SELECT extn_no FROM call_data)); I have missed something? Lloyd, You shouldn't need to use PHP or Delphi. You need to create a tempoarary copy of your existing table, then delete and recreate your table with the new column added. Then use the insert command to copy the old data nad the new data (from your users table) back into the new table. Finally you can delete the old copy. You need to do something like the following: CREATE TABLE old_call_data AS SELECT * FROM call_data; DROP TABLE call_data; CREATE TABLE call_data ( ., caller_nameVARCHAR(100) ); INSERT INTO call_data SELECT old_call_data.*, firstname || ' ' || surname FROM old_call_data JOIN users USING extn_no; DROP TABLE old_call_data; I have assumed that you will add the new column at the end of the existing column list. If not your select statement simply gets a little more compilcated. HTH Dennis Cote
Re: [sqlite] add new column to table
The only working example I can see is the following. BEGIN TRANSACTION; CREATE TEMPORARY TABLE t1_backup(a,b); INSERT INTO t1_backup SELECT a,b FROM t1; DROP TABLE t1; CREATE TABLE t1(a,b); INSERT INTO t1 SELECT a,b FROM t1_backup; DROP TABLE t1_backup; COMMIT;I can see any help or FAQ regarding UPDATES with SUB SELECTS. What is the URL?Lloyd- Original Message - From: Bert Verhees [EMAIL PROTECTED] To: sqlite-users@sqlite.org 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 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 users WHERE extn_no = (SELECT extn_no FROM call_data)); I have missed something? The FAQ gives a working example, I used it a lot, please try that. Bert Verhees - Original Message - From: Lloyd Thomas [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Monday, January 10, 2005 9:08 PM Subject: Re: [sqlite] add new column to table Thanks Paul, I have used that example before to recreate a table. Can I use the same thing to recreate a table and populate a new column with data from a select query. The table I wish to recreate has an ID number in it and I wish to use this to get the forename and surname from another table. Lloyd - Original Message - From: Paul Dixon [EMAIL PROTECTED] To: sqlite-users@sqlite.org 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 another table.What is the best way? There's no ALTER TABLE support in the support language, so you have to recreate the entire table and re-populate it. Check the FAQ: http://www.sqlite.org/faq.html#q13 Paul -- Met vriendelijke groet Bert Verhees ROSA Software
Re: [sqlite] add new column to table
EDIT The only working example I can see is the following. BEGIN TRANSACTION; CREATE TEMPORARY TABLE t1_backup(a,b); INSERT INTO t1_backup SELECT a,b FROM t1; DROP TABLE t1; CREATE TABLE t1(a,b); INSERT INTO t1 SELECT a,b FROM t1_backup; DROP TABLE t1_backup; COMMIT; I can not find any help or FAQ regarding UPDATES with SUB SELECTS. What is the URL? Lloyd - Original Message - From: Bert Verhees [EMAIL PROTECTED] To: sqlite-users@sqlite.org 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 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 users WHERE extn_no = (SELECT extn_no FROM call_data)); I have missed something? The FAQ gives a working example, I used it a lot, please try that. Bert Verhees - Original Message - From: Lloyd Thomas [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Monday, January 10, 2005 9:08 PM Subject: Re: [sqlite] add new column to table Thanks Paul, I have used that example before to recreate a table. Can I use the same thing to recreate a table and populate a new column with data from a select query. The table I wish to recreate has an ID number in it and I wish to use this to get the forename and surname from another table. Lloyd - Original Message - From: Paul Dixon [EMAIL PROTECTED] To: sqlite-users@sqlite.org 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 another table.What is the best way? There's no ALTER TABLE support in the support language, so you have to recreate the entire table and re-populate it. Check the FAQ: http://www.sqlite.org/faq.html#q13 Paul -- Met vriendelijke groet Bert Verhees ROSA Software
[sqlite] add new column to table
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
Re: [sqlite] add new column to table
Thanks Paul, I have used that example before to recreate a table. Can I use the same thing to recreate a table and populate a new column with data from a select query. The table I wish to recreate has an ID number in it and I wish to use this to get the forename and surname from another table. Lloyd - Original Message - From: Paul Dixon [EMAIL PROTECTED] To: sqlite-users@sqlite.org 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 another table.What is the best way? There's no ALTER TABLE support in the support language, so you have to recreate the entire table and re-populate it. Check the FAQ: http://www.sqlite.org/faq.html#q13 Paul
Re: [sqlite] add new column to table
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 users WHERE extn_no = (SELECT extn_no FROM call_data)); I have missed something? - Original Message - From: Lloyd Thomas [EMAIL PROTECTED] To: sqlite-users@sqlite.org Sent: Monday, January 10, 2005 9:08 PM Subject: Re: [sqlite] add new column to table Thanks Paul, I have used that example before to recreate a table. Can I use the same thing to recreate a table and populate a new column with data from a select query. The table I wish to recreate has an ID number in it and I wish to use this to get the forename and surname from another table. Lloyd - Original Message - From: Paul Dixon [EMAIL PROTECTED] To: sqlite-users@sqlite.org 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 another table.What is the best way? There's no ALTER TABLE support in the support language, so you have to recreate the entire table and re-populate it. Check the FAQ: http://www.sqlite.org/faq.html#q13 Paul
[sqlite] Why does my query take so long
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, duration, cost, U.firstname AS firstname, U.surname AS surname FROM call_data as C LEFT JOIN users as U on C.extn_no = U.extn_no WHERE C.stamptime = $unixtimestart AND C.stamptime = $unixtimeend AND direction = 'Out' ORDER BY cost desc LIMIT 0,16 -- Lloydie-t
[sqlite] Why does my query take so long
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, duration, cost, U.firstname AS firstname, U.surname AS surname FROM call_data as C LEFT JOIN users as U on C.extn_no = U.extn_no WHERE C.stamptime = $unixtimestart AND C.stamptime = $unixtimeend AND direction = 'Out' ORDER BY cost desc LIMIT 0,16 -- Lloydie-t
[sqlite] Speeding up quer
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.8.14 (not
sure).
SELECT call_id, C.extn_no AS extn_no, dest, dest_name, call_time, duration,
cost, U.firstname AS firstname, U.surname AS surname FROM call_data as C
LEFT JOIN users as U on C.extn_no = U.extn_no WHERE 1 = '1' AND
julianday(call_time) = julianday('2004-10-16 09:00:00') AND
julianday(call_time) = julianday('2004-11-16 17:29:59') AND direction =
'Out' ORDER BY cost desc LIMIT 0,16;
Lloydie-T
Re: [sqlite] Backing up data by date
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: Wednesday, April 21, 2004 3:27 PM Subject: Re: [sqlite] Backing up data by date On Tue, 20 Apr 2004, Lloyd thomas wrote: 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. Just begin a transaction to get a snapshot of the database, then dump the data within the transaction. No temporary table needed. Will lock the database, but it is the only way to ensure a consistent view unless you are saving data from a single table or view, in which case you won't need the transaction. Christian -- /\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Backing up data by date
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.
Re: [sqlite] differences between 2.8.11 and 2.8.12
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 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 differences in these to versions which would stop the following query returning results. sql--- SELECT C.extn_no AS extn_no, dest, dest_name, call_time, duration, cost, firstname, surname FROM call_data as C INNER JOIN users as U on C.extn_no = U.extn_no WHERE '1' = '1' AND call_time = '2004-12-12 00:00:00' AND call_time = '2004-12-12 23:59:59' AND direction = 'Out' AND U.user_id IN (SELECT user_id FROM grp_user WHERE group_id = '1') LIMIT 0,1 - - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] user id select
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 PM Subject: RE: [sqlite] user id select -Original Message- From: Lloyd thomas [mailto:[EMAIL PROTECTED] Sent: Sunday, April 11, 2004 1:18 PM To: [EMAIL PROTECTED] Subject: [sqlite] user id select I am trying to create a query which selects a user where it is not part of a specified group. Perhaps you just want: SELECT user_id FROM users WHERE user_id NOT IN (SELECT user_id FROM grp_user WHERE group_id = 42) -Ken - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Matching telephone strings
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 '0018764582' LIKE areacode || '%'; results: 001 USA 001876 Jamaica As you can see the second result is the correct one, is there a way I can narrow down the result to the nearest fit? I tried the follwing with no results: select areacode from stdcodes where '01865323260' like areacode || '%' Don't know what to tell you. Here's an actual query: sqlite select id from entities where 'tmkxyz' like id || '%'; id -- tmk Looks analogous to what you're trying to do. What version of SQLite are you using? - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Matching telephone strings
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, 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 '0018764582' LIKE areacode || '%'; results: 001 USA 001876 Jamaica As you can see the second result is the correct one, is there a way I can narrow down the result to the nearest fit? I tried the follwing with no results: select areacode from stdcodes where '01865323260' like areacode || '%' Don't know what to tell you. Here's an actual query: sqlite select id from entities where 'tmkxyz' like id || '%'; id -- tmk Looks analogous to what you're trying to do. What version of SQLite are you using? - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Matching telephone strings
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(albeit the best match seems to be first). The following query seems to sort it out, but is it correct? sql--- select MAX(areacode) as areacode, band, destination from stdcodes where '001212' like areacode || '%' group by areacode limit 0,1; - 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, 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 '0018764582' LIKE areacode || '%'; results: 001 USA 001876 Jamaica As you can see the second result is the correct one, is there a way I can narrow down the result to the nearest fit? I tried the follwing with no results: select areacode from stdcodes where '01865323260' like areacode || '%' Don't know what to tell you. Here's an actual query: sqlite select id from entities where 'tmkxyz' like id || '%'; id -- tmk Looks analogous to what you're trying to do. What version of SQLite are you using? - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Matching telephone strings
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're depending on the values of band and destination just happening to be correct in this implementation. select areacode, band, destination from stdcodes where areacode = (select max(areacode) from stdcodes where '001212' like areacode || '%') - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] user id select
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 user where it is not part of a specified group. But it does not seem to work as I get multiple results for the same user id's. sql--- Select U.user_id from users as U, grp_user as G where U.user_id != G.user_id - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] user id select
Thanks chaps, I ended up with the following query, which seems to do the job. Not sure how efficient it is though. sql SELECT U.user_id FROM users U LEFT JOIN grp_user G ON G.user_id = U.user_id WHERE G.group_id != '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:34 PM Subject: Re: [sqlite] user id select On Sun, 11 Apr 2004, Lloyd thomas wrote: 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 user where it is not part of a specified group. But it does not seem to work as I get multiple results for the same user id's. sql--- Select U.user_id from users as U, grp_user as G where U.user_id != G.user_id You'll get a user_id for each group that the user is not a member off. You need an extra where clause selecting which group you're checking for: Select U.user_id from users as U, grp_user as G where U.user_id != G.user_id AND G.group_id != group; - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] -- /\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Matching telephone strings
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 - From: Kurt Welgehausen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, April 11, 2004 12:20 AM Subject: Re: [sqlite] Matching telephone strings Is there any faster way I can compare these telephone numbers within sqlite? How about something like select areacode from stdcodes where $telno like areacode || '%' Regards - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] DATE_SUB
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 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 duration
FROM call_data where call_time = '2003-12-04 13:10'
AND DATE_SUB('2003-12-04 13:10', INTERVAL duration HOUR_SECOND) =
call_time
Sorry to refer to MySQL code but it is all I have to go by.
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] time calculations
I know there are now some time functions in sqlite, but I did not quite understand the
functions available in it. I apologise for the MySQL references below, but I wish to
achieve the query below with SQLite, assuming some of you are fimiliar with MySQL. Can
you help?
code
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
Re: [sqlite] time calculations
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
-
call_time | duration
-
2003-12-04 09:27:00 | 00:01:21
-
2003-12-04 09:28:00 | 00:04:19
-
- Original Message -
From: Kurt Welgehausen [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, February 28, 2004 9:26 PM
Subject: Re: [sqlite] time calculations
it relies on having the seconds as well.
I don't understand the question. As far as I know,
nothing in SQLite requires the seconds.
sqlite select julianday('2003-12-04 16:41');
julianday('2003-12-04 16:41')
-
2452978.19513889
If you're getting an error msg, maybe you can be
more specific.
Regards
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] time calculations
Kurt,
I managed to resolve my problem with the following. Thanks for your help.
code
SELECT round((julianday(duration)-julianday('00:00:00')) * 86400) as
duration_sec
FROM call_data
WHERE julianday(call_time) = julianday('2003-12-03 18:42')
AND (julianday('2003-12-03 18:42') - julianday(duration)) =
julianday(call_time)
---
Lloyd
- Original Message -
From: Lloyd thomas [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, February 28, 2004 10:28 PM
Subject: Re: [sqlite] time calculations
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
-
call_time | duration
-
2003-12-04 09:27:00 | 00:01:21
-
2003-12-04 09:28:00 | 00:04:19
-
- Original Message -
From: Kurt Welgehausen [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, February 28, 2004 9:26 PM
Subject: Re: [sqlite] time calculations
it relies on having the seconds as well.
I don't understand the question. As far as I know,
nothing in SQLite requires the seconds.
sqlite select julianday('2003-12-04 16:41');
julianday('2003-12-04 16:41')
-
2452978.19513889
If you're getting an error msg, maybe you can be
more specific.
Regards
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] time calculations
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?
Lloyd
- Original Message -
From: D. Richard Hipp [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Sunday, February 29, 2004 12:57 AM
Subject: Re: [sqlite] time calculations
Lloyd thomas wrote:
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
-
call_time | duration
-
2003-12-04 09:27:00 | 00:01:21
-
2003-12-04 09:28:00 | 00:04:19
-
I just checked in changes to the date/time functions to
fix a bug that this question brought to light, and to
add some new capability.
If you give one of the date-time functions just a time
with no date, they are suppose to fill in a date of
2000-01-01. For example:
SELECT datetime('00:01:21');
2000-01-01 00:01:21
This was working for julianday() but not for datetime().
It has now been fixed.
I also added the ability to put a time value in as the
modifier and shift the date by that amount. For example:
SELECT datetime('2003-12-04 09:27:00', '00:01:21');
2003-12-04 09:28:21
The time modifier can be negative. So to shift a date/time
backwards by 2 hours and 45 minutes, you could say this:
SELECT datetime('2003-12-04 09:27:00', '-02:45');
2003-12-04 06:42:00
In situations like the above, the new capability can be
used to compute the ending time of a call as follows.
SELECT datetime(call_time, duration);
But I don't think the original post needs any of the above.
These were just deficiencies I noticed in the date/time
functions as I looked at the question. The original
poster just wanted to know the number of seconds in a
call, and that can be computed as follows:
SELECT (julianday(duration) - julianday('2000-01-01'))*86400
Note that you are subtracting two number that are very close
to one another - an operation that introduces a lot of error.
So the result will be off by a few microseconds. You can use
the round() function to round it off to the nearest second
which should then be exact.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
