Re: [sqlite] memory usage after VACUUM

> Is there any sqlite function I can call, or some other technique, to reduce > the memory allocated and hung-onto by sqlite, particularly during a VACUUM? Yes, execute "pragma cache_size = 100" for example, or put other number of your liking into there. If closing and re-opening of the database

Re: [sqlite] [sqlite-dev] Amalgamation code doesn't supoort limit on update / delete

http://lmgtfy.com/?q=SQLITE_ENABLE_UPDATE_DELETE_LIMIT+site%3Asqlite.org=1 If you look for description of SQLITE_ENABLE_UPDATE_DELETE_LIMIT on the resulting page you'll see that it's not a bug or oversight, it's a documented feature. Pavel On Sun, Mar 13, 2011 at 2:46 PM, Steven Hartland

Re: [sqlite] X most recent entries

Try this one: select * from (select * from multiturnTable order by rowid desc limit 5000) where (player1 = ? or player2 = ?) and (complete=0 or p1SubmitScore=0 or p2SubmitScore=0) Pavel On Mon, Mar 14, 2011 at 1:58 PM, Ian Hardingham wrote: > Ah, sorry about this - my query

Re: [sqlite] Only select most recent results takes longer

> My tests show that, counter-intuitively, the second query takes between > 3 and 5 times as long as the first query. > > This seems weird to me - any ideas why this would be? What does EXPLAIN QUERY PLAN for both queries say? And what SQLite version do you use? Also do you understand that '?'

Re: [sqlite] SQLite.so dynamic library-linux

What's wrong with compiling SQLite library by yourself with all parameters you need? Why do you need cross-compiler for that? What's wrong with compiler on your linux box? Pavel On Fri, Mar 18, 2011 at 10:54 AM, Udon Shaun wrote: > Hi Peeps. > I've noticed that the SQLite

Re: [sqlite] SQLite.so dynamic library-linux

Oh, and BTW, reply to the whole list, not to me only, please. Pavel On Fri, Mar 18, 2011 at 9:41 PM, Pavel Ivanov <paiva...@gmail.com> wrote: > I still don't understand what you do. If you don't have Linux, you > don't compile on Linux and you don't test on Linux then why do you &g

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

> Is there way how to store numeric values, which are out of REAL range ? SQLite has no way of storing numbers other than REAL or INTEGER. If you want the exact number to be stored your only option is to store it as TEXT (and don't work with it as a number on SQL level). Pavel On Tue, Mar 22,

Re: [sqlite] Create table if not exists and insert seed data

You can use a simple CREATE TABLE (without IF NOT EXISTS clause). If it succeeds then you populate table with data (remember to do that in the same transaction where you created the table). If CREATE TABLE fails then you don't insert your data. Pavel On Mon, Mar 21, 2011 at 6:04 PM, Erich93063

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

> Yes, but I can not affect column type ... FreePascal SQLite3 connector must > be able to work with any user database. If your goal is to work with any user database created outside of your FreePascal connector then chances are that user will use the same database outside of your FreePascal

Re: [sqlite] sqlite3_step() error and constraints

> http://sqlite.org/c3ref/step.html . See "Goofy Interface Alert" section > at the bottom. I believe it was changed with recent versions of SQLite. Is call to sqlite3_extended_result_codes (http://www.sqlite.org/c3ref/extended_result_codes.html) not needed anymore? Pavel On Thu, Mar 31, 2011

Re: [sqlite] updating records in table A from joined records in table B

> ... that is to say, update table_a.value from table_b.value, but only > on rows where table_a.key = table_b.key update table_a set value = (select table_b.value from table_b where table_b.key = table_a.key) Pavel On Fri, Apr 1, 2011 at 8:42 PM, Robert Poor wrote: > I'd

Re: [sqlite] Build instructions for Winodws with unicode support

> I can create the dll, but it does not work with BLOB data type. It works > with other data types. The dll that I downloaded from the sqlite.org website > works with BLOB data type. > > Any help would be appreciated. Any pointers on what doesn't work for you and how it works instead of intended

Re: [sqlite] Build instructions for Winodws with unicode support

                       return; >                } >        } > > > } > > The exact error message is as follows. > > The buffer could not be written to the database. > java.sql.SQLException: TEST.model may not be NULL > java.sql.SQLException: TEST.model may not be N

Re: [sqlite] Request for an example code use async IO

> Can someone be so kind as to provide a short example of initializing > asynchronous module, opening DB for read/write, create a table, and write > some data to it? There's nothing special in opening db, creating table or writing data into it while using async module. You should use the same

Re: [sqlite] Request for an example code use async IO

:  - sqlite3async_run() Pavel On Tue, Apr 5, 2011 at 1:45 PM, Ricky Huang <rhhu...@soe.ucsd.edu> wrote: > On Apr 4, 2011, at 7:10 PM, Pavel Ivanov wrote: > >>> Can someone be so kind as to provide a short example of initializing >>> asynchronous module, opening DB for read/wr

Re: [sqlite] No journal vs WAL journal

> Furthermore, if I turn off auto checkpointing, the WAL file grows to > more than 5 GB without transactions, but only to about 922 MB with a > transaction. Are the commit markers really taking that much space? WAL-journal is not some kind of change log with commit markers. It's a sequence of

Re: [sqlite] No journal vs WAL journal

<slav...@bigfraud.org> wrote: > > On 6 Apr 2011, at 3:15pm, Pavel Ivanov wrote: > >> For each transaction SQLite must >> write all changed pages into WAL-journal. It would be at least one >> page per transaction. > > Two ?  One for the page holding the table, an

Re: [sqlite] Access violation at address 6090B662 in module 'sqlite3.dll'. Read of address DE8D6B84

> any idea ? Your application have race conditions and corrupts memory. Pavel On Wed, Apr 6, 2011 at 2:36 PM, Vander Clock Stephane wrote: > hello, > > in heavy multithread environnmeent we receive (one time a month, so not > very often), this error : > Access

Re: [sqlite] GROUP BY Problem

Probably this could work: SELECT c1,c2,sum(t2.c3) / count(distinct t3.rowid),count(distinct t3.rowid) FROM t1 LEFT JOIN t2 on t2.key2=t1.key1 LEFT JOIN t3 on t3.key3=t1.key1 GROUP BY t1.key1 And btw, you didn't say where your c1 and c2 come from but if they are from t1 and t1 has several rows

Re: [sqlite] Trouble inserting null value from txt file

> There is a way to insert null values from a file without specifying it > in? It's impossible when you use standard sqlite3 command line utility. In your particular case you have 2 options: either write your own application that will recognize some kind of value as null and insert it instead of

Re: [sqlite] How is the page-cache filled?

> * Which version of the source should I use? The amalgamized source > isn't really useful Why it's not useful for you if it contains all the functionality SQLite have? > * Where are pages read and written? I found the page-cache, but not > the functions that read/write those pages to disk. You

Re: [sqlite] Problem setting journal_mode=WAL

> If the 'COMMIT' reports that no transaction is active, why is the > JOURNAL_MODE pragma still complaining about it being within a transaction? > Am I missing something here? Probably you have some not finalized SELECT statements still active. To check that you can call sqlite3_close - it will

Re: [sqlite] Corruption on many Windows XP hosts

> The fact that one engineer installed a site, began operating the app, > then saw it become corrupt minutes later rules out power loss or hard > resets in at least that case. An operating system level problem should > have been noticed by now given it's Windows XP... And the file is > locally

Re: [sqlite] Corruption on many Windows XP hosts

is way. Pavel On Wed, Apr 13, 2011 at 10:25 AM, Richard Hipp <d...@sqlite.org> wrote: > On Wed, Apr 13, 2011 at 9:53 AM, Pavel Ivanov <paiva...@gmail.com> wrote: > >> > The fact that one engineer installed a site, began operating the app, >> > then saw it be

Re: [sqlite] Balance between keeping DBs open vs repeated open/close

> Keep in mind that sqlite has a limit of 32 open databases. 32 _attached_ databases, not opened. With different sqlite3* handlers you can open as many databases as you want (and your memory permits). Pavel On Thu, Apr 7, 2011 at 6:05 AM, Eduardo wrote: > At 18:11

Re: [sqlite] 'integer'

Stefan, > SQLite should'nt be called a "SQL database" (as e.g. touted on its homepage). > Instead it should clearly declare itself as an "In-memory SQL > Datastore" or a "Data container with SQL capabilities". This is quite serious allegations. Making them you should explain what they are based

Re: [sqlite] What happens if you insert more than your RAM size into an in memory database?

You won't be able to insert. The statement will fail. Pavel On Mon, Apr 18, 2011 at 9:44 AM, Adam DeVita wrote: > Good day, > > What happens if you insert more than your RAM size into an in memory > database? > (I'm particularly interested in the Windows context). > >

Re: [sqlite] Performance issues with sqlite3 sqlite3_step function

> Depending on the database size, it sometimes takes minutes to get a result. > But when using the Firefox plugin "SQLite Manager", it "only" takes 1-2 > seconds. > > I use SQLITE Version 3.6.22. Maybe SQLite Manager uses newer version of SQLite? Try to execute in it "SELECT

Re: [sqlite] Performance issues with sqlite3 sqlite3_step function

AM, Spoerr Mathias <math...@spoerr.org> wrote: > Hello Pavel, > > thank you for your answer. SQLite Manager uses version 3.7.4, but I checked > the SQLite release notes and I could not find changes for sqlite3_step. > Should I upgrade anyway? > > Thanks, > Mathias >

Re: [sqlite] Performance issues with sqlite3 sqlite3_step function

cement was with 3.6.22 > http://www.sqlite.org/changes.html > > Thanks, > Mathias > > > > On Mon 18/04/11 16:05 , "Pavel Ivanov" paiva...@gmail.com sent: > >> I checked >> the SQLite release notes and I could not find changes for sqlite3_step. >> Sh

Re: [sqlite] Building sqlite for windows in a proper way

> Does anyone one know how to build sqlite to get the same binary as on > download page ? Did you try to remove all those defines that you add at build time and leave only default values set inside sqlite3.c file? Pavel On Mon, Apr 18, 2011 at 8:42 AM, Kuba Nowak wrote:

Re: [sqlite] Is Lock Table statement spported?

Whenever you have doubt like that just go to http://www.sqlite.org/lang.html and see if the statement you are trying to execute exists there and what its correct syntax is. Pavel On Wed, Apr 20, 2011 at 10:40 AM, wrote: > Is LOCK TABLE statement supported by by SQLite? I

Re: [sqlite] just fyi: iPhone and iPad 3G's tracking their owners' movements and saving to sqlite db

It's not related to the list but still... > Technically, the data referred to is as follows.  An iPhone logs details of > which phone base stations it connects to, and the 'status' data obtained from > the base station when it was connected.  The location (long & lat) of the > base station is

Re: [sqlite] date field with default current date

> Does not work on python with sqlite3 module What does "SELECT sqlite_version()" gives you in python with sqlite3 module? Pavel On Thu, Apr 21, 2011 at 9:17 AM, Fabio Spadaro wrote: > Hi. > > 2011/4/21 Black, Michael (IS) > >> create table t

Re: [sqlite] date field with default current date

Thu, Apr 21, 2011 at 1:43 PM, Fabio Spadaro <fabiolinos...@gmail.com> wrote: > Hi. > > 2011/4/21 Pavel Ivanov <paiva...@gmail.com> > >> > Does not work on python with sqlite3 module >> >> What does "SELECT sqlite_version()" gives you in python

Re: [sqlite] date field with default current date

nt UTC date and/or time." It won't be your local time. Pavel On Fri, Apr 22, 2011 at 5:46 AM, Fabio Spadaro <fabiolinos...@gmail.com> wrote: > Hi. > > 2011/4/22 Fabio Spadaro <fabiolinos...@gmail.com> > >> Hi >> >> >> 2011/4/22 Fabio Spadaro <fab

Re: [sqlite] query to find mixed values

> REQ3 is a problem because you have mixed signs in INV and only one record in > REP > > How would I craft a query to return REQ3? Could be something like this: select INV.REQ, count(case when INV.AMT > 0 then 1 else null end) positive, count(case when INV.AMT < 0 then 1 else null end)

Re: [sqlite] vacuum and rowids

> After more poking, it appears that rowids might not be changed by a vacuum if > I have an index on the table. Is this true? If so, is it something I can rely > on going forward? No, it's not true. The only way to keep your rowids intact is to declare an INTEGER PRIMARY KEY alias for it. And

Re: [sqlite] vacuum and rowids

>> which suggests that referring to rowids is fine. > > It does not suggest referring to ROWIDs is fine, it only says that it > can be done.  I think Pavel's point is that referencing ROWIDs is bad > practice, so that is why he says you shouldn't do it. Yes, that's right. You can refer to rowid,

Re: [sqlite] Compile Error

I believe any compilation options that require changes in SQL parser require compiling from original sources as well. They cannot be used with amalgamation file which has already generated SQL parser's code. Pavel On Tue, May 3, 2011 at 3:05 PM, jeff archer wrote: > I am

Re: [sqlite] INNER JOIN Optimization

> The problem is that I would like to avoid splitting the query into two parts. > I would expect SQLite to do the same thing for me automatically (at least in > the second scenario), but it does not seem to happen... Why is that? In short, because SQLite cannot read your mind. To understand

Re: [sqlite] Addition: Re: SQLite locking question

>> Any other chance to speed this up (apart from the obvious "optimize the >> query, do not use distinct on large tables)= > > Without seeing the query or database schema? Not really... Depending > on the exact query an index on "xyz" might help. Another suggestion could be to turn off shared

Re: [sqlite] Query question

> sqlite> select * from a where a=10 group by b; > 10|1 > 10|2 > sqlite> select * from a where a=11 group by b; > 11|2 > 11|3 > > How can I do count equivalent of such a query to find out how many > distinct values of b there are for a given a? (That is get an answer of > 2 in the above) select

Re: [sqlite] Addition: Re: SQLite locking question

> My understanding of the shared cache model was that it just ist intended > for solving our problem by relaxing the locking a little and that there > should not be any mutexes at all when using the uncomitted read mode. > > Have I missed anything? Yes, you are involved in a "magical" thinking.

Re: [sqlite] Question: Memory-Based Databases

> Is this true, or is the memory usage pretty much similar? Until you reach limit set by 'pragma cache_size' memory usage would be the same for in-memory database and on-disk database. When the size of your database grows beyond 'pragma cache_size' in-memory database starts to consume more memory

Re: [sqlite] Three questions

>> 00:01 Transaction A: BEGIN >> 00:02 Transaction B: BEGIN >> 00:03 Transaction A: INSERT INTO test VALUES (1) // works okay >> 00:04 Transaction B: INSERT INTO test VALUES (1) // aborts with 'duplicate >> key' error! why??? > > I get SQLITE_BUSY "database is locked" at this point, as I would

Re: [sqlite] Three questions

> Therefore, the second insert fails on every database system i ever > encountered. Apparently you didn't encounter Oracle. In such situation Oracle freezes transaction B until transaction A is committed or rollbacked. After that it knows what to return to transaction B - error or success

Re: [sqlite] Can I dynamically select a table ?

> I can't trully construct sql statement piece by piece with SQL > db as I did with Oracle. Just wanted to confirm. Why do you need to construct SQL specifically with db's tools? Why can't you do that in your host language? Oracle needs dynamic SQL feature because it will work much faster than

Re: [sqlite] Multi-threading Common Problem

> "After a BEGIN EXCLUSIVE, no other database connection except for > read_uncommitted connections will be able to read the database and no other > connection without exception will be able to write the database until the > transaction is complete." > > This tells me that reads outside of a

Re: [sqlite] Common Multi-treaded Problem

> Interesting is the impression I had with prepared statements was the reset > was only necessary if you wanted to reuse that statement.  Since each each DB > connection is in its own instance of a class (with it own set of prepared > statements) I would not think there would be any dependency

Re: [sqlite] Common Multi-treaded Problem

t prepared statement will lock the DB from other threads > (or is it DB connections?) but not the current thread (or is it DB > connection). > > Thanks for the help! > > John > > --- On Thu, 5/12/11, Pavel Ivanov <paiva...@gmail.com> wrote: > >> From: Pa

Re: [sqlite] Common Multi-treaded Problem

, John, for bullying in the discussion... > > On Thu, 12 May 2011 17:58:40 -0400 > Pavel Ivanov <paiva...@gmail.com> wrote: > >> There's no dependency between different prepared statements, but there >> is dependency between transactions as they use the sam

Re: [sqlite] IN clause in search query to search a single field containing comma delimited values

> SELECT categories FROM myTable WHERE (","||categories||",") LIKE "%,7,%"; > > but I'm guessing LIKE isn't as efficient, and the query is more > awkward.  Any advise would be appreciated.  Thanks! This LIKE is the only way to get information from your table. But you can do it more efficiently by

Re: [sqlite] Common Multi-treaded Problem

n of the > former was more established than the later. > > Again thanks for the information and I apologize for taking up so much list > bandwidth.  I hope others can benefit. > > John > > --- On Thu, 5/12/11, Pavel Ivanov <paiva...@gmail.com> wrote: > >> From: Pav

Re: [sqlite] threading and grand central dispatch (OS X/iOS)

> I'm still not 100% sure if there's a problem sharing connections across > threads with SQLITE_OPEN_NOMUTEX as long as I guarantee that they aren't > concurrent. I suspect there aren't, but I'm not 100% sure. Any case where > sqlite3 would be less than happy that pthread_self wasn't always the

Re: [sqlite] Query efficiency

> That is, is leaving it to the > query optimiser to figure out that I only need the sub select once the > best thing to do? AFAIK, SQLite's optimizer is not that smart to collapse two identical sub-queries and reuse once generated result. > Is the select I'm doing where both a_id1 & 2 are "in"

Re: [sqlite] SQL Statement Help(selecting days).

> I need help to build a statement in order to select all days free from > events in a specific time range. This kind of task should be implemented in your application. SQL wasn't intended for and can't solve such tasks. Pavel On Wed, May 18, 2011 at 4:06 PM, Danilo Cicerone

Re: [sqlite] Query efficiency

where ab.a_id2 = 1 and ab.b_id = 1) ; > 1|1 > 1|2 > 2|1 > sqlite> select aa.* from aa ar, ab ab1, ab ab2; > Error: no such table: aa > sqlite> select aa.* from aa as ar, ab ab1, ab ab2; > Error: no such table: aa > sqlite> > > At this point I got confused b

Re: [sqlite] (no subject)

> Is there a rationale for allowing such statements or is that an effect > of the 'Lite' nature?  (Note: I'm not complaining, just asking.) I believe that's an effect of the "typeless" design. As SQLite doesn't have strict type names for columns it accepts pretty much anything for that. So in

Re: [sqlite] Multi-threading Common Problem

> I have all writes in transactions.  If I deactivate my pthread_rwlock() that > enforce the above, several writes fail with a "database locked" error (I > assume it is returning SQLITE_BUSY). > > So how do I implement the equivalent of a pthread_rwlock() using SQLite > mechinisms? When

Re: [sqlite] Multi-threading Common Problem

ed I should be using SQLite-specific mechanisms to achieve the same > results.  I am just trying to understand how to do that.  Pthread_rwlock() > works fine. > > Thanks, > > John > > --- On Tue, 5/24/11, Pavel Ivanov <paiva...@gmail.com> wrote: > >> From

Re: [sqlite] Using sqlite.net with Linq to create database

> If I try to query database existence using Linq's "DatabaseExists" I get > a NotImplemented exception in the sqlite ChangeDatabase function - which > doesn't make sense to me. SQLite doesn't have a notion of server containing several databases. That's why it makes perfect sense that SQLite

Re: [sqlite] Using sqlite.net with Linq to create database

ssuing this command then it can't be used with SQLite. Pavel On Tue, May 24, 2011 at 11:05 AM, Ruth Ivimey-Cook <r...@ivimey.org> wrote: > Pavel Ivanov wrote: >>> If I try to query database existence using Linq's "DatabaseExists" I get >>> a NotImplemented

Re: [sqlite] Incorrect left join behavior?

> I can use two left joins.  While writing the first left join, I discovered > that it is behaving like an inner join. > > select * > from test a >   left join test b on a.component = b.component > where a.machine = 'machine1' >   and b.machine = 'machine2'; By the WHERE condition you limit

Re: [sqlite] SQLite in Xcode

> 2.)     I moved SQLite3.c and SQLite3.h into my source folder and added them > to the project.  I made no changes to the code nor did I do anything special > when I added them to my project (i.e. I did not set any special compile > flags-I simply added the two files to the project). > 3.)    

Re: [sqlite] SQLite in Xcode

> Here are two errors out of the 1200+ errors generated (I tried to include as > much information as possible to help identify the problem.  Please forgive if > I am overdoing it...): > >        Compiling /.../sqlite3.c >        Error:invalid conversion from "void *" to "char *" >        This

Re: [sqlite] TRANSACTIONs

>  From within a BEGIN TRANSACTION and END TRANSACTION block, should I not > update the same row in a table more than once? You can update it as many times as you need. > What are the exact limitations on what I can do during a Transaction? Do not update a table if there is some select

Re: [sqlite] TRANSACTIONs

.org for you when I have some spare time). Pavel On Wed, Jun 1, 2011 at 2:41 PM, Robert Myers <rob.my...@ziften.com> wrote: > On 6/1/2011 1:23 PM, Simon Slavin wrote: >> On 1 Jun 2011, at 7:12pm, Jan Hudec wrote: >> >>> On Wed, Jun 01, 2011 at 10:17:02 -0400, Pavel Iva

Re: [sqlite] TRANSACTIONs

o read uncommitted data in the same transaction. Pavel On Wed, Jun 1, 2011 at 2:57 PM, Robert Myers <rob.my...@ziften.com> wrote: > On 6/1/2011 1:47 PM, Pavel Ivanov wrote: >>> Actually, you do know what SQLite does without knowing the internals. It >>> claims to b

Re: [sqlite] Trigger help needed

> CREATE TRIGGER ts_update after update on ip_timestamps when This means that your trigger fires after each UPDATE statement. But > I'm adding/updating records with statements like: > > INSERT OR REPLACE into ip_timestamps VALUES ( "182.71.33.222" , 1306932777 ); you do INSERT statements, so

Re: [sqlite] Bug reports

> I did try sending a mail over the dev-list, but got no reply. Could > someone tell me how it should be done? You should send it here with the exact steps of reproducing the bug. Hopefully your tool is not code analyzer because "potential bugs" without any real life steps to catch it won't be

Re: [sqlite] HELP : how to use datetime('column', 'localtime') as a part of sql string

> but is returning the value stored in the database when executed > as a part of a java program. Can  anyone help me if I am missing > something here ? Looks like your java program runs with a different locale than SQLite Manager. I'm not sure why it thinks that you are in a UTC timezone. Maybe

Re: [sqlite] HELP : how to use datetime('column', 'localtime') as a part of sql string

; On 02-06-2011 19:34, Sridhar Polavarapu wrote: >> >> The returned value is displayed to the user. We are not storing back the >> returned column. >> >> Sridhar >> >> On 02-06-2011 19:28, Pavel Ivanov wrote: >>>> >>>> but is returning the

Re: [sqlite] HELP : how to use datetime('column', 'localtime') as a part of sql string

LETE|md|2011-06-02 07:16:24 > 728|SridharTest|2011-06-02 12:31:59|COMPLETE|md|2011-06-02 07:02:25 > 727|automatedTest_1306496245310334336328084528|2011-06-02 > 12:24:48|COMPLETE|md|2011-06-02 06:55:29 > 726|automatedTest_130648151940731961056560|2011-06-01 > 12:30:33|COMPLETE|md|2011-06-01 07:01:04 > 725|automa

Re: [sqlite] Slow rollback - possible causes?

> DB file in WAL mode, checkpointing done every 5 seconds by separate > thread in program Depending on the mode of checkpointing you use it can fail if there are some other reading or writing transactions in progress. And at the time you observe very long rollback actual checkpointing happens

Re: [sqlite] Slow rollback - possible causes?

o 130MB or so.  I'll try to catch > the WAL size and see if it changes dramatically. > > (Actually, that was another general question we had, should that WAL > file ever shrink during use?  Why would it grow to that size at all?) > > -Eric > > On Tue, Jun 7, 2011 at 6:44 PM, Pave

Re: [sqlite] Slow rollback - possible causes?

sify.com> wrote: > So, should the WAL file shrink back to 0 then?  We're issuing "PRAGMA > wal_checkpoint" to the open DB handle. > > -Eric > > On Tue, Jun 7, 2011 at 7:06 PM, Pavel Ivanov <paiva...@gmail.com> wrote: >>> (Actually, that was another gen

Re: [sqlite] Cannot load SQLite.Interop.dll but file is in the folder

ies For Windows" on the download page? > Should I re-compile SQLite with the same framework than the one used for > my application? > > Thanks for your support > Cyrille > > > Le 31/05/2011 15:57, Pavel Ivanov a écrit : >> I would ask one more question: do they have t

Re: [sqlite] Sqlite DATE() function. Add a variable amount of months.

> The main issue for me here is that the +1 is inside the string '+1 month' > and i thus cant use a subquerry of the type SELECT date('now','+(SELECT id > FROM table) month'); You can do like this: SELECT date('now','+' || (SELECT id FROM table) || ' month'); Pavel On Wed, Jun 8, 2011 at

Re: [sqlite] Table info for a particular query

> Is there any way to do something along the lines of PRAGMA > some_command(SELECT * FROM table1 JOIN table2) and have it return A, B, C, > a, b, c as the headers? If you insist on getting results as resultset then there's no way to do that. But you can do the similar thing by using API like

Re: [sqlite] Dynamically loaded sqlite (linux)

> I know that I should avoid such things, but just curious, is it something > inside sqlite that probably makes one dynanmically linked and other > dynamically loaded library share global data and can this be avoidable? No, it's Linux linker who works like that. If you have several libraries

Re: [sqlite] [sqlite-dev] Index problem on Windows

> So my next question is then, given that I have some data coming in, in > a random order, and I want an index. What can I do in order for the > performance to be better? Create index only after all data is populated. BTW, this kind of questions will be answered best on a sqlite-users list.

Re: [sqlite] Bug in composite foreign key constraints.

IIRC, there was a bug in enforcement compound foreign keys where one of columns is primary key and it was fixed. So if you take the latest SQLite version I guess you won't see this problem. Pavel On Fri, Jun 10, 2011 at 11:12 AM, Max B wrote: > Hello, > > Please consider

Re: [sqlite] [sqlite-dev] Is there an option to not lock during transaction?

> Hmm... unless read_uncommited is persistant somehow; looks like if I > just don't try to use shared cache it works... I thought I had > disabled cache before alone and still got the same results... read_uncommitted works only when shared_cache is on. To answer your original question: if you

Re: [sqlite] [sqlite-dev] Is there an option to not lock during transaction?

Jun 17, 2011 at 1:18 AM, Florian Weimer <f...@deneb.enyo.de> wrote: > * Pavel Ivanov: > >> To answer your original question: if you disable shared cache, start >> reading transaction on one connection and start writing transaction on >> the other connection then yo

Re: [sqlite] Opening a database on a Mac

>   strcpy(DBEnginePath, "Macintosh HD:Applications:Adobe InDesign > CS5:Plug-Ins:WPC_ID:IndexData.db"); Try to change path here to "/Applications/Adobe InDesign CS5/Plug-Ins/WPC_ID/IndexData.db". Pavel On Fri, Jun 17, 2011 at 3:08 PM, john darnell wrote: > Sorry

Re: [sqlite] Sqlite database integrity check fails with disk I/O error (10)

> Can some one help me why the disk I/O error occured during the > sqlite3_prepare()  statement ? Because while preparing the statement SQLite must read schema from the database. And although in case of integrity check it's not really necessary I think it's still the general rule and is done

Re: [sqlite] openmpi parallel problem

Do you check result code of connection closing? Is it successful? If it's not successful then some statements are still active and reading transaction is still open. That could be the reason of error in write process. Pavel On Wed, Jun 22, 2011 at 10:58 AM, Thorsten Kersting

Re: [sqlite] openmpi parallel problem

far as i can see. > > On 06/22/2011 05:29 PM, Pavel Ivanov wrote: >> Do you check result code of connection closing? Is it successful? >> If it's not successful then some statements are still active and >> reading transaction is still open. That could be the reason of error

Re: [sqlite] [BUG?] unexpected effect of view nesting on type conversion

> Just thought I should add that the problem seems to be gone with the > just released version 3.7.7, but it's not mentioned in the release notes. It's not in release notes but it's in the timeline. See http://www.sqlite.org/src/info/91e2e8ba6f and http://www.sqlite.org/src/info/0b3174e0b1.

Re: [sqlite] help needed to optimize a query

> This has to run as fast as possible. A left join between these tables is too > slow, for 10.000 entries it takes around 15 seconds just to navigate through > the cursor, if I add a where clause selecting only one kind of data then it > reduces to less than 5 seconds which is acceptable. What

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

>> Putting the 'ORDER BY' clause in view won't work? > > It will work just fine, in that the results you see will appear in the ORDER > you asked for. I believe that's not always true and is not required by SQL standard. Most probably 'select * from view_name' will return rows in the order

Re: [sqlite] sqlite3_create_function and threads

> To put it another way, if I call sqlite3_create_function to install a > custom function, is that function now available to all threads using > SQLite or is it available only to the thread that made the > sqlite3_create_function call? Yes, it's available to all threads using the same connection.

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

; ones or at least make it look like that even if the records are physically > appended to the tables. > Hope this clarifies things and look forward to your thoughts. > > > On 1 July 2011 15:30, Pavel Ivanov <paiva...@gmail.com> wrote: > >> >> Putting the 'ORDER BY' cl

Re: [sqlite] sqlite3_create_function and threads

>  > Yes. What's wrong with that? > > Nothing at all - I just needed to know whether that was the case so I > could design certain sections of my code accordingly. Three question marks of yours suggested me that you think it's awfully wrong. Note that although your function and application

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

e in there somewhere. > It strikes me that > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' > needs to be operating on the results returned by > SELECT * FROM t_x BY ORDER BY pos > ie another level of query is required but I'm not sure of how you insert it. > I'll have a play. > &g

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

to add more data to the tables to see whats > going on. > > Thank you for your assistance though. > > On 1 July 2011 17:07, Pavel Ivanov <paiva...@gmail.com> wrote: > >> > It strikes me that >> > SELECT pos FROM t_x WHERE txt BETWEEN 'x1' AND 'x2' >&g

Re: [sqlite] Ensure that query acts on PRE-SORTED tables

rom the pos order perspective > ie doing the BETWEEN query on 'x1' and 'x2' would be useless from the rowid > perspective/order. > I'll write a better description of what I'm trying to do and come back. > > On 1 July 2011 17:48, Pavel Ivanov <paiva...@gmail.com> wrote: > >

Re: [sqlite] Java and accented characters

> Any idea? Please, anyone can help me? How did you put your data into database? I guess you did that with command line utility. And as your console wasn't set to have UTF-8 charset then contents of pais now is not in UTF-8. But jdbc driver tries to treat it as UTF-8 and so you don't see what you

Re: [sqlite] DROP TABLE yields SQLITE_LOCKED from user-defined function

> Is there something special one needs to do in order to drop a table from > inside a user-defined function? > > To test, I call it simply like so: > SELECT my_function(); It shouldn't be ever possible to change the database from within a function called from SELECT statement. SELECT query

  1   2   3   4   5   6   7   8   9   10   >