When I use the option data=, what is the line delimiter when I have multi lines?

I tried

CREATE VIRTUAL TABLE temp.t2 USING csv(data='name,salary;a1,100',header);

No errors are reported BUT temp.t2 contains no rows; I suspect that ; is NOT a valid line delimiter.

A new-line character, "char(10)".

Using V3.40.1 on Windows 11, I tried

CREATE VIRTUAL TABLE temp.t2 USING csv(data='name,salary' || Char(10) || 'a1,100',header);

No errors reported but no rows in temp.t2.

What am I missing?

The argument to data= is a constant string, not an expression.

sqlite> CREATE VIRTUAL TABLE temp.t2 USING csv(data='name,salary
'  ...> a1,100',header);
sqlite> select * from t2;
┌──────┬────────┐
│ name │ salary │
├──────┼────────┤
│ 'a1' │ '100'  │
└──────┴────────┘

So, I cannot use this hint.

My original question was When I use the option data=, what is the line delimiter when I have multi lines?

The chances of inputting several rows of values without any error at the ...> prompt (as in your solution) is virtually nil.

One way out is to write the (SQL) expression with line feeds after each row's data to a file and then use the .read to execute the file's content.

But if I have to create a file to be able to use data = with multiple rows, I may just as well forget the data= option and simply use the filename= option.

Interactively, that is, in the CLI, the data= option is usable with just one row of data and without headers. I am wondering why this option exists at all!

Since you are compiling and loading an extension, you could use my VSV extension, which is quite similar (read the code header for usage instructions). It would be able to parse your original example thusly:

sqlite> CREATE VIRTUAL TABLE temp.t2 USING vsv(data='name,salary;a1,100',header,rsep=;);
VM-steps: 41
Run Time: real 0.002 user 0.000000 sys 0.000000
sqlite> select * from t2;
┌──────┬────────┐
│ name │ salary │
├──────┼────────┤
│ 'a1' │ '100'  │
└──────┴────────┘
VM-steps: 17
Run Time: real 0.003 user 0.000000 sys 0.000000
sqlite>

You can get the code for vsv.c from http://www.dessus.com/files/vsv.c

vsv.c is an enhancement of the csv.c extension allowing for the return of data values with affinity applied, validation of text (UTF-8 only) encoding, differentiating NULL and EMPTY fields, and arbitrary field and record separator characters.

Since you could specify affinity, it could return salary as NUMERIC instead of TEXT like the CSV extension does:

sqlite> CREATE VIRTUAL TABLE temp.t2 USING vsv(data='name,salary;a1,100',header,rsep=;,affinity=numeric);
VM-steps: 41
Run Time: real 0.002 user 0.000000 sys 0.015625
sqlite> select * from t2;
┌──────┬────────┐
│ name │ salary │
├──────┼────────┤
│ 'a1' │ 100    │
└──────┴────────┘
VM-steps: 17
Run Time: real 0.002 user 0.000000 sys 0.000000
sqlite>

Since you are compiling and loading an extension, you could use my VSV extension, which is quite similar (read the code header for usage instructions)

Thanks Keith.

It would be able to parse your original example

It does; my session:

sqlite> DROP TABLE IF EXISTS temp.t1;CREATE VIRTUAL TABLE temp.t1 USING vsv(data='name,salary;a1,100;a2,900;a3,67',header,rsep=;,affinity=numeric);SELECT * FROM temp.t1;
name  salary
----  ------
a1    100
a2    900
a3    67
sqlite>

To compile your extension I made 2 changes:

1. In order that I remember where I got the source from, I modified line 10 from ** to ** http://www.dessus.com/files/vsv.c
2. I had to modify line 219 from #include <sqlite3ext.h> to #include "sqlite3ext.h".

I am wondering how I will know if vsv.c has changed! Would I be right to assume that http://www.dessus.com/files/vsv.c will always be the most recent version?

Next, I need to study the header, as you've suggested, and makes some notes for posterity.

In order that I remember where I got the source from, I modified line 10 from ** to ** http://www.dessus.com/files/vsv.c

I added that to the header comments.

I had to modify line 219 from #include <sqlite3ext.h> to #include "sqlite3ext.h"

That would be compiler and settings variable. "sqlite3ext.h" would (ought) mean "in the current directory" whereas <sqlite3ext.h> would mean "in a directory in the include path". I'll not change that otherwise it would not build as an extension for me.

I am wondering how I will know if vsv.c has changed! Would I be right to assume that http://www.dessus.com/files/vsv.c will always be the most recent version?

The last meaningful change was when the last change was made to the csv.c source on which it is based. Somewhat more recently I added with an #if 0 wrap around to the encoding validator the ability to validate longer encoded byte sequences for UTF-8 (ie, from 5 to 8 bytes, rather than the current 4).

Subsequent changes would be reflections of changes made to csv.c.

You need quotes if the string contains something that the sqlite3 parser thinks is a token separator character (such as a comma (,)). Apparently a colon (:) is also recognized by the parser as a token separator for something. So too is a backslash (\).

It never hurts to surround a string in quotes. A filename containing : , \ will also need to be quoted though if it does not contain those characters quoting is unnecessary.

Note that the default operation of vsv.c is the same as csv.c -- assuming that you do not specify any options that are not in common between the two.

As to the purpose of data=STRING is to allow that specification. It is no different from any other DDL in that it requires constant arguments.