This is technically valid CASE syntax which is why you're not getting an error,
it's just not what you're looking for.
...
CASE
(SELECT c.WYear FROM t2 WHERE pid = a.a)
WHEN c.WYear = 2020 THEN “YES” ELSE “NO” END
) AS DIGITAL
...
What that is saying is take the value you get from this:
(SELECT
Jose Isaias Cabrera, on Monday, January 27, 2020 08:42 AM, wrote...
>
>
> Keith Medcalf, on Monday, January 27, 2020 04:02 AM, wrote...
This is actually what I need:
SELECT a.a,
a.c,
a.e,
b.g,
b.h,
b.i,
coalesce((
SELECT
Keith Medcalf, on Monday, January 27, 2020 04:02 AM, wrote...
>
>
> This version generates the most efficient query plan in 3.31.0 when you
> have indexes on the necessary columns:
>
> CREATE INDEX t0_1 on t0 (a, idate, c); -- c does not have to be in the
> index
> CREATE INDEX t1_1 on t1 (f,
Keith Medcalf, on Monday, January 27, 2020 02:28 AM, wrote...
>
>
> Do you perhaps mean:
>
> SELECT a.a,
> a.c,
> a.e,
> b.g,
> b.h,
> b.i,
> coalesce((
>SELECT 'YES'
> FROM t2
>
Except that should be for the header bytes only. It is somewhat inaccurate
because IEEE doubles may be stored as varints and values 0 and 1 may be stored
as just the header code 8 or 9 without storing the actual varint (if the schema
version is 4 or more, which cannot be read in an extension,
Keith Medcalf, on Sunday, January 26, 2020 11:19 PM, wrote...
>
>
> I get nothing at all except a complaint that the syntax is invalid. In
> particular
>
> (
> CASE
> (
> SELECT WYear FROM t2 WHERE pid = a.a
> )
> WHEN c.WYear = 2020 THEN “YES”
> ELSE “NO” END
> ) AS
Simon Slavin, on Sunday, January 26, 2020 09:59 PM, wrote...
>
> On 27 Jan 2020, at 2:44am, Jose Isaias Cabrera
> wrote:
>
> > CASE
> >(
> > SELECT WYear FROM t2 WHERE pid = a.a
> >)
> >WHEN c.WYear = 2020 THEN “YES”
> >ELSE “NO” END
>
> That's not the structure of a
You are missing
maxsize += _varIntSize_(maxsize)
fort he size varint at the begin oft he header just before the return
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Keith Medcalf
Gesendet: Montag, 27. Januar 2020 12:43
Hi,
I came across a problem during mate test, where fuzzcheck ends with
segfault.
The problem appears to be only on this arches. Other architectures are
working fine.
Build here:
https://koji.fedoraproject.org/koji/taskinfo?taskID=40950404
Log:
./fuzzcheck
Here is a wee bit of C code that you can compile as a plugin that will give you
the row size (well, it may be bigger than the actual record size by a few bytes
but it is pretty close) ...
works properly for utf-16 encoded databases as well.
-//- sqlsize.c -//-
#include
You can certainly get the max and average cell size per page of rows from
dbstat which is the most granular data available I think, as well as the
average and max for all the rows taken together. Assuming that the table is a
"rowid" table, then that is the data for the "leaf" pages only. As
This version generates the most efficient query plan in 3.31.0 when you have
indexes on the necessary columns:
CREATE INDEX t0_1 on t0 (a, idate, c); -- c does not have to be in the index
CREATE INDEX t1_1 on t1 (f, idate);
CREATE INDEX t2_1 on t2 (pid, wyear); -- this could be a without rowid
As previously mentioned, SQLite uses a compressed format to store rows. You
would have to reverse engineer at least the calculation
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Deon Brewis
Gesendet: Samstag, 25. Januar
Do you perhaps mean:
SELECT a.a,
a.c,
a.e,
b.g,
b.h,
b.i,
coalesce((
SELECT 'YES'
FROM t2
WHERE wYear == a.c
AND pid == a.a
), 'NO') AS
I get nothing at all except a complaint that the syntax is invalid. In
particular
(
CASE
(
SELECT WYear FROM t2 WHERE pid = a.a
)
WHEN c.WYear = 2020 THEN “YES”
ELSE “NO” END
) AS DIGITAL
Is not a valid scalar expression. Parsing fails at "WHEN". What exactly
Igor Tandetnik, on Sunday, January 26, 2020 09:57 PM, wrote...
>
> On 1/26/2020 9:44 PM, Jose Isaias Cabrera wrote:
> > CASE
> > (
> >SELECT WYear FROM t2 WHERE pid = a.a
> > )
> > WHEN c.WYear = 2020 THEN “YES”
> > ELSE “NO” END
> > ) AS DIGITAL
>
> This
On 27 Jan 2020, at 2:44am, Jose Isaias Cabrera wrote:
> CASE
>(
> SELECT WYear FROM t2 WHERE pid = a.a
>)
>WHEN c.WYear = 2020 THEN “YES”
>ELSE “NO” END
That's not the structure of a CASE statement.
After CASE comes an expression.
After WHEN comes another expression.
On 1/26/2020 9:44 PM, Jose Isaias Cabrera wrote:
CASE
(
SELECT WYear FROM t2 WHERE pid = a.a
)
WHEN c.WYear = 2020 THEN “YES”
ELSE “NO” END
) AS DIGITAL
This should probably be simply
case c.WYear when 2020 then 'YES' else 'NO' end
or equivalently
case
Greetings!
I am getting the wrong output, and I don't know how to get it to work. Please
take a look at the following (Pardon the lengthy data):
create table t0 (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
insert into t0 (a, b, c, d, e, idate) values ('p001', 1, 2019, 'n', 4,
'2019-02-11');
Is there a missed parsing on the input file? It's likely to be a bare word
situation where an extra delimiter is encountered in the record. It's
probably only on one record in particular, although I don't know if the
error message reads that back.
The shell is sensitive to these, as it's expected
On Sunday, 26 January, 2020 10:29, chiahui chen wrote:
>After creating a table (total 8 columns including 1 generated column), I
>tried to import data from a csv file (each record has values for 7
>columns that match the non-generated column names and data types, no
>headers ).
>The system
On Sun, Jan 26, 2020 at 11:01 AM chiahui chen
wrote:
> Hi,
>
> After creating a table (total 8 columns including 1 generated column) , I
> tried to import data from a csv file (each record has values for 7 columns
> that match the non-generated column names and data types, no headers ).
>
> The
Hi,
After creating a table (total 8 columns including 1 generated column) , I
tried to import data from a csv file (each record has values for 7 columns
that match the non-generated column names and data types, no headers ).
The system issued " error: table has 7 columns but 8 values were
To whom it may concern,
A couple minor SQLite documentation bugs:
https://sqlite.org/testing.html Section 7.1, in paragraph beginning "Branch
coverage is more strict": Paragraph ends saying "three test cases", but only
two are listed (first appears garbled).
Hello Richard !
I'm no getting this:
sqlite3$ fossil update
Autosync: https://www3.sqlite.org/cgi/src
server says: 500 Server Malfunction
Pull done, sent: 1295 received: 217 ip: 64.225.41.2
Autosync failed.
continue in spite of sync failure (y/N)? n
update abandoned due to sync failure
Doh! I'm on 3.30.0.
Gotcha, thanks.
On Sat, Jan 25, 2020 at 8:22 PM Richard Hipp wrote:
> On 1/25/20, sky5w...@gmail.com wrote:
> > SELECT DISTINCT name FROM pragma_function_list
> > --WHERE (flags & 0x20)!=0 -- no such column: flags
> > ORDER BY name;
> >
> > Works if I drop the WHERE.
>
On 1/25/20, sky5w...@gmail.com wrote:
> SELECT DISTINCT name FROM pragma_function_list
> --WHERE (flags & 0x20)!=0 -- no such column: flags
> ORDER BY name;
>
> Works if I drop the WHERE.
> Is there a special compile flag that must be used?
You need to be using SQLite 3.31.0 or later.
--
D.
SELECT DISTINCT name FROM pragma_function_list
--WHERE (flags & 0x20)!=0 -- no such column: flags
ORDER BY name;
Works if I drop the WHERE.
Is there a special compile flag that must be used?
On Fri, Jan 24, 2020 at 5:42 PM Brian Curley wrote:
> separate but somewhat related question, based
Hi -- I've not heard anything more about this, and I don't see a bug listed
at https://www.sqlite.org/src/rptview?rn=1.
Will it be addressed as a bug?
I hope I'm not coming across as demanding a fix -- I just want to make sure
this hasn't fallen through the gaps!
On Mon, 6 Jan 2020 at 20:24,
On 1/25/20, Richard Hipp wrote:
> On 1/25/20, Domingo Alvarez Duarte wrote:
>> Hello Richard !
>>
>> Since yesterday I'm getting this message when trying to use fossil for
>> sqlite3.
>
> Yeah. That machine went completely bonkers and I had to rebuild it
> from scratch, using a new IP address.
And for anyone who might not be aware, there are official mirror servers
including
https://www.sqlite.org/cgi/src/doc/trunk/README.md
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
On 23 Jan 2020, at 10:22pm, Evert van Dijken wrote:
> The 64-bits DLL is missing from the download page,
"sqlite-dll-win64-x64-331.zip" works without problems for me. Please try
again, or tell us what other thing you're referring to.
___
On 1/24/20, Naumowicz, Ken E wrote:
> Hello,
>
> I need to know if there is a security patch for this CVE on Windows Server
> 2012:
>
> Java SE Vulnerability CVE-2019-16168 Related to JavaFX (SQLite) <<<===
> https://www.symantec.com/security-center/vulnerabilities/writeup/111496
>> NO
On 1/23/20, Evert van Dijken wrote:
> The 64-bits DLL is missing from the download page,
Which download page are you looking at?
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
Hello,
I need to know if there is a security patch for this CVE on Windows Server 2012:
Java SE Vulnerability CVE-2019-16168 Related to JavaFX (SQLite) <<<===
https://www.symantec.com/security-center/vulnerabilities/writeup/111496
> NO UPDATE/PATCH FOUND at SQLite - SQLite Homepage
The 64-bits DLL is missing from the download page,
--
Evert van Dijken
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Thank you for the suggestion, Warren.
Bisect first pointed to commit df51ae19c1aa4c26, but sometime after that this
segfault seemed to be fixed, a second bisect blamed commit 2ae77bd233570834:
https://www.sqlite.org/src/info/2ae77bd233570834
I've attached a backtrace from a crash at this
On 1/25/20, Domingo Alvarez Duarte wrote:
> Hello Richard !
>
> Since yesterday I'm getting this message when trying to use fossil for
> sqlite3.
Yeah. That machine went completely bonkers and I had to rebuild it
from scratch, using a new IP address. And because it used a new IP
address, I
Hello Richard !
Since yesterday I'm getting this message when trying to use fossil for
sqlite3.
Autosync: https://www3.sqlite.org/cgi/src
SSL: cannot connect to host www3.sqlite.org:443 (Connection refused)
Cheers !
___
sqlite-users mailing list
No I mean e.g.
row 1 = 500 bytes,
row 2 = 600 bytes
row 3 = 80 bytes
row 4 = 300 bytes
etc.
Like the info that DBSTAT gives, but per row, not per page. This doesn't need
to be performant - it's for usage analysis during development time.
- Deon
-Original Message-
From: sqlite-users
separate but somewhat related question, based on the response:
Has any thought been given to updating the documentation to cover those
pragmas that have been upgraded to selectable entities?
I've only been able to find a handful of references along the way, such as
pragma_table_info,
SELECT DISTINCT name
FROM pragma_function_list
WHERE (flags & 0x20)!=0
ORDER BY name;
On 1/24/20, Peter Kolbus wrote:
> Is there any documentation showing, or an easy way to generate, the exact
> list of SQLite-provided functions that are innocuous?
>
> I’d like to turn on the new
Is there any documentation showing, or an easy way to generate, the exact list
of SQLite-provided functions that are innocuous?
I’d like to turn on the new SQLITE_TRUSTED_SCHEMA but support a variety of
applications and am hoping for something to guide analysis.
Thanks
-Peter
On Thursday, January 23, 2020 17:00 CET, Richard Hipp wrote:
> On 1/23/20, Bernhard Rosenkraenzer wrote:
> > Hi,
> > after updating sqlite to 3.31.0, both firefox and thunderbird crash on
> > startup (rebuilding them against the newer sqlite doesn't help).
>
> Is this related to
Hi Jens,
the MATCH operator is not inside an OR expression. The MATCH operator is in an
AND expression, only the rowid request is in an OR expression.
Regards,
Hartwig
PS: In FTS5 since version 3.30.1 also the MATCH operator is allowed in OR
statements (try SELECT PlayersFTS.rowid FROM
On Jan 23, 2020, at 8:33 AM, Bernhard Rosenkraenzer wrote:
>
> The Debian guys have also observed this:
> https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=949644
> (and also don't have a fix yet).
>
> Any ideas?
Can you bisect SQLite to narrow the range here? This release had an unusually
On Jan 23, 2020, at 7:02 AM, Mark Benningfield wrote:
>
> ...whenever I do a Fossil pull of the latest
> version takes a grand total of about 2 seconds, but it would be nice not to
> have to remember to do it every time :)
If you’re having to reapply the change on every Fossil update, you’re
On Jan 23, 2020, at 5:45 AM, Dominique Devienne wrote:
>
> Hi. Looks like 3.31 (congrats on the release) does not include that
> small extension in the amalgamation. Could it please?
It’s easy to fix:
1. Get the SQLite source proper (https://sqlite.org/src/)
2. Add “uuid.c” to the loop
> On Jan 23, 2020, at 6:47 AM, mailing lists wrote:
>
> The following SELECT statement fails with the error "unable to use function
> MATCH in the requested context":
This is an annoying but documented limitation of FTS, not a bug. The MATCH
operator can’t be used inside an OR expression. It
On 1/23/20, Bernhard Rosenkraenzer wrote:
> Hi,
> after updating sqlite to 3.31.0, both firefox and thunderbird crash on
> startup (rebuilding them against the newer sqlite doesn't help).
Is this related to https://bugzilla.mozilla.org/show_bug.cgi?id=1607902
> Backtrace:
> (gdb) bt
> #0
Hi,
after updating sqlite to 3.31.0, both firefox and thunderbird crash on startup
(rebuilding them against the newer sqlite doesn't help).
Backtrace:
(gdb) bt
#0 0x71b9fe20 in ?? () from /usr/lib64/firefox-71.0/libxul.so
#1 0x71b993d2 in ?? () from
Hi,
create and fill the tables:
CREATE TABLE Games (ID INTEGER PRIMARY KEY, WhiteID INTEGER, BlackID INTEGER);
CREATE VIRTUAL TABLE PlayersFTS USING FTS5 (LastName,FirstNames);
INSERT INTO Games (WhiteID,BlackID) VALUES(1,2);
INSERT INTO PlayersFTS (rowid,LastName,FirstNames) VALUES(1,'A','1');
Well, I kinda thought that this would be fixed on the next release. The
"value_frombind" typo in particular prevents FTS3/4 from being built as a
loadable extension. I only have one legacy application that uses FTS3/4 that
way, and fixing these typos whenever I do a Fossil pull of the latest
On 1/23/20, Ondrej Dubaj wrote:
> I discovered an issue found by coverity scan.
Thanks for the report. This was previously fixed here:
https://www.sqlite.org/src/info/465a15c5c2077011
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
Hi,
I discovered an issue found by coverity scan.
sqlite-src-326/shell.c:5697: var_compare_op: Comparing "zFree" to null
implies that "zFree" might be null.
sqlite-src-326/shell.c:5698: alias_transfer: Assigning: "zPath" =
"zFree".
sqlite-src-326/shell.c:5699: var_deref_model: Passing
Hi. Looks like 3.31 (congrats on the release) does not include that
small extension in the amalgamation. Could it please? Uuids are fairly
common in many schemas, so native support "by default" would
standardize support for them in the SQLite ecosystem. Thanks, --DD
PS: And we'd be able to retire
SQLite uses a compressed format to store records (be it rows of a table or
entries in an index), so the length of a specific record depends on its
contents.
See https://sqlite.org/fileformat.html
Storing a row of (NULL, NULL, NULL, NULL) takes just 5 bytes, whereas (1024,
1.234, 'some
On 22 Jan 2020, at 11:44pm, Deon Brewis wrote:
> Is there any way to get the length of rows in a table / index in sqlite?
Do you mean the count of rows in a table / index ?
SELECT count(*) FROM MyTable
There's no easy fast way to do this because SQLite doesn't keep that number
handy
Is there any way to get the length of rows in a table / index in sqlite?
DBSTAT/sqlite3_analyzer --stats almost gives me the information I want, but
it's an aggregate sum & max per page - I need the data per row (cell).
- Deon
___
sqlite-users
> > Is it going to be fixed in 3.31?
>
> I'll fix the documentation, so that it doesn't say that any more, if
> that is what you mean by "fixed". :-)
>
> Hoped to have it compiled in by default :-) It'd be nice addition... Come
on, typical Linux or Windows has multiple gigabytes of memory... I'm
On 1/21/20, Digital Dog wrote:
> Hi!
>
> In the docs here
> https://www.sqlite.org/series.html
> there's this statement "The generate_series(START,END,STEP) table-valued
> function is a loadable extension included in the SQLite source tree, **and
> compiled into the command-line shell.**"
On 1/21/20, Merijn Verstraaten wrote:
> Should issues with the documentation (i.e., missing/unclear things) be
> reported to this mailing list too?
>
> Specifically, something that was unclear to me while implementing my own
> aggregate function is what happens if sqlite3_result_error() is called
Should issues with the documentation (i.e., missing/unclear things) be reported
to this mailing list too?
Specifically, something that was unclear to me while implementing my own
aggregate function is what happens if sqlite3_result_error() is called and
another result functions gets called
Hi!
In the docs here
https://www.sqlite.org/series.html
there's this statement "The generate_series(START,END,STEP) table-valued
function is a loadable extension included in the SQLite source tree, **and
compiled into the command-line shell.**" Unfortunately in version 3.30.1 on
Windows
On Tuesday, 21 January, 2020 05:28, Richard Hipp wrote:
>On 1/21/20, Keith Medcalf wrote:
>> Richard,
>>
>> The TRUSTED_SCHEMA setting works really well but I have noticed one
>> problem (there may be more, but I haven't run across any yet) with
>> it that is perhaps easy to address, though
On 1/21/20, Keith Medcalf wrote:
>
> Richard,
>
> The TRUSTED_SCHEMA setting works really well but I have noticed one problem
> (there may be more, but I haven't run across any yet) with it that is
> perhaps easy to address, though it needs to be done properly. That is
> perhaps adding an
https://www.sqlite.org/draft/c3ref/vtab_config.html
should also reference SQLITE_VTAB_INNOCUOUS and SQLITE_VTAB_DIRECTONLY
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
___
sqlite3_vtab_config(db, SQLITE_INNOCUOUS) should that not be
sqlite3_vtab_config(db, SQLITE_VTAB_INNOCUOUS)?
Which explains why my quick patch in the pragma.c xConnect code to make all
pragma vtabs innocuous didn't work (I copied from series.c) :)
rc = sqlite3_declare_vtab(db, zBuf);
if(
Richard,
The TRUSTED_SCHEMA setting works really well but I have noticed one problem
(there may be more, but I haven't run across any yet) with it that is perhaps
easy to address, though it needs to be done properly. That is perhaps adding
an innocuous flag to pragma definitions in
On Monday, 20 January, 2020 12:42, David Bicking wrote:
> Thanks. I figured the solution would use CTE (this is a CTE, isn't it??)
>Unfortunately, they were neither in Sqlite, nor mentioned in any of the
>sql stuff I read when I taught myself to do SQL.so it took me a while to
>figure out how
>Could you show us the constraint you feel disallows those things ? We
might be able to find a loophole in the exact way you phrased it.
Also, could someone explain to me what EMPTY means here ? I've seen ''
called "empty string" but it doesn't seem to be normal SQL language.
Thanks. I figured the solution would use CTE (this is a CTE, isn't it??)
Unfortunately, they were neither in Sqlite, nor mentioned in any of the sql
stuff I read when I taught myself to do SQL.so it took me a while to figure out
how it works.
Unfortunately, I extend the goals to cover all 12
Thanks Simon.
From: sqlite-users on behalf of
Simon Slavin
Sent: Monday, January 20, 2020 4:49:04 PM
To: SQLite mailing list
Subject: Re: [sqlite] Find schema of a table in a query
On 20 Jan 2020, at 2:53pm, x wrote:
> Is this possible in sql?
In SQL
On 20 Jan 2020, at 2:53pm, x wrote:
> Is this possible in sql?
In SQL schema names and table names are entities. You cannot bind a parameter
to a either of them.
This is why, instead of keeping data in many different databases, you make one
big schema, and use that value as a column.
> select name from pragma_database_list d
> where (select name from pragma_table_info(?1) where schema==d.name)
> order by seq!=1, seq limit 1;
Is this possible in sql?
Given a variable ?1 which contains a valid attached DB name is it possible to
retrieve the contents of
?1.table_name ??
On 1/20/20 4:17 AM, Keith Medcalf wrote:
The "main" database is always seq == 0, the "temp" database is always seq == 1,
and other databases are seq == 2 and greater in the order they were attached. seq 2 -> whatever is
always contiguous. The table search order for unqualified names (when a
Brilliant Keith. Many thanks.
From: sqlite-users on behalf of
Keith Medcalf
Sent: Monday, January 20, 2020 9:28:50 AM
To: SQLite mailing list
Subject: Re: [sqlite] Find schema of a table in a query
You are correct that same table names in temp obscure
You are correct that same table names in temp obscure those names from main and
other attached databases, so your order by addition is required.
select name
from pragma_database_list as d
where exists (select *
from pragma_table_info
where schema ==
The "main" database is always seq == 0, the "temp" database is always seq == 1,
and other databases are seq == 2 and greater in the order they were attached.
seq 2 -> whatever is always contiguous. The table search order for unqualified
names (when a search is required) is always in the seq
WOW Keith. That’s the sqlite coding equivalent of scoring a hat trick in the
world cup final. I’ve added an order by to get the solution
select name from pragma_database_list d
where (select name from pragma_table_xinfo where schema==d.name and arg==?1)
order by seq!=1, seq limit 1;
I’m
Simon Slavin, on Saturday, January 18, 2020 04:58 PM, wrote...
>
> On 18 Jan 2020, at 9:30pm, Csanyi Pal, on
>
> > can one edit a multiline SQL statement in the sqlite3 CLI?
>
> No.
>
> But if you make a multiline SQL statement in a text file you can paste it
> into the CLI all in one operation.
On Sun, 19 Jan 2020 17:07:38 +0100, you wrote:
>On 16.01.2020 14:02, Daniel Janus wrote:
>> Dear SQLiters,
>>
>> If an INSERT ... ON CONFLICT DO UPDATE statement detects that a row
>> already exists and needs to be updated, it doesn't seem to set lastRowid
>> to the rowid of that row. Observe
On Sunday, 19 January, 2020 01:47, x wrote:
>Suppose you’re given a query ‘SELECT 1 from tbl’ by a user and you want
>to know the name of the schema that tbl belongs to. What’s the easiest
>way to do this?
>I know sqlite will use temp.tbl if it exists else main.tbl if it exists
>else it will
Defining SQLITE_DEFAULT_DEFENSIVE prevents proper working of the CLI .parameter
commands.
SQLite version 3.31.0 2020-01-19 18:49:07
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .schema
sqlite> .param
If I could answer the “such as” I wouldn’t have asked the question. The word
“hoping” is the clue as in I was hoping there was some function I had
overlooked. I’ve settled for using the sqlite3_table_column_metadata function
in a loop using each db name in turn in the order sqlite3 does.
On 16.01.2020 14:02, Daniel Janus wrote:
Dear SQLiters,
If an INSERT ... ON CONFLICT DO UPDATE statement detects that a row
already exists and needs to be updated, it doesn't seem to set lastRowid
to the rowid of that row. Observe (sqlite 3.30.1):
> create table users (id integer primary
On Jan 19, 2020, at 2:41 AM, x wrote:
>
> I was hoping for something simpler.
Such as?
I mean, your question basically reduces to “I need to be inside the parse
loop,” and SQLite has come along and said, “Hey, check this out, you can be
inside the parse loop.” I mean, how cool is that?
Thanks Dominique. I was aware of that route but I was hoping for something
simpler.
From: sqlite-users on behalf of
Dominique Devienne
Sent: Sunday, January 19, 2020 9:32:28 AM
To: SQLite mailing list
Subject: Re: [sqlite] Find schema of a table in a query
On Sun, Jan 19, 2020 at 9:47 AM x wrote:
> Suppose you’re given a query ‘SELECT 1 from tbl’ by a user and you want to
> know the name of the schema that tbl belongs to. What’s the easiest way to
> do this?
Set an authorizer. Requires to write code though, cannot be done in SQL.
Suppose you’re given a query ‘SELECT 1 from tbl’ by a user and you want to know
the name of the schema that tbl belongs to. What’s the easiest way to do this?
I know sqlite will use temp.tbl if it exists else main.tbl if it exists else it
will search for the earliest attached schema with a
On 1/18/20 3:21 AM, Rocky Ji wrote:
Hi,
I am asked to highlight rows containing strange characters. All data were
ingested by a proprietary crawler.
By strange, I mean, question marks, boxes, little Christmas Trees, solid
arrows, etc. kind of symbols; these appear suddenly in flow of normal
Keith Bertram wrote:
>
> Do you have an estimated time for this release?
>
If everything goes as planned, right around the third week of February.
--
Joe Mistachkin
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
While there are lines to be edited:
Press up arrow until line is recalled
Edit the line
Press the ENTER key to enter that line
Maybe you have to compile your own to include readline (on Linux), but it works
for me. Both Linux and Windows.
--
The fact that there's a Highway to Hell but
On 18 Jan 2020, at 9:30pm, Csanyi Pal wrote:
> can one edit a multiline SQL statement in the sqlite3 CLI?
No.
But if you make a multiline SQL statement in a text file you can paste it into
the CLI all in one operation.
___
sqlite-users mailing list
Ooops. Wrong query pasted, should be this one:
with p (period) as (
values (cast(strftime('%m') as integer))
),
unks (period, type, amount) as (
select p.period,
'UNK',
(
select sum(amount)
from goals
Hello,
can one edit a multiline SQL statement in the sqlite3 CLI?
Say I entered the following multiline SQL statement:
sqlite> SELECT 1 UNION ALL
...> SELECT 2 UNION ALL
...> SELECT 3
...> ;
Then after a while I want to run it again, then how can I use the bash
history to get back
Mayhaps like this?
CREATE TABLE Goals
(
period integer primary key,
amount integer not null
);
CREATE TABLE Data
(
period integer not null references Goals(period),
type text not null,
amount integer not null
);
create index Data_Period on Data (period);
INSERT INTO Goals
I suspect the answer is that it is best to do this in the application
program. However, the platform I want to use is dumb as a brick.
It basically can call sqlite3_get_table, mildly reformat the data
and send it to the display.
Anyway, there are two tables
CREATE TABLE Goals (period integer
If we are talking the later case, and the 'text' field contains text in Windows
MBCS then you can use, for example:
for row in db.execute('select cast(mbcsfield as blob) from table'):
textfield = row[0].decode('mbcs')
to recover proper unicode text. If the encoding is not 'mbcs' substitute
On 18 Jan 2020, at 12:12pm, Rocky Ji wrote:
> By question marks, I meant- that some text, like Dutch programmers names, and
> address in Nordic locations, have accents and umaults and other such
> modifications done to English-alphabets. These get displayed as ? or box
SQLite doesn't display
801 - 900 of 113868 matches
Mail list logo
