World: r3wp

:-) Do you really find anything so valuable on sqlite.com domain, 
that you need to grab their whole site? :-)

of course Louis runs into an error I've never seen before

:>)  Just tired and playing around.

sqlite.com contains a general domain site.

you probably want sqlite.org

and it downloads fine here

It does help to have the correct website.  :>)

It's working now. Thanks Henrik!

Now I've probably got my whole harddrive filled up.

A good entry point if you just want to grab the SQL syntax is: http://www.sqlite.org/lang.html

What am I doing wrong here:

rebol []
do %sqlite.r
do %rebgui.r

if not exists? %id.txt [write %id.txt 1]

db: %indodex.db
either not exists? db [
    CONNECT/create/flat/direct/format db

    SQL "create table base (id, Nama, Alamat, Telefon, Handfon, Fax, 
    Email, Tgl_Nikah, Nota)"

    SQL "create table birthdays (id, Nama, Jenis, Hubungan, Tgl_Lahir, 
    Agama, Nota)"
][
    CONNECT/flat/direct/format db
]
unless value? 'ctx-rebgui [
	either exists? %rebgui-ctx.r [do %rebgui-ctx.r] [do %rebgui.r]
]
set-colors
tab-size: 120x55
fonts: reduce [font-sans-serif font-fixed font-serif "verdana"]
do show-cc: make function! [] [
display "IndoDex Ver. 1.0.1" [
    tab-panel #HW data [
        "Add" [

            label "Title:" priority: drop-list 30 #W "Pak" data ["Pak" "Ibu" 
            "Sdr." "Sdri." "Drs." "Dr." "Tuan" "Nyonya"] 20x5
            return
            label "Nama:" nama: field 
            return
            label "Alamat:" alamat: area 50x30
            return
            label "Telefon" telefon: field
            return
            label "Handfon" handfon: field
            return
            label "Fax:" fax: field
            return
            label "E-Mail:" email: field
            return
            label "Nota:" nota: area 50x30
            return
            button "Save" [
                (id: to-integer read %id.txt)

                SQL/flat/direct {insert into base values (id, Nama, Alamat, Telefon, 
                Handfon, Fax, Email, Tgl_Nikah, Nota)}
                (write %id.txt id)
                show-text ex-status "Saved"]
        ]
        "Edit" [
            
	]
	"Search" [
	]
    ]
]
]
do-events

I'm getting this:

** User Error: SQLite no such column: id
** Near: make error! reform ["SQLite" error]
>>

This doesn't work either:


 SQL ["insert into base values (?, ?, ?, ?, ?, ?, ?, ?, ?)" ID, Nama, 
 Alamat, Telefon, Handfon, Fax, Email, Tgl_Nikah, Nota]

Try reducing the block, as in:


 SQL reduce ["insert into base values (?, ?, ?, ?, ?, ?, ?, ?, ?)" 
 ID, Nama, Alamat, Telefon, Handfon, Fax, Email, Tgl_Nikah, Nota]

Thanks, Ashley.  I just got called to dinner. When I get back I'll 
try that.

Ashley, I still can't get it to work.

Here's the latest version:

rebol []
do %sqlite.r
do %rebgui.r
unless value? 'ctx-rebgui [
	either exists? %rebgui-ctx.r [do %rebgui-ctx.r] [do %rebgui.r]
]
set-colors
tab-size: 120x55
fonts: reduce [font-sans-serif font-fixed font-serif "verdana"]

if not exists? %id.txt [write %id.txt 1]
i: to-integer read %id.txt
i: i - 1
either not exists? %indodex.db [
    CONNECT/create %indodex.db

    SQL "create table base (ID, Title, Nama, Alamat, Telefon, Handfon, 
    Fax, Email, Tgl_Nikah, Nota)"

    SQL "create table birthdays (ID, Nama, Jenis, Hubungan, Tgl_Lahir, 
    Agama, Nota)"
][
    CONNECT %indodex.db
]
do show-cc: make function! [] [
set-colors
display "IndoDex Ver. 1.0.1" [
            label 16 "ID:" id: text (to-string i)
            return

            label 16 "Title:" title: drop-list 30 #W "Pak" data ["Pak" "Ibu" 
            "Sdr." "Sdri." "Drs." "Dr." "Tuan" "Nyonya"] 20x5
            return
            label 16 "Nama:" nama: field 
            return
            label 16 "Alamat:" alamat: area 50x30
            return
            label 16 "Telefon:" telefon: field
            return
            label 16 "Handfon:" handfon: field
            return
            label 16 "Fax:" fax: fax: field
            return
            label 16 "E-Mail:" email: field
            return
            label 16 "Nota:" nota: area 50x30
            return
            button "Save" [

                ;UNCOMMENT THE FOLLOWING LINES AND YOU WILL SEE THEY DON'T WORK. 
                ANYBODY KNOW WHAT IS WRONG?

                ;SQL reduce ["insert into base values (?, ?, ?, ?, ?, ?, ?, ?, ?, 
                ?)" id/text, title/text, nama/text, alamat/text, telefon/text, handfon/text, 
                fax/text, email/text, tgl_nikah/text, nota/text]

                ;SQL {insert into base values ('Pak' 'Ibu' 'Sdr.' 'Sdri.' 'Drs.' 
                'Dr.' 'Tuan' 'Nyonya' 'Jonew')}
                (i: to-integer i)
                (i: i + 1)
                (write %id.txt i)
                (i: to-string i)
            ]
            button "GUI Info [

                print [id/text " " title/text " " nama/text " " alamat/text newline]
            ]
            button "DB Info" [
                print TABLES
                SQLite/col-info?: true
                print SQL "select * from base"
                print SQLite/columns
            ]
            button "Halt" [
                halt
            ]
            button "Quit" [
                quit
            ]
]
]
do-events

Click on the <DB Info> button, and you will see that the tables have 
been created. But I haven't been able to insert any data.


Another problem is that the id field doesn't advance to the next 
number.

You have a few errors in there.


 - your values are comma seperated (e.g. use SQL reduce ["..." val1 
 val2 ...] instead

 - tgl_nikah/text appears in your INSERT but isn't present in your 
 display
	- your "DB Info" button can use ... print ROWS "base"

ID field problem can be fixed by using code like:

	...
	id/text: form i: i + 1
	save %id.txt i
	show id

No need for parenthesis.

Ashley, that works. Thanks!

Now, how can all the fields and areas be cleared in preparation for 
entering data for the next record?

Whoops. This should be in the RebGUI group, but I'm going to leave 
it here, since the script needing fixed is here.

Hi, I have a problem wheren using the ? binding feature with integer! 
values. For example:
	["select * from a where mynum  = ?" 1]
won't give an results. But
	["select * from a where mynum = ''1']
gives results.

How is integer! encoded for SQLite?

Does:

	["select * from a where mynum = ?" "1"]

work? If so, then your "integer" is in fact a string.

Another idea could be, that you are somehow mixing "raw" and "managed" 
access, this bit me once, but I think it was related to strings then.

Ashley, no this didn't worked as well. I tried it. That's really 
strange. SQLite browsers see the field as NUMERIC.

What did the statement that originally inserted the value look like? 
What refinement(s) do you use when CONNECTing? Same for both the 
INSERT and SELECT?

I have created a semicolon seperated file and imported it via the 
SQLite command line tool. All numbers where just plain included, 
not guarded by " chars.

CONNECT I just use the CREATE refinement.

I mostly use FLAT for SELECT and no refinement for INSERT.

I send you my version.

Ashley, I notice that sqlite.r value binding is missing some records 
when using LIKE.


>> sql ["select * from base where alamat like ?" "%Grand%"]   <<=====<<< 
This fails.
== "No rows selected."

>> sql ["select * from base where alamat like '%Grand%'"]      <<====<<< 
This finds a record.

It does not always fail, just sometimes. I've not yet discovered 
why.

Ok, it seems to be related to certain records. No matter what word 
I search for it is not found in certain records. So it has something 
to do with those records.

Hi, has anyone even got to the problem, where you import data into 
database, and it is corrupted? (select fails)

I am trying to analyse few sendmail logs. Our admin sent me three 
files. The first one, has those small boxes instead of newlines, 
you know it, when you try to open linux file under windows

I read all thre using read/lines, choose info I want, append it to 
resulting block. Probing block shows no defects. I believe it is 
a REBOL low level bug with some hidden chars. It happened on me in 
the past already, in different situation ...

If I import one file at a time, clear the block, then data is OK 
in sqlite, but if I append first to one block, then insert into sql, 
data is corrupted on few random places ...

following works:

;--- import log files
import-logs: does [

   ;--- pairs of incident No and incident file [[1 filename][2 filename] 
   atd.] ...

   log-files: [[1  06-12-06-7_50-7_59][2  06-12-12-15_46-15_47][3 
    06-12-13-15_29-15_31]]
   foreach file log-files [
     log-info: copy []
     log-file: read/lines file/2  
     ;print length? log-file
     foreach string-line log-file [
       line: parse string-line " "
       if line/7 == "GET" [ 

         append log-info reduce [line/1 line/2 line/4 line/8 line/11 to-string 
         file/1]
       ] 
     ] 

    SQL "BEGIN"

    foreach [date time ip-address url content-type incident-id] log-info 
    [

       SQL reduce ["INSERT INTO logs VALUES (?, ?, ?, ?, ?, ?)" date time 
       ip-address url content-type incident-id]
    ]
    SQL "COMMIT"

   ]
]

If I put SQL section out of the foreach file log-files, simply appending 
all logs at once, data is corrupted ... it is reproducable ....

hmm, it fails too ...

>> do %gotcha!.r
Script: "Untitled" (none)
Script: "SQLite driver" (5-Nov-2006)
>> sql "select * from logs"
** Syntax Error: Invalid string -- "
** Near: (line 1) È&*
6/Dec/06"

here's small package - www.xidys.com/gotcha!.zip

converting the first file (reading and saving) did not help either 
... my suspicion is, there is some bug with driver ...

>> sql "select * from logs"
** Syntax Error: Invalid integer -- 0+*
** Near: (line 1) 0+*
6/Dec/06"

later in the night, or over the weekend I will try not to use block 
syntax, but rather compose query string. All values inserted are 
strings (I tried with native rebol datatypes too)

uf, following works. Maybe it has something with my nonunderstanding 
of differences between string/non string values and how to properly 
insert them ...


 SQL s: rejoin ["INSERT INTO logs VALUES ('" date "', '" time "', 
 '" ip-address "', '" url "', '" content-type "', '" incident-id "')"]

whereas this one does not:


SQL reduce ["INSERT INTO logs VALUES (?, ?, ?, ?, ?, ?)" date time 
ip-address url content-type incident-id]