Re: [sqlite] cross-database time function.
On Dec 28, 2009, at 9:29 AM, Simon Slavin wrote: On 28 Dec 2009, at 5:06pm, Israel Brewster wrote: Or perhaps some other way to extract the time of a timestamp that works in all three databases? Sorry. Each database handles time/date information differently (there's no SQL standard for doing it). You will fail to find one function that returns a useful result in all three SQL engines. I recommend that you use an agnostic way of storing your datestamps in the database, for instance as a TEXT field in the format mmdd(if you need just a date) hhmmss (if you need just a time) mmddThhmmss (the date, then a 'T', then the time) All of these formats are easy to recognise and sort and index correctly. Write library routines in your software which converts from your programming language's date representation to and from this representation. This way you need write just one routine and that routine /will/ work for all three SQL engines. Thanks for the info - I was sort of afraid of that. Yeah, I can implement parsing in my program easily enough, I was just hoping to be able to write a one-line SQL statement that would give me the info I wanted (just the time in that case, although I do want both time and date stored). Oh well. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Israel Brewster Computer Support Technician II Frontier Flying Service Inc. 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7250 x293 --- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] cross-database time function.
I am looking for a cross-database (SQLite, PostgreSQL, MySQL) method of extracting the time portion of a timestamp. I can't just use the "time()" function, because in PostgreSQL it needs to be in quotes (or preceded by pg_catalog.) to work, while in SQLite quoting the function returns an error. It was suggested that I use a cast(timestamp_column as time) function, but while this works in PosgreSQL, in SQLite it returns the year of the timestamp, not the time. Is there a way to make the cast function actually return the time in SQLite? Or perhaps some other way to extract the time of a timestamp that works in all three databases? Thanks. ------- Israel Brewster Computer Support Technician II Frontier Flying Service Inc. 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7250 x293 --- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SOLVED] Force cache flush?
Figured out my problem - it actually had little to do with SQLite itself, but rather with my code, perhaps due to the way Qt interacted with SQLite. Be that as it may, it turns out I still had an active SELECT query on the database, which was keeping my changes from being committed to disk. Once I closed out the SELECT, my INSERTS started showing right up. Thanks for the feedback! On Nov 19, 2009, at 12:34 PM, Israel Brewster wrote: Nov 19, 2009, at 12:13 PM, Jay A. Kreibich wrote: On Thu, Nov 19, 2009 at 11:58:59AM -0900, Israel Brewster scratched on the wall: When issuing an INSERT command on a SQLite database, is that insert cached before being written to the disk? Sometimes. It may be partly cached until the transaction is committed. If it is a stand-alone INSERT, that will be as soon as the statement is fully processed. If so (as I suspect to be the case), is there a way to force the cache to be flushed to disk? Commit any open transactions. I have an application that issues a number of INSERT statements, but these don't appear to be written to the actual database file until the application quits. If you're using the C API, make sure you call sqlite3_reset() and/or sqlite3_finalize() to close out the statement. Thanks for the info. I'm not using transactions, so that shouldn't be the problem. I'm using the Qt SQL drivers, but they just use the C API's in a fairly standard way, to my understanding. I'll look more into that then. Thanks! -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ------- Israel Brewster Computer Support Technician II Frontier Flying Service Inc. 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7250 x293 --- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ------- Israel Brewster Computer Support Technician II Frontier Flying Service Inc. 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7250 x293 --- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Force cache flush?
Nov 19, 2009, at 12:13 PM, Jay A. Kreibich wrote: On Thu, Nov 19, 2009 at 11:58:59AM -0900, Israel Brewster scratched on the wall: When issuing an INSERT command on a SQLite database, is that insert cached before being written to the disk? Sometimes. It may be partly cached until the transaction is committed. If it is a stand-alone INSERT, that will be as soon as the statement is fully processed. If so (as I suspect to be the case), is there a way to force the cache to be flushed to disk? Commit any open transactions. I have an application that issues a number of INSERT statements, but these don't appear to be written to the actual database file until the application quits. If you're using the C API, make sure you call sqlite3_reset() and/or sqlite3_finalize() to close out the statement. Thanks for the info. I'm not using transactions, so that shouldn't be the problem. I'm using the Qt SQL drivers, but they just use the C API's in a fairly standard way, to my understanding. I'll look more into that then. Thanks! -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ------- Israel Brewster Computer Support Technician II Frontier Flying Service Inc. 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7250 x293 --- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Force cache flush?
When issuing an INSERT command on a SQLite database, is that insert cached before being written to the disk? If so (as I suspect to be the case), is there a way to force the cache to be flushed to disk? I have an application that issues a number of INSERT statements, but these don't appear to be written to the actual database file until the application quits. Usually this isn't an issue (and, in fact, is probably a good thing), but it has caused problems a couple of times. This could also be caused by the program itself, of course, but I wanted to check the obvious places first. Thanks! --- Israel Brewster Computer Support Technician II Frontier Flying Service Inc. 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7250 x293 --- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite, network share, and Qt
On Nov 16, 2009, at 3:41 PM, Simon Slavin wrote: On 16 Nov 2009, at 7:32pm, Israel Brewster wrote: Now I know that this sounds like a Qt problem, and in fact I believe that to be the case. However, when I ask them about it, they stubbornly keep insisting that it is a sqlite problem. I think you have already proved it isn't: in my testing it works fine from the command line sqlite3 client Tell them that and see what they say. I did. I'm not convinced they actually read my e-mail though, cause the response was "this is an sqlite problem." Twice, actually, from two different people. :-P. My apologies to Bill King, if you are reading this, but that is what it felt like to me. Alternatively whack up a very simple C program which does just those SQLite3 commands which cause the problem you're seeing and see if the same problem happens. I did a simple Qt test (which showed the same symptoms, no surprise), but I haven't tried a direct C program yet. I'll have to do that, and see what happens. Thanks again. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ------- Israel Brewster Computer Support Technician II Frontier Flying Service Inc. 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7250 x293 --- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite, network share, and Qt
On Nov 17, 2009, at 12:49 AM, Simon Slavin wrote: On 17 Nov 2009, at 7:45am, Dan Kennedy wrote: On Nov 17, 2009, at 2:32 AM, Israel Brewster wrote: Simple (I hope) question here for my first posting to this list: Running SQLite 3.4.0 on Mac OS X 10.5.8, should I or should I not be able to place a SQLite database on a network share and have it work for a single user? I know there could be issues if multiple people tried accessing it at the same time, as well as potential performance issues regardless, but I'm trying to find out definitively if it should work at all - the idea is to have location portability, as opposed to truly sharing the database. Mac OS X 10.5.8 comes with SQLite .h and .dylib files. The library is tested with the SQLite test suites and was built with the switches that make it pass all the tests. Note: /that/ library is tested, supplied with and works for /that/ version of Mac OS X. If the OP is using either of /usr/lib/libsqlite3.dylib /System/Library/Tcl/sqlite3/libtclsqlite3.dylib then all SQLite tests have been passed for all media types supported by Apple, including Flash drives, NFS, AFP and supported SMB configurations (I do not know about RAM disks). Of course, it's possible to set PRAGMAs and do library calls which will make it fail again, for example by defeating locking and/or caching under certain circumstances. One possibility is that the OP is not using the libraries supplied by Apple in which case this sort of thing There is some tricky stuff in os_unix.c contributed by Apple that I think is supposed to help with this. But with older SQLite versions you have to turn it on by defining SQLITE_ENABLE_LOCKING_STYLE when you build the library. Maybe your command line tool was built with this option but the Qt driver was not. is definitely a good thing to be worrying about. But the supplied libraries should have no trouble with network shares mounted using the tools built into OS X. hmm...now that's definitely a good question. When compiling Qt, there is a configure flag for --system-sqlite. The implication here (I would think) is that if you don't use that flag, Qt uses it's own version of SQLite (perhaps it just contains the SQLite source?), which very well may not have this flag defined. I'll have to see if I can get it to compile with the --system-sqlite flag. Last time I tried, compilation failed, but I didn't spend much time looking into it. Thanks for the info! Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- Israel Brewster Computer Support Technician II Frontier Flying Service Inc. 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7250 x293 --- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite, network share, and Qt
Simple (I hope) question here for my first posting to this list: Running SQLite 3.4.0 on Mac OS X 10.5.8, should I or should I not be able to place a SQLite database on a network share and have it work for a single user? I know there could be issues if multiple people tried accessing it at the same time, as well as potential performance issues regardless, but I'm trying to find out definitively if it should work at all - the idea is to have location portability, as opposed to truly sharing the database. Actually, in my testing it works fine from the command line sqlite3 client. The problem I am running into is that whenever I try using sqlite through the Qt (http://www.trolltech.com/) sqlite driver, I get an error indicating that the database is locked if the database file is located on a network share (tried AFP and SMB). Now I know that this sounds like a Qt problem, and in fact I believe that to be the case. However, when I ask them about it, they stubbornly keep insisting that it is a sqlite problem. As such, I wanted to at least throw the question out to you guys to get your feedback. Could this possibly be a SQLite problem? Or are the Trolltech/Qt people just not willing to accept that the problem is with their driver? Thanks. --- Israel Brewster Computer Support Technician II Frontier Flying Service Inc. 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7250 x293 --- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
