Re: [sqlite] Autoincrement failure

Give the recent notice of a bug in gcc-4.1 what compiler are you using and how are you compiling? And can you reproduce this with a small example table? Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From:

Re: [sqlite] Last record in db

limit 1; 4|two Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Igor Tandetnik [itandet...@mvps.org] Sent: Monday, August 22, 2011 3:01 PM To: sqlite-users@sqlite.org Subje

Re: [sqlite] Last record in db

:Re: [sqlite] Last record in db On 22 Aug 2011, at 8:43pm, Black, Michael (IS) wrote: > I thought we were answering the question "how can I retrive the last row" -- > though we never got a definition of what "last" meant. [snip] which is, of course, the problem with t

Re: [sqlite] Last record in db

Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Igor Tandetnik [itandet...@mvps.org] Sent: Monday, August 22, 2011 1:14 PM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Last record in db On 8/22/2011 1:56

Re: [sqlite] Last record in db

Sent: Monday, August 22, 2011 12:44 PM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Last record in db On 8/22/2011 1:33 PM, Black, Michael (IS) wrote: > That's because my id is autoincrement and yours is not. What do you mean, mine vs yours? I continue with your example, using th

Re: [sqlite] Last record in db

...@mvps.org] Sent: Monday, August 22, 2011 12:18 PM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Last record in db On 8/22/2011 1:11 PM, Black, Michael (IS) wrote: > Sure it does. > > sqlite> select rowid,id,* from t1 where id=(select max(id) from t1); > 4|4|4|t

Re: [sqlite] Last record in db

___ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Igor Tandetnik [itandet...@mvps.org] Sent: Monday, August 22, 2011 11:49 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Last record in db On 8/22/2011 12:42 PM, Black, Michael (IS) wrote: >

Re: [sqlite] Last record in db

sqlite.org] on behalf of Simon Slavin [slav...@bigfraud.org] Sent: Monday, August 22, 2011 11:32 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] EXT :Re: Last record in db On 22 Aug 2011, at 5:13pm, Igor Tandetnik wrote: > On 8/22/2011 9:52 AM, Black, Michael (IS) wrote: >

Re: [sqlite] Last record in db

sqlite3 does NOT guarantee rowid always increments and never gives FULL return (at least according to the docs). autoincrement does. http://www.sqlite.org/autoinc.html Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate

Re: [sqlite] EXT :Re: Last record in db

What happens when you do: select * from t1 where rowid = (select max( rowid ) from t1); or select * from t1 where rowid in (select max( rowid ) from t1); On Mon, Aug 22, 2011 at 10:01 AM, Cousin Stanley <cousinstan...@gmail.com> wrote: > > Black, Michael (IS) wrote: > >&g

Re: [sqlite] EXT :Re: Last record in db

No...if you use autoincrement you can guarantee that "last" will be the last record inserted. So "select * from mytable where myid=max(myid)" will work where myid is autoincrement. The normal rowid will work also as long as you don't delete the max(rowid) and you don't insert more than

Re: [sqlite] Last record in db

Or if you are talking about some specific select statement. select * from table order by mystuff desc; Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org

Re: [sqlite] Last record in db

Oh...can I guess? select * from table where rowid=max(rowid); Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of

Re: [sqlite] Difference between Setups and Precompiled binaries for windows

I think if you check the archives everybody has come to the conclusion that just putting the amalgamation in your project is your best solution and build it yourself. Then you never have to worry about somebody blowing away the DLL. You don't save anything using the DLL unless your planning

Re: [sqlite] Improving the query optimizer

Run "analyze" and your numbers will make more sense...albeit still not perfect. Those numbers are estimations, ergo the "~" in the answer. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From:

Re: [sqlite] Insert rows into a foreign key table

From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Igor Tandetnik [itandet...@mvps.org] Sent: Monday, August 15, 2011 6:58 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] Insert rows into a foreign key table Black, Michael (IS) <michael.

Re: [sqlite] Insert rows into a foreign key table

insert into orders (OrderNo,P_Id) select 12345,P_Id from persons where LastName='Hansen'; Should do it for you. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org

Re: [sqlite] Bulk Insert

guidance is great. Looks like Sqlite finally solve my problem thus far :). With Regards, Sumit Gupta -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS) Sent: 12 August 2011 17:48 To: General Discussion of SQLite

Re: [sqlite] Bulk Insert

It doesn't hold them in memory...they are still written to disk and just rolled back if you abort the transaction. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org

Re: [sqlite] SQLite with 10M record

rds, Sumit Gupta -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS) Sent: 11 August 2011 17:54 To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite with 10M record Have you ever used SQLite befo

Re: [sqlite] SQLite with 10M record

Have you ever used SQLite before? 10M records is not a big deal. With experience it should take you less than hour to prove this one way or the other. But that depends on you having experience with databases (indexes) and SQLite in particular (WAL mode, exclusive locking, etc). Without

Re: [sqlite] In memory v/s tmpfs

Under Linux: man fuser Will detect other processes which have the DB open. It's done by looking at all the /proc entries. Under windows http://www.codeproject.com/KB/IP/OpenFiles.aspx Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From:

Re: [sqlite] Shell doesn't do

If you don't like upper case then change the code and re-compile. That's the nice part of source code. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org

Re: [sqlite] Shell doesn't do

If you want it just DIY... .mode html select ""; select * from stuff; select ""; Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on

Re: [sqlite] Maximum number of tables in a data file

MongoDB http://www.mondodb.org Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Jaco Breitenbach

Re: [sqlite] Maximum number of tables in a data file

Sounds to me like you're over normalizing things. You'll never want to do a query spanning multiple minutes in your described setup? http://www.sqlite.org/limits.html The main limit that applies to your question is max of 64 tables in a Join. But you apparently never plan on joining

Re: [sqlite] Threading makes SQLite 3x slower??

Also.. . Your elapsed time is using clock() which tells you processor time. With SQLITE_THREADSAFE=2 59.855 - 1 thread 49.535 - 2 threads 92.789 - 3 threads with SQLITE_THREADSAFE=1 61.146 - 1 thread 49.568 - 2 threads 64.932 - 3 threads The way you're splitting your work is bad.

Re: [sqlite] Threading makes SQLite 3x slower??

You didn't show your timing results or say what kind of machine you're running on. I'm also seeing Calculating Subset Sample... SQL error (635): near "ORDER": syntax error I'm running Linux, sqlite 3.7.5, E5520 2.27Ghz Intel with 16 threads possible. Here's my timings just on the first few rows

Re: [sqlite] Field drop work around

Since SQLite is type agnostic why don't you use generic field names? Just name your fields 0-NN and keep a set of defines for field names. Then you just use #define to name the fields. create table mytable (field1,field2,field3,field4); #define NAME "field1" #define ADDR "field2"

Re: [sqlite] Virtual Table xBestIndex and NULL Search Conditions (Bug?)

If it's meaningless then shouldn't it be a syntax error? Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Simon Slavin

Re: [sqlite] Will SQLite supports UnQL?

This is a side-question to this thread...but has anybody every done row-level locking for edit? I can see it: create table t(id int primary key,stuff text, lock l); insert into t values(1,'stuff1',0); select * from t where id=1 and lock=0; // or drop lock to get all and check lock!=0 to

Re: [sqlite] LevelDB benchmark

I ran a few tests. First the 2 tests from Rev 45. Then NORMAL sync, and finally bump the cache up to what sqlite3 needs vs an arbitrary 100M. I think the question should be how well does it perform in cache -- not necessarily how much cache it needs. How much cache is just a comparision of

Re: [sqlite] LevelDB benchmark

bject: EXT :Re: [sqlite] LevelDB benchmark 2011/7/29 Black, Michael (IS) <michael.bla...@ngc.com>: > What they don't say explicitly is that if all you need is key/value > capability then an SQL database is overkill and only slows you down (bit of a > duh factor there though

Re: [sqlite] LevelDB benchmark

What they don't say explicitly is that if all you need is key/value capability then an SQL database is overkill and only slows you down (bit of a duh factor there though not obvious to neophytes). Generally speaking that's one thing they don't teach in college is optimization. I can't count

Re: [sqlite] SELECT query first run is VERY slow

Part of the problem is it seems you can't create an index with rowid: 3.7.5 sqlite> create table t(i int); sqlite> create index idx1 on t(i); sqlite> create index idx2 on t(i,rowid); Error: table t has no column named rowid Any particular reason it can't be included in an index?

Re: [sqlite] SELECT query first run is VERY slow

If I run your sql.txt script with the normalization of kind my first time query shows 0 seconds. D:\x>sqlite3 sq1 > select count(*) as cnt,kind,computer > from log > group by kind,computer > order by cnt desc > > what happens here? > > SELECT * > FROM log INDEXED BY idxlog_kind_computer >

Re: [sqlite] SELECT query first run is VERY slow

That's because they have 2 completely different query plans. I created the table so that id,a,b,c all had the same values so the indexing would be indentical. #include main() { int i; for(i=1;i<=10;++i) { char sql[4096]; sprintf(sql,"insert into abctable(a,b,c)

Re: [sqlite] SELECT query first run is VERY slow

You need to normalize your "kind" value. .pragma cache_size=15000; drop index idxlog_kind_computer; create table kind(id integer,kind text); insert into kind values(1,'debug'); insert into kind values(2,'error'); insert into kind values(3,'info'); insert into kind values(4,'timing'); insert

Re: [sqlite] EXT :Re: SELECT query first run is VERY slow

An 8X speedup is a pretty good achievement...congrats... #1 I take it your query is CPU bound the first time? #2 Can you show us the query planner please? #3 Can you show us the query planner minus the "INDEXED BY"? #4 Can you show us sqlite_stat1? #5 Can you show us your tables now? #6

Re: [sqlite] System.Data.Sqlite problem

And don't you wish Microsoft and other's could spit out intelligent error messages like: "64-bit application trying to load 32-bit DLL". I ding my students whey do stupid stuff like: FILE *fp=fopen(filename,"r"); if (fp == NULL) { fprintf(stderr,"Cannot open file\n"); } Instead

Re: [sqlite] Strange result using JOIN

Try "order by code.rowid" I think that will do what you want and fits in with what sqlite3's behavior should be. As long as you don't delete rows you'll be OK. http://www.sqlite.org/autoinc.html Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From:

Re: [sqlite] SELECT query first run is VERY slow

You need to normalize your database. Though it's easy to put everything in one table it's horrendously inefficient for indexing. Your indexes are huge as you're putting strings in them. Ideally you should never have a string in an index if you can avoid it. Too much space, too long a

Re: [sqlite] Select names where occurences are greater than 10

select id,count(distinct track) from tracks group by id having count(distinct track)>=10; Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on

Re: [sqlite] Performance Improvement

Could you define "awfully slow"? That's pretty hard to tell if your speed is what one should expect. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Paul Linehan

Re: [sqlite] Performance Improvement

Unless your caching at byte-level you don't need all the addresses. For 4K page size mask off the lower 11 bits. You should actually be able to reduce your memory usage by a LOT if you track by pages and not bytes. That will also speed you up along with it. Michael D. Black Senior

Re: [sqlite] Handle leak using IIS on windows 7?

If you showed a small sample code that caused your problem it would sure help a lot. I peeked at the library code. That message is generated when _cnn is null (the DB connection). private void InitializeForReader() { if (_activeReader != null && _activeReader.IsAlive)

Re: [sqlite] Hidding records from the application

I decided to test this so here's an almost-complete example minus any cross-checks on the tables which you should ultimately do. This implements exclusion rules which is what you've been describing. You can make them inclusion rules if you just remove the "not" in the select statement below, but

Re: [sqlite] Hidding records from the application

If you don't know how to get your rules yet then you don't know how to design a solution. Based on what you've said I see 2 more tables. create table (pid int, rule int) create table (rule int, record int) You can have multiple rules per pid, reuse rules across pids, and records can

Re: [sqlite] year, month & day problem

Column numbers are zero-based, not one-based. >From http://www.sqlite.org/c3ref/column_blob.html "The leftmost column of the result set has the index 0. " That's at least part of your problem. So should be: k.AAArticoli=sqlite3_column_int(statmentS, 0);

Re: [sqlite] Hidding records from the application

If each pid sees a completely separate set of rows then add the pid to each row and select by pid. If 2 pid's can see any of the same records this won't work (i.e. all pid's must be mutually exclusive) No views necessary but you could create a view for each pid if you wanted to for some

Re: [sqlite] Index question about index

Would glob be faster than like? Since I assume like has to case-convert? And you don't have to set any pragmas or NOCASE for it to use the index. select * from tsamov where tsamov_code glob 'AFG*'; sqlite> explain query plan select * from tsamov where tsamov_code glob 'AFG*'; sele order

Re: [sqlite] Minimize sqlite3.o size

http://www.sqlite.org/compile.html Read section 1.4 -- tells you all you need to know. First thing you should do is see if there's any significant performance difference with -Os instead of -O3. You may be surprised. Michael D. Black Senior Scientist NG Information Systems Advanced

Re: [sqlite] Storing/editing hierarchical data sets

I thought of another way to do your copy/cut/paste... Assuming you keep the original audio around and use the levels I showed before. create table sequence(level int,parent int,start int,end end); insert into seqeunce values(1,0,0,-1); // note that -1 means "until end of data". See

Re: [sqlite] Storing/editing hierarchical data sets

I can see adding a forward/reverse link to the tables making it a linked-list type structure much like your btree. By default each node is linked to the one in front and back. Then you adjust those pointers for cut/paste operations. You could also do the cut/paste just by copying to a new

Re: [sqlite] Insert not working for sqlite3

If CREATE has no return why do I get a return? To be clear I'm using PHP 5.3.6 and it most definitely gets a return But I re-wrote this according to some PDO examples I found. This is a more complete example. You should be able to run this with a PHP command line -- forget the web

Re: [sqlite] Storing/editing hierarchical data sets

Somebody smarter than I may be able to figure out how to use views to do the upper levels. But if you can afford your database to be a bit less then twice as big just use tables. create table level1(id int,l int,r int); insert into level1 values(1,251,18); insert into level1 values(2,5,91);

Re: [sqlite] using index when using concatination

te> select count(*) from words where word like 'L%P'; 136 CPU Time: user 0.281250 sys 0.00 Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@s

Re: [sqlite] using index when using concatination

3.7.4 doesn't indicate it will use an index in either case with like...but glob seems to call the index...why does glob use the index but like does not? sqlite> create table words(word text); sqlite> create index idx on words(word); sqlite> explain query plan select * from words where "word"

Re: [sqlite] using index when using concatination

Any particular reason you can't build your own string and just pass one parameter? select * from words where "word" like ?; And any reason why you don't create a 2nd field holds the 1st and last char? and index that? Then your query should be blazingly fast as it will actually use the index

Re: [sqlite] Insert not working for sqlite3

I downloaded 5.3.6 from here -- I used the Thread Safe version Installer. http://windows.php.net/download/ And you're script ran just fine and created this: sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE tbl2 (one varchar(10),two varchar(10)); COMMIT; I the modified

Re: [sqlite] SQLite Encryption Extension (SEE) and Public Domain Sqlite

If you hash the username too you don't have that problem. Passwords should always be a 1-way hash. It's actually best to collapse username/password into a single one-way hash. That way it's very difficult to crack it. You can use a user "account#" value that users would have to track for

Re: [sqlite] Insert not working for sqlite3

You're going to have to create a complete stand-alone example if you want us to debug your code for you. Make a simple program that only does that one insert and share that with us. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate

Re: [sqlite] How to search for multiple values in a column including null without repeating column name ?

That would 'splain it...I should have noticed... So we're left with this question... What's the problem with building the query that works? I don't see where you can't make it "column agnostic"...though perhaps there's another definition of that sprintf(sql,"select * from t where %s

Re: [sqlite] How to search for multiple values in a column including null without repeating column name ?

If you can build your SQL string with column_name exactly what is the problem with adding it a 2nd time? How do you build your SQL statement? And I get the same answer with either of your queries in 3.7.4 -- what version are you running and what answer are you getting? Are you saying that

Re: [sqlite] Defining a relationship as unique

e: [sqlite] Defining a relationship as unique On 06/30/11 02:31 PM, Black, Michael (IS) wrote: > sqlite> create table user(userid integer primary key autoincrement,name > varchar, login varchar); > sqlite> create unique index index1 on user(userid); Isn't userid already unique by &q

Re: [sqlite] Defining a relationship as unique

You're getting closeif you don't use a field in a table you don't HAVE to create it. Also...if you want to make your database a bit more bullet proof you want foreign keys to help ensure you maintain the relationship between users and tournaments (otherwise you can accidentally delete

Re: [sqlite] Is this a BUG or am I wrong ? sub-select returns nothing when column id matches the inner query

Use single quotes instead of double sqlite> select (select v from t1 where n='a') wrong,* from a1; wrong|a|b 2000|123|456 2000|999|999 Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From:

Re: [sqlite] Compile sqlite3 with g++? Do I need a wrapper?

I'd recommend NOT relying on the system sqlite3. That way you can control your changes. Get the amalgamation and put sqlite3.c and sqlite3.h in your project. And, you forgot to put in the name for "-o" -- so you would get a file named "-lsqlite3" in your directory. And you'll probably

Re: [sqlite] Substring question

It's not obvious but this works CREATE TABLE x (s string); INSERT INTO "x" VALUES('ab:cdef'); INSERT INTO "x" VALUES('ghij:klmn'); sqlite> select ltrim(ltrim(s,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'),':') from x; ltri cdef klmn Just make sure your char set contains all

Re: [sqlite] EXT :Re: question about sqlite aggregate funcitons

Slavin [slav...@bigfraud.org] Sent: Saturday, June 25, 2011 10:38 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] question about sqlite aggregate funcitons On 25 Jun 2011, at 4:12pm, Black, Michael (IS) wrote: > Can the optimizer recognize these "instant&qu

Re: [sqlite] question about sqlite aggregate funcitons

Can the optimizer recognize these "instant" functions and perhaps lean towards using them? Are there cases where this would NOT be good? I did show you can construct a query which runs at the "correct" speed. sqlite> select * from (select min(i) from test) as a,(select max(i) from test)

Re: [sqlite] question about sqlite aggregate funcitons

s-boun...@sqlite.org] on behalf of Black, Michael (IS) [michael.bla...@ngc.com] Sent: Saturday, June 25, 2011 8:15 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] question about sqlite aggregate funcitons OK...I generated 10M ints and imported them. I get times that I would

Re: [sqlite] question about sqlite aggregate funcitons

NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Luuk [luu...@gmail.com] Sent: Saturday, June 25, 2011 7:37 AM To: sqlite-users@sqlite.org Subject: EXT :Re: [sqlite] ques

Re: [sqlite] question about sqlite aggregate funcitons

Ucaching? Try doing your first select last and see what happens. I'm betting your first "select min(*) from test" will be a lot slower. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From:

Re: [sqlite] Query with UNION on large table

[sqlite-users-boun...@sqlite.org] on behalf of Black, Michael (IS) [michael.bla...@ngc.com] Sent: Thursday, June 23, 2011 2:03 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Query with UNION on large table Any reason you can't add another field to your database? 0=

Re: [sqlite] Query with UNION on large table

Any reason you can't add another field to your database? 0=equal 1=n1n2 The create an index on that field. Then your query plan would look like this: sqlite> explain query plan select n1,n2 from table2 where flag = 1 intersect select n2,n1 from table2 where flag = 2; sele order

Re: [sqlite] Howto...multi-sqlite command string through sqlite3_prepare_v2() to create SINGLE statement

I believe this will work if you put the SQL-required semi-colons at the end of your statements. sql = "BEGIN;"; //you need to add newline here sql += "create table episodes (id integer primary key, season int, name text);"; sql += "insert into episodes(id, season, name) Values(1,2,'bill');";

Re: [sqlite] Help sqlite database corruption

As I said before...welcome to DLL hell... You guessed itthe original DLL will give an error if any of the DLLs it depends on are not loadable. Since sqlite.interop.dll is non-standard you should include ALL DLLs that it depends on in your installation. Subject to Microsoft's

Re: [sqlite] Critical issue

questions are stupid but I cannot figure it out. Best regards, Cyrille Le 18/06/2011 15:50, Black, Michael (IS) a écrit : > Also...try dependency walker to ensure you have all the DLLs you need deployed with your package that aren't "standard". > > http://www.dependencywalk

Re: [sqlite] Critical issue

Also...try dependency walker to ensure you have all the DLLs you need deployed with your package that aren't "standard". http://www.dependencywalker.com/ Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From:

Re: [sqlite] Critical issue

See if this thread helps you -- a couple of different solutions. Both of which you should be able to solve for your users. http://forums.asp.net/t/939729.aspx/1 Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate

Re: [sqlite] need help with a query using datetime

sqlite> create table t(d date); sqlite> insert into t values('2011-12-31 09:00'); sqlite> insert into t values('2011-12-31 12:15'); sqlite> select d,substr(datetime(d,'-12 hours'),1,16) from t; 2011-12-31 09:00|2011-12-30 21:00 2011-12-31 12:15|2011-12-31 00:15 Michael D. Black Senior

Re: [sqlite] A simple SELECT

) [m...@phrio.biz] Sent: Monday, June 13, 2011 12:45 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] EXT :Re: A simple SELECT For example "Test" -Message d'origine----- From: Black, Michael (IS) Sent: Monday, June 13, 2011 7:30 PM To: General Discussion of SQLit

Re: [sqlite] EXT :Re: A simple SELECT

...@phrio.biz] Sent: Monday, June 13, 2011 12:08 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] A simple SELECT If I change the table name it works. I tryed the same query using SqLite3Explorer and it does not give any result ! -Message d'origine- From: Black, Michael

Re: [sqlite] A simple SELECT

Oops...sorry...you're correct...been far too long since I've done assembler. Are we to understand that if you simply give this table a different name it works? That seems to be what you said in your initial email. Are you inserting records in the table in your assembler too? Are you

Re: [sqlite] A simple SELECT

You do this: INVOKE sqlite3_prepare_v2,__hSql,__lpszQuery,-1,eax,edx Then this: INVOKE sqlite3_step,_lpSQLStatment You probably want INVOKE sqlite3_prepare_v2,__hSql,__lpSQLStatement,-1,eax,edx At least from what I tell of your logic. You're not using the same variable fo the statement

Re: [sqlite] To index or not to index?

Ummm...wouldn't it be a whole lot simpler and faster to have an "Online" table? One id is all that's needed unless you want other info. Then you're just inserting and deleting records and the whole table is "online" at any moment. I don't see any reason why this online status needs to be

Re: [sqlite] Handling of IEEE-754 nan and +/-inf in SQLite?

That's the nice thing about standards...there's so many to choose from... :-) Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of

Re: [sqlite] Do I need to migrate to MySQL?

} else { // error occured Con::executef(this, 2, "onQueryFailed", m_szErrorString); delete pResultSet; return 0; } return 0; } On 03/06/2011 15:28, Black, Michael (IS) wrote: > > And do you wrap all your updates inside a transactio

Re: [sqlite] Do I need to migrate to MySQL?

return 0; } return 0; } On 03/06/2011 15:28, Black, Michael (IS) wrote: > > And do you wrap all your updates inside a transaction? > > Michael D. Black > > Sen

Re: [sqlite] Do I need to migrate to MySQL?

And do you wrap all your updates inside a transaction? Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Ian Hardingham

Re: [sqlite] Do I need to migrate to MySQL?

My radar says having a TEXT field as a primary key is bad (your userTable). String compares are horrendously slow. Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org

Re: [sqlite] Cannot load SQLite.Interop.dll but file is in the folder

as the EXE file. I do not know if there is a link but the user who experiences this problem is running my application under WIndows 7 32bit. Is there any possible link? Thanks again Cyrille Le 29/05/2011 20:33, Black, Michael (IS) a écrit : > Welcome to DLL hell...Microsoft keeps changing it... >

Re: [sqlite] Cannot load SQLite.Interop.dll but file is in the folder

Welcome to DLL hell...Microsoft keeps changing it... http://msdn.microsoft.com/en-us/library/ms682586(v=vs.85).aspx Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org

Re: [sqlite] Blob newbie problem

Do I understand you're still seeing a segfault? I assume you're not seeing your "Year retrieved..." statement? You haven't showed us your table definition. Change your strcmp to strcasecmp and see if that fixes it for you. Michael D. Black Senior Scientist NG Information Systems

Re: [sqlite] Memory leak in SQlite

#1 I don't see where you're freeing m_szErrorString (not real sure if it gets malloc'd on success) -- but you do need to free it on errors for sure. And where's your Callback function? Why are you calling SaveResultSet (which you also don't show)? That should probably be done inside the

Re: [sqlite] Transaction speed too slow?

You do realize the number they quote is a MAXnot necessarily what you'll get. With 16 transactions per second you're writing on transaction every 63ms. My understanding is that the hard drive in the netbook is 15ms access time so you're seeing 4 accesses per transaction with the way

Re: [sqlite] EXT : Transaction speed too slow?

Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Nick [maill...@css-uk.net] Sent: Sunday, May 08, 2011 3:52 PM To:

[sqlite] RE : Feature request: Fuzzy searching

Sounds like soundex might work for you. Not sure how it handles diacritics though. http://www.mail-archive.com/sqlite-users@sqlite.org/msg35316.html Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From:

Re: [sqlite] EXT :Re: BUG : round(x,y) not consistent

3.75 on Redhat 5.6 does not have a problem. I get 8.88 16.88 32.88 64.88 Fixed in 3.7.5 maybe? Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org

<    1   2   3   4   5   6   7   8   9   >