Re: [sqlite] SQL statement to get min/max values
I've got 3.6.0 and it works fine here On Thu, Aug 14, 2008 at 9:09 PM, Dennis Volodomanov <[EMAIL PROTECTED]> wrote: >> I'm using 3.3.5, I'll get the latest and see if works there or not in > a >> few minutes. > > Ok, it works in 3.6.1 - sorry for the troubles... I'll just upgrade the > program then. > > Thank you, > > Dennis > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
> I'm using 3.3.5, I'll get the latest and see if works there or not in a > few minutes. Ok, it works in 3.6.1 - sorry for the troubles... I'll just upgrade the program then. Thank you, Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
> Could it be that since you're not defining a type for Data it assumes > string? > Try creating the table with > id integer, externalid integer, data number (or numeric) That Data column could contain anything (int, double, string), it'll be up to the application's logic to only get Data for ExternalIDs that are numeric. I've confirmed it to work in 3.6.1 though - it doesn't work in 3.3.5. Thanks, Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
Could it be that since you're not defining a type for Data it assumes string? Try creating the table with id integer, externalid integer, data number (or numeric) On Thu, Aug 14, 2008 at 7:00 PM, Dennis Volodomanov <[EMAIL PROTECTED]> wrote: > Hello all, > > I've tried a few SQL statements, but can't seem to get it to work > properly, so I'd like to ask your help. > > Suppose I have a table like this: > > CREATE TABLE test_table (ID INTEGER PRIMARY KEY, ExternalID, Data); > > And some contents: > > 1| 2| -7 > 2| 2| 5 > 3| 1| 0 > 4| 2| -20 > 5| 2| -5 > 6| 2| 1 > 7| 1| 10 > > Now, what I'd like to do is get minimum (-20) and maximum (5) from the > table where ExternalID=2. It must be very simple, but I can't seem to > get the correct result. What I tried was: > > SELECT min(Data) FROM (SELECT Data FROM test_table WHERE ExternalID=2); > > But the above doesn't return the expected result. > > Thanks for your help! > > Dennis > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
> What version of SQLite are you using? I'm using the 3.5.7 version that > came > with OS X 10.5, and I get -2 as expected. I'm using 3.3.5, I'll get the latest and see if works there or not in a few minutes. > Also, what's with the superfluous subquery? Why not just say > > SELECT max(Data) FROM test_table WHERE ExternalID=2; > > You can even do min and max at the same time: > > SELECT min(Data), max(Data) FROM test_table WHERE ExternalID=2; Yes, that query is a remnant of things I tried, because I couldn't get the values to return as expected. Your second query looks perfect for my needs, thanks! Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
On Thu, Aug 14, 2008 at 9:45 PM, Dennis Volodomanov < [EMAIL PROTECTED]> wrote: > > Seems to work ok for me. What values were you expecting? > > Yes, that works. Bad example on my part, sorry. > > What doesn't work is this: > > 1|2|-7 > 2|2|-5 > 3|2|-20 > 4|2|-5 > 5|2|-2 > > SELECT max(Data) FROM (SELECT Data FROM test_table WHERE ExternalID=2); > > This returns a -5, while I'm expecting a -2. > > Thank you, What version of SQLite are you using? I'm using the 3.5.7 version that came with OS X 10.5, and I get -2 as expected. Also, what's with the superfluous subquery? Why not just say SELECT max(Data) FROM test_table WHERE ExternalID=2; You can even do min and max at the same time: SELECT min(Data), max(Data) FROM test_table WHERE ExternalID=2; Or get real fancy: create view test_stats as select ExternalId, max(Data) as maxData, min(Data) as minData, avg(Data) as avgData from test_table group by ExternalId -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
> Seems to work ok for me. What values were you expecting? Yes, that works. Bad example on my part, sorry. What doesn't work is this: 1|2|-7 2|2|-5 3|2|-20 4|2|-5 5|2|-2 SELECT max(Data) FROM (SELECT Data FROM test_table WHERE ExternalID=2); This returns a -5, while I'm expecting a -2. Thank you, Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
On Fri, Aug 15, 2008 at 1:00 AM, Dennis Volodomanov <[EMAIL PROTECTED]> wrote: > Suppose I have a table like this: > > CREATE TABLE test_table (ID INTEGER PRIMARY KEY, ExternalID, Data); > > And some contents: > > 1| 2| -7 > 2| 2| 5 > 3| 1| 0 > 4| 2| -20 > 5| 2| -5 > 6| 2| 1 > 7| 1| 10 > > Now, what I'd like to do is get minimum (-20) and maximum (5) from the > table where ExternalID=2. It must be very simple, but I can't seem to > get the correct result. What I tried was: > > SELECT min(Data) FROM (SELECT Data FROM test_table WHERE ExternalID=2); > > But the above doesn't return the expected result. - SQLite version 3.5.9 Enter ".help" for instructions sqlite> create table test_table (ID INTEGER PRIMARY KEY, ExternalID, Data); sqlite> insert into test_table values (1,2,-7); sqlite> insert into test_table values (2,2,5); sqlite> insert into test_table values (3,1,0); sqlite> insert into test_table values (4,2,-20); sqlite> insert into test_table values (5,2,-5); sqlite> insert into test_table values (6,2,1); sqlite> insert into test_table values (7,1,10); sqlite> select * from test_table; 1|2|-7 2|2|5 3|1|0 4|2|-20 5|2|-5 6|2|1 7|1|10 sqlite> SELECT min(Data) FROM (SELECT Data FROM test_table WHERE ExternalID=2); -20 sqlite> SELECT max(Data) FROM (SELECT Data FROM test_table WHERE ExternalID=2); 5 sqlite> SELECT max(Data) from test_table WHERE ExternalID=2; 5 sqlite> SELECT min(Data) from test_table WHERE ExternalID=2; -20 - Seems to work ok for me. What values were you expecting? Regards, ~Nuno Lucas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL statement to get min/max values
Hello all, I've tried a few SQL statements, but can't seem to get it to work properly, so I'd like to ask your help. Suppose I have a table like this: CREATE TABLE test_table (ID INTEGER PRIMARY KEY, ExternalID, Data); And some contents: 1| 2| -7 2| 2| 5 3| 1| 0 4| 2| -20 5| 2| -5 6| 2| 1 7| 1| 10 Now, what I'd like to do is get minimum (-20) and maximum (5) from the table where ExternalID=2. It must be very simple, but I can't seem to get the correct result. What I tried was: SELECT min(Data) FROM (SELECT Data FROM test_table WHERE ExternalID=2); But the above doesn't return the expected result. Thanks for your help! Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Currency Issues
> jonwood wrote: > I've done very little programming related to currency (dollars, > etc.) and I'm wondering if I need to worry about rounding errors. > Since SQLite doesn't appear to have a currency type, I had planned > on using REAL instead. But I have a lot of reports to print out and > I could see rounding errors with REAL. That's a very real possibility. I'll second your idea, and Dennis' recommendation, to use an integer type for this (unless you are using a compiler that has a scaled integer or dedicated currency type). In addition, you might consider storing at least one more digit than pennies, to keep your fractional pennies, if you need to do that. I once had a system where I was required to keep thousandths of pennies, and we used this method. The vast majority of numbers had no fractional pennies, so $10.00 looked like 100, but that's the way it had to be. Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Generic speed testing
Linux: Sqlite 3.5.9, AMD x64 3800 (2ghz) dual core, 2gb RAM, 7200rpm drive. SQL error near line 100: no such table: TEST1 SQL error near line 115: no such table: TEST1 TestNumber|Description|ROUND(EndTime- StartTime,2)|Rows|Round(Rows/(EndTime-StartTime)/1000)||'K Rows/Second' 0|performance.txt,v 1.0|1.0|0|0.0K Rows/Second 1|Trivial Inserts|24.34|10077696|414.0K Rows/Second 2|Trivial Selects|1.6|10077696|6286.0K Rows/Second 3|Trivial Updates|130.56|10077696|77.0K Rows/Second 4|Trivial Deletes|35.13|10077696|287.0K Rows/Second 5|Insert with calculations|0.0|1|2.0K Rows/Second 6|Updates with calculations and longer rows|0.0|1|2.0K Rows/Second Interesting that your hard drive is 5000rpm but yet your getting x2 performance. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Loading a existing database 100% into memory
Sorry I should have said, I'm using these calls in C++ on windows. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Thursday, August 14, 2008 1:45 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Loading a existing database 100% into memory On Thu, 14 Aug 2008, D. Richard Hipp wrote: > On Aug 14, 2008, at 4:18 PM, Brown, Daniel wrote: > >> Hello Stefan, >> >> I'm trying to use the code snippet you suggested but when I try to >> query >> the master table of the attached database I get and error with the >> following message: >> "SQL error: no such table: 'test.sqlite_master'; >> >> I am running the following queries: >> 1. "ATTACH DATABASE 'data\\test.db' AS test" - this seems to succeed >> (no >> error code). >> 2. "SELECT tbl_name FROM test.sqlite_master' WHERE type = 'table'" - >> this one is failing. >> > > Works when I try it. > > But I'm not running on windows. Do you really need two backslashes in > the filename? Should there be just a single backslash? You do not > quote backslashes in SQL strings as you do in C. > > But I'm not running on windows. Do you really need two backslashes in > the filename? Should there be just a single backslash? You do not Backslashes are illegal in a Windows filenames, except as folder delimiters, so "data\\test.db" is not a valid Windows filename. Hence, if this is on Windows, the ATTACH must be failing. It must be 'data\test.db'. Chris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] returning multiple rows from custom functions
Nathan Kurz <[EMAIL PROTECTED]> writes:
> On Thu, Dec 15, 2005 at 09:17:48PM +, Andrew McDermott wrote:
>> For example, I'm currently computing a histogram in application code for
>> a moderately sized table (7+ million rows), but I'm wondering whether it
>> would be quicker to get the results computed via a custom SQLite
>> function. I'm expecting it to be quicker because the current
>> implementation traverses the JNI boundary for each row in the result set
>> whereas a custom function wouldn't need to do this.
>
> No, it is not currently possible. The way the VDBE is set up, all
> functions return only a single value. But do you need multiple rows,
> or multiple values? For a histogram, would multiple values suffice?
Thanks for this suggestion. I have done this and it works just fine.
>
> If so you can fake it. With aggregate functions it's possible to
> compute the histogram, and then return it in some special form. You
> could for example define a histogram function that returns a comma
> separated list of text, or you could have it return an array as a blob.
>
>> select histogram(column) from table;
>> 1,4,1
>
> Or you could split the histogram function into pieces, one for each
> bin, and write a simple aggregate function that just counts the
> occurences of one particular value:
>
>> select number(column,1), number(column,2), number(column,3) from table;
>> 1,4,1
-
This pertains to the mailing list thread
http://www.mail-archive.com/sqlite-users@sqlite.org/msg12076.html
Andy, Nathan,
I've recently "discovered" sqlite and I am very happy with it! As such,
this is my first post, and I came across this thread starting back in 2005
while searching for how to return multiple values in table form from a
user-defined extension function. I'm sure that this problem has been solved
already, and that I'm not doing anything new, but I didn't see this approach
in the mailing list, so I thought I'd give an alternate way to return
multiple values (or approximate the return, rather than having to deal with
a comma-delimited list, which doesn't allow further SQL set functions to be
applied to the result).
The approach is to construct (and index) a simple one-dimensional table with
integers ranging from 1 to some arbitrarily large number (larger than the
number of rows in your largest table, for example) and then use this as a
type of "iterator" across the data.
For example, to create a histogram from data values, you may try the
following:
sqlite>.mode column
sqlite>.header on
sqlite>create table his(value int);
sqlite>insert into his values (1);
sqlite>insert into his values (2);
sqlite>insert into his values (3);
sqlite>insert into his values (4);
sqlite>insert into his values (5);
sqlite>insert into his values (4);
sqlite>insert into his values (1);
sqlite>insert into his values (1);
sqlite>insert into his values (1);
sqlite>create table iterator(i int);
sqlite>insert into iterator values (1);
sqlite>insert into iterator values (2);
sqlite>insert into iterator values (3);
sqlite>insert into iterator values (4);
sqlite>insert into iterator values (5);
sqlite>insert into iterator values (6);
sqlite>insert into iterator values (7);
sqlite>insert into iterator values (8);
sqlite>insert into iterator values (9);
sqlite>create index iter on iterator(i);
sqlite>select iterator.i as i, count(his.value) as count from his,iterator
where his.value=iterator.i group by iterator.i;
i count
- -
1 4
2 1
3 1
4 2
5 1
sqlite>select iterator.i as i, iterator.i+2 as j, count(his.value) as count
from his,iterator where his.value between iterator.i and iterator.i+2 group
by iterator.i;
i j count
- - -
1 3 6
2 4 4
3 5 4
4 6 3
5 7 1
An interesting thing about using such an 'iterator table' is that it can be
used for traversals using simple functions as well as aggregate functions.
Consider, for example, the simple function charindex contained in
extension-functions.c contributed by Mikey C. and packaged by Liam Healy.
This function takes two arguments, one a string to be found in another
larger string, designated by the second argument. There is an optional
third argument, which designates the string position from which to begin
searching. The function returns the integer position of the start of the
first string in the second string:
sqlite>select
charindex("ELVIS","ELVIShasleftthebuildingbutELVISwillbebacktomorrownight")
as pos;
pos
-
1
To find all occurrences of ELVIS, and more importantly, to return the
results in tabular form, use the iterator table and the optional third
parameter, to which we pass the iterator value:
sqlite>select
charindex("ELVIS","E
Re: [sqlite] System function with Sqlite
Thanks for your continued help Chris > As someone else suggest strace might be useful, build your application > as a binary and run it under strace: > > strace -f -o trace.txt ./name/of/your/thang I do not seem to have strace on my system. I am running Linux version 2.4.17_mv_121-malta-mips_fp_le with gcc version 2.95.3. I attempted to download and run strace but I couldn't get it to run correctly. > Can you check which libc you are using and if there is any LD_PRELOAD > or similar active? I'm sorry but how do I check this? > Some code does something like: > >for (fd = 2; fd <= 255; ++fd) >fcntl(fd, F_SETFD, FD_CLOEXEC); > I attempted to implement this but it did not appear to have any affect. Either that or I haven't implemented it correctly. Can you offer any further help/suggestions? Thanks Chris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Currency Issues
jonwood wrote: > I've done very little programming related to currency (dollars, etc.) and I'm > wondering if I need to worry about rounding errors. Since SQLite doesn't > appear to have a currency type, I had planned on using REAL instead. But I > have a lot of reports to print out and I could see rounding errors with > REAL. > > Another alternative would be to use an INTEGER to store the number of > pennies. But I don't really know how much of an issue this is and I'd > appreciate any comments from anyone who has dealt with it. > You are almost certainly better off using an integer data type for currency calculations. If not you will end up with small, but disturbing errors in your reports. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Loading a existing database 100% into memory
On Thu, 14 Aug 2008, D. Richard Hipp wrote: > On Aug 14, 2008, at 4:18 PM, Brown, Daniel wrote: > >> Hello Stefan, >> >> I'm trying to use the code snippet you suggested but when I try to >> query >> the master table of the attached database I get and error with the >> following message: >> "SQL error: no such table: 'test.sqlite_master'; >> >> I am running the following queries: >> 1. "ATTACH DATABASE 'data\\test.db' AS test" - this seems to succeed >> (no >> error code). >> 2. "SELECT tbl_name FROM test.sqlite_master' WHERE type = 'table'" - >> this one is failing. >> > > Works when I try it. > > But I'm not running on windows. Do you really need two backslashes in > the filename? Should there be just a single backslash? You do not > quote backslashes in SQL strings as you do in C. > > But I'm not running on windows. Do you really need two backslashes in > the filename? Should there be just a single backslash? You do not Backslashes are illegal in a Windows filenames, except as folder delimiters, so "data\\test.db" is not a valid Windows filename. Hence, if this is on Windows, the ATTACH must be failing. It must be 'data\test.db'. Chris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Loading a existing database 100% into memory
On Aug 14, 2008, at 4:18 PM, Brown, Daniel wrote: > Hello Stefan, > > I'm trying to use the code snippet you suggested but when I try to > query > the master table of the attached database I get and error with the > following message: > "SQL error: no such table: 'test.sqlite_master'; > > I am running the following queries: > 1. "ATTACH DATABASE 'data\\test.db' AS test" - this seems to succeed > (no > error code). > 2. "SELECT tbl_name FROM test.sqlite_master' WHERE type = 'table'" - > this one is failing. > Works when I try it. But I'm not running on windows. Do you really need two backslashes in the filename? Should there be just a single backslash? You do not quote backslashes in SQL strings as you do in C. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Generic speed testing
After looking at the code for speed test #1-4, I've decided that a
command line version that does not use TCL would be better. Wanting to
keep things simple, I'm looking only at inserts, selections and deletes.
I'm not sure if this will even be a valid comparison, so your feedback
and initial numbers are appreciated.
Please reply with your data as follows
SQLite 3.6.1 running under "Vista 32 bit, QuadCore 2.4GHz, 3G ram,
5000rpmDisk"
0|performance.txt,v 1.0|1.0|0|0.0K Rows/Second
1|Trivial Inserts|16.12|10077696|625.0K Rows/Second
2|Trivial Selects|0.87|10077696|11650.0K Rows/Second
3|Trivial Updates|81.69|10077696|123.0K Rows/Second
4|Trivial Deletes|22.17|10077696|455.0K Rows/Second
5|Insert with calculations|29.02|10077696|347.0K Rows/Second
6|Updates with calculations and longer rows|54.52|10077696|185.0K
Rows/Second
Regards, Noah
Performance script version 1 follows:
--
-- The author disclaims copyright to this source code. In place of
-- a legal notice, here is a blessing:
--
--May you do good and not evil.
--May you find forgiveness for yourself and forgive others.
--May you share freely, never taking more than you give.
--
--**
*
-- This file contains code used to implement the performance scripts
--
-- $Id: performance.txt,v 1.0 2008/08/14 12:50:00 nbh Exp $
PRAGMA SYNCHRONIZATION = FULL;
PRAGMA locking_mode = EXCLUSIVE;
PRAGMA synchronous = OFF;
--
-- A LITTLE SETUP BEFORE WE BEGIN
--
CREATE TABLE TIMER(TestNumber INTEGER, Description TEXT, StartTime
REAL, EndTime REAL DEFAULT NULL, Rows INTEGER DEFAULT NULL);
INSERT INTO TIMER VALUES(0, 'performance.txt,v 1.0', 0, 1, 0);
CREATE TABLE TEST1 (I INTEGER, T TEXT);
CREATE TABLE N_1(i INTEGER, t TEXT);
INSERT INTO N_1 VALUES(1, 't1_');
INSERT INTO N_1 VALUES(2, 't_22_');
INSERT INTO N_1 VALUES(3, 'tx_3_3_3_');
INSERT INTO N_1 VALUES(4, 'txt_4_4_4_4_');
INSERT INTO N_1 VALUES(5, 'text_555_');
INSERT INTO N_1 VALUES(6, ' ');
CREATE TABLE N_2(i INTEGER, t TEXT);
INSERT INTO N_2 SELECT N1.I+N2.I*7, N1.T||N2.T FROM N_1 N1 CROSS
JOIN N_1 N2 CROSS JOIN N_1 N3;
-- TEST 1
-- TRIVIAL INSERTS -- KEEP THE NUMBER AND TEXT SMALL
BEGIN;
INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
1+MAX(TESTNUMBER), 'Trivial Inserts', (julianday('now') -
2440587.5)*86400 FROM TIMER;
INSERT INTO TEST1 SELECT 1,'T' FROM N_2 N1 CROSS JOIN N_2 N2 CROSS
JOIN N_2 N3;
COMMIT;
UPDATE TIMER SET EndTime = (julianday('now') -
2440587.5)*86400.0, Rows = changes()
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
-- TEST 2
-- TRIVIAL SELECTS
INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
1+MAX(TESTNUMBER), 'Trivial Selects', (julianday('now') -
2440587.5)*86400 FROM TIMER;
UPDATE TIMER SET Rows = (SELECT COUNT(*) FROM TEST1 where rowid
> 0)
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
UPDATE TIMER SET EndTime = (julianday('now') -
2440587.5)*86400.0
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
-- TEST 3
-- TRIVIAL UPDATES -- THE NUMBERS AND ROW SIZE ARE SMALL
BEGIN;
INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
1+MAX(TESTNUMBER), 'Trivial Updates', (julianday('now') -
2440587.5)*86400 FROM TIMER;
UPDATE TEST1 SET I=I;
COMMIT;
UPDATE TIMER SET EndTime = (julianday('now') -
2440587.5)*86400.0, Rows = changes()
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
-- TEST 4
-- TRIVIAL DELETES
BEGIN;
INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
1+MAX(TESTNUMBER), 'Trivial Deletes', (julianday('now') -
2440587.5)*86400 FROM TIMER;
DELETE FROM TEST1 WHERE I >0;
COMMIT;
UPDATE TIMER SET EndTime = (julianday('now') -
2440587.5)*86400.0, Rows = changes()
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
--
-- A LITTLE CLEANUP BEFORE WE CONTINUE
--
DROP TABLE TEST1;
PRAGMA page_count;
VACUUM;
PRAGMA page_count;
-- TEST 5
-- INSERTS WITH CALCULATIONS -- SHOULD BE SLOWER THAN 1
BEGIN;
INSERT INTO
Re: [sqlite] Loading a existing database 100% into memory
Hello Stefan, I'm trying to use the code snippet you suggested but when I try to query the master table of the attached database I get and error with the following message: "SQL error: no such table: 'test.sqlite_master'; I am running the following queries: 1. "ATTACH DATABASE 'data\\test.db' AS test" - this seems to succeed (no error code). 2. "SELECT tbl_name FROM test.sqlite_master' WHERE type = 'table'" - this one is failing. Do you have any ideas? I tried looking at the docs but there doesn't seem to be much about it, from what I've read the queries appear to be correct. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Szomraky, Stefan Sent: Thursday, August 07, 2008 12:02 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Loading a existing database 100% into memory What do you mean by loading it into memory? If you want to dump the on-disk tables into memory and also want to check the memory footprint used for caching try this: Open the :memory: database and attach the on-disk database with ATTACH filename.db AS filename Then do a CREATE TABLE tableName AS SELECT * FROM filename.tableName On each table in the file, thus creating an in-memory copy of the DB and having done a select on each table (i.e. you'll see how much cache in memory will be used, etc.) You can enumerate all tables in a your on-disk-file in the mentioned scenario by doing a "SELECT tbl_name FROM filename.sqlite_master WHERE type = 'table'". Best regards, Stefan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Currency Issues
I've done very little programming related to currency (dollars, etc.) and I'm wondering if I need to worry about rounding errors. Since SQLite doesn't appear to have a currency type, I had planned on using REAL instead. But I have a lot of reports to print out and I could see rounding errors with REAL. Another alternative would be to use an INTEGER to store the number of pennies. But I don't really know how much of an issue this is and I'd appreciate any comments from anyone who has dealt with it. Thanks. -- View this message in context: http://www.nabble.com/Currency-Issues-tp18988348p18988348.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] prevent column from being written to disk
Jeff Picciotti wrote: > I know it would be simple to separate the column out and create a > :memory db, but that would require numerous changes to our system > (easily doable, just not at the moment) So I was just checking the > community to see if anyone has any ideas or thoughts on this! > Jeff, You might be able to do this easier if you use a temporary table to hold the transient data. The temp table could be joined with a permanent table in a view that would replace the existing table. All your existing queries would work the same as before (I think, there may be issues with column naming, but that can be fixed by setting aliases for all the result columns in the view that match the existing table's column names). You would then have to add a set of "instead of" triggers to handle inserts, updates, and deletes from the view. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] prevent column from being written to disk
Hello, new to the list and to SQLite. A little background... writing C library for an embedded system (ARM) which started in mysql but now we want something much lighter. Anyway, things are going pretty well. We had an existing library and application we ported to use SQLite which was already based on an existing table structure. In a few tables we have status columns where the data is not need for long term storage. The data can always be restored and can change often. We were curious if anyone knew of a way to prevent updates to a single column from being written to disk(flash) and just have it written to the cache? Most of our runtime writes will be of the status variety. Configuration of the system is stored and needs to be protected. I know it would be simple to separate the column out and create a :memory db, but that would require numerous changes to our system (easily doable, just not at the moment) So I was just checking the community to see if anyone has any ideas or thoughts on this! Thanks in advance. Jeff ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there a pragma to disable triggers?
Kodok Márton wrote: > Hello, > I am working on a syncing project and I do have a lot of triggers to > read/write foreign keys. > And while I do the sync of one table the triggers are causing a strange > effect (as the other table is not yet synced). > Is there a pragma to disable triggers on the sqlite database? > Regards, > Marton I think a better solution here would be some kind of syntax that lets you perform multiple data-manipulation operations "simultaneously"; for example: INSERT INTO DEBITS (...) VALUES (...), INSERT INTO CREDITS (...) VALUES (...); In this example, the 2 statements are separated by a comma rather than a semicolon (you can use different syntax instead if you want), so they are treated as a single statement in that their updates all happen as a single update. In this case, the triggers don't have to be disabled; they would just run after the combined statement completes. Note that my proposal is not the same as transactions, since you can have triggers run between statements in a transaction and said statements are not collectively atomic in the same way where no database state exists between statements. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there a pragma to disable triggers?
Kodok Márton wrote: > now this raises questions: > > eg: > CREATE TABLE clients ... > CREATE VIEW getclients AS select * from clients. > > now if I update the 'getlist' view that will with a instead of trigger > update the clients table > > that will raise the triggers for the client table, I am right? > Yes, it will. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there a pragma to disable triggers?
now this raises questions: eg: CREATE TABLE clients ... CREATE VIEW getclients AS select * from clients. now if I update the 'getlist' view that will with a instead of trigger update the clients table that will raise the triggers for the client table, I am right? - Original Message - From: "Kees Nuyt" <[EMAIL PROTECTED]> To: "General Discussion of SQLite Database" Sent: Thursday, August 14, 2008 8:59 PM Subject: Re: [sqlite] is there a pragma to disable triggers? > On Mon, 11 Aug 2008 13:16:45 +0300, you wrote: > >>Hello, >> >>I am working on a syncing project and I do have a lot of triggers to >>read/write foreign keys. >>And while I do the sync of one table the triggers are causing a strange >>effect (as the other table is not yet synced). >>Is there a pragma to disable triggers on the sqlite database? > > Perhaps you can solve the problem with "INSTEAD OF" triggers > on one or more views. Yes, that's possible: a view is > `virtually` updatable when you define triggers for all > statements that will be used to update the view. > It is a very powerful mechanism. > > The updatable view can be (almost) any select or join and > has to contain all columns of all tables that you need to > update with the INSERT | UPDATE view_name statements. All > foreign key contraints would be handled by the INSTEAD OF > triggers if all your updates are performed on those views > instead of tables. > > sql-statement ::= > CREATE [TEMP | TEMPORARY] TRIGGER > [IF NOT EXISTS] trigger-name > INSTEAD OF database-event > ON [database-name .] view-name > trigger-action > > database-event ::= > DELETE | INSERT | UPDATE | UPDATE OF > column-list > > http://www.sqlite.org/lang_createtrigger.html > >>Regards, >>Marton > > I hope this helps. > -- > ( Kees Nuyt > ) > c[_] > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there a pragma to disable triggers?
On Mon, 11 Aug 2008 13:16:45 +0300, you wrote: >Hello, > >I am working on a syncing project and I do have a lot of triggers to >read/write foreign keys. >And while I do the sync of one table the triggers are causing a strange effect >(as the other table is not yet synced). >Is there a pragma to disable triggers on the sqlite database? Perhaps you can solve the problem with "INSTEAD OF" triggers on one or more views. Yes, that's possible: a view is `virtually` updatable when you define triggers for all statements that will be used to update the view. It is a very powerful mechanism. The updatable view can be (almost) any select or join and has to contain all columns of all tables that you need to update with the INSERT | UPDATE view_name statements. All foreign key contraints would be handled by the INSTEAD OF triggers if all your updates are performed on those views instead of tables. sql-statement ::= CREATE [TEMP | TEMPORARY] TRIGGER [IF NOT EXISTS] trigger-name INSTEAD OF database-event ON [database-name .] view-name trigger-action database-event ::= DELETE | INSERT | UPDATE | UPDATE OF column-list http://www.sqlite.org/lang_createtrigger.html >Regards, >Marton I hope this helps. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_prepare/bind to exnter data or just query?
flakpit wrote: > Peter, sorry to be a nuisance but, can you update the same way? I've not > idea how that statement would look with this way of doing things Yep: sqlite3_prepare_v2(db,"UPDATE ans SET col1=?, col2=?;",-1,&stmt,NULL); sqlite3_bind_text(stmt,1,"Peter's",-1,SQLITE_STATIC); sqlite3_bind_text(stmt,2,"Reply",-1,SQLITE_STATIC); sqlite3_step(stmt); sqlite3_reset(stmt); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_prepare/bind to exnter data or just query?
flakpit wrote: > > Peter Holmes-4 wrote: >> Yep. Works great! For example: >> >> sqlite3_prepare_v2(db,"INSERT INTO ans VALUES (?,?);",-1,stmt,NULL); >> sqlite3_bind_text(stmt,0,"Peter's",-1,SQLITE_STATIC); >> sqlite3_bind_text(stmt,1,"Reply",-1,SQLITE_STATIC); >> sqlite3_step(stmt); >> sqlite3_reset(stmt); >> > > so ?,? represent columns to be filled in right? Yep. > and stmt,0 is column 0 and stmt,1 is column 1? Yep, but the first column is 1 (see the last posted version of the snippet which corrects my previous errors). Watch out when retrieving values from SELECT output because in that case the first column is 0. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Linking databases
Yes, databases. The databases are generated by another process but I need to search all of them for matches. Bruce Martin The Martin Solution [EMAIL PROTECTED] http://www.martinsolution.com http://externals.martinsolution.com On Aug 14, 2008, at 11:51 AM, P Kishor wrote: > On 8/14/08, Bruce Martin <[EMAIL PROTECTED]> wrote: >> Is there a way to link and do a select on multiple databases? >> For example I have 3 databases maybe containing a list of file names. >> >> DB1 >> Apple.txt >> Grape.html >> Cherry.txt >> Peach.txt >> >> DB2 >> Dell.txt >> HP.txt >> Gateway.txt >> Apple.txt >> >> DB3 >> >> Apple Pie.txt >> Cherry Pie.txt >> Grape Jelly.txt >> >> >> Now I want to select any record that starts with "apple" in all of >> the >> databases. > > You sure you mean "databases" when you might be meaning "tables"? > Usually tables contain "lists", and a collection of related tables > make up a database. Of course, you can also ATTACH disparate > databases, but evaluate your strategy... you might be able to do with > one db with three tables likes so > > CREATE TABLE fruits (id, name); > CREATE TABLE computers (id, name); > CREATE TABLE foods (id, name); > > Now you can select across tables > > SELECT * FROM fruits WHERE name LIKE '%apple%' > UNION > SELECT * FROM computers WHERE name LIKE '%apple%' > UNION > SELECT * FROM foods WHERE name LIKE '%apple%'; > > >> >> Thanks, >> >> Bruce Martin >> The Martin Solution >> [EMAIL PROTECTED] >> http://www.martinsolution.com >> http://externals.martinsolution.com >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Linking databases
On 8/14/08, Bruce Martin <[EMAIL PROTECTED]> wrote: > Is there a way to link and do a select on multiple databases? > For example I have 3 databases maybe containing a list of file names. > > DB1 > Apple.txt > Grape.html > Cherry.txt > Peach.txt > > DB2 > Dell.txt > HP.txt > Gateway.txt > Apple.txt > > DB3 > > Apple Pie.txt > Cherry Pie.txt > Grape Jelly.txt > > > Now I want to select any record that starts with "apple" in all of the > databases. You sure you mean "databases" when you might be meaning "tables"? Usually tables contain "lists", and a collection of related tables make up a database. Of course, you can also ATTACH disparate databases, but evaluate your strategy... you might be able to do with one db with three tables likes so CREATE TABLE fruits (id, name); CREATE TABLE computers (id, name); CREATE TABLE foods (id, name); Now you can select across tables SELECT * FROM fruits WHERE name LIKE '%apple%' UNION SELECT * FROM computers WHERE name LIKE '%apple%' UNION SELECT * FROM foods WHERE name LIKE '%apple%'; > > Thanks, > > Bruce Martin > The Martin Solution > [EMAIL PROTECTED] > http://www.martinsolution.com > http://externals.martinsolution.com > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there a pragma to disable triggers?
Regarding: I can't use command line utility as I do this on a smartphone device. and I am using sqlite by c# the Ado .Net library which is very new to me and I can for now only execute insert,delete,update and basic commandtext queries. -- Well, you might have to resort to a more complex method. The following queries may be of help -- they might be part of general "DropAllTriggers" and a "RecreateAllTriggers" subroutines. -- Build SQL to re-create all triggers select sql as BuildSQL from sqlite_master where type= 'trigger'; -- Build SQL to drop all triggers in current database select 'DROP TRIGGER ' || name || ';' as DropSQL from sqlite_master where type= 'trigger'; You may even find that ability to read sql from a file is so useful that you want to implement it within your ADO application. This email and any attachments have been scanned for known viruses using multiple scanners. We believe that this email and any attachments are virus free, however the recipient must take full responsibility for virus checking. This email message is intended for the named recipient only. It may be privileged and/or confidential. If you are not the named recipient of this email please notify us immediately and do not copy it or use it for any purpose, nor disclose its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_prepare/bind to exnter data or just query?
Dennis Cote wrote: > > See, http://www.sqlite.org/c3ref/prepare.html for details on prepare and > previous link for detail on bind_text. > Dennis Cote > Thank you for the links Dennis, this will help me a lot. -- View this message in context: http://www.nabble.com/SQLITE_prepare-bind-to-exnter-data-or-just-query--tp18977257p18983578.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there a pragma to disable triggers?
I can't use command line utility as I do this on a smartphone device. and I am using sqlite by c# the Ado .Net library which is very new to me and I can for now only execute insert,delete,update and basic commandtext queries. - Original Message - From: "Griggs, Donald" <[EMAIL PROTECTED]> To: "General Discussion of SQLite Database" Sent: Thursday, August 14, 2008 5:12 PM Subject: Re: [sqlite] is there a pragma to disable triggers? > Regarding: Is there a pragma to disable triggers on the sqlite > database? > > Hello Marton, > > There's no such pragma to my knowledge. See > http://www.sqlite.org/pragma.html > > Perhaps you can use the command line utility "sqlite3" to ".dump" the > schema of your database, isolate just the CREATE TRIGGER commands, and > call this file "addTriggers.sql" > > Then grep that file for just the CREATE TRIGGER first lines, and edit > these down to a "dropTriggers.sql" file. > > Now the sqlite3 ".read" command will allow you to quickly delete or > restore triggers with a simple batch file. > > > > > > This email and any attachments have been scanned for known viruses using > multiple scanners. We believe that this email and any attachments are > virus free, however the recipient must take full responsibility for virus > checking. > This email message is intended for the named recipient only. It may be > privileged and/or confidential. If you are not the named recipient of this > email please notify us immediately and do not copy it or use it for any > purpose, nor disclose its contents to any other person. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_prepare/bind to exnter data or just query?
flakpit wrote: > > Peter, sorry to be a nuisance but, can you update the same way? Yes, of course. > I've not > idea how that statement would look with this way of doing things > SQLite has copious and well written documentation. You would be well served by reading it rather than trying to divine its operation by scrutinizing code snippets. See section 2.2 of http://www.sqlite.org/capi3.html for details on executing SQL statements. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there a pragma to disable triggers?
Regarding: Is there a pragma to disable triggers on the sqlite database? Hello Marton, There's no such pragma to my knowledge. See http://www.sqlite.org/pragma.html Perhaps you can use the command line utility "sqlite3" to ".dump" the schema of your database, isolate just the CREATE TRIGGER commands, and call this file "addTriggers.sql" Then grep that file for just the CREATE TRIGGER first lines, and edit these down to a "dropTriggers.sql" file. Now the sqlite3 ".read" command will allow you to quickly delete or restore triggers with a simple batch file. This email and any attachments have been scanned for known viruses using multiple scanners. We believe that this email and any attachments are virus free, however the recipient must take full responsibility for virus checking. This email message is intended for the named recipient only. It may be privileged and/or confidential. If you are not the named recipient of this email please notify us immediately and do not copy it or use it for any purpose, nor disclose its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_prepare/bind to exnter data or just query?
Peter Holmes-4 wrote: > > Make that: > > sqlite3_prepare_v2(db,"INSERT INTO ans VALUES (?,?);",-1,&stmt,NULL); > Peter, sorry to be a nuisance but, can you update the same way? I've not idea how that statement would look with this way of doing things -- View this message in context: http://www.nabble.com/SQLITE_prepare-bind-to-exnter-data-or-just-query--tp18977257p18982469.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_prepare/bind to exnter data or just query?
flakpit wrote: > > Peter Holmes-4 wrote: >> Yep. Works great! For example: >> >> sqlite3_prepare_v2(db,"INSERT INTO ans VALUES (?,?);",-1,stmt,NULL); >> sqlite3_bind_text(stmt,0,"Peter's",-1,SQLITE_STATIC); >> sqlite3_bind_text(stmt,1,"Reply",-1,SQLITE_STATIC); >> sqlite3_step(stmt); >> sqlite3_reset(stmt); >> > > so ?,? represent columns to be filled in right? Yes. > and stmt,0 is column 0 and stmt,1 is column 1? Yes, see http://www.sqlite.org/c3ref/bind_blob.html for details. > and the -1 is add to end of db? No, the -1 means use strlen to find the length of the strings (i.e. up to first 0 character). See, http://www.sqlite.org/c3ref/prepare.html for details on prepare and previous link for detail on bind_text. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_prepare/bind to exnter data or just query?
Enrique Ramirez-3 wrote: > > Depends on where you're looking at your block of text. Are you using a > GUI SQLite Manager of sorts, or maybe peeking at the variable's > contents from a dev IDE? > I checked the contents of the db to ensure that all my text was in it and it was. Then I peek the results of a column_text data return and it is truncated at the first CR/LF pair. Wish I knew why. There are no nulls in the data, checked with a hex editor. Maybe there is a problem with my dev IDE's peek function. Hmmm. -- View this message in context: http://www.nabble.com/SQLITE_prepare-bind-to-exnter-data-or-just-query--tp18977257p18982354.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there a pragma to disable triggers?
On 8/14/08, Dennis Cote <[EMAIL PROTECTED]> wrote: > Kodok Márton wrote: > > > > I am working on a syncing project and I do have a lot of triggers to > read/write foreign keys. > > And while I do the sync of one table the triggers are causing a strange > effect (as the other table is not yet synced). > > Is there a pragma to disable triggers on the sqlite database? > > > > > No, there is no way to disable triggers. > > You could save the SQL used to create the triggers from the > sqlite_master table, drop the triggers, do the updates, and then finally > use the saved SQL to recreate the triggers after the updates. > > HTH > > Dennis Cote > Interestingly, I have found need for such a mechanism as well... esp. while updating a table but wishing not to update the TRIGGERed FTS tables. Ended up following the approach suggested above, but would have been nice to have something like WITHOUT TRIGGERS kinda mechanism. Or a PRAGMA DISABLE_TRIGGERS PRAGMA ENABLE_TRIGGERS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_prepare/bind to exnter data or just query?
Peter Holmes-4 wrote: > > Yep. Works great! For example: > > sqlite3_prepare_v2(db,"INSERT INTO ans VALUES (?,?);",-1,stmt,NULL); > sqlite3_bind_text(stmt,0,"Peter's",-1,SQLITE_STATIC); > sqlite3_bind_text(stmt,1,"Reply",-1,SQLITE_STATIC); > sqlite3_step(stmt); > sqlite3_reset(stmt); > so ?,? represent columns to be filled in right? and stmt,0 is column 0 and stmt,1 is column 1? and the -1 is add to end of db? -- View this message in context: http://www.nabble.com/SQLITE_prepare-bind-to-exnter-data-or-just-query--tp18977257p18982253.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there a pragma to disable triggers?
Kodok Márton wrote: > > I am working on a syncing project and I do have a lot of triggers to > read/write foreign keys. > And while I do the sync of one table the triggers are causing a strange > effect (as the other table is not yet synced). > Is there a pragma to disable triggers on the sqlite database? > No, there is no way to disable triggers. You could save the SQL used to create the triggers from the sqlite_master table, drop the triggers, do the updates, and then finally use the saved SQL to recreate the triggers after the updates. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_prepare/bind to exnter data or just query?
SQLite doesn't truncate anything. Either you truncated at the time you > put the data into the database in the first place, or you are truncating > now when looking at the string. It definately wasn't truncated when I put it in, I checked. So as you say, something is truncating it as it is being read out somewhere. I'm just peeking the text in the column_text returned data. -- View this message in context: http://www.nabble.com/SQLITE_prepare-bind-to-exnter-data-or-just-query--tp18977257p18982143.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is there a pragma to disable triggers?
Hello, I am working on a syncing project and I do have a lot of triggers to read/write foreign keys. And while I do the sync of one table the triggers are causing a strange effect (as the other table is not yet synced). Is there a pragma to disable triggers on the sqlite database? Regards, Marton ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_prepare/bind to exnter data or just query?
"flakpit" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] >> I've been using the column_text type to get back a block of text and it's >> being truncated at the first CR/LF pair in the text block and I can't help >> thinking that I could have avoided this somehow by entering it with a >> prepared statement. >> >> Or am I completely wrong? > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > Depends on where you're looking at your block of text. Are you using a GUI SQLite Manager of sorts, or maybe peeking at the variable's contents from a dev IDE? -- // -- Enrique Ramirez Irizarry Lead Developer Indie Code Labs http://www.indiecodelabs.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_prepare/bind to exnter data or just query?
DOH! sqlite3_prepare_v2(db,"INSERT INTO ans VALUES (?,?);",-1,&stmt,NULL); sqlite3_bind_text(stmt,1,"Peter's",-1,SQLITE_STATIC); sqlite3_bind_text(stmt,2,"Reply",-1,SQLITE_STATIC); sqlite3_step(stmt); sqlite3_reset(stmt); I'll shut up now... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Linking databases
Thanks that's exactly what I was looking for. Bruce Martin The Martin Solution [EMAIL PROTECTED] http://www.martinsolution.com http://externals.martinsolution.com On Aug 14, 2008, at 9:04 AM, Alexandre Courbot wrote: >> Is there a way to link and do a select on multiple databases? > > You probably want to use the "attach" command: > > http://www.sqlite.org/lang_attach.html > > Then you can join all your tables as if they were declared in the same > database, without any performance penalty. > Alex. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Linking databases
> Is there a way to link and do a select on multiple databases? You probably want to use the "attach" command: http://www.sqlite.org/lang_attach.html Then you can join all your tables as if they were declared in the same database, without any performance penalty. Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Linking databases
Is there a way to link and do a select on multiple databases? For example I have 3 databases maybe containing a list of file names. DB1 Apple.txt Grape.html Cherry.txt Peach.txt DB2 Dell.txt HP.txt Gateway.txt Apple.txt DB3 Apple Pie.txt Cherry Pie.txt Grape Jelly.txt Now I want to select any record that starts with "apple" in all of the databases. Thanks, Bruce Martin The Martin Solution [EMAIL PROTECTED] http://www.martinsolution.com http://externals.martinsolution.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Table functions emulation
Hello! I did write some wrapper for create table testrange(rowid); select intrange2table (1,10,1,'testrange'); select * from testrange; 1 2 3 4 5 6 7 8 9 10 select intrange2table (100,1000,100,'testrange'); select * from testrange; 1 2 3 4 5 6 7 8 9 10 100 200 300 400 500 600 700 800 900 1000 If you have similar functions - for dates, months, etc. please to publicate these. See code http://paste.pocoo.org/show/82154/ Best regards, Alexey. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System function with Sqlite
On Thu, Aug 14, 2008 at 04:37:48PM +1200, Chris Brown wrote: > After SQLite: [... odd output ...] > pipe:[120]fd1 ?g?T 1?? 2 04:54:50 CST 2008 > 6 > pipe:[120]fd1 ?g?T 1?? 2 04:54:50 CST 2008 [BTW; Your email is encoded in a very odd way and looks quite strange to me (others might have more luck).] Having those pipes seems odd. I assume sqlite closes everthing. As someone else suggest strace might be useful, build your application as a binary and run it under strace: strace -f -o trace.txt ./name/of/your/thang and put trace.txt somewhere for examination. I'm wondering if some library call(s) are doing something unexpected here. Can you check which libc you are using and if there is any LD_PRELOAD or similar active? > I am still relatively new to Linux so some of the commands you > mentioned are unfamiliar to me. I hope this output is what you > wanted. Could you explain how I can set FD_CLOEXEC? If its > unreliable then I really can't use it permanently but it may be a > useful test to run. Some code does something like: for (fd = 2; fd <= 255; ++fd) fcntl(fd, F_SETFD, FD_CLOEXEC); FD_CLOEXEC means that the file descriptors are closed on exec (otherwise they are 'inherited'). The reason I say it's not reliable is that you can have thousands (millions perhaps) of file descriptors so marking them all is cumbersome. It's better to fix the cause of the problem. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] System function with Sqlite
On Thu, Aug 14, 2008 at 10:59:57AM +1200, Chris Brown wrote: > Then this after sqlite3_close but before the second call to system: > > . > .. > 0 > 1 > 2 > 3 > 5 > 6 that seems wrong, for those which are symlinks (ie. 0->6) can you also readlink and print that out too please? it seems like some fd's are beinh held open as a work around you can loop over these fd's (say 2 though 255) and set FD_CLOEXEC (lots of things do this, it's a but of a hack and not entirely reliable) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_prepare/bind to exnter data or just query?
Make that: sqlite3_prepare_v2(db,"INSERT INTO ans VALUES (?,?);",-1,&stmt,NULL); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_prepare/bind to exnter data or just query?
Yep. Works great! For example: sqlite3_prepare_v2(db,"INSERT INTO ans VALUES (?,?);",-1,stmt,NULL); sqlite3_bind_text(stmt,0,"Peter's",-1,SQLITE_STATIC); sqlite3_bind_text(stmt,1,"Reply",-1,SQLITE_STATIC); sqlite3_step(stmt); sqlite3_reset(stmt); flakpit wrote: > I've been escaping single quote characters in all my text fields and using > the sqlite_execute function to put the data into the table. But is it > possible to use the prepare/bind commands to enter data so that I don't have > to do this? > > I've been using the column_text type to get back a block of text and it's > being truncated at the first CR/LF pair in the text block and I can't help > thinking that I could have avoided this somehow by entering it with a > prepared statement. > > Or am I completely wrong? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_prepare/bind to exnter data or just query?
"flakpit" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I've been escaping single quote characters in all my text fields and > using the sqlite_execute function to put the data into the table. But > is it possible to use the prepare/bind commands to enter data so that > I don't have to do this? Yes. > I've been using the column_text type to get back a block of text and > it's being truncated at the first CR/LF pair in the text block SQLite doesn't truncate anything. Either you truncated at the time you put the data into the database in the first place, or you are truncating now when looking at the string. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_prepare/bind to exnter data or just query?
I've been escaping single quote characters in all my text fields and using the sqlite_execute function to put the data into the table. But is it possible to use the prepare/bind commands to enter data so that I don't have to do this? I've been using the column_text type to get back a block of text and it's being truncated at the first CR/LF pair in the text block and I can't help thinking that I could have avoided this somehow by entering it with a prepared statement. Or am I completely wrong? -- View this message in context: http://www.nabble.com/SQLITE_prepare-bind-to-exnter-data-or-just-query--tp18977257p18977257.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
