[sqlite] php/sqlite

I have an issue with the php sqlite3 interface where it returns a fail on bindValue(), yet lastErrorCode() lastErrorMsg() return "no error". I am using sqlite 3.24. and php 7.2 Does anyone know if this is a php issue or is it an sqlite issue? ___

Re: [sqlite] CSV import deletes trailing zeroes on text fields

"CSV import deletes /leading/ zeroes on text fields" excel does this. Quite difficult to stop it from doing so. On Fri, Jul 13, 2018 at 6:52 AM, R Smith wrote: > I believe your subject should read: "CSV import deletes /leading/ zeroes on > text fields" - Your trailing Zero is in tact. > > And

Re: [sqlite] I'm getting a compiler error on a SQLIte build on Ubuntu 16.04 (which surprises me)

> It works without -O2, do you have optimisations left on? -fPIC -O2 -Wall -Wextra -pedantic ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I'm getting a compiler error on a SQLIte build on Ubuntu 16.04 (which surprises me)

I'm using gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609 on 64 bit. I have built both 64 and 32 bit versions from sqlite-src-324 No problems. Check your build-essential install? On Tue, Jul 10, 2018 at 1:05 AM, Dan Kennedy wrote: > On 07/09/2018 09:26 PM, Rob Willett wrote: >> >>

Re: [sqlite] explain this shell command please

( a integer , b integer); SELECT a,b FROM x INSERT INTO x VALUES(1,1); INSERT INTO x VALUES(2,2); INSERT INTO x VALUES(3,3); COMMIT; sqlite> **The same thing happens with a file based database On Mon, Jul 2, 2018 at 10:22 AM, Simon Slavin wrote: > On 2 Jul 2018, at 1:08am, David Burgess

Re: [sqlite] explain this shell command please

Thanks simon. Back to my original issue. Is this a bug? sqlite> .echo on sqlite> .dump x .dump x PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE x ( a integer , b integer); SELECT a,b FROM x INSERT INTO x VALUES(1,1); INSERT INTO x VALUES(2,2); INSERT INTO x VALUES(3,3); COMMIT; sqlite>

[sqlite] explain this shell command please

I have a few sqlite databases and .dump in the shell seems to work fine for me (unless I have '.echo off') I was preparing a test case to report the bug and I note that .dump does not work on temp tables (3.24). Is this a feature? sqlite> drop table if exists x;create temp table temp.x ( a

Re: [sqlite] Time Precision

Too long since I have coded for windows. BUT getting a accurate time/interval from a loaded windows system is non-trivial. The multimedia timers are ok (from memory). ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] column types and constraints

"This flexible type-name arrangement works because SQLite is very forgiving about you putting non-proscribed values into columns - it tries to convert if it can do so without loss of information but if it cannot do a reversible type conversion it simply stores whatever you give it. Hence if you

Re: [sqlite] Check if the new table has been created

; On Thu, Jun 21, 2018 at 12:03 PM, Igor Korot wrote: > Richard, > > On Wed, Jun 20, 2018 at 8:17 PM, Richard Hipp wrote: >> On 6/20/18, Igor Korot wrote: >>> if( ( res = sqlite3_step( stmt ) ) == SQLITE_OK ) >> >> sqlite3_step() returns SQLITE_ROW when it has data, not

Re: [sqlite] Check Constraint

my guess check( your_col NOT GLOB '[@%$!]" ' ) On Tue, Jun 12, 2018 at 7:50 PM, Cecil Westerhof wrote: > I want to create a field that only has values that consist of letters, > numbers end '-'. So no spaces, quotes or special characters like: '@%$!'. > What is the best way to write this check

Re: [sqlite] Trigger Performance

Trying again/ Specifically, preparation of the constant "trigger part" of the statement is the overhead? Correct? On Tue, Jun 12, 2018 at 9:47 AM, David Burgess wrote: > Specifically, preparation of the "trigger part" of the statement is > the overhead? Correct? >

Re: [sqlite] Trigger Performance

gt; taking up the rest of the observed difference. > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says a > lot about anticipated traffic volume. > > >>-Original Message- >>From: sqlite-users [mailto:sqlite-users- >>boun...@maili

[sqlite] Trigger performance

I have a table where I insert 284,000 rows time taken 3.39 seconds. I add the following trigger CREATE TRIGGER x_trigger BEFORE INSERT ON x WHEN 0 = 1 BEGIN SELECT RAISE ( ABORT, 'raise' ); END; time taken 4.49 seconds. ___

Re: [sqlite] Trigger Performance

Run Time: real 0.000 user 0.00 sys 0.00 > sqlite> .exit > > > What version of SQLite are you using? > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says a > lot about anticipated traffic volume. > > >>-Original Message-

[sqlite] Trigger Performance

I have a table where I drop, create and insert 284,000 rows time taken 3.39 seconds. I add the following trigger CREATE TRIGGER x_trigger BEFORE INSERT ON x WHEN 0 = 1 BEGIN SELECT RAISE ( ABORT, 'raise' ); END; time taken 4.49 seconds.

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

> What problem are you having with trigger performance? My primary use of triggers in SQLite is on insert/update and using RAISE. Not sure where the trigger overhead comes from, but If I place a simple check in CHECK and have a trigger with the same CHECK then CHECK is way faster than a trigger.

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

> reminder that views can have triggers Anyone else have an issue with trigger performance in SQLite? ___ 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

> There are some functions which are banned. Are extension functions permitted? And how does an extension function author indicate that the function is deterministic? On Sun, Jun 10, 2018 at 10:23 AM, Simon Slavin wrote: > On 10 Jun 2018, at 1:09am, David Burgess wrote: > >> full

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

Would schema defined functions fulfill the same requirement (e.g. return val = p1 || p2) ? Computed columns would be of assistance to me, assuming that functions are permitted. e.g. full_account_number CHAR(16), branch CHAR(4), account_number CHAR(12), full_account_number AS ( branch ||

Re: [sqlite] Explain Query plan

COLLATE NOCASE has the desired effect, like then performs much the same as glob. pragma case_sensitve_like = had no effect. On Fri, Jun 8, 2018 at 9:29 PM, R Smith wrote: > > On 2018/06/08 1:24 PM, Clemens Ladisch wrote: >> >> David Burgess wrote: >>>> >>

Re: [sqlite] [EXTERNAL] Explain Query plan

> Have you run ANALYZE? Yes. And LIKE now uses the index, but like is still ~100 times slower. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Explain Query plan

I have a table with a 16 byte column (500,000 rows), the column is indexed. When I use explain query plan in the shell, with LIKE in the WHERE clause it responds with "SCAN TABLE" whereas GLOB (or EQUALS or >= ) use the index. LIKE 'exact hit', LIKE 'matches start%' or 'matches start _' returns

Re: [sqlite] Feature suggestion / requesst

Agreed. Would be good. On Fri, Jun 8, 2018 at 1:25 PM, Rowan Worth wrote: > On 3 June 2018 at 07:28, Scott Robison wrote: > > > I've encountered a feature that I think would be awesome: > > https://www.postgresql.org/docs/9.3/static/dml-returning.html > > > > Example: INSERT INTO blah (this,

Re: [sqlite] shell edit quoting

> And it works for me: I'm pleased for you. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] shell edit quoting

> Where do the quotes around the value come from? I typed them. Simgle set of double quotes > Are you using the standard command-line shell, and which output mode? yes and the default mode ___ sqlite-users mailing list

[sqlite] shell edit quoting.

ve 2 quotes for each one entered in the editor. Feature or bug? -- David Burgess ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] shell edit quoting

sqlite> insert into sql_procs (name, sql) values ('a', edit('sql','vim')); then in the editor I enter one line: select * from "mytable" ; sqlite> select sql from sql_procs where name = 'a'; sql "select * from ""mytable""; " edit() seems to give 2 quotes for each one entered in the editor.

Re: [sqlite] Stored Procedures

> The usual way of handling that in SQLite is to store a script in a text > column someplace, then execute them as needed. Is there a simple way to do this from SQLite shell? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?

For those of you who use SQLite to prepare CSV for import/open into Excel beware of this problem: "Text","Next bit is a reference id","A001" "text","same again","0009" On Windows, In the second row, 3rd column Excel will remove the leading zeroes, if the file has an extension of .csv The same

Re: [sqlite] Are you getting spam when you post to sqlite-users ?

No problems with spam on my first day back on this list. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Shell CSV bug

Version SQLite 3.22.0 Linux 3.19.0-32-generic The header line when output to file is terminated by \x0a all other lines are terminated with \x0d\x0a test case - .mode csv .once afile.csv select ... -- David Burgess ___ sqlite-users

Re: [sqlite] INTEGER PRIMARY KEY

> But we have to preserve backwards compatibility - even with bugs > like this. ​How about a new release? i.e. sqlite4 No backward compatibilty issues.​ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Is sqlite3_auto_extension() same compilation unit ruled out?

Have a look at the way readfile() and writefile() is implemented in the sqlite interpreter. On Tue, Jun 13, 2017 at 10:38 AM, petern wrote: > I have a situation where it would be convenient to locate externally > loadable SQLite extension code in the same

Re: [sqlite] SQLITE_OMIT_* vs amalgamation

OINIT > * SQLITE_OMIT_SHARED_CACHE > > hoping for some speedups. > > Thanks, > - Kim > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Release 3.18.0

n useless indexes. > Thanks. > > josé > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- David Burgess ___ sqlite-

Re: [sqlite] 3.18 Problem

e.org> wrote: > On 4/2/17, Simon Slavin <slav...@bigfraud.org> wrote: >> >>> On 3 Apr 2017, at 2:11am, David Burgess <dburg...@gmail.com> wrote: >>> >>> (I guess I should re-check >>> to see if that has changed since 3.7) >> &

Re: [sqlite] 3.18 Problem

]' THEN RAISE ( ABORT, 'Invalid col2' ) WHEN CASE "ditto for third check" END = 0 THEN RAISE ( ABORT, 'Invalid col1 or col2' ) END; END; On Mon, Apr 3, 2017 at 10:23 AM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 3 Apr 2017, at 12:41am, David Burgess <

Re: [sqlite] 3.18 Problem

Bad trigger performance prevents me using your suggested solution. Is there a secret to making triggers perform like CHECK? On Mon, Apr 3, 2017 at 1:35 AM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 2 Apr 2017, at 2:50pm, David Burgess <dburg...@gmail.com> wrote: &

[sqlite] 3.18 Problem

Hi . I am new to this mailing list I have a table with 500,000 rows, it has a unique column of registration numbers The rules for the format of these numbers has changed over time and the schema has changed with the rules of the time. PRAGMA integrity_check now complains about some rows not