Mailing List Archive: 49091 messages
  • Home
  • Script library
  • AltME Archive
  • Mailing list
  • Articles Index
  • Site search
 

[COMMAND] Database null values

 [1/4] from: davidegessi::tin::it at: 26-Jan-2003 4:59


Hi all, if I want to append new records to a table I can insert into a command port insert cmd-port [ "INSERT INTO tab (f1,f2,f3) VALUES (?,?,?)" v1 v2 v3 ] but how can I handle null values ? if f1 is a text field and v1 is none, then f1 will set to [to-string none] :( Could be the following a good approach ? to-sql-value: func [ v [any-type!] ][ either none? v [ return "null" ][ either string? v [ return rejoin ["'" to-string v "'"] ][ either date? v [ return rejoin ["#" to-string v "#"] ][ return to-string v ] ] ] ] sql-str: rejoin ["INSER INTO tab (f1,f2,f3) VALUES (" to-sql-value v1 "," to-sql-value v2 "," to-sql-value v3 ")"] insert cmd-port sql-str Any hint ? Davide

 [2/4] from: edanaii:cox at: 26-Jan-2003 8:11


Davide Gessi wrote:
>Hi all, >if I want to append new records to a table I can insert into a command port
<<quoted lines omitted: 27>>
>insert cmd-port sql-str >Any hint ?
Are you inserting into an Oracle database? If so, just use the SQL keyword NULL. It should be true of any database that uses SQL. If you are replacing "NONE" with "NULL" in your example, above, that should work fine. -- Sincerely, | Control is an illusion, you infantile egomaniac! Ed Dana | Nobody knows what's gonna happen next: not on a Software Developer | freeway, not in an airplane, not inside our own 1Ghz Athlon Amiga | bodies and certainly not on a racetrack with 40 | other infantile egomaniacs! | -- Nicole Kidman, Days of Thunder

 [3/4] from: g:santilli:tiscalinet:it at: 26-Jan-2003 19:32


Hi Davide, On Sunday, January 26, 2003, 4:59:02 AM, you wrote: DG> Could be the following a good approach ? This is what I use with MySQL: 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 "'" ] I think Nenad has an improved version in his mysql-protocol, called to-sql. Regards, Gabriele. -- Gabriele Santilli <[g--santilli--tiscalinet--it]> -- REBOL Programmer Amigan -- AGI L'Aquila -- REB: http://web.tiscali.it/rebol/index.r

 [4/4] from: davidegessi:tin:it at: 28-Jan-2003 17:12


Thanks Gabriele & Ed, seems that Rebol works fine with DB2, Oracle and MS Access at the same time :) Davide

Notes
  • Quoted lines have been omitted from some messages.
    View the message alone to see the lines that have been omitted