Re: [sqlite] Simple SQLite-based spreadsheet?

2012-12-07 Thread Black, Michael (IS)
http://www.sqlmaestro.com May do what you want... Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org

Re: [sqlite] disk image malformed

2012-12-06 Thread Black, Michael (IS)
Can you try doing table copies using select with offset and limit? See where limit crashes the system and then offset past it perhaps? Just a guess on my part as one possibility. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop

Re: [sqlite] Tracing latencies

2012-12-05 Thread Black, Michael (IS)
To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Tracing latencies On 12/05/2012 09:03 PM, Black, Michael (IS) wrote: > Hmmm...looking at that strace sequencing is there some reason those 24-byte > and 1024-byte writes can't be combined? The 1024-byte write is occurring at &g

Re: [sqlite] Tracing latencies

2012-12-05 Thread Black, Michael (IS)
Run this program (change the diff threshold if you want) on the strace log file. This will only show the calls that take "too long" and the time involved. #include #include main(int argc, char *argv[]) { char buf[65535]; double t1=0,t2; FILE *fp=fopen(argv[1],"r");

Re: [sqlite] Tracing latencies

2012-12-05 Thread Black, Michael (IS)
Hmmm...looking at that strace sequencing is there some reason those 24-byte and 1024-byte writes can't be combined? The 1024-byte write is occurring at the end boundary of the 24-byte. That would cut the i/o ops in half and might be a noticeable improvement. A memory copy would be a lot

Re: [sqlite] Tracing latencies

2012-12-04 Thread Black, Michael (IS)
Can you re-run your strace as "strace -tt" and look at the timings to help pinpoint it? Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From:

Re: [sqlite] Tracing latencies

2012-12-04 Thread Black, Michael (IS)
Could it be waitiing on the prior transaction though? Since disk I/O lies it might be syncing the last transaction causing the new one to wait longer. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems

Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

2012-11-30 Thread Black, Michael (IS)
on that platform... -David From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Black, Michael (IS) [michael.bla...@ngc.com] Sent: Friday, November 30, 2012 9:46 AM To: General Discussion of SQLite Database Subject: Re: [sqlite

Re: [sqlite] Windows (slow) vs. iOS/OSX (fast) Performance

2012-11-30 Thread Black, Michael (IS)
Could this be your problem? http://mattgadient.com/2011/02/18/mac-os-x-slow-for-10-15-minutes-after-boot-the-fix/ Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems

Re: [sqlite] Database design preferences

2012-11-30 Thread Black, Michael (IS)
One of my considerations would be whether or not the fields are 1-to-1 to the user or are non-related. In your list for example favorite politician is something non-related to the user and you might want to implement either as a search function or a pulldown list or a tabulated page. So

Re: [sqlite] Sqlite 3.4.1 write performance difference between linux kernel 2.6.33 and 3.4.6

2012-11-29 Thread Black, Michael (IS)
The Linux kernel used to default to writeback for a while until 2.6.36 where it then defaulted to ordered. So you're seeing the ordered behavior now which is the safest mode. http://forum.linode.com/viewtopic.php?t=7815 How to convert to writeback if that's what you want...it is a more dangerous

Re: [sqlite] Converting in-memory sqlite database to char array

2012-11-29 Thread Black, Michael (IS)
to char array On Thu, Nov 29, 2012 at 02:05:02PM +, Black, Michael (IS) scratched on the wall: > And if you want to improve latency you can use fifo's on Unix or > anonymous pipes on Windows and run a thread to send your data > while it's writing since those methods are synchronous.

Re: [sqlite] Converting in-memory sqlite database to char array

2012-11-29 Thread Black, Michael (IS)
And if you want to improve latency you can use fifo's on Unix or anonymous pipes on Windows and run a thread to send your data while it's writing since those methods are synchronous. man popen (you open write in one thread and open a read in another)

Re: [sqlite] Replace on fts4 table results in unexpected matchinfo result

2012-11-27 Thread Black, Michael (IS)
Does this make it weirder or what? If you do the replace after the insert you get the expected result. But if you do the replace, followed by 2 more inserts you get this: SQLite version 3.7.14.1 2012-10-04 19:37:12 Enter ".help" for instructions Enter SQL statements terminated with a ";"

Re: [sqlite] Anomalously slow performance on updates to earlyentries in a DB

2012-11-09 Thread Black, Michael (IS)
> boun...@sqlite.org] On Behalf Of Black, Michael (IS) > Sent: 09 November 2012 14:26 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Anomalously slow performance on updates to > earlyentries in a DB > > >[O'Toole, Eamonn] This definitely sounds like it could be

Re: [sqlite] Anomalously slow performance on updates to earlyentries in a DB

2012-11-09 Thread Black, Michael (IS)
>[O'Toole, Eamonn] This definitely sounds like it could be an issue. There is >just one container_stat entry >confirmed by sqlite3_analyzer output which I'll >post later). So you're saying that the single container_stat table :entry is >potentially being relocated very frequently the closer

Re: [sqlite] Anomalously slow performance on updates to early entries in a DB

2012-11-09 Thread Black, Michael (IS)
Hmmm...is this a disk head seeking problem? You've got several TEXT entries which are either NULL or default to ''; I did a small experiment: on 3.7.13 CREATE TABLE container_stat ( account TEXT, container TEXT, created_at TEXT,

Re: [sqlite] EXT :Re: Compiling SQLite3 with MSVC 2010

2012-11-05 Thread Black, Michael (IS)
4, 2012 at 5:59 AM, Black, Michael (IS) <michael.bla...@ngc.com> wrote: > Hmmm...interesting...I'm using VS 2010 Express 32-bit and I would've assumed > the warnings would match. I also enabled SQLITE_64BIT_STATS to try and force > the first warning but that didn't cause it. >

Re: [sqlite] EXT :Re: Compiling SQLite3 with MSVC 2010

2012-11-04 Thread Black, Michael (IS)
ld All: 2 succeeded, 0 failed, 0 skipped == *Jonas Malaco Filho* 2012/11/3 Black, Michael (IS) <michael.bla...@ngc.com> > You probably have the warning level turned up high. > > Up to level 3 it compiles without warnings. Level 4 starts complaining > loudly. > &g

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

2012-11-04 Thread Black, Michael (IS)
lf of Григорий Григоренко [grigore...@mail.ru] Sent: Sunday, November 04, 2012 1:34 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite]SUGGESTION: "now" as alias for "strftime('%s','now')" Fri, 2 Nov 2012 14:11:26 + от "Black, Michael (IS)" <m

Re: [sqlite] Compiling SQLite3 with MSVC 2010

2012-11-03 Thread Black, Michael (IS)
You probably have the warning level turned up high. Up to level 3 it compiles without warnings. Level 4 starts complaining loudly. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

2012-11-02 Thread Black, Michael (IS)
CREATE TABLE t(id,time); INSERT INTO t VALUES(1,CURRENT_DATE); INSERT INTO t VALUES(2,CURRENT_TIMESTAMP); INSERT INTO t VALUES(3,datetime('now')); INSERT INTO t VALUES(4,date('now')); SELECT * FROM t; 1|2012-11-02 2|2012-11-02 14:10:15 3|2012-11-02 14:10:15 4|2012-11-02 Perhaps the documentation

Re: [sqlite] sqlite eclipse

2012-10-30 Thread Black, Michael (IS)
Sounds ilke you're using a 64-bit JDK. Use the 32-bit JDK. That's what the error is telling you64-bit can't load 32-bit DLL. As long as all your code is 32-bit it will run on 32-bit. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit

Re: [sqlite] how to select " char in sqlite

2012-10-26 Thread Black, Michael (IS)
You appear to be programming in C so that's what this is... Here's a complete example where you can control the table formatting yourself. This is using sqlite3 calls and I made it produce a simple, complete HTML page. This is, of course, tied to your database due to the specific column names.

Re: [sqlite] I/O error on creating index with 3.7.14

2012-10-26 Thread Black, Michael (IS)
Hmmm...looks a lot like 32-bit overflow into a 64-bit number. 2^64 18446744073709551616 Your read offset 18446744071873782392 Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems

Re: [sqlite] how to select " char in sqlite

2012-10-26 Thread Black, Michael (IS)
Here it is with your desire to use system(). The table output you get probably is not going to be formatted the way you like. You can extend the logic here to put special sequences in the string to then replace with formatting. It would really be easier oveall to do this yourself by using the

Re: [sqlite] how to select " char in sqlite

2012-10-24 Thread Black, Michael (IS)
If Windows get FART (find and replace text) from here: http://blog.secaserver.com/2011/07/windows-find-and-replace-text-command-line-utility/ If Unix learn sed: http://www.thegeekstuff.com/2009/09/unix-sed-tutorial-replace-text-inside-a-file-using-substitute-command/ Then sqlite3 test.db create

Re: [sqlite] Getting Error SQLITE_NOTADB

2012-10-23 Thread Black, Michael (IS)
inserting the records in table ...but after tht i am trying to update the coloum value of table and using update command with where clause ..here every time sqlite_exec command failing and rc returns in sqlite3_prepare such as SQLITE_NOTADB CHEERS KRITESH On Oct 24, 2012 2:10 AM, "Black, M

Re: [sqlite] Getting Error SQLITE_NOTADB

2012-10-23 Thread Black, Michael (IS)
I assume you have some program doing the sqlite_exec? Care to show us your code? I just ran a test doing what you describe with SQLite Database Browser Version 2.0b1 and the following program compiled against 3.7.13; After the 2nd run of this program there are 2 records in test.db which the

Re: [sqlite] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22

2012-10-22 Thread Black, Michael (IS)
Have you tried making your own DLL from each source and comparing them when they are compiled the same? Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From:

Re: [sqlite] Find first non-NULL values of several columns

2012-10-21 Thread Black, Michael (IS)
How's about you store your interpolated value during insert? You can use a binary mask of say, 16384, to indicate the value is interpolated in case you need to know that. In the original data you sent only one value can be interpolated at record 3. You probably want the interpolation to be

Re: [sqlite] Find first non-NULL values of several columns

2012-10-19 Thread Black, Michael (IS)
Does a view help you out? Are you just trying to make it easier for somebody to create a query for that answer without typing so much? create table v(a integer primary key,b,c,d,e,f); insert into v values(0,NULL,NULL,2,null,9); insert into v values(1,1,null,3,null,8); insert into v

Re: [sqlite] EXT :Re: CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22

2012-10-18 Thread Black, Michael (IS)
[sqlite-users-boun...@sqlite.org] on behalf of Black, Michael (IS) [michael.bla...@ngc.com] Sent: Thursday, October 18, 2012 10:16 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] EXT :Re: CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22 And using Dan's downloads

Re: [sqlite] EXT :Re: CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22

2012-10-18 Thread Black, Michael (IS)
And using Dan's downloads 3.7.14.1 took 30.4 seconds 3.6.22 took 40.94 (there was a lot of idle time towards the end here...disk I/O I assume) Re-did my compilation again... 3.7.14.1 took 26.8 Recompiled under Visual Studio Express 2010 "cl /O2 sqlite3.c shell.c" 3.7.14.1 took 26.2 seconds I'm

Re: [sqlite] CREATE INDEX is 13 times slower with 3.7.14.1 than with 3.6.22

2012-10-18 Thread Black, Michael (IS)
I used 3.7.14.1 Compiled thusly with Visual Studio Express 2008 cl /O2 sqlite3.c shell.c CREATE INDEX idx_namen_name ON Namen(name); Took 26.6 seconds and one CPU was pegged the whole time. I'm on a 3Ghz 8-core machine. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced

Re: [sqlite] SQLite flush on disk to calc db file hash,how?

2012-10-17 Thread Black, Michael (IS)
Are you maybe using WAL mode? Do you have any other files alongside your database like *.db-shm or *.db-wal? If so, you can just cat all the files together and pipe through md5sum or such. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit

Re: [sqlite] find sequential groups

2012-10-16 Thread Black, Michael (IS)
Ok...how about with triggers then? This will give a unique number to each sequence as you insert them. CREATE TABLE Test(ID,Value,Group_Marker); CREATE TRIGGER insert_trigger1 after insert on Test WHEN new.id=1 BEGIN UPDATE Test set Group_Marker=1; END; CREATE TRIGGER insert_trigger2 after

Re: [sqlite] EXT : find sequential groups

2012-10-16 Thread Black, Michael (IS)
Do this work for you? CREATE TABLE Test(ID,Value,Group_Marker); INSERT INTO "Test" VALUES(1,'D',0); INSERT INTO "Test" VALUES(2,'X',0); INSERT INTO "Test" VALUES(3,'X',0); INSERT INTO "Test" VALUES(4,'X',0); INSERT INTO "Test" VALUES(5,'A',0); INSERT INTO "Test" VALUES(6,'B',0); SELECT * FROM

Re: [sqlite] 5. Re: Sqlite, Is it possible to calculate the length of the longest increasing subsequence using an UDF

2012-10-16 Thread Black, Michael (IS)
subsequence using an UDF Black, Michael (IS) <michael.bla...@ngc.com> wrote: > Pseudo-code: > > lastchar=''; > For (char c in array) > if (lastchar = '' || c = lastchar+1) >curseq.push(c); > else >curseq.clear(); >curseq..push(c); > end The longest

Re: [sqlite] 5. Re: Sqlite, Is it possible to calculate the length of the longest increasing subsequence using an UDF

2012-10-16 Thread Black, Michael (IS)
Maybe I'm missing something (wouldn't surprise me) but I can think of O(n) traversal of the array for doing this. Not in SQL of course but you should be able to write a user function for it. Pseudo-code: lastchar=''; For (char c in array) if (lastchar = '' || c = lastchar+1)

Re: [sqlite] EXT :Re: System.Data.SQLite Field Name are surrounded by double quotes for Views

2012-10-13 Thread Black, Michael (IS)
And if you don't quote the 2nd select it comes out OK. This is 3.6.13: sqlite> SELECT "id" FROM "TESTVIEW"; -- Observe the results here. "id" -- test sqlite> SELECT id FROM "TESTVIEW"; -- Observe the results here. id -- Presumably a simple bug for the powers-to-be to fix.

Re: [sqlite] light weight write barriers

2012-10-12 Thread Black, Michael (IS)
There isn't Somebody sure wasted their time on this article then... http://www.linux-magazine.com/w3/issue/78/Write_Barriers.pdf Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems

Re: [sqlite] Seemingly random Access violation errors (resent)

2012-10-08 Thread Black, Michael (IS)
Can you check the difference in stack size between the emulator and the real device? The emulator could well have a larger default stack size and you're getting stack overflow on the real device. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions

Re: [sqlite] Subtract times hh:mm:ss

2012-10-07 Thread Black, Michael (IS)
The word times in my previous post confused/misled you. These are execution times, not values produced by SQL. RBS On Sunday, October 7, 2012, Black, Michael (IS) wrote: > You expect the readers on this list to go find your old post and then look > at what you're NOT doing now? You ask

Re: [sqlite] Subtract times hh:mm:ss

2012-10-07 Thread Black, Michael (IS)
...@gmail.com] Sent: Sunday, October 07, 2012 8:18 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Subtract times hh:mm:ss There are no different answers and I think all the information is in the first post. RBS On Oct 7, 2012 1:21 PM, "Black, Michael (IS)" &l

Re: [sqlite] Subtract times hh:mm:ss

2012-10-07 Thread Black, Michael (IS)
You haven't provided enough info for anybody to tell what's going on. What data are you substracting? Can you provide an sql dump of the data that gets different answers and your code? You can' even get fractional seconds from those statements as the time format only supports hr/min/sec

Re: [sqlite] Seemingly random Access Violation errors

2012-10-05 Thread Black, Michael (IS)
It would seem to me that if GC.Collect fixes the problem than all you've done is move the problem to someplace else that isn't fatal (at the moment) and could rear its ugly head at most any time. The reason your emulator doesn't throw the error would be for the same reason...different memory

Re: [sqlite] Problem with Foreign Key constraints

2012-10-01 Thread Black, Michael (IS)
You don't show any code but it sounds like you're using volatile variables. Are you using SQLITE_STATIC instead of SQLITE_TRANSIENT? http://www.sqlite.org/c3ref/bind_blob.html Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop

Re: [sqlite] Issue with SQLite3 for WinRT ARM

2012-10-01 Thread Black, Michael (IS)
I took a wee bit of a look at your project -- don't have Win 8 so can't debug it. And you didn't include the sqlite assembly anyways. But...if I read your comments correctly it appears that the primary difference between what works and what doesn't is the size of the SQL string. So...this

Re: [sqlite] Sending SQLite3 .timeout command in Command-Line Mode

2012-09-27 Thread Black, Michael (IS)
The other thing you should do is check the exit status of sqlite3. if not 0 then an error occurred. Plus parse the output to see if you get any errors -- in specific handle the errors you know about and show errors that need a handler. So for BUSY and LOCKED you may loop for a while retrying

Re: [sqlite] EXT :Re: Sending SQLite3 .timeout command in Command-Line Mode

2012-09-27 Thread Black, Michael (IS)
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Black, Michael (IS) [michael.bla...@ngc.com] Sent: Thursday, September 27, 2012 9:26 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] EXT :Re: Sending SQLite3 .timeout command

Re: [sqlite] EXT :Re: Sending SQLite3 .timeout command in Command-Line Mode

2012-09-27 Thread Black, Michael (IS)
On Thu, Sep 27, 2012 at 10:07 AM, Black, Michael (IS) < michael.bla...@ngc.com> wrote: > The command does need to be in quotes if it's more than one word. sqilte3 > expects 1 argument for the command. > > I'm not familiar with applescript but I assume you have to escape quo

Re: [sqlite] Sending SQLite3 .timeout command in Command-Line Mode

2012-09-27 Thread Black, Michael (IS)
.timeout 2\" " & databasePath & " \"select * from "& table1 & ";\"" or can I simply use ? set xxx to do shell script "sqlite3 -cmd .timeout 2 " & databasePath & " \"select * from " & table1 & ";\&qu

Re: [sqlite] Sending SQLite3 .timeout command in Command-Line Mode

2012-09-27 Thread Black, Michael (IS)
SQLite3 .timeout command in Command-Line Mode Thank you Michael. I can't find anything in the documentation about the -cmd switch. Will you point me in the right direction? Also, a 2 second timeout would be .timeout 2000 , right? John On Thu, Sep 27, 2012 at 8:36 AM, Black, Michael (IS) <michael.

Re: [sqlite] Sending SQLite3 .timeout command in Command-Line Mode

2012-09-27 Thread Black, Michael (IS)
Try the -cmd switch. Probably the easiest solution. set xxx to do shell script "sqlite3 -cmd \".timeout 2\" " & databasePath & " \"select * from "& table1 & ";\"" Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman

[sqlite] :Re: DELETE Query Assistance Please

2012-09-24 Thread Black, Michael (IS)
You said you need to keep something like 30 days, right? Why convert at all? What's wrong with this: delete from mytable where mytime < max(mytime)-30 If you want to round it off to whole days: delete from mytable where mytime < round(max(mytime)-.5)-30 Or is there something else you need to

Re: [sqlite] Is it possible to get the amalgation as individual files

2012-09-22 Thread Black, Michael (IS)
You may just want to split the amalgamation code...a wee bit easier...see the split utility in this dicussion. http://sqlite.1065341.n5.nabble.com/SQLite-Amalgamation-td11315.html Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop

Re: [sqlite] Store error messages in thread local memory

2012-09-20 Thread Black, Michael (IS)
SK> * using a single connection for all threads: 11 seconds > SK> * using one connection per thread: 59,3 seconds > > > SK> On Thursday, 20. September 2012 at 15:37, Black, Michael (IS) wrote: > > > > You don't say how much speed difference you see > > > >

Re: [sqlite] Store error messages in thread local memory

2012-09-20 Thread Black, Michael (IS)
ELECT *" on this table from 100 concurrent threads where each thread randomly selected 20,000 table entries. The results are: * using a single connection for all threads: 11 seconds * using one connection per thread: 59,3 seconds On Thursday, 20. September 2012 at 15:37, Black, Michael

Re: [sqlite] Store error messages in thread local memory

2012-09-20 Thread Black, Michael (IS)
-users-boun...@sqlite.org] on behalf of Black, Michael (IS) [michael.bla...@ngc.com] Sent: Thursday, September 20, 2012 8:37 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Store error messages in thread local memory You don't say how much speed difference you see

Re: [sqlite] Store error messages in thread local memory

2012-09-20 Thread Black, Michael (IS)
You don't say how much speed difference you see But a separate connection will have separate caches. So you could just be seeing a difference in caching behavior. One connection uses one cache so will be in L1/L2/L3 cache more often than multiple threads thrashing the cache.

Re: [sqlite] sqliteman vs sqlite3 tcl package

2012-09-18 Thread Black, Michael (IS)
2 things #1 Create indexes on testTable.deTestRecordId, dataXyTable.deTestRecordid, and testTable.testName. #2 Do you really need the "LIKE" operator? That's going to scan the entire table every time. If you can change that to "=" you'll likely run a lot faster too. This shouild speed

Re: [sqlite] Count(*) help

2012-09-17 Thread Black, Michael (IS)
though sqlitebrowser declared them as empty with '' ! On 17 September 2012 15:50, Black, Michael (IS) <michael.bla...@ngc.com>wrote: > Or just fix the existing table: > > update members set year2007=NULL where year2007=''; > update members set year2008=NULL where year2008=''; >

Re: [sqlite] Count(*) help

2012-09-17 Thread Black, Michael (IS)
Or just fix the existing table: update members set year2007=NULL where year2007=''; update members set year2008=NULL where year2008=''; update members set year2009=NULL where year2009=''; update members set year2010=NULL where year2010=''; update members set year2011=NULL where year2011='';

Re: [sqlite] Count(*) help

2012-09-17 Thread Black, Michael (IS)
You have a bad table structure which is helping to cause your problem. It's pretty obvious that you don't want one column per year, you want want a membership table that has member,year, and paid status (or whatever info you're keeping for year). The way you have you have to modify your

Re: [sqlite] Getting query results as new records are added

2012-09-13 Thread Black, Michael (IS)
If you use rowid correctly (always incrementing rowid by using AUTOINCREMENT) you can always query records > lastrowid. That's probably easier. http://www.sqlite.org/autoinc.html So something like: startrowid = 0; lastrowid = select max(rowid) from mytable; select * from mytable where rowid >=

Re: [sqlite] C++ - HOW MANY rows?

2012-09-12 Thread Black, Michael (IS)
Try using this method...you just need to ensure mystmt is set to NULL to start with and reset to NULL in finalize(). const int mySQLite3::read_int(int pos) throw(somexception) { if (mystmt == NULL) { // Ensure mystmt is set to NULL in constructor rc = sqlite3_prepare_v2(db,

Re: [sqlite] : C# access to SQLite and Windows 8

2012-09-11 Thread Black, Michael (IS)
Have you looked at this? http://timheuer.com/blog/archive/2012/08/07/updated-how-to-using-sqlite-from-windows-store-apps.aspx Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems _

Re: [sqlite] selecting real values

2012-09-11 Thread Black, Michael (IS)
op Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org] Sent: Tuesday, September 11, 2012 8:01 AM To: General Discussion of SQLite Database Subject: EXT :R

Re: [sqlite] selecting real values

2012-09-11 Thread Black, Michael (IS)
A quick experiment shows that 3.7.14 rounds off the last 2 digits of a double-precision. As of 3.7.14 sqlite3 rounds to 15 significant digits when using the internal formatting routines. sqlite3 test.db SQLite version 3.7.14 2012-09-03 15:42:36 Enter ".help" for instructions Enter SQL

Re: [sqlite] instr function or equivalent

2012-09-10 Thread Black, Michael (IS)
rt.smissa...@gmail.com> wrote: > Nice one! Works here. > > RBS > > > On 9/10/12, Black, Michael (IS) <michael.bla...@ngc.com> wrote: >> Does this work for you? >> >> SQLite version 3.7.13 2012-06-11 02:05:22 >> Enter ".help" for ins

Re: [sqlite] instr function or equivalent

2012-09-10 Thread Black, Michael (IS)
Does this work for you? SQLite version 3.7.13 2012-06-11 02:05:22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table t(s); sqlite> insert into t values('Logging in user [aa] from [10.165.69.247]'); sqlite> insert into t

Re: [sqlite] C++ - WHERE clause - 2nd update

2012-09-07 Thread Black, Michael (IS)
You're example should work if you only prepare the statement once. So assuming mystmt is set to NULL on your object creation. if (mystmt == NULL) { rc = sqlite_prepare_v2. } Then reset it to NULL again when you set apstr="finished". After sqlite3_finalize(mystmt). That way you're next

Re: [sqlite] EXT : C++ - WHERE clause - update

2012-09-06 Thread Black, Michael (IS)
You need to : cout << this->SQLStatement.c_str() << endl; Then put that SQL into the sqlite3 shell against your database and ensure you actually get rows back. You also need to be sure you're looking at the same database. Many times people have multiple copies and the one the program uses is

Re: [sqlite] Read-only media

2012-09-06 Thread Black, Michael (IS)
'twould appear so...the shell even knows about a read-only database. $ sqlite3 test.db SQLite version 3.7.9 2011-11-01 00:52:41 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table test(a,b); sqlite> insert into table values(1,2); Error: near "table":

Re: [sqlite] C++ - WHERE clause

2012-09-06 Thread Black, Michael (IS)
Yeah -- I should've been in a better teaching mode Trying to keep things simple opens up these type of security problemsthough there are lots of situations where this works just fine and is no problem at all (e.g. when you don't have user input or it's completely under your own control

Re: [sqlite] C++ - WHERE clause

2012-09-06 Thread Black, Michael (IS)
And, when you have problems, you should always examine your SQL by running the EXACT same string you generate in your program through the sqlite3 shell. This will help you to figure out if your SQL is wrong or your C++ is wrong. For example even just your SELECT portion generates the wrong

Re: [sqlite] why no such column in sqlite3 ?

2012-08-30 Thread Black, Michael (IS)
As for sprintf what they didn't tell you is that you don't want to use that due to security considerations. If you are getting ANY data from user input they can craft sql injection attacks which sprintf is very susceptible to. Binding the values helps to ensure they can't do that. Michael D.

Re: [sqlite] Pragma Synchronous=OFF is not working

2012-08-28 Thread Black, Michael (IS)
time to parsing and insert the 500 image =42 sec > Time to insert one image in table after parsing = .02 Sec > Time to parsing one images = .04 sec > > Cheers > kritesh > > On Tue, Aug 28, 2012 at 9:22 PM, Black, Michael (IS) < > michael.bla...@ngc.com > > wrote:

Re: [sqlite] Pragma Synchronous=OFF is not working

2012-08-28 Thread Black, Michael (IS)
the values using sqlite_binding will effect the performance .. Is performance due to hardware like SD card speed or Filesystem ? Cheers kritesh On Tue, Aug 28, 2012 at 9:55 PM, Black, Michael (IS) <michael.bla...@ngc.com > wrote: > So you're already doing the smart thinggoodand

Re: [sqlite] Pragma Synchronous=OFF is not working

2012-08-28 Thread Black, Michael (IS)
stored ) in SD card . Total time to parsing and insert the 500 image =42 sec Time to insert one image in table after parsing = .02 Sec Time to parsing one images = .04 sec Cheers kritesh On Tue, Aug 28, 2012 at 9:22 PM, Black, Michael (IS) <michael.bla...@ngc.com > wrote: > I think

Re: [sqlite] Pragma Synchronous=OFF is not working

2012-08-28 Thread Black, Michael (IS)
I think the first thing you'll hear is to NOT store the video data in the database. Just store a file path. That is much faster and should complete a lot faster than your expectations. Is there some specific reason why you want the blob data in your database? How long does it take you just to

Re: [sqlite] Pragma Synchronous=OFF is not working

2012-08-28 Thread Black, Michael (IS)
Tell us what kind of speed you're seeing. And what your insert looks like. Then tell us what you expect. Then we can tell you if your expectations are reasonable or if you're already getting as much speed as one can expect. Michael D. Black Senior Scientist Advanced Analytics Directorate

Re: [sqlite] EXT :Re: Multi-Thread Reads to SQLite Database

2012-08-10 Thread Black, Michael (IS)
Why should shared cached be serialized when all the threads are reading? I can see it for writing, but not just for reading. There must be some logic that be done to allow this I would think (he said without looking at the code). Michael D. Black Senior Scientist Advanced Analytics

Re: [sqlite] C# Dynamic data type

2012-08-07 Thread Black, Michael (IS)
You can use sscanf to determine data type...I've done it before using a method that's not obvious... You parse from most restrictive to least restrictive format like this...this will accept any valid float format including scientific notation. #include enum {UNKNOWN, FLOAT, INT, STRING};

Re: [sqlite] Suggestions for approximate date

2012-08-07 Thread Black, Michael (IS)
I'd vote for the date-range as that can be indexed and result in fast retrieval. The separate column for accuracy would be a computed range and not indexable. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information

Re: [sqlite] sqlite3 database unreadable on Mountain Lion

2012-08-06 Thread Black, Michael (IS)
Fully qualified path names may still both load the same shared library. I assume you have "ldd" available? Run that on the binaries and see which library they'll load. Probably the same one unless they are statically linked. Michael D. Black Senior Scientist Advanced Analytics Directorate

Re: [sqlite] Windows I/O (was: Initial read speed greater than subsequent)

2012-08-01 Thread Black, Michael (IS)
You may be interested in this article: http://www.drdobbs.com/parallel/multithreaded-file-io/220300055?pgno=2 Mutli-threaded reading of multiple files (which is basically what you're talking about by splitting a file in half) is only faster if you have multiple disks (in this article that's a

Re: [sqlite] EXT : Unknown module FTS4

2012-07-30 Thread Black, Michael (IS)
ication. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Black, Michael (IS) > Sent: Sunday, 29 July, 2012 06:22 > To: Genera

Re: [sqlite] EXT : open database on Linux. Already db created on Mac.

2012-07-29 Thread Black, Michael (IS)
You familiar with the sqlite3 shell? sqlite-shell here: http://www.sqlite.org/sqlite-shell-linux-x86-3071300.zip sqlite3 filename Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems

Re: [sqlite] EXT : Unknown module FTS4

2012-07-29 Thread Black, Michael (IS)
You probably have another shared library in your path that is getting loaded first. Since you said shared library and not DLL I assume you're using Unix of some sort? Run "ldd" on your GUI app and see what library it says it will use. Also, you should have either strace or truss which can

Re: [sqlite] C++ - Finalizing my SQLite interface

2012-07-28 Thread Black, Michael (IS)
Or since in C++ use an unordered_map. Add your statement pointers to that, and delete them from the map when finalized. Then walk through that map on destruction to finalize all you haven't cleaned up yourself. Given the way he's developing I would make a function to do this that pre-checks

Re: [sqlite] C++ - All the data in ONE row

2012-07-25 Thread Black, Michael (IS)
In keeping with your example what you want to do is add a "done" flag to your write class. So you tell it when your SQL can be executed. Something like this: void someClass::write2tblName() { stmtName = "INSERT INTO name (n_id, title, fname, mname, lname) VALUES (?, ?, ?, ?, ?)"; int

Re: [sqlite] EXT : C++ - sqlite3_extended_result_codes(

2012-07-24 Thread Black, Michael (IS)
Should be this: on = true = !0 = 1 (other !=0 values also work typically) off = false = 0 Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From:

Re: [sqlite] read sql script file

2012-07-24 Thread Black, Michael (IS)
You're going to get questions like "why do you want to do this" so you may as well tell us now. The usual way to do his is to execute the sql yourself using statement prepares and step. It gives you a lot more control over error messages. Why don't you want to do it this way? The 2nd way

Re: [sqlite] C++ - Creating Table

2012-07-23 Thread Black, Michael (IS)
Just as a sanity check your code does work OK. I made it a standalone program. #include #include "sqlite3.h" using namespace std; class mySQLite3Class { private: //SQLite3 sqlite3* db; //SQLite3 string dbName; // Database name string apstr; // All Purpose String string

Re: [sqlite] SQLite Shell Bug, Ignores Separators in Quotes Sometimes When Importing Data

2012-07-23 Thread Black, Michael (IS)
Nope -- that doesn't work. Seems to me if the import is going to assume the field is text it should also recognize that if it doesn't start with a quote it shouldn't assume that all quotes are delimiters. Plus, it should recognize that any quotes that aren't at the beginning or end-of-field

Re: [sqlite] SQLite Shell Bug, Ignores Separators in Quotes Sometimes When Importing Data

2012-07-23 Thread Black, Michael (IS)
Hmmmyour data import works just fine in 3.7.9...but you're correct that 3.7.13 burps with that error message doing the same import. So something changed C:\sqlite>sqlite3 data.db SQLite version 3.7.9 2011-11-01 00:52:41 Enter ".help" for instructions Enter SQL statements terminated with

Re: [sqlite] database AND table already exist?

2012-07-20 Thread Black, Michael (IS)
] database AND table already exist? Black, Michael (IS) <michael.bla...@ngc.com> wrote: > table_info() will be faster than doing "select *" I would think in most all > cases. To check the existence of a table, you don't need to actually run the select statement - just prepare

Re: [sqlite] database AND table already exist?

2012-07-20 Thread Black, Michael (IS)
I needed a quick excercise this morning. Never used table_info() before. table_info() will be faster than doing "select *" I would think in most all cases. #include #include #include #include "sqlite3.h" using namespace std; bool dbExists(string dbName) { sqlite3 *db; int rc =

  1   2   3   4   5   6   7   8   9   >