World: r3wp

One strange bug remains. Occasionally %demo.r will fail (typically 
selecting rows from Items) due to garbage characters that somehow 
get inserted. I have reduced the problem down to this script:

REBOL []

unless value? 'SQLite [do %sqlite.r]

repeat cnt 10 [
	prin ["^/Run" cnt "..."]
	;	Clean up from previous runs
	error? try [delete %test.db]
	error? try [delete %test.db-journal]
	;	Create Items (1000 rows) records
	prin " create ..."
	CONNECT/flat/create %test.db
	SQL "create table t (c1,c2,c3,c4,c5)"
	prin " insert ..."
	SQL "begin"
;	loop 1000 [
	repeat z 10000 [

;		SQL reduce ["insert into t values (?,?,?,?,?)" 1 "A 1" $1 1 $1 
* 1]

  SQL reduce ["insert into t values (?,?,?,?,?)" 1 reform ["A" 1] $1 
  1 $1 * 1]
	]
	SQL "commit"
	prin " select ..."
	SQL "select * from t"
	DISCONNECT
]

quit


Running the script should cause a failure like the following within 
the first couple of runs:


Run 1 ... create ... insert ... select ...** Syntax Error: Invalid 
tag -- <C
** Near: (line 1) À<C"
>> sqlite/direct?: true
== true
>> sql "select * from t where c2 like '%<%'"
== [1 {À<C^B"} "$1.00" 1 "$1.00"]


Changing the repeat to a loop seems to shift the error, often (but 
not always) making it take more runs to materialize. Replacing the 
reform with a string will often (but not always) allow all runs to 
complete successfully. Changing the number or order of INSERTed values 
also seems to shift the error. I'm not sure whether this is a REBOL 
or SQLite library error, but any help in tracking it down would be 
greatly appreciated.

I copied the cycle to clipboard and then did [ do read clipboard:// 
] first run it was fine, the second one I got ** Syntax Error: Invalid 
string -- "

(first run of do read clipboard:// - all 10 runs were fine)

Good. You've confirmed the error is repeatable (although inconsistent).

To me this looks like either a strange character coding happens or 
that there are some 0x00 chars included, which are interpreted as 
string-end.

I think, the internal memory is mingled, either in sqlite or in rebol, 
probably in the interface by the garbage collector.

I observed already some high memory consumption, until my pc was 
blocked.

I had this already with the earlier versions of sqlitex.r after some 
errors.

I would recommend that you use deliberately some [save].

recommend that you use deliberately some [save]
 ... not sure I understand, what are you suggesting?

Also note that this problem (garbage characters on insert) only seemed 
to materialize on 0.1.7 and the change from type? to type?/word. 
Or at the very least it seems more prevalent now.

[save] I think you can mark your function with a save attribute? 
kind of like you define catch and throw? then it survives garbage 
collection or something like that IIRC ...

You can protect  your memory defined in the interface with [save]
http://www.rebol.com/docs/library.html#Garbage

Yes, in the routine spec.

ah, yes, that's it ....

The high memory consumption and crashing of an process or should 
I say silently vanishing, that I observed with this routine after 
two consecutive runnings, I saw already  sporadic with the former 
releases after some sql errors and trying to resolve a busy lock 
on the db with some tricks, for example repeating a step.

Added [save] to all struct! ... no improvement. ;)

ok, we can at least rule it out :-)

hmm, not sure it is kind of zero char string terminator, as rebol 
suggests, but strange stuff indeed and maybe a rebol bug .... e.g. 
with even new View and  Cyphre's grid I am able to get such strange 
chars in particular cells ....

there must be some leak somewhere ....

or just improper assignment, pointing to some strange location, dunno 
...

now debugger would be usefull ;-)

Bobik is just reporting to me, that without the /direct, sqlite driver 
returns string values as words, how is that?

Ashley, something is wrong maybe .... he just tried to make sqlite 
db in sqliteadmin tool ... he has id (integer) name (text) lastname 
(text) ... he inserts some values, but your driver makes name and 
lastname words, instead of strings ... while sqlite3 returns strings 
correctly ...

/direct corrects the problem, but imo /direct should not be needed? 
Imo our driver should behave consistently with a) other drivers b) 
external sqlite tools?

I mean - in regards to molding/loading string values ....

connect/create %test.db
sql "create table test (id, name, last_name)"
sql {insert into test values (1, "Petr", "Krenzelok")}
sql {select * from test}

; returns [[1 Petr Krenzelok]]   ... sqlite3 does not do that according 
to Bobik's info ... I am not sure I like it - it convert apparent 
string to word, I don't want that and I am not sure I want to use 
/direct for that, that seem like a reverse logic to me ...

ah, now I see ... and I have questions .... Ashley, when the sql 
query is not block, but a string - you send the query as-is? Why 
the difference? with ' it works, with " it does not ... can the functionality 
be made just the same? I think most ppl are used to "quote" string, 
not to 'quote it like that' .... I would prefer driver to return 
an error for quotes, if they can't be used, instead of returning 
string as a word :-)

it is inconsistent with:


sql ["insert into test values (1, ?, ?)" "Petr" "Krenzelok"] ; in 
this case it returns strings, not words ... imo string and block 
sql queries should be compatible

It should return always string!

there is imo inconsistency between block and string format of query 
- if you use quotes with block format, it remains string, if you 
use quotes inside of string query, sqlite returns different values, 
because a word is returned ... imo there is bug in parsing and in 
the insert phase with string query format already ...

uhm, there is no parse inside sqlite.r ;-)

either string? statement [statement][first statement]

so it may as well be sqlite library, who is causing it ....

hehe, type? 'aaaaa'' == word! .... of a value aaaaa'

It could be a memory alignment problem as well. Maybe some kind of 
internal offset that gets screwed up.

I'm not following this , but in sql ' is used as the quote character 
for literal strings, not "

Graham - you may be right, but anyway, I would like to get even ' 
quoted chars returned back as a string to rebol, not a words ...

type?/word returns word!

type? returns datatype!

Pekr, for an explanation of string vs block see the "Using the Driver" 
section of: http://www.dobeash.com/SQLite/DriverGuide/


The various refinements (including /direct) are covered earlier in 
the document under "Database access functions".


Jaime: the type? change relates to a problem with "type? ... switch 
... #[datatype ..." vs "type?/word ... switch ... integer! ..." as 
the first form is not compatible with encap.

Hmm, adding /direct to the example posted previously and changing 
the last part of the INSERT to "... form $1 1 form $1 * 1" seems 
to work properly (100 error-free runs so far). The *only* difference 
then is in this line of the value binding logic:

	unless direct [val: mold/all val]

which if you change it to something like:

	unless direct [p: mold/all val]
	*bind-text sid i p length? p 0


seems to handle more runs before a failure. Thinking that mold/all 
might be the problem I then reran my /direct test with the following 
SQL statement:


 SQL reduce ["insert into t values (?,?,?,?,?)" 1 mold/all reform 
 ["A" 1] mold/all $1 1 mold/all $1 * 1]


which is functionally equivalent to the failing statement ... but 
no failures (after 100 runs). So, the conditions needed to reproduce 
this error [so far] are:

	SQLite library
	INSERT statement using a particular sequence of bind variables
	MOLD/ALL coded / used in a particular manner
	High volume of INSERTs

Now is that an obscure error or what? ;)

Ashley - I am not saying anything. I just want consistent result 
for those two cases - inserting a string into database in the same 
way, should return the same results ....

But they are not the same way ...

	SQL "insert into t values ('text')
	SQL {insert into t values ('"text"')}

map to:


 SQL ["insert into t values (?)" "text"]	; with /direct refinement

 SQL ["insert into t values (?)" "text"]	; without /direct refinement


The first approach in each case is saying, "I want this value to 
be stored as a SQLite TEXT value which will not be LOADed upon retrieval"; 
while the second is saying, "I want this value to be stored as a 
MOLDed SQLite TEXT value which will be LOADed upon retrieval back 
into a REBOL string value (as opposed to the word 'text)".


A string! statement is strictly literal, it is passed onto SQLite 
with no parsing or conversion. If you want to bind values, use the 
block form ... that's what it's there for!

But simply put - string is a string and in rebol I expect a string 
without the compromises or clumsy /direct refinement .... no wonder 
person I know remains with sqlite3 driver just because of that ...

I wonder if /direct is usefull at all and if we should have two modes 
.... does mysql driver has two modes?

and if I, and another person, independently run into problems agains 
what naturaly ppl would expect without checking docs and study some 
modes, then there is something wrong. Imo it is the same, as rebol 
shares subobjects by default - nearly EVERY person I know, run at 
some problems because of that. Of course it is by some purpose, but 
then some things are not  of "rebol is simiple" nature ...

It's simply about choice. By default the driver assumes you want 
to be dealing with the full range of REBOL datatypes. If all you 
need are the five datatypes that SQLite supports (Integer, Decimal, 
Binary, Text and Null) then use the /direct refinement. How is this 
"confusing" or "clumsy"? If you don't like the fact that the string! 
form differs from the block form then choose the one you are most 
comfortable with and stick with that.

ok, not sure now and I will retest, but the problem also is, that 
the string form allows you to use "text here" in quotes instead of 
'text here'  ...but - once you query your db, driver returns it as 
two separate words because of space - it simply ruins the block, 
because you get two elements instead of one. Bobik told me, that 
sqlite3 does not do that, so I assume it is a difference of how returned 
data is being processed. But I will do some tests using both drivers, 
because he defined his dbs using some external tool (sqlite admin 
 or so ...)

You'll find the exact same behaviour with the other sqlite3 scripts 
... none of them attempt to parse a literal statement string. The 
other scripts basically default to /direct which means that any non-numeric 
/ binary values are converted to TEXT (i.e. you can insert a REBOL 
date! but it comes back as a string!). If you want that behaviour 
then just use /direct. It's not that complex, really ...

Does sqlite support timestamp/date fields ?  Is there a way to set 
a default value for a field ( so that on a sql insert, you don't 
have to explicitly mention that column  ) ?

Are there any triggers ?

Or, autoincrementing fields ?

Triggers, Views, auto-increments - yes .... (not sure about auto-increments, 
but iirc yes)

default value - yes ...