Re: [sqlite] Problem with System.Data.SQLite and join II
Alessandro, stop drinking, or at least dont ask support. may be incredible at your eyes but some times peoples is working,, ciao Il giorno mar, 21/06/2011 alle 00.27 +0200, Alessandro Caliaro ha scritto: > Thanks Simon. I'm happy not to be alone > Bye > > > -Messaggio originale- > From: Simon Slavin > Sent: Tuesday, June 21, 2011 12:25 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Problem with System.Data.SQLite and join II > > > On 20 Jun 2011, at 10:55pm, Alessandro Caliaro wrote: > > > Thanks to all. I've found the problem. I was working on two different > > db... > > ;( > > Heh. We've all made mistakes like that. Glad you caught it. > > Simon. > ___ > 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] Problem with System.Data.SQLite and join II
Thanks Simon. I'm happy not to be alone Bye -Messaggio originale- From: Simon Slavin Sent: Tuesday, June 21, 2011 12:25 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Problem with System.Data.SQLite and join II On 20 Jun 2011, at 10:55pm, Alessandro Caliaro wrote: > Thanks to all. I've found the problem. I was working on two different > db... > ;( Heh. We've all made mistakes like that. Glad you caught it. Simon. ___ 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] Problem with System.Data.SQLite and join II
On 20 Jun 2011, at 10:55pm, Alessandro Caliaro wrote: > Thanks to all. I've found the problem. I was working on two different db... > ;( Heh. We've all made mistakes like that. Glad you caught it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with System.Data.SQLite and join II
Thanks to all. I've found the problem. I was working on two different db... ;( Ale -Messaggio originale- From: Simon Slavin Sent: Monday, June 20, 2011 11:12 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Problem with System.Data.SQLite and join II On 20 Jun 2011, at 10:05pm, Alessandro Caliaro wrote: > Your test works fine. I have returned all lines... > Which could be the problem? Then the data is actually there. Run an integrity check in the command-line shell: http://www.sqlite.org/pragma.html#pragma_integrity_check If this says there is no file corruption, then the fault is in System.Data.Sqlite or in your programming. See if you can check which version of SQLite your System.Data.Sqlite has built in (I don't know how to do this). Simon. ___ 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] Problem with System.Data.SQLite and join II
Integrity is ok. I've downloaded the installation package from http://sqlite.phxsoftware.com/ I think the SQLite version is 3.6.16 Ale -Messaggio originale- From: Simon Slavin Sent: Monday, June 20, 2011 11:12 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Problem with System.Data.SQLite and join II On 20 Jun 2011, at 10:05pm, Alessandro Caliaro wrote: > Your test works fine. I have returned all lines... > Which could be the problem? Then the data is actually there. Run an integrity check in the command-line shell: http://www.sqlite.org/pragma.html#pragma_integrity_check If this says there is no file corruption, then the fault is in System.Data.Sqlite or in your programming. See if you can check which version of SQLite your System.Data.Sqlite has built in (I don't know how to do this). Simon. ___ 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] Problem with System.Data.SQLite and join II
On 20 Jun 2011, at 10:05pm, Alessandro Caliaro wrote: > Your test works fine. I have returned all lines... > Which could be the problem? Then the data is actually there. Run an integrity check in the command-line shell: http://www.sqlite.org/pragma.html#pragma_integrity_check If this says there is no file corruption, then the fault is in System.Data.Sqlite or in your programming. See if you can check which version of SQLite your System.Data.Sqlite has built in (I don't know how to do this). Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Documentation typos and inconsistent logic
1. May be added note "All triggers associated with the view are also deleted." to http://www.sqlite.org/lang_dropview.html 2. This behaviour is incompatible with other DBMS and undocumented: sqlite> create table a(a); sqlite> create view b as select * from a; sqlite> drop table a; sqlite> select * from b; Error: no such table: main.a sqlite> .s CREATE VIEW b as select * from a; sqlite> create view a as select 1; sqlite> create view b as select * from a; sqlite> drop view a; sqlite> select * from b; Error: no such table: main.a sqlite> .s CREATE VIEW b as select * from a; This strange, really. View cannot reference objects in attached databases but view can reference deleted object? -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with System.Data.SQLite and join II
Hi Simon. Your test works fine. I have returned all lines... Which could be the problem? -Messaggio originale- From: Simon Slavin Sent: Monday, June 20, 2011 10:55 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Problem with System.Data.SQLite and join II On 20 Jun 2011, at 9:49pm, Alessandro Caliaro wrote: > SELECT DISTINCT COM_STRADARIO.ID_STRADARIO, COM_STRADARIO.TIPO_VIA, > COM_STRADARIO.NOME_VIA FROMCOM_STRADARIO inner join INT_CONDOMINI on > COM_STRADARIO.ID_STRADARIO = INT_CONDOMINI.ID_STRADARIO ORDER BY NOME_VIA > > It works fine if I run with SQLiteManager for Firefox, but with > System.Data.Sqlite no rows are returned. > > Can you help me? > > I use 1.0.65.0 with 2.0.50727 runtime and SQLite.Interop.065.dll Try the same SELECT with the command-line shell under Windows: http://www.sqlite.org/sqlite.html download it from here: http://www.sqlite.org/download.html Simon. ___ 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] Problem with System.Data.SQLite and join II
On 20 Jun 2011, at 9:49pm, Alessandro Caliaro wrote: > SELECT DISTINCT COM_STRADARIO.ID_STRADARIO, COM_STRADARIO.TIPO_VIA, > COM_STRADARIO.NOME_VIA FROMCOM_STRADARIO inner join INT_CONDOMINI on > COM_STRADARIO.ID_STRADARIO = INT_CONDOMINI.ID_STRADARIO ORDER BY NOME_VIA > > It works fine if I run with SQLiteManager for Firefox, but with > System.Data.Sqlite no rows are returned. > > Can you help me? > > I use 1.0.65.0 with 2.0.50727 runtime and SQLite.Interop.065.dll Try the same SELECT with the command-line shell under Windows: http://www.sqlite.org/sqlite.html download it from here: http://www.sqlite.org/download.html Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem with System.Data.SQLite and join II
Sorry if you have already received this post, but I don’t know if I’ve been able to send it. I'm using this wonderful library with no problem till today. I've this simple query SELECT DISTINCT COM_STRADARIO.ID_STRADARIO, COM_STRADARIO.TIPO_VIA, COM_STRADARIO.NOME_VIA FROMCOM_STRADARIO inner join INT_CONDOMINI on COM_STRADARIO.ID_STRADARIO = INT_CONDOMINI.ID_STRADARIO ORDER BY NOME_VIA It works fine if I run with SQLiteManager for Firefox, but with System.Data.Sqlite no rows are returned. Can you help me? I use 1.0.65.0 with 2.0.50727 runtime and SQLite.Interop.065.dll I use it on CompactFramework 3.5 over Windows Mobile 6.5.3 Thanks Alessandro ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite for 64-bit Windows-based machines?
On Mon, Jun 20, 2011 at 4:11 PM, HFN Account wrote: > Hello SQLite users, > > This is my first post to this user group, so please be gentle with me if I > am out of line. > > I would like to know if there is SQLite for 64-bit Windows-based machines. > On the web site of SQLite.org there is version 3.7.6.3 for Widnows 32-bit. > How about a version for 64-bit machines? > > I am using SQLite (I think I have 2.5 -- how can I check a file to know > which version I have?) with Django-based web application (Django is a web > application development framework that comes with a light web server). When > I install the my application on 32-bit machines, SQLite works just fine. I > install the same application in a 64-bit Windows-basaed machine and I get > these errors. They are generated in Django, but I suspect they have to do > with SQLite. > > I appreciate any help or guideline you can give. > We do not currently supply precompiled binaries for win64, but you can certainly get the source code and compile it yourself. We do test on win64 - we just don't provide precompiled binaries for that platform. > > Regards, > HF Naraghi > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite for 64-bit Windows-based machines?
Hello SQLite users, This is my first post to this user group, so please be gentle with me if I am out of line. I would like to know if there is SQLite for 64-bit Windows-based machines. On the web site of SQLite.org there is version 3.7.6.3 for Widnows 32-bit. How about a version for 64-bit machines? I am using SQLite (I think I have 2.5 -- how can I check a file to know which version I have?) with Django-based web application (Django is a web application development framework that comes with a light web server). When I install the my application on 32-bit machines, SQLite works just fine. I install the same application in a 64-bit Windows-basaed machine and I get these errors. They are generated in Django, but I suspect they have to do with SQLite. I appreciate any help or guideline you can give. Regards, HF Naraghi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite database integrity check fails with disk I/O error (10)
If I had the same problem ... I would: 1. Refresh the surface of the hard drive using spinrite (grc.com), which is OS and file-structure independent; 2. Then, I'd run the Linux equivalent to the Windows chkdsk command to resolve any file-structure issues; In all but the worst hard drive issues, this would in all likelihood take care of any read errors. --- On Mon, 6/20/11, Raja Kondu wrote: From: Raja Kondu Subject: [sqlite] Sqlite database integrity check fails with disk I/O error (10) To: sqlite-users@sqlite.org Date: Monday, June 20, 2011, 11:25 AM Hi I am using the Sqlite database in the linux box . Here I am encountered the disk I/O error when I perform the integrity check on the database. The error occuted during the sqlite prepare statement only. The prepared select statement is : " pragma integrity_check " retValue = sqlite3_prepare(pDb,pragma integrity_check ) ; retvalue = 10 #define SQLITE_IOERR 10 /* Some kind of disk I/O error occurred */ Can some one help me why the disk I/O error occured during the sqlite3_prepare() statement ? Kindly provide me your feed back. -- Thanks, Raja Kondu. ___ 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 database integrity check fails with disk I/O error (10)
> Can some one help me why the disk I/O error occured during the > sqlite3_prepare() statement ? Because while preparing the statement SQLite must read schema from the database. And although in case of integrity check it's not really necessary I think it's still the general rule and is done before it's even known what statement is being prepared just so that resolving of all identifiers could be done during the parsing. Pavel On Mon, Jun 20, 2011 at 11:25 AM, Raja Kondu wrote: > Hi I am using the Sqlite database in the linux box . Here I am encountered > the disk I/O error when I perform the integrity check on the database. > > The error occuted during the sqlite prepare statement only. > > The prepared select statement is : " pragma integrity_check " > > retValue = sqlite3_prepare(pDb,pragma integrity_check ) ; > > retvalue = 10 > > #define SQLITE_IOERR 10 /* Some kind of disk I/O error occurred */ > Can some one help me why the disk I/O error occured during the > sqlite3_prepare() statement ? > > Kindly provide me your feed back. > > > -- > Thanks, > Raja Kondu. > ___ > 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] Sqlite database integrity check fails with disk I/O error (10)
Hi I am using the Sqlite database in the linux box . Here I am encountered the disk I/O error when I perform the integrity check on the database. The error occuted during the sqlite prepare statement only. The prepared select statement is : " pragma integrity_check " retValue = sqlite3_prepare(pDb,pragma integrity_check ) ; retvalue = 10 #define SQLITE_IOERR 10 /* Some kind of disk I/O error occurred */ Can some one help me why the disk I/O error occured during the sqlite3_prepare() statement ? Kindly provide me your feed back. -- Thanks, Raja Kondu. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question, how multiple joins are executed
On 20/06/2011 6:59 AM, Richard Hipp wrote: > On Mon, Jun 20, 2011 at 6:55 AM, Ryan Johnson wrote: >> IIRC sqlite does *not* do any join ordering optimizations and simply >> runs them in whatever order the query specifies. This can have >> unfortunate effects on runtime for some queries. Can anyone verify this? > The SQLite query optimizer started reordering joins beginning with SQLite > version 3.2.3 in August 2005. Oh, good. Searching the docs, I found where I was led astray: http://www.sqlite.org/vdbe.html (granted, the page does say it's many years out of date, but I forgot where I'd read it). Ryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_CANTOPEN on an open database
Thanks Jay, that clears things up for me. On 20/06/2011 14:17, Jay A. Kreibich wrote: > On Mon, Jun 20, 2011 at 02:04:36PM +0100, Ian Hardingham scratched on the > wall: >> Hey guys. >> >> We revisit my situation - I'm accessing the same database with 4 >> processes and they are using busy-waiting to access the db somewhat >> concurrently. >> >> I'm expecting SQLITE_LOCKED and SQLITE_BUSY, but I also get >> SQLITE_CANTOPEN - this is on an *already open* database and a query >> shortly later works fine. >> >> Can anyone explain when this error happens? >It is likely related to the journal file. > >If you're not using WAL, you might try changing the journal mode to >"truncate." > >http://www.sqlite.org/pragma.html#pragma_journal_mode > > -j > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_CANTOPEN on an open database
On Mon, Jun 20, 2011 at 02:04:36PM +0100, Ian Hardingham scratched on the wall: > Hey guys. > > We revisit my situation - I'm accessing the same database with 4 > processes and they are using busy-waiting to access the db somewhat > concurrently. > > I'm expecting SQLITE_LOCKED and SQLITE_BUSY, but I also get > SQLITE_CANTOPEN - this is on an *already open* database and a query > shortly later works fine. > > Can anyone explain when this error happens? It is likely related to the journal file. If you're not using WAL, you might try changing the journal mode to "truncate." http://www.sqlite.org/pragma.html#pragma_journal_mode -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_CANTOPEN on an open database
Hey guys. We revisit my situation - I'm accessing the same database with 4 processes and they are using busy-waiting to access the db somewhat concurrently. I'm expecting SQLITE_LOCKED and SQLITE_BUSY, but I also get SQLITE_CANTOPEN - this is on an *already open* database and a query shortly later works fine. Can anyone explain when this error happens? Thanks, Ian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] problem with join and System.Data.Sqlite
Hi. I'm using this wonderful library with no problem till today. I've this simple query SELECT DISTINCT COM_STRADARIO.ID_STRADARIO, COM_STRADARIO.TIPO_VIA, COM_STRADARIO.NOME_VIA FROMCOM_STRADARIO inner join INT_CONDOMINI on COM_STRADARIO.ID_STRADARIO = INT_CONDOMINI.ID_STRADARIO ORDER BY NOME_VIA It works fine if I run with SQLiteManager for Firefox, but with System.Data. Sqlite no rows are returned. Can you help me? I use 1.0.65.0 with 2.0.50727 runtime and SQLite.Interop.065.dll I use the dll in compactframewor 3.5, windows mobile 6.5.3 Thanks Alessandro ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Auto Reply: sqlite-users Digest, Vol 42, Issue 20
This is an auto-replied message. I am currently unavailable. I will be back at work on Tuesday 28th June. For urgent questions please contact Michael Brey (michael.b...@oracle.com) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question, how multiple joins are executed
On Mon, Jun 20, 2011 at 6:55 AM, Ryan Johnson wrote: > IIRC sqlite does *not* do any join ordering optimizations and simply > runs them in whatever order the query specifies. This can have > unfortunate effects on runtime for some queries. Can anyone verify this? > > The SQLite query optimizer started reordering joins beginning with SQLite version 3.2.3 in August 2005. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question, how multiple joins are executed
On 19/06/2011 8:03 PM, Simon Slavin wrote: > On 20 Jun 2011, at 12:58am, Lucas Cotta wrote: > >> I understand that for a query with a two tables join, SQLite will do a >> nested loop join with these two tables. >> But what about a query joining 5 tables? >> It would be like this?: >> >> for(table1 lines){ >> for(table2 lines){ >> for(table3 lines){ >> > You can use JOIN any number of times in a SELECT query. SQLite will work out > an optimal way to access each table. You can speed things up a great deal by > having useful INDEXes created. IIRC sqlite does *not* do any join ordering optimizations and simply runs them in whatever order the query specifies. This can have unfortunate effects on runtime for some queries. Can anyone verify this? Ryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Garbled data after binding
On 20 Jun 2011, at 9:23am, Jean-Denis Muys wrote: > Select * is not very informative, though we can try to guess. Please select > only the mentioned columns. Check to see they don't have any unexpected 'invisible' characters in, by doing something like SELECT FullName,LENGTH(FullName) FROM MEMBERS where db_id =1127; and see if the lengths are what you expect. Also check to see that the database doesn't have some lower-level corruption using "PRAGMA integrity_check" http://www.sqlite.org/pragma.html#pragma_integrity_check Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Garbled data after binding
On 20 juin 2011, at 08:42, CodeBoy DVM wrote: > NSEnumerator *enumerator = [ward objectEnumerator]; > id element; > > while(element = [enumerator nextObject]) > { You might want to modernize that code a bit (though this is likely not your problem here): for (NSDictionary *element in ward) { } Also try to avoid "id" as a type for variables you work with as "id" will prevent the compiler from doing any static type checking. Better to use the real class name, with perhaps one type cast. > sqlite> select * from members where db_id =1127; > 1127|2202|?? ?D??||0| > sqlite> select * from members where db_id =1177; > 1177|2015|h?? ?f??||0| > Select * is not very informative, though we can try to guess. Please select only the mentioned columns. Also I would inspect your database with a GUI tool (eg MesaSQLite) to double check those columns. > I have installed NSLog statements to try to hunt down the problem (those > NSLogs are in the method shown above), and every time I execute the code, the > NSLog seems to show the correct information, compared to the CSV: I would put a breakpoint in the code just before your bind statement, to check the data you are going to send them is indeed what you expect. > > 2011-06-19 22:50:24.534 iClerk[4292:a0f] Processing Person Moody, Isaiah > Terrell with Birth:1991-09-28 and ID: 2202 > 2011-06-19 22:50:24.534 iClerk[4292:a0f] Trying to bind > > 2011-06-19 22:50:24.591 iClerk[4292:a0f] Processing Person White, Lonnie Jay > with Birth:1973-12-12 and ID: 2015 > 2011-06-19 22:50:24.591 iClerk[4292:a0f] Trying to bind Perhaps set the values to bind in their own variables, and NSLog those variables right there. I would even log them *after* the call to sqlite3_step, just to make sure it's not a weird memory allocation issue. > > This particular update contains an array of 105 NSDictionaries to be added, > and every time, it is only these two that create problems. Everyone else's > data writes perfectly to the table, and I can SELECT them all I want, no > hiccups. > > I should also mention that there were originally about 5 or 6 other persons > creating difficulty, but when I narrowed down the desired data to just Full > Name and Birthdate, it is only these two. (The original routine collects > about a dozen pieces of information for each person, but my list only needs > name and birthdate, so I tried to just make it work by only dealing with > those.) But did you find out *why* those 5 or 6 persons had problems? If not, by discarding those 5 or 6 persons, you also discarded information that may have lead to the reason for the problem. I am sorry I don't have a better suggestion, I'm pretty much a SQLite newbie too. Jean-Denis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users