Re: [sqlite] Multiprocess accessing SQLite connection

ting via ATTACH doesn't magically eliminate those issues - you still can't have two processes write to the same file simultaneously. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sq

Re: [sqlite] Multiprocess accessing SQLite connection

On 6/17/2013 1:01 AM, Vijay Khurdiya wrote: In that case can I have separate DB file associated with each process. Of course. Just pass different file names to sqlite3_open or similar. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users

Re: [sqlite] GROUP BY syntax

by field1; Is the above syntax standard ANSI SQL? No, it's an extension implemented by SQLite. Most other DBMS produce an error for such statements. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin

Re: [sqlite] Feature request: add support for COMMENT statement

On 6/14/2013 10:17 AM, Finn Wilcox wrote: Yes but it is defined once-per-file instead of once-per-table. "we've found that the use of a single place to store our version number makes the checking much easier." -- Igor Tandetnik ___ sq

Re: [sqlite] sqlite security

root or Administrator, you own the system and can mess with it in all kinds of creative ways. It's that little insignificant detail of "privilege escalation exploit to be able to do so initially" that we are discussing. You make it sound like those are trivial to come by. -- Igor

Re: [sqlite] Feature request: add support for COMMENT statement

On 6/14/2013 9:59 AM, Dave Wellman wrote: We use a COMMENT to store information about the version of our tables that are in place on the customer system. PRAGMA user_version is intended for this very purpose. -- Igor Tandetnik ___ sqlite-users

Re: [sqlite] sqlite security

rrier" is zero. Citation needed. A low-privilege process cannot overwrite system DLLs on Windows. How come microsoft.com and other Microsoft's web properties are not lying in shambles? Windows is not nearly as bad as you make it out to be. -- Igor

Re: [sqlite] Feature request: add support for COMMENT statement

but SQLite doesn't support it. Suppose you have it. What would you do with it? What's the use case? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite security

third party (information disclosure). -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Prepared statement invariants

the values to change. Is that a safe assumption to make? Well, that's how it works with the current implementation. Whether it will work this way forever, I don't know. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org

Re: [sqlite] Prepared statement invariants

On 6/13/2013 8:29 PM, Igor Tandetnik wrote: The column addition should have failed, unless you are using WAL in which case changes made by the writer are not visible to outstanding readers. A useful test would be to prepare the statement, then get column count (without ever stepping), then add

Re: [sqlite] Prepared statement invariants

, then get column count (without ever stepping), then add the column, then get column count again. Or alternatively, step then reset then add the column. The bottom line is, schema change should occur while the statement is not active for the experiment to be meaningful.

Re: [sqlite] Different result set of query in examples of SQLite book

work no differently than, say, COUNT(1), which in turn should be equivalent to COUNT(*). Of course, there is really no good reason to write COUNT(id) here. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi

Re: [sqlite] Obtain limit-less count of previous select

(), not on _prepare(). Prepare is always nearly instantaneous. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updating a table from itself

the definition of his table or relevant indexes, there is no way to tell what INSERT OR REPLACE would do. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updating a table from itself

, but none such exists at the moment. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updating a table from itself

On 6/8/2013 2:51 PM, Dave Wellman wrote: update t1 from (select c1,c2 from t1) as dt1 set c2 = dt1.c2 where t1.c1 = dt1.c2 - 1; update t1 set c2 = coalesce((select c2 from t1 dt1 where t1.c1 = dt1.c2 - 1), c2); -- Igor Tandetnik ___ sqlite-users

Re: [sqlite] Transaction

On 6/8/2013 10:17 AM, RSmith wrote: Could someone please shortly discuss performance considerations for having nested Transactions vs. sequential transactions in SQLite. There ain't no such thing as a nested transaction. The second BEGIN statement will simply fail. -- Igor Tandetnik

Re: [sqlite] Not reading data in the -wal file? (Not Reproducible)

. New record is ABSENT. One possibility: Process 2 already has a connection, that has a pending read transaction (and so doesn't observe the concurrent write). Shared cache is enabled. The new connection then also effectively sees the same open read transaction. -- Igor Tandetnik

Re: [sqlite] select count(*) does not use primary key??

s count real fast for some reason, then create a separate table, with one column and one row, that would store the count. Then create INSERT and DELETE triggers that would maintain the count. -- Igor Tandetnik ___ sqlite-users mailing list sq

Re: [sqlite] Row_number?

On 6/4/2013 8:49 AM, Paxdo wrote: In fact, it is for purposes of pagination. For example, I have a web application that displays a list of customers. Each page of the list is 20 lines. It is sorted by city name. http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor -- Igor Tandetnik

Re: [sqlite] sqlite3_randomness Quality

there was none before. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Correct way to open multiple in memory databases

In case it helps, QSQLiteDriver has a constructor taking sqlite3* handle. You could open the database with sqlite3_open_v2, then wrap a QSQLiteDriver instance around it. Igor Tandetnik On 5/28/2013 9:49 AM, rol...@logikalsolutions.com wrote: Correct. We must not drop below the Qt interface

Re: [sqlite] Correct way to open multiple in memory databases

feature, but not OK to use SQLite-specific API to gain access to it? Igor Tandetnik On 5/28/2013 9:49 AM, rol...@logikalsolutions.com wrote: Correct. We must not drop below the Qt interface level. On May 28, 2013 at 7:29 AM Igor Tandetnik <i...@tandetnik.org> wrote: On 5/28/2013 8

Re: [sqlite] Correct way to open multiple in memory databases

your application from calling sqlite3_config(SQLITE_CONFIG_URI, 1), or using sqlite3_open_v2() with SQLITE_OPEN_URI flag to open the database? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman

Re: [sqlite] Exact content of a column in a row

ex(QUERYID), len(QUERYID), count(*) from MyTable group by 1; This should collect enough information to determine the actual contents of the column. Post the results here if you have difficulty interpreting them. -- Igor Tandetnik ___ sqlite-users ma

Re: [sqlite] Use more than one database in one application

connection to each) and never had any problems. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Julian days in Sqlite

y you have experienced in this thread. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Julian days in Sqlite

so on, as well as, say, 2012-15-35T00:00:00. This has nothing to do with leap seconds, and everything to do with the fact that mktime accepts values out of range and automatically normalizes them. -- Igor Tandetnik ___ sqlite-users mailing list sql

Re: [sqlite] Getting Started with Sqlite

uot; :syntax error. What am I doing wrong? You are running this command on sqlite3 command line (do you see "sqlite3>" prompt?) Instead, you should run this command on Windows command line (Start > Run > cmd), in order to start sqlite3 shell with a give

Re: [sqlite] Using "COLLATE nocase" with BETWEEN

c/' and 'ABC0' The expression "x between a and b" behaves the same as "x >= a AND x <= b". In your example, you end up with data >= 'abc/' and data <= 'ABC0' COLLATE nocase; The two comparisons use different collations. -- Igor Tandetnik

Re: [sqlite] autoincrement and primary key

to fail instead of advancing key to a new integer. Just make the column "INT PRIMARY KEY NOT NULL" (note INT rather than INTEGER). This way, it is not an alias for ROWID but a column in its own right, and doesn't get assigned a value automatically. -- Igor

Re: [sqlite] query help

not in (select num1 from tab); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_REAL was not declared in this scope

://sqlite.org/c3ref/c_blob.html) -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting datatypes of columns in a resultset from an SQLITE view

Yes, as long as p_Stmt is positioned on a row (that is, the last call to sqlite3_step returned SQLITE_ROW). In SQLite, columns don't really have types; only individual values do. Igor Tandetnik On 5/17/2013 1:32 PM, Dulini Atapattu wrote: Adding more to my question, is it possible to get

Re: [sqlite] (no subject)

table2 where id2=id1) where t1 is null; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] bind field name?

On 5/14/2013 7:47 PM, E. Timothy Uy wrote: Hi, is it possible to bind a field name to a sqlite query? For example, WHERE @field1 > @param1 No. A parameter may only appear where a literal would be allowed. -- Igor Tandetnik ___ sqlite-users mail

Re: [sqlite] Is there a way to select a precision?

ahead and do that - I'm not sure what that has to do with SQLite. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting indexes of multiple instances of one bound parameter name

., internally replaces them both with "?1" then i can see how it would work as-is. Effectively, yes, that's pretty much what happens. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listi

Re: [sqlite] Getting indexes of multiple instances of one bound parameter name

me manner. What do you mean, get the values? There's only one parameter, to which you can bind one value. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature request: Generate 'INSERT' command

FOR myTable FOR COLUMNS (id, player, team, time) Are you perhaps looking for .dump command of sqlite3 console? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is _reset() removes binding?

On 5/1/2013 7:30 PM, Igor Korot wrote: 1. Is the call to sqlite3_reset() removes the binding for the column a? No. You can use sqlite3_clear_bindings for that. 2. Do I need to call sqlite3_reset() at all? Yes. You can't bind parameters to an active statement. -- Igor Tandetnik

Re: [sqlite] Query optimizer bug?

ich cannot be indexed). This may be used to suppress the choice of a particular index, at which point the query planner is likely to choose a different index, often rearranging the joins. So the unary plus does often influence the query plan, but in a rather roundabout way. -- Igor

Re: [sqlite] Transaction question

way. Other connections would only see changes after they've been committed. This is "I" in ACID. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sequential row numbers from query

get it as serial records nos. Please help me that what to do While you iterate over the rows with sqlite3_step (or the equivalent in your language of choice), simply keep incrementing a counter. -- Igor Tandetnik ___ sqlite-users mailing list

Re: [sqlite] Order of columns in group by statement affects query performance

to miss this particular opportunity. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Programming API vs console

he DB file that you open in the console - it should get bumped up every time your program runs. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Programming API vs console

using the console program - record is still not present. What exactly does "let the transaction finish" mean? Do you issue a COMMIT or END statement? What you describe looks very much like the program terminates leaving an uncommitted transaction behind, which is then rolled back

Re: [sqlite] Help speed up query

named DTC, by any chance? Perhaps DTC_Status? If so, then your WHERE clause doesn't mean what you think it means: it's not a filter, but another join. That would explain the query plan. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlit

Re: [sqlite] Help speed up query

Are you sure? The query plan looks like Objects(Object_Type_ID) was added but DTC_Statuses(Object_ID) was not. Igor Tandetnik On 4/15/2013 3:57 PM, ven...@intouchmi.com wrote: DTC_Statuses(Object_ID) and Objects(Object_Type_ID) Vance on Apr 15, 2013, Igor Tandetnik <i...@tandetnik.

Re: [sqlite] Help speed up query

On 4/15/2013 3:45 PM, ven...@intouchmi.com wrote: As a follow up, I added the other two indexes with no real inprovement. Which two indexes? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin

Re: [sqlite] Help speed up query

), and everything else done via SEARCH (which requires a primary key or a suitable index). The only missing link is a way to search DTC_Statuses given an Object_ID. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi

Re: [sqlite] Help speed up query

On 4/15/2013 2:20 PM, ven...@intouchmi.com wrote: here is the output from explain: I asked about EXPLAIN QUERY PLAN, not EXPLAIN. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo

Re: [sqlite] Help speed up query

are backed by indexes. But I'm asking about indexes on fields other than primary key - e.g. DTC_Statuses(Object_ID), not DTC_Statuses(DTC_Status_ID). What good is an index on DTC_Status_ID when no joins mention it? -- Igor Tandetnik ___ sqlite-users mailing

Re: [sqlite] Help speed up query

, probably to a lesser degree. In sqlite3 command line console, run your query with EXPLAIN QUERY PLAN prepended. Show the output of that. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listi

Re: [sqlite] Getting row count for all the tables with a single SQL execute command

for other tables, if that's what you are asking. The only way to know how many rows a table contains is to count them. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When is column type valid?

, and the pointer will become invalid, when you call sqlite3_finalize -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When is column type valid?

any other sqlite3_column_* calls. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When is column type valid?

lly return the string "foo bar". The only way the type is used is to determine column affinity, as documented at http://www.sqlite.org/datatype3.html . Both VARIANT and "foo bar" would result in NUMERIC affinity. -- Igor Tandetnik ___

Re: [sqlite] When is column type valid?

ual type of the value (e.g. using sqlite_column_text on an integer value to retrieve the textual representation of the integer). -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When is column type valid?

On 4/9/2013 9:04 PM, Simon Slavin wrote: I'l try sqlite3_column_dectype tomorrow. Erm ... no such function. Unless you're getting it from somewhere I'm not looking. Can you show a URL ? I'm pretty sure the OP means http://www.sqlite.org/c3ref/column_decltype.html -- Igor Tandetnik

Re: [sqlite] When is column type valid?

in the given column and the *current row*. For it to work, the statement has to actually be positioned over some row. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 64bit compatibility warnings

A smarter compiler could have realized that, and suppressed the warning. This is not technically a bug (defined as non-conformance with the standard), but a quality-of-implementation issue. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@s

Re: [sqlite] 64bit compatibility warnings

"int" is 32 bits or larger. By definition, A % B < B. Thus, if B fits into an int (be it 32-bit or 16-bit or otherwise), then A % B would too. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cg

Re: [sqlite] SQLite 3.7.17 preview - 2x faster?

ng" "memory-mapped"... on google, he or she would find a forum topic "Memory mapped file performance" where the first contributor to answers is Igor Tandetnik and we knows that Igor gave very valuable answers also on this list, so he (having good knowledge about both win32 an

Re: [sqlite] SQLite 3.7.17 preview - 2x faster?

ng" "memory-mapped"... on google, he or she would find a forum topic "Memory mapped file performance" where the first contributor to answers is Igor Tandetnik and we knows that Igor gave very valuable answers also on this list, so he (having good knowledge about both win32 an

Re: [sqlite] [Question] How can I recognize arguments are dynamic binding values in user defined function?

rally, a statement with syntax errors cannot be executed, so your function wouldn't be called in the first place; of course, if it's not called, it can't issue any warnings. If your function is running, this means it's been given a valid string, whether as a string lit

Re: [sqlite] How do I write a query

On 3/25/2013 3:59 PM, Igor Korot wrote: What do you mean by "put ORDER BY clause in the index"? Could you clarify? For example, if you often need to run a query like "select * from T where A = ? order by B;", it would benefit from an index on T(A, B)

Re: [sqlite] [EDIT]What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON CONFLICT IGNORE?

there are two (redundant) indexes on the same column that need to be updated - one explicitly created, and another implicit in UNIQUE constraint. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin

Re: [sqlite] What is the reason for SQLITE UPSERT performance improvment with UNIQUE ON CONFLICT IGNORE?

faster simply because it makes much fewer writes. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserting a CURRENT_TIMESTAMP value into a BIG INT column seems to work

On 3/19/2013 7:51 AM, Philipp Kursawe wrote: This goes through without an error and the physical db file then really contains the current timestamp as a string. How can that be? Is the column internally converted on the fly? http://sqlite.org/datatype3.html -- Igor Tandetnik

Re: [sqlite] Efficient way to store counters

ing the values meeting the criterion. You seem to expect the optimizer to solve equations - to effectively rewrite the conditions as "B.a = A.a - 1" and "B.a >= 0 and B.a > (case when A.a < 0 then 0 else A.a * A.a end)". I'm pretty sure no major DBMS does that. Def

Re: [sqlite] Query Help

;20907" Why do you expect 'chu' to be accompanied by an ID of 20851, and not 20935? These seem to be equally valid choices? If you want, say, the smallest of the two, just say so: select lower(pattern) as pattern, min(id) as minid from symbols where value1 = ?1 or value2 = ?1 group by

Re: [sqlite] How can I improve this query?

id] etc... select genres, movies from genres g1 where movies in (select g2.movies from genres g2 where g1.genres = g2.genres order by g2.movies limit 3) order by genres; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http

Re: [sqlite] How can I improve this query?

ase movies when 'tt1637725' then movies else '' end) from genres group by genres order by genres; An index on genres(genres) would help. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listi

Re: [sqlite] insert / replace into joined tables.

<>".NO-REG" And (datatemp.newRegistration) Is Not Null)); update aircraft set Registration = coalesce( (select newregistration from datatemp where newModeS = ModeS and newregistration != '.NO-REG' and newRegistration is not null ) , Registration) where Registration = '.N

Re: [sqlite] Memory DB - Query does not return all records after Update

that reproduces the problem? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory DB - Query does not return all records after Update

(or at least reset) the statement first, then commit the transaction. The statement is considered "active" after step until it's reset or finalized. Your END TRANSACTION statement actually fails, leaving the transaction open. -- Igor Tandetnik __

Re: [sqlite] like query

On 2/27/2013 4:35 AM, Dominique Devienne wrote: PS: Something else that should also be part of SQLite built-in is the optimization that col LIKE 'prefix%' queries should implicitly try to use an index on col. http://www.sqlite.org/optoverview.html#like_opt -- Igor Tandetnik

Re: [sqlite] SQLite equivalent to Oracle's MERGE INTO

DUAL as a table with no data, just something to put into a FROM clause to keep the parser happy. In this case, you would be glad to know that SQLite doesn't require FROM clause at all - you can simply write SELECT 'A_NAME' as name, 'A_KEY' as key -- Igor Tandetnik

Re: [sqlite] like query

On 2/26/2013 9:25 AM, dd wrote: Igor/Clemen Ladisch, SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND "somedata/z" I want to replace z with 10 character. But, it's failed. Failed in what way? How do you run your query? Show your cod

Re: [sqlite] like query

On 2/26/2013 9:18 AM, dd wrote: 10 decimal value is 1114111. But, some chinese characters are greater than this value. You are mistaken. There are no Unicode characters above U+10, whether Chinese or otherwise. -- Igor Tandetnik ___ sqlite

Re: [sqlite] like query

On 2/26/2013 8:31 AM, Clemens Ladisch wrote: Igor Tandetnik wrote:> On 2/26/2013 2:39 AM, dd wrote: SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND "somedata/zzz" This database has unicode strings(chinese/japanese/...etc strings). can you tell me which i

Re: [sqlite] sqlite3 last insert rowid

sqlite3_last_insert_rowid is needed is that SQLite may automatically assign ROWID for newly inserted rows, and the function reports this automatic value. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman

Re: [sqlite] like query

On 2/26/2013 2:39 AM, dd wrote: >>SELECT * FROM emp WHERE column_test BETWEEN "somedata/" AND "somedata/zzz" This database has unicode strings(chinese/japanese/...etc strings). can you tell me which is the correct character to replace with z? U+, of

Re: [sqlite] SQLite equivalent to Oracle's MERGE INTO

on update path, then it becomes a bit trickier: insert or replace into x(name, key, otherField) select name, key, otherField from y left join x on (y.key = x.key); If x.key is not unique, then there's nothing better in SQLite than running INSERT and UPDATE statements separately. -- Igor Tandetnik

Re: [sqlite] minor typos in lang_corefunc.html's description of abs(X). domain of abs(X)

kes a sequence of characters "-9223372036854775809" and interprets it as a numeric literal representing a floating point number -9.22337203685478e+18 (note loss of precision). -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sq

Re: [sqlite] Faster query result

of the dates. Thanks. Why don't you test both and measure the difference? For what it's worth, my bet would be on #1. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] got selected items ok but can't update using them

On 2/20/2013 8:46 AM, e-mail mgbg25171 wrote: but how do I fill a table in a :memory: db with the contents of another in a real database? http://sqlite.org/lang_attach.html -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] got selected items ok but can't update using them

resemble a syntactically valid UPDATE statement. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] got selected items ok but can't update using them

o, std_itms.itm n from alias_itms join stmnts on (alias_id = stmnts.ID) join std_itms on (std_id = std_itms.ID); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help with select

om MyTable where f1 = '' and f2 in (select f3 from MyTable where f1 != ''); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get the RecNo ???

it. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get the RecNo ???

On 2/11/2013 6:51 AM, Simon Slavin wrote: On 11 Feb 2013, at 3:45am, Igor Tandetnik <i...@tandetnik.org> wrote: On 2/10/2013 10:06 PM, Mohit Sindhwani wrote: * You decide then to do a sort by ROWID ASC - expecting that ROWID is maintaining the sequence since when you do a general

Re: [sqlite] search string in the db question

use star like this. You have to spell out your condition in full: select * from mytable where c5='9806067880' or c6='9806067880' or c7='9806067880'; Or a bit more compactly: select * from mytable where '9806067880' in (c5, c6, c7); -- Igor Tandetnik

Re: [sqlite] Deletion slow?

rds does sound excessive (for a regular PC with database file stored on a hard drive), even considering that three indexes need to be updated. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mail

Re: [sqlite] Deletion slow?

On 2/5/2013 8:35 AM, John Drescher wrote: Put the delete in a transaction. It's a single statement, it runs in a single implicit transaction. An explicit transaction shouldn't make any difference. -- Igor Tandetnik ___ sqlite-users mailing list

Re: [sqlite] update to limits infomation

what point you are trying to make here. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] update to limits infomation

On 2/4/2013 8:22 PM, YAN HONG YE wrote: I hava a table like this: id,name,score,rank 1,anna,80,0 2,qera,65,0 6,kero,90,0 10,rosa,95,0 what I would like to do is to update the rank position. update mytable set rank = (select count(*) from mytable t2 where t2.score <= mytable.score); -- I

Re: [sqlite] sorting two distinct groups

what is the meaning of calls.last? Are you updating this field for all calls for a firm whenever a new call is inserted? Perhaps you are looking for something like this: select f.id from firms f left join calls c on (f.id = c.firm_id) group by f.id order by min(c.last)

<    2   3   4   5   6   7   8   9   10   11   >