[sqlite] Maximum database size?

Hello SQLite users, I've been running into some disk I/O errors when doing things such as vacuuming and/or inserting things into temp tables in a database. The databases that are giving me trouble are quite large: between 29 and 55GB. However, as large as that is, I don't think running out of

Re: [sqlite] Order of triggers

On 2 Mar 2010, at 8:38pm, Jens Frøkjær wrote: > So, please consider this a feature request: "Deterministic order of > triggers". I understand what you want, but I don't think you'll get it. SQL is full of ambiguity about orders. For instance suppose you execute an UPDATE command which

Re: [sqlite] The character "'" not liked by sqlite?

On 2 Mar 2010, at 7:45pm, Adam DeVita wrote: > If you look in http://www.sqlite.org/capi3ref.html#sqlite3_bind_blob > for the function > > int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*)); > > This will allow you to bind any character into an SQL statement. > There

Re: [sqlite] A question about sqlite : How could i get the rows affected by current sql statement ?

> This function returns the number of row changes caused by INSERT, UPDATE or >> DELETE statements since the database connection was opened. >> > > Either you're or this sentence on the site should be changed (in the final > part) Oops, missed the last part. But it seems not very useful for OP

Re: [sqlite] Order of triggers

What's wrong with adding new code to existing triggers instead of creating new ones? Pavel On Tue, Mar 2, 2010 at 3:38 PM, Jens Frøkjær wrote: > Hi, > Sorry to BUMP this thread. I was hoping someone would come along with a > better answer. > This means that executing the same

Re: [sqlite] Order of triggers

Hi, Sorry to BUMP this thread. I was hoping someone would come along with a better answer. This means that executing the same deterministic piece of SQL on two identical databases could yield different results. And I don't mean different in "order of rows in tables" or something similar. My

Re: [sqlite] The character "'" not liked by sqlite?

Good day, If you look in http://www.sqlite.org/capi3ref.html#sqlite3_bind_blob for the function int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*)); This will allow you to bind any character into an SQL statement. There are other benefits to using this technique.

Re: [sqlite] A question about sqlite : How could i get the rows affected by current sql statement ?

On Tue, Mar 2, 2010 at 6:36 PM, Pavel Ivanov wrote: > OK, now I see the problem, but sqlite3_total_changes() will not help > here too - it behaves the same way as sqlite3_changes(), it doesn't > accumulate changes over several statements. > Hmm... are you sure about this? A

Re: [sqlite] The character "'" not liked by sqlite?

Simon and Gabriel, I'm using the C API, I'm inserting strings. One of the strings happens to have an "'" in it. I have to write extra code to parse the character and escape it, I'll do that if I have to. I have not tried the command line tool. I'll try it and get back to you. Kavita On 3/2/10

Re: [sqlite] The character "'" not liked by sqlite?

You should be using prepared statements. If that's not possible, then escape the "'", for example: INSERT INTO "this" VALUES ('Rootuser''s Desktop') On Tue, 2010-03-02 at 12:51 -0600, Kavita Raghunathan wrote: > I notice that when I try to insert the character “’” as part of a string

Re: [sqlite] The character "'" not liked by sqlite?

On 2 Mar 2010, at 6:51pm, Kavita Raghunathan wrote: > I notice that when I try to insert the character “’” as part of a string into > the sqlite database, my updates don’t work. Any ideas why? The same string > without the “’” character works. I have not debugged to see where exactly in >

[sqlite] The character "'" not liked by sqlite?

I notice that when I try to insert the character “’” as part of a string into the sqlite database, my updates don’t work. Any ideas why? The same string without the “’” character works. I have not debugged to see where exactly in sqlite it fails. I’m inserting a text like this: “Rootuser’s

Re: [sqlite] why is underscore like dash?

Oh snap! Well, the reason I bring it up is because fossil uses the LIKE operator to compare file names when adding new files to a fossil repository. If the file name you're adding has an underscore, then craziness ensues. Thanks for the quick answer and your patience with my ignorance. I'll

[sqlite] lemon mods

With all the good changes to lemon recently, I thought I'd post changes that I have made to my personal copy.  One of my customers requires that I demonstrate the version of all build tools I use at build time, so I added a few command line parameters to help with that. It would be nice if the

Re: [sqlite] why is underscore like dash?

Perhaps because underscore is considered to be a wild-card search character. Take a look at: http://sqlite.org/lang_expr.html#like If you want to match underscore literally, use an optional escape character clause and escape the underscore. -Allan > -Original Message- > From:

Re: [sqlite] why is underscore like dash?

On Tue, Mar 2, 2010 at 9:41 AM, Wilson, Ronald wrote: > sqlite> select * from test where text like '_'; from http://www.sqlite.org/lang_expr.html > An underscore ("_") in the LIKE pattern matches any single character in the > string.

Re: [sqlite] why is underscore like dash?

> This test was performed on Windows XP: > > PS C:\Documents and Settings\ma088024> sqlite3 > SQLite version 3.6.22 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> create table test (text); > sqlite> insert into test values('_'); > sqlite> insert into test

Re: [sqlite] why is underscore like dash?

>sqlite> select * from test where text like '_'; Underscore '_' is LIKE wildcard for any single character, percent '%' matches any substring. ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] why is underscore like dash?

This test was performed on Windows XP: PS C:\Documents and Settings\ma088024> sqlite3 SQLite version 3.6.22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table test (text); sqlite> insert into test values('_'); sqlite> insert into test values('-');

Re: [sqlite] possible buffer over-read in sqlite3VXPrintf()

On Mar 2, 2010, at 6:54 PM, Jonathan Kew wrote: > I've run into what appears to be a small bug in this function (from > sqlite3.c, v 3.6.22). Suggested patch: > > diff --git a/sqlite3.c b/sqlite3.c > --- a/sqlite3.c > +++ b/sqlite3.c > @@ -16938,17 +16938,17 @@ SQLITE_PRIVATE void

[sqlite] Type affinity changed by HAVING clause

Hi, I think I may have found a bug where affinities change through the HAVING expression. For example, under v3.6.22, if I do... create table t1(a text, b int); insert into t1 values(123, 456); select typeof(a), a from t1 group by a having a

Re: [sqlite] FTS & Doc Compression

My db definitely did go up in size with fts - which I think is ok just because that's what needs to be when using fts. So I'm not concerned so much about the stop words and things, although I agree that adjusting that list would definitely help. Since I'm on a mobile device, space is key. I

Re: [sqlite] A question about sqlite : How could i get the rows affected by current sql statement ?

OK, now I see the problem, but sqlite3_total_changes() will not help here too - it behaves the same way as sqlite3_changes(), it doesn't accumulate changes over several statements. So without introducing some difference between SELECT queries and any data-changing queries in your program you won't

[sqlite] sqlite3BtreeSetPageSize() compile error with SQLITE_OMIT_PAGER_PRAGMAS and SQLITE_OMIT_VACUUM

The current Fossil trunk [dd4962aa34] does not compile with both * SQLITE_OMIT_PAGER_PRAGMAS * SQLITE_OMIT_VACUUM enabled. These defines exclude btree.c sqlite3BtreeSetPageSize(), but it is still referenced from build.c. The problem was introduced by Check-in [5dcfb0c9e4]: "Make the TEMP file

[sqlite] possible buffer over-read in sqlite3VXPrintf()

I've run into what appears to be a small bug in this function (from sqlite3.c, v 3.6.22). Suggested patch: diff --git a/sqlite3.c b/sqlite3.c --- a/sqlite3.c +++ b/sqlite3.c @@ -16938,17 +16938,17 @@ SQLITE_PRIVATE void sqlite3VXPrintf( int i, j, k, n, isnull; int needQuote;

[sqlite] Maybe just a Question

Is there something that I do not know about the protocol around here? http://sqlite.org:8080/cgi-bin/mailman/confirm/sqlite-users/ff1e1920f75999f00d53fb1c451753e70393fcf7 URL says that the item has expired after 3 days. How do I find out what happened? Where do i go to find out the

Re: [sqlite] BLOBs' affect on DB performance

Hello Paul, My experiences with blobs suggests it's better to keep them in a different DB file. My uses sounded very similar to yours, tables of normal data interleaved with blob inserts. The physical process of having to move from page to page seems to be the bottleneck, not Sqlite itself. I

Re: [sqlite] A question about sqlite : How could i get the rows affected by current sql statement ?

On Mon, Mar 1, 2010 at 6:31 PM, Pavel Ivanov wrote: > sqlite3_changes() is exactly what you should use in this case. And I > didn't understand why did you find it unsuitable for you? > > Pavel > I think I understand his confusion. Imagine if for some reason you don't know

Re: [sqlite] FTS & Doc Compression

On Tue, Mar 2, 2010 at 2:41 AM, Jason Lee wrote: > Hi all, > > I've been playing around with the FTS3 (via the amalgamation src) on a > mobile device and it's working well. But my db file size is getting > pretty big and I was looking for a way to compress it. > Jason, can

Re: [sqlite] BLOBs' affect on DB performance

On Tue, Mar 2, 2010 at 10:54 AM, Paul Vercellotti wrote: > Now I'm guessing that storing all those blobs will slow down access to the > main tables (assuming records are added gradually - most without associated > blobs, some with), because records would be spread out over

Re: [sqlite] UPDATE

On 2 March 2010 11:31, Matt Eeles wrote: > Hi, > > > > I'm trying to update a field of the last record using UPDATE and MAX(). > The following query parses ok but updates all records.  Any reason why ? > > > > UPDATE logs SET Stop = DATETIME('NOW') WHERE (SELECT MAX(ID)

Re: [sqlite] UPDATE

On 2 Mar 2010, at 11:31am, Matt Eeles wrote: > I'm trying to update a field of the last record using UPDATE and MAX(). > The following query parses ok but updates all records. Any reason why ? > > > > UPDATE logs SET Stop = DATETIME('NOW') WHERE (SELECT MAX(ID) FROM logs) Your WHERE clause

[sqlite] UPDATE

Hi, I'm trying to update a field of the last record using UPDATE and MAX(). The following query parses ok but updates all records. Any reason why ? UPDATE logs SET Stop = DATETIME('NOW') WHERE (SELECT MAX(ID) FROM logs) Thanks, Matt.

[sqlite] Possible Corruption for DB malformation

Hi, Below is the part of code which gives error, when running the corrupted DB with gdb, i guess this may help to find the root cause for DB corruption, The DB was able to recover with "vacuum" command also. SQLite version 3.6.22 Enter ".help" for instructions Enter SQL statements