SQLite Forum

\n breaks reading from SQLITE3
Login

\n breaks reading from SQLITE3

(1) By anonymous on 2021-12-17 22:33:52 [link] [source]

In SQLITE 3.37.0 we are able to write correctly text strings containing the \n line to a text field

When we read it though, everything breaks, I guess since Sqlite3 thinks these newlines are different records ?

Do we need to covert each text string that contains \n before adding a record and then convert it back after we read it ?

(2) By Simon Slavin (slavin) on 2021-12-18 00:28:50 in reply to 1 [source]

SQLite has no problem with the newline character inside test strings. It doesn't even notice that its an unusual character.

You have one of two problems. Either the correct string is not being stored in the database, or the correct string is being stored, but your process of reading it back out is returning an incorrect string to your program. You can use the SQLite Command Line Shell to find out which of these is happening. The Command Line Shell shows the strings exactly as they are stored in the database.

https://sqlite.org/cli.html

How are you calling SQLite ? Are you using the C API ? Or are you using a library for your favourite programming language ? If so, which language and library are you using ?

(3) By anonymous on 2021-12-18 01:11:11 in reply to 2 [link] [source]

I am on ubuntu using the terminal

sqlite3 /product.sqlite " SELECT title,description,price FROM listings "

and I get

longhat|this is a long hat etc...|5
shirt|this is a large shirt...|7
pants|wide at the bottom|15
belt|the material is shiny|8

records are fields separated by

|
and records are separated by the
\n
newline char so when there is a \n inside the text field, sqlite seems to think it's a new record ?

(4) By Larry Brasfield (larrybr) on 2021-12-18 03:11:05 in reply to 3 [link] [source]

so when there is a \n inside the text field, sqlite seems to think it's a new record ?

The CLI is simply emitting the record contents as you have specified. It's not that the tool (aka 'sqlite3', 'CLI' or 'shell') thinks it's a new record; it's that it conceptualizesa its job as getting field content output with the field and record separators you have specified with the .separator command or allowed to retain default values.


a. We may attribute the tool's thought processes to its authors' intention.

(5) By Harald Hanche-Olsen (hanche) on 2021-12-18 10:08:06 in reply to 3 [link] [source]

To get data out of your database using sqlite3, you just need to use a more robust format, such as csv: Either one of

sqlite3 -csv /product.sqlite " SELECT title,description,price FROM listings "
sqlite3 /product.sqlite ".mode csv" " SELECT title,description,price FROM listings "

should produce a csv file that can be reliably read by any conformant csv reader, including sqlite3 itself.

Most of the other output formats are intended for human consumption, not for computer programs to read.

(8) By anonymous on 2021-12-18 16:50:50 in reply to 5 [link] [source]

thanks a lot!!!

(6) By Warren Young (wyoung) on 2021-12-18 11:27:50 in reply to 3 [link] [source]

In addition to the other answers, I have to ask, what did you expect to see instead? An actual "\n" pair of characters?

Realize that the C compiler turns "\n" inside a string into a byte called a newline — value 10 decimal — on ASCII machines, and it is this byte that SQLite is storing for you and faithfully retrieving. The "\n" pair only exists in the program's source code.

(7) By Simon Slavin (slavin) on 2021-12-18 13:33:32 in reply to 3 [link] [source]

I'm confused about what you mean when you write "everything breaks". What is broken in what you just showed us ?

If you put a return character inside one of the text fields. You should find that it gets converted to a return character and will be shown as such in the output. You do need to be very sure that some part of your input process converts the two characters 'n' into a return character. There is no part of SQLite which does this, it has to be done in your code.

it might help you understand what you're seeing if before the SELECT line you enter the command '.mode table'. I'm not sure how you'd to that in whatever Linux shell you're using, but it should work if you type it in an interactive session.

(9) By anonymous on 2021-12-18 17:06:19 in reply to 7 [link] [source]

longhat|this is a long hat and this is also a text|5\n
shirt|this is a large shirt and an example|7\n
pants|wide at the bottom|15\n
belt|the material is shiny|8\n

the example above is what SQLITE3 outputs to the shell. Fields separated by the "|" char and "\n" at the end of every record (I spelled out the newlines at the end of every record for this example)

now let's say the second records has a "\n" into the second field between shirt and and

longhat|this is a long hat and this is also a text|5\n
shirt|this is a large shirt\nand an example|7\n
pants|wide at the bottom|15\n
belt|the material is shiny|8\n

now this is considered a 5 records output because my script thinks the \n is a new record

longhat|this is a long hat and this is also a text|5\n
shirt|this is a large shirt\n
and an example|7\n
pants|wide at the bottom|15\n
belt|the material is shiny|8\n

that's all

I am now using CSV output suggested by hanche and it's working perfectly !

thank you

(10) By Simon Slavin (slavin) on 2021-12-18 21:58:54 in reply to 9 [link] [source]

Okay. So you're talking about the SQLite3 Command Line Shell, not sqlite3 itself.

You can change the characters that program uses using various dot commands. See the documentation at

https://sqlite.org/cli.html#changing_output_formats

(11) By anonymous on 2021-12-19 02:29:12 in reply to 10 [link] [source]

that's great I didn't know that thank you!!