Re: [sqlite] Index on joined statements

Okay, thanks! I think I understand better now, how to proceed. Regards, Jonas On Fri, Nov 27, 2009 at 3:19 AM, Igor Tandetnik wrote: > Jonas Sandman wrote: >> Yes, but considering that I first join on typeid and then have name >> and subspecies in the where, wouldn't that

Re: [sqlite] Sqlite Java blob

I really think your simply missing a ps.close(); before your connection.commit() Ulric ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Java blob

Thanks for taking the time Ulric, I'm using sqlitejdbc-v054 from http://files.zentus.com/sqlitejdbc/, I notice they are up to v056, so I'll give that a go. The stack trace ERROR 53:13 [DB.prepareStatement() 697]: Error in sql: INSERT INTO delay_forms (t2_access_wp_action_required, t2_access_key,

Re: [sqlite] Sqlite Java blob

Having the complete stack trace of the exception would help a lot. Here are some thoughts: 1- You did not explicitly close the preparedstatement before calling commit. 2- You are trying to commit without having started a transaction. I don't think the problem is related to the setBytes() as I

Re: [sqlite] Sqlite Java blob

Ulric Auger wrote: > What is failing at line: [DB.prepareStatement() 686] > > Connection.prepareStatement("INSERT INTO delay_forms (...) VALUES (...)"); > > OR > > ps.executeUpdate() > > Your code (between ) seem fine. > > Ulric Hi ulric, the line that is failing 695 connection.commit();

Re: [sqlite] Index on joined statements

Jonas Sandman wrote: > Yes, but considering that I first join on typeid and then have name > and subspecies in the where, wouldn't that index be optimal for that > query? Again, the order of conditionals in the query is largely irrelevant. Remember, your statement is equivalent to this one:

Re: [sqlite] sqlite-users Digest, Vol 23, Issue 26

Peter Haworth wrote: > > I have this question too. I'm not programming in C so don;t have > access to the sqlite_last_insert_rowid > Yes you do. You just didn't realize it. Using the cmd line tool: F:\sqlite>sqlite3 SQLite version 3.5.9 Enter ".help" for instructions sqlite> create

Re: [sqlite] fsync/fdatasync problem on UBIFS

>> I'm using SQLite 3.6.20 on an ARM Linux device which uses the UBIFS >> filesystem (on OneNAND flash). >> >> When I perform a database update, and cut the power a few seconds >> later, the >> changes are rolled back >> when the device restarts. This is because after the restart the >>

Re: [sqlite] Index on joined statements

Yes, but considering that I first join on typeid and then have name and subspecies in the where, wouldn't that index be optimal for that query? Jonas On Thu, Nov 26, 2009 at 4:42 PM, Igor Tandetnik wrote: > Jonas Sandman wrote: >> Doesn't that mean that my original

Re: [sqlite] Huge Table with only one field -- efficient way to create index ?

Simon Slavin schrieb: > On 26 Nov 2009, at 2:50pm, Thomas Lenders wrote: > > >> I am looking for an exact match as in I need to know if a number exists >> in the table or not. >> Something like "select count(*) from ART where artnr='0123456789'". >> >> I could store the data in a text file

Re: [sqlite] Huge Table with only one field -- efficient way to create index ?

On 26 Nov 2009, at 2:50pm, Thomas Lenders wrote: > I am looking for an exact match as in I need to know if a number exists > in the table or not. > Something like "select count(*) from ART where artnr='0123456789'". > > I could store the data in a text file instead but then I would have to >

Re: [sqlite] Huge Table with only one field -- efficientway to create index ?

Actually, what did was create a text file including : CREATE TABLE [ART] ([artnr] INTEGER NOT NULL); CREATE UNIQUE INDEX [IDX_ART] ON [ART]([artnr] ASC); INSERT INTO "ART" VALUES('01'); INSERT INTO "ART" VALUES('02'); Etc.and then ran "sqlite3.exe my.db < my.sql" on the

Re: [sqlite] Index on joined statements

Jonas Sandman wrote: > Doesn't that mean that my original suggestion is correct then? > > create index idx_animals on animals(typeid, name, subspecies) > > as those three columns of the animals table are used in the select? For this particular query, and for one particular way of executing this

Re: [sqlite] Huge Table with only one field -- efficientway to create index ?

As Igor says, it's most likely the way you're creating your SQL query. For example, if you're using C/C++ and you're trying to write a 64 bit number to a string using the %d flag in sprintf(), if won't work properly - you'd need to use %I64d for 64 bit numbers. 32-bit integers go up to (unsigned)

Re: [sqlite] Index on joined statements

I see! Doesn't that mean that my original suggestion is correct then? create index idx_animals on animals(typeid, name, subspecies) as those three columns of the animals table are used in the select? Regards, Jonas On Thu, Nov 26, 2009 at 4:05 PM, Igor Tandetnik wrote: >

Re: [sqlite] Huge Table with only one field -- efficient way to create index ?

Thomas Lenders wrote: > I tried creating the field as INTEGER, but 10 digit numbers where > imported as 0. SQLite uses 64-bit integers, for about 19 decimal digits. The problem must be with the software that populates the table. > I assume the field was created as 32 bit integer and invalid

Re: [sqlite] Huge Table with only one field -- efficient way to create index ?

Hi Nick. Yes, actually that leads me to another question. :) I tried creating the field as INTEGER, but 10 digit numbers where imported as 0. I assume the field was created as 32 bit integer and invalid entries got a default value. I also tried LONGINT, BIGINT and INT64 but no joy. What would

Re: [sqlite] Index on joined statements

Jonas Sandman wrote: > But I guess the answer is that only the "where" parts should be > indexed, not the id's in the joins? No, that's generally not true. ON clauses in joins are basically a syntactic sugar (though there's a subtle difference in case of outer joins). These three statements are

Re: [sqlite] Huge Table with only one field -- efficient way to create index ?

Out of interest, is all the data in the artnr field numeric? If so, why are you storing it as text and not an integer? Integer searching is much, much faster. Nick. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Thomas

Re: [sqlite] Index on joined statements

Yes, I forgot to add "at.name as AnimalType" in the select. It's not a real-life sample, it was just a way to try to describe what I am thinking about... But I guess the answer is that only the "where" parts should be indexed, not the id's in the joins? /Jonas On Thu, Nov 26, 2009 at 3:38 PM,

Re: [sqlite] Index on joined statements

Jonas Sandman wrote: > If you have a join in an SQL-query, how do you add the statements to > optimize the query in an index? > > For example: > > select a.* from animals a > join animaltype at on at.id=a.typeid > where a.name='Monkey' and a.subspecies=2 Why are you joining with animaltype

Re: [sqlite] Huge Table with only one field -- efficient way to create index ?

Simon Slavin schrieb: > On 26 Nov 2009, at 2:04pm, Thomas Lenders wrote: > > >> I am using SQLite on a mobile device. >> >> I have this one table which has only one field, but I need to search in >> the table very quickly. >> When creating an index on this field the size of the database is

Re: [sqlite] Huge Table with only one field -- efficient way to create index ?

On 26 Nov 2009, at 2:04pm, Thomas Lenders wrote: > I am using SQLite on a mobile device. > > I have this one table which has only one field, but I need to search in > the table very quickly. > When creating an index on this field the size of the database is doubled > - which makes sense. > >

[sqlite] Huge Table with only one field -- efficient way to create index ?

Hello all, I am using SQLite on a mobile device. I have this one table which has only one field, but I need to search in the table very quickly. When creating an index on this field the size of the database is doubled - which makes sense. However, is there some way to implement this scenario

Re: [sqlite] Index on joined statements

On 26 Nov 2009, at 11:02am, Jonas Sandman wrote: > If you have a join in an SQL-query, how do you add the statements to > optimize the query in an index? Create good indexes. SQLite uses its own cleverness to pick which of the available indexes is the best one. It is much better at picking

Re: [sqlite] fsync/fdatasync problem on UBIFS

On Nov 26, 2009, at 12:01 AM, Ronny Dierckx wrote: > Dear list, > > I'm using SQLite 3.6.20 on an ARM Linux device which uses the UBIFS > filesystem (on OneNAND flash). > > When I perform a database update, and cut the power a few seconds > later, the > changes are rolled back > when the

[sqlite] Index on joined statements

Hello, If you have a join in an SQL-query, how do you add the statements to optimize the query in an index? For example: select a.* from animals a join animaltype at on at.id=a.typeid where a.name='Monkey' and a.subspecies=2 do I add the index like this: "create index idx_animals on

Re: [sqlite] sqlite3 is blocked by transaction when wanting to close the sqlite3 *

By "other process" do you mean a separate DLL or similar? You can't free memory allocated in a DLL from an application, even when that application has the DLL loaded - Windows will complain. This could be what's happening. Could you instead write the database close call within this other