Re: [sqlite] Using with clause in update statement

x wrote: > If I replace the following statement > > UPDATE SomeTable SET SomeCol = Col1 * (LongWindedFormala) + Col2 * > (LongWindedFormala) + ... > > with > > WITH CTE(X) AS (SELECT LongWindedFormula) > UPDATE SomeTable SET SomeCol = Col1 * (SELECT X FROM CTE) + Col2 * (SELECT X > FROM

Re: [sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

Ziemowit Laski wrote: > Visual C++ Which one? > correctly catches this. Oh? What exactly is illegal about this? > struct IcuScalar { > const char *zName;/* Function name */ > int nArg; /* Number of arguments */ > int enc;

[sqlite] BUG: Illegal initialization in icu.c : sqlite3IcuInit

Visual C++ correctly catches this. The fragment struct IcuScalar { const char *zName;/* Function name */ int nArg; /* Number of arguments */ int enc; /* Optimal text encoding */ void

[sqlite] Using with clause in update statement

If I replace the following statement UPDATE SomeTable SET SomeCol = Col1 * (LongWindedFormala) + Col2 * (LongWindedFormala) + ... with WITH CTE(X) AS (SELECT LongWindedFormula) UPDATE SomeTable SET SomeCol = Col1 * (SELECT X FROM CTE) + Col2 * (SELECT X FROM CTE) + . Is ‘(SELECT X

Re: [sqlite] Creating indexes on non-existent columns with double quotes

On Wed, Jan 25, 2017 at 9:55 AM, Simon Slavin wrote: > > On 25 Jan 2017, at 5:45pm, Ersin Akinci wrote: > >> Thanks Richard and Simon for your insights. I think I'm still missing >> a few things, though. >> >> 1. What does it mean when SQLite tries

Re: [sqlite] Creating indexes on non-existent columns with double quotes

On 25 Jan 2017, at 5:45pm, Ersin Akinci wrote: > Thanks Richard and Simon for your insights. I think I'm still missing > a few things, though. > > 1. What does it mean when SQLite tries to create an index on a string? > Simon suggested that it's creating a calculated

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

On 1/25/17, Warren Young wrote: > > stCmdString += " AND ‘%?1%’ LIKE ‘%?2%’”; > > Then use the sqlite3_bind_*() calls to insert parameters 1 and 2 into the > string. Not quite. You cannot have parameters embedded in the middle of strings. The whole string is replaced by a

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

On Jan 25, 2017, at 8:50 AM, Clyde Eisenbeis wrote: > > Are there code examples similar to the following (OLE DB)? Code examples? Maybe, but the second link I gave you is pretty clear. The bits you want are even in bold text. > stCmdString += " AND " + stLikeFieldName + "

Re: [sqlite] Creating indexes on non-existent columns with double quotes

Thanks Richard and Simon for your insights. I think I'm still missing a few things, though. 1. What does it mean when SQLite tries to create an index on a string? Simon suggested that it's creating a calculated index, but I'm not sure what means. (Does it just mean an "index" literally just on

Re: [sqlite] Finding the largest TOTAL() of numerous GROUP totals

On 2017/01/25 6:15 PM, Simon Slavin wrote: On 25 Jan 2017, at 4:14pm, R Smith wrote: Maybe I'm misunderstanding some fundamental principle, but I don't see how TOTAL(count) is equivalent to TOTAL(*)? COUNT(some_field_name) and COUNT(*) might be equivalent, but I'm

Re: [sqlite] application function value in sql where

On 2017/01/25 3:18 PM, Dominique Devienne wrote: On Wed, Jan 25, 2017 at 1:54 PM, Richard Hipp wrote: On 1/25/17, Richard Hipp wrote: On 1/25/17, dspub...@freemail.hu wrote: I get weird sql result with subselect too select * from

Re: [sqlite] Finding the largest TOTAL() of numerous GROUP totals

On 25 Jan 2017, at 4:14pm, R Smith wrote: > Maybe I'm misunderstanding some fundamental principle, but I don't see how > TOTAL(count) is equivalent to TOTAL(*)? COUNT(some_field_name) and COUNT(*) > might be equivalent, but I'm wondering how total would know which column

Re: [sqlite] Finding the largest TOTAL() of numerous GROUP totals

On 2017/01/25 1:36 PM, Simon Slavin wrote: On 25 Jan 2017, at 5:13am, Jeffrey Mattox wrote: (1) SELECT TOTAL(count) as grandTotalCount FROM History Please try replacing TOTAL(count) with TOTAL(*). Nothing to do with your question but it may lead to faster results with

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

I should clarify ... I am currently using: string stCmdString = "SELECT" + stFieldNames + "FROM " + stTableName + " WHERE " + stLikeFieldName + " LIKE '%" + liststLikeFieldValue[0] + "%'"; for (int iii = 1; iii < liststLikeFieldValue.Count; iii++) stCmdString += " AND " +

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

Thanks for the prompt response! Are there code examples similar to the following (OLE DB)? oledbCmd.CommandText = "SELECT" + stFieldNames + "FROM " + stTableName + " WHERE " + stLikeFieldName + " LIKE @p0"; for (int iii = 1; iii < liststLikeFieldValue.Count; iii++)

Re: [sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

On Jan 25, 2017, at 8:33 AM, Clyde Eisenbeis wrote: > > The use of .Parameters in OLE DB fixes this problem. Is there an > equivalent for SQLite? You’re looking for prepared statements with parameters: https://sqlite.org/c3ref/stmt.html

[sqlite] Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters?

Using SQLite, how can I search for chars that include a ', similar to OLE DB .Parameters? This works to find bruce and baseball: string stCmdString = "SELECT fstInfo FROM PadTable WHERE fstInfo LIKE '%bruce%' AND fstInfo LIKE '%baseball%'" Not sure how to find bruce's and baseball. I know

Re: [sqlite] _SQLITEINT_H_ vs SQLITEINT_H

On 25 Jan 2017, at 3:14pm, Clyde Eisenbeis wrote: > It's not clear how I post questions. Do I just send to this mailing list? Although some questions get very fast replies, it is just a coincidence that one of the development team happened to be reading the list when the

Re: [sqlite] _SQLITEINT_H_ vs SQLITEINT_H

It's not clear how I post questions. Do I just send to this mailing list? On Wed, Jan 25, 2017 at 6:59 AM, Theo Veenker wrote: > Hi all, > > Just downloaded the 3.16.2 amalgamation (used 3.11.1 before). I then wanted > to apply the userauth extension. The resulting

Re: [sqlite] application function value in sql where

On Wed, Jan 25, 2017 at 1:54 PM, Richard Hipp wrote: > On 1/25/17, Richard Hipp wrote: > > On 1/25/17, dspub...@freemail.hu wrote: > >> > >> I get weird sql result with subselect too > >> select * from (select row_number(name) as id,name

Re: [sqlite] Matching wildcards in Lemon

> On 24 Jan 2017, at 14:47, Richard Hipp wrote: > > On 1/24/17, Ben wrote: >> >> Just for a heads up, this isn't documented at: >> http://www.hwaci.com/sw/lemon/lemon.html >> >> > > Documentation patches

[sqlite] _SQLITEINT_H_ vs SQLITEINT_H

Hi all, Just downloaded the 3.16.2 amalgamation (used 3.11.1 before). I then wanted to apply the userauth extension. The resulting sqlite3.c doesn't compile however because userauth.c contains this fragment: #ifndef _SQLITEINT_H_ # include "sqliteInt.h" #endif Apparently

Re: [sqlite] application function value in sql where

On 1/25/17, Richard Hipp wrote: > On 1/25/17, dspub...@freemail.hu wrote: >> >> I get weird sql result with subselect too >> select * from (select row_number(name) as id,name from example ) t where >> id<=5 >> > > SQLite is invoking your row_number()

[sqlite] Possible bug when creating a calculated index

Bug is as follows: SQLite version 3.14.0 2016-07-26 15:17:14 Enter ".help" for usage hints. sqlite> CREATE TABLE reports (a INT, b TEXT); sqlite> CREATE INDEX index_reports_quotes ON reports ("yearz_doesnt_exist"); sqlite> PRAGMA index_xinfo('index_reports_quotes'); 0|-2||0|BINARY|1

Re: [sqlite] application function value in sql where

On 1/25/17, dspub...@freemail.hu wrote: > > I get weird sql result with subselect too > select * from (select row_number(name) as id,name from example ) t where > id<=5 > SQLite is invoking your row_number() function twice for each row - once for the return value and a

Re: [sqlite] Creating indexes on non-existent columns with double quotes

On 23 Jan 2017, at 9:33pm, Ersin Akinci wrote: > CREATE INDEX index_reports_on_yearz_doesnt_exist ON reports > (yearz_doesnt_exist); > CREATE INDEX index_reports_on_yearz_doesnt_exist ON reports > ('yearz_doesnt_exist'); > CREATE INDEX index_reports_on_yearz_doesnt_exist

Re: [sqlite] Finding the largest TOTAL() of numerous GROUP totals

On 25 Jan 2017, at 5:13am, Jeffrey Mattox wrote: > (1) SELECT TOTAL(count) as grandTotalCount FROM History Please try replacing TOTAL(count) with TOTAL(*). Nothing to do with your question but it may lead to faster results with less processing. Simon.

Re: [sqlite] Creating indexes on non-existent columns with double quotes

On 1/23/17, Ersin Akinci wrote: > CREATE INDEX index_reports_on_yearz_doesnt_exist ON reports > ("yearz_doesnt_exist"); > > > I understand that the double quotation syntax is used to indicate > identifiers. Why am I allowed to create an index on a non-existent column >

[sqlite] application function value in sql where

Hi Everyone, I would like to report a probably bug, what I found in sqlite (3.16.2) I want to make and use some application defined function (ADF). If I use ADF value in sql where expression, then the server result is incorrect. example data:

[sqlite] SQLITE Massif diagram

Hi all, I wrote a simple app that is use sqlite 3.8.2 as below: // // Name: test.cpp // Author : Ali // Version : // Copyright : GPLv3 // Description : Sqlite Test // SQLITE Version : 3.8.2

[sqlite] Creating indexes on non-existent columns with double quotes

I'm trying to track down a behavior in SQLite that I don't fully understand and was hoping to get some help with. Here are three CREATE INDEX statements for a table called reports that does NOT have a column called yearz_doesnt_exist: CREATE INDEX index_reports_on_yearz_doesnt_exist ON reports

Re: [sqlite] How to circumvent UNIQUE constraint

2017-01-23 23:20 GMT+01:00 Ben Newberg : > what i've done in the past is append a character to the value and make use > of "cast": > > ​​ > update desktops set indexNo = indexNo || '_'; > update desktops set indexNo = cast(indexNo as integer) + 1; > > then: > insert into