Re: [sqlite] SQL join syntax

inner join, a condition has the same effect either way, whether it appears in ON or WHERE clause. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error handling

On 2/15/2017 10:42 PM, Igor Korot wrote: Now I presume that calling sqlite3_finalize() on the NULL handle is safe? Yes; the documentation explicitly states that. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Error handling

tory, it may return an error code from the most recent failed sqlite3_step() call (see Goofy Interface Alert section at https://sqlite.org/c3ref/step.html ). Since you are using sqlite3_prepare_v2, you may ignore the return value of sqlite3_finalize

Re: [sqlite] sqlite3 hangs on query

plan, it ends up in the innermost loop. SQLite then goes through the full cross join of all the other tables, only to get to the inner loop and discover that there isn't going to be a row after all. "LIMIT 1" doesn't help any as the query is never going to produce a row

Re: [sqlite] sqlite3 hangs on query

ed if that takes an enormous amount of time. You have a cross-join of 19 tables. Even if each one contains just 2 rows, that's 2^19 ~ 500K rows to work through. And it grows exponentially from there. -- Igor Tandetnik ___ sqlite-users mailing li

Re: [sqlite] Can stmt SQL be changed?

andom garbage it contains just accidentally happens to match a valid statement handle. That kind of heisenbug would be a doozy to debug. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cg

Re: [sqlite] Can stmt SQL be changed?

finalize the first statement before reusing the variable for the second one. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_errmsg() after sqlitr3_exec() on ATTACHed DB

e you get around to sqlite3_errmsg(). Which is why sqlite3_exec() makes the effort to preserve the message and forward it to you, should you choose to accept it. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlit

Re: [sqlite] SQLite - SELECT query slowing down while fetching from higher offset.

phonebook, with people's names listed in alphabetic order. This makes it easy to find an entry for a particular name, but doesn't help at all with finding an entry number N; for that, you still have to start from the first entry, and count them one by one. -- Igor

Re: [sqlite] Problem with sqlite3_bind_int

? sqlite3_bind_int numbers parameters starting from 1. What is this btLBAdd that you have in one place but not the other? Also, as a sanity check, print CType() of that element and, if it's a string, the length of that string. -- Igor Tandetnik ___ s

Re: [sqlite] Problem with sqlite3_bind_int

, and perhaps CLng() returns -1 to indicate failure. Another possibility is that the variant contains boolean True value; VB6 represents it numerically as -1 -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http

Re: [sqlite] Weird chars inserted

vant here: sqlite3_exec still expects a UTF-8 encoded string. There are SQLite API functions that accept UTF-16 strings (e.g. sqlite3_bind_text16), but sqlite3_exec is not one of them. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.s

Re: [sqlite] Weird chars inserted

On 12/17/2016 8:38 PM, Ariel M. Martin wrote: Note: szSQL is the wchar-string my app uses char szAux[2048]; ZeroMemory(szAux, 2048); WideCharToMultiByte(CP_ACP, WC_COMPOSITECHECK, szSQL, wcslen(szSQL), szAux, 2048, NULL, 0); Replace CP_ACP with CP_UTF8. -- Igor

Re: [sqlite] Run non-data producing statement just once to test UDF

this? limit 1 only applies to data producing statements. Adding a where clause, say where rowid = 1 is a bit tricky (all this has to be done in code) and there may not be a rowid of 1 where rowid = (select min(rowid) from table1); -- Igor Tandetnik

Re: [sqlite] like operator

While SQLite only folds ASCII letters by default, it could be compiled with full ICU collation support. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] like operator

On 12/5/2016 10:19 AM, Igor Tandetnik wrote: On 12/5/2016 7:30 AM, ravi.shan...@cellworksgroup.com wrote: select name from employee table where name like '%Araya%' or name like '%Amul%' or name like '%Aj%'; Table - Employee Id | Name | age | 1 | Araya

Re: [sqlite] like operator

Ajay Kumar | 45 | I dont like to use may or conditions for pattern matching using like operator. Is there any other way I can workaround without using or condition in like operator in sqlite. WHERE length(replace(replace(replace(name, 'Araya', ''), 'Amul

Re: [sqlite] Weird issue with the query

On 12/2/2016 8:39 PM, Igor Tandetnik wrote: On 12/2/2016 6:56 PM, Igor Korot wrote: res = sqlite3_bind_text( stmt, 1, sqlite_pimpl->m_myconv.to_bytes( tableName.c_str() ).c_str(), -1, SQLITE_STATIC ); SQLITE_STATIC tells SQLite that the string will outlive the statement handle. But

Re: [sqlite] Weird issue with the query

fer, deallocated at the end of the statement. Your program exhibits undefined behavior. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Scope of sqlite3_update_hook?

n other OS processes? (I'm looking for a way to detect this.) There's nothing in SQLite that would help with that, to my knowledge. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite

Re: [sqlite] A total with a GROUP BY

the Count's.) SELECT totalUsed, COUNT(*) AS Count FROM tips GROUP BY totalUsed union all SELECT null, COUNT(*) FROM tips; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/ma

Re: [sqlite] Datatype for prices (1,500)

On 11/30/2016 9:42 AM, Werner Kleiner wrote: It would be nice if there is a way to store in the same way as in MySQL with filling zeros. Is this not possible with sqlite? Store it as an integer, in $.0001 units. So $1.500 would be represented simply as an integer 1500. -- Igor Tandetnik

Re: [sqlite] trimming with tab and other special characters

On 11/29/2016 4:00 AM, Max Vlasov wrote: I wonder why OP and other authors of the discussion https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg49355.html was so sure about backslash escaping support, even Igor Tandetnik :) I said, and I quote: """ If yo

Re: [sqlite] How to get row number of an ID in sorted results

hrough the resultset. For example, imagine that, in the limit, all rows have the same value in Value2. If you only do "order by Value2", then the resulting order will be completely indeterminate; there's no telling where a row with a given ID would

Re: [sqlite] How to get row number of an ID in sorted results

o "order by Value2, ID" in your temporary table approach. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get row number of an ID in sorted results

elect ID from Test order by Value2; select rowid from TempIDs where ID = 1; drop Table TempIDs; select count(*) from Test where Value2 <= (select t2.Value2 from Test t2 where t2.ID=1); -- Igor Tandetnik ___ sqlite-users mailing list sqli

Re: [sqlite] Bug: CAST string timestamp to TIMESTAMP truncates the value

CAST(expr AS name) determines affinity from `name`, then coerces the value of `expr` according to that affinity. The default affinity, when no specific rules apply, is NUMERIC. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.s

Re: [sqlite] How to get these ordered correctly

words (id INTEGER PRIMARY KEY AUTOINCREMENT ,cnt INTEGER DEFAULT 1 ,rvw INTEGER DEFAULT 0 ,txt TEXT UNIQUE); sqlite> select txt, cnt, rvw from words where txt!="" and rvw!=0 order by cnt desc; Just do " order by cnt desc, txt &

Re: [sqlite] Pivot & concat SELECT?

On 10/17/2016 10:32 AM, Daniel Polski wrote: Den 2016-10-17 kl. 16:03, skrev Igor Tandetnik: select unit, sum(1 << bit_position) from table1 where val group by unit; Wow!! Thanks alot! Alot accepts expressions of gratitude but wonders why they are being addressed to it:

Re: [sqlite] Pivot & concat SELECT?

in the above table). How can I get the byte data in a single select instead of parsing through the individual bits one by one? select unit, sum(1 << bit_position) from table1 where val group by unit; -- Igor Tandetnik ___ sqlite-

Re: [sqlite] Is there a best practice for breaking up a large update?

mode: https://www.sqlite.org/wal.html . It allows a writing transaction to co-exist with multiple readers. See if this helps. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/ma

Re: [sqlite] Inserting a new record (anywhere) into a table of ordered records that have an integer auto-increment primary key

r you haven't told us about that suggests the new record should go between 2 and 3? If so, make that indicator part of the row data, and use it in your ordering to break ties between timestamps. It makes no sense to use RowId for that. -- Igor

Re: [sqlite] display also the average score of a flim

it's possible to also display the average score of a film. Have you tried the obvious? SELECT films.*, AVG(films_genres.score) ... -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.o

Re: [sqlite] sqlite-users Digest, Vol 105, Issue 13

e or the most recent sqlite3_reset; and ends with sqlite3_reset or sqlite3_finalize. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] When is data committed on one connection seen on another?

time T+10 and ends it at T+30. Both reads are part of the same unbroken transaction lasting (at least) from T to T+30. If there's an update committed at T+5 on a different connection, neither read would see it, even though thread B started after it. --

Re: [sqlite] Clarification on "No Isolation On Same Db Connection"

o interleave SELECT and updates on the same connection. This is the current situation that I need to correct. My SQLite is old enough that it doesn't prevent committing in this case Check for errors. I predict your COMMIT statement fails. -- Igor Tandetnik

Re: [sqlite] Clarification on "No Isolation On Same Db Connection"

ed under SELECT's feet, phantom rows are just one problem; it's also possible for the statement to skip rows it would have otherwise returned, and to return rows containing stale data. Basically, undefined behavior is undefined. -- Igor Tandetnik ___

Re: [sqlite] Multi-column indexing

on Y alone (it doesn't matter which of the two is X and which is Y in this case). The (X, Y) index also works for searching on X, but not for searching on Y. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org htt

Re: [sqlite] get field precision

On 7/20/2016 6:40 AM, Leonardo Massei wrote: I've a simple question: I need to get the precision (or scale) of a table's field SQLite doesn't have a concept of field precision or scale, so there's nothing to get. See also: http://www.sqlite.org/datatype3.htm

Re: [sqlite] Menory leak/false positive

On 7/13/2016 8:12 PM, Igor Korot wrote: char *z = sqlite3_mprintf( query2.c_str(), tableName ); There is a bunch of calls to sqlite3_mprintf, but nary a call to sqlite3_free. -- Igor Tandetnik ___ sqlite-users mailing list

Re: [sqlite] Readers Sharing Connection

? Yes in journal mode, no in WAL mode. I'm using PRAGMA read_uncommitted = true This is only meaningful when using shared cache mode ( https://www.sqlite.org/sharedcache.html ). It does nothing otherwise. -- Igor Tandetnik ___ sqlite-users mailing

Re: [sqlite] Order of applying subquery in SELECT statement

precludes NULLs and is sure to pick the -- | - --- correct max when joined to b too. -- 1 | ORD005 -- 2 | ORD009 And if you do need NULLs, then just change it to "LEFT JOIN b". -- Igor Tandetnik

Re: [sqlite] Order of applying subquery in SELECT statement

, get max(b.orderno) across the whole group, only across some random item in it. If you want max(orderno) across the group, just join with b: SELECT a.id, max(b.orderno) as maxorderno FROM a INNER JOIN c on c.a_id=a.id JOIN b ON b.c_id=c.id GROUP BY a.id -- Igor Tand

Re: [sqlite] sqlite3_column_origin_name for INSERT and UPDATE

assume a one-to-one correspondence between columns and placeholders. This assumption doesn't generally hold. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Correct, best, or generally accepted database structure for groups of things

ples.Weight may store 79 or 81 for an apple belonging to such a group. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Latin-1 characters cannot be supported for Unicode

http://www.joelonsoftware.com/articles/Unicode.html . -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Latin-1 characters cannot be supported for Unicode

in fact a valid byte sequence in any Unicode encoding - neither UTF-8 nor UTF-16 nor any other. If you want Unicode data in your database, then store Unicode data, and not ANSI, in your database. -- Igor Tandetnik ___ sqlite-users mailing list sqlite

Re: [sqlite] Limit to 5 records per Group / Top N results per group

one query. Something along these lines, perhaps: select ProjectName, KeyDate from Projects p, KeyDates using (ProjectID) where KeyDate in ( select d2.KeyDate from KeyDates d2 where d2.ProjectId = p.ProjectId order by d2.KeyDate limit 5); -- Igor Tandetnik

Re: [sqlite] Fwd: Messages posted on Nabble not getting to list

them, and their support fixed it. Like you, I'm not an admin or anything, just a regular lurker. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT ... FOR UPDATE

On 5/24/2016 1:34 PM, Scott Doctor wrote: SELECT field FROM table FOR UPDATE; My question is, looking through the sqlite documentation, it is not clear how sqlite will handle that statement. SQLite will report a syntax error. That's pretty easy to confirm experimentally. -- Igor Tand

[sqlite] SQL Help: Finding case insensitive matches

On 5/13/2016 11:51 AM, Dominique Devienne wrote: > But it still returns both rows. Any idea on that part? Change "u1.user <> u2.user" to "u1.user < u2.user" -- Igor Tandetnik

[sqlite] obtaining bracketing rows where rows are missing

high from Brackets where high > low + 1; -- Igor Tandetnik

[sqlite] Why SQLite allows to create table with unknown types?

way? It should not make any difference. -- Igor Tandetnik

[sqlite] Index Selection

insensitive index cannot be used to satisfy it. If you expect SQLite to inspect the string literal character by character and prove that case sensitivity won't make a difference, then I'm afraid you expect too much. -- Igor Tandetnik

[sqlite] Article about pointer abuse in SQLite

de heap manager further sub-allocating those pages. The garbage you see in uninitialized heap allocations came from your own process; you don't get to observe random data from other processes this way. -- Igor Tandetnik

[sqlite] Article about pointer abuse in SQLite

tps://en.wikipedia.org/wiki/Race_condition . In any case, "undefined behavior" is not at all the same thing as "non-deterministic behavior". A conforming C (or C++) program does not (by definition of "conforming") exhibit undefined behavior, but may very well be non-deterministic. -- Igor Tandetnik

[sqlite] Correlated subquery refers to wrong rowid - bug?

> operators or functions in a where clause. Things that appear in the list after SELECT are also expressions, no different from those that appear in WHERE clause or elsewhere. -- Igor Tandetnik

[sqlite] Correlated subquery refers to wrong rowid - bug?

On 3/9/2016 9:58 AM, R Smith wrote: > On 2016/03/09 4:35 PM, Igor Tandetnik wrote: >> Yes, but why is that a problem? It is perfectly legal, and often >> useful, for a subquery to refer to columns from enclosing query. >> That's what makes it a *correlated* subquery. >

[sqlite] Correlated subquery refers to wrong rowid - bug?

and often useful, for a subquery to refer to columns from enclosing query. That's what makes it a *correlated* subquery. -- Igor Tandetnik

[sqlite] CTE for a noob

l and DateViewed is null) Unwatched, count(VideoID) Videos from Projects left join vViewedVideos using (ProjectID) group by ProjectID; -- Igor Tandetnik

[sqlite] obtainingthe number of rows returned by a query (with a limit)

row. If you want to count the number of rows but stop at, say, 100, then you can transform your original statement like this: select count(*) from ( select original ... LIMIT 100 ); So, you append the LIMIT clause to the original query, then wrap the whole thing in another query that does the counting. -- Igor Tandetnik

[sqlite] Mixing text and numeric values in comparisons

ve it one: SELECT * FROM test WHERE A = CAST(1.0 as REAL); -- returns 1. See also: https://www.sqlite.org/datatype3.html#compaff -- Igor Tandetnik

[sqlite] obtainingthe number of rows returned by a query (with a limit)

number of rows that the inner query would have returned. Is this not what you want? Of course, it would take approximately as much time to run this new query as it would the original query. -- Igor Tandetnik

[sqlite] Mixing text and numeric values in comparisons

On 3/4/2016 9:29 AM, Paul van Helden wrote: > On Fri, Mar 4, 2016 at 4:23 PM, Igor Tandetnik wrote: > >> On 3/4/2016 9:15 AM, Paul van Helden wrote: >> >>> So I have to detect integers in order to avoid the .0 >>> >> >> WHERE CAST(A as integer) = 1

[sqlite] Mixing text and numeric values in comparisons

On 3/4/2016 9:15 AM, Paul van Helden wrote: > So I have to detect integers in order to avoid the .0 WHERE CAST(A as integer) = 1 -- Igor Tandetnik

[sqlite] Why is a separate journal file needed ?

On 2/28/2016 9:19 PM, Rowan Worth wrote: > On 27 February 2016 at 00:02, Igor Tandetnik wrote: > >> On 2/26/2016 4:01 AM, Rowan Worth wrote: >> >>> In principle this is correct, but actually the database *file* is not >>> immediately modified in rollback mode.

[sqlite] Can SQLite be used from DLL

uble. There is a very narrow band of build configuration parameters in which this would work. -- Igor Tandetnik

[sqlite] Why is a separate journal file needed ?

On 2/26/2016 4:01 AM, Rowan Worth wrote: > On 24 February 2016 at 23:46, Igor Tandetnik wrote: > >> On 2/24/2016 10:08 AM, Stephen Chrzanowski wrote: >> >>> IMO, all that, plus the fact that you have an easy roll back mechanism. >>> Anything that needs to be pu

[sqlite] ABI report

On 2/25/2016 7:28 PM, Stephen Chrzanowski wrote: > What does ABI stand for? https://en.wikipedia.org/wiki/Application_binary_interface -- Igor Tandetnik

[sqlite] Why skip invoking busy handler while pBt->inTransaction!=TRANS_NONE

s "deadlock". -- Igor Tandetnik

[sqlite] Why is a separate journal file needed ?

goes out in the middle of this? Note also that a typical application commits much more often than it rolls back, so the system should be designed to make the former fast; the latter can be slow. -- Igor Tandetnik

[sqlite] WAL checkpoint

On 2/24/2016 12:56 AM, Sairam Gaddam wrote: > Before checkpointing the data from WAL, if the DB is queried, will the > result include updated data from WAL or not? It will. A transaction reads both from WAL and the original database file - whichever contains the fresher data. -- Igor Tandetnik

[sqlite] User-defined SQL functions

andle; the time_t variable was also part of that class). -- Igor Tandetnik

[sqlite] Process duplicate field values

ges() API function. It tells you how many rows where touched by the most recent data modification statement. So you can call it right after performing the UPDATE. -- Igor Tandetnik

[sqlite] Process duplicate field values

to 9. Keep all other > records intact. update MyTable set F2=9 where F1 in (select t.F1 from MyTable t group by t.F1 having count(*) > 1); > Can task 2 and 3 be implemented in one SQL query No. One is a "get", the other is a "set". A single SQL query can't do both. Did you mean tasks 1 and 2, perhaps? -- Igor Tandetnik

[sqlite] MIN/MAX query

t scan the table with "select * from MyTable order by I, L" and compute the runs in one pass, in linear time. The query above runs in O(N^3) time (though an index on (I, L) might improve things) - only really suitable as a toy example. -- Igor Tandetnik

[sqlite] Get count of unique values?

On 2/18/2016 12:42 AM, admin at shuling.net wrote: > Then I want to obtain the total count of unique values for F1 field. select count(distinct F1) from MyTable; -- Igor Tandetnik

[sqlite] Windows A and W APIs dual support

On 2/12/2016 10:44 PM, J Decker wrote: > On Fri, Feb 12, 2016 at 7:37 PM, Igor Tandetnik wrote: >> It performs the conversion it is documented to perform. It indeed doesn't >> perform the conversion that you, for reasons unclear, expect it to perform. >> In other wo

[sqlite] Windows A and W APIs dual support

y :) It performs the conversion it is documented to perform. It indeed doesn't perform the conversion that you, for reasons unclear, expect it to perform. In other words, you engage in wishful thinking, and then blame the messenger for failure of your wishes to materialize. -- Igor Tandetnik

[sqlite] Windows A and W APIs dual support

. In any case, MultiByteToWideChar and WideCharToMultiByte are perfectly capable of converting between UTF-8 and UTF-16. -- Igor Tandetnik

[sqlite] Is the first column of a composite primary key, special?

n your example, it would help with grouping (or sorting, or filtering) on (a), or (a, b), or (a, b, c). -- Igor Tandetnik

[sqlite] Best way to store only date

similar, but MMDD representation needs to be parsed and converted first; simply subtracting two integers like this produces meaningless result. -- Igor Tandetnik

[sqlite] Best way to store only date

On 1/30/2016 8:22 AM, E.Pasma wrote: > With respect to Igor's suggestion, mmdd (as integer), why not leave out > the century? I prefer the oldfashoned yymmdd. I too like to live dangerously. -- Igor Tandetnik

[sqlite] Best way to store only date

'now') as int) - in other words, storing calendar dates as integers like 20160129. -- Igor Tandetnik

[sqlite] `UNIQUE` constraint allows multiple NULL: is this expected?

;m reasonably, but not 100%, sure this is standard-conforming. -- Igor Tandetnik

[sqlite] hard links and SQLite

On 1/11/2016 11:52 PM, Scott Doctor wrote: > TOCTTOU? What is that? A friendly local search engine suggests it means "time of check to time of use" ( https://en.wikipedia.org/wiki/Time_of_check_to_time_of_use ) -- Igor Tandetnik

[sqlite] batch or one by one?

r code. -- Igor Tandetnik

[sqlite] batch or one by one?

gether. If you look at the implementation of sqlite3_get_table, it works by calling _prepare and _step and so on. It's not some kind of alternative interface to SQLite, merely a wrapper. -- Igor Tandetnik

[sqlite] sqlite3_free needed when calling sqlite3_result_text ?

tion, so that SQLite knows that the string is in fact Unicode. -- Igor Tandetnik

[sqlite] sqlite3_free needed when calling sqlite3_result_text ?

ection (SQLite to VB), but not in the other, as far as I can tell. I suspect you need sqlite3_result_text16 instead. Also lPos-2 looks wrong. Can't the substring be found at lPos == 1 ? -- Igor Tandetnik

[sqlite] sqlite3_free needed when calling sqlite3_result_text ?

the last parameter - the pointer returned by sqlite3_value_text is only guaranteed to be valid until the custom function returns. > or it could be a pointer to a locally declared variable In this case, you would also use SQLITE_TRANSIENT. -- Igor Tandetnik

[sqlite] sqlite3_free needed when calling sqlite3_result_text ?

On 12/14/2015 2:21 PM, Bart Smissaert wrote: > Not sure if I need to call sqlite3_free after running sqlite3_result_text or > if sqlite3_free should be an argument (last one) in sqlite3_result_text. That depends on how the memory was obtained that the second argument points to. --

[sqlite] Selecting total and not used in one query

(Total - Used) AS Free select count(*) Total, sum(used is not null) Used, sum(used is null) Free, sum(any_boolean_condition) CountSatisfyingCondition from proverbs; -- Igor Tandetnik

[sqlite] maybe bug in regexp and replace with newlines?

nverted to a string). 0x0A is an integer, I suspect it gets converted to the string '10' -- Igor Tandetnik

[sqlite] NOP INSERT still writes to the DB/journal

quent no-op inserts. -- Igor Tandetnik

[sqlite] maybe bug in regexp and replace with newlines?

f REGEXP, not in SELECT clause. The reason it doesn't work is that, with most regular expression engines, unless certain flags are used, "." (period) doesn't match newline characters. -- Igor Tandetnik

[sqlite] drop, create and copy a table

te it started from. So you may as well do nothing at all and achieve the same result. -- Igor Tandetnik

[sqlite] [Sqlite3] segfault in sqlite3_step()

the corrupted data structures when it tries to allocate memory. Heap corruption is nasty this way. -- Igor Tandetnik

[sqlite] regular expression in check constraint?

git:]]+$'); >> >> However, this generates the error: >> >> Error: no such function: regexp > > Maybe this instead: > > SELECT * FROM zip_codes WHERE zip_code NOT GLOB '[^0-9]'; Another variation: WHERE ltrim(zip_code, '0123456789')='' -- Igor Tandetnik

[sqlite] Dont Repeat Yourself (DRY) and SQLite

On 11/24/2015 2:58 PM, Domingo Alvarez Duarte wrote: > Actually we can not use "select" on constraints ! And that's a problem because... ? Your triggers don't refer to any other table either. Color me dense, but I utterly fail to grasp the nature of the difficulty. -- Igor Tandetnik

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