date! and SQL..
[1/5] from: chris::starforge::co::uk at: 11-Jul-2001 19:44
Hi,
Probably a rather stupid question to ask, but I prefer to be sure....
I've been playing around with Doc Kimbel's mySQL scheme and noticed in
the usage doc "String! datatypes will be escaped, all others types will be
MOLDed ! (So, watch out when working with types like date! !)". Is this still
true? (ie: the latest version doesn't do type conversion for inserts) If so
is doing something like
>> to-sql-date: func [
[ "Convert REBOL date (dd-mmm-yyyy) to SQL (yyyy-mm-dd) format"
[ source [date!] "date to convert"
[ ][
[ rejoin [source/year "-" source/month "-" source/day]
[ ]
>> insert db ["insert into test values (?,?)" "Entry1" to-sql-date now/date]
the correct way to go about inserting with dates?
Chris
[2/5] from: petr:krenzelok:trz:cz at: 11-Jul-2001 21:48
Hi,
I haven't tried inserting into table yet, but obtaining data from
AdvantageDatabaseServer via ODBC rebol seems to tranlate dates into rebol
ones ... I am just curious, if rebol automatically translates rebol date
datatypes and converts them into database format it is connected to ...
-pekr-
[3/5] from: dockimbel:free at: 12-Jul-2001 1:13
Hi Chris,
Yes, this is currently the correct way. Unfortunately, i didn't have time to
include all the types conversion in the last release. Next release (next week...)
will handle all the conversions for you.
In the meantime, you can use external functions to convert values or directly
patch the driver source in the 'to-sql function :
to-sql: func [value][
switch/default mold type? value [
"none!" ["NULL"]
; "date!" [] ; <= add you own code here
; "time!" []
; "money!" []
"string!" [join "'" [sql-escape value "'"]]
][value]
]
Regards,
DocKimbel.
Chris wrote:
[4/5] from: g:santilli:tiscalinet:it at: 12-Jul-2001 21:56
Hello Chris!
On 12-Lug-01, you wrote:
C> the correct way to go about inserting with dates?
This does more than needed, but maybe you will find it useful.
form-sql: func [
"Forms a value into SQL syntax"
value
/local result chars normal-chars
] [
if none? :value [return "NULL"]
if :value = '__last-id [return "LAST_INSERT_ID()"]
if date? :value [
return rejoin compose [
"'" (value/year) "-" (value/month) "-" (value/day)
(either value/time [reduce [" " value/time/hour ":" value/time/minute ":" value/time/second]]
[])
"'"
]
]
if time? :value [
return rejoin ["'" value/hour ":" value/minute ":" value/second "'"]
]
if number? :value [return form value]
if block? :value [
if empty? value [return "(NULL)"] ; SQL's quite silly... this is a workaround
result: make string! 256
insert tail result "("
foreach element value [
insert insert tail result form-sql :element ","
]
return head change back tail result ")"
]
if word? :value [return form value]
if not any-string? :value [value: mold :value]
result: make string! 256
insert tail result "'"
normal-chars: complement charset "^(00)^/^-^M^(08)'\"
parse/all value [
any [
#"^(00)" (insert tail result "\0")
| #"^/" (insert tail result "\n")
| #"^-" (insert tail result "\t")
| #"^M" (insert tail result "\r")
| #"^(08)" (insert tail result "\b")
| #"'" (insert tail result "\'")
| #"\" (insert tail result "\\")
| copy chars some normal-chars (insert tail result chars)
]
]
head insert tail result "'"
]
(This is part of a set of functions to simplify accessing MySQL
from REBOL. If anyone's interested in doing betatesting /
providing comments...)
Regards,
Gabriele.
--
Gabriele Santilli <[giesse--writeme--com]> - Amigan - REBOL programmer
Amiga Group Italia sez. L'Aquila -- http://www.amyresource.it/AGI/
[5/5] from: chris:starforge at: 13-Jul-2001 17:35
On 12-Jul-01, Gabriele Santilli wrote:
> Hello Chris!
> On 12-Lug-01, you wrote:
>> the correct way to go about inserting with dates?
> This does more than needed, but maybe you will find it useful.
<snip>
:) Should be very useful indeed!!
> (This is part of a set of functions to simplify accessing MySQL
> from REBOL. If anyone's interested in doing betatesting /
> providing comments...)
I'm interested :)
Chris
--
New sig in the works
Explorer2260, Designer and Coder
http://www.starforge.co.uk
--
Magnocartic, adj.:
Any automobile that, when left unattended, attracts shopping
carts.
-- Sniglets, "Rich Hall & Friends"