SQLite Forum

Timeline
Login

12 forum posts by user EricTsau

2021-11-28
07:28 Reply: Feature Request split string table-valued function (artifact: fea93907b3 user: EricTsau)

You can adapt the answer to this question to your needs. https://stackoverflow.com/questions/24258878/how-to-split-comma-separated-value-in-sqlite/32051164#32051164

WITH split(word, str) AS (
    -- alternatively put your query here
    -- SELECT '', category||' ' FROM categories
    SELECT '', 'Auto A 1234444'||' '
    UNION ALL SELECT
    substr(str, 0, instr(str, ' ')),
    substr(str, instr(str, ' ')+1)
    FROM split WHERE str!=''
) SELECT word FROM split WHERE word!='';

Output is as expected:

Auto
A
1234444
2021-10-11
12:44 Reply: x86_64/amd64 sqlite3 binaries (artifact: 6ce1a0ae2a user: EricTsau)

At this rate just download the x64 linux binary and use that as your sqlite shell.

$ fossil sql --no-repository SQLite version 3.37.0 2021-07-21 15:42:05 Enter ".help" for usage hints. sqlite>

12:41 Reply: x86_64/amd64 sqlite3 binaries (artifact: 854f2926bb user: EricTsau)

It is strange, if you take a look at fossil https://fossil-scm.org/home/uv/download.html there is only a 64bit linux binary for download. A 32bit linux binary is noticeably absent.

Windows has both 32 and 64 bit for download.

03:53 Post: Updates to 22.1. The --safe command-line option (artifact: a41024ded6 user: EricTsau)

In the draft docs, it makes reference to read_file(), and write_file() which should be readfile() and writefile().

Also on the same note is .shell disabled in --safe?

2021-08-06
05:09 Post: SQLITE_NOTICE(283): recovered 2 frames from WAL file /home/hwaci/fossil/sqlite.fossil-wal (artifact: 4483ea70d5 user: EricTsau)

I was googling something sqlite related, I noticed every time I visit a http://www.hwaci.com/cgi-bin/sqlite/ link there is a

SQLITE_NOTICE(283): recovered 2 frames from WAL file /home/hwaci/fossil/sqlite.fossil-wal

warning at the top of the page on the first visit. Is this normal behaviour?

Refreshing the page makes it disappear.

If you google inurl:http://www.hwaci.com/cgi-bin/sqlite/info/ and visit the links you will see it in effect.

2021-08-04
07:32 Post: CAST type-name is optional (artifact: 527bd45352 user: EricTsau)

select typeof(cast('42' as)); integer

It appears that the type name passed to cast() is optional, however the docs don't reflect this, so can the cast railroad at https://www.sqlite.org/lang_expr.html be amended with an arrow from AS to ) bypassing type-name to show that it is optional?

2021-06-21
02:34 Post: Non-existent function lsname() in cli (artifact: 4cb73f3c26 user: EricTsau)

Hi,

In 14.6.1 of the cli documentation theres a reference to a lsname function, which does not exist in the cli shell.

SQLite version 3.35.5 2021-04-19 18:32:05 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> select * from lsname(); Error: no such table: lsname

https://sqlite.org/cli.html

2021-04-04
12:32 Reply: SQLite Plus: all the missing SQLite functions (artifact: 9eeb7fa313 user: EricTsau)

That's a nice project but sooner or later you'll be asked to rename it, see what happened to SQLite database browser, now Database browser for SQLite. https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg85627.html

2021-03-11
23:10 Post: Missing SQLITE_OMIT_POPEN guard (artifact: 2a527eadf8 user: EricTsau)

I'm attempting to compile shell.c to webassembly with SQLITE_OMIT_POPEN however it misses one popen on line 19058 in shell.c, which causes popen to be called regardless of defining SQLITE_OMIT_POPEN.

    if( azArg[1][0]=='|' ){
      p->in = popen(azArg[1]+1, "r");
      if( p->in==0 ){
        utf8_printf(stderr, "Error: cannot open \"%s\"\n", azArg[1]);
        rc = 1;
      }else{

The other two calls were guarded.

#ifdef SQLITE_OMIT_POPEN
      raw_printf(stderr, "Error: pipes are not supported in this OS\n");
      rc = 1;
      p->out = stdout;
#else
      p->out = popen(zFile + 1, "w");
2020-12-26
08:04 Reply: 3 incorrect tables created during import (artifact: 126c792c50 user: EricTsau)

Thanks for the in depth explanation, I understand now.

2020-12-25
20:28 Reply: 3 incorrect tables created during import (artifact: 4af0fe7c45 user: EricTsau)

No that doesn't explain importing into a temporary table, the table doesn't need to be virtual and headers are off.

The docs say: when the table already exists, every row of the CSV file, including the first row, is assumed to be actual content.. I've created a temporary table first so the problem is .import can't import into a temporary table, instead creating another "temp.files" table.

In the below example file1 and file2 should be in the temporary files table under column file.

$ sqlite3 
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .headers off
sqlite> .mode csv
sqlite> .shell echo file1 > import.csv
sqlite> .shell echo file2 >> import.csv
sqlite> create table temp.files(files);
sqlite> .import import.csv temp.files
sqlite> .mode line
sqlite> select * from temp.files; -- this should show file = file1 and file = file2
sqlite> select * from "temp.files"; -- this should not exist
file1 = file2
2020-12-22
20:54 Post: 3 incorrect tables created during import (artifact: 2fbb8f94fa user: EricTsau)

I'm trying to import a csv file into a existing temporary table, however instead of that happening two empty tables are created in addition to a third table with the first row as the header, none of them being the table I need with the first line as a row not the column name.

$ cat import.sql
.mode csv
.shell ls > files
create virtual table temp.files using fts5(file);
.import files temp.files
.mode col
select * from files;

$ sqlite3 '' .read import.sql
Usage: .read FILE
$ # no output
$
$ sqlite3 -init import.sql 
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
sqlite> select * from files;
sqlite> select * from temp.files;
sqlite> select * from "temp.files";
... -- output with first row as header