Re: [INTERFACES] pgaccess & null dates

Ok, yes I agree. Absolute user control this way. I would however, make one
more quickie change to remove case sensitivity:

>+ if {[string toupper $sqlfldval] == "NULL"} {

Ken

At 01:20 PM 4/29/99 -0500, Ross J. Reedstrom wrote:
>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(at)rice(dot)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} {
>