Elements: periodic_table sqlite example in User Guide

[jazper]

I have never been able to get this to work. After tweaking the elements string to ensure there are 11 items in each line (I take out the ellipsis in the example, and shorten the list to 5 lines just for testing), it never has yet inserted the elements into sqlite3, yet it also does not report an error.

Today I removed the

Code: Select all

(and 

after

Code: Select all

(if not (find "t1" (sql3:tables)))

to arrive at this:

Code: Select all

(if (not (find "t1" (sql3:tables)))
   (create-table)
   (init-table))

.
Now, for the first time ever after tens of attempts, it inserts the elements to sqlite3 just fine, but I get a repl error message:

Code: Select all

inserted element 1 1.0079 Hydrogen H -259 -253 0.09 0.14 1776 1 13.5984
inserted element 2 4.0026 Helium He -272 -269 0 0 1895 18 24.5874
inserted element 3 6.941 Lithium Li 180 1347 0.53 0 1817 1 5.3917
inserted element 4 277 Hassium Hs 0 0 0 0 1984 8 0
inserted element 5 268 Meitnerium Mt 0 0 0 0 1982 9 0

ERR: list index out of bounds in function int
called from user defined function sql3:sql
called from user defined function init-table

even though the insert went fine.

Testing the indices in the repl, they look fine to me. Can anyone spot why I get this error message? Here follows the entire program:

Code: Select all

(set 'elements [text]1 1.0079 Hydrogen H -259 -253 0.09 0.14 1776 1 13.5984
2 4.0026 Helium He -272 -269 0 0 1895 18 24.5874
3 6.941 Lithium Li 180 1347 0.53 0 1817 1 5.3917
4 277 Hassium Hs 0 0 0 0 1984 8 0
5 268 Meitnerium Mt 0 0 0 0 1982 9 0
[/text])

(module "sqlite3.lsp")

(if (sql3:open "periodic_table") 
   (println "database opened/created")
   (println "problem: " (sql3:error)))

(set 'column-def "number INTEGER, atomic_weight FLOAT, element TEXT, symbol TEXT, mp FLOAT, bp FLOAT, density FLOAT, earth_crust FLOAT, discovered INTEGER, egroup INTEGER, ionization FLOAT")

(define (create-table)
 (if (sql3:sql (string "create table t1 (" column-def ")"))
    (println "created table ... OK")
    (println "problem " (sql3:error))))

(define (init-table)
 (dolist (e (parse elements "\n" 0))
 (set 'line (parse e))
 (if (sql3:sql 
  (format "insert into t1 values(%d, %f, '%s', '%s', %f, %f, %f, %f, %d, %d, %f);" 
    (int (line 0))
    (float (line 1))
    (line 2) 
    (line 3) 
    (float (line 4)) 
    (float (line 5))
    (float (line 6))
    (float (line 7))
    (int (line 8))
    (int (line 9))
    (float (line 10))))
  ; success
  (println "inserted element " e)
  ; failure
  (println (sql3:error) ":" "problem inserting " e))))

(if (not (find "t1" (sql3:tables)))
   (create-table)
   (init-table))

(define (query sql-text)
 (set 'sqlarray (sql3:sql sql-text))    ; results of query
 (if sqlarray
   (map println sqlarray)
   (println (sql3:error) " query problem ")))

[cormullion]

Coincidentally, I uploaded an old test example this morning, following the forum posts yesterday...

http://github.com/cormullion/newlisp-pr ... -table.lsp

and it works on my machine. Make sure the directory is writeable before you start - (change-directory "/tmp") for example...

[cormullion]

I think this is your problem:

Code: Select all

5 268 Meitnerium Mt 0 0 0 0 1982 9 0
[/text])

Spot the empty line at the end? If you delete that, it works. Perhaps the parse routines need tightening up... ;)

[jazper]

Thanks, Cormullion. Snap. After closing down, I realised that empty line was probably it, but you got there before me!

I will try your fuller example.

[jazper]

I ran the fuller example, but it - first it reports this:

Code: Select all

create table t1 (number INTEGER, atomic_weight FLOAT, element TEXT, symbol TEXT, mp FLOAT, bp FLOAT, density FLOAT, earth_crust FLOAT, discovered INTEGER, egroup INTEGER, ionization FLOAT);
created table  ... Ok

then comes up with this:

Code: Select all

ERR: data type and format don't match : (float (line 4))
called from user defined function sql3:sql
called from user defined function init-table

I will have to check the element rows to see if they all line up, or whatever.

[cormullion]

Since it works for me, there could be some platform-dependent issues. The init-table function was just a quick hack to fill a database. If it's causing you problems, you should debug the line and item parsing code until you find the error. Sorry that you're finding issues everywhere today.. :(

[jazper]

Well, thanks for your attentions, Cormullion - your fuller program works straight off on my Win 76 64 bit setup - I commented out the chdir to /tmp line (too impatient to go creating it), but that is the only change I made. All element data was inserted, and all the queries worked.

The attempt made about an hour ago was on an Ubuntu (12.10) 64 bit setup. I did not think to check permissions for /tmp, just that it is there, but I checked everything else out. Perhaps it is, as you say, platform specific that the same code doesn't work from Win 7 to Linux. I don't know: I know the latter is more particular about file and folder permissions. It occurs to me it could be line-end problems, but newLISP wouldn't install without libreadline5 (I think), and I thought that would have fixed that sort of problem.

Onward ...

[rickyboy]

I tried this on an Ubuntu 12.04 box and I got no errors.

I started with a fresh box, built newlisp 10.4.5. from sources, and cloned cormullion's repo to get the test driver.

Well, that's just one data point. I'll get my grubby little hands on a 12.10 box in about 6 hours or so, and I'll let you know what I found with that. At any rate, I'm still interested to hear what becomes of jazper's debugging. Good luck, jazper!

[jazper]

I ran Cormullion's elements.lsp code under Win XP at work: it ran without a hitch. That's Windows 2, Ubuntu 0.

Sigh. I foresee some bloodshot eyes ahead, trying to figure out why it will not work under Ubuntu 12.10.

I built newLISP from source for Ubuntu 12.04 64 bit around July 2012, after a prompt from Lutz on this forum - I recall having some difficulties, but eventually succeeded (I know I wrote about it somewhere here). When I get home, I will check the newLISP version, because I have not updated to a newer version since 12.04 - what I have is still in place after upgrading Ubuntu 12.04 to 12.10 online.

[jazper]

I tested Cormullion's program on a distro named Peppermint Ice, running on an old Acer Aspire One Z150. It ran without problems. Peppermint Ice is an Ubuntu lightweight spin, so I guess we could change the score to Windows 2: Ubuntu 1.

Without tracing and debugging, I am pretty sure this is a "64 bit" problem. I can't find any bug reports on Ubuntu forums about it not working, so I guess I am going to have to finally get my head around tracing and debugging line by line. Been shying away from this for a long time :(

[cormullion]

For all this investigative work, perhaps you'll be awarded the Sleuth of the Week Sherlock Holmes Matching Hat, Pipe, and Hypodermic Syringe kit... :)

[Lutz]

On Mac OSX this: https://raw.github.com/cormullion/newli ... -table.lsp runs fine on both and 32-bit and 64-bit. But there the same libsqlite3.0.dylib works for 32-bit and 64-bit. At least on OSX, I would get an error message when trying to import a library with the wrong bit'ness and I guess the same would be true on Linux. Many Linux distributions come with two sets of libraries for 32 and 64-bit in two different paths.

Also, I don't think the libreadline has anything to do with it. This library is exclusively used on the command-line (REPL) but not by newLISP's primitive read-line which uses a lower level I/O.

[jazper]

Lutz got there before me - I did suspect libreadline, so that is now ruled out, but the fact remains that I did build my Ubuntu 64 bit newLISP from source myself, and I did encounter problems along the way. So, I thought, it's as likely as anything else that I made a botch job of it.

So, I just ran nearly all of the demos. Ubuntu crashed on font-demo.lsp, but I couldn't get it to crash again, so I think newLISP was probably built alright: it's almost certainly elements.lsp code that's tripping up. Sigh.

[jazper]

Hey, Cormullion, you got a chuckle from me there. But, if I earn any a nickname from this, it'll come from a different author, the one who wrote about Sheerluck Holmes.

[rickyboy]

OK, I tried cormullion's test on an Ubuntu 12.10 box. Works just fine. No errors. Sorry, jazper, you'll have to debug. There doesn't seem to be an intrinsic issue with the operating system/platform that is causing your issue. So, score 2 more points for Ubuntu because I got 12.04 running yesterday too (you forgot that in your score).

Funnily, a find of the libraries on my 12.10 system is different than my 12.04.

Code: Select all

rick@waylon:~$ find /usr/lib -name libsqlite\*
/usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6
/usr/lib/x86_64-linux-gnu/libsqlite3.so.0
rick@waylon:~$ ls -l /usr/lib/x86_64-linux-gnu/libsqlite3.so.0*
lrwxrwxrwx 1 root root     19 Oct 31 23:25 /usr/lib/x86_64-linux-gnu/libsqlite3.so.0 -> libsqlite3.so.0.8.6
-rw-r--r-- 1 root root 692984 Jun 14  2012 /usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6
rick@waylon:~$ 

(This time I had to set a symlink, a la bairui, for libsqlite3.so, since it did not exist on this box. I chose to put it in /usr/local/lib.) BTW, precisely speaking, my 12.10 system is a Lubuntu system.

Well, here's another data point.

[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 all

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 all

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 all

(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... :)