[sqlite] Help me about sqlite3_open(),please!
Dear all: 1. Will SQLite run sqlite3_open_v2 if I call sqlite3_open with 4 arguments ? I mean, Did SQLite pick up the interface depends on number of parameter ? 2. How to assign flag parameter in sqlite3_open_v2() with combination of 『SQLITE_OPEN_READWRITEhttp://www.sqlite.org/c3ref/c_open_autoproxy.html 』and『SQLITE_OPEN_FULLMUTEXhttp://www.sqlite.org/c3ref/c_open_autoproxy.html 』? is this 『sqlite3_open_v2(db_file_name,pointer,*0x0002+0x0001*,Name of VFS module)』right ? Best Regards tom ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help me about sqlite3_open(),please!
黃楨民 huangchen...@gmail.com wrote: 1. Will SQLite run sqlite3_open_v2 if I call sqlite3_open with 4 arguments ? I mean, Did SQLite pick up the interface depends on number of parameter ? Your compiler won't let you pass four arguments to a function delcared with two, whether this function is part of SQLite API or otherwise. 2. How to assign flag parameter in sqlite3_open_v2() with combination of 『SQLITE_OPEN_READWRITEhttp://www.sqlite.org/c3ref/c_open_autoproxy.html 』and『SQLITE_OPEN_FULLMUTEXhttp://www.sqlite.org/c3ref/c_open_autoproxy.html 』? You combine flags using | (a pipe or vertical bar character), a bitwise-OR operator. is this 『sqlite3_open_v2(db_file_name,pointer,*0x0002+0x0001*,Name of VFS module)』right ? + would work, too. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] synchronization issue: no directory sync after unlink
AFAIK, before deleting journal file SQLite writes some zeroed header into it. This kind of header means that transaction is finished. And even if after power shutdown SQLite finds this journal persisting on disk it will see the header and will understand that this journal just needs to be deleted. So what important here is sync after writing zeroed header, not after deletion. Directory sync is important only when journal is created, so that it doesn't suddenly disappear when power shuts off. You can check in strace whether what I said above is actually true. Pavel On Thu, Feb 23, 2012 at 6:02 AM, Florent Bayendrian yerva...@gmail.com wrote: Hi, I have a synchronization issue on an embedded device running Linux : if a power shutdown is done just after a commit, sqlite will restore the database to the previous state using the journal file. At the end of a transaction the unlink of the journal file is not enough, the journal could physically persist on the file system several seconds after the call to unlink and only a sync on the directory could guarantee that the journal file has been physically deleted. You can strace sqlite3 tool to see that the unlink of the journal file is not followed by any sync. The fix is simple, sqlite3OsDelete should be called with the last parameter (dirSync) set to 1. This fix is necessary to be compliant with the last property of an ACID database. Regards, Florent ___ 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] Crash (c0000005 - Access Violation) in sqlite3.exe
Hi, I accidently stumbled on a repeatable crash in sqlite3.exe, here is what I do to reproduce it: $ sqlite3 temp.db SQLite version 3.7.10 2012-01-16 13:28:40 Enter .help for instructions Enter SQL statements terminated with a ; sqlite select 1 from (select *); CRASH It this known or should I submit a bug report? Thanks, - Levi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crash (c0000005 - Access Violation) in sqlite3.exe
On Feb 23, 2012, at 6:21 PM, Levi Haskell wrote: sqlite select 1 from (select *); Wow, wicked :) Confirmed on sqlite3 -version 3.7.10 2012-01-16 13:28:40 ebd01a8deffb5024a5d7494eef800d2366d97204 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Crash (c0000005 - Access Violation) in sqlite3.exe
On Thu, Feb 23, 2012 at 12:29 PM, Petite Abeille petite.abei...@gmail.comwrote: On Feb 23, 2012, at 6:21 PM, Levi Haskell wrote: sqlite select 1 from (select *); Wow, wicked :) Confirmed on sqlite3 -version 3.7.10 2012-01-16 13:28:40 ebd01a8deffb5024a5d7494eef800d2366d97204 Fixed here: http://www.sqlite.org/src/info/c8c7846fb9 ___ 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
Re: [sqlite] synchronization issue: no directory sync after unlink
The header is overwritten with some zeros only if I change the journal_mode to PERSIST : http://www.sqlite.org/pragma.html#pragma_journal_mode By default, the journal_mode is set to DELETE so the journal file is valid if the file is present after an unlink + power shutdown. (I have checked with strace and with an exit just before the unlink) BR, Florent On Feb 23, 5:34 pm, Pavel Ivanov paiva...@gmail.com wrote: AFAIK, before deleting journal file SQLite writes some zeroed header into it. This kind of header means that transaction is finished. And even if after power shutdown SQLite finds this journal persisting on disk it will see the header and will understand that this journal just needs to be deleted. So what important here is sync after writing zeroed header, not after deletion. Directory sync is important only when journal is created, so that it doesn't suddenly disappear when power shuts off. You can check in strace whether what I said above is actually true. Pavel On Thu, Feb 23, 2012 at 6:02 AM,FlorentBayendrian yerva...@gmail.com wrote: Hi, I have a synchronization issue on an embedded device running Linux : if a power shutdown is done just after a commit, sqlite will restore the database to the previous state using the journal file. At the end of a transaction the unlink of the journal file is not enough, the journal could physically persist on the file system several seconds after the call to unlink and only a sync on the directory could guarantee that the journal file has been physically deleted. You can strace sqlite3 tool to see that the unlink of the journal file is not followed by any sync. The fix is simple, sqlite3OsDelete should be called with the last parameter (dirSync) set to 1. This fix is necessary to be compliant with the last property of an ACID database. Regards, Florent ___ sqlite-users mailing list sqlite-us...@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-us...@sqlite.orghttp://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] SQLiteDataAdaptor Missing
Yes. It's running on 64bit but I compiled it for 32 bit. I've tried using the 64bit version however, when I reference the 64bit dll's the compiler generates warnings about the image even when I set it to debug 64bit, and when I try to run the 64bit version it crashes so I've opted to compile it as 32bit where I reinstalled the 32bit version of Sqlite using setup. The development environment happens to be 64bit however, the application needs to run on both CPU architectures. I'm able to drag and drop an SQLiteConnection from the toolbox within VS designer 2005. In 2008 all works well and when I design using 2008 and manually copy the 2008 generated files back to the 2005 project the code works fine, therefore the images seem fine. I have see some instances previously where a package load failure messages popped up when starting VS however, I believe this happened when I was compiling under Any CPU or 64Bit. Patrick On Thu, Feb 23, 2012 at 1:06 AM, Joe Mistachkin sql...@mistachkin.comwrote: Something is not quite right with that output. You are running on 64-bit Windows? Are you able to add a new SQLite data connection from inside Visual Studio? Do you see any messages about a package load failure? For some reason, the installer tool believes it is running on 32-bit Windows. -- Joe Mistachkin ___ 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] synchronization issue: no directory sync after unlink
On 02/23/2012 06:02 PM, Florent Bayendrian wrote: Hi, I have a synchronization issue on an embedded device running Linux : if a power shutdown is done just after a commit, sqlite will restore the database to the previous state using the journal file. At the end of a transaction the unlink of the journal file is not enough, the journal could physically persist on the file system several seconds after the call to unlink and only a sync on the directory could guarantee that the journal file has been physically deleted. You can strace sqlite3 tool to see that the unlink of the journal file is not followed by any sync. The fix is simple, sqlite3OsDelete should be called with the last parameter (dirSync) set to 1. This fix is necessary to be compliant with the last property of an ACID database. You're correct. The way things are now, if you commit a transaction and then the power fails very quickly afterwards, following a reboot you may find that your transaction has been rolled back. And the way to fix it would be to sync the directory following the unlink() as part of the commit. The downside is, of course, that that extra sync will slow down all transactions committed using PRAGMA journal_mode=DELETE. With smallish transactions, it would be reasonable to assume that the overhead might be somewhere around 20-30%. Since there is no risk of database corruption, only transaction rollback following an unlucky power failure, we figure that the extra durability is not worth the performance cost. Easiest workaround would be to use either journal_mode=PERSIST or journal_mode=WAL. Or to create a VFS shim that makes sure the syncDir flag is set on all calls to xDelete. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Limit on the Compound Select Statements
On Thu, Feb 23, 2012 at 6:50 PM, Simon Slavin slav...@bigfraud.org wrote: On 23 Feb 2012, at 1:16pm, Abhinav Upadhyay er.abhinav.upadh...@gmail.com wrote: I do not remember the exact error message but it close to this. As per the documentation on the compound select statements (http://www.sqlite.org/lang_select.html) on Sqlite website, there is no mention of an explicit limit. I would like to know the exact limit on this, so that I could my code to work within this limit http://www.sqlite.org/limits.html especially item 3, but also others. However, I question the advantage of doing one long INSERT rather than doing many inside a transaction. Are you binding parameters ? It was already inside a bigger transaction, I was trying out something naive and turns out it is not worth it. Thanks for the pointer :) -- Abhinav ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Can we access table column using field and index instead of name?
Hi, would it be possible to reference a column in a table using other than its name? For example, if I have : create table X ( a TEXT NOT NULL); would it be possible to access x.a with something similar as x.field[0]? Thank's. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can we access table column using field and index instead of name?
On 24 Feb 2012, at 1:45am, Rick Guizawa guizaw...@gmail.com wrote: Hi, would it be possible to reference a column in a table using other than its name? For example, if I have : create table X ( a TEXT NOT NULL); would it be possible to access x.a with something similar as x.field[0]? Thank's. No. Sorry, but there are no standards for that. However you can do a 'SELECT * FROM ...' and iterate through the columns until you find one with the correct name. Or you can use SQLite's PRAGMA function to list the columns in a table: http://www.sqlite.org/draft/pragma.html#pragma_table_info Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] xFilter or xBestIndex needs to know which columns were selected
Hello Is it possible to find out in xFilter or xBestIndex which columns were selected? That is, if I do SELECT a, b, c FROM t where t is a virtual table, I would like to know in xFilter or xBestIndex that the result will only consist of the rows a, b and c. I know that xColumn will only get called for these columns. The reason why I would like to know before that, is that xFilter will do a SELECT * on another table and it needs read a bunch of its rows before the first xColumn can be called (I'm doing interpolation and need to look ahead). But in most cases its a huge waste of resources to read every column ahead of xColumn. I only need to read those columns that xColumn will be called on. I could potentionally save GB's of memory if I know what wont be needed in xFilter. -- Steinar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Column names including spaces
On Wed, Feb 22, 2012 at 11:14 PM, Petite Abeille petite.abei...@gmail.comwrote: http://www.sqlite.org/lang_keywords.html @sqlite3 committers: there's a minor error on that page: is says There are three ways of quoting... and then goes on to show four ways. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with LIKE and greek characters
I'm trying to add ICU to my Sqlite static library, compilation is ok, and I have file of library, but when I try to use it I've got some errors, anybody can help me what I do wrong ? ..\..\Library\SQLCIPHER\libSQLCIPHER.a(SqlCipher.o):SqlCipher.c|| undefined reference to `ucol_strcoll_3_8'| ..\..\Library\SQLCIPHER\libSQLCIPHER.a(SqlCipher.o):SqlCipher.c|| undefined reference to `_imp__utf8_countTrailBytes_3_8'| ..\..\Library\SQLCIPHER\libSQLCIPHER.a(SqlCipher.o):SqlCipher.c|| undefined reference to `_imp__utf8_countTrailBytes_3_8'| ..\..\Library\SQLCIPHER\libSQLCIPHER.a(SqlCipher.o):SqlCipher.c|| undefined reference to `u_foldCase_3_8'| ..\..\Library\SQLCIPHER\libSQLCIPHER.a(SqlCipher.o):SqlCipher.c|| undefined reference to `u_foldCase_3_8'| ..\..\Library\SQLCIPHER\libSQLCIPHER.a(SqlCipher.o):SqlCipher.c|| undefined reference to `_imp__utf8_countTrailBytes_3_8'| ..\..\Library\SQLCIPHER\libSQLCIPHER.a(SqlCipher.o):SqlCipher.c|| undefined reference to `_imp__utf8_countTrailBytes_3_8'| ..\..\Library\SQLCIPHER\libSQLCIPHER.a(SqlCipher.o):SqlCipher.c|| undefined reference to `_imp__utf8_countTrailBytes_3_8'| ..\..\Library\SQLCIPHER\libSQLCIPHER.a(SqlCipher.o):SqlCipher.c|| undefined reference to `u_errorName_3_8'| ..\..\Library\SQLCIPHER\libSQLCIPHER.a(SqlCipher.o):SqlCipher.c|| undefined reference to `u_strToUpper_3_8'| ..\..\Library\SQLCIPHER\libSQLCIPHER.a(SqlCipher.o):SqlCipher.c|| undefined reference to `u_strToLower_3_8'| ..\..\Library\SQLCIPHER\libSQLCIPHER.a(SqlCipher.o):SqlCipher.c|| undefined reference to `uregex_setText_3_8'| ..\..\Library\SQLCIPHER\libSQLCIPHER.a(SqlCipher.o):SqlCipher.c|| undefined reference to `uregex_matches_3_8'| ..\..\Library\SQLCIPHER\libSQLCIPHER.a(SqlCipher.o):SqlCipher.c|| undefined reference to `uregex_setText_3_8'| ..\..\Library\SQLCIPHER\libSQLCIPHER.a(SqlCipher.o):SqlCipher.c|| undefined reference to `uregex_open_3_8'| ..\..\Library\SQLCIPHER\libSQLCIPHER.a(SqlCipher.o):SqlCipher.c|| undefined reference to `ucol_open_3_8'| ..\..\Library\SQLCIPHER\libSQLCIPHER.a(SqlCipher.o):SqlCipher.c|| undefined reference to `ucol_close_3_8'| ..\..\Library\SQLCIPHER\libSQLCIPHER.a(SqlCipher.o):SqlCipher.c|| undefined reference to `utf8_nextCharSafeBody_3_8'| ..\..\Library\SQLCIPHER\libSQLCIPHER.a(SqlCipher.o):SqlCipher.c|| undefined reference to `ucol_close_3_8'| ..\..\Library\SQLCIPHER\libSQLCIPHER.a(SqlCipher.o):SqlCipher.c|| undefined reference to `uregex_close_3_8'| ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite inside kernel
*Hi, * I want to store some access policy inside kernel. Is it possible to use sqlite inside kernel code? Thanks Jinesh ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] synchronization issue: no directory sync after unlink
Hi, I have a synchronization issue on an embedded device running Linux : if a power shutdown is done just after a commit, sqlite will restore the database to the previous state using the journal file. At the end of a transaction the unlink of the journal file is not enough, the journal could physically persist on the file system several seconds after the call to unlink and only a sync on the directory could guarantee that the journal file has been physically deleted. You can strace sqlite3 tool to see that the unlink of the journal file is not followed by any sync. The fix is simple, sqlite3OsDelete should be called with the last parameter (dirSync) set to 1. This fix is necessary to be compliant with the last property of an ACID database. Regards, Florent ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] best way to use sqlite as application's format
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 22 Feb 2012 17:10:39 -0800 Roger Binns rogerb-jfdgoz7s+bwwqnjq7v0...@public.gmane.org wrote: Just start using SQLite and see what happens. Good one. We'll try to (artificially) populate our sqlite database and then play with it. You don't have to use an ORM. And if you don't use one, you'll eventually see if you need one and what functionality it needs. After reading The Vietnam of Computer Science it definitely looks to me that using OOP costs too much for not much gain and we'll try to produce more FP-oriented code. I do have two other recommendations. The first is to ensure you have a sensible way of testing. A layer between your code and the database may make that easier or harder - validate up front that it will be the former. I believe one layer less is better in our case. :-) The second is to ensure you have an undo mechanism, as this will affect your schema and triggers. One thing I worked on didn't even have a 'save' menu item. Every action you did resulted in a database change. You could easily undo these. You could also see of the objects as they were at any prior point in time, being able to undo or redo any change. That sounds great, although atm I'm not sure how to tackle it, although it seems that Richard is giving some nice advice in regard. Sincerely, Gour - -- What is night for all beings is the time of awakening for the self-controlled; and the time of awakening for all beings is night for the introspective sage. http://atmarama.net | Hlapicina (Croatia) | GPG: 52B5C810 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.12 (GNU/Linux) iQIcBAEBAgAGBQJPRh/jAAoJELhxXPVStcgQKGsP/A0Zqn9bU+AK8mr09YF3jCKf bkAvsiyl2cZt28Lr8oDuuowwdF3w561+RsV8pUAZ9dg4Y0zBtVF/vz5ZeVlh3CRD KpD2T94iR6jyuTgrGV4t/JPH+F+e3BQ3G62T/r7WElbPClZqHUkqsXaS/2jbnB0T UaoYqd2az24vIRCgQXpXwTOtF71zMItcb6NN2kGtyRdS7uVROHHqk4hxbcN2BP6Y ObqHjplq5t2q18APaoQuTWmpVxv3C1XK/FIwBDpsmBRjwPR2wY+z+yPXXo9ekD13 18qc4rbw9rGLTtVl0gV73dapiZ9d7goN/V/gt+oUTcc7G7/dj+p3xfYJvMNewheh YrnUUVShA7d/OoTT4dQin9Cka+RTBnJuSQP8BCARFtgpWPTcDZ/l43sEWTQXDBES UDRq5Mr7r2x28NmZQtR4X7kOPjARbAz73v1Bf35EWsT4Sf1QDWY+HHj0wTY8SNfz tpVd6Gn3jyac2AqZ/N/JDRYsxg3CqLtSIBxQWST/0s6tWtOtx7DYAFM4o72d/GQe cUQOT59g5Ltf/qGJ2WJywB0DZ33w32ZkvIyXJNSZ7Fvy+r068hoC4iR5SQ846N7J f1WK4JGa+u3tCnFCMwp2RHitrYmALevQE5Wc+X0VwFrAUaV247STnMhqU9t6Hte5 ipQVBxtBSz8AXa/d9HIF =W+90 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] best way to use sqlite as application's format
On Wed, 22 Feb 2012 23:04:55 +0100 Petite Abeille petite.abei...@gmail.com wrote: Are you actually trying to solve a concrete problem? Yes. Or creating one out of thin air instead? No. http://blogs.tedneward.com/2006/06/26/The+Vietnam+Of+Computer+Science.aspx Thank you. It seems we are heading towards 'solution' 1) along with 5) by replacing using OOP in D with FP. Sincerely, Gour -- But for one who takes pleasure in the self, whose human life is one of self-realization, and who is satisfied in the self only, fully satiated — for him there is no duty. http://atmarama.net | Hlapicina (Croatia) | GPG: 52B5C810 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] best way to use sqlite as application's format
On Wed, 22 Feb 2012 20:52:54 -0500 Richard Hipp d...@sqlite.org wrote: Here http://www.sqlite.org/cvstrac/wiki?p=UndoRedo is a write-up from 2005 in which I describe a technique I used to implement unlimited-depth undo/redo in a application that used SQLite as its file format. There was no File/Save button. But you could undo as far back as you wanted - even in to prior sessions. Wonderful! Thank you very much for this reference. That application was written in Tcl/Tk, but the idea works the same in D or whatever language you want to use. OK. I hope we can continue asking here (despite using D) since there are no replies in D newsgroup where people are usually occupied replying to gigantic threads. :-) Another approach is to record historical versions of rows in the database somehow. In other words, design your schema as if it were a Version Control System http://en.wikipedia.org/wiki/Revision_control that keeps a permanent record of past images of the data. Bonus points if you can make it operate as a Distributed Version Control Systemhttp://en.wikipedia.org/wiki/Distributed_Version_Control_System. Hmm, I must say it sounds intriguing although I'm not sure whether we really need it since once when we calculate the whole set of data for one 'person', it would not change much, iow. the data in the database are not so much prone to change as much we're interested to study/query relations between the data. Note that Monotone http://www.monotone.ca/ was a pioneer in the DVCS space and their file format is an SQLite database. Note also that SQLite itself is maintained using Fossilhttp://www.fossil-scm.org/index.html/doc/trunk/www/index.wikiwhich is another DVCS that uses an SQLite database for storage. (Yes, the source code for SQLite is stored in an SQLite database. Recursion is a wonderful thing.) Heh...my VCS really started with the Darcs and I used Monotone for some time - too bad it was too slow for Linus so world ended up with Git itself. ;) I did use Fossil for some time (and still looking what's going on) - it's very fine piece of software, but to make our life simpler we ended up using Mercurial along with hg-git when the needs arises. Having more interoperability features in Fossil (e.g. 2-way sync with other DVCS) would make it even more appealing, although it's questionable how much is this in sync with Fossil's objectives. ;) Or, you could do all your work inside a transaction and then implement a File/Save button that does a COMMIT for you. That approach is conceptually simpler from the point of view of the programmer. But it is less intuitive to users. I agree. And if your application crashes, you lose all of your work done since the last File/Save. Considering data won't change much, it might be sufficient. If you do use this approach, please note that SQLite does support nested transactionhttp://www.sqlite.org/lang_savepoint.htmlwhich might be useful to you. Thank you very much. I was also not aware of that one...it's final time to start using those two books about Sqlite which I have. :-/ Sincerely, Gour -- Thus the wise living entity's pure consciousness becomes covered by his eternal enemy in the form of lust, which is never satisfied and which burns like fire. http://atmarama.net | Hlapicina (Croatia) | GPG: 52B5C810 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite shell dot commands
I have a database with a table name containing a quote and a space. Let's say it's called “Ben's table” I have created it with: CREATE TABLE Ben's table ([column_spec]); I tried the following in the SQLite shell: .import 'file_name.txt' Ben's table But that does not work. So I had to dig a bit into shell.c to understand, and I found the following: * inside the do_meta_command function, the arguments to dot-commands are tokenized with the following rules: * The tokenizer skips all whitespace character when finding the start for the next token. * When ' or is found where a token should start, the token will be the portion of text between that delimiter and its next occurrence. There is no possibility to escape the delimiter. The two delimiters are discarded. * If any other character is found where a token should start, the token ends at whitespace. * Backslashes get special processing except for tokens that were delimited by the single quote. * inside the same do_meta_command method, when processing the import command, the name of the table is appended to some queries this way: sqlite3_snprintf(nByte+20, zSql, INSERT INTO %s VALUES(?, zTable); So, my .import command was tokenized that way: Token 1: file_name.txt Token 2: Ben's table --- note: double quotes gone. And the SQL query that was forged was incorrect: INSERT INTO Ben's table VALUES(?... The only way I found in order to have my import succeed was: .import 'file_name.txt' [Ben's table] which I find cumbersome. Are there any plans to improve the tokenizer to enable escaping the delimiter (doubling the single quote or backslash-escaping the double quote), and to escape the table name in the dot-commands? Potential problem: how to use .backup or .restore with a file name that contains both ' and space? Thanks. -- Benoit Mortgat ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Limit on the Compound Select Statements
Hi, I have a single column table, in which I wish to store around several thousands of rows. I was wondering if I could insert them using a single INSERT query and came across this Stackoverflow answer: http://stackoverflow.com/a/1734067/348637 . According to that answer it is possible to insert multiple rows using a single query with an INSERT statement of the following form: INSERT INTO table_name SELECT 'val1' as col_name UNION SELECT 'val2' UNION SELECT 'val3'... This seems to work but in my case I get an error sometimes saying Too many terms in the compound select statement , I do not remember the exact error message but it close to this. As per the documentation on the compound select statements (http://www.sqlite.org/lang_select.html) on Sqlite website, there is no mention of an explicit limit. I would like to know the exact limit on this, so that I could my code to work within this limit :) Thanks Abhinav ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Limit on the Compound Select Statements
On 23 Feb 2012, at 1:16pm, Abhinav Upadhyay er.abhinav.upadh...@gmail.com wrote: I do not remember the exact error message but it close to this. As per the documentation on the compound select statements (http://www.sqlite.org/lang_select.html) on Sqlite website, there is no mention of an explicit limit. I would like to know the exact limit on this, so that I could my code to work within this limit http://www.sqlite.org/limits.html especially item 3, but also others. However, I question the advantage of doing one long INSERT rather than doing many inside a transaction. Are you binding parameters ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Limit on the Compound Select Statements
On Feb 23, 2012, at 2:16 PM, Abhinav Upadhyay wrote: I would like to know the exact limit on this, so that I could my code to work within this limit :) See Maximum Number Of Terms In A Compound SELECT Statement: http://www.sqlite.org/limits.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Limit on the Compound Select Statements
On Feb 23, 2012, at 2:16 PM, Abhinav Upadhyay wrote: . I was wondering if I could insert them using a single INSERT query Ah, also, there is not much benefit in using a compound insert. You could as well simply insert all your values in one transaction and be done. On the other hand, the forthcoming 3.7.11 release seems to support multi-valued insert statements. http://www.sqlite.org/draft/releaselog/3_7_11.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Limit on the Compound Select Statements
On Thu, Feb 23, 2012 at 8:25 AM, Petite Abeille petite.abei...@gmail.comwrote: On Feb 23, 2012, at 2:16 PM, Abhinav Upadhyay wrote: . I was wondering if I could insert them using a single INSERT query Ah, also, there is not much benefit in using a compound insert. You could as well simply insert all your values in one transaction and be done. On the other hand, the forthcoming 3.7.11 release seems to support multi-valued insert statements. http://www.sqlite.org/draft/releaselog/3_7_11.html The new multi-valued insert is merely syntactic suger for the compound insert. There is no performance advantage one way or the other. ___ 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
Re: [sqlite] Problem with LIKE and greek characters
Robert Gdula robert.gd...@gmail.com wrote: I'm trying to add ICU to my Sqlite static library, compilation is ok, and I have file of library, but when I try to use it I've got some errors, anybody can help me what I do wrong ? ..\..\Library\SQLCIPHER\libSQLCIPHER.a(SqlCipher.o):SqlCipher.c|| undefined reference to `ucol_strcoll_3_8'| I imagine you would also need to link to whatever library comes with ICU. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] double_quote(text_field)
Yes, that should work. Thanks!! Dave Watkinson wrote, On 2/22/2012 5:33 PM: Did you mean something like SELECT ''||text_field||'' FROM table; so that your column's data is quoted? ~~~ Dave Watkinson On Wed, Feb 22, 2012 at 6:26 PM, Igor Tandetnikitandet...@mvps.org wrote: On 2/22/2012 6:03 PM, Bill McCormick wrote: What is the easiest way to return a text field with double () quotes? SELECT quote(text_field) FROM table; is close, but I'd rather have a SELECT double_quote(text_field) FROM table; I'm not sure I understand the nature of the problem. What's wrong with simply SELECT text_field FROM myTtable; ? There's nothing special about text fields that happen to contain a double quote character. -- Igor Tandetnik __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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] double_quote(text_field)
The same, escaping double quotes inside the double quotes: SELECT '' || replace(replace(text_field,'\','\\'),'','\') || '' On Thu, Feb 23, 2012 at 15:16, Bill McCormick wpmccorm...@gmail.com wrote: Yes, that should work. Thanks!! Dave Watkinson wrote, On 2/22/2012 5:33 PM: Did you mean something like SELECT ''||text_field||'' FROM table; so that your column's data is quoted? -- Benoit Mortgat ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users