Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

On Monday, 9 March, 2020 18:18, Peng Yu wrote: >But I never experience the problem in my original email when I used >python3's default sqlite3 module (WAL was not used). What is the >difference between the default sqlite3 module and apsw? Thanks. THe relevant difference is that the sqlite3

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

On 3/8/20, Keith Medcalf wrote: > > On Sunday, 8 March, 2020 21:24, Peng Yu wrote: > >>When I open an sqlite3 db using the following python code, > >>conn=apsw.Connection(filepath, flags = apsw.SQLITE_OPEN_READONLY) > >>, I got the following error. > >>Traceback (most recent call last): >> File

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

On Monday, 9 March, 2020 08:33, Simon Slavin wrote: >If your .shm and .wal files still exist when no apps are accessing the >database, the most likely cause is that at least one of the apps is not >closing its connection correctly. or you are opening the database connection with

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

On 9 Mar 2020, at 2:16pm, David Raymond wrote: > In general I believe the last connection tries to do a complete checkpoint > when it closes, and if it succeeds then it'll delete the -wal and -shm files. > If you have automatic checkpointing turned off (maybe you're doing regular >

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

I see this. What does it mean? Does it mean even when the sqlite3 session is closed there is still -wal and -shm left on the disk? """ There is an additional quasi-persistent "-wal" file and "-shm" shared memory file associated with each database, which can make SQLite less appealing for use as

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

I see this. What does it mean? Does it mean even when the sqlite3 session is closed there is still -wal and -shm left on the disk? """ There is an additional quasi-persistent "-wal" file and "-shm" shared memory file associated with each database, which can make SQLite less appealing for use as

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

[This explanation is much simplified for clarity. Before filling in missing details please consider what the OP wants. Don't just show off your exhaustive knowledge of SQLite.] A database is normally in delete journal mode, as if you'd executed PRAGMA journal_mode=DELETE In this journal

Re: [sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

On Sunday, 8 March, 2020 21:24, Peng Yu wrote: >When I open an sqlite3 db using the following python code, >conn=apsw.Connection(filepath, flags = apsw.SQLITE_OPEN_READONLY) >, I got the following error. >Traceback (most recent call last): > File "/xxx.py", line 21, in >for x in

[sqlite] How to open an sqlite3 for reading without being blocked by process writing to it?

When I open an sqlite3 db using the following python code, conn=apsw.Connection(filepath, flags = apsw.SQLITE_OPEN_READONLY) , I got the following error. Traceback (most recent call last): File "/xxx.py", line 21, in for x in c.execute('SELECT (data) FROM sqlar'): File "src/cursor.c",

Re: [sqlite] How should I use the sqlite3 tool?(for linux)

On Tue Mar 03, 2020 at 05:12:17PM +0800, suanzi wrote: > Thank you,you let me know what happened. > > About your answer,I try it,but it can't work,maybe because can't have two > zlib. > > apt-get could not find zlib:i386. Did you specifically try "zlib1g"? I don't think the "zlib" package

Re: [sqlite] How to enforce a specific order of group_concat?

On Mon, Mar 2, 2020 at 6:35 PM Keith Medcalf wrote: > Well, in theory an order by in a nested select means that the result of the > operation is an ordered projection and not merely a set of rows. > For this particular case (a nested select with an order by and the outer > query with an

Re: [sqlite] How should I use the sqlite3 tool?(for linux)

Thank you,you let me know what happened. About your answer,I try it,but it can't work,maybe because can't have two zlib. apt-get could not find zlib:i386. And there have not sqlite3 tool for linux x64 in sqlite.org Anyway,thank you,I plan download the sqlite source and compile.

Re: [sqlite] How should I use the sqlite3 tool?(for linux)

This means you're missing a dependency - in this case zlib. It's hard to believe you don't have zlib on your system at all; probably this is happening because your system is amd64 but the sqlite binary you've downloaded is x86. I'm not a debian user but this should get you going: apt-get install

Re: [sqlite] How should I use the sqlite3 tool?(for linux)

My OS is Debian10 amd64,desktop is xfce4 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How should I use the sqlite3 tool?(for linux)

After I download the tool,I cd to the path,then,I run "./sqlite3" But It can't work,here are error msgs: ./sqlite3: error while loading shared libraries: libz.so.1: cannot open shared object file: No such file or directory So,What should I do for it?

Re: [sqlite] How to enforce a specific order of group_concat?

On Monday, 2 March, 2020 09:20, Dominique Devienne wrote: >On Mon, Mar 2, 2020 at 5:09 PM Keith Medcalf wrote: >> select group_concat(value) from (select distinct value from test order by >> value); >But is that guaranteed to be ordered correctly "forever" instead of by >"happenstance" from

Re: [sqlite] How to enforce a specific order of group_concat?

On Mon, Mar 2, 2020 at 5:09 PM Keith Medcalf wrote: > select group_concat(value) from (select distinct value from test order by > value); But is that guaranteed to be ordered correctly "forever" instead of by "happenstance" from current implementation details? My point was that the Window

Re: [sqlite] How to enforce a specific order of group_concat?

to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users On >Behalf Of Dominique Devienne >Sent: Monday, 2 March, 2020 08:02 >To: SQLite mailing list >Subject: Re: [sqlite] How to enforce a specific ord

Re: [sqlite] How to enforce a specific order of group_concat?

On Sun, Mar 1, 2020 at 10:58 PM mailing lists wrote: > Are there any other solutions / possibilities? I thought someone more knowledgeable than I about Window Functions [1] would answer, but since nobody mentioned them so far, I'll do it, as I believe this is the "SQL native" way to achieve what

Re: [sqlite] How to enforce a specific order of group_concat?

On 1/03/2020 22:57, mailing lists wrote: Assume I create the following table: CREATE TABLE Test (ID INTEGER PRIMARY KEY, Value TEXT); INSERT INTO Test (Value) VALUES('Alpha'); INSERT INTO Test (Value) VALUES('Beta'); INSERT INTO Test (Value) VALUES('Beta'); INSERT INTO Test (Value)

Re: [sqlite] How to enforce a specific order of group_concat?

Hi Keith, thanks for the explanation. PS: I used a CTE because official examples (e.g. Mandelbrot) also used CTEs in combination with group_concat. Although the incorporation of group_concat was not the primary reason to use CTEs. PPS: Is it possible to rephrase the documentation for

Re: [sqlite] How to enforce a specific order of group_concat?

On Sunday, 1 March, 2020 14:58, mailing lists wrote: >Assume I create the following table: >CREATE TABLE Test (ID INTEGER PRIMARY KEY, Value TEXT); >INSERT INTO Test (Value) VALUES('Alpha'); >INSERT INTO Test (Value) VALUES('Beta'); >INSERT INTO Test (Value) VALUES('Beta'); >INSERT INTO Test

[sqlite] How to enforce a specific order of group_concat?

Assume I create the following table: CREATE TABLE Test (ID INTEGER PRIMARY KEY, Value TEXT); INSERT INTO Test (Value) VALUES('Alpha'); INSERT INTO Test (Value) VALUES('Beta'); INSERT INTO Test (Value) VALUES('Beta'); INSERT INTO Test (Value) VALUES('Alpha'); According to the documentation of

Re: [sqlite] How to prevent sqlite_reset reporting an already known error

On 26 Feb 2020, at 11:58pm, mailing lists wrote: > The issue is that sqlite_reset() reports the same error that already > sqlite3_step() reported. How can I prevent that sqlite_reset() reports the > same error. You can't. It's working as designed. One would normally test the result that

[sqlite] How to prevent sqlite_reset reporting an already known error

I am executing a prepared statement S with a couple of different bindings. The execution sequence is similar to this while (moreBindings) { bind_parameters_to_prepared_statement; sqlite3_step(); if (error) { … } sqlite_reset(); if (error) { } } The issue is that sqlite_reset() reports the

Re: [sqlite] How to compile sqlite with gcov support?

When I deleted the --enable-debug flag, the .gcno file can be generated. Not sure why the --enable-debug flag will influence gcov compilation. On Tue, Feb 18, 2020 at 3:16 PM Xingwei Lin wrote: > Hi, > > I decompressed the sqlite-autoconf-3310100.tar.gz and compiled it with the > following

[sqlite] How to compile sqlite with gcov support?

Hi, I decompressed the sqlite-autoconf-3310100.tar.gz and compiled it with the following commands: ``` cd sqlite-autoconf-3310100 CFLAGS="-g -ftest-coverage -fprofile-arcs" ./configure --enable-fts3 --enable-debug --enable-session make -j$(nproc) ``` But when I completed the building process, I

Re: [sqlite] How to group this?

Solved this now, nil to do with SQL, but just running a different search (other value code and then you can ask for a secondary value and no need anymore to find the matching pair). RBS On Mon, Feb 10, 2020 at 8:22 AM Bart Smissaert wrote: > I fully agree with you, but I sofar I have no

Re: [sqlite] How to group this?

On 11/02/2020 01:35, Simon Slavin wrote: I don't think that creating an index on a view actually works, does it? You're right. What was I thinking ? Maybe I've used another implementation of SQL that it does work on. Thanks for picking me up on it. You are right, SQL Server allows you to

Re: [sqlite] How to group this?

On 10 Feb 2020, at 10:41pm, Wolfgang Enzinger wrote: > Am Mon, 10 Feb 2020 01:42:14 + schrieb Simon Slavin: > >> On 10 Feb 2020, at 1:25am, no...@null.net wrote: >> >> create two VIEWs, [...]. Index both VIEWs on (id, date), > > I don't think that creating an index on a view actually

Re: [sqlite] How to group this?

Am Mon, 10 Feb 2020 01:42:14 + schrieb Simon Slavin: > On 10 Feb 2020, at 1:25am, no...@null.net wrote: > > create two VIEWs, [...]. Index both VIEWs on (id, date), I don't think that creating an index on a view actually works, does it? Wolfgang

Re: [sqlite] How to group this?

Sorry, I made a typo. The windows should be ORDER BY ROWID, not ORDER BY ID. Jen On Mon, Feb 10, 2020 at 09:19:59AM -0700, Jen Pollock wrote: > I think the following works: > > SELECT s.ID 'ID', s.Date Date, Systolic, Diastolic > FROM > (SELECT ID, ENTRY_DATE Date, NUMERIC_VALUE Systolic,

Re: [sqlite] How to group this?

I think the following works: SELECT s.ID 'ID', s.Date Date, Systolic, Diastolic FROM (SELECT ID, ENTRY_DATE Date, NUMERIC_VALUE Systolic, row_number() OVER id_date r FROM pressure WHERE TERM = 'Systolic' WINDOW id_date AS (PARTITION BY ID, ENTRY_DATE ORDER BY ID) ) s JOIN (SELECT ID,

Re: [sqlite] How to group this?

On 2/9/20 11:44 PM, Rowan Worth wrote: On Mon, 10 Feb 2020 at 11:12, Richard Damon wrote: On 2/9/20 7:24 PM, Bart Smissaert wrote: ID ENTRY_DATE TERM NUMERIC_VALUE ROWID 1308 15/Mar/2013 Systolic 127 701559 1308 15/Mar/2013

Re: [sqlite] How to group this?

I fully agree with you, but I sofar I have no control over this data, I have it like I showed. As far as I can see there always will be a secondary value, but as you say I can't be sure. All this has to do with changing our clinical coding system from Read codes to Snomed. In the old setup there

Re: [sqlite] How to group this?

On Mon, 10 Feb 2020 at 11:12, Richard Damon wrote: > On 2/9/20 7:24 PM, Bart Smissaert wrote: > > ID ENTRY_DATE TERM NUMERIC_VALUE ROWID > > > > 1308 15/Mar/2013 Systolic 127 701559 > > 1308 15/Mar/2013 Diastolic 81 701568 > > 1308

Re: [sqlite] How to group this?

On 2/9/20 7:24 PM, Bart Smissaert wrote: ID ENTRY_DATE TERM NUMERIC_VALUE ROWID 1308 15/Mar/2013 Systolic 127 701559 1308 15/Mar/2013 Diastolic 81 701568 1308 27/Jun/2013 Systolic 132 701562 1308 27/Jun/2013 Systolic 141 701563

Re: [sqlite] How to group this?

to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users On >Behalf Of no...@null.net >Sent: Sunday, 9 February, 2020 18:26 >To: SQLite mailing list >Subject: Re: [sqlite] How to group this? > >On Mon Feb 10, 2020 at 12:24:33AM +00

Re: [sqlite] How to group this?

. >-Original Message- >From: sqlite-users On >Behalf Of Keith Medcalf >Sent: Sunday, 9 February, 2020 19:17 >To: SQLite mailing list >Subject: Re: [sqlite] How to group this? > > select id, > entry_date, > max(case when term == 'Systolic'

Re: [sqlite] How to group this?

t: Sunday, 9 February, 2020 17:25 >To: General Discussion of SQLite Database us...@mailinglists.sqlite.org> >Subject: [sqlite] How to group this? > >ID ENTRY_DATE TERM NUMERIC_VALUE ROWID > >1308 15/Mar/2013 Syst

Re: [sqlite] How to group this?

On 10 Feb 2020, at 1:25am, no...@null.net wrote: > Here is one way that appears to generate the correct result. Another way: create two VIEWs, one for systolic, one for diasystolic. Index both VIEWs on (id, date), then JOIN ON id AND date. If you want to, you could use this to make a third

Re: [sqlite] How to group this?

On Mon Feb 10, 2020 at 12:24:33AM +, Bart Smissaert wrote: > I should get: > > 127/81 > 132/82 > 141/85 > 143/94 > > What should be the SQL to group like this? Here is one way that appears to generate the correct result. CREATE TABLE pressure( id INTEGER PRIMARY KEY,

[sqlite] How to group this?

ID ENTRY_DATE TERM NUMERIC_VALUE ROWID 1308 15/Mar/2013 Systolic 127 701559 1308 15/Mar/2013 Diastolic 81 701568 1308 27/Jun/2013 Systolic 132 701562 1308 27/Jun/2013 Systolic 141 701563 1308 27/Jun/2013 Systolic 143 701564 1308

Re: [sqlite] How can I detect rows with non-ASCII values?

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

Re: [sqlite] How can I detect rows with non-ASCII values?

t >Subject: Re: [sqlite] How can I detect rows with non-ASCII values? > > >On Saturday, 18 January, 2020 05:21, Rocky Ji >wrote: > >>> > GLOB supports character classes > >>thanks for teaching new keyword and its use. > >>My first attempt was very s

Re: [sqlite] How can I detect rows with non-ASCII values?

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

Re: [sqlite] How can I detect rows with non-ASCII values?

On Saturday, 18 January, 2020 05:21, Rocky Ji wrote: >> > GLOB supports character classes >thanks for teaching new keyword and its use. >My first attempt was very similar to what you suggest, except I used >sqlite3 and re from inside Python. >But as you see, I can't reliably seprate

Re: [sqlite] How can I detect rows with non-ASCII values?

On Saturday, 18 January, 2020 05:13, Rocky Ji wrote: >Sorry for lack of clarity. >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

Re: [sqlite] How can I detect rows with non-ASCII values?

> > > GLOB supports character classes thanks for teaching new keyword and its use. My first attempt was very similar to what you suggest, except I used sqlite3 and re from inside Python. But as you see, I can't reliably seprate 'interrogative' question marks from question marks that get

Re: [sqlite] How can I detect rows with non-ASCII values?

Sorry for lack of clarity. 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 On Sat, Jan 18, 2020, 16:34 Clemens Ladisch

Re: [sqlite] How can I detect rows with non-ASCII values?

Rocky Ji wrote: > 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 ASCII > English

[sqlite] How can I detect rows with non-ASCII values?

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 ASCII English letters. How do I

Re: [sqlite] how to disable dot commands?

I like this answer!! I think I think it's the easiest way. On Mon, Jan 13, 2020 at 10:22 AM Keith Medcalf wrote: > > On Sunday, 12 January, 2020 18:44, Xingwei Lin > wrote: > > >Is there any way can we disable the dot commands feature in sqlite? > > SQLite does not process dot commands, they

Re: [sqlite] how to disable dot commands?

On Sunday, 12 January, 2020 18:44, Xingwei Lin wrote: >Is there any way can we disable the dot commands feature in sqlite? SQLite does not process dot commands, they are commands to the shell.c SQLite Application program. The current shell.c application currently does not have a way to omit

Re: [sqlite] how to disable dot commands?

Hi, On Sun, Jan 12, 2020 at 7:44 PM Xingwei Lin wrote: > > Hi, > > Is there any way can we disable the dot commands feature in sqlite? Are you talking about the SQLite shell? Why do you want to disable them? What is your specific scenario? Thank you. > > -- > Best regards， > Xingwei Lin >

Re: [sqlite] how to disable dot commands?

On 13 Jan 2020, at 1:43am, Xingwei Lin wrote: > Is there any way can we disable the dot commands feature in sqlite? SQLite – the library you call from C and other programming languages – does not support the dot commands. It doesn't recognise them. If you try to use them you will get a

[sqlite] how to disable dot commands?

Hi, Is there any way can we disable the dot commands feature in sqlite? -- Best regards， Xingwei Lin ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to get rowid for select query?

previous: rescells.Clear; rc := sqlite3_get_table(db, PChar(selectquery), @selres, @nRow, @nColumn, @zErrmsg); Result := rc = SQLITE_OK; for i := 0 to nRow * nColumn - 1 do begin rescells.Add(selres[i]); end; if selres <> nil then sqlite3_free_table(selres); current, working

Re: [sqlite] How to get rowid for select query?

OK, first row is always header, next rows are values of query, pt., 10 sty 2020 o 13:56 Andy napisał(a): > I don't know why sqlite3_get_table > for 'SELECT rowid, id, Bytes, BytesCompr, flags FROM Articles where id=5 > limit 1' > give me column names: > rowid id Bytes BytesCompr flags > instead

Re: [sqlite] How to get rowid for select query?

Andy wrote: > I try "select rowid, field1,field2 from table" but first value was not > number rowid but literary string "rowid". Please show the actual code (not SQL, but your program) that you're executing. Regards, Clemens ___ sqlite-users mailing

Re: [sqlite] How to get rowid for select query?

I don't know why sqlite3_get_table for 'SELECT rowid, id, Bytes, BytesCompr, flags FROM Articles where id=5 limit 1' give me column names: rowid id Bytes BytesCompr flags instead od values of this columns pt., 10 sty 2020 o 13:36 Andy napisał(a): > I successfully insert blob. I use

[sqlite] How to get rowid for select query?

I successfully insert blob. I use sqlite3_last_insert_rowid afer calling sqlite3_step. But how to retrieve blob? I try "select rowid, field1,field2 from table" but first value was not number rowid but literary string "rowid". I can get blob if I know rowid.

Re: [sqlite] how to pass -Dxxx compile option

Thanks all the nice guys' reply!! On Thu, Jan 9, 2020 at 10:59 PM Gary R. Schmidt wrote: > On 09/01/2020 17:47, Xingwei Lin wrote: > > Hi, > > > > How can I pass -Dxxx compile option when I build sqlite? Such as, - > > DSQLITE_ENABLE_INTERNAL_FUNCTIONS. > > > ./configure --help will tell you

Re: [sqlite] how to pass -Dxxx compile option

I have an executable file myconfigure with ./configure CFLAGS="-O2 -DSQLITE_MAX_COLUMN=4096 -DHAVE_FDATASYNC -DHAVE_STRCHRNUL -DHAVE_LOCALTIME_R -DHAVE_GMTIME_R -DHAVE_NAN -DHAVE_USLEEP -DSQLITE_DEFAULT_WORKER_THREADS=4 -DSQLITE_TEMP_STORE=2 -DSQLITE_USE_URI -DSQLITE_ENABLE_API_ARMOR

Re: [sqlite] how to pass -Dxxx compile option

On 09/01/2020 17:47, Xingwei Lin wrote: Hi, How can I pass -Dxxx compile option when I build sqlite? Such as, - DSQLITE_ENABLE_INTERNAL_FUNCTIONS. ./configure --help will tell you that CFLAGS is how you do that, so: ./configure CFLAGS=-Dwhatever If you have many options:

Re: [sqlite] how to pass -Dxxx compile option

On Jan 9, 2020, at 6:51 AM, Warren Young wrote: > >./configure CFLAGS='-DSQLITE_ENABLE_INTERNAL_FUNCTIONS' > > The reasons for the recommendation have to do with complications that result > from multiple variables, nested Makefile.am, etc. It also allows autoreconf to work properly in the

Re: [sqlite] how to pass -Dxxx compile option

On Jan 9, 2020, at 6:37 AM, Richard Hipp wrote: > > On 1/9/20, Xingwei Lin wrote: >> >> How can I pass -Dxxx compile option when I build sqlite? Such as, - >> DSQLITE_ENABLE_INTERNAL_FUNCTIONS. > > Option 1: > > CFLAGS='-O2 -DSQLITE_ENABLE_INTERNAL_FUNCTIONS' ./configure && make > > Option

Re: [sqlite] how to pass -Dxxx compile option

On 1/9/20, Xingwei Lin wrote: > Hi, > > How can I pass -Dxxx compile option when I build sqlite? Such as, - > DSQLITE_ENABLE_INTERNAL_FUNCTIONS. > Option 1: CFLAGS='-O2 -DSQLITE_ENABLE_INTERNAL_FUNCTIONS' ./configure && make Option 2: ./configure && OPTS='-DSQLITE_ENABLE_INTERNAL_FUNCTIONS'

Re: [sqlite] how to pass -Dxxx compile option

On Thu, 9 Jan 2020 14:47:44 +0800, you wrote: > Hi, > > How can I pass -Dxxx compile option when I build sqlite? Such as, - > DSQLITE_ENABLE_INTERNAL_FUNCTIONS. I don't consider myself an expoert, but the script below works for me on a Raspberry Pi, Raspbian Jessie. Note: instead of readline I

Re: [sqlite] how to pass -Dxxx compile option

On 9 Jan 2020, at 9:41, Xingwei Lin wrote: > I always use ./configure && make to compile sqlite. > > ./configure can add some compile options, but I don't know how to add -Dxxx > option in this compilation process. I don't know (since I've never needed to build SQLite), but can offer a hint

Re: [sqlite] how to pass -Dxxx compile option

Thanks for reply. I always use ./configure && make to compile sqlite. ./configure can add some compile options, but I don't know how to add -Dxxx option in this compilation process. On Thu, Jan 9, 2020 at 5:29 PM Simon Slavin wrote: > On 9 Jan 2020, at 6:47am, Xingwei Lin wrote: > > > How

Re: [sqlite] how to pass -Dxxx compile option

On 9 Jan 2020, at 6:47am, Xingwei Lin wrote: > How can I pass -Dxxx compile option when I build sqlite? Such as, - > DSQLITE_ENABLE_INTERNAL_FUNCTIONS. Depends on which compiler you're using. See examples for gcc on this page:

[sqlite] how to pass -Dxxx compile option

Hi, How can I pass -Dxxx compile option when I build sqlite? Such as, - DSQLITE_ENABLE_INTERNAL_FUNCTIONS. -- Best regards， Xingwei Lin ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] How create database, tables,indices...

Sent: Tuesday, January 7, 2020 9:23 AM To: SQLite mailing list Subject: Re: [sqlite] How create database, tables,indices... I see: http://zetcode.com/db/sqlitec/ New database is simply open not existing file in path? ___ sqlite-users mailing list sqlite

Re: [sqlite] How create database, tables,indices...

On 7 Jan 2020, at 2:11pm, Andy wrote: > Which function are for crating new database, tables, indices, columns, fill > tables. You do this with SQL commands, just like the commands you use to read data out of the database. On 7 Jan 2020,

Re: [sqlite] How create database, tables,indices...

I see: http://zetcode.com/db/sqlitec/ New database is simply open not existing file in path? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How create database, tables,indices...

I am beginner in Sqlite programming. I already open and read tables. I compiled sqlite3.c (8 MB) amalgamation file do DLL and call functions from FreePascal. Which function are for crating new database, tables, indices, columns, fill tables. I see #define SQLITE_CREATE_INDEX 1 /* Index

Re: [sqlite] How to determine the column type? – virtual table?

On 12/14/19 1:55 PM, František Kučera wrote: > Dne 14. 12. 19 v 18:20 Richard Damon napsal(a): >> What he wants is different. He takes a basically arbitrary database >> (user provided) and an arbitrary SQL statement (also user provided) and >> he wants to determine what type a given column will

Re: [sqlite] How to determine the column type? – virtual table?

On 14 Dec 2019, at 6:55pm, František Kučera wrote: > This is IMHO feasible, but would mean probably a lot of work. However it > might be beneficial for those who come from other relational databases and > prefer static typing rather than dynamic. I don't think it's practical. For instance,

Re: [sqlite] How to determine the column type? – virtual table?

I just don't see the trouble https://github.com/d3x0r/SACK/blob/master/src/SQLlib/sqlstub.c#L3613-L3680 for each row for each column = sqlite3_column_type( collection->stmt, idx - 1 ) and then get the data according to the type... sqlite3_column_double (for instance) It's not very expensive to

Re: [sqlite] How to determine the column type? – virtual table?

Dne 14. 12. 19 v 18:20 Richard Damon napsal(a): > What he wants is different. He takes a basically arbitrary database > (user provided) and an arbitrary SQL statement (also user provided) and > he wants to determine what type a given column will present. Yes, I am looking for something that is

Re: [sqlite] How to determine the column type? – explicit cast

On 12/14/19 11:12 AM, Simon Slavin wrote: > On 14 Dec 2019, at 10:46am, František Kučera wrote: > >> SELECT cast(dump+100 AS integer) FROM fstab; >> >> the sqlite3_column_decltype() still does not return the integer type. >> >> Would it be possible to modify this function or add a new one, to

Re: [sqlite] How to determine the column type? – explicit cast

On 14 Dec 2019, at 10:46am, František Kučera wrote: > SELECT cast(dump+100 AS integer) FROM fstab; > > the sqlite3_column_decltype() still does not return the integer type. > > Would it be possible to modify this function or add a new one, to tell the > correct type at least if there is an

Re: [sqlite] How to determine the column type?

On 12/14/19 5:22 AM, František Kučera wrote: > Dne 14. 12. 19 v 9:36 Darren Duncan napsal(a): >> With respect to SQLite, every column is the union of: Null, every Integer, >> every Float, every Text, every Blob. > OK, we can close this thread with that it is currently impossible to track > the

Re: [sqlite] How to determine the column type?

dump+100 (string), pass+1000 (string) the type information disappears and former integers becomes mere strings There must be something else going on here: Z:> sqlite3 SQLite version 3.30.1 2019-10-10 20:19:45 Enter ".help" for usage hints. Connected to a transient in-memory database. Use

Re: [sqlite] How to determine the column type? – explicit cast

P.S. Even if I do: SELECT cast(dump+100 AS integer) FROM fstab; the sqlite3_column_decltype() still does not return the integer type. Would it be possible to modify this function or add a new one, to tell the correct type at least if there is an explicit cast like this in given query? If I

Re: [sqlite] How to determine the column type?

Dne 14. 12. 19 v 9:36 Darren Duncan napsal(a): > With respect to SQLite, every column is the union of: Null, every Integer, > every Float, every Text, every Blob. OK, we can close this thread with that it is currently impossible to track the declared column types through relational operations.

Re: [sqlite] How to determine the column type?

František, Having done this myself, I will tell you that: The only effective design for your "generic software that should work with any database model" is that every column is the universal type, the union of all other types; the type is the set of all representable values. With respect to

Re: [sqlite] How to determine the column type?

On Friday, 13 December, 2019 18:35, Richard Damon wrote: >One big thing to watch out is that columns of NUMERIC type can easily >return values of either INTEGER or REAL type. Your single type >expectation is easily broken here. I also don't know if >9223372036854775807 (the biggest integer

Re: [sqlite] How to determine the column type?

On 12/13/19 7:16 PM, František Kučera wrote: > Dne 14. 12. 19 v 0:09 Keith Medcalf napsal(a): >> On Friday, 13 December, 2019 15:49, František Kučera >> wrote: >> >>> I know that SQLite uses dynamic types, so it is not easy… But what is the >>> best way to determine the column type of a result

Re: [sqlite] How to determine the column type?

On 14 Dec 2019, at 12:16am, František Kučera wrote: > In case of my software I can really expect that all values in a column will > have the same type or be NULL (and everything else means error). In that case, execute your query and use sqlite3_column_type() on each column of the first row

Re: [sqlite] How to determine the column type?

Dne 14. 12. 19 v 0:09 Keith Medcalf napsal(a): > On Friday, 13 December, 2019 15:49, František Kučera > wrote: > >> I know that SQLite uses dynamic types, so it is not easy… But what is the >> best way to determine the column type of a result set? > Result sets do not have "column types". Each

Re: [sqlite] How to determine the column type?

On Friday, 13 December, 2019 15:49, František Kučera wrote: >I know that SQLite uses dynamic types, so it is not easy… But what is the >best way to determine the column type of a result set? Result sets do not have "column types". Each result value (the intersection of row and column) has a

Re: [sqlite] How to determine the column type?

On 12/13/19 5:49 PM, František Kučera wrote: > Hello, > > I know that SQLite uses dynamic types, so it is not easy… But what is the > best way to determine the column type of a result set? > > The sqlite3_column_decltype() works only if I select directly a column, but > not when I do some other

[sqlite] How to determine the column type?

Hello, I know that SQLite uses dynamic types, so it is not easy… But what is the best way to determine the column type of a result set? The sqlite3_column_decltype() works only if I select directly a column, but not when I do some other operations (call function, increment etc.). The

Re: [sqlite] How to update plural rows from one table to another.

On Friday, 15 November, 2019 15:22, Gan Uesli Starling wrote: >In the following update query, I had expected for the integer values >"rowid" from the table "info" to project copies of themselves singly and >separately into the integer cells "info_id" of table "qso", row-by-row, >where the

[sqlite] How to update plural rows from one table to another.

In the following update query, I had expected for the integer values "rowid" from the table "info" to project copies of themselves singly and separately into the integer cells "info_id" of table "qso", row-by-row, where the timestamps "t_from" of each are matching. UPDATE qso SET info_id = (  

Re: [sqlite] How to use CASE statement to SUM() some numbers

Maybe something like: CREATE VIEW "Sum of Expenses Between two Dates" AS SELECT Date, sum( CASE WHEN Date BETWEEN date('now', '-1 months') AND date('2019-11-04', '-1 days') THEN Expense ELSE 0 END) as 'Sum of Expenses:' FROM Expenses; On 11/15/2019 12:22 PM, David

Re: [sqlite] How to use CASE statement to SUM() some numbers

So why do you need a case? What will not work with the simple: select sum(Expense) from Expenses where Date between date('now', '-1 months') and date('2019-11-04', '-1 days'); ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

[sqlite] How to use CASE statement to SUM() some numbers

Hi, I have a simple database, the 'Incomes_Expenses.db' on my system. It's SQL is here: -- -- File generated with SQLiteStudio v3.2.1 on P nov. 15 20:39:18 2019 -- -- Text encoding used: UTF-8 -- PRAGMA foreign_keys = off; BEGIN TRANSACTION; -- Table: Expenses CREATE TABLE Expenses (Id

  1   2   3   4   5   6   7   8   9   10   >