Re: [sqlite] errors compiling with MS VC++ 6.0
When I then try to build the project, I get internal compiler errors. Here are the first few lines of 35 errors it has: I have built v3.08 and v2.x using VC++ 6 and 7.x You do get a lot of casting warnings (which you can turn off/ignore), but I never got a compiler error. I would suspect it is something to do with your setup.. In fact I have only once seen an INTERNAL COMPILER ERROR when using VC++, and it was my fault. Things to check: Make sure the compiler can find the file. Try compiling the .c file alone (without other .c files) The file could be to large for the compiler.. There are settings that will increase the buffer size.. (I had this problem when I put in a char array that was just MASSIVE).. I ended up removing the data from the executable to fix it.. If the file still gives errors when you compile it alone, try commenting out some things and see if it gives a different (more normal) error.. Randall Fox
Re: [sqlite] transient SQLITE_CORRUPT
Kevin Schmeichel wrote: As I reported earlier, after updating to sqlite 3.2.2, I started getting occasional SQLITE_CORRUPT errors. The next query always worked, so the error was only a temporary condition. I decided that if I got an error on a query, I would retry the query after closing and reopening the db. I tried this, but I saw the SQLITE_CORRUPT error repeat itself. Next thought was that maybe sqlite just needed a bit of time to "uncorrupt" itself, so I put in a sleep of 300 ms before retrying the failed query. Seems to work... Did you try: *PRAGMA integrity_check; Perhaps that will tell you if you have integrity problems on the db.. Randall Fox *
[sqlite] PRAGMA user_version
I am trying to use "PRAGMA user_version;", but it doesn't seem to work with my version of Sqlite, 3.08. What version is this feature in? I also checked the pragma.c file, from the website CVS, and I didn't see any notes about it being added since 3.08.. Thanks Randall Fox
[sqlite] Cluster Indexes
What type of select query would benefit from cluster indexes in SQLITE3 ? If you can, an example would be nice.. I found the following in the wiki, but it doesn't explain much.. Use cluster indexes Clustered indexes are indexes that comprise more than one column. These increase the speed of queries where you have multiple joins of one table or you are selecting rows based on multiple columns. (This is true in other DB systems, I don't know if it holds true in SQLite.)
Re: [sqlite] Do _ErrMsg strings have to be freed?
On Mon, 7 Feb 2005 18:14:42 -0800, you wrote: >Does this > >var > pMsg: PChar; > >.. >pMsg := SQLite3_ErrMsg(aDB); > >necessitate this? > > if pMsg <> nil then SQLite3_Free(pMsg); It depends. If you use sqlite3_exec, then yes, if you use the other method (sqlite_prepare) then I would say no, depending on your definition of the word "ephemeral" ;-) From the docs: sqlite3_exec method: If an error occurs while parsing or evaluating the SQL (but not while executing the callback) then an appropriate error message is written into memory obtained from malloc() and *errmsg is made to point to that message. The calling function is responsible for freeing the memory that holds the error message. Use sqlite3_free() for this. If errmsg==NULL, then no error message is ever written. sqlite3_prepare method The sqlite3_errcode() routine returns a result code for the most recent major API call. sqlite3_errmsg() returns an English-language text error message for the most recent error. The error message is represented in UTF-8 and will be ephemeral - it could disappear on the next call to any SQLite API function. sqlite3_errmsg16() works like sqlite3_errmsg() except that it returns the error message represented as UTF-16 in host native byte order.
Re: [sqlite] still struggling with "Database schema has changed" errors
>Could you post it here? I bet I am not the only other one curious about it. Here you go. I sent the same to Dr. Hipp, but I didn't hear anything back. It would be nice to add this option to SQLITE since it is just an additional TCL command switch. If you want to see the difference, use a program like Examdiff and compare it to the same file in the source code. It will show the differences. I used 3.08 as the source, but I would bet the file is the same in the current version.. Randall Fox
Re: [sqlite] SQLite 3.0.8 dates
On Fri, 4 Feb 2005 12:09:59 -0800 (PST), you wrote: >I've been agonizing over the best way to store date/time >values in my databases (perl will be the app platform). > >So, my question is, true or false:, if I want to use >SQLite's date/time functions against field values, my only >real option is to store -MM-DD HH:MM:SS time strings. >Any other value, whether MMDD, julian day number, >MM/DD/, etc. (with or without the time portion), would >leave me with having to do all format conversions in my app >code. Store as Julian, and then when you select out of SQLITE, have it do the conversion for you. You can also convert when inserting or updating if you want. I prefer the julian because it smaller, and I have no worries about sorting by that column. Anything fancy and I use SQLITE to do the format conversions, which is rare. Randall Fox
Re: [sqlite] still struggling with "Database schema has changed" errors
>Do mind sharing or explaining your changes? No.. But I should have gave more information. My changes were for the TCL SQLITE code, and just affected the way TCL accesses the library. I was also getting the schema errors when I was using a single threaded app. I was opening two instances but only one was in a write operation (or read for that matter ) at any given time. What you should do is check the operation specifically for this error and when you encounter it, try to re-execute the command repeatedly until it goes through. I found when I did get the error, retrying the same operation was successful -- and that is the same advice I saw earlier from the author of SQLITE. Personally, I would probably put a fail safe in the code to prevent an infinite loop. In fixing my problem, I did not use that method. Instead I passed in the pointer for the SQLITE DB I already had open to the TCL interpreter, and had TCL use the already opened DB pointer instead of opening another instance. This seems to take care of the problem, but is a poor choice on a multi-threaded app. If you are using TCL, let me know I can send you the code, which is short and just adds another command line switch for the TCL sqlite3 instruction. Randall Fox
Re: [sqlite] still struggling with "Database schema has changed" errors
On Thu, 03 Feb 2005 14:15:52 -0500, you wrote: >Hello, > >I am using SQLite 3.0.8 in a Win32 threaded >environment. > >I keep getting random "Database schema has changed" >errors even though I am using thread local >storage to make sure sqlite3_open() gets called >on each thread and a there is a sqlite3* per thread. > >Has anyone had any luck with resolving SQLITE_SCHEMA >errors in a threaded environment? How are you accessing the database? Do you open it multiple times and write to it? I had the same problem with the schema errors. I was opening the database twice, and when I would create a table with one of the open instances, the other would get the schema error the next time I started a write operation. I did end up fixing it by rewriting part of the SQLITE code. Randall Fox
Re: [sqlite] quick question regarding INTEGER PRIMARY KEY default value
On Wed, 2 Feb 2005 09:22:09 -0800 (PST), you wrote: > >You really should not rely on implicit assumptions in your code, >such as 'the first value of an autoincrement field is 1'. If you >use a different database, or different version of this one, that >may be wrong and a nightmare to fix. True, but if you use the integer primary key (IPK) as a foreign key in another table, you don't want a zero value as the IPK. This is because when you get the value of the foreign key in the second table, if the foreign key has NULL in it, sqlite will return 0 from the function sqlite3_column_int to represent NULL. The only safe way is to either not allow zero as a key entry, or get the fkey as a text value. Randall Fox
Re: [sqlite] embedded TCL and sqlite in one app
FYI: for those interested.. >> Re: Schema change error when using sqlite >> with TCL as a macro language inside an >> application that already has an open db I took a look at the code (tclsqlite.c) and saw I could easily change it to support passing in a pointer to an existing database connection (db already open). I didn't see anything on your website on how to submit changes. So, here is the file I changed. Diff it against 3.0.8. The changes were minimal, and in preliminary testing seems to work just fine. The TCL command line is still the same with one minor difference: sqlite3 db1 0x -use-existing-connection if the -use-existing-connection is present as above, it will assume 0x is a pointer to an already opened sqlite3 db. Otherwise the old behavior of opening the supplied filename is used. No testing is done on the open db to see if it is healthy. I assume the user is smart enough to know. If you feel it is worthy, use the changes in your next release. I would prefer if you did, since I won't have to maintain the code myself.. :) Randall Fox
[sqlite] Is it possible to BIND in a CREATE TABLE
I have a CREATE TABLE sql command with DEFAULT values. I would like to BIND the default values, but it seems it is not supported. Is this so? I am using SQLITE3.. Any help appreciated. Randall Fox
Re: [sqlite] DATABASE SCHEMA HAS CHANGED
On Sat, 20 Nov 2004 08:08:48 -0500, you wrote: >So to answer your questions: > > No, this is not a serious bug. You just need to be prepared to > reissue any SQL statement that returns SQLITE_SCHEMA. > > Yes, this issue is fixed in version 3.0. Thank you.. It seems the original poster didn't say what version, and a follow up mentioned the sqlite3 structure, so I assumed it was v3. Good to know it is not a problem. Randall Fox
Re: [sqlite] DATABASE SCHEMA HAS CHANGED
>I believe VACUUM changes the schema version numbering. After you >VACUUM, your sqlite3 struct holds information about your previous >database version. Solution: reopen the database and SQLite will pick up >the new changes. After that, INSERT will not report an error (until you >VACUUM again, that is.) This sounds like a major bug. I use Sqlite embedded in my app and an error like that could be catastrophic. Should I close and reopen the DB every time after a vacuum? Is there a fix? I was not able to recreate the error but I just upgraded to v3 today so I am still testing.. Randall Fox
Re: [sqlite] What does the first expr do in a CASE statement ?
On Fri, 17 Sep 2004 19:49:34 -0600, you wrote: >Randall Fox wrote: > >>What does the first expr do in a CASE expression ? >> >>I understand the rest of the syntax, but I don't get what the very >>first expr (after the keyword "case") does.. And I couldn't find the >>answer in the documentation or wiki.. >> >>Thanks >> >>Randall Fox >> >> >There's a few ways you can use case statements, I think you're referring >to this: > >select >case field1 > when 1 then > field1+1 > when 2 then > field1/2 > else > 1 >end as casefield >from table1; > >in which case, field1 will be compared to each "when value" to determine >which "then value" to return. It's very similar to a switch statement. > >Case can also be used as an if elseif else type statement, when the >field is omitted. > > >select >case > when field1 is null then > field2 > else > field1 >end as casefield >from table1; > > >John LeSueur Thanks.. So it is a true SWITCH.. I didn't realize you can have multiple when statements!! wow! Thanks Randall Fox
[sqlite] What does the first expr do in a CASE statement ?
What does the first expr do in a CASE expression ? I understand the rest of the syntax, but I don't get what the very first expr (after the keyword "case") does.. And I couldn't find the answer in the documentation or wiki.. Thanks Randall Fox
[sqlite] Any speed advantage in a view
I am coding a program that uses SQLITE and I am wondering if there are any advantages (speed?) to using a preset view instead of hard coding the SELECT query into the source code? Is the view precompiled at all? Thanks Randall Fox
Re: [sqlite] Alternating Output
On Fri, 23 Jul 2004 18:50:49 +0200, you wrote: > >> How do you renumber an index anyway? > >Hmm ... given the tables > work (idx, sortcrit) > storeroom(rowcount integer) > >where 'idx' may be the index to renumber and 'sortcrit' a sorting criteria. > >I would try to do something like the code below, but I just remembered that >sqlite doesn't support recursive triggers, therefore this won't work :( So much for theory, I guess the only way is to write a custom function.. On a side note, is there a repository of user written functions somewhere that I can browse? Thanks Randall Fox
Re: [sqlite] Alternating Output
The trigger is a nice idea, but renumbering the index won't work, since the index might be used as a key to link the record to another table.. I guess I could use a new index, just for this purpose but that seems to be a waste of resources.. How do you renumber an index anyway? Randall Fox On Thu, 22 Jul 2004 11:19:52 +0200, you wrote: > >> I am just wondering if it was done or there is an easy way.. > >As suggested by Kurt, given a subsequent index from idx = 1 ... N, you may > use color = idx%2 to compute your color. > >To work around the problem of insert/delete, I'd try to employ a trigger on >these events to renumber the index (idx) first and then recompute the colors. > >Cheers > Daniel
Re: [sqlite] Alternating Output
Ya.. I thought about all that, but the web pages will be built based on queries, so the support needs to come from the SQL statement. Using the key value is just not practical. What I am thinking about is adding a custom function (I think sqlite supports that) and just using a boolean static variable that toggles between two supplied strings each time. I am just wondering if it was done or there is an easy way.. Thanks Randall Fox On Wed, 21 Jul 2004 08:55:09 -0500, you wrote: >> Is there an easy way to have an "alternating" column value ...? > >No easy or reliable way that I can see. > >Given a table with an integer primary key (ipk), you could let >(ipk % 2 == 0) => white and (ipk % 2 != 0) => gray, but that >would work only if (1) you never delete a row or (2) you delete >only adjacent pairs of rows, meaning that if you delete a >particular ipk, you must also delete the next greater or next >smaller ipk. > >Also, if you insert a row, you'd probably want to supply the >new ipk yourself [max(ipk)+1]; otherwise you'd be depending on >SQLite to suppy sequential keys automatically, and I vaguely >remember some postings to this list claiming that SQLite >sometimes skips a number. I don't know if that issue was ever >resolved. > >This also assumes that sorting by ipk gives the order that you >want. > >The basic problem is that you want to do something based on >row order, and (theoretically) row order doesn't exist until >you've retrieved your data. All in all, it would probably be >better to add the colors after you retrieve the data into an >array or list in your host language. > >Regards
[sqlite] Alternating Output
Is there an easy way to have an "alternating" column value in a query, using sqlite ? I want to have a column that alternates its value on every row. The application is web based. I want to alternate colors for each row in a table.. Something row1 = gray background, row2 = white, row3=grey, row4 = white, etc... Thanks Randall Fox
Re: [sqlite] Another way of corrupting the database.
On Tue, 20 Jul 2004 07:21:43 -0500, you wrote: >>> Is there any way to determine the sqlite version ... ? > >Look at the first 47 bytes of the file -- or the first 48 >bytes if you want a null-terminated string. That works.. Thank you.. Randall Fox
Re: [sqlite] Another way of corrupting the database.
Is there any way to determine the sqlite version either before opening the database, or immediately after? For example, if I update my application to v3.x, I would like to detect when a user tries to open a v2.x database, and then perhaps convert it to the new v3 compatible format. Off the top of my head I can't think of a way to do it easily. I guess in the end I would need to include both versions of the sqlite db engine inside the application, in order to do a conversion from 2.x to 3.x.. Randall Fox On Mon, 19 Jul 2004 09:08:47 -0400, you wrote: >George Ionescu wrote: > > > > I think I have found another way to corrupt an sqlite database: create it > > with v3.0.2, add some data, open it with v2.8.14 and then try to open it back > > with v3.0.2. > > > >I tried this. 2.8.14 says "Unable to open database "test.db": file >is encrypted or is not a database". Then I try 3.0.2 again and it >works fine. > >Please tell me again exactly what you are doing?
Re: [sqlite] SQLite performance with mid-size databases
On Tue, 15 Jun 2004 23:04:04 -0500, you wrote: >Hi. We are using SQLite to store and retrieve data rows where each >row is roughly 2K total in size and in a table of 15 columns. The total >size of the database ranges from 100-300 MB. > >The problem we are seeing is that query and insert performance is >unusually bad and scales up linearly with database size. Compared to MS >Access, the query times are several times slower. Frankly I was a bit >shocked at this considering that most people seem to think the >performance is good. However, I don't see anything that we are doing >wrong...we query the rows we want only by rowid. I'm very puzzled that >this hasn't come up a lot in my searches of the mailing list, but >perhaps the slower query times aren't a concern for many of the >applications using SQLite. > >Empirically speaking, we display our data in a scrolling 2 >dimensional grid format. With MS access, this grid responds >instantaneously when moving through the grid. With SQLite, there is >very noticable stalling and lag and the disk i/o is higher than MS >Access by roughly a factor of 10. > >I suppose I am looking to see if anyone is seeing the same results >that I am seeing, and wondering if this is known and expected to be the >case. The speed results on the website seem way off to me or must be so >skewed towards a small dataset that they do not apply in a real world >scenario. I would also like to state that I am very impressed with the >simplicity of SQLite, which is rare to find these days. It was very >easy to get up and running. I'm just having trouble getting past the >performance issues. Any explanation would be helpful. > >Richard Kuo How do you fill in the grid control? Is it storing the data, or do you provide the data when requested? (owner data..) You may need to implement some caching if it isn't implemented already, I know some controls have this set up in them, and it could be that either access is taking advantage of this, or is caching it from w/in the DB itself.. Also, did you implement a integer primary key, and use indexing? Randall Fox - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Adding and subtracting decimals is not accurate
On Fri, 04 Jun 2004 23:46:06 -0400, you wrote: >Welcome to the wonderful world of floating point numbers :-) > >I always store money values as cents (or as integers, what ever your >currency might call it) and move the decimal to format for display.. Yes, I took a closer look at floating point addition using my compiler. This does indeed work. I was worried that once the number got put into a floating point notation, the error would still creep up, but I didn't get that, even when adding millions of numbers. Still, this is a little awkward (but workable).. I may have to make a special "MONEY" type for users, and every time convert it to cents.. Hmm.. Thanks Randall Fox - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Adding and subtracting decimals is not accurate
On Fri, 04 Jun 2004 20:39:45 -0700, you wrote: >You mean like rounding once in a while to the number of significant >decimal places? Once every few billion additions like those above should >suffice. That defeats the purpose of being able to "SELECT SUM(X) FROM ... " The aggregate query sum(x) doesn't round for me, does it? Or are you saying that I should programmatically SUM instead of using the aggregate function? I would guess it to be slow, and not to mention awkward. Think big, summing 1 numbers or more... That error will creep up into the significant digits sooner or later.. Randall Fox - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Adding and subtracting decimals is not accurate
After reading the FAQ and type information over, I went and tried the following: CREATE TABLE M (A numeric, B text); INSERT INTO M VALUES (1.2); INSERT INTO M VALUES (1.2); INSERT INTO M VALUES (1.2); . . ; did this about 45 times . . SELECT SUM(A) FROM M; result was 52.8001 Defining the field as text made no difference, and in fact the best way was to put in the number without the decimal, to guarantee it will be treated as an integer (i think) instead of a float. But this could be confusing to the end user who doesn't understand the details of floating point arithmetic.. Is there a work around to accurately add fixed point numbers? Or more generally, has this been addressed before? I couldn't find any reference to it on the FAQ or web site.. This problem becomes more obvious when the 1.2 is interpreted as $1.20 Thank you Randall Fox - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
