SQLite Data Base

Emile_Schwarz · July 2, 2015, 8:11am

Code used to save the data from the ListBox into a SQLite Data Base file:

SQL_Record = "INSERT INTO List (Issue, Main_Title, Release_Date, Bar_Code_Label) VALUES " +_ "('" + LB.Cell(LoopIdx,0) + "','" + LB.Cell(LoopIdx,1) + "','" + LB.Cell(LoopIdx,2) + "','" + LB.Cell(LoopIdx,3) + "')"

Code used to read data from the SQLite Database / add data into a ListBox:

LB.AddRow(OpenRS.IdxField(2).StringValue, OpenRS.IdxField(3).StringValue, OpenRS.IdxField(4).StringValue, OpenRS.IdxField(5).StringValue, OpenRS.IdxField(3).StringValue)

The ‘’ character is not displayed as is, but as a three character set <?><?><?>

A Copy of a row with this data into a TextEdit document reveal the right sentence (with the correct character). This is an Encoding problem only.

I tried:

OpenAtom = OpenRS.IdxField(5).StringValue Open.Append OpenAtom.ConvertEncoding(Encodings.UTF8)

and:

Open.Append OpenRS.IdxField(2).StringValue.ConvertEncoding(Encodings.UTF8)

then (both cases above):

OpenAtom = Join(Open,Chr(9)) LB.AddRow Open

But the text still loose an Encoding (as far as I can understand reading what I got in the ListBox).

How can I get the correct encoding ?

PS: the code comes from the Language Reference.

BTW: if youare asking yourself why I started with IdxField(2) in OpenRS.IdxField(2).StringValue it is because IdxField(1) returns the value of the Primary Key. I have nothing against that, but I only wanted to get the real data as I stored them in the SQLite data base file.

The example from Database.SQLExecute was taken to create the table.

The other example - INSERT INTO above - comes from SQLiteDatabase.

Of course, this is preliminary coding and will be changed later (once I know I can achive my needs using SQLite as a data storage): I do not even create all fields / tables for my (future) need.
One of the changes is to use the Field Names instead of IdxField().

Emile_Schwarz · July 2, 2015, 10:37am

After two hours of searches, I found here :

#define SQLITE_UTF8

But how do I use that ?

SQLExecute("#define SQLITE_UTF8") ?

A search with Google on:

site:documentation.xojo.com  #define SQLITE_UTF8

leads to advertising only.

I just tried:

theDB.SQLExecute("#define SQLITE_UTF8")

Same results in the ListBox.

Simon_Berridge · July 2, 2015, 12:20pm

Emile

From the SQLite documentation:

[code]PRAGMA encoding;
PRAGMA encoding;
PRAGMA encoding = “UTF-8”;
PRAGMA encoding = “UTF-16”;
PRAGMA encoding = “UTF-16le”;
PRAGMA encoding = “UTF-16be”;

In first form, if the main database has already been created, then this pragma returns the text encoding used by the main database, one of “UTF-8”, “UTF-16le” (little-endian UTF-16 encoding) or “UTF-16be” (big-endian UTF-16 encoding). If the main database has not already been created, then the value returned is the text encoding that will be used to create the main database, if it is created by this session.

The second through fifth forms of this pragma set the encoding that the main database will be created with if it is created by this session. The string “UTF-16” is interpreted as “UTF-16 encoding using native machine byte-ordering”. It is not possible to change the text encoding of a database after it has been created and any attempt to do so will be silently ignored.

Once an encoding has been set for a database, it cannot be changed.

Databases created by the ATTACH command always use the same encoding as the main database. An attempt to ATTACH a database with a different text encoding from the “main” database will fail.
[/code]

I think it would be used:

database.SQLExecute("PRAGMA encoding = ""UTF-8"";")

Simon.

Emile_Schwarz · July 3, 2015, 7:56am

THANK YOU Simo for your kind answer.

I started to download all pages from the SQLite web site yesterday and the url for the one you talk above is: Pragma. Because of the hot (and some other things), I was not available for reading these documentation. Fortunately, a global search was very helpful. (I will continue to dosnload the remainding of the web pages later this morning).

The most important thing (from your useful answer) is the syntax you gave. I will try to check that later today (this morning ?).

Note (before texting your solution): in the .sqlite file (on disk), the curcly simple quote is stored on three characters (as far as I can see), just like what is read from it and sent to the ListBox. For no reason, my deep feeling is the problem is elsewhere: when I copy the ListBox Row and paste it in Apple TextEdit, the string is converted and appears correctly.

Emile_Schwarz · July 3, 2015, 9:01am

SQLite Documentation

Once I ended to download the docs.html sub-files (the documentation, I started to explore more in depth the sqlite server and found Download (menu):

[b]Documentation[/b] [url=http://sqlite.com/2015/sqlite-doc-3081002.zip]sqlite-doc-3081002.zip[/url] (4.83 MiB) Documentation as a bundle of static HTML files. (sha1: 2e816999255e4fced7d93b6ad3650a361a446288)

So, if you want the docs from teh SQLite web site: download that file.

Christian_Schmitz · July 3, 2015, 10:12am

are you sure you put in your strings with known encoding?

Because SQLite is UTF-8 by default in Xojo.

Emile_Schwarz · July 3, 2015, 12:27pm

Hi Christian,

Yes. I load the text file directly into the ListBox without problem. From the ListBox (I can check a direct import too, later), I create the SQLite data base and export teh ListBox contents.
You are correct. However, I am starting to be a bit defiant from the documentation. So, I checked in the SQLite generated file the Encodings Long (4 digits value) and it is 1: the UTF-8 encoding.

With and without Simon advice.

Even when I apply an UTF-8 encoding to the string I get from the Data Base file, before I send it to the ListBox (my original post), the result appears wrong.

I will modify the SQLiteExample.xojo_binary_project example to add a text with a curly simple quote and check what happens.

It works fine there ! Unbelievable !

Now, I changed a bit my code using the example AddRow code and the bug is still here.

I found another difference between the code I tpok from the docs and the example project:
My used code (docs) use insert,
Example project use a RecordSet.

I used the code below (similar to the example) and the results are the same:

[code] Dim row As New DatabaseRecord
// ID will be updated automatically
row.Column(“Issue”) = LB.Cell(LoopIdx,0)
row.Column(“Main_Title”) = LB.Cell(LoopIdx,1)
row.Column(“Release_Date”) = LB.Cell(LoopIdx,2)
row.Column(“Source”) = LB.Cell(LoopIdx,3)
row.Column(“Bar_Code_Label”) = LB.Cell(LoopIdx,4)

  NewSQLite.InsertRecord("List", row)[/code]

Fortunately, I went to the barber shop lately and my hair are too small to be pulled by hands

I will rewrite the whole code later this afternoon (on a duplicate of the example project) and see what I get.

Emile_Schwarz · July 4, 2015, 7:53am

After writting this report, my conclusion is: the trouble is with the ListBox, not related to SQLite at all.

The “offending code” is in the While / Wend block.

[code]Dim Open_SQL_Select As String
Dim OpenRS As RecordSet
Dim OpenAtom As String
Dim Open(-1) As String

Open_SQL_Select = “SELECT * FROM List”
OpenRS = OpenSQLite.SQLSelect(Open_SQL_Select)

If OpenSQLite.Error Then
MsgBox(“Open SQLite Data Base File” + EndOfLine + EndOfLine + "SQLine Error: " + OpenSQLite.ErrorMessage)
Return
End If

If OpenRS <> Nil Then
// Adjust the number of LB Columns
LB.ColumnCount = OpenRS.FieldCount

While Not OpenRS.EOF
  // Get the Record contents
  Open.Append OpenRS.Field("Issue").StringValue
  Open.Append OpenRS.Field("Main_Title").StringValue
  Open.Append OpenRS.Field("Release_Date").StringValue
  Open.Append OpenRS.Field("Source").StringValue
  Open.Append OpenRS.Field("Bar_Code_Label").StringValue
  
  OpenAtom = Join(Open,Chr(9))
  
  // 1. Encoding is OK, but all goes to Column(0)
  // 
  'LB.AddRow Array(OpenAtom,Chr(9))
  

  // 2. Encoding is bad, but the data goes to all Columns
  // 
  'LB.AddRow Open

  
  // 3. The data goes to all Columns AND Encoding correct
  // 
  LB.AddRow Split(OpenAtom,Chr(9))
  
  // Reset the local variable
  Redim Open(-1)
  
  // Next Record
  OpenRS.MoveNext
Wend
OpenRS.Close

End If[/code]

The code above is strange, very strange. Why some lines do work and some do not ?

Of course, this code is locked to a specific design. Once it will be stabilized, I will have to modify it :(.

Once more: the code below place the text correctly, but the encoding is bad.

[code] While Not OpenRS.EOF
// Add One Row
LB.AddRow “”

  // Get the value of the just added Row
  LocRow = LB.LastIndex
  
  // Fill the Rows Cells
  LB.Cell(LocRow,0) = OpenRS.Field("Issue").StringValue
  LB.Cell(LocRow,1) = OpenRS.Field("Main_Title").StringValue
  LB.Cell(LocRow,2) = OpenRS.Field("Release_Date").StringValue
  LB.Cell(LocRow,3) = OpenRS.Field("Source").StringValue
  LB.Cell(LocRow,4) = OpenRS.Field("Bar_Code_Label").StringValue
  
  // Next Record
  OpenRS.MoveNext
Wend[/code]

And the following runs fine:

LB.AddRow "Some non ASCII: éèçàãñõ ÿ" LocRow = LB.LastIndex LB.Cell(LocRow,1) = "El ninõ is back !" LB.Cell(LocRow,2) = "Mon il"

Last piece of code:

LB.AddRow Array("Johns","Pauls","Georges","Ringos","The Beatles") LB.AddRow Array(Open(0),Open(1),Open(2),Open(3),Open(4))

Both lines add data in all Columns. The first line (The Beatles) display correctly the non ASCII character () and the next line, if there is a non ASCII character, it is NOT correctly displayed. go figure.

Last: the ParamArray entry in the Language Reference does not know anything related to ListBox; ListBox.AddRow first syntax is:
ListBox.AddRow ( ParamArray Item as String )

Emile_Schwarz · July 10, 2015, 8:35am

After so many days (nearly one week), I found where the bug lies: I created the Table and set the most important Column as INTEGER instead of TEXT.

Thank you for SQLite for FireFox to display correctly the text I wrote in an INTEGER Column (my error).

This tooks me times to see: the error is absolutely not obvious.

BTW: I even created a single window to extract Tables from a loaded SQLite file, display the selected Table shema as ListBox headings and Populate that ListBox with the selected Table Records (all RecordSet).

The next move could have been to analyse the SQLite file in more depths but I recall that long time ago (three years ?) I used that Firefox addon

The Addon for Firefox I used is:
SQLite Manager
Manage any SQLite database on your computer.
Noté 5 sur 5 étoiles (34) 195 561 utilisateurs

Thanks to all for your advices and suggestions.