World: r3wp

sql-protocol don't need these heavy use of 'compose, 'join, 'reduce 
 if you use the dialect. Something like this work :
   var1: "Marco" 
   insert my-db [select * from my-table where name = var1]
   my-result: copy my-db

Another example to ilustrate this :

   insert my-db [select * from my-table where name like rejoin ["*" 
   var1 "*"]]


This is only if you use the dialect to query the database. If you 
use the standard SQL string, you need to compose the query.

ashley, I'm starting to use rebdb this week for a very cool segmented 
code versioning project.

wrt simplyfing the use of "noise"  ... why not just call compose/deep 
by default within the 'execute call of the client on ALL sql calls? 
 it could be a global option and IMHO the use of parens within the 
code is quite obvious and is used in many dialects. and its less 
cumbersome than to add the reduce word in your code, a string and 
then variables out of context of the sql statement.

See my post from 12-Feb. The answer to "noise" is substitution variables 
(as it's a form of expression more familiar to long time SQLers). 
Also, I've never liked the idea of performing an operation (be it 
'compose, 'reduce, 'copy, etc) that takes away that choice from the 
coder ... someone may use statements that *never* need to be composed 
and they shouldn't have to carry that cost just because some else 
needs it.

I understand... which is why I noted, it could be an option... cause 
although  substitution variables are closer to SQL,  expression embedding 
within parenthesis is quite familiar in rebol ... 


but in any case, We can all just wrap the rebdb calls within our 
own I guess ;-)

and btw... good job on rebdb... for what it does,  I like it a lot.

.

Is it possible to parametrize the rowid? If I read the error message, 
it seems not :

>> pickno: 7
== 7
>> db-select/where * prospectDB [rowid = :pickno]
** Script Error: Cannot use subtract on get-word! value
** Where: switch
** Near: insert tail buffer copy/part skip
>> :pickno
== 7
>> db-select/where * prospectDB [rowid = 7]
== [

    7 "7-02-2006" "Nom-7" "Prénom-7" "Québec" "Cie-7" "Référence: US" 
    "Conjoint-7" "Enfant-7" "Bur: 418-845-7" "Rés: 418-845-7...
>>

Also should I select a row before to delete it or update it?  Reading 
the Quick start guide, il may be implicit from the examples.

The Parenthesis does not work too:

>> db-select/where * prospectDB [rowid = (pickno)]
** Script Error: Cannot use subtract on paren! value

>> db-select/where * prospectDB compose [rowid = (pickno)]

Thanks that works.  Can someone provide the syntax in db.r of the 
following command in sql.r.  I am looking for the function to update 
all or some 20 fields, loading only db.r in my interface.  The manual 
is sparse, so I tried it in sql.r but it does not give the equivalent 
function.  Is there an echo to toggle to have such translation? It 
would be helpfull to later to discover db.r.  The function I tried 
is :

update tbtst set [id name surname] to [2 "Leclerc2" "Normand2"] where 
[rowid = 2]

help db-update is a good start. ;) For a full list of db functions 
just enter "help db-" at the console.

is anyone here interested in a contextualised version of rebdb ? 
 I mean one with everything hidden within an object, and most functions 
fixed to support it?

it allows us to use rebdb by supplying table names within values, 
instead of having to supply them directly within code.

that is not the problem of RebDB imo ... e.g. me and my friend stopped 
using it because of missing 'join and because Ashley stated, that 
instead of reinventing the wheel (RIF based on-disk version), he 
suggests SQLite ....

the only drawback is that sql use needs ticks for table names.   
acter using it for a while I actually like this better, cause it 
clearly identifies what is a reserved word and was is an argument, 
within the statements.

other than that - I like RebDB very much and I regard it being one 
of the best rebol tools so far ....

I have a merge function.   :-)

which can do some of the joining.

but you do need to do two queries beforehand... which is not exactly 
a join.. but anyhow you do end up with a block containing interleaved 
data from two tables ... and its pretty quick.

Another newbee one: db-select can retreive the rows on multiples 
values like the query  db-select/where * noms [any [nom = 'leclerc 
prenom = 'marion]] but can I query a column's values to find all 
rows where a column contains a substring, like the query db-select/where 
* noms [find ["lec"] nomprenom] ?  This last one is not the answer. 
 What would be the good query for such a purpose?

Almost there, needs to be in the form:

	db-select/where * my-table [find string-column "string"]

I am trying to update my RebDB database with a level of indirection,
as I am using a block of 39 colunms names and 
the corresponding 39 entry-fields from ReBGui.

Shortening those blocks for the example's sake, I do try to update 
the DB with the following command:

Let's say that exColumns: [name surname dob phone] and exValues: 
["Doe" "Jane" "1990-01-01" "418-840-4040"].

Then >> db-update/where exDB (exColumns) (exValues) compose ['rowid 
= (to-id)] do not work, nor variants I know.
Proof:
>> db-create exDB exColumns == true

>> db-insert exDB ["Foe" "Joe" "1991-01-01" "514-480-8080"] == true
>> db-commit exDB == true

Then what is a working update, as those do not work with indirection 
in column & values spec and rowid?
to-id: 1

Variant1 >> db-update/where exDB exColumns exValues compose [rowid 
= to-id]
== ** User Error: Invalid or duplicate column

Variant2 >> db-update/where exDB (exColumns) (exValues) compose [rowid 
= to-id]

== ** Script Error: db-update expected columns argument of type: 
word block

Variant3 >> do [ db-update/where exDB exColumns exValues compose 
[rowid = to-id] ]
== ** User Error: Invalid or duplicate column

Variant4 >> reduce [ db-update/where exDB exColumns exValues compose 
[rowid = to-id] ]
== ** User Error: Invalid or duplicate column

Variant5 >> reduce [ db-update/where exDB (exColumns) (exValues) 
compose [rowid = to-id] ]

== ** Script Error: db-update expected columns argument of type: 
word block

The more I go, the more I am frustrated into the word/value distinction.
What is the resiliently simple answer to my show stopper?

I also tried:

>> db-update/where bottinDB (get fieldsnames) (get fvalues) compose 
['rowid = (to-id)]

** Script Error: db-update expected columns argument of type: word 
block

>> do [ db-update/where bottinDB (get fieldsnames) (get fvalues) 
compose ['rowid = (to-id)] ]

** Script Error: db-update expected columns argument of type: word 
block

>> reduce [ db-update/where exDB ( get exColumns) (get exValues) 
compose [rowid = to-id] ]

** Script Error: db-update expected columns argument of type: word 
block
I am getting short of inspiration.

And about find:
db-select/where * exDB [find name "e"]
Expectedly, any letter of the string searched retrieves it:
>> db-select/where * exDB [find name "e"]
== ["Foe" "Joe" "1991-01-01" "514-480-8080"]
>> db-select/where * exDB [find name "oe"]
== ["Foe" "Joe" "1991-01-01" "514-480-8080"]
>> db-select/where * exDB [find name "Foe"]
== ["Foe" "Joe" "1991-01-01" "514-480-8080"]

But, reverting column and search-string, is this behaviour a specification 
of RebDB, or is it simply luck?
>> db-select/where * exDB [find "e" name]
== []
>> db-select/where * exDB [find "Foe" name]
== ["Foe" "Joe" "1991-01-01" "514-480-8080"]

Following works fine:

	db-update/where names name "Jane" [rowid = 1]

and reversing the find is saying:

	find "e" name -> where name appears in "e" ... so [] is corect

 find "Foe" name -> where name appears in "Foe" ... so ["Foe" ...] 
 is correct

is this behaviour a specification of RebDB

 ... this is how the REBOL find word works. Type "help find" at the 
 REBOL console for more information.

Thanks for the 'find' explanation.  As for the update, the formula 
'db-update/where names name "Jane" [rowid = 1]' is a rather simple 
case.  I am trying to update a 39 columds database splitted into 
3 tab-panels.  So I figure that there is a way to parametrize the 
update and to obtain back, in return, the rowid  where RebDB placed 
it, as the rowid is not allways the same as the saved id, as shown 
in >> db-select id bottinDB
== [1 2 3 4 7 8 9 10]
>> db-select rowid bottinDB
== [1 2 3 4 5 6 7 8].

db-update returns an integer! indicating how many rows where updated. 
Rowid is the physical row sequence computed dynamically at query 
execution.


You can update multiple columns at once by providing a block of column 
names followed by a block of values (but number of values must match 
number of columns). If you are updating a large number of column 
values it is probably more efficient (and easier to code) to delete 
then re-insert the row.

Well.  Is there any way to obtain the rowid (physical row order number) 
of a selected entry, or deleted or updated entry?  I am trying to 
devise a way to navigate into the database, from the initial point 
of a query or of an update.  But with delete operations, the first 
column, id, is not allways in sequence.  >> db-select id bottinDB 
 == [1 2 3 4 7 8 9 10]  >> db-select rowid bottinDB == [1 2 3 4 5 
6 7 8].  And, as much as I can, I want to keep the historical order 
of entry, as it gives me information.  It may be usefull to know 
that I did read a book or meet a person before another. The rowid 
gives a hint to that.  All and all, parametrizing the update and 
obtaining the rowid are stoping me to complete my small everyday 
application.  By the way, I do not see any example of 'demo' applications 
with RebDB and RebGui.  A little help with mine could give birth 
to one.  I should say that, design wise, RebGui is a great decision, 
to favor simplicity.  You adress there some needs never covered by 
other systems: the myriads of simple applets everybody needs but 
were unable to ask.  After a couple of  weekends, I have got 400 
lines of codes and am almost done.  And I am not a programmer. So 
I figure out how efficient it is for you guys. Thanks.

It sounds like you should add your own field for insert order. I 
don't think that RebDB keeps the records in the order it inserts 
them. Even its web site says that it sorts the records when selecting 
- this isn't Access you know. If you want to keep some information 
in the database, put it there.

If records aren't inserted often you can use a date/time for the 
field - otherwise use a number that you would increment. Keep in 
mind that if you do a lot of insertions and deletions, a number field 
could end up overflowing unless you occasionally compact the numbers.

Another option is to select the rowid(s) you are after:

	id: db-select/where rowid table [...]

then perform the operations you want:

	db-update/where ... compose [ ... (id) ...]


BTW, thanks for the feedback on both RebDB/RebGUI ... always good 
to know where folks are having both success and problems. More demos/tutorials 
are on the ever growing ToDo list. ;)

Thanks for those suggestions.   If I finish it, I'll feedback with 
it.

hello, i d like to know if its possible to call db.r script from 
an encaped program.  It says that core 2.6 is needed and if i try 
to remove the security it fails.  what i try to do is the following 
:
rebol []


#include %/C/Documents and Settings/xavier/rebApps/spikes/mezz.r;

#include %/C/Documents and Settings/xavier/rebApps/spikes/prot.r

#include %/C/Documents and Settings/xavier/rebApps/spikes/view.r

test: request-file
write %test/param.par test
do to-rebol-file read %test/param.par
probe db-select * patients
ask ""


in this example the file u are supposed to point is db.r.  When it 
s not encaped it work, not when it is.  if someone has an idea ...

A guess is that the path  %test/param.par  is not being resolved 
correctly when encapped. Try adding.
	probe what-dir
and see if the results are different encapped and normally.

RebDB uses features introduced in core 2.6 (case, unless, etc) so 
the solution to your problem is to use an SDK that supports Core 
2.6 and later. Calling %db.r from your encapped script will use the 
SDK version of REBOL, whilst running it from file explorer will use 
whatever version of REBOL is associated with .r files. Latest SDK 
builds are located here: http://www.rebol.net/builds/sdk/


You should then be able to include %db.r directly in your encapped 
script.r.

it works ashley , thanks for everything , u found the solution.  
thanks to that i normally could be able to access to rebdb from my 
encaped program.  I ll do trial to see if i can access to the db.r 
script from the network but normally this could be THE solution to 
my problem.  Thanks for your very good job and for the information 
:)).

Ashley, I noticed some strange behavior with db-select.  If you do 
NOT supply the /where argument then blocks are inserted into the 
result correctly but if do they are inserted inline.

Example:
>> db-describe tags
== [id integer! tag string! files block!]
>> db-select [id files] tags
== [1 [3]]
>> db-select/where [id files] tags [tag = "t"]
== [1 3]


I've spent about an hour trying to find where to fix this in db.r 
but I'm not seeing it.  Any ideas?

Sounds like a missing /only refinement on an insert. I'll look at 
this later tonight ... odd how no-one else has hit this in over 2 
years ... maybe not many folks store blocks! ;)

That's what I thought so I started poking around but I didn't find 
the place to add the /only to...

Think I've nailed the 3 spots /only is required:

	http://www.dobeash.com/RebDB/RebDB-203.zip


Note that this pre-release includes a few other untested changes; 
including /joins and /replaces refinements to db-select (refer discussion 
from 12-Feb-2006 onwards for explanation).

interesting to know, someone still uses RebDB ... although in-memory 
and not shareable, it is cool Rebol design!

I'm hoping R3 will make it and the SQLite driver redundant ;)

ah, high hopes :-)

I can imagine solving even sharing problem - simply to queue requests 
....

the question is, if RIF comes or not.