SQLite-Blob: Store Multiline Text

Hi everyone,

i created a SQLite-Database with some Tables. One of the Tables, called “Notes” has a “Note”-Column of type Blob. Now i wanna save multiline Text of an Object. My ErrorLog says “Error near 45F86D03-5F21-4FEB-AAFE-2A49F02C0F26”. I think its because of the Linebreaks within the Note. How to get it work?

[code]INSERT INTO Notes (Type, LinkID, Note, AutomatedRecordID, DateCreated, DateModified) VALUES (2, ‘45F86D03-5F21-4FEB-AAFE-2A49F02C0F26’, 'Lebenspartner von Elton John

  • Nationalitt: Kanadier
  • Kanadische Krankenversicherungsnummer: 43-456-1963’, AutomatedRecordID = NULL ‘2016-08-08 00:02:27’, ‘2016-08-08 00:02:27’);[/code]
    Thanks and Greetings

EncodeBase64 before storing, decodeBase64 when retrieve ?

I store three lines (so two “new line” characters in it) in a SQLite Column, as is… without trouble(s).

Thanks Michel, its not working, because i use the new TEXT-Type.[quote=281110:@Emile Schwarz]I store three lines (so two “new line” characters in it) in a SQLite Column, as is… without trouble(s).[/quote]
Thanks Emile, how did you do this? Any sample code?

You are missing a comma:

AutomatedRecordID = NULL '2016-08-08 00:02:27'

… should be:

AutomatedRecordID = NULL, '2016-08-08 00:02:27'

Newlines:

Dim s As String = "'Lebenspartner von Elton John" + Chr(10) + "- Nationalitt: Kanadier" Dim s As String = "'Lebenspartner von Elton John" +EndOfLine.UNIX + "- Nationalitt: Kanadier" Dim s As String = "'Lebenspartner von Elton John char(10)- Nationalitt: Kanadier"

I do that like I do for a simple line, a simple string: nothing special. It worked, and so I started to think about displaying it and there too, nothing special *.

  • For displaying it: in List view, I let Xojo do the job (and so only the contents of the first line is visible), but I enlarged my TextArea in Record view (a window that displays all fields of a Record).

The client was happy with that (there is three fields with 3 lines: if there is something in the column, there will be 3 lines). A simple explanation and he will be OK.

Eli: funny, I was hearing Sleeping with the past earlier today :slight_smile:

As others have said, sql doesn’t care about line breaks. It treats them as whitespace.

Please guys use prepared statements and store TEXT as TEXT. No encodeBase64 needed.

Thanks Eli Ott. Little Comma, big effect :smiley:

Because i work with large files to parse (some with more then 1 Million lines), i decided to work with transactions. I use the SQLitePrepareStatement only, if i insert a Single record, but not if i parse a large Textfile to transfer it into SQLite.