# SQLite Forum

Timeline

## 50 most recent forum posts by user knu

 2021-10-14 11:58 • Reply: sqldiff for Windows 10 x 64 bits (artifact: 1b2b1a1424 user: knu) Hi anonymous, 32-bit sqldiff.exe runs just fine on my 64-bit Windows 10, that is Microsoft Windows [Version 10.0.19043.1288] . Can you be a bit more specific on your diagnosis "does not start" and "is not compatible"? If you expect a graphical user interface, there isn't one. It is a command line utility. Open a CMD.EXE window and run sqldiff.exe --help for more information. -- Kind Regards, Kees Nuyt  2021-10-12 14:32 • Reply: Compiling sqlite system on Ubuntu (artifact: 9da545904f user: knu) Where do I place them? sudo make install will place anything that needs to be installed into the proper directories, by default in the /usr/local tree, or anywhere else where ./configure --prefix=.... points to. Run ./configure --help for more info. ... python for some scripting and I want to make sure that the latest built libraries are the one it uses. The sqlite make process will not build or install python modules. If you want to build that yourself, you are probably best off with Roger Binns' APSW (Another Python SQLite Wrapper) -- Kind Regards, Kees Nuyt  2021-10-02 16:49 • Reply: bug? Unexpected CLI shell comment handling (artifact: f07616e218 user: knu) Hi Larry, Thanks for your quick reply and thorough analysis. I can confirm that the current trunk behaves as expected. Next time I report a possible bug, I will bisect it beforehand, so you can pinpoint it more easily. Thank you for your efforts! -- Kind regards, Kees Nuyt  12:29 • Post: bug? Unexpected CLI shell comment handling (artifact: 600247a354 user: knu) In recent versions of SQLite, I noticed single line comments in the sqlite3 command line tool behave weird. Illustration: knu@f7p3:~ $sqlite3 test.db # a database with one table, t1 SQLite version 3.37.0 2021-10-01 21:01:07 Enter ".help" for usage hints. sqlite> .tables t1 sqlite> -- .tables ...> ; sqlite> SELECT name FROM sqlite_schema; t1 sqlite> -- SELECT name FROM sqlite_schema; ...> ; sqlite>  Observed behavior: Line starting with -- requires continuation, and a ';' to complete. Expected behavior: No continuation prompt. Same behavior when processing redirected scripts with single line comments, as in: sqlite3 test.db in.tmp <pragma0.txt for p in$(cat relevant_pragmas) ; do printf ".print %s::\nPRAGMA %s;\n" "$p" "$p" ; done | sqlite3 yourdb.sqlite >pragma1.txt diff -u pragma0.txt pragma1.txt -- Regards, Kees  2021-03-16 01:42 • Reply: Serious problems with STORED columns (artifact: c545226a94 user: knu) Unlike a VIRTUAL column that is computed on the fly, a generated STORED column is actual stored data and as such it must survive dumps, i.e., it must be possible to restore to the exact same content it had before a DUMP. That would be hard, as a dump file just contains DDL and INSERT statements. And you can't INSERT : insert into log(dt,msg) VALUES ('somestamp','c'); Error: near line ...: cannot INSERT into generated column "dt"  AFAIC, a solution is needed for that. The current behavior in my eyes is buggy! A solution is available in the backup interface and the VACUUM INTO statement. 2021-03-12 08:30 • Reply: How to READ and WRITE an image to an SQLITE BLOB FIELD (artifact: 9ae1d26e28 user: knu) I don't have any working code and this is not the place to discuss it. A web search for "PHP sqlite database store image file tutorial" returns quite a few examples. Many are about MySQL, but the mechanisms are mostly the same. A PHP forum or usenet group would be a better place to ask. 2021-03-11 21:06 • Reply: How to READ and WRITE an image to an SQLITE BLOB FIELD (artifact: 6167f4d8bf user: knu) The reference material says: 7.3. File I/O Functions The command-line shell adds two application-defined SQL functions that facilitate reading content from a file into a table column, and writing the content of a column into a file, respectively. So, those functions are part of the SQLite shell, and are not in the SQLite library by default. The SQLite library that PHP offers will not have them either, because it doesn't need them. You will have to use PHP functions to read the image into memory and bind it as a BLOB to your prepared INSERT statement, and the other way around for SELECT. 2021-02-27 12:48 • Delete reply: BUG Precompiled sqlite shell for windows can't open filenames with unicode chars (artifact: 4ac1166b90 user: knu) Deleted 12:37 • Reply: BUG Precompiled sqlite shell for windows can't open filenames with unicode chars (artifact: da23e11181 user: knu) But I'm using a old version of windows, which doesn't have well support of 'chcp 65001', We can't solve that for you. Codepage 65001 was already available in Windows XP. This article may help you. and I'd like drag database to the icon of sqlite.exe. It would be appreciated if precompiled sqlite shell have native unicode support. As far as I know, the precompiled shell has native unicode support, but it depends on the Windows "terminal". Try this: Make a new shortcut (icon) which does not point to sqlite3.exe, but to a .cmd file that you write yourself, which sets up the environment for sqlite. Something like: @echo off chcp 65001 disk:\path\to\sqlite3.exe "%1"  (untested) 2021-02-10 12:45 • Reply: SAVEPOINTS (artifact: 4c7e9a3c76 user: knu) According to the syntax diagram for CREATE TRIGGER, SAVEPOINT is not a supported statement. You can make a trigger execute conditionally with the WHEN expr syntax. Also, RAISE() can influence the result of the statement that caused the trigger to fire. By the way, your code example does not represent a trigger definition, and it is unclear what problem you experience. 2021-01-25 15:35 • Reply: external pages or resources returned a 403 HTTP status code (artifact: 8ac4e6ed74 user: knu) I have a similar construction, but I generate the site from a local clone of the sqlitedoc repository. Then I run a postprocessing script over the generated html pages to replace any references to sqlite.org with links to my local site. 2021-01-24 22:42 • Reply: external pages or resources returned a 403 HTTP status code (artifact: 1a528b630f user: knu) Yes, sqlite.org has protections in place to prevent bots from (recursively) iterating over all links in the web site. In my opinion, your tool should not try to follow external links. Any content of sqlite.org is not part of your website and should not be analyzed by your tool. It is perfectly fine to have hyperlinks to sqlite,org on your site, and your users are free to click them. But the tools should not do that. Just my two cents. 08:51 • Reply: Cannot change my email settings (artifact: 4c4bb3b65c user: knu) Other forums that I use allow me to request email notifications of replies to any thread that I have posted to or replied to. I use filters in my email client whenever I need that. The forum does not need to know which posts I have read to do this: it only needs to know which threads I have posted or replied to, which can be found by a search. The forum software is discussed on the fossil forum . Your patches might be considered, if processor and I/O load are not excessive. 2021-01-23 15:23 • Reply: sqlite3_exec: the 3rd argument (artifact: f5e410ce9a user: knu) Please refer here for my code (also, note the response/advice the callback interface is rarely the best way to do something even in C. That stackoverflow post only shows the function headers, not the actual processing code. 2021-01-08 15:50 • Reply: Additional Delimiter Support for the CSV Virtual Table Module (artifact: ef8b7677b5 user: knu) Other separators are supported by the sqlite3 shell. Consider this script: #!/bin/bash set -x # echo commands rm -f test.db* test.csv # clean start # create colon delimited input file cat >test.csv <connect("dbi:SQLite:$dbname","","",RaiseError=>1,PrintError=>1,sqlite_unicode=>1});$dbh->exec("PRAGMA case_sensitive_like = 1;"); $sth=$dbh->prepare(qq{select artists.name,artists.artistid,cds.genre from artists,cds where artists.artistid = cds.artistid and name like ? group by name,artists.artistid,cds.genre order by artists.name;}) or die "Can't prepare statement: $DBI::errstr\n";$sth->execute('$ArtistLetter%') or die "Can't execute statement:$DBI::errstr\n"; warn $DBI::errstr if$DBI::err; while (@records=\$sth->fetchrow_array()) { -- Regards, Kees Nuyt  2020-10-14 13:37 • Reply: Lack of abstraction ability in SQLite (artifact: a1b9ccc564 user: knu) There is a way to add custom functions via the C API but no way to write custom functions in SQL (to reuse commonly used code, etc.) or a built-in procedural language. Database procedures are especially useful in client/server database models, where they avoid the dataflow bottleneck between client and server by running procedures in the server. Dataflow between a host program and the SQLite library is orders of magnitude faster. Think of SQLite not as a replacement for Oracle but as a replacement for fopen() . Reuse of commonly used code can easily be obtained by using functions or macros in the host language, or by using some other macro processor (m4, anyone?). There are virtual tables but no way to create writable views (creating triggers over views is impossible) You can create INSTEAD OF triggers on views and make them writable that way. There is a bytecode engine with powerful features such as coroutines but it lacks a stack (to have reentrancy, continuations, and abstractions) and is not exposed as an API to applications. It is good enough to serve its sole purpose: a database engine. It is, and should be, a black box, only exposed through higher abstractions like the C-API and SQL. There is no way to create a "virtual database" that handles reads and writes its own way. Couldn't that be achieved with virtual tables, and a :memory: database for just the schema? The C API is lacking as well: There is no way to create and register new storage engines. ... This makes it impossible for SQLite to read from and write to CSVs, Excel files, etc. without the special "virtual table" feature. (Ideally, the "virtual table" feature shouldn't be special.) IMHO virtual tables are not more special than alternative storage engines. And good luck implementing an Excel storage engine. SQLite can be used the other way around, as a storage engine for a spreadsheet program, e.g. with an ODBC driver. SQLite doesn't support the CREATE PROCEDURE syntax even just to pass the code to an external interpreter to run. SQLite intents to replace fopen() in order to manage a structured data storage, accessible with SQL. As a database engine library, the expectation is that all procedural code is handled by the host program. I think adding these features to SQLite, which is already modular by design, isn't difficult. ... I think the strength of SQLite is that it is faithful to its scope and only implements features that can be maintained by the small team. SQLite is a modular, well-designed, extensible data storage and processing library I fully agree. and if it is a little bit more modular and extensible it would take over the world. It already has taken over the world. Anyone is free to fork the code and add features, and people have done that to some extent, but the results were never as successful as core SQLite itself. However, I understand your enthusiasm about the concept, quality of implementation, and excellent support for SQLite, and your desire to apply that quality to a bigger scope. Just my EUR 0.02 -- Regards, Kees Nuyt `