Re: [sqlite] SQLITE3_CANTOPEN
As I think, the code you were surprised to see was a fix for this issue:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg35864.html
as Jeremy Spiegel in that post wrote
> when I try calling DeleteFileW on a file with a handle open on
> that file, I see DeleteFileW returning 1 (success) and I see
> GetFileAttributesW returning INVALID_FILE_ATTRIBUTES, I think because
> even though the file still exists it is in a "delete pending" state.
So your variant was no longer sufficient as I suppose to address this
problem. As I see your code relies on DeleteFile return value thorughly.
I just can add that new problems with DeleteFile can appear recently with
introducing Windows 7. I'm not sure but once I saw a strange failure on a
system with Windows 7 and Microsoft Security Essentials installed (so at
least one candidate for "me-to" accessing any new file) when rollback failed
leaving -journal file existing and getting disk i/o error on the first
sqlite3_prepare after this rollback. But still I'm not sure it's related to
winDelete .
Max
On Thu, Jan 14, 2010 at 9:04 PM, Dave Dyer wrote:
>
> There is a known problem, at least with NTFS, that deleting the
> journal file fails unexpectedly, resulting in sqlite3_cantopen.
> I believe I'm the one who originally reported this problem,
> but the installed solution isn't quite what I recommended.
>
> I'm not sure what OS/File System you are using, but something
> analogous may be going on for you.
>
> In "winDelete", the delete code in the released version is
>
> if( isNT() ){
>do{
> DeleteFileW(zConverted);
>}while( ( ((rc = GetFileAttributesW(zConverted)) !=
> INVALID_FILE_ATTRIBUTES)
> || ((error = GetLastError()) == ERROR_ACCESS_DENIED))
> && (++cnt < MX_DELETION_ATTEMPTS)
> && (Sleep(100), 1) );
>
>
>
> The code I"m using is
>
> if( isNT() ){
>do{
> rc = DeleteFileW(zConverted);
> if(rc==0)
> { long attr = GetFileAttributesW(zConverted);
>if(attr==0x) { rc=1; } // ok as long as
> sombody deleted it
> }
>}while( rc==0
>&& (cnt++ < MX_DELETION_ATTEMPTS)
>&& (Sleep(100), 1) );
>
> ___
> 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] SQL syntax diagrams
Hi, I really like the way the SQL syntax diagrams are done (e.g. at http://www.sqlite.org/syntaxdiagrams.html). What software did you use for it? Regards Andy _ We want to hear all your funny, exciting and crazy Hotmail stories. Tell us now http://clk.atdmt.com/UKM/go/195013117/direct/01/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL syntax diagrams
On Jan 15, 2010, at 9:10 PM, Andy Gibbs wrote: > > Hi, > > I really like the way the SQL syntax diagrams are done (e.g. at > http://www.sqlite.org/syntaxdiagrams.html) > . What software did you use for it? http://wiki.tcl.tk/21708 > > Regards > Andy > > _ > We want to hear all your funny, exciting and crazy Hotmail stories. > Tell us now > http://clk.atdmt.com/UKM/go/195013117/direct/01/ > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL syntax diagrams
On Jan 15, 2010, at 9:10 AM, Andy Gibbs wrote: > > Hi, > > I really like the way the SQL syntax diagrams are done (e.g. at > http://www.sqlite.org/syntaxdiagrams.html) > . What software did you use for it? > http://wiki.tcl.tk/21708 Also: http://www.sqlite.org/faq.html#q25 and http://www.sqlite.org/search?q=syntax+diagrams D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BUG - Documentation
Perhaps "...to whole underlying operating system..." should be "...to the whole underlying operating system..." - Original Message From: Dennis Cote To: General Discussion of SQLite Database Sent: Thu, January 14, 2010 4:38:48 PM Subject: [sqlite] BUG - Documentation On the website page at http://www.sqlite.org/custombuild.html The following sentence appears: "This object is somewhat misnamed since it is really an interface to whole underlying operating system, just the filesystem." I think it should be changed to: "This object is somewhat misnamed since it is really an interface to whole underlying operating system, *NOT* just the filesystem." Or something similar. Maybe the NOT doesn't need that much emphasis, but it should be there. HTH Dennis Cote ___ 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] How to find Rank in SQLite3?
Jigar, If these two employees have the same rank: dept, employee, salary 10, Joe, 75000 10, Mary, 75000 then you do not need to include employee columns in the inline view. The salary gets the rank, not the employee, and the employee record is joined to the ranked salary on salary. Tim From: Jigar Shah To: sqlite-users@sqlite.org Sent: Thu, January 14, 2010 7:07:38 AM Subject: Re: [sqlite] How to find Rank in SQLite3? First of all thanks Tim and Pavel for replying to my query. > > Assuming you're doing this by department, try to get just a list of the > distinct salary rankings into an inline view, using your count(*) +1 > approach to set the salary rank, where these three columns are unique > in combination: > > (dept, salary, rank ) as S > > select EE.dept, EE.name, EE.salary, S.rank > from employees as EE > JOIN > (inline view to create distinct salary bands by department goes here) as S > on EE.dept = S.dept > and EE.salary = S.salary > order by dept, rank My aim is to find rank of each employee within his dept. So I guess the inline view (adding empname and empno columns) is the final output that I require. So to join this output with original table would be an extra activity. Also, count(*) +1 approach for ranking is time consuming as it iterates over entire table for each row, so using the same in inline view may not help to increase speed. >> I have used following query but it takes more than one hour even after >> indexing, > > Shouldn't be - query is not so hard if a proper index used. What index > did you create? I have used following index. create index tempidx on EMPLOYEES(DEPTNO, SAL); I have tried query, using count(*) +1 approach, in mysql but it was slower (>two hours, still query was running) than Sqlite. Is there is any other possible way to find out rank? Thanks, Jigar Shah Disclaimer note on content of this message including enclosure(s)and attachments(s): The contents of this e-mail are the privileged and confidential material of National Stock Exchange of India Limited (NSE). The information is solely intended for the individual/entity it is addressed to. If you are not the intended recipient of this message, please be aware that you are not authorized in any which way whatsoever to read, forward, print, retain, copy or disseminate this message or any part of it. If you have received this e-mail in error, we would request you to please notify the sender immediately by return e-mail and delete it from your computer. This e-mail message including attachment(s), if any, is believed to be free of any virus and NSE is not responsible for any loss or damage arising in any way from its use. ___ 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] How to find Rank in SQLite3?
Not saying this will be faster for sure, but it's worth a try. Try a
composite index on (dept, salary). The goal is to rank distinct salaries
within department, and have those salary rankings be used as the inner
loop when the EEs table is joined to the ranking on the salary column.
The assumption is that there are far fewer distinct salaries than
distinct employees.
Tim Romano
CREATE TABLE "EES" ("id" int PRIMARY KEY NOT NULL , "dept" int NOT NULL
, "salary" int NOT NULL , "empname" text NOT NULL )
select EES.dept, EES.empname, S3.rank from EES
JOIN
(
select s1.dept, s1.salary,
(select count(*) + 1 from
(select distinct dept, salary from EES) as S2 where S1.dept=s2.dept and
s1.salary < s2.salary) as rank
from
(select distinct dept, salary from EES) as S1
) as S3
ON S3.dept = EES.dept and S3.salary = EES.salary
order by EES.dept, rank
Explain Query Plan:
Order, from, detail
0,0, TABLE EES WITH INDEX EES_IX ORDER BY
0,1, TABLE AS S1
1,0, TABLE EES WITH INDEX EES_IX
0,0, TABLE EES WITH INDEX EES_IX ORDER BY
0,0,TABLE AS S2
0,0, TABLE EES WITH INDEX EES_IX ORDER BY
0,0, TABLE AS S2
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reasons for SQLITE_CANTOPEN
"D. Richard Hipp" writes:
> On Jan 12, 2010, at 6:58 AM, Ian Jackson wrote:
>
>> Nikolaus Rath writes ("Re: [sqlite] Reasons for SQLITE_CANTOPEN"):
>>> Edzard Pasma writes:
Hope strace (see Roger Binns' post) will help you further. Also lsof
may help.
>>>
>>> The problem was indeed that I reached the maximum number of fds.
>>> Thanks
>>> for the help!
>>
>> If SQLite had reported the errno value from the failed system call,
>> the cause would have been obvious and this entire investigation would
>> have been unnecessary.
>
>
> sqlite3_file_control(db, "main", SQLITE_LAST_ERRNO, &errno_value);
This does not seem to work. With SQLite 3.6.22, errno seems to be always
zero, especially also if there are no file descriptors left or the database
file is not readable.
See also: http://code.google.com/p/apsw/issues/detail?id=92
Best,
-Nikolaus
--
»Time flies like an arrow, fruit flies like a Banana.«
PGP fingerprint: 5B93 61F8 4EA2 E279 ABF6 02CF A9AD B7F8 AE4E 425C
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
