Re: [INTERFACES] pgaccess & null dates - Mailing list pgsql-interfaces

Ken J. Wright wrote:
>
> At 08:59 AM 4/29/99 -0700, you wrote:
> >PgAccess 0.96, RH5.2
> >
> >When editing a table and clearing a datetime field in the grid, the update
> >fails because the update sql is not declaring the new value as null, but as
> >an empty string.
> >
>
> This fixes it, although it also makes an empty string a null. The lessor of
> evils I think, without changing to do some field type checking before the
> update.
>

Two things - your patch wrapped, so be careful - unified diff is more
readable BTW.
Second: No! don't do it this way! "" vs null is an important
distinction. Here's my take on a patch to handle this: it's a quick
'have something work now' hack. It uses the sql standard string
representation of a null, i.e. the word null :-)

Pgaccess has a few quoting problems still. I think the right solution is
to come up with an sqlquote proc that takes a string and returns the sql
quoting required to us it as a SQL value. This is seperate from an
sqlpquote, which would take a string and return the minimum quoting to
use it as a parameter id (field or table name). Then, go through
pgaccess and wrap all variables that derive from end user input (either
from the UI or from the pg database). The only problem is to remember to
_unwrap_ these in the right places (I've had this problem turn into
exponentially growing single quotes ').

Here's the patch: (also in a file attachment)
---------------------------------------------------cut-here---------------------------------
--- pgaccess.tcl.orig   Thu Apr 29 12:54:35 1999
+++ pgaccess.tcl        Thu Apr 29 13:08:31 1999
@@ -1422,7 +1422,19 @@
        set mw($wn,msg) "Updating record ..."
        after 1000 "set mw($wn,msg) {}"
        regsub -all ' $fldval  \\' sqlfldval
-       set retval [sql_exec noquiet "update \"$mw($wn,tablename)\" set
\"$fld\"
='$sqlfldval' where oid=$oid"]
+
+#FIXME rjr 4/29/1999 special case null so it can be entered into tables
+#really need to write a tcl sqlquote proc which quotes the string only
+#if necessary, so it can be used all over pgaccess, instead of explicit
's
+
+       if {$sqlfldval == "null"} {
+            set retval [sql_exec noquiet "update \"$mw($wn,tablename)\"
\
+               set \"$fld\"= null where oid=$oid"]
+         } else {
+          set retval [sql_exec noquiet "update \"$mw($wn,tablename)\" \
+               set \"$fld\"='$sqlfldval' where oid=$oid"]
+       }
+
 }
 cursor_normal
 if {!$retval} {
---------------------------------------------------cut-here---------------------------------


--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005--- pgaccess.tcl.orig    Thu Apr 29 12:54:35 1999
+++ pgaccess.tcl    Thu Apr 29 13:08:31 1999
@@ -1422,7 +1422,19 @@
     set mw($wn,msg) "Updating record ..."
     after 1000 "set mw($wn,msg) {}"
     regsub -all ' $fldval  \\' sqlfldval
-    set retval [sql_exec noquiet "update \"$mw($wn,tablename)\" set \"$fld\"='$sqlfldval' where oid=$oid"]
+
+#FIXME rjr 4/29/1999 special case null so it can be entered into tables
+#really need to write a tcl sqlquote proc which quotes the string only
+#if necessary, so it can be used all over pgaccess, instead of explicit 's
+
+    if {$sqlfldval == "null"} {
+            set retval [sql_exec noquiet "update \"$mw($wn,tablename)\" \
+            set \"$fld\"= null where oid=$oid"]
+         } else {
+          set retval [sql_exec noquiet "update \"$mw($wn,tablename)\" \
+          set \"$fld\"='$sqlfldval' where oid=$oid"]
+       }
+
 }
 cursor_normal
 if {!$retval} {