Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

> Please consider subscribing to the new Forum. The intent is that the forum > will eventually replace this mailing list. Having just had a go at subscribing, I screwed up and mistyped my email address. Is there any way for me to change it? Regards And

Re: [sqlite] Maintaing a Last updated timestamp column using triggers

>Thanks Andy >> In addition, the role of the "when" clause is unclear. Is it necessary? >> >> I don't think it is. I have a very similar trigger which I've been >> using for several years And it doesn't have the where... >> >> CREATE TRIGGER [Up

Re: [sqlite] Maintaing a Last updated timestamp column using triggers

ot;when" clause is unclear. Is it necessary? I don't think it is. I have a very similar trigger which I've been using for several years And it doesn't have the where... CREATE TRIGGER [UpdatePortTime] AFTER UPDATE ON ports FOR EACH ROW BEGIN

[sqlite] After column add, what should be done to update the schema?

Hi, I use a simple ALTER TABLE ADD COLUMN statement. However, when I use the Sqlite Expert, the DDL is not reflected. Even though the column is created. I read somewhere that the DDL is really just a copy of the text when teh table was created. Is there a way to refresh this? Thanks, Andy

[sqlite] Sql update script. check for existing rows before inserting...

INTO [Antennas]([Code], [Antenna], [Bands], [Ports], [Offset], [Bidirectional]) VALUES(0, '', '', null, null, '0'); -- a few of those... END; However, I am getting a syntax error near IF. Any ideas where my error is? Thanks Andy Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for W

Re: [sqlite] unexpected sqlite_busy behaviour within transactions

SQLite is provided. Further reading: https://sqlite.org/c3ref/busy_handler.html https://sqlite.org/rescode.html#busy https://sqlite.org/lang_transaction.html#immediate https://www.sqlite.org/lockingv3.html Best wishes, @ndy -- andy...@ashurst.eu.o

Re: [sqlite] Is randomblob(16) a good guid generation across multiple computers?

I admit I do not fully understand all the arguments. I am running on Windows. Are you saying the PRNG on Windows is not good enough to use randomblob(16) in Sqlite? All I need is a reasonable assurance that is are unique... Andy Sent from my T-Mobile 4G LTE Device Get Outlook for Android<ht

[sqlite] Is randomblob(16) a good guid generation across multiple computers?

://devblogs.microsoft.com/oldnewthing/20120523-00/?p=7553 Thanks Andy Sent from my T-Mobile 4G LTE Device Get Outlook for Android<https://aka.ms/ghei36> ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/c

Re: [sqlite] Searching by Guid index in table (.NET)? I can write and read, but not search by the Guid

not, or does it? Andy Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10 From: sqlite-users on behalf of Radovan Antloga Sent: Friday, February 14, 2020 2:25:01 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Sea

[sqlite] Searching by Guid index in table (.NET)? I can write and read, but not search by the Guid

;@ID", DbType.Guid, Me.Id) rdr2 = theDB.ExecuteQuery("SELECT * FROM [Dxlog] WHERE ID = @ID", sqlParameters) rdr2.Read() Do I have to converte the Guid to binary somehow? Thanks Andy Sent from Mail<https://go.microsof

Re: [sqlite] SqlLite.Net: Tables creation and schema changes strategies

getting a syntax error 'near IF' Thanks Andy Sent from my T-Mobile 4G LTE Device Get Outlook for Android<https://aka.ms/ghei36> From: sqlite-users on behalf of Simon Slavin Sent: Saturday, February 8, 2020 9:42:25 AM To: SQLite mailing list Subject: Re: [

[sqlite] SqlLite.Net: Tables creation and schema changes strategies

of my program? Any help is appreciated. Thanks Andy Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/li

Re: [sqlite] Database speed comparison

And huge speedup by exec("BEGIN") myloop exec("COMMIT") sob., 11 sty 2020 o 07:30 Andy napisał(a): > I had sqlite3_prepare_v2 without sqlite3_finalize and sqlite3_close not > closes multiple databases. > > sob., 11 sty 2020 o 07:17 Andy napisał(a): > &

Re: [sqlite] Database speed comparison

I had sqlite3_prepare_v2 without sqlite3_finalize and sqlite3_close not closes multiple databases. sob., 11 sty 2020 o 07:17 Andy napisał(a): > These tests are a lot faster than calling Sqlite in my program, I must use > the same convention: for example first prepare, next sqlite3_bind

Re: [sqlite] Database speed comparison

These tests are a lot faster than calling Sqlite in my program, I must use the same convention: for example first prepare, next sqlite3_bind_int64, sqlite3_bind_text and sqlite3_step. My calling was very not optimal. pt., 10 sty 2020 o 20:13 Simon Slavin napisał(a): > Are you aware of > >

Re: [sqlite] Database speed comparison

I like to compare these tests on my computer. My first attempt to Sqlite works but a lot of slower and I don't know why, maybe due to inserting blobs? But blobs are small, about 10 kB. pt., 10 sty 2020 o 19:37 Cecil Westerhof napisał(a): > I ran across this page: >

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

ite3_free_table(selres); pt., 10 sty 2020 o 14:01 Clemens Ladisch napisał(a): > 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

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

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

[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.

[sqlite] Best way to store key,value pairs

I am trying using Sqlite to news reader and local small NNTP server. NNTP message has header and body. Some special headers field needed to XOVER I keep in one table, body I will compress and store as blob, some other required header fields I will store in own columns, but also exists not

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] Size limits

ure to define it as > > id integer primary key, > little_field_1 text, > little_field_2 int, > little_field_3 float, > big_blob_at_the_end blob > > > > -Original Message- > From: sqlite-users On > Behalf Of Andy > Sent: Tuesday, January 7, 2020 5:3

Re: [sqlite] Size limits

Hi Andy, What are reasonable limits for size Sqlite3 database file and large blobs? Here are some benchmarks on BLOB performance: https://sqlite.org/intern-v-extern-blob.html However, see the note here in section 4.1 about using the incremental BLOB I/O routines so that you don't thrash

[sqlite] Size limits

What are reasonable limits for size Sqlite3 database file and large blobs? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance vs. memory trade-off question

Hi, (3) Each database connection uses about 72 KB less heap space. How much is this as a percentage of a freshly initialised connection and how much as a percentage of some kind of "typical" connection (is there even one?)? andy...@ashurst.eu.org http://www.ashurst.eu

Re: [sqlite] Request: Combining skip-scan with 'max' optimization

generator my program uses does not (yet) have the capability to generate nested SELECTs, so the optimization is unavailable to me until/unless we implement that. andy...@ashurst.eu.org http://www.ashurst.eu.org/ http://www.gonumber.com/andyjpb 0x7EBA75FF ___

Re: [sqlite] Optimising query with aggregate in subselect.

In section 3 search for: "Side note: Bare columns in an aggregate queries." -- Best wishes, @ndy -- andy...@ashurst.eu.org http://www.ashurst.eu.org/ 0x7EBA75FF ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailingli

Re: [sqlite] Optimising query with aggregate in subselect.

. Best wishes, @ndy -- andy...@ashurst.eu.org http://www.ashurst.eu.org/ 0x7EBA75FF ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimising query with aggregate in subselect.

ones that do not require quoting and the identifier quoted items that are not column names with parameter markers. Best wishes, @ndy -- andy...@ashurst.eu.org http://www.ashurst.eu.org/ 0x7EBA75FF ___ sqlite-users mailing list sqlite-users@mailinglists.sq

Re: [sqlite] Optimising query with aggregate in subselect.

hru'. I'm pretty happy with the indexes it's currently choosing (apart from the MATERIALIZE). Adding a covering index on timestamp theoretically improves things but doesn't seem to make a (measurable) difference in practice with current data sizes. Best wishes, @ndy -- andy...@ashurst

Re: [sqlite] Optimising query with aggregate in subselect.

Hi, ORDER BY "key" DESC This should be ASC, not DESC: I've been working on versions of the query that can go forwards and backwards and made an editor snafu when writing the eMail. Best wishes, @ndy -- andy...@ashurst.eu.org http://www.ashurst.eu.org/

[sqlite] Optimising query with aggregate in subselect.

a LIMIT clause at the point denoted with "-- (1)". This keeps the subselect small and then materialising the subselect and generating the automatic covering index becomes cheap. For pagination I then feed in the key from the last row of the previous batch at the points denoted w

Re: [sqlite] Getting "chunked" output from a large SELECT operation.

in memory? Are you able to show us your query and how you are executing it from your language? Best wishes, @ndy -- andy...@ashurst.eu.org http://www.ashurst.eu.org/ 0x7EBA75FF ___ sqlite-users mailing list sqlite-users@mailinglists.sqlit

Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)

ading new or vice versa. > > > > Then if you want to retain that, just don't use new features that might > break fwd-compatibility. Yes, I am aware of this. I was just providing a "real" example to answer the question posed by the OP. Regards Andy Ling ***

Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)

will run on a variety of versions of Android with a similar variety of versions of sqlite. Currently any version can read the database from any other version regardless of whether it is old reading new or vice versa. Regards Andy Ling ***

Re: [sqlite] [SPAM?] Is WAL mode serializable?

er engaged? Best wishes, @ndy -- andy...@ashurst.eu.org http://www.ashurst.eu.org/ 0x7EBA75FF ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is WAL mode serializable?

n't think that the stuff about checkpointing, durability aside, is relevant from a semantic POV, yes? Thanks for your help. Best wishes, @ndy -- andy...@ashurst.eu.org http://www.ashurst.eu.org/ 0x7EBA75FF ___ sqlite-users mailing list sqlite-user

[sqlite] Is WAL mode serializable?

help! Best wishes, @ndy -- andy...@ashurst.eu.org http://www.ashurst.eu.org/ 0x7EBA75FF ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] wal

ered while setting up SQLTools on Sublime for SQLite. It doesn't/can't create a persistent connection) Ingo ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please explain multi-access behaviour to me

g said that, SQLITE is nice because its single writer model means that read-modify-write hazards are less of a problem than other RDBMSes, provided transactions that intend to write use BEGIN IMMEDIATE. -- andy...@ashurst.eu.org http://www.ashurst.eu.org/ http://www.gonumber.com/andyjpb 0x7E

Re: [sqlite] Please explain multi-access behaviour to me

any of the existing data with UPDATE or UPSERT-style statements. If this is too much trouble then you can, as you say, use BEGIN EXCLUSIVE and fix it when you get to the point where more concurrency is required. Best wishes, @ndy -- andy...@ashurst.eu.org http://www.ashurst.eu.org

Re: [sqlite] Have SQLite handle values of my own type

rations that can be done directly on the encoded data such as sorting, comparison and composition. It's not quite on topic for this list but if anyone's interested I can provide more information and links to implementations in Scheme and Javascript. andy...@ashurst.eu.org http://www.ashu

Re: [sqlite] round function inconsistent

> I do not know what the underlying representation for floating point > numbers is in Excel, but as your experiment shows, it is probably not > IEEE754 double-precision binary. > Well according to this is does...

[sqlite] Is Cursor a 1 time only object?

Chemistry Chemistry Chemistry But if I now follow that command with this for row in cursor: print(row[0],row[1]) I again get no response/output. So is the cursor a 1 use only object? Does it null itself out after the first reading? Regards, Andy Hegedus Founder AGH Analytics, LLC

Re: [sqlite] Remove row to insert new one on a full database

e id is incrementing. So id 4000 may not be able to be put on the same page as the id 1 you have just deleted. Andy Ling ** DISCLAIMER: Privileged and/or Confidential information may be contained in this message. If you are not

Re: [sqlite] What is the recommended way to write to views?

www.sqlite.org/c3ref/last_insert_rowid.html -- andy...@ashurst.eu.org http://www.ashurst.eu.org/ http://www.gonumber.com/andyjpb 0x7EBA75FF ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Am I understanding how to use ROLLBACK - SAVEPOINT correctly?

after the one that was last fetched rather the one that was fetched immediately after the first time that the SAVEPOINT was passed. Does anyone know what the guarantees are? andy...@ashurst.eu.org http://www.ashurst.eu.org/ http://www.gonumber.com/andyjpb 0x7EBA75FF

Re: [sqlite] ISO8601 vs Numeric Timestamp for Date Storage

arithmentic on future dates is left as an exercise for the reader (sorry)! Regards, @ndy -- andy...@ashurst.eu.org http://www.ashurst.eu.org/ 0x7EBA75FF ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http

Re: [sqlite] Custom aggregate functions in Tcl

I made many updates to my implementation, which can be found here: https://chiselapp.com/user/andy/repository/sqlite-andy/timeline?r=andygoth-tcl-function https://chiselapp.com/user/andy/repository/sqlite-andy/artifact?fn=src/tclsqlite.c=andygoth-tcl-function https://chiselapp.com/user/andy

Re: [sqlite] Custom aggregate functions in Tcl

ents where the first argument > is "final". Then when you go to add the xValue and xInverse routines > for window functions, you will have a convenient way to distinguish > those calls from xStep and xFinal. > > On 1/30/19, Andy Goth wrote: > > On 1/29/19 1:15 AM,

Re: [sqlite] Custom aggregate functions in Tcl

On 1/30/19 3:27 PM, Andy Goth wrote: The next chance I get (probably tomorrow morning), I'll go ahead and add "step" or "final" as the initial argument to aggregate functions. I'll also lift the prohibition on aggregate functions with no arguments. This change is n

Re: [sqlite] Custom aggregate functions in Tcl

turn value, making it possible to modify it in-place without incurring copy-on-write, as documented in the tclSqlFuncStep() comments. Overall, my preference is to avoid creating global named objects when anonymous values will do the job, hence my use of [apply]. -- Andy Goth |

Re: [sqlite] Custom aggregate functions in Tcl

On 1/29/19 1:15 AM, Andy Goth wrote: I wish to define custom aggregate functions in Tcl Initial implementation: https://chiselapp.com/user/andy/repository/sqlite-andy/info/e0689f05d1f8792d Sample program, intended to be run from the root of a built SQLite tree: #!/usr/bin/env tclsh load

Re: [sqlite] Custom aggregate functions in Tcl

Oh yeah, I meant to say that I was going to leave window functions for future expansion. First I need to get more familiar with their use. Yesterday was my first time implementing an aggregate function, and I need to work my way up. On Tue, Jan 29, 2019, 07:46 Richard Hipp On 1/29/19, Andy Goth

Re: [sqlite] SQLite slow when lots of tables

t;EXPLAIN QUERY PLAN " to see what it is doing. You can use that information to add the correct index. Of course, when that happens, we can help you with interpretation of the query plan if you want it. Regards, @ndy -- andy...@ashurst.eu.org http://www.ashurs

[sqlite] Custom aggregate functions in Tcl

I wish to define custom aggregate functions in Tcl, but this capability is currently not exposed through the Tcl interface. Thus I am thinking about how best to add it. Here's a first crack at a design proposal: Extend the [db function] command to accept an -aggregate switch that makes the new

Re: [sqlite] Query Planning Knowledge

foreign key relationships to get out of sync if one of the connections omits to apply the pragma? Thanks for the tips! Regards, @ndy -- andy...@ashurst.eu.org http://www.ashurst.eu.org/ 0x7EBA75FF ___ sqlite-users mailing list sqlit

Re: [sqlite] Query Planning Knowledge

Y clause to the sub-select (** 3 **) the query plan doesn't change so I guess it's happy to use that index to get the ordering. If I then also add an ORDER BY to the main select it still uses a temporary b-tree to confirm the sort. This is the main source of my confusion because the query plan

[sqlite] Query Planning Knowledge

INTEGER NOT NULL , PRIMARY KEY ("log-id", "entry-number")); CREATE TABLE IF NOT EXISTS "item-digests" ("item-id" INTEGER NOT NULL , "algorithm" TEXT NOT NULL , "digest" BLOB NOT NULL , PRIMARY KEY ("item-id", "algorithm&

Re: [sqlite] Ideas or Guide on SQLite Search Engine for a relational database?

You can try reading the Fossil source code to see how it handles full-text searching across multiple tables. https://fossil-scm.org/index.html/artifact?fn=src/search.c=trunk On Sat, Jan 19, 2019, 06:10 Scott > I apologize, I sent this from a different email than I registered > accidentally. >

Re: [sqlite] Lazy JSON data to sqlite database

If you have an SQL schema that works for you and also sample data, I might be able to assist writing the conversion program. Dunno if you're interested since it sounds like you won't need to do this again for another year. On Sat, Jan 19, 2019, 23:17 David Bicking >> Is there a tool out there

Re: [sqlite] SQLite 3.24.0 Solaris 9 build failure

Dennis Clarke wrote: > On 2018-07-28 08:33, Andy Goth wrote: >> SQLite 3.24.0 fails to build on Solaris 9 (a.k.a. Solaris 2.9) > It may be [worth] while to spin up a Solaris 9 zone on a Solaris 10 or > Solaris 11 server for this purpose. I don't have access to any Solaris serv

Re: [sqlite] Best practices for forward conversion of database formats?

These scripts where left by the installer, so if you needed to install an earlier version of code, it could find the "downgrade" scripts and run them without the earlier version of code having to know how to downgrade. HTH Andy Ling **

Re: [sqlite] Common index for multiple databases

ps we could get some clarification from the OP about what is actually wanted. Regards Andy Ling ** DISCLAIMER: Privileged and/or Confidential information may be contained in this message. If you are not the addressee of th

Re: [sqlite] Common index for multiple databases

ps we could get some clarification from the OP about what is actually wanted. Regards Andy Ling ** DISCLAIMER: Privileged and/or Confidential information may be contained in this message. If you are not the addressee of this m

[sqlite] SQLite 3.24.0 Solaris 9 build failure

ris 9 without breaking other Solaris/SunOS platforms. -- Andy Goth | ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Immediate mode transaction gets error 5, SQLITE_BUSY when attempting commit

ntees that no subsequent operations on the same database through the next COMMIT will return SQLITE_BUSY." The read-only process has busy_timeout set to 1, if that matters. Am I right that this should not be happening, if indeed my assertions are correct? th

Re: [sqlite] Idea: defining table-valued functions directly in SQL

ALUES ('Bob', 33), ('Jen', 19), ('Liz', 30); SELECT people.name AS name , count(other.name) AS nolder FROM people LEFT JOIN people AS other ON (other.age > people.age) GROUP BY people.name; namenolder -- -- Bob

Re: [sqlite] Idea: defining table-valued functions directly in SQL

it's okay to simply say "lp" in the above indexing expressions, rather than "lp.lp" which is what I believe is meant. -- Andy Goth | ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] CASE and NULL

the two "sleep" cases (or the "commute" cases) to be combined with OR: "WHEN BETWEEN 22 AND 24 OR BETWEEN 0 and 6 THEN 'sleep'". But I imagine this would complicate the parser far beyond any practical benefit. -- Andy Goth | ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Idea: defining table-valued functions directly in SQL

n aggregate function? But before we get bogged down in semantics, I ask whether or not this distinction even matters. -- Andy Goth | ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Understanding SELECT statement

ing this is SELECT aDate FROM TeachingSaturdaysInSchoolYear AS T WHERE T.aDate The AS is optional, but makes it slightly clearer that you are saying use the table TeachingSaturdaysInSchoolYear and call it T. In this case it is a way of saving typing. Regards Andy Ling

Re: [sqlite] Idea: defining table-valued functions directly in SQL

On 6/9/2018 8:31 PM, Simon Slavin wrote: > On 10 Jun 2018, at 2:18am, Andy Goth wrote: >> Skip computed columns in the value list? If two tables have the same >> schema, this should duplicate one into the other, but apparently not: >> >> INSERT INTO table2 SELECT * fr

Re: [sqlite] Idea: defining table-valued functions directly in SQL

On 06/09/18 20:10, Simon Slavin wrote: On 10 Jun 2018, at 2:00am, Andy Goth wrote: CREATE TABLE tempLog ( datestamp TEXT COLLATE NOCASE PRIMARY KEY , centTemp REAL); CREATE VIEW tempLogView AS SELECT * , centTemp * 9 / 5 + 32 AS fahrTemp FROM tempLog; Yes

Re: [sqlite] Idea: defining table-valued functions directly in SQL

that exist in the underlying tables. I wish SELECT statement expressions could refer not only to input columns but also output columns that have been named using AS, but we don't have this feature. -- Andy Goth | ___ sqlite-users mailing list sq

Re: [sqlite] Idea: defining table-valued functions directly in SQL

/c_deterministic.html -- Andy Goth | ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Idea: defining table-valued functions directly in SQL

uld be no different, provided they return exactly one column. CREATE VIEW multiples AS VALUES (x) UNION ALL VALUES (x * 2) UNION ALL VALUES (x * 3) PARAMETERS (x); SELECT * FROM foo WHERE (a, b, c) IN multiples(a); -- Andy Goth |

[sqlite] Idea: defining table-valued functions directly in SQL

eas arguments are values. I'm not sure this distinction is important, but I went with it anyway. I used views for my syntax examples, but I'd also like to see this work for common table expressions. WITH double AS (SELECT arg * 2 PARAMETERS (arg)) SELECT

Re: [sqlite] This is driving me nuts

for all the pages. Andy Ling --- This email has been scanned for email related threats and delivered safely by Mimecast. For more information please visit http:

Re: [sqlite] [External] Re: Always call a value-quoting routine

ctually quoting strings? > > It could just indicate someone with a sense of humor who crafted a > name that looks like an injection attack for their company. > More about the reason for the name https://pizzey.me/blog/no

Re: [sqlite] How to get the entry from sqlite db without primary key or rowid order?

Whoops, your right. I should have tested. I am more used to MySQL and assumed field was more "standard". Andy Ling -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of x Sent: Fri 09 March 2018 13:40 To: SQLite mailing li

Re: [sqlite] How to get the entry from sqlite db without primary key or rowid order?

1, 2); So you need to put the same IDs in the FIELD as you have in the IN. HTH Andy Ling -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hegde, Deepakakumar (D.) Sent: Fri 09 March 2018 13:15 To: sqlite-users@mailinglists.sqlite.o

[sqlite] Building 3.22.0 for vxWorks fails

((uid_t(*)(void))aSyscall[21].pCurrent) Regards Andy Ling --- This email has been scanned for email related threats and delivered safely by Mimecast. For more informat

Re: [sqlite] Efficient ways to maintaining order rank / row_number() in a rather large set ?

the sorting on output, not input. I rather liked Jens solution to use a string to define the sort order. (top of second page of thread) Andy Ling -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Eric Grange Sent: Tue 09 January 2018

Re: [sqlite] Sqlite3.6 Command-line delete/update not working with large db file(>280GB)

Try changing the "DELETE FROM table WHERE " to "SELECT COUNT(1) FROM table WHERE " and see if you get a number bigger than 0. If not, then your WHERE isn't matching the rows you think it should. Regards Andy Ling -Original Message- From: sqlite-users [mail

Re: [sqlite] XOR operator

page says that '<>' means 'non-equals'. This is not the same as the >binary operation 'XOR' since 'non-equals' can yield only two values: true >and >false. Clemens said "For boolean values" So if a and b are limited to the values 0

Re: [sqlite] Sharing data between desktop and Android

e information about it? It's not my app, I just use it. It's called MobileSheetsPro. There's more information here http://www.zubersoft.com/mobilesheets/ Mike, the guy that wrote it, is extremely helpful. So if you contact h

Re: [sqlite] Sharing data between desktop and Android

s. It has a companion PC app that lets you backup and modify the underlying sqlite database. It transfers the database between Android and PC using a web link. There is a "sync to PC" menu on the app that opens the connecti

Re: [sqlite] Issue with updating database content (C++)

You're not deleting any rows, you're just changing the value of the data in the row. What you probably want is something like DELETE FROM BC WHERE PK = '%q'; HTH Andy Ling -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Ali

Re: [sqlite] Database version

that's up to you to code. There is a PRAGMA user_version you might want to use or you can create your own table with stuff like schema_version in it. Regards Andy Ling --- This email has been scanned for emai

Re: [sqlite] SQLite3.Step fast forward / skipping rows

BY clause, then count through to row "rowNumber" to give you the row you asked for. Not much different to the suggestion already made to create a temporary table. Andy Ling -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behal

Re: [sqlite] SQLite3.Step fast forward / skipping rows

Then when you detect a jump you'll need to use a new search to "jump" to the page you want. Something like SELECT WHERE sortedColumn > lastValue ORDER BY sortedColumn And make sure you have indexes on all the columns that you can sort by. Andy Ling -Original Mess

[sqlite] Checking gcc/clang compiler version (was: OS X/Xcode build error: use of unknown builtin)

ally disable the optimisations given by __builtin_{add|sub|mul}_overflow when compiled with clang. Cheers, Andy ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Now you know a bit more about what you are looking for, perhaps you should revisit the links Warren Young gave a few days ago... >> You’re looking for prepared statements with parameters: >>https://sqlite.org/c3ref/stmt.html >>https://sqlite.org/lang_expr.html#varpar

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

LIKE ('%' || ?2 || '%')" Then use the sqlite3_bind_* () calls to replace the ?n markers with the " liststLikeFieldValue" strings. HTH Andy -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Clyde Eisenbeis Sent: Thu 2

Re: [sqlite] Autoincrement sequence not updated by UPDATE

say about auto increment is that it will create a unique number. Andy -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Tony Papadimitriou Sent: Tue 01 November 2016 13:09 To: SQLite mailing list Subject: Re: [sqlite] Autoincrement

Re: [sqlite] Autoincrement sequence not updated by UPDATE

t you can also reset the auto increment value via SQL. Regards Andy --- This email has been scanned for email related threats and delivered safely by Mimecast. For more information please visit http://www.mim

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Tue 01 November 2016 11:42 To: SQLite mailing list Subject: Re: [sqlite] Autoincrement sequence not updated by UPDATE On 1 Nov 2016, at 11:14am, Andy Ling <andy.l...@s-a-m.com> wrote: > MySQL lets y

  1   2   3   4   >