World: r3wp

It returns info about a table, and by default returns info about 
the columns of the table, in the order they are declared in the table, 
including column names. From there, you can extract the names quite 
easily.

Yep, thanks. I missed this one.

extract/index DESCRIBE "table" 6 2

Ashley - Bobik is getting following error (not so with original sqlite3 
protocol):

>> sql "select * from kategorie"

== [[1 30 34 M30] [2 35 39 M35] [3 40 44 M40] [4 45 49 M45] [5 50 
54 M50] [6 55 59 M55] [7 60
 64 M60] [8 65 69 M65] [9 70 74 M70] [...
>> sql "select * from kartoteka"
** Script Error: Out of range or past end
** Where: sql
** Near: either all [block? v #"[" <> first s] [rejoin v] [v]
>>

DESCRIBE returns the column ID 0 based. As I map this either to block 
positions or objects and Rebol is 1 based how about adding an option 
to either get the results 0 based or 1 based as in Rebol?

hmm, it works for him via connect/direct ... what could the problem 
be?

problem found - there is some problem with empty fields .... he probably 
saved it using other driver, it did not contain NULL, it was empty 
.... so hence the crash?

hmm, detailed problem - he had zip code defined as integer in some 
sqlite editor. Then he has rebol form, with fields ... and he saved 
empty zip code field ... so he saved "" to db, where number was required 
...

how about adding an option to either get the results 0 based or 1 
based

 ... best done in your own script as that's a fairly specific and 
 [probably] uncommon requirement. Same goes for other 'one-off' requirements 
 like "all uppercase object names", or "all lowercase object names".

Does anyone understand this error?

>> sql "select * from comm"
** Script Error: Out of range or past end
** Where: sql
** Near: either all [block? v #"[" <> first s] [rejoin v] [v]
>>
>> sql "select guid from comm"

== [[h-o-h.org_20060326_182311681_3176] [h-o-h.org_20060326_182311681_7315] 
[h-o-h.org_20060326_182311701_2470] [h-o-h.org_2006032
6...
>> length? sql "select guid from comm"
== 541
>> probe tables

["comm" {CREATE TABLE comm ( guid, type, value, note, flags, keywords, 
reference, created, updated, deleted )} ;...

Furthermore, I sometimes have the problem, that errors in sql statements 
are not caught by try, if I call it from a view gui. 
Is this a known problem?

Looks like your data is tripping the driver up. Please add the following 
line:

	print mold s


prior to the "either all [block? v ...] line and post the last result 
back here.


As for the second issue, haven't experienced this myself. Do you 
have a small code snippet that exhibits the problem?

aaahhh, once again, I forgot /direct in my testing on the console

I'll try to find a minimal code to show the non-catched errors ...

Hi Ashley, while trying to find a minimal code example ... I found 
the error ... ;-) 

That's the error message ...
** User Error: SQLite SQL logic error or missing database
** Near: make error! reform ["SQLite" error]

And it was caused by:

      if string? face/user-data [
         if error? set/any 'err try [

            set pAddress-disp first rule compose [pAddress get guid = (face/user-data) 
            *]
	; rule creates an sql string and starts calls 'sql with it
	; yadda yadda yadda ...
         ]
      ][probe disarm err]

Do you find the error??? 

Somehow the [probe disarm err] block moved to the wrong if ... 


I don't know how this could trigger _this_ error, but after I moved 
the block the error has not occurred again.

can you try not to probe? Maybe printing somehow interferes with 
dll?

I have one suggestion. Trying to use sqlite for cgi, I have following 
dir structure:

\app
app.cgi

\app\system (sqlite.r, sqlite.dll, other app related "system" files)
\app\data (*.db)


I don't like sqlite driver putting .log file into caller directory 
= main app directory. Not sure where it belongs, if in \system, \data, 
or simply \log subdir, but the driver has no ability to set the path 
...

I thought that the same parameter could be used for DBs too - setting 
the path, but it is not so important, as I can connect %path-to-db/my-db.db, 
but as for logging, it just writes to "current dir"

I did following modifications to driver:

log-path: to-file copy ""

then replace/all "%sqlite.log" "join log-path %sqlite.log"


then in my cgi script I am able to do sqlite/log-path: %db/ to change 
location ...... maybe it would be usefull to even set db path and 
don't bother with paths, not sure ....

hmm, not sure I easily follow how dbs are opened in 'connect, so 
I skip the change to set path for dbs thru some variable ....

I don't understand the line: unless find first database %/ [insert 
first database what-dir], as it just changes path to first file, 
is that ok?

sqlite open command expects a fully qualified local file name ... 
the line in question prepends the supplied file name with current 
dir unless the file name is in fact a path.

ok, and when you open multiple databases as with connect [%my-db.db 
%my-db2.db] ?

my understanding is, that then only first db gets path appended?

Look about 12 lines further down in the code ... the same logic is 
applied to each db after the first.

The log-path issue is best resolved by adding a log-file word to 
the sqlite context that defaults to %sqlite.log. You can then do 
the following in your code:

	sqlite/log-file: %my-path/my-log-file.log

I did something similar, as above ....

Ashley - I have problems with writing permissions under Linux. Till 
I resolve it, I wanted to disable logging. I looked at the driver 
and I can see, there is a word - log?: false, but when you try to 
write to log, you don't test for it. Is that ok, or is that variable 
for any other purpose of logging?

OK, maybe it is just me, but I can't get enough permission for my 
CGI, even if run in -cs mode, for sqlite.log to be written to (the 
script works in console though, but I am root there, so ...). Could 
the driver be updated to work with log?: false variable? I can see 
logging on four places, but only on one place (sql function) it ever 
checks the variable ....

sqlite is cool, so imo it would be pitty if it would not easily work 
in cgi environment .... if there is something wrong with my set-up, 
then sorry.....

From the User Guide: "Every connect, disconnect, error and statement 
retry is logged to %sqlite.log. This refinement adds SQL statements 
as well. While this can be useful to monitor what SQL statements 
are being issued and what the volume and distribution is; be sure 
to monitor the size of this file in high transaction environments."


If you really don't want any log output then just direct it to /dev/null

what is log?: false good for, then?

ah, so this variable applies only to disable/enable logging of sql 
statements, right?

anyway - inability to work directly in cgi mode will drive ppl away 
from driver usage .... Logging should be disabled by default imo, 
and the name of the variable suggests that too ....

the log can't be used for rollbacks anyway, can it? (so not so important)

Hi, I know that some talk of encryption was held here some time ago, 
but currently I was asked to eventually protect sqlite data and I 
am not sure what is correct aproach. I would not go DB-as-a-file 
encryption, then "unpacking" into memory, or so. I prefer app level 
encryption, but I am not sure about searches, using LIKE directive. 
Would it work?

hmm, would be problematic ....

>> (encloak "3" "pass") > (encloak "2" "pass")
== false
>> "3" > "2"
== true

the proper encryption would have to come at lower db level, namely 
- storage level ....

that is nearly useless then, because there is no easy way of how 
to protect your data

I think that SQLite itself can be extended with encrypted fields.

hmm, author offers kind of extension for 2000USD .... there is no 
way how poor pekr would know C code to the extent of writing such 
an extension myself :-)

but I found - http://sqlite.phxsoftware.com/

I haven't given this ADO stuff a try to see if it fits my requirements. 
But will do so.

At the moment I tend to use the 2K$ extension. Yes, not quite cheap 
and I might do it myself but it will take some time too. So not being 
cheaper.

The strategy is to en/decrypt every block that gets written to disk. 
Even all B*-tree stuff etc. with this it's fully transparent to the 
database engine.

It appears that the SQLite version at Petr's link will work without 
ADO if you want to use the traditional APIs.

I can't install it, need to download .NET framework first, will do 
so at home :-)

how much slower the encrypted db is?

You can run it without the .NET framework.