Elements: periodic_table sqlite example in User Guide

[jazper]

Thanks rickyboy for doing the Ubuntu 12.10 test.  I hope to get around to debugging today.  Extra points for Ubuntu duly noted :)

[jazper]

Well, what do you know?  The code, of course (since it works for everybody but me) is fine, so I wanted to home in the INSERT statement.  It turned out I could not fine tune the debugger well enough to access what was really being produced as Sqlite's INSERT string.  So I debugged after each appended, pasted statement in  a new script file.  That way, stepping through many instances of problems, all with the same format of

Code Select Expand

ERR: data type and format don't match : (float (line 7))
(most were at (line 4), the insert statement was finally revealed in all its glory::

Code Select Expand

insert into t1 values ('85','210,000000','Astatine','At','302,000000','337,000000','0,000000','0,000000', '1940', '17', '9,000000');.

As you can see, it's my fault for living in South Africa!  The problem here is that Ubuntu is technically correct:  the locale setting specifies a comma as decimal point separator, and that is what finds itself into this INSERT statement. Given its South African origins, it would be surprising for Ubuntu to get this wrong, but the problem is that Sqlite3 here doesn't accept a comma for a decimal point.



It is very easy to change decimal point settings in Windows through Regional Settings in Control Panel.  Ubuntu doesn't make it quite so easy:  Googling reveals that one must edit a "locale" file, found in /etc/default/locale.  I suspect there is an easy way to do this, and will report back about that, but for now, the program will probably work if I just set my locale to England.  I am not going to do that:  I'd rather leave my settings as is.  



South African, and other users with decimal points set to comma, should be aware of it though, and either store numbers as strings in Sqlite3, using code to convert to numbers and back, or change their locale to suit Sqlite3.  



So how is Ubuntu scoring on this?  A point for correct SA number format, or a fail for messing up Sqlite3?

[jazper]

In the end I had to have a go, and just to confirm:  Changing my language from English South Africa to English GB solved the problem.  Raw code copied from Cormullion's link worked without a hitch.  



This is a real problem for South African Ubuntu users.  Wikipedia notes (from the official government style guide) that the comma is "officially" used.  However, it is not actually used at all nowadays - I worked in a Treasury office for two years six years ago, and never saw a comma used as a decimal point.  I don't know what people are being taught in school.  



At least we can know now that if the newLISP user

• lives in South Africa
  
  Uses Ubuntu
  
  with Ubuntu set to EN ZA

they will not get this (and probably any other Sqlite3 programs with float values) to insert values properly without changing things.



Somewhat sheepishly, I now recall that I solved this problem years ago, with exactly the same finding.  I doubt that I posted anything about it though.

[cormullion]

Congratulations!

In most parts of the Western world, you will only need to set your locale using the newLISP function set-locale.



More than half of the countries in the world use a decimal comma instead of a decimal point. newLISP will correctly read and write decimal commas when switched to the correct locale.

[jazper]

Thanks, Cormullion.  I did indeed overlook set-locale.  



So, fearlessly, Sheerluck will intrepidly try same out.  I'll report whether I turned out to be Inspector Clouseau instead.

[jazper]

I am Sheerluck still, not Clouseau.

Code Select Expand

(set-locale "en_ZA") did the trick.  Thanks again Cormullion.  I doubt I would have tumbled to that, notwithstanding all my hours spent RTFM.  That penny somehow never dropped :(

[jazper]

Apologies to all whom I involved in this, when it was caused by nothing more than my ignorance of (set-locale).

[cormullion]

No problem - it was kind of interesting. Like watching a new Olympic sport... :)