date! and SQL..

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 [

>> insert db ["insert into test values (?,?)" "Entry1" to-sql-date now/date]

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-

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:

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/

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>

> (This is part of a set of functions to simplify accessing MySQL > from REBOL. If anyone's interested in doing betatesting / > providing comments...)