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"